如何用溢出公式实现多列Sumifs及Top X+其余行分组汇总

需求 #

基于溢出公式生成的动态源表（行列数不固定），生成汇总表：

• 展示源表前X行数据
• 最后一行将剩余所有行分组汇总为「Others」
• 确保汇总表总计与源表完全一致

当前问题 #

• 现有公式仅能正确提取前X行数据，无法汇总X行之后的所有行（当前仅提取了ID=6的数据）
• 修改后的公式返回#N/A错误，无法实现多列批量求和
• 现有方案需手动增减公式适配列数，不符合动态变化要求

现有公式 #

仅前X行正常运行 #

=MAKEARRAY( ROWS(I2#), COLUMNS(K1#), LAMBDA(r,c,SUM(C2#*--(A2#=INDEX(I2#,r))*--(C1#=INDEX(K1#,1,c)) )))

返回#N/A的错误版本 #

=MAKEARRAY( ROWS(I2#), COLUMNS(K1#), LAMBDA(r,c,SUM(C2#*--IF(I2#>5,(A2#>=INDEX(I2#,r)),(A2#=INDEX(I2#,r)))*--(C1#=INDEX(K1#,1,c)) )))

修正后的方案 #

1. 动态行标签（生成I2#区域） #

先生成包含前5行ID和「Others」的动态行标签：

=VSTACK(TAKE(A2#, 5), "Others")

2. 核心汇总公式 #

=MAKEARRAY(ROWS(I2#), COLUMNS(K1#), LAMBDA(r,c,
    LET(
        target_id, INDEX(I2#, r),
        IF(
            target_id="Others",
            SUM(C2# * --(A2# > 5) * --(C1# = INDEX(K1#, 1, c))),
            SUM(C2# * --(A2# = target_id) * --(C1# = INDEX(K1#, 1, c)))
        )
    )
))

说明 #

• 用LET定义变量简化逻辑，避免重复引用
• 自动判断行标签：若为「Others」则汇总所有ID>5的行数据，否则匹配对应ID求和
• 公式完全适配源表的行列动态变化，无需手动调整列数

内容的提问来源于stack exchange，提问作者Mark S.