实际做采购计划时最让人头疼的往往不是“这个月卖了多少”而是“下个月该备多少货”。工厂的长周期物料要提前下单电商大促前要铺库存物流线路要安排运力这些决策背后都依赖同一个能力需求预测。而 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 表格大部分函数也兼容但个别动态数组函数可能需要留意版本差异。会用到的核心函数包括函数作用SUMPRODUCT数组相乘再求和移动加权平均的核心函数SUM求和用于计算权重之和AVERAGE简单平均用于对比验证STDEV.S计算样本标准差用于安全库存计算NORM.S.INV根据服务水平反算 Z 值用于安全库存FORECAST.ETS指数平滑预测作为进阶参考方法OFFSET动态引用最后 N 行数据版本需要根据你的项目实际情况调整本文示例以常见环境为例重点演示配置思路。2.2 原始数据表结构设计做预测分析前第一步不是写公式而是把原始数据整理规范。很多项目的预测偏差其实不是方法问题而是数据质量问题。建议的原始数据表结构如下字段名类型说明日期日期建议使用每月 1 日格式便于后续分组SKU文本物料编码或商品编码品类文本可选便于分类汇总销量数字该月份该 SKU 的销售数量采购单价数字该月份实际采购单价供应商文本可选用于供应商分析这里要特别注意几点日期列必须是真正的日期格式而不是文本字符串。销量列不能出现“约 100 件”这类带文本的内容。数据中不要留空行和合并单元格。同一 SKU 尽量连续存放后续公式更简单。2.3 用 Excel 表格功能定义数据区域推荐把原始数据区域转成 Excel 表格快捷键 CtrlT。转成表格后公式引用会自动扩展到新数据这在“移动平均”场景下非常实用因为每个月都要追加新数据。选中数据区域后按 CtrlT弹出创建表窗口确认“表包含标题”后点击确定即可。之后在表里新增一行公式区域会自动扩展不需要手动修改引用范围。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.50.7 之间。权重递减幅度不能太激进否则预测值几乎等于上期实际值波动太大也不能太平缓否则就退化成了简单平均。当历史数据量较大时可以把时间窗拉长到 5 期或 6 期权重分布更均匀。常见的一种方案是“等差递减”比如 3 期权重设为 0.6、0.3、0.14 期权重设为 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 月销量。新建工作表按以下结构录入数据ABCD月份实际销量权重系数预测值1月1202月1303月1254月1405月1506月1657月?权重系数可以放在另一个区域比如 H1:H3H1 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 的采购单价在不同批次有波动历史采购记录如下批次采购数量采购单价第 1 批10012.5第 2 批15011.8第 3 批12012.2为了估算下次采购的成本不能简单把三个单价平均而要使用移动加权平均的思路计算“平均采购单价”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.6599% 时 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.6448STDEV.S 计算需求标准差SQRT(7) 把方差按时间扩展。这个结果可以作为安全库存的参考值。5.3 不同服务水平下的安全库存对比服务水平越高安全库存越大缺货风险越低但库存持有成本也越高。企业需要权衡。服务水平Z 值安全库存示例适用场景90%1.28较低非关键物料、可替代性强95%1.65中等常规备件、标准品99%2.33较高关键物料、缺货损失大这部分可以做成 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 列是 SKUB 列是月份C 列是销量。可以新建一个“预测模型”工作表通过 SUMIFS 或 FILTER 函数把指定 SKU 的数据提取出来然后再套用移动加权平均公式。这种方法虽然不如 Python 批量处理高效但在数据量几百行以内时足够使用。7. 常见问题与排查思路问题现象常见原因解决思路预测值始终偏低窗口太长老数据拖累预测缩短移动窗口或提高近期权重预测值波动过大权重过于偏向最近一期降低最近一期权重平滑预测结果SUMPRODUCT 返回 #VALUE!数据区域包含文本或空单元格检查销量列格式清理文本内容追加新数据后公式不更新公式引用了固定区域改用 OFFSET 动态区域或使用 Excel 表格预测值与实际相差很大数据存在季节性或趋势性因素尝试 FORECAST.ETS或调整窗口安全库存计算为 0标准差为 0 或提前期设置错误检查需求波动数据确认提前期单位另一个高频问题是在复制公式时引用区域发生偏移。解决方法是把权重区域用绝对引用固定例如写成$H$1:$H$3这样向下填充时权重区域不会跑偏。8. 最佳实践与工程建议8.1 数据管理规范预测分析的前提是数据可信。建议建立每月数据更新机制专人负责维护销售、采购和库存数据。原始数据表只做记录不要在上面直接写计算公式预测模型单独建工作表所有公式统一维护。这样能避免误删数据导致公式错乱也方便月度复盘。8.2 模型权重与滚动周期调整移动加权平均的权重参数不是一劳永逸的。每隔一段时间建议用最近 3-6 个月的数据做一次回测比较不同权重组合下的平均绝对百分比误差选择误差最小的组合。计算公式AVERAGE(ABS(预测值区域-实际值区域)/实际值区域)如果误差长期偏高说明业务环境已经发生变化单纯的移动加权平均可能不再适用这时要升级到更复杂的预测模型比如指数平滑法、回归分析或者引入外部因素如促销计划、市场行情数据。8.3 生产环境中的注意事项采购预测结果只能作为辅助决策不能替代人工判断。关键物料宁可适当提高安全库存也不要追求零库存导致断供。每次调整权重或窗口参数时保留一份历史版本的 Excel 文件方便对比和回溯。对供应商给出预测需求时建议给一个区间而不是一个单一数值例如“预计 150170 件”让供应商有一定弹性。安全库存计算中的提前期要按实际供应商交期填写不能拍脑袋。可以定期统计供应商平均到货天数用更准确的数字更新模型。最后是一个很实用的习惯每个月做一次“预测复盘会”把上个月的预测值与实际值对比找出偏差原因并记录。持续迭代半年后你手里的这套 Excel 预测模型会比想象中可靠得多。移动加权平均只是一个起点但把最简单的模型用好、用扎实在供应链分析中已经能解决大部分日常问题。