Формулы массива в EXCEL, возвращающие несколько значений

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

Начнем сразу с примеров.

СОВЕТ :Начальные знания о формулах массива можно прочитать в статье Формулы массива. Знакомство . О формулах массива, возвращающих одно значение можно прочитать в статье Формулы массива, возвращающие одно значение .

Пример1. Создание таблицы умножения

Решение задачи по созданию таблицы умножения приведено в файле примера .

Для расчета всей таблицы умножения использована только одна формула массива =B4:K4*A5:A14

Чтобы создать таблицу умножения нужно:

• создать строку (см. строка 4 на рисунке выше) и столбец (A) со значениями – множителями (1, 2, 3, …);
• выделить диапазон B 5: K 14 , в котором будет размещены результаты произведения множителей;
• в Строке формул ввести формулу массива =B4:K4*A5:A14 и нажать CTRL+SHIFT+ENTER ;
• все выделенные ячейки автоматически заполнятся результатами перемножения соответствующих множителей из строки и столбца.

Теперь попробуем удалить любой компонент формулы массива. Например, щелкните ячейку E7 и нажмите клавишу DELETE . Появится сообщение: Нельзя изменять часть массива . Для того, чтобы удалить формулу массива, возвращающую множество значений, придется выделить весь диапазон ячеек B 5: K 14 и нажать клавишу DELETE .

Таким же образом можно изменить формулу: выделите весь диапазон ячеек, в Строке формул измените формулу. После этого нужно нажать сочетание клавиш CTRL+SHIFT+ ENTER , чтобы подтвердить изменение формулы.

Очевидно, что такая процедура изменения/ удаления формулы массива снижает риск ее случайного изменения.

Иногда возникает необходимость увеличить диапазон формулы массива. Увеличим размер таблицы умножения до 10х14:

• выделите диапазон ячеек, содержащий текущую формулу массива ( B5:K14 ), а также пустые ячейки ( B15:K18 ), расположенные ниже.
• в Строке формул измените формулу массива на =B4:K4*A5:A18 ;
• нажмите сочетание клавиш CTRL+SHIFT+ ENTER . Формула в ячейках B 5: K 14 будет обновлена, при этом экземпляры новой формулы (и результаты их вычисления) будут вставлены в новые ячейки B 15: K 18 .

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

Пример2. Три наибольших значения

Найдем 3 наибольших значения в списке и выведем их в 3-х ячейках.

Формула массива для решения этой задачи: =НАИБОЛЬШИЙ(A2:A11;{1:2:3})

• выделите 3 ячейки, которые будут содержать 3 наибольших значения.
• в Строке формул введите вышеуказанную формулу массива;
• нажмите сочетание клавиш CTRL+SHIFT+ ENTER .

Выделенные ячейки будут заполнены значениями. Стоит обратить внимание на использованный в формуле массива массив констант {1:2:3}. Элементы разделены двоеточием, что заставляет интерпретировать EXCEL данный массив констант как столбец. Эту задачу можно конечно решить и без формул массива записав в 3-х ячейках 3 различные формулы: =НАИБОЛЬШИЙ(A2:A11;1) =НАИБОЛЬШИЙ(A2:A11;2) =НАИБОЛЬШИЙ(A2:A11;3) Здесь наглядно видно, что формулы массива представляют лишь сокращенную запись группы обычных формул.