如何在现有13个月求和Excel公式中实现负值转0逻辑

我需要实现一个功能：选择一个月份后，计算该日期起往前13个月的累计总和，但累计过程要遵循特定规则：从最远的月份开始累加，若累计值为负，遇到第一个正值时先把累计值置0再相加；后续如果再出现负值，累计值再次置0，以此类推。

目前我已经用下面的公式实现了基础的13个月求和，但不知道怎么添加上述重置规则，也不确定直接套MAX(0, 原公式)是否符合要求：

=LET(
     dates,MAP($A$5:$A$23,LAMBDA(a,EOMONTH(a,0))),
     income, $B$5:$B$23,
     lost_inventory,$C$5:$C$23,
     select_month,EOMONTH($D$2,0),
     last_month, EOMONTH(select_month,-13),
     net,income-lost_inventory,
     SUM(FILTER(net,(dates<=select_month)*(dates>=last_month))))

示例表格 #

解决方案 #

直接套MAX(0, 原公式)不符合需求，它只会对最终结果取0，不会在累加过程中执行重置逻辑。要实现你需要的累加规则，需使用SCAN函数逐行计算带重置的累计值，最终取累计结果的最后一项：

修改后的公式如下：

=LET(
     dates,MAP($A$5:$A$23,LAMBDA(a,EOMONTH(a,0))),
     income, $B$5:$B$23,
     lost_inventory,$C$5:$C$23,
     select_month,EOMONTH($D$2,0),
     last_month, EOMONTH(select_month,-13),
     net,income-lost_inventory,
     filtered_data,FILTER(HSTACK(dates,net),(dates<=select_month)*(dates>=last_month)),
     sorted_data,SORT(filtered_data,1,1), // 按日期从远到近排序
     sorted_net,INDEX(sorted_data,,2),
     running_total,SCAN(0,sorted_net,LAMBDA(acc,val,
         IF(acc<0,
             IF(val>0,val,acc+val),
             MAX(acc+val,0)
         )
     )),
     INDEX(running_total,ROWS(running_total))
)

公式说明：

1. 先筛选出目标13个月的日期和对应净收支值，再按日期从远到近排序，确保累加顺序正确
2. 使用SCAN函数逐行计算累计值：
   • 若当前累计值为负，遇到正值时直接用该正值作为新的累计起点；遇到负值则继续累加
   • 若当前累计值非负，累加后结果为负则置0，否则保留累加结果
3. 最后取SCAN计算出的累计序列的最后一个值，即为符合规则的最终累计总和

内容的提问来源于stack exchange，提问作者YellowTextOnly