【物料管理精讲】欠料分析：从一级到二级替代件的智能筛选

优秀创作者

全文约1800字；

阅读时间：约4分钟；

听完时间：约8分钟；

在昨天的文章里，我们已经对优先级为一级的物料进行了“欠料”的分析。鉴于欠料的子件数量庞大，这其中既涵盖了唯一组数量的零件——即不可替代的部件，也包括了组数量超过一个的子件——这些是可以被替换的。下一步，我们的任务是筛选出所有这些可替代的子件，进行进一步的评估和判断。

筛选数据

接下来，我们将聚焦于优先级1的“欠料”数据，具体筛选条件为：状态标记为“欠料”，且“组数量”超过1的子件数据。这些筛选出的数据将作为可替代子件的参考标准，用于评估下一个优先级层级（即优先级2）的潜在替代子件。为此，我们需要输入以下的动态数组公式：

=FILTER(P3:X8,(X3#="欠料")*(U3#>1))

公式解释：

FILTER(P3:X8, ...)：从P3到X8的范围内应用过滤条件。

(X3:X8 = "欠料")：确保数据行的“欠料”状态为真。

(U3:U8 > 1)：确认“组数量”大于1。

*：逻辑运算符，表示两个条件需同时满足。

为了将筛选结果转化为独立列的形式，以便于后续处理，可以采用INDEX函数结合上述FILTER公式，如下所示：

=INDEX(FILTER(P3:X8, (X3:X8 = "欠料") * (U3:U8 > 1)), , 1)

=INDEX(FILTER(P3:X8, (X3:X8 = "欠料") * (U3:U8 > 1)), , 2)

...

直到

=INDEX(FILTER(P3:X8, (X3:X8 = "欠料") * (U3:U8 > 1)), , 8)，效果如下图所示：

查询2级子件

由于存在多于两个的组件（即组的数量大于2），而这些组件有对应的替代件，我们的目标是找出所有子组件的可用替代件。为此，我们将在表格的旁边添加一列，并将其命名为“可替代件”。接下来，我们将使用以下动态数组公式进行数据录入：

=LET(A,FILTER(G3:G12,(F3:F12=Z3)*(K3#=AD3)),INDEX(A,MATCH(AA3,A,0)+1))

公式解释：

使用FILTER函数从范围G3:G12中筛选出满足条件的数据：即在F3:F12列中的值与Z3单元格中的父组件相匹配，同时K3列中的值等于AD3单元格中的组数量。将此筛选结果存储为变量A。

利用MATCH函数确定AA3单元格中的子件在变量A中的位置。

最后，通过INDEX函数返回变量A中MATCH所确定的位置下一行的值，这通常代表了该子件的替代件。

转成动态数组

为了确保公式的自动计算，我们需要将静态公式转换为动态数组公式。首先，使用OFFSET函数来锁定Y值：

在AA6单元格中输入=OFFSET(Z3, 0, 1)，这作为子件的查询值，用于确定优先级1的位置，从而判断优先级2的条件；

在AA7单元格中输入=OFFSET(Z3, 0, 4)，这是组数量，作为筛选条件之一。例如，如果该组的数量是3，则筛选将产生三个结果，分别对应优先级1、2和3。

完成这些基础转换后，我们可以编写更复杂的动态数组堆叠公式：

有了上面的转换，就可以写高阶公式了，在下面录入动态数组堆叠公式：

=DROP(REDUCE("",Z3#,LAMBDA(X,Y,VSTACK(X,LET(A,FILTER(G3:G12,(F3:F12=Y)*(K3#=OFFSET(Y,,4))),INDEX(A,MATCH(OFFSET(Y,,1),A,0)+1))))),1)

函数解释：

REDUCE函数循环遍历Z3#中的每个元素，其中X是累积结果，Y是当前处理的元素。

FILTER函数在G3:G12范围内筛选数据，其条件是F3:F12列中的值等于Y（当前元素），并且K3#列中的值等于OFFSET(Y, 0, 4)（组数量）。

LET函数定义局部变量A，它是经过筛选的结果。

INDEX和MATCH组合用来从A中选择与OFFSET(Y, 0, 1)匹配的优先级位置的值。

VSTACK函数垂直堆叠累积结果X和新计算的行。

最后的DROP函数移除第一个元素，因为REDUCE的初始值为""，我们不希望它成为最终结果的一部分。

库存引用

在处理替代零件并确定第二级优先替代子件后，下一步是检查这些替代件的库存是否足够满足需求。为此，我们将参照替代件的库存，并结合第一级物料的欠料信息。通过将库存数值减去需求量，我们可以判断二级物料是否存在“欠料”。

在合适位置录入以下公式：

=XLOOKUP(AF3#,M3:M8,N3:N8,0)+AE3#

如果在计算过程中我们发现结果仍然是负数，这意味着即使已经考虑了一级替代件，物料仍然不足。在这种情况下，我们需要进一步检查是否有更多的替代选项。这时，我们应该关注那些零件组数量大于2的情况，因为这意味着还存在额外的替代件可以使用。

继续第3级的替代，将于明天和大家分享。

最后总结

本文深入探讨了物料管理中欠料问题的多层次解决方案，特别是针对具有替代选项的物料。我们首先回顾了一级欠料分析，随后聚焦于筛选出可替代的子件，这一步骤至关重要，因为它区分了唯一组数量的零件和多组数量的子件，后者提供了替代的可能性。通过运用FILTER函数，我们能够精确地定位到“欠料”状态且组数量超过1的子件，为进一步的替代件查询打下了基础。

接着，文章介绍了如何使用LET, FILTER, INDEX, MATCH, 和 VSTACK函数组合的高级动态数组公式，这一公式不仅能够有效地处理大量数据，还能准确地找到符合优先级2条件的替代件。此外，我们还讨论了如何通过OFFSET函数锁定关键值，以支持更复杂公式的构建。

最后，库存检查环节利用了XLOOKUP函数，它帮助我们评估替代件的库存是否足以弥补一级欠料。若发现库存仍不足以覆盖需求，我们则需继续探索第三级替代方案，这一过程体现了物料管理中的灵活性和策略性。

通过本文的学习，读者可以掌握一系列实用的WPS表格技巧，用以优化物料管理和欠料处理流程，确保供应链的顺畅运行。明天，我们将继续探索第三级替代件的识别与管理，敬请期待。

397 BOM优化实战：从一级子件到缺料计算的系统化攻略

396 【27周总结】欠料计算：从入门到精通，供应链管理的终极指南

395 【实战攻略】库存天数计算：告别缺货困扰，优化供应链效率

394 多产品的精细化排程：按日计算欠料，决胜供应链！

393 欠料运算精进：XLOOKUP与BOM清单的智能协同

392 智能供应链：XLOOKUP巧解欠料谜题，动态公式赋能高效物料管理

391 揭秘PMC欠料计算的艺术：从基础到高阶，打造生产计划金钥匙

390 精粹复盘！古老师26周文章指南：入门到精通的WPS修炼术

389 销售王者速查手册：WPS高级公式助力月末冠军统计