создать текущий итог, который сбрасывается при определенных условиях

Содержание

Вопрос или проблема
Ответ или решение
Шаги для создания формулы
Разбор формулы
Пример использования
Заключение

Вопрос или проблема

Как я могу объединить LAMBDA и SCAN, чтобы создать накопительный итог, который сбрасывается при определенных условиях в Excel?

Я экспериментирую с функциями LAMBDA и SCAN в Excel 365 и хочу создать формулу, которая рассчитывает накопительный итог для столбца чисел, но сбрасывает общий итог каждый раз, когда выполняется определенное условие (например, когда значение в другом столбце равно нулю).

Пока что мне удалось создать базовые накопительные итоги с помощью SCAN, но я испытываю трудности с тем, как динамически сбрасывать накопительный итог на основе условия. Я бы хотел избежать вспомогательных столбцов, используя при этом одну формулу. Вот упрощенная версия того, что я пытаюсь сделать:

Столбец A содержит случайные числа.
Столбец B содержит маркеры (0 или 1), при которых сумма должна сбрасываться (например, сбрасывается, когда B = 0).
Существует ли способ интегрировать логику накопительного итога и условный сброс в одну формулу LAMBDA + SCAN? Есть ли предложения, как к этому подойти?

=SCAN(0, A2:A6, LAMBDA(acc, x, IF(B2:B6=0, 0, acc + x)))

Передайте номер строки и используйте INDEX:

=SCAN(0,ROW(A1:A30),LAMBDA(acc,x,IF(INDEX(B:B,x)=0,0,acc+INDEX(A:A,x))))

На SO вы можете найти много решений с использованием необходимой функциональности:

=LET(arr,Am:Bn,cntr,SEQUENCE(ROWS(arr)),
  SCAN(0,cntr,LAMBDA(acc,x,IF(INDEX(arr,x,2)=0,0,acc+INDEX(arr,x,1))))
)

Этот подход позволяет обращаться к любым массивам, а не только к диапазонам.

Обратите внимание на TRUE ветвь IF. В формуле выше значения в строках сброса теряются. Чтобы подавить этот эффект, используйте измененную формулу:

=LET(arr,Am:Bn,cntr,SEQUENCE(ROWS(arr)),
  SCAN(0,cntr,LAMBDA(acc,x,IF(INDEX(arr,x,2)=0,INDEX(arr,x,1),acc+INDEX(arr,x,1))))
)

Этот пример демонстрирует, как работает формула с динамическим массивом(ами):

=LET(arr,HSTACK(RANDARRAY(10,1,0,100),RANDARRAY(10,1,0,10,TRUE)),cntr,SEQUENCE(ROWS(arr)),
  sumrace,SCAN(0,cntr,LAMBDA(acc,x,IF(INDEX(arr,x,2)=0,INDEX(arr,x,1),acc+INDEX(arr,x,1)))),
  HSTACK(arr,sumrace)
)

Логика очень похожа на уже представленную, не ворую ее.

Когда B = 0, сбросить на 0, игнорировать A	Сбросить итог на A
`=SCAN(0,A3:A12,LAMBDA(acc,a, INDEX(B:B,ROW(a),1) * (acc + a)))`	`=SCAN(,A3:A12,LAMBDA(acc,a, INDEX(B:B,ROW(a),1) * acc + a))`

Ответ или решение

Чтобы создать динамическое накопительное значение с сбросом по определённым условиям в Excel с помощью функций LAMBDA и SCAN, вы можете воспользоваться следующим подходом. Мы будем использовать функцию SCAN для вычисления общего итога по значениям в одном столбце и сбрасывать его, когда в другом столбце будет выполняться заданное условие (например, когда значение равно нулю).

Шаги для создания формулы

Подготовка данных: Убедитесь, что у вас есть два столбца: первый столбец (например, A) содержит числа, для которых мы хотим рассчитать накопительную сумму, а второй столбец (например, B) содержит индикаторы (0 или 1), при которых сумма должна сбрасываться.
Использование функции SCAN: Функция SCAN позволяет нам накапливать значения. Она проходит по массиву и возвращает результирующий массив, состоящий из промежуточных результатов вычислений.
Создание пользовательской функции LAMBDA: Используйте LAMBDA для задания логики накопления и сброса. Ваша формула может выглядеть следующим образом:

=LET(data, A2:A6, markers, B2:B6, ROWS(data), 
    SCAN(0, SEQUENCE(ROWS(data)), LAMBDA(acc, x, IF(INDEX(markers, x) = 0, 0, acc + INDEX(data, x))))
)

Разбор формулы

LET: Эта функция делит вашу формулу на более мелкие части, что облегчает чтение. В данном случае мы используем переменные data для значений из столбца A и markers для значений из столбца B.
SCAN: Начинаем с начального значения 0 и последовательно идем по индексам значений из столбца A через SEQUENCE. LAMBDA принимает два аргумента: аккумулятор (аккумулирующее значение) и текущий индекс.
Условие сброса: Внутри LAMBDA мы проверяем, равно ли текущее значение из столбца B (через INDEX(markers, x)) нулю. Если да, то аккумулятор сбрасывается в 0. Если нет – добавляет текущее значение из столбца A (через INDEX(data, x)) к аккумулятору.

Пример использования

Предположим, ваши данные находятся в следующем виде:

A	B
10	1
20	1
30	0
40	1
50	1

Когда вы примените формулу к этому диапазону, результат будет следующим:

A	B	Сумма
10	1	10
20	1	30
30	0	0
40	1	40
50	1	90

Заключение

С помощью функций LAMBDA и SCAN в Excel 365 вы можете создать мощные и гибкие формулы для подсчета накопительных сумм, которые могут сбрасываться в зависимости от условий. Это позволяет избежать использования вспомогательных столбцов и делает вашу таблицу более организованной. Надеюсь, это поможет вам в вашей задаче!