Excel中用Lookup Table将唯一产品ID转多配料用量的技术求助

1. 构建正确的映射表（Lookup Table） #

将每个产品ID对应的所有配料和单份用量拆分为独立行，这是实现单ID对应多配料匹配的核心前提。示例结构如下：

注意：禁止将同一产品ID的多种配料合并在同一行，必须保证每个「产品ID+配料」组合单独占一行。

2. 统计各配料总用量 #

假设数据分布如下：

• 订单表在Sheet1，B列为所有订单的产品ID（示例范围B2:B1000，可根据实际数据调整）
• 映射表在Sheet2，A列=产品ID，B列=配料名称，C列=单份用量
• 统计表在Sheet3，A列为需要统计的配料名称（如A2=鸡肉、A3=甜椒等）

方法1：SUMPRODUCT函数（兼容所有Excel版本） #

在Sheet3的B2单元格输入以下公式，下拉填充至所有配料行：

=SUMPRODUCT((Sheet2!$A:$A=Sheet1!$B:$B)*(Sheet2!$B:$B=A2)*Sheet2!$C:$C)

公式逻辑：遍历所有订单的产品ID，匹配映射表中对应配料的单份用量，自动累加所有符合条件的数值。

方法2：动态数组公式（Excel 365/2021及以上） #

如果使用支持动态数组的Excel版本，可自动生成全量统计结果，无需手动下拉：

1. 提取所有唯一配料（在Sheet3的A2单元格输入）：

=UNIQUE(Sheet2!$B:$B)

2. 在Sheet3的B2单元格输入公式，自动溢出所有配料的统计结果：

=SUMIFS(Sheet2!$C:$C,Sheet2!$B:$B,A2#,Sheet2!$A:$A,Sheet1!$B:$B)

其中A2#表示引用A列的动态数组区域，自动匹配每个配料对应的统计规则。

3. 结果验证 #

可手动计算少量订单的配料用量（比如2份BYO4/CHI+3份BYO4/CHI/GUAC的鸡肉总用量应为4*2 + 4*3 = 20），对比公式输出结果，确认映射表的行拆分和公式引用范围无误。

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