Все формулы РФ

Excel и Google Workspace / Формулы Google Таблиц

Google Sheets: ARRAYFORMULA для массовых вычислений

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

Опубликовано: Обновлено:

Google ТаблицыОфисные расчетыСсылки на ячейкиАнализ данныхЕсть историческая справка

Формула

$$=ARRAYFORMULA(IF(B2:B200>0, C2:C200/B2:B200, ""))$$

Обозначения

$array_expression$
выражение, которое применяется к диапазону
$input_range$
исходный диапазон
$output_range$
динамический развернутый результат

Условия применения

  • В целевой колонке не должно быть уже заполненных формулами данных ниже стартовой ячейки.
  • Аргументы должны быть массивами одинаковой высоты.
  • В формуле часто используют IF/IFERROR для защиты от ошибок деления.

Ограничения

  • Не всегда подходит для условно-структурированных таблиц с пустотами и смешанными типами данных.
  • Чрезмерно большие диапазоны могут заметно замедлять пересчёт.
  • Если внутри диапазона есть ручные значения в местах spill-результата, формула перестаёт работать.

Подробное объяснение

ARRAYFORMULA превращает обычную формулу в векторную: все операции применяются поэлементно к массивам и возвращают массив результатов.

Смысл страницы — показать рабочий сценарий, ограничения и поведение формулы в живой таблице. Ее используют для расчета суммы по каждой строке, формирования текстовых ключей, массовых проверок условий и автоматического заполнения новых строк. Для таблиц, которые постоянно пополняются, это экономит время и снижает риск, что формулу забыли протянуть. В отличие от простого перечисления аргументов, здесь важно понимать, как формула меняет диапазон результата, что происходит при пустых строках и как она сочетается с другими функциями Google Таблиц.

Как пользоваться формулой

  1. Вставьте формулу в первую ячейку нужного столбца.
  2. Сформируйте формулу так, чтобы все входные диапазоны имели одинаковые размеры.
  3. Добавьте проверку ошибок по делению на ноль и пустые строки.
  4. Оставьте «свободную» ячейку под весь возможный spill-массив.

Историческая справка

ARRAYFORMULA упрощает переход от «копипастного» подхода к формульной обработке массивов и уменьшает количество служебных формул.

Функция рассматривается как часть экосистемы Google Sheets и Google Docs Editors. Исторический блок здесь полезен не как биография автора, а как контекст появления облачных таблиц, совместной работы и динамических массивов, которые изменили привычный способ строить отчеты.

Историческая линия формулы

Подробная документация по ARRAYFORMULA опубликована в официальной справке Google Sheets. У функций Google Таблиц нет корректного единоличного автора. Их развитие связано с продуктовой эволюцией электронных таблиц, SQL-подобных запросов, динамических массивов и облачной совместной работы, поэтому атрибуция описывает технологическую линию, а не персональное открытие.

Пример

Для столбца B (кол-во) и C (выручка) формула `=ARRAYFORMULA(IF(B2:B200>0, C2:C200/B2:B200, ""))` рассчитает средний чек по всем строкам. Ее используют для расчета суммы по каждой строке, формирования текстовых ключей, массовых проверок условий и автоматического заполнения новых строк. Для таблиц, которые постоянно пополняются, это экономит время и снижает риск, что формулу забыли протянуть.

Частая ошибка

Классическая ошибка — использовать обычную ссылку на одну ячейку вместо диапазона внутри IF/ARRAYFORMULA, из-за чего вторая колонка вообще не заполняется. Главная ошибка — поставить формулу туда, где ниже уже есть данные: динамический результат не сможет развернуться. Также нужно помнить, что не все функции одинаково хорошо работают с массивами без дополнительной обертки.

Практика

Задачи с решением

Процент завершения задач

Условие. В B — выполнено, в C — запланировано.

Решение. =ARRAYFORMULA(IF(C2:C100=0, "", B2:B100/C2:C100))

Ответ. =ARRAYFORMULA(IF(C2:C100=0, "", B2:B100/C2:C100))

Разбивка суммы на 10% и 90%

Условие. В B — сумма чека.

Решение. =ARRAYFORMULA(IF(B2:B100="", "", B2:B100*{0.1,0.9}))

Ответ. =ARRAYFORMULA(IF(B2:B100="", "", B2:B100*{0.1,0.9}))

Дополнительные источники

  • Google Docs Editors Help: ARRAYFORMULA function - https://support.google.com/docs/answer/3093275?hl=en
  • Google Docs Editors Help: IFERROR function - https://support.google.com/docs/answer/3093311?hl=en
  • Google Docs Editors Help: Google Sheets function list - https://support.google.com/docs/table/25273?hl=en
  • Google Docs Editors Help: Google Sheets function list
  • Google Docs Editors Help: function documentation for the corresponding Google Sheets function

Связанные формулы

Excel и Google Workspace

Google Sheets: IFERROR для аккуратного lookup

$=IFERROR(VLOOKUP(A2, A:D, 4, FALSE), "Не найден")$

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

Excel и Google Workspace

Google Sheets: динамические диапазоны с INDIRECT

$=ARRAYFORMULA(SUM(INDIRECT("B2:B" & COUNTA(B:B))))$

INDIRECT даёт гибкость для построения диапазонов из текста. В связке с COUNTA удобно подстраиваться под длину данных. Динамические диапазоны позволяют формуле подстраиваться под растущий список строк или выбранное имя листа.

Excel и Google Workspace

QUERY в Google Таблицах: базовый SELECT

$=QUERY(A1:D100,"select A, C where B = 'Оплачен'",1)$

QUERY выполняет запрос к диапазону Google Таблиц на языке, похожем на SQL. Базовый SELECT выбирает нужные столбцы и строки по условию.