Применение формул к столбцам с ARRAYFORMULA

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

Google Таблицы предлагают более удобный способ: ARRAYFORMULA. Одна формула в одной ячейке сразу вычисляет весь столбец и автоматически расширяется.

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

Вместо того чтобы 100 раз повторять =A2*B2, Вы записываете одну формулу со ссылками на A2:A и B2:B, а результаты появляются автоматически.

Предположим, в столбце A указано количество, а в столбце B — цена. Чтобы вычислить итог для каждой строки, поместите эту единственную формулу в C2.

Она перемножает каждое значение из A с соответствующим значением из B до самого низа, поэтому ячейки начиная с C3 не придётся изменять.

Обратите внимание на A2:A вместо A2:A100. Форма с открытым концом означает все строки начиная со 2-й, поэтому новые данные включаются автоматически.

В этом и заключается настоящая сила ARRAYFORMULA: таблица готова к появлению будущих данных. Добавьте новый заказ — и итог появится без дополнительных действий.

Диапазоны с открытым концом включают тысячи пустых строк под Вашими данными, поэтому формула пытается вычислять и их, часто показывая 0 или посторонние значения.

Оберните вычисление в IF, которое проверяет, пуста ли строка. Если A пуста, верните пустую строку; в противном случае выполните вычисление.

Полезный приём: включите заголовок столбца в формулу массива, чтобы весь столбец управлялся одной ячейкой. Поместите эту формулу в C1.

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

ARRAYFORMULA предназначена не только для вычислений. Она работает и с текстовыми функциями. Чтобы создать столбец с полными именами из имени и фамилии, объедините два диапазона.

Эта формула одновременно объединяет имя из A с фамилией из B, добавляя между ними пробел, для каждой строки.

Некоторые функции самостоятельно работают с массивами и не требуют ARRAYFORMULA. SUMIF, FILTER, UNIQUE и QUERY уже работают с диапазонами.

ARRAYFORMULA в основном нужна, когда операцию, которая обычно выполняется в одной ячейке (например, *, & или LEFT), требуется применить к каждой строке.

Такие функции, как LEFT, UPPER и TRIM, обычно работают с одной ячейкой. Внутри ARRAYFORMULA они применяются ко всему диапазону.

Так все адреса электронной почты в столбце A переводятся в верхний регистр за один раз. Без обёртки Вы изменили бы только первую ячейку.

Вам не всегда нужно вводить имя функции вручную. В Google Таблицах запишите формулу со ссылками на диапазоны, а затем нажмите Ctrl плюс Shift плюс Enter (на Mac — Cmd плюс Shift плюс Enter).

Google Таблицы автоматически обернут формулу в ARRAYFORMULA. Это быстрый способ превратить протянутую формулу в одну формулу с автоматическим заполнением.

Для поэлементных операций диапазоны должны иметь одинаковую высоту. Сочетание A2:A и B2:B50 может привести к ошибкам или неправильному выравниванию результатов.

Оставляйте оба диапазона открытыми (A2:A и B2:B) или задавайте им одинаковый фиксированный размер. Согласованность обеспечивает выравнивание результата по строкам.

Проверьте свои знания об ARRAYFORMULA.

Вы научились вычислять целые столбцы с помощью одной формулы:

• ARRAYFORMULA применяет операцию ко всему диапазону
• Диапазоны без конечной границы, такие как A2:A, автоматически включают новые строки
• Оберните формулу в IF(A2:A = "", "", ...), чтобы пропускать пустые ячейки
• Она работает с математическими операциями, объединением текста и функциями вроде UPPER
• Сочетание Ctrl/Cmd, Shift и Enter автоматически добавляет эту оболочку

Одна ячейка — целый обновляемый столбец.