ARRAYFORMULA

Работа с диапазонами и массивами

Указатель Функций Таблиц Google
Содержимое страницы

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

Все примеры доступны в Таблице, см. раздел Ссылки .

Описание

Функция ARRAYFORMULA предназначена для вывода массивов. Основной интерес она представляет в задачах массивной обработки, исключения ошибок при копировании однотипных формул, в создании массивов из диапазонов.

  • Многие формулы массива автоматически расширяются на соседние ячейки, чтобы избежать явного использования функции ARRAYFORMULA.
  • Одновременное нажатие клавиш [CTRL]+[SHIFT]+[ENTER] во время редактирования формулы автоматически добавляет код ArrayFormula( в начало.
  • Обратите внимание, что формулы массива не могут быть экспортированы.

Примеры

Использование совместно с VLOOKUP

Одна из самых популярных задач - “растянуть” VLOOKUP - замена колонки повторяющихся VLOOKUP формул на одну.

Для одной колонки

  • Колонка с данными для поиска: B4:B5
  • Массив, в котором ищутся данные: 'Данные'!B:D
  • Индекс колонки для вывода значений: 2
=ARRAYFORMULA(VLOOKUP(B4:B5;'Данные'!B:D;2;FALSE))

Для нескольких колонок

  • Колонка с данными для поиска: B9:B10
  • Массив, в котором ищутся данные: 'Данные'!B:D
  • Индекс колонки для вывода значений: 2 и 3
=ARRAYFORMULA(VLOOKUP(B9:B10;'Данные'!B:D;{2\3};FALSE))

Для нескольких колонок, которые идут не подряд

  • Колонка с данными для поиска: B14:B15
  • Массив, в котором ищутся данные: 'Данные'!A:F
  • Индекс колонки для вывода значений: 2, 7 и 5
=ARRAYFORMULA(VLOOKUP(B14:B15;{'Данные'!B:B\'Данные'!A:F};{2\7\5};FALSE))

Использование совместно с SUMIF

Расчет ряда сумм по условию “вдоль” колонки

Сумма для одного условия

Если 'Данные'!F:F совпадают с B4:B6, суммировать 'Данные'!D:D

=ARRAYFORMULA(SUMIF('Данные'!F:F;B4:B6;'Данные'!D:D))

Сумма для условий в нескольких колонках

Если 'Данные'!F:F и 'Данные'!B:B попарно совпадают с B4:B6 и C10:C12, суммировать 'Данные'!D:D

=ARRAYFORMULA(SUMIF('Данные'!F:F&'Данные'!B:B;B4:B6&C10:C12;'Данные'!D:D))

Сумма с преобразованием даты

Расчет суммы за месяц. Если в ячейке B16 месяц даты равен датам из диапазона 'Данные'!C:C и требуется подсчитать сумму 'Данные'!D:D

=SUMIF(ARRAYFORMULA(EOMONTH('Данные'!C:C;1));EOMONTH(B16;1);'Данные'!D:D)

Аналогичный подход с преобразованием данных в текст. Ячейка со значением месяца в B20

=SUMIF(
  ARRAYFORMULA(TEXT('Данные'!C:C;"MMYYYY"));
  TEXT(B20;"MMYYYY");
  'Данные'!D:D)

Построчные суммы/средние арифметические

Существует несколько вариантов расчета построчной суммы с неизвестным (большим) количеством колонок

Построчная сумма перебором сплошного массива

Массив для расчета C2:F18

=ARRAYFORMULA(SUMIF(IF(COLUMN(C1:F1);ROW(A2:A18));ROW(A2:A18);C2:F18))

Построчное среднее арифметическое перебором сплошного массива

Массив для расчета C2:F18

=ARRAYFORMULA(SUMIF(IF(COLUMN(C1:F1);ROW(A2:A18));ROW(A2:A18);C2:F18)/COLUMNS(C1:F1))

Построчная сумма. MMULT

Массив для расчета C2:F18

=MMULT(C2:F18;TRANSPOSE(ARRAYFORMULA(COLUMN(C1:F1)^0)))

Построчное среднее арифметическое. MMULT

Массив для расчета C2:F18

=ARRAYFORMULA(MMULT(C2:F18;TRANSPOSE(ARRAYFORMULA(COLUMN(C1:F1)^0)))/COLUMNS(C1:F1))

Построчная сумма объединенных массивов. MMULT

Массив для расчета {C2:C18\E2:F18}

=MMULT({C2:C18\E2:F18};TRANSPOSE(SPLIT(REPT("1|";COLUMNS({C2:C18\E2:F18}));"|")))

Построчное среднее арифметическое объединенных массивов. MMULT

Массив для расчета {C2:C18\E2:F18}

=ARRAYFORMULA(
  MMULT(
    {C2:C18\E2:F18};TRANSPOSE(SPLIT(REPT("1|";COLUMNS({C2:C18\E2:F18}));"|"))
  )/COLUMNS({C2:C18\E2:F18})
)

Ссылки