能否创建可双参数查找并汇总多列的自适应Excel公式？

需求说明 #

原始数据表格（A1:E5）：

1    2    3    3    
Soda   1    2    1    0    
Beer   2    0    0    0    
Beer   2    1    2    2      
Soda   0    2    0    2      

需要按饮品类别（行）和列编号（列）动态汇总，得到结果：

1  2  3
Soda  1  4  3
Beer  4  1  4

现有公式灵活性差，无法自动适配原始表格的列标题新增/修改、行类别变更，需手动调整公式。

基础Excel公式解决方案 #

可以用SUMPRODUCT函数实现完全动态的双参数汇总，无需手动修改公式。假设结果表格的行标题（饮品类别）在A8:A9，列标题（编号）在B7:D7，在B8单元格输入以下公式后，向右向下填充即可：

=SUMPRODUCT(($A$2:$A$5=$A8)*($B$1:$E$1=B$7)*($B$2:$E$5))

公式逻辑 #

• $A$2:$A$5=$A8：匹配原始数据中与当前结果行一致的饮品类别
• $B$1:$E$1=B$7：匹配原始数据中与当前结果列一致的编号
• * 等价于逻辑AND，仅当两个条件同时满足时，提取对应单元格的数值，最终由SUMPRODUCT完成求和

适配性说明 #

• 新增/修改列标题：只要原始数据的列标题包含在$B$1:$E$1范围内（若需覆盖整行可改为$1:$1，注意排除无关列），公式会自动识别
• 修改行类别：只要结果行标题与原始数据行标题一致，公式会自动匹配汇总
• 若需更彻底的动态数据范围，可结合INDEX+MATCH定义区域，上述公式已能满足大部分基础场景的自动适配需求

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