含查找数组的SUMPRODUCT应用：装配体每周组件总价值计算需求

适用Excel 365/2021及以上版本公式 #

在Total Value工作表的B2单元格（对应装配体1、第1周）输入以下公式，然后横向、纵向拖动填充至整个矩阵区域：

=SUMPRODUCT(INDIRECT("'"&$A2&"'!$B$2:$B$100"), XLOOKUP(INDIRECT("'"&$A2&"'!$A$2:$A$100"), CV!$A$2:$A$200, INDIRECT("CV!"&B$1&":"&B$100), 0, 0))

公式各部分说明 #

• INDIRECT("'"&$A2&"'!$B$2:$B$100")：动态引用当前行装配体工作表中的组件数量列（可根据实际数据行数调整$B$100的行号）
• XLOOKUP(...)：精准匹配并提取对应组件的周价值
  • 第一个参数：当前装配体的组件ID列表
  • 第二个参数：CV表中所有组件ID的范围
  • 第三个参数：动态引用当前列对应的周数在CV表中的价值列
  • 最后两个0：启用精确匹配，找不到对应ID时返回0（避免出现错误值）
• SUMPRODUCT：将组件数量与对应周的组件价值一一相乘后求和，得到该装配体本周的总组件价值

旧版Excel（无XLOOKUP支持）替代公式 #

如果使用的是旧版Excel，可在Total Value工作表的B2单元格输入以下公式，再拖动填充：

=SUMPRODUCT(INDIRECT("'"&$A2&"'!$B$2:$B$100"), INDEX(CV!$B$2:$BA$200, MATCH(INDIRECT("'"&$A2&"'!$A$2:$A$100"), CV!$A$2:$A$200, 0), MATCH(B$1, CV!$B$1:$BA$1, 0)))

替代公式说明 #

• MATCH(INDIRECT("'"&$A2&"'!$A$2:$A$100"), CV!$A$2:$A$200, 0)：定位每个组件ID在CV表中的行号
• MATCH(B$1, CV!$B$1:$BA$1, 0)：定位当前周在CV表中的列号
• INDEX(...)：根据行号和列号提取对应组件的周价值
• SUMPRODUCT：完成乘积求和计算总价值

通用注意事项 #

• 调整范围：公式中所有行号限制（如$B$100、$A$200）需根据实际数据量修改
• 混合引用：$A2和B$1为混合引用，拖动时会自动匹配对应装配体和周数
• 空白处理：装配体工作表中的空白行不会影响计算，SUMPRODUCT会自动忽略0值的乘积

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