О чём идёт речь?

Можно годами работать с Excel, очень неплохо в нём разбираться, но при этом совершенно ничего не знать о формулах массивов. Настолько этот инструмент незаметен, парадоксален, на первый взгляд, и непонятен, что самому, методом тыка с ним ознакомиться практически нереально - нужна небольшая теоритическая часть. Вот такое введение в формулы массивов вы в данный момент и читаете.

То, к чему мы привыкли

Мы все привыкли к тому, что в качестве параметров в формулах используются ссылки либо на отдельные ячейки, либо на диапазоны ячеек. При этом, редко какие формулы являются всеядными, принимая в качестве параметров и то, и другое. Обычно формула всё-таки оперирует параметром, состоящим из одной ячейки или значения. Таких формул много, например, математические, логические, проверки свойств и значений.

Например, формула модуля числа =ABS(A2:A3) вернёт ошибку #ЗНАЧ!, так как ожидает в параметрах увидеть только одно число, а не массив чисел.

Предположим нам надо подсчитать сумму чисел, взятых по модулю, в каком-то диапазоне ячеек. Что в таких случаях все обычно делают? Ясное дело - добавляют к таблице колонку, в которой будут вычисляться модули чисел, а потом суммируют значения этой колонки в отдельной итоговой ячейке. Это стандартный, всем понятный подход - если задача сложная, то надо разбить её на части и решить по частям.

Альтернатива

Так вот формулы массивов часто позволяют избежать промежуточных расчётов и получать результат сразу при помощи только ОДНОЙ формулы. Посмотрите на этот экран:

Вы видите буквальто то, что вызывало ошибку абзацем выше в обычной формуле! Но в том то и дело, что сейчас перед нами в ячейке D2 не обычная формула, а формула массива. Обратите внимание на характерные фигурные скобки, обрамляющие такую формулу. Только не пытайтесь вводить эти скобки с клавиатуры - Excel примет это за текстовую строку. Чтобы ввести в ячейку формулу массива, надо в конце нажимать не Enter, а Ctrl+Shift+Enter. Только в этом случае Excel вас поймёт правильно.

Как это работает?

Как же работает {=СУММ(ABS(B2:B10))}? Дело в том, что Excel вычисляет эту формулу хитро. Видя, что в параметрах стоит диапазон ячеек B2:B10 (а диапазон - есть массив), Excel вычисляет значение функции ABS для каждой ячейки диапазона (элемента массива). То есть он вычисляет ABS(B2), затем ABS(B3), ..., ABS(B10), далее он снова объединяет это всё в единый массив и передаёт его в функцию СУММ, которая и возвращает нам итог. Всё это происходит в памяти компьютера автоматически, а мы видим уже только конечный результат, удивляясь откуда, что берется.

Более сложный пример

Представим, что нам с вами надо контролировать содержимое некоего диапазона. Мы хотим знать, сколько в нём числовых значений, текстовых строк, логических значений, пустых ячеек и сколько ячеек в статусе ошибка (в данном случае я воспользовался операцией деления на ноль, вызывающую ошибку #ДЕЛ/0!). Я не знаю простых и изящных способов, как это можно было бы сделать без формул-массивов. Но зато с последними задача решается "без шума и пыли".

Тут надо кое-что пояснить:

• Зачем я использую функции ЕЛОГИЧ (ISLOGICAL), ЕТЕКСТ (ISTEXT), ЕОШ (ISERR), ЕПУСТО (ISBLANK), ЕЧИСЛО (ISNUMBER), думаю, понятно - я выясняю таким образом, что в ячейке.

• Непонятно, наверное, зачем я прибавляю к результатам ноль? Дело в том, что вышеуказанные функции возвращают не числовые, а логические значения (ИСТИНА/ЛОЖЬ), которые, таким образом (прибавлением ноля), конвертируются в свои числовые эквиваленты (ИСТИНА=1, ЛОЖЬ=0). В результате функция СУММ (SUM) имеет уже дело с нулями и единицами, а не с ИСТИНА и ЛОЖЬ, с которыми она бы работать не стала. Аналогичного результата можно добиться не прибавляя ноль (+0), а умножая на единицу (*1). Выбор за вами.

Скачать файл примера

Добавим красоты

А теперь за счёт умных таблиц я продемонстрирую, как формулы массивов изящно избавляются от абсолютных координат диапазона $A$2:$C$21. Последний заменяется на лаконичное Таблица2, а формулы после этого не надо менять при добавлении/удалении строк из нашего диапазона! Разве это не круто?

Дальше что?

Я вам много-чего не договорил про формулы массивов, но вы поняли суть и теперь можете пробовать двигаться самостоятельно.

Читайте также:

• Список всех формул Excel и классификация их востребованности

• Видеоурок по функции ВПР (VLOOKUP)

• 7 причин полюбить именованные диапазоны

• Функция ВПР (VLOOKUP) или тайна четвёртого параметра

• Производительность функций извлечения данных

• Усовершенствованные динамические именованные диапазоны

• Наиважнейшая формула Excel