Excel移动加权平均：从需求预测到安全库存的采购实战指南

实际做采购计划时，最让人头疼的往往不是“这个月卖了多少”，而是“下个月该备多少货”。工厂的长周期物料要提前下单，电商大促前要铺库存，物流线路要安排运力，这些决策背后都依赖同一个能力：需求预测。而 Excel 中的移动加权平均，就是一套不需要复杂工具、不需要编程、在表格里就能直接落地的预测思路。

本文会从供应链管理和采购分析的实际场景出发，围绕“移动加权平均”这个核心方法展开。你会看到它的计算公式、Excel 实现方式、采购成本分析中的应用，以及如何用安全库存公式把预测结果变成真正可执行的采购建议。无论你是供应链专员、采购跟单、物流计划岗，还是刚接触数据分析的 Excel 用户，这篇文章都能帮你建立起一套完整的思路。

1. 背景与核心概念

1.1 供应链预测分析为什么重要

供应链管理的核心矛盾是“需求不确定”和“供应有周期”之间的矛盾。客户下单往往集中在某几天，供应商交货却需要固定的提前期，仓库又不能无限扩容，资金也有占用成本。如果备货太少，销售缺货，客户满意度下降；如果备货太多，库存积压，仓储成本和呆滞风险都会上升。

需求预测就是缓解这个矛盾最直接的手段。通过历史销售数据、采购数据、物流发货数据，推算出未来一段时间内可能的需求量，采购部门才能确定“买多少、什么时候买”，物流部门才能确定“调多少车、发多少货”。在众多预测方法里，移动加权平均是入门门槛最低、最容易解释、也最容易在 Excel 中维护的一种。

1.2 什么是移动加权平均

移动加权平均，简单理解就是“用最近几期的历史数据，按不同权重计算一个平均值，作为下一期的预测值”。它有两个关键词：

• 移动：随着时间向前推进，计算窗口不断滑动。比如用最近 3 个月预测下个月，当新的月份到来，最老的那个月会被剔除，新月份会被加入。
• 加权：不同时期的数据对预测结果的影响不同。通常离预测点越近的数据，参考价值越大，权重越高；离得越远的数据，权重越低。

它的最大优势是操作简单、结果稳定，适合需求波动不是特别剧烈、又带有一定趋势的常规物料。相比简单平均（把每一期都当成一样重要），移动加权平均能更快地反映最近需求的变化；相比指数平滑法，它又更容易理解，业务人员能够清楚解释每个数字是怎么算出来的。

1.3 Excel 能做哪些预测分析

很多同学以为预测分析必须使用 Python、SPSS 或者专业供应链软件，其实 Excel 的能力被低估了。Excel 里可以完成以下预测相关工作：

• 用函数计算移动平均、移动加权平均、指数平滑。
• 用 FORECAST.ETS 系列函数做带季节性的预测。
• 用数据透视表按 SKU、月份、供应商汇总历史数据。
• 用图表功能观察需求趋势，辅助判断预测结果是否合理。
• 用单元格公式搭建动态模型，更新数据后预测值自动刷新。

对于大多数中小型企业的采购预测场景，Excel 完全够用。本文后面会用实际表格一步步演示。

2. 环境准备与数据规范

2.1 Excel 版本与常用函数说明

本文的示例基于常见版本的 Excel 编写，Excel 2016 及以上版本基本都能正常使用。如果你使用的是 WPS 表格，大部分函数也兼容，但个别动态数组函数可能需要留意版本差异。

会用到的核心函数包括：

版本需要根据你的项目实际情况调整，本文示例以常见环境为例，重点演示配置思路。

2.2 原始数据表结构设计

做预测分析前，第一步不是写公式，而是把原始数据整理规范。很多项目的预测偏差其实不是方法问题，而是数据质量问题。

建议的原始数据表结构如下：

这里要特别注意几点：

• 日期列必须是真正的日期格式，而不是文本字符串。
• 销量列不能出现“约 100 件”这类带文本的内容。
• 数据中不要留空行和合并单元格。
• 同一 SKU 尽量连续存放，后续公式更简单。

2.3 用 Excel 表格功能定义数据区域

推荐把原始数据区域转成 Excel 表格（快捷键 Ctrl+T）。转成表格后，公式引用会自动扩展到新数据，这在“移动平均”场景下非常实用，因为每个月都要追加新数据。

选中数据区域后，按 Ctrl+T，弹出创建表窗口，确认“表包含标题”后点击确定即可。之后在表里新增一行，公式区域会自动扩展，不需要手动修改引用范围。

3. 移动加权平均预测原理拆解

3.1 加权平均和简单平均的区别

先看一个例子。某 SKU 最近 3 个月的销量分别是 100、130、160，如果用简单平均预测下个月，结果是：

(100 + 130 + 160) / 3 = 130

但仔细想一下，最近的 160 应该比 3 个月前的 100 更有参考价值，因为市场趋势可能正在上升。简单平均把老数据和新数据视为同等重要，预测结果存在明显的滞后性。

移动加权平均的做法是给最近的数据更高的权重，比如最近一期权重 0.6、中间一期权重 0.3、最早一期权重 0.1，那么预测值就变成：

100 × 0.1 + 130 × 0.3 + 160 × 0.6 = 145

这个结果显然比 130 更贴近最近的市场走势。核心思想就是：越靠近预测点的数据，对未来影响越大。

3.2 权重如何设定

权重是移动加权平均里最关键的参数，没有绝对固定的公式，但有常见的设定原则：

• 权重之和必须等于 1，或者使用“权重系数”形式让 Excel 自动归一化。
• 最近一期的权重最大，通常在 0.5～0.7 之间。
• 权重递减幅度不能太激进，否则预测值几乎等于上期实际值，波动太大；也不能太平缓，否则就退化成了简单平均。
• 当历史数据量较大时，可以把时间窗拉长到 5 期或 6 期，权重分布更均匀。

常见的一种方案是“等差递减”：比如 3 期权重设为 0.6、0.3、0.1；4 期权重设为 0.4、0.3、0.2、0.1。这种权重分配方法可解释性强，业务评审时也容易讲清楚。

3.3 移动窗口如何选择

移动窗口指的是“用最近几个周期的数据来预测”。窗口太短，预测结果容易受偶然波动影响；窗口太长，预测结果又太迟钝。

判断窗口是否合适，可以从几个方面入手：

• 看需求波动幅度，波动大可以考虑增加窗口期数，平滑噪音。
• 看业务周期，如果存在明显的季度性，窗口最好能覆盖一个完整周期，比如 3 个月或 6 个月。
• 可以用历史数据做回测，分别尝试 3 期、4 期、5 期窗口，计算预测误差，选择误差最小的参数。

在 Excel 中做这种参数对比非常方便，只需复制几列公式，修改权重区域即可。

4. 完整实战：采购需求预测

4.1 需求预测基本表

现在进入核心实战环节。我们模拟一个电商采购场景：某公司需要为 SKU-A1001 制定下个月的采购计划，目前有 1 月至 6 月的实际销量数据，采用 3 期移动加权平均预测 7 月销量。

新建工作表，按以下结构录入数据：

权重系数可以放在另一个区域，比如 H1:H3：

H1 = 0.6 H2 = 0.3 H3 = 0.1

其中 H1 对应最近一期（6 月）的权重，H2 对应 5 月，H3 对应 4 月。

4.2 手动版本：使用 SUMPRODUCT

如果数据量不大，可以直接写固定区域的公式。在 C7 单元格输入：

=SUMPRODUCT(B4:B6,$H$1:$H$3)/SUM($H$1:$H$3)

这个公式的含义是：

SUMPRODUCT(B4:B6, H1:H3) = 140 × 0.1 + 150 × 0.3 + 165 × 0.6

计算结果是 158。除以 SUM(H1:H3) 的目的是归一化，当权重之和等于 1 时，这步不改变结果；当权重系数未归一化时，这步能保证预测值在合理范围内。

得到的预测销量是 158 件，意味着如果 7 月的业务环境和 4-6 月类似，可以按 158 件左右来准备物料。

4.3 动态版本：使用 OFFSET 和整列引用

手动版本有个问题：每个月新数据追加后，公式里的 B4:B6 区域都要手动修改，非常繁琐。更推荐使用 OFFSET 函数实现“自动取最后 3 期数据”。

假设数据从第 2 行开始，B 列是销量，B2:B7 共 6 个数据。预测 7 月的公式可以写成：

=SUMPRODUCT(OFFSET($B$1,COUNTA($B:$B)-3,0,3,1),$H$1:$H$3)/SUM($H$1:$H$3)

公式拆解：

• COUNTA($B:$B) 统计 B 列非空单元格数量，包括表头。如果有表头加 6 个月数据，结果是 7。
• 用 COUNTA 减 3 得到偏移量 4，从 B1 向下偏移 4 行，到达 B5。
• 第三个参数 0 表示不偏移列，第四个参数 3 表示返回 3 行，于是取到 B5:B7。
• 当 7 月实际数据录入后，COUNTA 变成 8，偏移量变成 5，区域自动变成 B6:B8，正好是 6、7、8 月的数据。

这样一来，每个月只需要在表格下面追加新数据，预测值会自动更新，不需要重复修改公式，非常适合长期滚动维护。

4.4 结果说明与验证

预测不是算完就结束了，还要做验证。我们可以用已有数据做“回测”：例如用 1-3 月预测 4 月，用 2-4 月预测 5 月，用 3-5 月预测 6 月，然后对比预测值和实际值。

在 E 列添加一个“误差率”字段：

=ABS(C4-B4)/B4

误差率越低，说明权重参数越合适。如果发现误差率偏大，可以调整 H1:H3 的权重分配，再对比效果。这一步虽然在 Excel 里做起来简单，但对最终预测质量影响很大，值得在每个周期复盘时执行。

5. 进阶：采购成本分析与安全库存

5.1 用预测需求量估算采购成本

预测出需求量后，接下来采购部门最关心的是成本。假设 SKU-A1001 的采购单价在不同批次有波动，历史采购记录如下：

为了估算下次采购的成本，不能简单把三个单价平均，而要使用移动加权平均的思路计算“平均采购单价”：

=SUMPRODUCT(B2:B4,C2:C4)/SUM(B2:B4)

结果约为 12.14 元。这种考虑采购数量的加权平均，比简单平均更准确，因为它反映了企业实际的资金占用水平。

接下来，预计采购金额就是：

预计采购金额 = 预测需求量 × 加权平均采购单价

如果在前面预测出 7 月销量为 158 件，则采购金额约为 158 × 12.14 = 1918.12 元。当然，这里只是原材料或商品本身的采购成本，实际业务中还需要考虑运费、关税、损耗率等因素。

5.2 安全库存计算公式

有了预测需求量，还不能直接作为采购量，因为预测总会有误差。为了应对需求波动和供应商交货延迟，需要设置安全库存。

常见的库存计算公式是：

采购建议量 = 预测需求量 + 安全库存 - 现有库存 - 在途订单

安全库存的经典公式为：

安全库存 = Z × σ × √L

其中：

• Z 是服务水平对应的系数，服务水平 95% 时 Z 约为 1.65，99% 时 Z 约为 2.33。
• σ 是需求数量的标准差，反映需求波动大小。
• L 是采购提前期（以天为单位），反映从下单到入库的时间。

在 Excel 中，假设每日需求量记录在 B2:B32，提前期为 7 天，目标服务水平为 95%，公式可以写成：

=NORM.S.INV(0.95)*STDEV.S(B2:B32)*SQRT(7)

这里 NORM.S.INV(0.95) 返回 1.6448，STDEV.S 计算需求标准差，SQRT(7) 把方差按时间扩展。这个结果可以作为安全库存的参考值。

5.3 不同服务水平下的安全库存对比

服务水平越高，安全库存越大，缺货风险越低，但库存持有成本也越高。企业需要权衡。

这部分可以做成 Excel 参数表，用数据验证下拉选择服务水平，安全库存自动变化。采购人员只需修改服务水平，就能快速看到库存建议的变化，方便与财务和销售沟通。

6. 数据可视化与周期性预测

6.1 用折线图观察预测效果

预测值不是孤立存在的，建议把“实际销量”和“预测销量”放在同一个折线图中观察。这样能直观看到模型是否跟上了趋势，也能发现明显的异常值。

在 Excel 中插入折线图的操作很简单：选中包含日期、实际销量、预测值的区域，点击“插入”→“折线图”。如果实际销量和预测销量量级一致，可以直接用双线显示；如果差异太大，检查数据区域是否选对。

通过折线图，还能发现一些数值上不容易察觉的问题：比如某个月出现断崖式下跌，可能是促销停止、缺货，也可能是数据录入错误，需要回到原始数据确认。

6.2 FORECAST.ETS 补充季节性分析

移动加权平均适合波动平缓的数据，但如果你的业务存在明显的季节因素，比如羽绒服冬季销量高、空调夏季销量高，移动加权平均就会显得迟钝。此时可以尝试 Excel 自带的 FORECAST.ETS 函数。

假设日期在 A2:A13，销量在 B2:B13，预测第 14 期的公式为：

=FORECAST.ETS(A14,B2:B13,A2:A13,1,1)

其中第四个参数 1 表示季节性周期长度为 1 年，第五个参数 1 表示自动处理缺失值。此函数需要 Excel 2016 及以上版本支持，且日期必须是真正的日期类型，数据最好按时间顺序排列。

FORECAST.ETS 适合对比验证：先用移动加权平均算一版，再用 FORECAST.ETS 算一版，如果两者偏差很大，说明数据中可能存在较强的周期或趋势，需要进一步分析。

6.3 多 SKU 场景的处理思路

实际业务中不会只有一个 SKU。处理多 SKU 时要注意两点：

• 每个 SKU 单独建立预测区域，不要把不同物料的数据混在一个公式里。
• 数据透视表按 SKU 汇总后，再对每个 SKU 做预测，或者使用函数按条件动态引用。

例如，在一张总表里，A 列是 SKU，B 列是月份，C 列是销量。可以新建一个“预测模型”工作表，通过 SUMIFS 或 FILTER 函数把指定 SKU 的数据提取出来，然后再套用移动加权平均公式。这种方法虽然不如 Python 批量处理高效，但在数据量几百行以内时足够使用。

7. 常见问题与排查思路

另一个高频问题是在复制公式时引用区域发生偏移。解决方法是把权重区域用绝对引用固定，例如写成$H$1:$H$3，这样向下填充时权重区域不会跑偏。

8. 最佳实践与工程建议

8.1 数据管理规范

预测分析的前提是数据可信。建议建立每月数据更新机制，专人负责维护销售、采购和库存数据。原始数据表只做记录，不要在上面直接写计算公式；预测模型单独建工作表，所有公式统一维护。这样能避免误删数据导致公式错乱，也方便月度复盘。

8.2 模型权重与滚动周期调整

移动加权平均的权重参数不是一劳永逸的。每隔一段时间，建议用最近 3-6 个月的数据做一次回测，比较不同权重组合下的平均绝对百分比误差，选择误差最小的组合。

计算公式：

=AVERAGE(ABS(预测值区域-实际值区域)/实际值区域)

如果误差长期偏高，说明业务环境已经发生变化，单纯的移动加权平均可能不再适用，这时要升级到更复杂的预测模型，比如指数平滑法、回归分析，或者引入外部因素如促销计划、市场行情数据。

8.3 生产环境中的注意事项

• 采购预测结果只能作为辅助决策，不能替代人工判断。
• 关键物料宁可适当提高安全库存，也不要追求零库存导致断供。
• 每次调整权重或窗口参数时，保留一份历史版本的 Excel 文件，方便对比和回溯。
• 对供应商给出预测需求时，建议给一个区间而不是一个单一数值，例如“预计 150～170 件”，让供应商有一定弹性。
• 安全库存计算中的提前期要按实际供应商交期填写，不能拍脑袋。可以定期统计供应商平均到货天数，用更准确的数字更新模型。

最后是一个很实用的习惯：每个月做一次“预测复盘会”，把上个月的预测值与实际值对比，找出偏差原因并记录。持续迭代半年后，你手里的这套 Excel 预测模型，会比想象中可靠得多。移动加权平均只是一个起点，但把最简单的模型用好、用扎实，在供应链分析中已经能解决大部分日常问题。