Формулы массивов крайне редко применяются пользователями Excel и многие даже не слышали об их существовании. Я уже посвятил отдельную заметку формулам массива, но она была довольно поверхностной, поэтому решил копнуть эту тему чуть глубже.
Во-первых, определимся с тем, что такое массив.
Под массивом обычно понимается некоторый набор значений - элементов массива. Применительно к Эксель можно сказать, что массив - это некоторый диапазон ячеек. При этом массив может быть одномерным (строка или столбец), либо двумерным.
Часто при использовании формул в Эксель мы в функции подставляем массивы, то есть некоторые диапазоны ячеек, но при этом на выходе всегда получаем единственное значение. Так, например, функция СУММ может просуммировать значения двумерного массива, то есть некоторого диапазона и на выходе мы получим лишь одно значение - рассчитанную сумму.
Формулы массива работают иначе. Они обрабатывают указанный массив или несколько массивов и на выходе получаем также массив значений. В ряде случаев полученный массив может обрабатываться какой-либо функцией, и в итоге результатом будет только одно значение, как и при использовании стандартных функций.
Давайте рассмотрим примеры.
Таблица умножения
Пожалуй, классический пример применения формулы массива - это таблица умножения.
У нас есть два массива значений, элементы которого нужно попарно перемножить.
Задачу можно решить с помощью смешанных ссылок, о чем я рассказал в соответствующем видео, но намного быстрее и проще воспользоваться формулой массива.
Чтобы задать формулу нам необходимо предварительно выделить диапазон ячеек, в которых будет формула массива работать. Затем вводим знак равенства и создаем формулу. Фактически нам нужно перемножить два диапазона значений, поэтому выделяем первый диапазон, вводим знак умножения и выделяем второй диапазон.
Таким образом мы попарно перемножим значения массивов, но, чтобы все работало как надо, мы должны не просто нажать Enter, а нажать сочетание Ctrl + Shift + Enter. Тогда Excel воспримет введённую ранее формулу именно как формулу массива.
Мы получили необходимые значения и, выбирая ячейки диапазона с формулой массива, мы увидим в строке формул одну и ту же формулу. При этом она заключена в фигурные скобки, которые были подставлены Excel автоматически. Фактически это своеобразный атрибут формулы массива, то есть скобки дают вам понять, что перед вами формула массива. Вводить их вручную бесполезно, так как при этом ваша формула не станет формулой массива. Это произойдет только при нажатии сочетания Ctrl + Shift + Enter.
Если вы сами создали формулу массива, то, скорее всего, помните какие диапазоны в ней задействованы и для каких ячеек она задавалась. Но вот когда вы изучаете чужой документ, то может потребоваться время, чтобы это определить. Проще всего в такой ситуации воспользоваться сочетанием Ctrl + /, которое позволит быстро выделить весь массив ячеек, для которого создавалась формула массива - устанавливаем табличный курсор в любую ячейку с формулой массива и нажимаем это сочетание.
Товарный чек
Еще один классический пример формулы массива - это товарный чек.
У нас есть товар, его цена и количество. Необходимо быстро посчитать сумму заказа. Обычно такие задачи решаются с помощью вспомогательного столбца, в котором рассчитывается общая стоимость каждого товара, а затем эти значения суммируются.
Но мы можем создать простейшую формулу массива и результат будет таким же.
Правда если мы просто перемножим диапазоны, то на выходе получим массив значений произведений. Так как нам нужна сумма этих значений, то воспользуемся стандартной функцией СУММ, которая выдаст итоговую сумму.
Еще для решения этой задачи можно воспользоваться функцией СУММПРОИЗВ, которая также не требует промежуточных расчетов и выдаст ровно такой же результат.
Можем усложнить задачу, например, добавив еще один столбец, в котором будет указана стоимость упаковки.
Таким образом с помощью формулы массива можно быстро просчитать общую сумму заказа.
Объем продаж
По аналогии можно посчитать общую сумму продаж за определенный период. Например, есть таблица с продажами определенных товаров по месяцам. Необходимо быстро прикинуть общую сумму выручки за все время. Создаем формулу, в которой суммируем перемноженные массивы - цену товара и его количество.
В итоге очень быстро получаем необходимое значение.
Мы рассматривали довольно простые задачи, но формулы массива можно значительно усложнить, например, добавив условие.
Допустим, нам нужно быстро получить объемы продаж товаров, цена которых ниже определенного значения. Применить можно функцию ЕСЛИ - если значения из диапазона цены больше интересующего нас значения, то эту цену учитывать не будем, то есть подставим ноль. Иначе возьмем значение из необходимого диапазона и умножим на количество.
Не забываем нажать Ctrl+Shift+Enter, чтобы сделать формулу формулой массива. Все готово.
Массив констант
До этого момента мы рассматривали массивы, находящиеся в ячейках диапазона на листе Эксель, но мы можем использовать массив констант, прописанный прямо в формуле.
Например, давайте решим такую задачу - нам нужно, чтобы при вводе в ячейку А1 номера месяца, его название появлялось рядом в ячейке В1.
Воспользуемся функцией ИНДЕКС, которая возвращает значение из диапазона по номеру строки и столбца, но вот сам диапазон мы указывать не будем, а зададим массив констант вручную.
Массив задается в фигурных скобках, элементы массива (если это текст, а не числовые значения) заключаются в двойные кавычки и разделяются между собой двоеточием. Таким образом мы вручную создали массив из двенадцати элементов и по порядковому номеру элемента массива мы будем его выводить в ячейке В1. Сам порядковый номер будет задаваться в ячейке А1, поэтому сошлемся на нее.
Для ввода формулы, в которой есть массив констант сочетание Ctrl+Shift+Enter нажимать не нужно, достаточно нажать Enter.
В итоге получаем довольно простую формулу, которая решает поставленную задачу.
Кстати, любой диапазон значений, который используется в формуле, можно превратить в массив констант. Для примера возьмём простейшую формулу с функцией СУММ, которая возвращает сумму столбца А.
Выделяем диапазон А1:А9 в формуле и нажимаем клавишу F9 на клавиатуре. Теперь в формуле фигурируют уже не ссылки на ячейки листа, а массив констант, а значит формула не привязана к диапазону, на основе которого она создавалась и эти значения с листа можно удалить.
Иногда это бывает полезным, например, когда нужно получить массив констант для использования в других формулах. Так в предыдущем примере я создавал массив констант с названиями месяцев вручную, но мог бы быстро создать диапазон значений на листе с помощью автозаполнения, затем в ячейке рядом создам формулу - введу знак равенства и выберу только что созданный диапазон значений.
Теперь осталось выделить диапазон в формуле и нажимать F9. Полученный массив констант можно скопировать и использовать в других формулах.
Плюсы и минусы формул массива
Ну и подводя итог, нельзя не сказать о плюсах и минусах формул массива.
Из плюсов можно назвать универсальность, так как формулы массива работают во всех версиях Excel, а вот новые функции и, например, динамические массивы, появились в Эксель относительно недавно.
Также формулы массива зачастую намного более компактны и заменяют собой промежуточные вычисления, в чем мы уже убедились, рассматривая примеры.
Но есть и минусы.
Пожалуй, главным является достаточно сложная для понимания логика формул массива. Именно поэтому многие не применяют их в своей работе.
Ну и еще один существенный минус - ресурсоемкость. Использование формул массива при работе со значительными диапазонами отразится на быстродействии расчета. То есть формулы массива будут довольно сильно нагружать компьютер, ведь при изменении любого значения входящих массивов будет пересчитываться формула для всего диапазона, а это могут быть десятки тысяч ячеек.
Но несмотря на недостатки формулы массива могут быть весьма полезными в ряде ситуаций. Например, с их помощью можно смоделировать функцию ВПР, которая будет возвращать не только первое найденное значение (как это делает стандартная функция), но все значения, удовлетворяющие заданному критерию. И подобную задачу я подробно разберу в одной из последующих статей.
Кстати, работу с формулами массива при анализе данных я подробно разобрал в видеокурсе "Microsoft Excel Шаг за Шагом". Умение составлять формулы и применять стандартные функции - один из ключевых навыков работы в Excel и именно его "прокачивает" данный курс.
Узнать подробности можно на сайте курса >>
Ссылки на мои ресурсы по Excel
★ Телеграм
