Excel中使用SUMPRODUCT、INDEX、MATCH公式计算动态总需求成本

你的工作簿包含两个工作表：

• Capacity 工作表（存储资源单位小时成本）
  
• Demand 工作表（存储资源需求小时数）
  

你需要的总需求成本可根据你的Excel版本选择以下方案实现： #

方案1：Excel 365/2021及以上版本（单公式直接出结果，无需辅助列） #

直接使用SUMPRODUCT搭配XLOOKUP完成逐行匹配求和，公式如下：

=SUMPRODUCT(XLOOKUP(DEMAND!$A$2:$A$100, CAPACITY!$A$2:$A$10, CAPACITY!$B$2:$B$10, 0) * DEMAND!$B$2:$B$100)

使用说明：将公式里的$A$100、$B$100修改为你Demand表实际数据的最大行号即可，公式会自动匹配每个资源对应的小时成本，乘以对应需求小时后汇总所有结果，匹配不到的资源默认按0成本计算，不会干扰汇总结果。

方案2：兼容Excel 2019及更早版本（无需辅助列） #

如果使用的是旧版Excel，可使用数组形式的SUMPRODUCT+INDEX+MATCH组合，公式输入完成后需要按Ctrl+Shift+Enter三键结束触发数组计算：

=SUMPRODUCT(IFERROR(INDEX(CAPACITY!$B$2:$B$10, MATCH(DEMAND!$A$2:$A$100, CAPACITY!$A$2:$A$10, 0), 0) * DEMAND!$B$2:$B$100, 0))

注意：三键结束输入后，公式外侧会自动生成大括号{}，代表数组公式生效。

方案3：辅助列方案（兼容性最强，易排查错误） #

如果担心数组公式出错不好调试，可以先在Demand表的任意空白列（比如C列）逐行粘贴你已经写好的单行成本计算公式，算出每个资源的单独需求成本，最后直接用=SUM(C:C)求和即可得到总成本。 #

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