Excel如何根据多条件匹配表格值编写适配动态数据的计算公式

前提约定 #

• 原始数据工作表命名为数据源，四列对应关系为：A列=VAR1维度（取值VAR-1/VAR-2）、B列=VAR2维度（取值VAR-A/VAR-B/VAR-C）、C列=PROD类型（取值PROD-1/PROD-2/PROD-3）、D列=对应数值
• 汇总表结构：A列填写VAR1维度值、B列填写VAR2维度值，C列写入公式返回计算结果

公式实现 #

Excel 365/2021及以上版本 #

直接用数组参数的SUMIFS自动聚合存在的PROD项，无需额外做空值判断：

=SUMIFS(数据源!$D:$D,数据源!$A:$A,A2,数据源!$B:$B,B2,数据源!$C:$C,"PROD-1") + SUMIFS(数据源!$D:$D,数据源!$A:$A,A2,数据源!$B:$B,B2,数据源!$C:$C,{"PROD-2","PROD-3"})/1.7

Excel 2019及更早版本 #

拆分PROD-2、PROD-3的求和逻辑，兼容旧版函数规则：

=SUMIFS(数据源!$D:$D,数据源!$A:$A,A2,数据源!$B:$B,B2,数据源!$C:$C,"PROD-1") + (SUMIFS(数据源!$D:$D,数据源!$A:$A,A2,数据源!$B:$B,B2,数据源!$C:$C,"PROD-2")+SUMIFS(数据源!$D:$D,数据源!$A:$A,A2,数据源!$B:$B,B2,数据源!$C:$C,"PROD-3"))/1.7

适配说明 #

• 缺失的PROD-2/3项，SUMIFS匹配不到会自动返回0，不会触发计算错误，自动只对存在的项求和后除以1.7
• 若需要处理无匹配数据的场景，可在外层嵌套IFERROR函数自定义返回值，示例：

=IFERROR(上述公式,"无对应数据")

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