用 Excel 做 MRP：六张表与公式，以及什么时候该停

大多数关于“别用 Excel 做计划”的劝告，来自卖软件的人。真实情况是：一家两百个物料、BOM 三层的工厂，用一张结构正确的表，可以稳定地跑出可用的净需求和下单日期。它比一套没人维护的 ERP 模块更可靠，因为每个数字你都能点进去看它是怎么来的。

这篇文章讲结构。不是模板截图，是每张表放什么、行和列怎么排、公式怎么写。文末列出五个判据，用来判断什么时候这套东西开始不值得维护。

为什么表格仍然是个合理答案

三个理由，都是真的：

• 透明。计划员能看到 4200 这个数是从哪三个单元格加出来的。ERP 里点三层菜单之后仍然是个黑盒。
• 改得动。客户改了交期，直接改一格，整条链跟着动。这个反馈速度在实施期的 ERP 里是没有的。
• 不依赖别人。不用等 IT 排期，不用为加一个字段提需求。

它的代价同样明确：并发编辑、版本控制、权限、审计，全都没有。这些在两百个物料时不算问题，在两千个物料时是致命的。

六张表，其中只有一张是输出

一个能长期维护的结构，工作表数量要少。建议就六个，命名固定：

1. 物料——物料号、名称、单位、类型（采购/自制）、提前期（工作日）、最小起订量、批量倍数、安全库存。
2. BOM——父件、子件、单位用量、损耗率、生效日期。一行一个父子关系，不要用合并单元格。
3. 库存——物料号、现有量、快照日期。一列现存量，不要在这里算任何东西。
4. 供应——未结采购订单、未结生产订单，统一成“物料号 / 数量 / 预计入库日”三列。
5. 需求——未结销售订单展开到成品层，加预测。同样是“物料号 / 数量 / 需求日”。
6. 计划——输出表。每个物料一段，横向是周桶，纵向是毛需求、预计入库、预计可用、净需求、计划下单。

关键约束：前五张表是纯数据，不含公式；第六张表是纯计算，不含手工录入。混在一起是这类文件崩掉的头号原因——有人在计算表里手改了一个数，下个月没人记得为什么那一格和别的不一样。

周桶，十三个就够

按周分桶，不要按天。按天会让列数失控，而多数工厂的提前期精度本来就在“周”这个量级上。十三个周桶覆盖一个季度，足够做采购决策。

计划表的第一行放周起始日，用公式生成而不是手填：

=TEXTJOIN("-",TRUE,{"周","起始日"})
' 假设 B1 是计划基准日（本周一）
B2: =B1
C2: =B2+7
D2: =C2+7
' 一直拉到第 13 列

周桶的边界必须固定——比如一律取周一。混着用“本周三到下周二”的滚动窗口，两周之后就没有人能对齐两张表了。

毛需求来自上一层

这是整套东西唯一需要动脑的地方。毛需求不是销售订单，是把销售订单按 BOM 往下展开的结果。

展开必须从下往上算：先算最底层采购件的毛需求，再算它上面那层组件的净需求与计划下单，然后把组件的计划下单当作下一层的毛需求继续往上推。反过来算（从成品往下逐层减库存）会得出偏小的缺口，因为上层组件的库存没有被考虑进去。

单位用量要乘损耗：一行 BOM 写“单位用量 2、损耗率 3%”，那么实际需求是 需求数量 × 2 × 1.03。损耗率忘了乘，是缺料清单偏乐观的常见原因。

净算，以及必须带着走的余额

净需求公式本身只有一行，难的是余额要逐周滚动：

预计可用(本周) = 预计可用(上周) + 预计入库(本周) - 毛需求(本周)
净需求(本周)   = MAX(0, 安全库存 - 预计可用(本周))
计划入库(本周) = 净需求(本周) 向上取整到批量倍数，且不小于最小起订量

三个容易错的地方：

• 第一周要有期初。第一周的“预计可用(上周)”必须等于库存快照的现有量，否则整条链是错的。
• 安全库存要参与净算。忘了这一项，算出来的计划会在库存刚好归零时仍显示“不缺”。
• 计划入库必须在同一周加回可用量。否则下一周又算一遍同样的缺口，缺口被重复采购。

提前期倒排与日历陷阱

净需求是“什么时候要”，下单日期是“什么时候必须买”。后者等于前者减提前期，再减来料检验天数。

陷阱在这里：减的必须是工作日，不是自然日。30 天提前期如果按自然日减，实际只覆盖了大约 21 个工作日，下单日期会算晚一周半，而这一周半正好是缺料发生的地方。

' 需求日 - 提前期(工作日)，跳过周末
=WORKDAY(需求日, -提前期天数)

' 如果还有法定节假日表（Holidays 区域）
=WORKDAY(需求日, -(提前期天数 + 检验天数), Holidays)

如果工厂周末也开工，那就不是 WORKDAY 的问题，而是要把自己的停工日历建成一张表，用 WORKDAY.INTL 指定休息日。

真正干活的公式

把上面几段收成一张对照表，可以直接照抄。假设物料在某一行，周桶从 B 列到 N 列：

要算的东西	放在哪	公式
毛需求	第 2 行	=SUMIFS(需求数量, 物料号, $A3, 需求日, ">="&B$1, 需求日, "<"&C$1)
预计入库	第 3 行	=SUMIFS(供应数量, 物料号, $A3, 入库日, ">="&B$1, 入库日, "<"&C$1)
预计可用	第 4 行	B4 =期初库存；C4 =B4+B3-B2+C5
净需求	第 5 行	=MAX(0, 安全库存-B4)
采购建议	第 6 行	=IF(B5=0,0,MAX(最小起订量,CEILING(B5,批量倍数)))
最晚下单日	第 7 行	=IF(B5=0,"",WORKDAY(B$1,-(提前期+检验天数),Holidays))

注意第 4 行里 +C5：这是把自己这一周的计划入库加回可用量，否则缺口会被重复计算。

文件从哪里开始坏

不是从算错开始，是从这五件事开始：

• 出现第二个版本。“计划-最终版-2.xlsx”。从这一刻起，会议讨论的是哪一个版本成了政治问题。
• 有人开始手工覆盖公式。通常是赶一张急单。覆盖第一次之后就没人敢信这张表了。
• 物料超过几百个、BOM 超过三层。不是算不动，是每改一个提前期要动的单元格太多，出错的概率超过手工复核的能力。
• 需要按工作中心看产能。表格里加产能负荷会让文件体积翻几倍，而且几乎必然算错。
• 需要别人也能跑。只有一个人会开那个宏的时候，计划就是单人依赖。

出现两条以上，继续投入就是在给一个不会变好的结构加功能。

怎么迁出去而不丢掉计划

迁移不是重写，是导出。把这六张表原样导出成 CSV，导入任何计划工具都应该是无损的——物料、BOM、库存、供应、需求，这五张表就是 MRP 的全部输入。

迁完之后保留原表两周，逐行比对净需求。差异只有两类：一类是你原来的公式算错了（通常是损耗或安全库存漏项），一类是新工具的口径不同（比如按工作日还是自然日）。两类都要弄明白再停用旧表，否则你会把一次修正当成一次回归。

常被问到的问题