ARRAYFORMULA в Google Sheets — руководство

Логотип Google Sheets на фоновом изображении с водой

TL;DR

ARRAYFORMULA позволяет применять одну формулу сразу ко всему диапазону ячеек и автоматически расширяется при добавлении данных. Используйте её для уменьшения количества формул, повышения производительности и упрощения поддержки таблиц. В статьях ниже — синтаксис, практические примеры, типичные ошибки, альтернативы и чек-листы для разных ролей.

Зачем использовать ARRAYFORMULA

ARRAYFORMULA — функция Google Sheets, которая возвращает результат в виде диапазона (несколько строк и/или столбцов), а не одиночного значения. Это делает её идеальной для задач, где одна и та же логика должна применяться ко многим строкам: объединение полей, условные метки, вычисления по нескольким столбцам и т.д.

Преимущества:

Меньше явных формул в листе — лучше читаемость и меньше вычислений.
Автоматическое расширение при добавлении строк (динамическая природа).
Простая интеграция с другими функциями (IF, SUM, VLOOKUP, FILTER, QUERY).

Замечание: ARRAYFORMULA работает с диапазонами одного размера; если диапазоны разных размеров — результат будет ошибкой или неожиданным.

Базовое определение

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

=ARRAYFORMULA(formula)

Аргумент formula может быть:

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

Подсказка: если вы уже набрали обычную формулу в ячейке, поставьте курсор между = и именем функции и нажмите Ctrl+Shift+Enter — Google Sheets автоматически обернёт формулу в ARRAYFORMULA.

Примеры использования

1. Простое объединение имён (Simple ARRAYFORMULA)

Обычная по ячейке формула для объединения:

=A2 & " " & B2

С ARRAYFORMULA для диапазона A2:A и B2:B:

=ARRAYFORMULA(A2:A & " " & B2:B)

Если у вас есть заголовок в первой строке, используйте условие, чтобы не объединять заголовки:

=ARRAYFORMULA(IF(ROW(A2:A)=1; "Полное имя"; IF(A2:A=""; ""; A2:A & " " & B2:B)))

Шаги:

Кликните в целевой ячейке для результата.
Введите =ARRAYFORMULA( и затем выражение на диапазонах, например A2:A & “ “ & B2:B.
Закройте скобку и нажмите Enter.

Пример простого ARRAYFORMULA для объединения имён

2. ARRAYFORMULA с функцией IF

Допустим, в B2:B хранится количество на складе, и нужно пометить товары, где остаток меньше порога:

=ARRAYFORMULA(IF(B2:B<25; "order"; "no"))

Если заголовок в первой строке, используйте защиту заголовка:

=ARRAYFORMULA(IF(ROW(B2:B)=1; "Статус"; IF(B2:B=""; ""; IF(B2:B<25; "order"; "no"))))

ARRAYFORMULA с IF для пометки товаров на заказ

3. Математические выражения на диапазонах

Сложение столбцов по строкам:

=ARRAYFORMULA(C2:C + D2:D)

Процентное соотношение:

=ARRAYFORMULA(IF(B2:B=0; ""; A2:A / B2:B))

4. Использование с функциями, возвращающими диапазоны

Некоторые функции (например, TRANSPOSE или FILTER) уже возвращают диапазон. Их можно обернуть в ARRAYFORMULA для комбинирования с другими выражениями:

=ARRAYFORMULA(FILTER(A2:A; C2:C="active") * 1.2)

Когда ARRAYFORMULA не подходит

Диапазоны разной длины. ARRAYFORMULA ожидает согласованные по размеру диапазоны; иначе результат будет некорректным.
Случаи, где каждая строка должна содержать уникальную формулу со ссылками на соответствующие строки (например, сложные смешанные абсолютные/относительные ссылки).
Когда вы хотите уникально форматировать отдельные результирующие ячейки вручную — массивы занимают место результирующего диапазона.

Пример провала: попытка объединить A2:A и C2:C, где C имеет заголовки, пропуски и дополнительные строки внизу — результат будет сдвинут или с ошибкой.

Альтернативные подходы и когда их выбирать

FILTER — удобно, если нужно отфильтровать строки по условию и получить только подходящие.
QUERY — мощный инструмент для агрегации, фильтрации и преобразования данных SQL-подобным языком.
UNIQUE + MAP/ARRAYFORMULA — для обработки уникальных значений.
Apps Script — когда логика слишком сложна или требуется постобработка / внешние API.
Автозаполнение Google Sheets — простой, но ненадёжный при дальнейшем изменении данных.

Короткая рекомендация:

Для простых операций по каждой строке используйте ARRAYFORMULA.
Для фильтрации и сложных агрегаций используйте QUERY или FILTER.
Для автоматизации, которую нельзя выразить формулами, используйте Apps Script.

Лучшие практики и heuristics (эмпирические правила)

Держите один «мастер»-столбец с ARRAYFORMULA вместо множества отдельных формул.
Оборачивайте проверки пустых строк: IF(A2:A=””; “”; …). Это убирает пустые строки внизу.
Избегайте ссылок на целые столбцы (A:A), если возможно — используйте разумный диапазон (A2:A1000), чтобы улучшить производительность.
Не помещайте массив в область, где пользователи будут вручную править отдельные ячейки.
Документируйте поведение массива в заголовке (например, пометка «Результат — не редактировать»).

Производительность и масштабирование

Меньше формул = меньше вычислений. Одна ARRAYFORMULA часто быстрее чем тысячи одинаковых отдельных формул.
Однако сложные выражения внутри ARRAYFORMULA (несколько вложенных VLOOKUP/IMPORTRANGE) всё равно будут нагружать систему.
Если лист стал медленным: профилируйте выражение, разбейте на промежуточные столбцы или используйте QUERY для агрегации.

Типичные ошибки и способ их исправления

Ошибка #REF!: массив не помещается — убедитесь, что справа/внизу достаточно пустых ячеек; удалите пересекающиеся данные.
Несогласованные диапазоны — проверьте, что все используемые диапазоны имеют одинаковую длину.
Неправильное форматирование заголовков — используйте проверку ROW() для исключения заголовков.
Автозаполнение конфликтует с массивом — вручную удалите автозаполненные формулы и оставьте только ARRAYFORMULA.

Чек-листы по ролям

Аналитик:

Проверить, одинаковы ли длины диапазонов.
Добавить обработку пустых значений.
Документировать колонку с массивом.

Бухгалтер/финансы:

Ограничить диапазон (например, до 10000 строк).
Проверить точность вычислений для крайних случаев (деление на ноль).

Операционный сотрудник:

Убедиться, что результат массива не нуждается в ручном редактировании.
Обучить пользователей: «не писать в колонку результатов».

Набор приёмов и сниппеты (cheat sheet)

Обязательная защита заголовка:

=ARRAYFORMULA(IF(ROW(A2:A)=2; "Заголовок"; IF(A2:A=""; ""; <ваше выражение>)))

Сочетание с VLOOKUP (поиск с возвратом для каждой строки):

=ARRAYFORMULA(IF(A2:A=""; ""; VLOOKUP(A2:A; Sheet2!A:B; 2; FALSE)))

Фильтрация значений и умножение результата:

=ARRAYFORMULA(FILTER(C2:C; B2:B="active") * 1.15)

Пример сложной логики с несколькими условиями:

=ARRAYFORMULA(IF(A2:A=""; ""; IF(B2:B>100; "high"; IF(B2:B>50; "medium"; "low"))))

Мини-методология внедрения в проект

Определите, какие колонки можно свести к одной формуле.
Протестируйте формулу на небольшом диапазоне (A2:A20).
Обработайте граничные случаи (пустые строки, заголовки, деление на ноль).
Разверните для полного диапазона и проверьте производительность.
Задокументируйте и обучите пользователей.

Критерии приёмки

Формула корректно обрабатывает пустые строки и заголовки.
Нет #REF! или #VALUE! ошибок при типичных данных.
Производительность приемлема: лист открывается и вычисляется без заметных задержек.
Результат совпадает с эталонным набором тестовых данных.

Тест-кейсы и проверки

Пустой диапазон: ожидание — пустые результаты без ошибок.
Добавление новой строки с данными: ожидание — результат появляется автоматически.
Разные длины диапазонов: ожидание — либо ошибка, либо корректная обработка с понятным сообщением.
Граничные значения (0, большие числа, текст вместо числа): оценить обработку и сообщения об ошибках.

Ментальные модели

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

Decision flowchart

flowchart TD
  A[Нужно применить одну логику ко всем строкам?] -->|Да| B{Данные фильтруются/агрегируются?}
  A -->|Нет| Z[Не использовать ARRAYFORMULA]
  B -->|Нет| C[Использовать ARRAYFORMULA]
  B -->|Да| D{Простая фильтрация?}
  D -->|Да| E[Использовать FILTER или QUERY]
  D -->|Нет| F[Использовать QUERY или Apps Script]
  C --> G[Тестировать и ограничить диапазон]
  E --> G
  F --> G

Короткое резюме и рекомендации

ARRAYFORMULA — мощный инструмент для массовых преобразований строк и столбцов в Google Sheets. Он экономит время, уменьшает количество формул и улучшает читабельность. Тем не менее, важно контролировать размеры диапазонов, обрабатывать пустые значения и тестировать производительность. В сложных случаях QUERY или Apps Script могут быть предпочтительнее.

Важно

Не редактируйте вручную ячейки, занимаемые массивом — вы перезапишите результат.
Всегда документируйте колонки с массивами и добавляйте комментарий в шапке таблицы.

Заметки

Если вы мигрируете в Excel: функция ARRAYFORMULA специфична для Google Sheets; в Excel есть аналогичные возможности через динамические массивы (например, SEQUENCE, FILTER, LET) и комбинации формул.

1‑строчный глоссарий

ARRAYFORMULA — функция Google Sheets, возвращающая диапазон значений на основе выражения.
FILTER — возвращает подмножество строк по условию.
QUERY — SQL-подобная функция для фильтрации и агрегации.

Если хотите, могу подготовить набор готовых шаблонов (CSV/TSV) с типовыми ARRAYFORMULA для ваших реальных таблиц или помочь адаптировать существующие формулы.