如何结合Excel FILTER()与SUM()函数实现动态数据汇总？

核心思路 #

通过LET()封装所有变量（源数据、拼接的唯一标识key、唯一组合列表），结合BYROW()+BYCOL()或MMULT()实现批量精准汇总，无需逐列设置公式，实时计算且无需宏。

具体公式实现 #

步骤1：生成唯一组合（H4单元格） #

先通过UNIQUE()生成颜色+类型的唯一组合（用TEXTJOIN()加分隔符避免拼接歧义）：

=UNIQUE(TEXTJOIN("|",,A2:A100,B2:B100))

注：A列=颜色，B列=类型，可根据实际列调整范围；若数据量动态变化，改用A2:INDEX(B:B,COUNTA(A:A))自动适配行号

步骤2：批量汇总公式（I4单元格） #

方案1：BYROW+BYCOL（直观易读）

=LET(
    srcData, A2:D100,
    srcKey, TEXTJOIN("|",,INDEX(srcData,,1),INDEX(srcData,,2)),
    fltrKey, H4#,
    valuesCol, INDEX(srcData,,3):INDEX(srcData,,4),
    BYROW(fltrKey, LAMBDA(currentKey,
        BYCOL(valuesCol, LAMBDA(col,
            SUM(FILTER(col, srcKey=currentKey, 0))
        ))
    ))
)

• srcData：定义完整源数据范围（包含颜色、类型、数值列）
• srcKey：生成每行的颜色+类型拼接标识（用分隔符避免歧义）
• fltrKey：引用H4的唯一组合动态数组
• 外层BYROW()遍历每个唯一组合，内层BYCOL()遍历每个数值列，通过FILTER()精准匹配对应行后求和，无匹配时返回0

方案2：MMULT（大数据量更高效）

利用矩阵乘法实现批量求和，性能优于嵌套遍历：

=LET(
    srcData, A2:D100,
    srcKey, TEXTJOIN("|",,INDEX(srcData,,1),INDEX(srcData,,2)),
    fltrKey, H4#,
    matchMatrix, --(TRANSPOSE(srcKey)=fltrKey),
    valuesRange, INDEX(srcData,,3):INDEX(srcData,,4),
    MMULT(matchMatrix, valuesRange)
)

• matchMatrix：生成匹配矩阵，TRANSPOSE(srcKey)=fltrKey判断每行key是否匹配唯一组合，--将布尔值转成1/0
• MMULT()通过矩阵乘法直接计算每个唯一组合对应数值列的总和，效率更高

问题排查：原方法错误原因 #

你之前用FILTER()+ISNUMBER()+SEARCH()的问题在于模糊匹配，SEARCH()会识别部分重合的字符串（比如"红|蓝"和"红|蓝绿"会被误判匹配），改用srcKey=currentKey的精确比对才能保证结果正确。

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