Excel works!

Формула массива Excel — это формула, при использовании которой происходит работа с диапазонами ячеек (не отдельно с ячейками), именуемыми как раз массивами. Если массив одномерный т.е. выделен только столбец или строку, то такой массив называют вектором, если двухмерный (несколько столбцов и строк), то массив называют матрицой.

Какие преимущества можно получить при использовании формул массива?

Когда не помогают другие возможности Excel, приходится прибегать к формулам массива (например, транспонирование формулой).

Итак, начнем с самих массивов. Объясняя простым языком, массив представляет собой набор данных, обрабатываемых в Excel одним параметром (аргументом).

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

Пример 1. Формула массива Excel

Имеются два диапазона чисел. Нам требуется получить сумму произведений диапазонов. Обычный пример можно реализовать так: добавляется дополнительный, третий по счету столбец (либо строчка), после чего получается произведение и делается суммирование всего столбца.

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

=СУММ()

После этого определим аргументы, представляющие сумму этого массива в виде произведения из двух массивов, таким образом:

=СУММ(Диапазон1*Диапазон2)

В нашем случае

=СУММ(A1:A10*B1:B10)

В завершении нажимаем одновременно клавиши в следующей комбинации: CTRL+Shift+Enter

Нажатие этого сочетания и означает запуск Формулы массива! Конечная формула будет заключена в фигурные скобки {}

{=СУММ(A1:A10*B1:B10)}

Нюанс. Имеется специальная функция, =СУММПРОИЗВ(), предназначенная для получения суммы сразу всех произведений в массиве.

Пример №2. Практическое применение

Требуется определить сумму 1-го, 3-го и 10-го наибольших элементов из набора данных (диапазона B1:B10). Не прибегая к ФМ это делается так: сортируем по убыванию таблицу, воспользуемся дополнительным столбцом, проставляем в нем порядковые номера и извлекаем сумму при помощи функции =СУММЕСЛИ().

Но формулой массива все делается проще:

=СУММ(НАИБОЛЬШИЙ(B1:B10;{1;3;10}))

1. Здесь функция =НАИБОЛЬШИЙ() осуществляет поиск по убыванию элементов, которые были заданы (в нашем случае это 10, 8 и 1) в диапазоне данных B1:B10

2. Суммирование данного массива происходит с использованием известной функции =СУММ().

3. CTRL+Shift+Enter, не забываем

Удобно!

Пример файла

И напоследок еще раз обозначу  условия использования формул массива

— завершаем ввод формулы комбинацией клавиш вида Ctrl+Shift+Enter;

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