无需修改原有列表的Excel按关联类别汇总金额公式求解

场景说明 #

需要在不修改列表2、列表3原有内容的前提下，通过列表3的物品-类别映射关系，汇总列表2对应类别的总金额，填入列表1的金额列。

涉及列表配置 #

• 列表1（List1）：包含「类别（Category）」列（取值为Fruit、Vegetable）、「金额（Amount）」列，目标公式填入金额列对应单元格
• 列表2（List2）：包含「物品（Item）」列（取值为Apple、Carrot、Banana、Lettuce）、「金额（Amount）」列（对应数值为4、3、5、2）
• 列表3（List3）：物品-类别映射表，包含「物品（Item）」、「类别（Category）」两列，对应关系为Apple→Fruit、Carrot→Vegetable、Banana→Fruit、Lettuce→Vegetable

公式方案 #

以下公式均默认使用Excel结构化引用，如果你使用普通单元格区域，替换对应范围即可。

全版本兼容方案（适配所有Excel版本，无需数组回车） #

公式填入列表1金额列首个数据单元格后下拉即可：
=SUMPRODUCT((List3[Category]=[@Category])*SUMIF(List2[Item],List3[Item],List2[Amount]))
计算逻辑：遍历列表3的所有映射行，匹配当前类别对应的所有物品，再匹配列表2中对应物品的金额求和，最终得到当前类别的总金额。

365/2021及以上版本简化方案 #

=SUM(SUMIFS(List2[Amount],List2[Item],FILTER(List3[Item],List3[Category]=[@Category])))
计算逻辑：先用FILTER函数筛选出当前类别对应的所有物品，再通过SUMIFS批量匹配列表2中对应物品的金额，最后求和得到总金额。

结果验证 #

按上述公式计算可得：

• Fruit类别总金额为4+5=9
• Vegetable类别总金额为3+2=5

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