如何用Office365 Excel数组公式按人员独立计算并重置YTD累计值？

问题背景 #

现有包含姓名、月份、月度金额的数据集（人员记录已按月份排序），需在不改变数据顺序的前提下，为每位人员独立计算YTD累计列。当前使用的SCAN公式未按人员重置累计，导致所有人员的YTD值持续累加，不符合需求。原公式如下：

=LET(data,A4:C12, name,CHOOSECOLS(data, 1), amt,CHOOSECOLS(data, 3),
    calc,   LAMBDA(a, v, a + v),
    ytdAmt, SCAN(0, amt, calc),
    resp,   HSTACK(name, ytdAmt),
resp )

使用Excel版本：2304

解决方案 #

方案一：基于SCAN的重置累加逻辑 #

修改SCAN的判断逻辑，通过对比当前行与上一行的姓名，决定是否重置累计值。公式如下：

=LET(
    data, A4:C12,
    name, CHOOSECOLS(data, 1),
    amt, CHOOSECOLS(data, 3),
    prevName, VSTACK("", DROP(name, -1)),
    ytdAmt, SCAN(0, amt, LAMBDA(a, v, 
        LET(rowNum, ROW(v)-ROW(INDEX(amt,1))+1,
            IF(INDEX(name, rowNum)=INDEX(prevName, rowNum), a+v, v)
        )
    )),
    HSTACK(name, ytdAmt)
)

逻辑说明

• prevName：生成一个首行为空、后续与姓名列一致的辅助列，用于对比当前行与上一行的姓名
• SCAN内部通过IF判断：若当前行姓名与上一行相同，继续累加；若不同（切换人员），则以当前金额作为新的累计起点

方案二：基于BYROW的动态范围求和 #

利用MATCH定位当前人员的第一条记录，再对该记录到当前行的金额求和。公式如下：

=LET(
    data, A4:C12,
    name, CHOOSECOLS(data, 1),
    amt, CHOOSECOLS(data, 3),
    ytdAmt, BYROW(amt, LAMBDA(x, 
        LET(rowNum, ROW(x)-ROW(INDEX(amt,1))+1,
            SUM(INDEX(amt, MATCH(INDEX(name, rowNum), name, 0)):x)
        )
    )),
    HSTACK(name, ytdAmt)
)

注意事项

• 需确保数据集已按姓名+月份排序，否则MATCH会匹配到第一个同名记录，导致累计错误

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