Функция 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.
Давайте посмотрим на первые два шага тут.
Итог = 0 — это начальное значение, первый аргумент функции SCAN (initial_value).
Значение = 1153 — это первое значение в массиве, заданном во втором аргументе функции SCAN (array)/
Результат нашей формулы, заданной в последнем аргументе LAMBDA = 0 (итог) + 1153 (значение) = 1153
Здесь итог — это 1153, результат вычисления на предыдущем шаге.
Значение = очередное число из массива, 3877
Результат = 1153 + 3877 = 5030.
А так выглядел бы результат работы функции, задай мы первым значением не ноль, а 5 000. Это число прибавилось бы к значению на первом шаге:
Понятно, что для вычисления простого нарастающего итога можно было бы обойтись и какой-то простой формулой, например, такой:
Но этот пример был необходим, чтобы разобраться в синтаксисе функции 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)="Лемур"; значение+итог; итог)))
Мой бесплатный мини-курс на Stepik про новые функции, в том числе про LAMBDA.
Мой курс "Магия Excel" в МИФе (там про все эти и многие другие функции, обновляется регулярно)