ФОРМУЛЫ МАССИВА
Формула массива — это формула, обрабатывающая (перемножающая, суммирующая и т. п.) несколько массивов (то есть наборов значений).
Практически любой диапазон больше одной ячейки — это массив.
Если в Excel формула массива вводится сочетанием клавиш Ctrl + Shift + Enter и после этого отображается в фигурных скобках, то в Google Таблицах для формул массива есть специальная функция ARRAYFORMULA. Ее единственный аргумент — это формула массива. По сути, это просто другой формат записи: вместо фигурных скобок вы вводите свою формулу внутри функции ARRAYFORMULA.
ПЕРЕМНОЖЕНИЕ НЕСКОЛЬКИХ ДИАПАЗОНОВ
С помощью формулы массива можно рассчитать общую стоимость из многих строк, не перемножая в каждой строчке цену и количество проданных товаров. Исходная таблица этого примера выглядит так:
Если бы работали с каждой строчкой отдельно, то формула выглядела бы так:
=B2*C2
(произведение цены и количества по строке 2).
Мы протянули бы ее на все строки и затем считали бы сумму этих произведений:
Вместо этого мы будем перемножать сразу диапазоны и затем суммировать произведения:
SUM(B2:B12*C2:C12)
СУММ(B2:B12*C2:C12)
Так как это формула массива, то нам нужно использовать ее в качестве аргумента ARRAYFORMULA:
=ARRAYFORMULA(SUM(B2:B12*C2:C12))
=ARRAYFORMULA(СУММ(B2:B12*C2:C12))
И тогда мы можем рассчитать общую сумму без промежуточных вычислений:
VLOOKUP (ВПР) В ФОРМУЛЕ МАССИВА: ПОДТЯГИВАЕМ СРАЗУ НЕСКОЛЬКО СТОЛБЦОВ С ДАННЫМИ
Такая формула VLOOKUP подтянет из исходной таблицы данные сразу из нескольких столбцов, а ввести ее придется только однажды:
=ARRAYFORMULA(VLOOKUP($F$2;$A1:$D13;{2\3\4};0))
=ARRAYFORMULA(ВПР($F$2;$A1:$D13;{2\3\4};0))
В данном примере мы из исходной таблицы $A1:$D13 получаем данные из столбцов 2, 3 и 4 (их вводим как массив в фигурных скобках, разделяя обратной косой чертой: {2\3\4}).
Всю функцию VLOOKUP вводим в качестве аргумента функции ARRAYFORMULA:
В ячейки H2 и I2 вводить ничего не нужно. Достаточно нашей формулы в G2.
ПРОДОЛЖЕНИЕ ПРЕДЫДУЩЕГО ПРИМЕРА: ПОЛУЧАЕМ НОМЕРА СТОЛБЦОВ АВТОМАТИЧЕСКИ
Усложним и автоматизируем функцию из предыдущего примера. Заменим массив с номерами столбцов (в нашем примере {2\3\4}) функцией MATCH (ПОИСКПОЗ), которая будет определять номера столбцов автоматически:
MATCH(заголовки, по которым нужны данные;заголовки в исходной таблице;0)
Или в нашем случае:
MATCH($G$1:$I$1;$A$1:$D$1;0)
ПОИСКПОЗ($G$1:$I$1;$A$1:$D$1;0)
Благодаря тому, что функция MATCH находится внутри формулы массива, она работает с аргументом $G$1:$I$1 — целым диапазоном и возвращает массив с номерами столбцов в исходной таблице.
Вместе это будет выглядеть так:
=ARRAYFORMULA(VLOOKUP(F2;$A1:$D;MATCH($G$1:$I$1;$A$1:$D$1;0);0))
=ARRAYFORMULA(ВПР(F2;$A1:$D;ПОИСКПОЗ($G$1:$I$1;$A$1:$D$1;0);0))
В чем преимущества этого способа по сравнению с предыдущим вариантом?
- Не нужно смотреть, под какими номерами в таблице находятся те или иные поля — все работает автоматически.
- Можно поменять заголовки и подтягивать другие данные или в другом порядке, никак не меняя формулу; она становится универсальной:
Достаточно поменять названия столбцов, и та же самая формула возвращает данные уже в другом порядке.
