在Excel Power Query M语言中按Index计算反向累计值

需求说明 #

需要为表格每行计算反向累计值Value_Cum，即求和所有索引大于等于当前行Index的Value值，要求兼容Index重复、未排序的场景。

Excel表格中等价的公式为：=SUM(FILTER([Value];[Index]>=[@Index]))

基础示例 #

输入表格：

输出表格：

复杂场景示例（重复/未排序Index） #

输入表格：

预期输出：

之前错误的尝试 #

使用以下公式添加自定义列时出现错误（空值为错误结果）：

= Table.AddColumn(#"Sorted Rows", "Custom", each List.Sum(List.RemoveFirstN(Source[Value],[Value]-1)))

错误结果表格：

正确实现方案 #

方案1：逐行筛选求和（直观易懂） #

直接模拟Excel的FILTER+SUM逻辑，每行筛选出所有Index≥当前行Index的Value后求和：

= Table.AddColumn(源, "Value_Cum", each List.Sum(Table.SelectRows(源, (r) => r[Index] >= [Index])[Value]))

方案2：预计算分组累计值（性能更优，大数据量推荐） #

如果数据量较大，逐行筛选会重复遍历表格，效率较低。可先按Index降序排序计算累计和，再分组保留每个Index对应的累计值，最后合并回原表：

let
    源 = 你的数据源,
    // 按Index降序排序
    排序 = Table.Sort(源,{{"Index", Order.Descending}}),
    // 添加累计和列
    添加累计和 = Table.AddColumn(排序, "累计和", each List.Sum(List.FirstN(排序[Value], Table.PositionOf(排序, _) + 1))),
    // 按Index分组，取每个Index对应的累计和（同Index行的累计值一致）
    分组 = Table.Group(添加累计和, {"Index"}, {{"Value_Cum", each List.First(_[累计和]), type number}}),
    // 合并回原表
    合并 = Table.NestedJoin(源, {"Index"}, 分组, {"Index"}, "分组", JoinKind.LeftOuter),
    // 提取Value_Cum并移除辅助列
    提取列 = Table.ExpandTableColumn(合并, "分组", {"Value_Cum"}, {"Value_Cum"})
in
    提取列

方案3：使用List.Accumulate构建映射（高效简洁） #

通过一次遍历构建每个Index对应的反向累计值映射，再添加列：

let
    源 = 你的数据源,
    // 获取唯一Index并降序排序
    唯一索引降序 = List.Sort(List.Distinct(源[Index]), Order.Descending),
    // 计算每个Index对应的反向累计值
    累计映射 = List.Accumulate(唯一索引降序, [], (state, current) => 
        let
            当前组值 = Table.SelectRows(源, (r) => r[Index] = current)[Value],
            当前组和 = List.Sum(current组值),
            累计和 = if List.IsEmpty(state) then 当前组和 else state{0}[累计和] + 当前组和
        in
            {{Index=current, Value_Cum=累计和}} & state
    ),
    // 转成表格
    累计表 = Table.FromRecords(累计映射),
    // 合并回原表
    合并 = Table.NestedJoin(源, {"Index"}, 累计表, {"Index"}, "累计表", JoinKind.LeftOuter),
    提取列 = Table.ExpandTableColumn(合并, "累计表", {"Value_Cum"}, {"Value_Cum"})
in
    提取列

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