告别手工算料：动态数组打造智能BOM替代分析 - 5

在在昨天的文章中，我们已成功完成了库存模块的全流程设计，涵盖了《仓库现存量明细表》的输入与输出两个关键环节。通过结构化“库存输入”表，并利用 GROUPBY 等动态数组函数构建具备汇总与筛选能力的“库存输出”表，我们为后续的物料需求分析打下了坚实的数据基础。

至此，BOM替代分析系统的三大核心输入模块已基本搭建完成：

✅ 物料清单（BOM）：定义产品结构与替代关系；

✅ 主生产计划（MPS）：明确生产任务、数量与排程时间；

✅ 库存数据（ST）：提供实时、可参与运算的库存可用性信息。

三大模块协同运作，形成了完整的数据支撑体系。

从今天起，我们将正式进入本系列的核心阶段——物料需求（MRP）模块的设计与实现。接下来的内容将聚焦于如何将BOM展开、MPS计划与库存数据深度融合，自动计算出每个生产任务下的物料需求，并智能判断齐套状态、识别缺料项及可用替代方案。

判断零件数

在基于MPS主生产计划展开物料需求（MRP）的过程中，一个关键步骤是准确获取每个产品所包含的子件数量。这是因为后续的物料需求计算需要知道：每生产一个单位的产品，需要消耗多少种不同的子件（即BOM层级中的“子件顺序”最大值）。

为此，我们可以通过查找引用的方式，从已生成的BOM输出表 中提取对应产品的最大子件序号，该值即代表该产品在BOM结构中的总零件数。录入动态数组公式：

=XLOOKUP(B2#,BOM输出!A2#,BOM输出!B2#,,,-1)

公式解释：

B2# 当前MPS计划中待查的产品编码列表

BOM输出!A2:A30000：BOM输出表中的“产品”列（查找源）

BOM输出!B2:B30000：对应的“子件顺序”列（返回值）

,, 省略查找模式和匹配模式，默认为精确匹配

-1 搜索方向参数：表示从最后一个匹配项开始向前搜索，即返回最后一个出现的；

记录为什么使用 -1？

在“BOM输出”表中，数据按“产品 + 子件顺序”排序，同一产品的子件顺序从1开始递增。因此，最后一个子件顺序即为该产品的最大序号，也即该产品所包含的总子件种类数。使用 XLOOKUP 的倒序搜索功能（-1），可直接获取该最大值，无需额外排序或辅助列。

顺序编号

在将主生产计划（MPS）展开为物料需求（MRP）的过程中，常用的方法之一是筛选判断堆叠法——即通过 FILTER 动态筛选BOM数据，并结合 REDUCE 与 VSTACK 实现逐行堆叠。这种方法的优点是结构灵活、无需额外辅助列；但其缺点也较为明显：计算效率较低，尤其在数据量较大时，由于每次都需要从BOM表中重新查找对应数据，容易导致公式响应变慢。

为了提升运算效率与公式可维护性，本文采用另一种更为高效的设计思路：基于辅助列的引用索引法。

具体做法是：在“MPS输出”表中新增一个辅助列，为每一个生产任务生成一个唯一顺序编号，作为后续关联BOM结构、展开物料需求的索引依据。通过该编号，我们可以实现快速定位与批量引用，避免重复查找带来的性能损耗。

=SEQUENCE(ROWS(A2#))

公式解释：

A2# “MPS输出”表中“生产任务”或“产品”列的动态溢出区域（Spill Range）

ROWS(A2#) 计算当前MPS计划中有效任务的总行数

SEQUENCE(...) 生成一个从1开始、连续递增的数字序列，长度等于任务总数

这个编号将成为我们后续进行 BOM展开、需求复制、优先级匹配 的关键索引字段。

物料清单

在完成MPS计划的“任务序号”索引设计后，我们回到物料清单（BOM）表，为后续的高效关联与快速查找做好准备。为了实现“MPS任务”与“BOM子件”的精准匹配，我们需要在 “BOM输出”表 中建立一个强有力的关联字段——即通过辅助列生成每个子件行的唯一标识符。

在“BOM输出”表中新增一列，命名为 “父件&子件顺序号”，使用 & 符号将“父件”编码与“子件顺序”号进行文本连接，形成结构化的唯一键值。在对应单元格输入以下动态数组公式：

=A2#&"-"&B2#

公式解释：通过文本连接的方式，将“父件”编码与“子件顺序”号结合，生成唯一标识符。该唯一标识可作为“外键”与后续展开的需求表进行精确匹配；替代复杂嵌套查找：避免使用多条件 FILTER 或数组运算带来的性能损耗；

未完待续……