如何优化Excel中基于组合列表的查找表求和SOMMEPROD公式？

嘿，我懂你现在的麻烦——用一串SOMMEPROD逐个相加来计算组合值的总和，公式不仅拉得老长，以后要加新的组合项还得手动加公式，太折腾了。下面给你几个适配法语版Excel的优化方案，根据你的组合值存储方式和Excel版本来选：

优化方案1：单个SOMMEPROD搞定单元格内的分隔组合值 #

如果你的组合值是放在单个单元格里（比如C4是用逗号分隔的"值1,值2,值3"），且你用的是Excel 365/2021及以上版本，直接用DIV.TEXTE（法语版的TEXTSPLIT）拆分组合值为数组，再配合SOMMEPROD计算：

=SOMMEPROD(--ESTNUM(EQUIV($B$13:$B$18, DIV.TEXTE(C4, ","), 0)), $C$13:$C$18)

逻辑拆解： #

• DIV.TEXTE(C4, ",")：把C4里的分隔值拆成动态数组，比如{"值1","值2","值3"}
• EQUIV(...)：检查B列的每个值是否在这个数组里，返回匹配位置或错误值
• --ESTNUM(...)：把匹配结果转成1（匹配成功）或0（匹配失败）
• 最后和C列的数值相乘后求和，一步代替原来多个SOMMEPROD相加的效果

优化方案2：针对单元格区域的组合值 #

如果你的组合值是放在连续单元格区域里（比如D4:F4分别是三个要匹配的值），可以用更简洁的SOMME+SOMME.SI.ENS组合：

=SOMME(SOMME.SI.ENS($C$13:$C$18, $B$13:$B$18, D4:F4))

• 新版本Excel（365/2021）直接回车即可；旧版Excel需要按Ctrl+Shift+Enter作为数组公式输入
• 原理是让SOMME.SI.ENS分别计算每个组合值的总和，再用SOMME把这些结果汇总，比一串SOMMEPROD清爽太多

优化方案3：兼容旧版Excel（无DIV.TEXTE） #

如果你的Excel版本比较旧，没有DIV.TEXTE函数，可以用FILTERXML来拆分单个单元格里的分隔值：

=SOMMEPROD(--ESTNUM(EQUIV($B$13:$B$18, FILTERXML("<t><s>"&SUBSTITUE(C4, ",", "</s><s>")&"</s></t>", "//s"), 0)), $C$13:$C$18)

逻辑拆解： #

• SUBSTITUE(C4, ",", "</s><s>")：把逗号替换成XML标签，把C4内容转成类似<t><s>值1</s><s>值2</s></t>的格式
• FILTERXML(...)：解析这个XML字符串，提取出所有<s>标签里的内容，生成数组
• 后续逻辑和方案1一致，实现旧版本Excel的兼容

不管选哪个方案，都能把原来冗长的多公式相加简化成单个公式，以后修改组合值时，只要调整源单元格内容或区域范围就行，不用再手动添加新的SOMMEPROD项，维护起来省心多了！

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