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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Пример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)

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

Связанные статьи

Похожие задачи
Прочитайте другие статьи, решающие похожие задачи в MS Excel. Это позволит Вам решать широкий класс подобных задач.
Средняя: 5 (7 оценок)