多级BOM齐套为什么Excel算不准?5个原因+1个5级算例
上周一个做小家电的 PMC 经理给我发来一张 Excel 截图,说他们的多级 BOM 齐套表算出来缺 320 个物料 Z,结果采购按 320 下了单,货到之后发现实际只缺 40 个——库存里还有一批 Z 没算进去,因为那批 Z 是挂在半成品 B 下面的,Excel 的汇总逻辑把它算重了。
这不是个例。过去一年我见过的 Excel 齐套表,几乎都栽在同一批坑上。问题不在 PMC 不够细心,而在 Excel 的行列结构,天然表达不了多级 BOM 递归展开和库存优先扣减这两个逻辑。这篇文章把 5 个原因拆开讲,然后用一个可以逐项复算的 5 级 BOM 算例,对比手工算法和递归算法的差异。
原因一:Excel 是“全展开”,不会“半成品库存优先扣减”
判断标准:如果齐套表把所有层级直接展开到最底层物料,再 SUMIF 汇总,那它一定算多了。正确做法是:半成品库存够的部分不再往下展开。
这是物料齐套检查里最常见、也最容易被忽略的错。Excel 的典型做法是:用 VLOOKUP 把所有 BOM 层级拍平,然后把每个物料的用量乘以订单数量,最后 SUMIF 汇总。这个逻辑默认“半成品库存为 0”,实际上把下级物料的需求放大了。
举个简单的例子。假设产品 P 需要 1 个半成品 A,A 需要 3 个物料 Y。订单 100 台 P,A 的库存有 30 台。
- 正确算法:P 需要 100 个 A,库存 30 个,实际只需再生产 70 个 A。Y 的需求是 70 × 3 = 210。
- Excel 全展开算法:直接把 P → A → Y 展开,Y 的需求是 100 × 3 = 300。
差了 90 个 Y。如果 Y 是 8 块钱一个的采购件,这一个物料就多压了 720 块。如果有几十个这样的物料,一个月的多采购金额就是几万块。这就是为什么很多工厂感觉“系统上了,但库存反而更高了”——不是系统的问题,是齐套算法没有做半成品库存优先扣减。
正确的递归规则只有一句话:算到某一层半成品时,先扣它的库存,只对“不够的部分”继续往下展开。这句话用 Excel 公式几乎写不出来,用代码是三行递归。
原因二:共享半成品被多个工单同时需要,Excel 会重复承诺
判断标准:当两个工单同时需要同一个半成品时,Excel 会分别按“库存够”算给两边,合起来就不够。系统必须做原子性扣减,或者至少按优先级排序后串行分配。
这在多品种小批量的工厂里几乎天天发生。假设半成品 A 的库存是 30 台,今天有两个工单同时要 A:工单甲要 20 台,工单乙要 20 台。
- Excel 分别算:甲看库存 30,够 20,齐套;乙看库存 30,够 20,也齐套。两边都绿灯。
- 实际情况:30 台库存只够一个工单,另一个必须等生产。
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 台。
正确算法(半成品库存优先扣减)
逐层算,半成品库存够的部分不往下展开:
- P 需要 100 台,需要 A 100 个、X 200 个。
- A 库存 30,缺 70,需生产 A 70 个。生产 70 个 A 需要 B 140 个、Y 210 个。
- B 库存 10,缺 130,需生产 B 130 个。生产 130 个 B 需要 Z 520 个、W 130 个。
- 净需求:X 200、Y 210、Z 520、W 130。
- 对比库存后的缺口:X 够(500−200=300 结余)、Y 缺 110(210−100)、Z 缺 320(520−200)、W 缺 80(130−50)。
Excel 全展开算法(错误示范)
直接把所有层级拍平,用订单数量 100 乘到底:
- X:100 × 2 = 200(这个对,因为 X 直接挂在 P 下)
- Y:100 × 1 × 3 = 300(错,多了 90)
- Z:100 × 1 × 2 × 4 = 800(错,多了 280)
- W:100 × 1 × 2 × 1 = 200(错,多了 70)
两种算法对比
| 物料 | 正确净需求 | Excel全展开 | 多算的量 | 库存缺口(正确) | 库存缺口(Excel) |
|---|---|---|---|---|---|
| X | 200 | 200 | 0 | 0 | 0 |
| Y | 210 | 300 | +90 | 110 | 200 |
| Z | 520 | 800 | +280 | 320 | 600 |
| W | 130 | 200 | +70 | 80 | 150 |
| 合计 | 1060 | 1500 | +440 | 510 | 950 |
这个算例里,Excel 全展开算法把缺口合计从 510 放大到 950,多算了 86%。如果物料平均单价 10 元,这一单就多下 4400 元的采购。放大到 150 个型号、每月几十单,就是几十万的资金占用。
正确的齐套算法长什么样
用伪代码表达,核心逻辑只有十几行:
这个函数做三件事:一是递归展开,二是半成品先扣库存再决定是否继续展开,三是把采购件的库存和在途一起返回。用 Excel 公式实现这三件事,几乎不可能;用代码实现,就是上面这十几行。
边界说明:什么时候 Excel 够用
不是所有工厂都需要换掉 Excel。下面两种情况,Excel 继续用是合理的:
- BOM 只有一层,产品下面直接挂物料,没有半成品。这时候 SUMIF + VLOOKUP 就能算准,换成系统反而增加负担。
- 订单少、型号少、变化慢,一个月就几单,PMC 有充足时间手工核对。这种情况下,Excel 的“快照失效”问题不明显。
但只要出现以下任一情况,Excel 的齐套表就开始系统性失准:BOM 有 3 层以上、半成品库存占比超过 20%、型号超过 50 个、有替代料、在途采购金额超过月采购额的三分之一。这些条件叠在一起,就是离散制造工厂的常态。
常见问题(FAQ)
本文为算法与方法讨论,算例中的 BOM 结构、库存数量、单价均为示意数据,仅用于说明递归算法与全展开算法的差异,不代表任何具体客户的真实数据。轻排 PMClite 为独立产品,与文中未点名的任何厂商无商业关联。