是否有可计算餐饮行业食材总使用量的Excel公式？

适用表结构前提 #

• 主表（表名可自定义，示例为「产品售卖总表」）：A列为产品名称，B列为统计周期内对应产品的售卖份数，C_{L列依次填写该产品使用的第1}10种食材名称
• 子表共10张，表名固定为「食材1」~「食材10」，每张子表结构统一：A列为产品名称，首行（第1行）为全量食材名称，行列交叉单元格为对应产品使用该位次食材的单份用量
• 汇总表：A列为所有食材名称，B列为对应食材的总使用量计算列

通用计算公式 #

在汇总表B2单元格（对应A2单元格的食材）输入以下公式，下拉即可批量计算所有食材的总用量：

=SUMPRODUCT(
    ('产品售卖总表'!C2:L1000=A2)*
    '产品售卖总表'!B2:B1000*
    N(INDIRECT("'食材"&COLUMN(A:J)&"'!R"&ROW(2:1000)&"C"&MATCH(A2,'食材1'!1:1,0),FALSE))
)

如果所有子表的产品行号和主表产品行号完全对应（即主表第2行是产品A，所有子表第2行也对应产品A），可以使用更简化的版本，计算效率更高：

=SUMPRODUCT(('产品售卖总表'!C2:L1000=A2)*'产品售卖总表'!B2:B1000*N(OFFSET(INDIRECT("'食材"&COLUMN(A:J)&"'!B2"),ROW(2:1000)-2,MATCH(A2,'食材1'!1:1,0)-2)))

注意事项 #

• 公式中1000是主表的最大数据行号，可根据你的实际数据量调整
• 所有表的食材名称、产品名称必须完全一致，不能存在多余空格、别名、大小写差异，否则会匹配失败
• 若使用WPS或Microsoft 365版本，直接回车即可生效；若使用旧版Excel，输入完成后需按Ctrl+Shift+Enter三键触发数组计算

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