← 全部文章 Read in English

多级BOM齐套为什么Excel算不准?5个原因+1个5级算例

上周一个做小家电的 PMC 经理给我发来一张 Excel 截图,说他们的多级 BOM 齐套表算出来缺 320 个物料 Z,结果采购按 320 下了单,货到之后发现实际只缺 40 个——库存里还有一批 Z 没算进去,因为那批 Z 是挂在半成品 B 下面的,Excel 的汇总逻辑把它算重了。

这不是个例。过去一年我见过的 Excel 齐套表,几乎都栽在同一批坑上。问题不在 PMC 不够细心,而在 Excel 的行列结构,天然表达不了多级 BOM 递归展开和库存优先扣减这两个逻辑。这篇文章把 5 个原因拆开讲,然后用一个可以逐项复算的 5 级 BOM 算例,对比手工算法和递归算法的差异。

半成品优先扣减 原因1 共享半成品 原因2 替代料/多供应商 原因3 在途采购 原因4 日期快照 原因5

原因一:Excel 是“全展开”,不会“半成品库存优先扣减”

判断标准:如果齐套表把所有层级直接展开到最底层物料,再 SUMIF 汇总,那它一定算多了。正确做法是:半成品库存够的部分不再往下展开。

这是物料齐套检查里最常见、也最容易被忽略的错。Excel 的典型做法是:用 VLOOKUP 把所有 BOM 层级拍平,然后把每个物料的用量乘以订单数量,最后 SUMIF 汇总。这个逻辑默认“半成品库存为 0”,实际上把下级物料的需求放大了。

举个简单的例子。假设产品 P 需要 1 个半成品 A,A 需要 3 个物料 Y。订单 100 台 P,A 的库存有 30 台。

差了 90 个 Y。如果 Y 是 8 块钱一个的采购件,这一个物料就多压了 720 块。如果有几十个这样的物料,一个月的多采购金额就是几万块。这就是为什么很多工厂感觉“系统上了,但库存反而更高了”——不是系统的问题,是齐套算法没有做半成品库存优先扣减。

正确的递归规则只有一句话:算到某一层半成品时,先扣它的库存,只对“不够的部分”继续往下展开。这句话用 Excel 公式几乎写不出来,用代码是三行递归。

原因二:共享半成品被多个工单同时需要,Excel 会重复承诺

判断标准:当两个工单同时需要同一个半成品时,Excel 会分别按“库存够”算给两边,合起来就不够。系统必须做原子性扣减,或者至少按优先级排序后串行分配。

这在多品种小批量的工厂里几乎天天发生。假设半成品 A 的库存是 30 台,今天有两个工单同时要 A:工单甲要 20 台,工单乙要 20 台。

PMC 如果按 Excel 的结论去派工,两个工单同时开工,第二个必然停线等料。这就是“齐套率显示 100%,车间还是天天停线”的典型原因之一。

系统里正确的处理方式有两种:一是按工单优先级串行扣减,高优先级的先占库存,低优先级的看到的是扣减后的余额;二是按工单开工日期串行扣减,先开工的先占。不管哪种,前提是系统知道“所有工单在看同一份库存”,而不是每个工单各自算一遍。

原因三:替代料和多供应商,Excel 的 VLOOKUP 不认

判断标准:如果你的 BOM 里有“主料缺了可以用替代料”的情况,Excel 的齐套表一定算不准,因为它只会匹配一个物料编码。

电子、五金、注塑行业里,替代料是常态:某个电容 10uF 缺货,可以用 22uF 顶上;某个牌号的钢材缺货,可以用同等强度的另一牌号。Excel 的 VLOOKUP 是按编码一一对应的,遇到替代关系要么算缺料(实际不缺),要么算齐套(实际主料不够、替代料也没走审批)。

真正可用的BOM 展开逻辑,需要支持“一个 BOM 位置对应多个候选物料”,齐套时按优先级依次尝试扣减。这一层 Excel 基本做不到,只能靠人工判断,而人工判断在 150 个型号、几百个物料的情况下,出错率极高。

原因四:在途采购没有联动,缺料清单是“假缺口”

判断标准:缺料清单上的数量,必须是“当前库存 + 在途采购”抵扣之后的净缺口。如果只显示当前库存缺口,PMC 会重复下单。

这是物料齐套排产里第二常见的坑。Excel 的齐套表通常只对比 BOM 需求和当前库存,不联动采购订单。结果是:物料 Z 缺 320,PMC 按 320 下单;但实际上已经有一张 300 的采购单在途,真实缺口只有 20。多下的 300 到货后变成呆滞库存。

反过来也成立。如果 Excel 表里手工维护了在途数量,但采购单状态变了(部分到货、取消、延期)没有同步更新,齐套表就会显示“已齐套”,实际物料还在路上。系统做这件事的价值不在于公式多复杂,而在于在途数量是实时从采购单读出来的,不是手工填的。

原因五:Excel 是“快照”,数据一变就失效

判断标准:齐套表算完之后,如果库存、BOM、采购单、工单任何一项发生变化,这张表就应该作废重算。Excel 做不到,系统可以做到按需重算。

PMC 的日常是:早上 8 点导出库存,9 点算完齐套,10 点车间领走一批料,11 点采购到了一车货。11 点之后,早上算的齐套表已经不准了,但 PMC 往往还在用。这就是为什么很多工厂“算得很认真,还是天天缺料”。

系统里的生产齐套率应该是按需计算的:点开工单的“齐套详情”那一刻,系统实时读当前库存、当前在途、当前 BOM,算出一个当下的结果。快照和实时计算的差别,在变化越快的工厂里越明显。

一个可以逐项复算的 5 级 BOM 算例

下面这个例子,你可以拿计算器跟着算一遍,看看手工算法和递归算法的差异。

BOM 结构

父件子件类型用量
成品 P半成品 A自制1
成品 P物料 X采购2
半成品 A半成品 B自制2
半成品 A物料 Y采购3
半成品 B物料 Z采购4
半成品 B物料 W采购1

当前库存

A 库存 30,B 库存 10,X 库存 500,Y 库存 100,Z 库存 200,W 库存 50。订单 P 需求 100 台。

正确算法(半成品库存优先扣减)

逐层算,半成品库存够的部分不往下展开:

  1. P 需要 100 台,需要 A 100 个、X 200 个。
  2. A 库存 30,缺 70,需生产 A 70 个。生产 70 个 A 需要 B 140 个、Y 210 个。
  3. B 库存 10,缺 130,需生产 B 130 个。生产 130 个 B 需要 Z 520 个、W 130 个。
  4. 净需求:X 200、Y 210、Z 520、W 130。
  5. 对比库存后的缺口:X 够(500−200=300 结余)、Y 缺 110(210−100)、Z 缺 320(520−200)、W 缺 80(130−50)。

Excel 全展开算法(错误示范)

直接把所有层级拍平,用订单数量 100 乘到底:

两种算法对比

物料正确净需求Excel全展开多算的量库存缺口(正确)库存缺口(Excel)
X200200000
Y210300+90110200
Z520800+280320600
W130200+7080150
合计10601500+440510950

这个算例里,Excel 全展开算法把缺口合计从 510 放大到 950,多算了 86%。如果物料平均单价 10 元,这一单就多下 4400 元的采购。放大到 150 个型号、每月几十单,就是几十万的资金占用。

这个算例想说明的事:问题不在 Excel 的公式写得对不对,而在“全展开”这个思路本身就不适合多级 BOM。递归展开 + 半成品库存优先扣减 + 在途采购联动,这三件事任何一件缺失,齐套结果都会系统性偏大或偏小。

正确的齐套算法长什么样

用伪代码表达,核心逻辑只有十几行:

function checkKit(productId, qty, stockMap, visited) { // 循环保护 if (visited.has(productId)) return []; visited.add(productId); const result = []; for (const item of bomOf(productId)) { const required = item.qty * qty; if (item.type === 'material') { // 采购件:直接对比库存 + 在途 result.push({ materialId: item.materialId, required, stock: stockMap[item.materialId] || 0, inTransit: inTransitMap[item.materialId] || 0 }); } else { // 半成品:先扣库存,只对缺的部分递归展开 const subStock = productStock(item.subProductId); const shortage = Math.max(0, required - subStock); if (shortage > 0) { result.push(...checkKit(item.subProductId, shortage, stockMap, visited)); } } } visited.delete(productId); return result; }

这个函数做三件事:一是递归展开,二是半成品先扣库存再决定是否继续展开,三是把采购件的库存和在途一起返回。用 Excel 公式实现这三件事,几乎不可能;用代码实现,就是上面这十几行。

边界说明:什么时候 Excel 够用

不是所有工厂都需要换掉 Excel。下面两种情况,Excel 继续用是合理的:

但只要出现以下任一情况,Excel 的齐套表就开始系统性失准:BOM 有 3 层以上、半成品库存占比超过 20%、型号超过 50 个、有替代料、在途采购金额超过月采购额的三分之一。这些条件叠在一起,就是离散制造工厂的常态。

常见问题(FAQ)

Q:单层 BOM 的产品,用 Excel 算齐套够不够?
够。如果产品下面直接挂物料、没有半成品,Excel 用 SUMIF 汇总需求、VLOOKUP 对比库存,20 分钟内可以算准。只有出现 3 层以上 BOM、或者半成品库存需要优先扣减时,Excel 的手工逻辑才开始失效。
Q:为什么不能把所有层级直接展开,再用 SUMIF 汇总?
因为这会忽略半成品库存。如果半成品 A 库存有 30 台,实际只需要再生产 70 台 A,它的下级物料就只需按 70 台计算,而不是按 100 台。全展开算法会把下级物料的需求放大约 43%(在这个算例里),导致多采购、多占用资金。
Q:齐套率算到 95% 以上,为什么车间还是天天停线?
常见原因是齐套算法没有联动在途采购。缺料清单只显示当前库存缺口,没扣减已下单未到货的数量,PMC 按缺口下单后,实际在途已经覆盖,但系统仍报缺料;反过来,如果算法没扣减在途,PMC 会重复下单。另一个原因是共享半成品被多个工单重复承诺,单看每个工单都齐套,合起来就不够。

想拿你工厂真实的 BOM,现场算一次齐套?

本地部署 · 多级 BOM 递归齐套 · 半成品库存优先扣减 · 在途采购联动 · 30 天完整功能免费试用

预约一次多级 BOM 齐套演示 →

本文为算法与方法讨论,算例中的 BOM 结构、库存数量、单价均为示意数据,仅用于说明递归算法与全展开算法的差异,不代表任何具体客户的真实数据。轻排 PMClite 为独立产品,与文中未点名的任何厂商无商业关联。