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

在在昨天的文章中，我们已经完成了主生产计划（MPS）与物料清单（BOM）的联动展开，成功将每一项生产任务分解为对应的子件毛需求。这一过程清晰地回答了“要生产什么，就需要哪些物料、需要多少”的问题。

然而，毛需求仅是MRP运算的第一步。它并未考虑当前仓库中已有的库存情况，因此无法判断“哪些料已有、哪些料真正短缺”。在实际生产管理中，我们更关心的是——净需求：即在扣除现有库存后，仍需采购或生产的物料数量。

在今天的文章中，我们将正式进入净需求计算阶段，聚焦于第一级物料需求（暂不考虑替代料），结合“库存输出”表中的可用库存数据，进行库存扣减与欠料分析。

我们将解答以下关键问题：

哪些物料库存充足，可直接满足生产？

哪些物料存在缺口？缺多少？

如何自动生成清晰的欠料清单，为采购与计划提供决策依据？

通过本节内容，您将掌握如何从“毛需求”迈向“净需求”，构建一个具备实际指导意义的初级欠料分析系统。

排序子件

在正式计算净需求之前，我们需要对上一阶段生成的第一级毛需求数据进行结构化整理。核心目标是：将相同物料编码的子件集中排列，以便后续高效地与库存数据匹配，并进行统一的库存扣减处理。

为此，我们将通过排序操作，按“子件编码”对毛需求数据进行升序排列，使相同物料连续分布，提升可读性与计算效率。在A2录入动态数组公式：

=SORT(TRIMRANGE(第1级毛需求!A2:I30000),7)

公式说明：

第1级毛需求!A2:I30000：引用毛需求表中的原始数据区域（共9列）；

TRIMRANGE：过滤掉空行或无效数据，确保仅处理有效记录；

SORT(..., 7)：按第7列（即“子件编码”列）进行升序排序（默认顺序）；

结果：返回一个结构完整、按物料编码有序排列的二维数组。

由于SORT 函数返回的是一个完整的二维数组区域，若直接在后续公式中引用特定列（如生产任务、排程数量等），容易因列位置变化导致错误。为提升公式的可维护性与灵活性，我们使用 INDEX 函数将排序后的结果逐列提取，形成独立的字段列。

在对应列中分别输入以下公式：

=INDEX(SORT(TRIMRANGE(第1级毛需求!A2:I30000),7),,1)

=INDEX(SORT(TRIMRANGE(第1级毛需求!A2:I30000),7),,2)

……

提示：只需修改 INDEX 函数的最后一个参数（列号），即可提取对应字段，操作简单、易于复制。

设计价值

数据有序化：按子件编码排序，为后续“按物料汇总需求”和“库存扣减”提供便利；

结构清晰：每列独立引用，避免数组嵌套混乱，提升可读性；

支持动态扩展：结合 TRIMRANGE 与动态数组，自动适应新增生产任务；

便于调试：可逐列检查数据是否正确对齐，降低出错风险。

引用库存

在完成毛需求数据的排序与结构化整理后，下一步是引入仓库的实际库存信息，为计算净需求提供数据支撑。为此，我们将在“第1级净需求”表中新增一列，命名为 “库存”，用于获取每个子件在仓库中的可用现存量。录入公式：

=XLOOKUP(G2#,库存输出!A2#,库存输出!B2#)

公式说明：

G2#：当前表中“子件编码”列的溢出数组；

库存输出!A2:A30000：库存输出表中的“编码”列（查找源）；

库存输出!B2:B30000：对应的“汇总数量”列（返回值）；

累计需求

在引入库存数据后，我们面临一个关键问题：同一子件可能出现在多个生产任务中，导致其在毛需求表中重复出现。例如，编码为A001 的物料可能在生产产品A和产品B时都被用到。

如果直接用单条记录的“子件需求”去扣减库存，会造成库存被多次独立扣减，而实际上，仓库中的库存是全局共享的。因此，在计算净需求之前，必须先对相同子件的毛需求进行累计求和，得到每个物料的总需求量。

为此，我们新增一列，命名为“累计需求”，使用 SCAN 函数实现按子件编码分组累加。：

=SCAN(0,G2#,LAMBDA(X,Y,LET(A,OFFSET(Y,,2),IF(Y=OFFSET(Y,-1,),X+A,A))))

公式说明：

G2#：当前“子件编码”列的溢出数组

SCAN(0, G2#, LAMBDA(X,Y,...))：从上到下遍历每一行，X 为累积值，Y 为当前子件编码

OFFSET(Y,,2)：向右偏移2列，获取当前行的“子件需求”值（假设该列位于编码右侧第2列）

IF(Y = OFFSET(Y, -1,), ...) ：判断当前行的子件编码是否与上一行相同

X + A：若相同，则将当前需求累加到前一个累计值上

A：若不同（即新物料首次出现），则重新开始，仅取当前需求值

设计意义

✅ 避免重复扣减：确保库存只被总需求扣减一次，而非每行都扣；

✅ 支持精准净需求计算：为后续“总需求 - 可用库存”提供准确依据；

✅ 提升分析效率：可在后续配合 UNIQUE 或条件判断，提取唯一子件的净需求汇总表。

注意事项

该公式依赖子件编码已排序（相同编码连续排列），否则累加逻辑会中断。请确保前序步骤中已使用SORT 按子件编码排序；

未完待续……