4. 原料批次与配方版本关联查询表构建

为什么需要“批次—版本”双向追溯表

配方版本表记录的是“某个配方在某次修订后，各组分用什么原料、什么比例”；原料批次表记录的是“某批原料的到货日期、供应商、检验结果”。两张表原本各管一段，一旦出现成品性能波动或客诉，就需要回答两个方向的问题：

• 正向追溯：V2.1 版配方用了哪些原料批次？
• 反向追溯：批号 RM-2405-B 的原料，被哪些配方版本使用过？

本节用 XLOOKUP（或兼容的 INDEX+MATCH）把两张表按“原料编码”这个唯一键关联起来，生成一张既能按版本查批次、又能按批次查版本的查询对照表。

示例数据结构

假设工作簿中有三张表，均放在同一工作表的不同区域，便于公式引用：

配方版本表 A 列到 F 列示例（节选）：

A列 配方编号 | B列 版本号 | C列 原料编码 | D列 组分含量% | E列 修订日期 | F列 状态
PF-101      | V2.0     | RM-001      | 32.5        | 2024-03-02  | 停用
PF-101      | V2.1     | RM-001      | 30.0        | 2024-05-10  | 生效
PF-101      | V2.1     | RM-002      | 18.0        | 2024-05-10  | 生效
PF-102      | V1.3     | RM-001      | 25.0        | 2024-04-18  | 生效
PF-102      | V1.3     | RM-003      | 40.0        | 2024-04-18  | 生效

原料批次表 H 列到 L 列示例：

H列 原料编码 | I列 批次号   | J列 供应商 | K列 到货日期 | L列 检验结论
RM-001      | RM-2405-B  | 华信化工   | 2024-05-06  | 合格
RM-001      | RM-2404-A  | 华信化工   | 2024-04-11  | 合格
RM-002      | RM-2405-C  | 恒泰新材   | 2024-05-08  | 让步接收
RM-003      | RM-2404-D  | 蓝海精化   | 2024-04-20  | 合格

关联逻辑与关键公式

关联的核心是“原料编码”这一公共键。配方版本表中同一原料编码可能对应多个批次（例如 RM-001 有 RM-2405-B 和 RM-2404-A 两批），因此不能简单一对一匹配，需要先明确取批规则。本节采用“按到货日期取最近一批”的规则，用 MAXIFS 先求出该原料编码的最大到货日期，再用 XLOOKUP 以“编码+日期”组合条件定位批次。

在查询对照表 N2 起逐行填写：

N2: =A2
O2: =B2
P2: =C2
Q2: =IFERROR(XLOOKUP(1,(H$2:H$8=P2)*(K$2:K$8=MAXIFS(K$2:K$8,H$2:H$8,P2)),I$2:I$8,"无匹配批次"),"无匹配批次")
R2: =IFERROR(XLOOKUP(Q2,I$2:I$8,J$2:J$8,"—"),"—")
S2: =IFERROR(XLOOKUP(Q2,I$2:I$8,K$2:K$8,"—"),"—")
T2: =IFERROR(XLOOKUP(Q2,I$2:I$8,L$2:L$8,"—"),"—")

说明：Q 列使用数组乘法构造“编码相同且到货日期等于该编码最大到货日期”的条件，XLOOKUP 查找值 1 命中第一条满足条件的记录，返回对应批次号。若使用不支持动态数组的旧版 Excel，可改为 INDEX+MATCH 配合 Ctrl+Shift+Enter，或先加辅助列 =H2&"|"&K2 再用普通 XLOOKUP 匹配辅助键。

关联关系可视化

结果判读与常见问题

生成对照表后，重点检查三类信号：

• “无匹配批次”：说明配方版本表里的原料编码在批次表中不存在，可能是编码录入不一致（如 RM-001 与 RM001），需先统一编码格式。
• 同一编码多批次：若业务要求全部列出而非只取最近一批，应改用 FILTER 函数返回多行，或把对照表改为“一编码多行”的展开结构。
• 检验结论为“让步接收”：该批次虽可用，但应在对照表中保留标记，便于后续质量追溯时快速定位。

可直接套用的查询对照表字段清单

完成上述步骤后，把 N1:T9 区域转为 Excel 表格（Ctrl+T），即可在筛选器中按版本号或批次号快速切换视角，实现双向追溯查询。