Функции и Формулы

ARRAYFORMULA в Google таблицах, с примерами на реальных проектах

ARRAYFORMULA в Google таблицах, с примерами на реальных проектах
Заменяет множество формул в каждой ячейке на одну единственную

ARRAYFORMULA - функция для массовых выражений в google таблицах

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

На примере функция массивов используется в популярной связке с функцией VLOOKUP (ВПР) и дополнительным условием, обрабатываемым функцией обработки ошибок — IFERROR (ЕСЛИОШИБКА).
Вместо тысяч формул с ВПР, в ячейку F1 вставлена функция работы с массивами — ARRAYFORMULA, которая автоматически расширяется при добавлении новых вводных данных:

={"В раб.часах";ARRAYFORMULA(IFERROR(E2:E/VLOOKUP(L2:L;'Справочник'!H:O;8;0)/24))}

Обратите внимание:

  • Вместо конкретной ячейки, которая обычно указывается в качестве первого искомого аргумента в таблице функции VLOOKUP (ВПР), указан диапазон с ячейками — столбец L2:L (весь столбец, начиная со 2й ячейки).
  • Вместо конкретной ячейки для обработки ошибки, в случае, если функция VLOOKUP (ВПР) не найдет искомый результат — указан столбец L2:L.
  • Вместо конкретной ячейки для математического действия деления — указан столбец E2:E.
  • Чтобы скрыть функцию в район неизменяемой строки с заголовками и защитить формулу от случайного удаления пользователем — используется «хук» с фигурными скобками:
    ={"В раб.часах"; формула }

Т.е., мы указываем функции с какими массивами (столбцами) надо работать. Размер массивов с данными должен быть одинаковым. И помещаем ее в шапку таблицы с заданным заголовком, защищая от случайного удаления пользователем.

ВАЖНО!: на пути у формулы не должно быть заполненных ячеек, иначе она сломается, выдав ошибку #REF! (#ССЫЛ!)

Массовая сцепка строк одной формулой

ARRAYFORMULA, массовая конкатенация строк в google таблицах

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

={"Результат";ARRAYFORMULA(C5:C&" "&D5:D)}

В данном случае, вместо привычного вида функции CONCATENATE (СЦЕПИТЬ) — используется амперсант «&».

Массовое произведение массива на число

Использование функции ARRAYFORMULA при умножении на константу

ARRAYFORMULA часто используется для выполнения математических действий c заданной константой. Константой может быть другая функция (в числовом формате) или просто произвольное число.
На примере ниже произведение данных из ограниченного диапазона D5:D21 на число 2:

={"Результат";ARRAYFORMULA(D5:D21*2)}
Разбиваем дату на составляющие

Функция ARRAYFORMULA и извлечение данных из даты

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

=ARRAYFORMULA(IFERROR(SPLIT(D5:D;".")))

В формуле задействована функция IFERROR, она убирает ошибку — #VALUE!, которая возникает когда SPLIT не находит данные в столбце D5:D (натыкается на пустые ячейки в столбце).

Как подружить ARRAYFORMULA и СУМЕСЛИ

Функция SUMIF и ARRAYFORMULA в гугл таблицах

Чтобы обернуть SUMIF в ARRAYFORMULA и комбинация сработала, нужно применить специальный прием объединения одинаковых массивов (столбцов) и использовать их в качестве единых аргументов:

=ARRAYFORMULA(IF(G4:G="";"";SUMIF(A4:A&B4:B&C4:C;G4:G&H4:H&I4:I;D4:D)))

В формуле задействованы функции:

  • IF (ЕСЛИ) — отрабатывает проверку на заполнение данными в ячейках из столбца G4:G.
  • SUMIF (СУМЕСЛИ) — проводит суммирование по объединенным столбцам с данными, месяцем и годом: A4:A&B4:B&C4:C, с условием по объединенному массиву: G4:G&H4:H&I4:I и сумме по столбцу D4:D.
    Т.е., мы просто объединяем амперсантом (&) данные из одинаковых столбцов с месяцем и годом, составляя сцепленные уникальные аргументы с единой структурой и «меткой» для функции — вот такой вот прием 😉
ARRAYFORMULA с условием отмены по пустой ячейке

Функция ARRAYFORMULA и массовая сумма значений из ячеек

В том случае, если нужно массово сложить 2 числа из двух ячеек по всему диапазону (по двум столбцам) можно применить конструкцию:

={"Сумма";ARRAYFORMULA(IF(D5:D="";"";D5:D+E5:E))}
  • {«Сумма»;комбинация формул} — массив с заголовком, в который помещена комбинация формул массива и определенного условия.
  • ARRAYFORMULA — отрабатывающая массовую сумму чисел по ячейкам из двух столбцов.
  • IF (ЕСЛИ) — функция условия, ссылающаяся на частный случай — если какая-нибудь ячейка из столбца D5:D будет пустой, то ничего не выводить, если будет число — проводить сумму чисел из ячеек Dn и En.
  • D5:D — столбец D, начиная с 5-ой ячейки.
  • E5:E — столбец E, начиная с 5-ой ячейки.

Обратите внимание: обязательно нужно применить условие, по которому функция массива перестанет автоматическую сумму двух ячеек из столбцов (поэтому и применена функция IF (ЕСЛИ)). В противном случае, массив будет продлен просуммирован до 25 000 + строк вниз таблицы: функция ARRAYFORMULA будет суммировать 0+0 из двух столбцов…

ARRAYFORMULA гугл таблицы

Функция ARRAYFORMULA синтаксис и основные положения

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

=ARRAYFORMULA(array_formula) - латинская версия
=ARRAYFORMULA(формула_массива) - русская версия
  • array_formula (формула_массива) — диапазон, математическое действие с одним или несколькими диапазонами одного размера либо функция, результат действия которой размещается в одной, либо более чем одной ячейке.

Обратите внимание: при ограниченном диапазоне ARRAYFORMULA захватывает на 1 строку, либо 1 столбец больше. Если в таблице будет на 1 строку или столбец меньше, чем нужно для отработки функции — массив зациклится и будет добавлять строки и столбцы до максимально возможного значения.

Функция массива часто в приоритете среди других функций при работе с огромными объемами данных (тысячи строк), так как легче внести нужные корректировки в одну формулу в одной ячейке, чем изменять структуру каждой формулы в отдельных ячейках и снова протягивать их до конца столбца на постоянной основе — что здорово экономит время.

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

Обратно ко всем статьям.

Статьи по теме

Комментарии к статье: “ARRAYFORMULA в Google таблицах, с примерами на реальных проектах

  1. Кирилл Решетников:

    Подскажите пожалуйста, у меня есть несколько столбцов с данными. И есть критерии: месяц и год.
    Мне нужно по месяцам и году посчитать сумму.
    Использую функцию SUMIFS, но через массивы не получается протянуть множественную сумму по минимум двум параметрам и по сотне наименований товаров — выдает ошибку.
    Как правильно применить массивы к множественной сумме?

    1. Добрый день, Кирилл.

      Функция массива работает либо с константами, либо с равными по объемам диапазонами с данными.
      Т.е., нужны прямые или косвенные математические условия (сумма/разность/произведение/деление), ссылающиеся на одинаковые массивы, либо на одинаковые сущности этих массивов (на массивы с прогнозируемыми вычислениями).

      В Вашем случае происходит не явное математическое событие. Множественная сумма — это логическое математическое событие, ссылающиеся не только на одинаковые диапазоны, но и на концентрированные критерии (год/месяц), что выдает, естественно, ошибку при использовании функции ARRAYFORMULA в google таблицах.

      В Вашем случае стоит рассмотреть возможности функции SQL запросов QUERY. Через запросы можно конкретно вывести множественную сумму по критериям и «категориям» отбора данных.

  2. Кирилл Решетников:

    Но ведь функция SUMIFS — это и есть же математическая функция. Не понимаю.

    У меня есть диапазон с названиями товаров, мне надо массово показать сумму по каждому товару за месяцы по годам.
    Стало быть не подойдёт функция массива?

    1. Все верно, но функция SUMIFS, повторюсь, выполняет математическую логику по условиям. Эти процессы нельзя зациклить функцией ARRAYFORMULA, так как нет прямого взаимодействия массивов с одинаковыми диапазонами.

      В данном случае, константы = условия-критерии, которые не могут принимать участия в математическом выражении, они лишь — признаки этих условий, по которым происходит многомерная сумма.

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *