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

优秀创作者

全文约2400字；

阅读时间：约6分钟；

听完时间：约13分钟；

在昨天的讨论中，我们深入研究了包含零件替代选项的物料清单（BOM）版本计算需求，并确定了三个核心考虑因素：标准化零件的分类、替代零件的优先排序，以及零件的总数目。一旦掌握了这些关键数据，我们就能启动“缺料分析”的流程。该流程的核心在于根据替代零件的优先级依次计算需求。具体而言，我们首先处理优先级为一级的替代零件，随后是二级，以此类推。在每个阶段，我们都必须从现有库存中准确地扣除相应数量的零件。

筛选一级

在进行“缺料”计算之前，若默认的第一级零件已足够满足主生产计划（MPS）的需求，则无需进一步计算后续级别的替代零件。因此，我们的首要任务是筛选出所有优先级为第一级的子件。为此，在适当的位置输入以下动态数组公式：

=FILTER(E3:J12,I3#=1)

公式解释：

此公式将筛选出表格区域E3:J12 中所有优先级为1的行。为了将结果转换为独立的列，分别输入以下公式：

=INDEX(FILTER(E3:J12,I3#=1),,1)

=INDEX(FILTER(E3:J12,I3#=1),,2)

以此类推直到

=INDEX(FILTER(E3:J12,I3#=1),,6)

确定子件

在输出一级子件的详细信息后，我们需要根据一级需求进一步引用MPS产品计划中父件的需求。产品需求应按照BOM中子件的数量进行扩展，也就是说，子件有多少个，就相应地扩展多少次。

在MPS产品计划旁边增加一列，并将列标题命名为“零件数”。在下方输入动态数组公式：

=XLOOKUP(B3:B4,F3:F12,I3:I12,,,-1)

公式解释：

查找值：产品计划中的产品。

查找范围：BOM中相应的产品列。

返回范围：BOM中相应的零件组。

未找到时返回：空白。

匹配方式：默认。

搜索模式：-1，表示从最后一个项目开始向第一个项目搜索，这样可以确保找到的数字是最大的，即代表零件数。

展开需求

有了上面的零件需求后，就可以直接进行BOM展开需求运算了，在筛选后的一级优先子件明细后增加一列，标题命名为“子件需求”，录入以下公式：

=TEXTSPLIT(CONCAT(REPT(C3:C4&"#",D3#)),,"#",1)*R3#

公式解释：

此公式目的是将一系列数字产品需求（30和20）重复一定次数（3次），然后将它们连接起来，拆分成一个数组，并保持数组中每个元素的值不变。最终的结果是一个包含6个元素的数组，每个元素都是30或20。

REPT函数：REPT函数用于重复文本一定次数。在这个函数中，它被用作数组公式，为每个指定的数字重复一个文本字符串（由井号 “#” 和分号 "; " 分隔的数字）。REPT({30;20}&"#",{3;3})将生成一个数组，其中包含重复30个 “#” 字符串3次和重复20个 “#” 字符串3次的结果。

CONCAT函数：CONCAT函数用于连接一个或多个文本字符串。在这个函数中，它将REPT函数生成的两个重复的字符串连接起来。

TEXTSPLIT函数：TEXTSPLIT函数用于将文本字符串按指定的分隔符拆分成单元格数组。在这个函数中，它使用CONCAT函数的结果作为要拆分的文本，井号 “#” 作为分隔符，并指定返回拆分后的第一段文本。

数组操作：{1;1;1;1;1;1}是一个数组，它包含6个1，这个BOM的用量，对应于TEXTSPLIT函数可能返回的6个结果（因为每个数字重复3次，有两个数字，所以总共是6个数字）。

数组相乘：计算出一级子件的需求

计算欠料

完成数组运算并计算出子件需求后，下一步是将这些需求与库存进行对比，以确定“欠料”情况。在引用库存时，特别要注意子件是否存在重复项，如果有重复，就需要额外的步骤将相同子件的需求汇总求和。为此，在适当的位置输入以下公式来计算欠料：

=XLOOKUP(Q3#,M3:M8,N3:N8,0)-V3#

公式解释如下：

Q3#：表示子件的标识符或名称，用于在库存列表中查找。

M3:M8：这是库存表中子件标识符或名称的列，XLOOKUP 将在此列中搜索 Q3#。

N3:N8：这是库存表中对应子件的库存量列，XLOOKUP 找到匹配项后将从此列返回库存量。

0：这是 XLOOKUP 的最后一个参数，意味着如果找不到匹配项，则返回0作为该子件的库存量，这有助于避免错误并保持公式的连续性。

V3#：表示子件的需求量，这是在之前的计算中得出的值。

通过这种方式，公式计算了每个子件的库存量与需求量之间的差额，从而得到欠料数量。如果子件在库存表中有多个条目（即存在重复项），则应在应用此公式之前使用SUMIFS 函数或类似的方法将这些条目的库存量汇总。例如，你可以先在库存表中增加一列来汇总相同子件的库存总量，然后再使用上述公式进行欠料计算。

数据分析

在完成默认优先级一级的“欠料”计算后，接下来的关键步骤是对这些欠料结果进行细致分析。任何计算结果小于零的情况都明确指示了欠料的存在。为了标识这些情况，在适当的位置输入以下公式：

=IF(W3#<0,"欠料","不欠")

公式解释：

W3#：指的是存储欠料计算结果的单元格。

当W3# 中的值小于零时，公式返回 “欠料”。

若W3# 的值大于等于零，公式则返回 “不欠”。

然而，这里有一个重要的细节需要留意：当组件的所需数量为1时，才能确定为真实的欠料状况。如果一个组件的所需数量超过1，这往往意味着存在替代子件——即有其他可选的部件可以用来满足生产需求。在这种情况下，我们需要进一步查阅库存明细表，以判断是否拥有足够的替代子件库存来弥补欠料。因此，有必要额外筛选出那些需要考虑替代子件的项目，进行更加详尽的分析。

这部分涉及更深层次内容的讲解，包括如何有效筛选出需要考虑替代子件的数据集，以及如何准确评估替代子件的库存状况，都将在明天由古老师继续为大家讲解和分享。

最后总结

总结回顾，我们探讨了如何通过多步骤的计算方法，精准地分析物料清单（BOM）中涉及的缺料情况。首先，我们确立了筛选一级优先级子件的策略，利用动态数组公式精确定位并提取所需信息。接着，通过XLOOKUP等高级函数，我们建立了连接MPS产品计划与BOM中子件需求的桥梁，确保了需求的准确无误。随后，展开需求的计算让我们能够根据BOM结构，精确计算每个子件的需求量，为后续的库存对比打下坚实基础。

计算欠料的过程中，我们不仅对比了需求与现有库存，还特别关注了子件的重复性和替代选项，通过SUMIFS等函数确保了数据的完整性和准确性。最终，通过细致的欠料分析，我们识别出了真实存在的物料短缺，并为后续的采购决策提供了关键依据。

值得注意的是，当组件的所需数量超过单一单位时，我们面临了更为复杂的替代子件考量，这要求我们在分析中更加审慎，确保库存管理和生产计划的无缝对接。这部分内容，古老师将在明日的分享中深入展开，带领大家探索如何高效筛选和评估替代子件的库存状态，进一步完善我们的物料管理策略，提升供应链的整体效率。通过今天的讨论，我们奠定了坚实的理论与实践基础，为实现精益生产和库存优化迈出了重要一步。

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

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

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

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

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

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

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

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

388 从新手到高手：小明的职场晋级之路——月销售汇总攻略

397 PMC一招搞定任务分配！如何用WPS智能识别多项目负责人？

396 告别繁琐手工整理：一键重塑采购报价单的自动化攻略

395 智能整理：一键自动化解决 PMC 客户信息混乱难题