Формула массива в Excel. Примеры использования

В Экселе есть замечательный инструмент, который называется формулой массива. Он позволяет выполнять действия не с отдельными значениями, а с целыми массивами данных, что значительно расширяет возможности Excel.

Пример применения формулы массива

Наиболее простой задачей, которой можно проиллюстрировать мощь формулы массива, является простой товарный чек. То есть у нас есть наименование товаров, их количество и цена за единицу. На выходе мы должны получить общую сумму покупки.

Как бы мы решали данную задачу стандартным образом?

Во-первых, мы бы получили итоговую сумму по каждому товару, перемножив количество товара на его цену.

Ну а затем просуммировали бы получение суммы, для получения итоговой. То есть нам бы потребовался дополнительный столбец для промежуточных вычислений.

Ту же самую задачу можно решить с помощью простейшей формулы массива.

Нам нужно получить сумму, поэтому воспользуемся соответствующей функцией СУММ. А вот суммировать мы должны произведение цены товара на его количество, что мы и сделаем — выбираем диапазон значений из столица B и умножаем его на аналогичный диапазон значений столбца C.

Если сейчас нажать Enter, то появится ошибка.

Так как мы в формуле использовали диапазоны (массивы) данных, то и формула должна быть формулой массива. Для этого нужно нажать сочетание клавиш Ctrl + Shift + Enterи получим результат.

Что произошло и как работает формула массива? Это важно понять, так как в дальнейшем можно будет применять формулы массива для решения намного более заковыристых задач…

Итак, при вычислении формулы массива Excel произвел попарное умножение значений диапазонов B2:B5 и C2:C5. В результате получился массив значений который был просуммирован соответствующей функцией. Мы получили только одно значение итоговой суммы без вспомогательных вычислений.

Обратите внимание на формулу в строке формул. Она заключена в фигурные скобки, которые указывают нам, что мы имеем дело с формулой массива.

Это не текстовые скобки, то есть нельзя их ввести с клавиатуры, чтобы сделать формулу формулой массива. Они появляются автоматически при нажатии сочетания клавиш Ctrl + Shift + Enter. Если после создания формулы массива вам необходимо ее отредактировать, то в конце необходимо вновь нажать сочетание клавиш, а не просто клавишу Enter.

Анализ данных с помощью формулы массива

Формулы массива очень удобно применять для анализа массивов данных, например, когда нужно получить информацию, соответствующую определенному критерию. Давайте разберем еще один пример, который наглядно продемонстрирует мои слова.

Довольно стандартная ситуация, когда нужно сделать выборку по определенному критерию, например, рассмотрим таблицу, в которой учитываются продажи. В ней есть три менеджера и три товара, а также общая сумма продажи по каждой сделке.

Нам нужно определить эффективность менеджеров, при этом нужно учитывать определенный товар.

В соответствующих ячейках укажем интересующие нас исходные данные — менеджера и товар.

Решить задачу можно довольно простой формулой массива.

Так как в итоге нужно получить сумму, то будем использовать соответствующую функцию — СУММ. Также мы будем использовать условие, ведь у нас есть критерии. Сейчас я создам формулу, а потом объясню принцип ее работы.

Итак, у нас в функцию суммирования подставлено три множителя.

Первый множитель (A2:A14=G3) позволяет выбрать из столбца с именами менеджеров то, которое мы указали в критериях поиска.

Второй множитель (B2:B14=G4) аналогичным образом позволяет определить необходимый товар.

Ну и третий (С2:С14) выводит соответствующую сумму заказа.

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

Формула массива проходит построчно в указанных диапазонах с именами менеджеров и товарами и проверяет выполнение условий. Если условие выполняется, то в возвращается 1, если же не выполняется, то 0.

Соответственно, произведение трех множителей в случае, если хотя бы одно из двух условий не выполняется будет равно 0. Ну а если выполняются два условия, то мы получаем произведение двух единиц и соответствующей суммы заказа.

Далее все полученные значения суммируются и на выходе мы получаем общую сумму по определенному товару и определенному менеджеру.

Вот так одной простой формулой можно решить довольно сложную на первый взгляд задачу.

Ну и данный отчет можно несколько улучшить, сделав выпадающий список с товарами и менеджерами, чтобы можно было получать быстрый отчет по каждому из них. И о том, как создать такой выпадающий список я расскажу в отдельном видео.