Excel и Google Workspace / Формулы Google Таблиц
ARRAYFORMULA для расчета целого столбца в Google Sheets
ARRAYFORMULA применяет выражение B2:B10*C2:C10 сразу ко всем строкам диапазона. В результате Google Sheets выводит столбец произведений: цена умножается на количество для каждой строки.
Формула
Обозначения
- $B2:B10$
- первый диапазон множителей, например цена
- $C2:C10$
- второй диапазон множителей, например количество
- $B2:B10*C2:C10$
- поэлементное умножение значений одной строки
Условия применения
- Диапазоны B2:B10 и C2:C10 должны иметь одинаковую высоту.
- Область вывода должна быть пустой ниже ячейки с формулой.
- Если есть пустые строки, часто добавляют IF, чтобы не показывать нули в результате.
Ограничения
- ARRAYFORMULA не превращает любую обычную функцию в массивную, если сама функция не поддерживает работу с диапазонами.
- Ошибки в одной строке могут появиться в соответствующей строке результата.
- При открытых бесконечных диапазонах вроде B2:B формула может считать лишние строки и замедлять лист.
Подробное объяснение
ARRAYFORMULA сообщает Google Sheets, что выражение нужно вычислять не для одной ячейки, а для массива значений. В этой формуле массивы берутся из двух столбцов.
Операция умножения выполняется по строкам. Значение B2 умножается на C2, B3 на C3, и так до конца заданного диапазона.
Результат выводится как динамический столбец. Если исходные значения изменятся, соответствующая строка результата пересчитается автоматически.
Для таблиц, куда данные поступают из Google Forms или импорта, это особенно удобно. Формула стоит один раз в верхней ячейке и обслуживает весь расчетный столбец.
На практике часто используют вариант с проверкой пустых строк: =ARRAYFORMULA(IF(B2:B="",,B2:B*C2:C)). Так отчет остается чистым и не показывает нули там, где данных еще нет.
Для открытых диапазонов вроде B2:B*C2:C часто добавляют IF, чтобы пустые строки не заполняли весь столбец нулями или ошибками. Например, можно проверять, заполнена ли цена, и только после этого умножать. Это особенно важно для таблиц из Google Forms, где новые строки появляются автоматически.
Как пользоваться формулой
- Выберите ячейку в верхней части расчетного столбца.
- Укажите диапазоны одинаковой высоты.
- Запишите выражение, которое должно выполняться по строкам.
- Оставьте место ниже для вывода массива.
- Добавьте IF для пустых строк, если используете открытые диапазоны.
Историческая справка
ARRAYFORMULA отражает одну из сильных сторон Google Sheets: умение работать с диапазонами как с динамическими массивами.
До таких функций авторы таблиц копировали формулу вниз вручную или полагались на автозаполнение, которое легко ломалось при новых строках.
В облачных таблицах, связанных с формами и импортами, одна массивная формула часто надежнее десятков повторяющихся формул в строках.
Современные Excel и Google Sheets постепенно сблизились в поддержке динамических массивов, но ARRAYFORMULA остается характерным и узнаваемым инструментом Google Sheets.
Массивные формулы изменили способ сопровождения рабочих таблиц. Вместо сотен копий одной формулы в строках можно хранить одно выражение в верхней ячейке, что снижает риск случайного удаления или расхождения формул между строками.
Пример
Дано: B2:B5 содержит цены 120, 340, 80, 150. C2:C5 содержит количества 3, 2, 10, 4. Формула =ARRAYFORMULA(B2:B5*C2:C5) возвращает четыре результата: 360, 680, 800, 600. Каждый результат соответствует произведению значений в той же строке. Проверка: если добавить пятую строку с ценой 200 и количеством 5, диапазон B2:B5*C2:C5 нужно расширить до B2:B6*C2:C6 или использовать открытые диапазоны с защитой от пустых строк. Тогда появится новый результат 1000. Если один из столбцов содержит текст вместо числа, соответствующая строка даст ошибку или неожиданный результат.
Частая ошибка
Частая ошибка: поставить формулу в столбец, где ниже уже есть значения, и получить ошибку разлива. Вторая ошибка: использовать диапазоны разной длины. Третья ошибка: применять ARRAYFORMULA к функции, которая ожидает одно значение и не умеет корректно обрабатывать массив. В рабочих таблицах также часто забывают скрывать результаты для пустых строк через IF.
Практика
Задачи с решением
Посчитать суммы строк
Условие. B2:B4 содержит 100, 250, 80. C2:C4 содержит 2, 4, 5. Нужно получить сумму по каждой строке.
Решение. Формула =ARRAYFORMULA(B2:B4*C2:C4) умножит значения построчно: 100*2, 250*4, 80*5.
Ответ. 200, 1000, 400
Скрыть нули в пустых строках
Условие. Используется открытый диапазон B2:B*C2:C, но ниже есть пустые строки. Нужно не показывать там нули.
Решение. Добавьте проверку первого столбца: =ARRAYFORMULA(IF(B2:B="",,B2:B*C2:C)). Если B пустая, результат тоже пустой.
Ответ. Используйте IF внутри ARRAYFORMULA
Дополнительные источники
- Google Docs Editors Help: ARRAYFORMULA function - https://support.google.com/docs/answer/3093275?hl=en
- Google Docs Editors Help: Google Sheets function list - https://support.google.com/docs/table/25273?hl=en
- Dan Bricklin: VisiCalc and the origins of spreadsheets - https://www.bricklin.com/visicalc.htm
Связанные формулы
Excel и Google Workspace
FILTER в Google Sheets с несколькими условиями
FILTER возвращает только те строки диапазона A2:D20, где статус в B2:B20 равен "Оплачен", а сумма в D2:D20 больше 1000. Результат разливается на лист как динамический массив.
Excel и Google Workspace
UNIQUE в Google Sheets для списка без повторов
UNIQUE возвращает список уникальных значений из диапазона A2:A100 в том порядке, в котором они впервые встречаются. Функция помогает быстро получить справочник клиентов, товаров, городов или категорий из длинной таблицы.
Excel и Google Workspace
SORT в Google Sheets для сортировки диапазона
SORT возвращает строки диапазона A2:D20, отсортированные по четвертому столбцу в порядке убывания. Исходная таблица не меняется, а результат выводится как отдельный динамический массив.
Excel и Google Workspace
QUERY в Google Sheets с условием where
QUERY выбирает из диапазона A1:D20 только столбцы A и D для строк, где столбец B равен "Оплачен". Последний аргумент 1 сообщает функции, что в исходном диапазоне есть одна строка заголовков.