May 31

Функция SCAN: нарастающий итог — простой, по каждому году/месяцу или с условием

SCAN — одна из вспомогательных функций LAMBDA, которая позволяет пробегаться по массиву, обращаясь к каждому элементу. И творить всякую магию. Доступно это удовольствие в Google Таблицах и в Excel 365 / Excel Online.

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

Скриншоты в статье сделаны в Excel, в Google Таблицах все будет работать аналогично, вот файлы с примерами из статьи в обоих форматах:

Синтаксис функции SCAN и простой накопленный итог

Синтаксис у функции следующий:

=SCAN(начальное значение; массив; LAMBDA(...))
=SCAN([initial_value]; array; LAMBDA(...))

Последний аргумент — это функция LAMBDA, в которой первый аргумент — это нарастающий итог (накопленный результат) второй — это отдельное значение (на первом шаге — начальное значение, заданное в первом аргументе SCAN, а далее — каждое очередное значение из массива), третий — вычисление:

=SCAN(начальное значение; массив; 
LAMBDA(нарастающий итог; значение; вычисление))

Нарастающий итог и значение в функции LAMBDA можно назвать как угодно — acc и value, итог и значение, x или y, как угодно. Потом мы обращаемся в вычислении (последнем аргументе LAMBDA) ссылаемся на них по этим именам.

Давайте рассмотрим простой пример: мы вычисляем нарастающий итог: первое значение — ноль, обрабатываем мы столбец с выручкой из одноименной таблицы и на каждом шаге прибавляем (вычисление задано в третьем аргументе LAMBDA) к накопленному итогу (это первый аргумент LAMBDA, у нас — итог) очередное значение из массива (второй аргумент LAMBDA, у нас — значение):

=SCAN(0; Выручка[Выручка]; LAMBDA(итог; значение; итог + значение))

Первые три шага тут расписаны подробно в столбцах G-J.

Давайте посмотрим на первые два шага тут.

Шаг 1

Итог = 0 — это начальное значение, первый аргумент функции SCAN (initial_value).

Значение = 1153 — это первое значение в массиве, заданном во втором аргументе функции SCAN (array)/

Результат нашей формулы, заданной в последнем аргументе LAMBDA = 0 (итог) + 1153 (значение) = 1153

Шаг 2

Здесь итог — это 1153, результат вычисления на предыдущем шаге.

Значение = очередное число из массива, 3877

Результат = 1153 + 3877 = 5030.

А так выглядел бы результат работы функции, задай мы первым значением не ноль, а 5 000. Это число прибавилось бы к значению на первом шаге:

Понятно, что для вычисления простого нарастающего итога можно было бы обойтись и какой-то простой формулой, например, такой:

Здесь мы вводим формулу =СУММ(C2:C2) и "закрепляем" первую ячейку (делаем ссылку на нее абсолютной $C$2). Таким образом, при протягивании формулы вниз начало диапазона всегда будет оставаться в C2, а конец — в строке с формулой.

Но этот пример был необходим, чтобы разобраться в синтаксисе функции SCAN. Давайте теперь посмотрим на более интересные случаи.

Накопленный итог в рамках каждого месяца

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

Получается, что нам нужно проверять день у каждой очередной даты.

И если этот день = 1 — возвращать не нарастающий итог, а только значение.

Номер дня можно узнать с помощью функции ДЕНЬ / DAY. Но вот как сослаться на значение (в нашем случае дату в столбце A) за пределами нашего массива? Мы ведь в LAMBDA уже не можем сослаться на одну ячейку так, чтобы это была относительная ссылка, которая будет “протягиваться” с каждым очередным значением. Мы можем оперировать значением из массива (второй аргумент SCAN), но это столбец с выручкой, а не датами.

На помощь придет старая добрая (и очень мощная) функция СМЕЩ / OFFSET, которая позволяет ссылаться на диапазоны, задавая ячейку и отступ от нее на любое количество строк и столбцов.

Например, это ссылка на ячейку B2 (потому что стартуем из A1, отступ по строкам 1, по столбцам 1):

=СМЕЩ(A1; 1; 1)

А это — на A1 (ссылаемся из B2, отступаем от нее на одну строку выше, один столбец левее):

=СМЕЩ(A1; 1; 1)

Четвертый и пятый аргумент СМЕЩ — высота и ширина диапазона, если мы работаем с одной ячейкой, их можно пропустить.

В нашем случае дата в двух столбцах от значения, так что понадобится следующая конструкция:

СМЕЩ(значение; 0; -2)

Номер дня получим функцией ДЕНЬ / DAY:

ДЕНЬ(СМЕЩ(значение; 0; -2))

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

ЕСЛИ(ДЕНЬ(СМЕЩ(значение;0;-2))=1;значение;итог+значение)

Вся наша формула будет выглядеть так:

=SCAN(0;Выручка2[Выручка]; 
LAMBDA(итог;значение;
ЕСЛИ(ДЕНЬ(СМЕЩ(значение;0;-2))=1; значение; итог+значение)))

Аналогично можно было считать нарастающий итог в рамках года — тогда мы бы проверяли номер месяца (МЕСЯЦ / MONTH); в рамках недели — номер дня недели (функция ДЕНЬНЕД / WEEKDAY со вторым аргументом 2 будет возвращать единицу для понедельника) и так далее.

Накопленный итог с условием

Схожим образом будет выглядеть логика, когда мы будем считать итог не по всем подряд строкам, а с каким-то условием. В нашем примере есть столбец "Кто работал" и, допустим, мы хотим считать выручку только в дни, когда администратором был Лемур.

На столбец, который проверяем, сошлемся также через СМЕЩ. Здесь это будет -1 столбец от значения:

ЕСЛИ(СМЕЩ(значение;0;-1)="Лемур";...

(регистр при сравнении через знак “равно” не важен, просто мы обращаемся к коту с уважением даже в формуле. Если же вам нужно совпадение с учетом регистра, используйте функцию СОВПАД / EXACT)

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

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

Тут будет по-другому: если у нас в столбце B Лемур, то мы суммируем итог со значением, а если не он, то оставляем итог, а не берем значение из этой строки. То есть продолжаем накапливать, но не добавляем значение очередного дня:

ЕСЛИ(СМЕЩ(значение;0;-1)="Лемур";значение+итог;итог)

Вся формула будет выглядеть так:

=SCAN(0;Выручка2[Выручка];
LAMBDA(итог;значение;
ЕСЛИ(СМЕЩ(значение;0;-1)="Лемур"; значение+итог; итог)))

Файлы с примерами из статьи:

Рабочая книга Excel

Google Таблица

Материалы по теме:

Мой бесплатный мини-курс на Stepik про новые функции, в том числе про LAMBDA.

Мой курс "Магия Excel" в МИФе (там про все эти и многие другие функции, обновляется регулярно)

SCAN и накопительный итог в Google Таблицах

Расчет среднего чека через SCAN