企业官网建设流程全解析

在日常供应链和采购管理工作中，最常被问到的一个问题不是“这个月花了多少钱”，而是“下个月到底该备多少货”。销售说市场要爆发，财务说现金流紧张，仓库说库存已经堆不下了，供应商说下单晚了交期跟不上。所有人都在催一个相对靠谱的数字，而你手里往往只有一堆历史采购记录和发货明细。

这个场景几乎是每个做供应链分析、采购分析的人都会遇到的。很多人第一反应是上复杂系统，比如 SAP、Oracle、专门的需求预测软件，或者写 Python 搭建机器学习模型。但现实是，大多数中小型公司的数据基础并没有那么完善，IT 资源也有限，业务方要的往往是一个能快速迭代、逻辑透明、人人能看懂的预测结果。这时候，Excel 反而是最合适、也最容易被低估的工具。

这篇文章想聊的核心是用 Excel 做供应链场景下的数据预测，重点拆解移动加权平均这个方法。它不花哨，但非常实用，尤其适合采购量预测、库存补货、物流波次参考这类短期需求预测。文章会用一份采购历史数据做完整示例，从数据处理、公式搭建、预测结果验证，到常见误区和工程化建议，尽可能让你看完就能直接套用到自己的表格里。

1. 这篇文章真正要解决的问题

很多供应链分析文章一上来就堆模型，什么 ARIMA、LSTM、随机森林，看起来很专业，但实际业务里根本落不了地。原因很简单：第一，历史数据量不够，很多公司 SKU 级别的有效数据也就几十条；第二，需求波动受促销、断货、季节、政策影响，纯统计模型很难捕捉；第三，业务人员看不懂模型输出，反而不敢用。

移动加权平均解决的是另一个层面的问题：在数据量有限、业务环境不稳定、需要快速给出参考预测时，如何用最少的成本，获得一个相对合理、可解释、可复制的预测值。

它尤其适合这几类场景：

• 采购部门做月度补货计划，需要根据近几个月的实际消耗量推测下月采购量；
• 供应链计划员做滚动需求预测，需要每周更新一次未来四周的需求基线；
• 物流部门做运力准备，需要根据历史发货量预测下周发货峰值；
• 财务或运营做预算滚动更新，需要用实际数据修正之前拍脑袋的指标。

如果你正处于以上任一场景，这篇文章可以帮助你建立一套完整的 Excel 预测分析流程。你不需要会编程，不需要装额外软件，只要有一份干净的历史数据表，就能在十分钟内搭出一个可用的预测模型。

这里需要先说清楚一个判断：移动加权平均不是最聪明的预测方法，但它可能是供应链日常分析中投入产出比最高的方法。它能快速帮你把“拍脑袋”变成“有依据”，而当你发现它的预测精度不够时，也说明你的数据量和业务复杂度已经到了引入更高级模型的阶段，这时再切换 Python 或专业工具也完全不迟。

2. 基础概念：为什么是移动加权平均

2.1 从平均到移动平均

先看最朴素的做法。假设你手上有过去 12 个月某物料的采购量，想预测下个月的采购量，最简单的办法是把 12 个月加起来除以 12，得到一个平均值，用它作为预测。这就是简单算术平均。

这种做法的问题非常明显：三个月前的数据和上个月的数据被一视同仁。如果最近业务增长很快，简单平均会严重低估下个月需求；如果最近在清库存缩减采购，简单平均又会高估。

于是有了移动平均。移动平均不是用全部历史数据，而是只取最近 N 期数据计算平均，并且每推进一期，就“移除”最旧的一期，加入最新的一期，所以叫“移动”。

例如取近 3 个月移动平均来预测下个月：

预测值 = (第 T-2 期实际值 + 第 T-1 期实际值 + 第 T 期实际值) / 3

移动平均的好处是能跟上近期的变化趋势，但仍然是等权重的。距离预测点最近的一个月和三个月前的数据，对预测的贡献完全一样，这显然不够合理。举一个极端例子：某物料最近 3 个月的采购量分别是 100、120、190，明显在快速增长。用简单移动平均预测下个月是 136.67，但直觉告诉我们，下个月大概率比 190 更接近 200 以上，因为最近一个月的强势增长应该对预测有更大影响。

2.2 移动加权平均的核心逻辑

移动加权平均正是为了解决“近期影响更大”的问题。它同样只取最近 N 期数据，但给每一期分配不同的权重，离预测点越近的期数权重越大。

计算公式如下：

加权移动平均预测值 = (W1 * X1 + W2 * X2 + ... + Wn * Xn) / (W1 + W2 + ... + Wn)

其中 X1 到 Xn 是最近 N 期的实际值，W1 到 Wn 是各期对应的权重。最常见的做法是让权重之和等于 1，这样分母就可以省略。

举一个具体例子。取最近 3 期，权重分别为 0.2、0.3、0.5，对应从最远到最近的数据：

预测值 = 100 × 0.2 + 120 × 0.3 + 190 × 0.5 = 20 + 36 + 95 = 151

对比简单移动平均的 136.67，加权移动平均因为给了最近一期更大的权重，预测值向上调整到 151，更贴近当前增长趋势。

2.3 移动加权平均的本质理解

如果只从公式看，移动加权平均像是一个小技巧。但从预测方法论的角度看，它其实是一种非参数的平滑方法：通过权重分配来刻画“近期数据比远期数据更有价值”这一业务常识。

指数平滑法可以看作是加权移动平均的“连续版本”。指数平滑计算时虽然理论上用到了全部历史数据，但权重按指数衰减，越久远的数据权重越小，在实际效果上和移动加权平均非常接近。Excel 里也可以直接用指数平滑工具做预测，后面会提到。

2.4 移动加权平均的适用边界

移动加权平均并不万能。它有几个天然局限：

第一，对趋势的响应有滞后。即使给了最近一期更大权重，它仍然是在“平均”过去的数值，无法预见突然的拐点，比如大客户突然下架、供应商停产、政策变化。

第二，无法处理季节性。如果需求有明显的季节性，比如双十一、春节前囤货、夏季饮料旺销，移动加权平均通常表现不佳。因为权重最高的近期数据，可能只是处在季节性低位或高位。

第三，需要一个预设的 N 和一组权重。N 取 3 还是取 5，权重应该按 0.5/0.3/0.2 还是 0.6/0.3/0.1，都需要根据业务情况调整，没有万能答案。

所以更准确的表述是：移动加权平均适合短期、低波动、无明显季节性的需求预测，适合做基线参考，不适合做突变预测。

3. 环境准备与数据要求

3.1 软件环境

本文的示例全部基于 Excel 完成。需要说明的是，不同版本的 Excel 在函数支持上有差异：

• 基础公式，如 AVERAGE、SUMPRODUCT、IFERROR，适用于 Excel 2010 及以上所有版本；
• FORECAST.ETS 函数，适用于 Excel 2016 及以上版本；
• 数据透视表，适用于全版本。

如果你使用的是 WPS 表格，大部分基础函数也可以通用，但 FORECAST.ETS 可能不支持，建议优先用 SUMPRODUCT 组合公式实现。

版本不是这篇文章重点，关键是公式背后的逻辑。

3.2 数据准备标准

在做预测分析之前，需要把数据整理成标准格式。供应链场景中，最常用的是一维明细表，形如：

理想情况下，这份明细表就是一个标准的一维数据表：每行是一条采购记录，每列是一个维度或度量。

在实际做预测之前，先要进行数据清洗。这里给出一个最小检查清单：

• 日期必须是真正的日期格式，不能是文本；
• 采购数量不能有负数、零值或文本型数字；
• 同一物料在不同供应商、不同仓库的记录是否能合并，取决于你的预测粒度；
• 去除重复记录，避免入库单和采购单重复统计；
• 确认是否有异常大单，比如一次性备货、工程领料，这类数据通常会扭曲预测结果，需要单独标记或剔除。

3.3 确定预测粒度

供应链预测之前，先要想清楚预测的粒度：

• 按物料维度：适合做补货计划；
• 按品类维度：适合做采购预算和供应商谈判；
• 按仓库维度：适合做库容和物流规划；
• 按供应商维度：适合做供应商产能分配。

粒度越细，数据波动越大，预测难度越高。所以第一版预测建议从品类维度开始，先跑通流程，再逐步细化到物料 SKU 维度。

4. 核心流程拆解：用 Excel 实现移动加权平均预测

下面用一份模拟数据完整演示整个过程。假设某公司要对物料编码 M001 的月度采购量做下月预测，手上有 2024 年 1 月到 12 月的月度实际采购量。

4.1 数据处理：按物料、月份汇总

做月度预测前，需要先把采购明细汇总成月份维度。这里可以用数据透视表，也可以用 SUMIFS 函数。

如果用 SUMIFS，公式如下：

=SUMIFS(采购明细!$E:$E, 采购明细!$A:$A, ">="&DATE(2024,1,1), 采购明细!$A:$A, "<"&DATE(2024,2,1), 采购明细!$B:$B, "M001")

如果你希望保留明细数据，并在此基础上生成月度汇总表，一个更简单的做法是：先插入数据透视表，把“日期”拖到行区域并分组为“月”，把“采购数量”拖到值区域，并对“物料编码”做筛选。数据透视表的刷新和数据更新天然联动，适合后续每周更新。

4.2 确定移动期数和权重

移动加权平均有两个参数需要确定：期数 N 和权重 W。

期数 N 的常见选择是 3 或 5。N 越小，预测对近期变化越敏感，但容易受随机波动影响；N 越大，预测越平滑，但对变化的响应越慢。业务上，如果采购周期是每月一次，N=3 通常是比较好的起点；如果数据波动大，可以尝试 N=5。

权重的设计没有绝对标准。常用做法是线性递增权重，比如：

• N=3 时，权重为 0.2、0.3、0.5；
• N=4 时，权重为 0.1、0.2、0.3、0.4；
• N=5 时，权重为 0.1、0.1、0.2、0.2、0.4。

更规范的做法是使用线性权重公式。假设第 i 期（从远到近）权重为 i / (1+2+...+N)，这样最近一期的权重就是 N/(1+2+...+N)。以 N=3 为例，权重分别为 1/6、2/6、3/6，也就是约 0.1667、0.3333、0.5。

这种方式的好处是权重有规律，便于复制到不同的滚动期。

4.3 构建移动加权平均公式

在 Excel 里实现移动加权平均，最核心的函数是 SUMPRODUCT。

示例表结构：

在 D5 单元格（对应 2024-04 的预测）中输入公式：

=SUMPRODUCT($B2:$B4, $C$6:$C$8)

这里有两个细节需要注意：

第一，SUMPRODUCT 要求两个数组尺寸相同。如果权重放在固定的辅助区域，比如 C6:C8，那么 B2:B4 是最近 3 期的实际值，C6:C8 是这 3 期的权重。你需要让同一个公式在往下填充时，B 列区域同步滚动，例如在 D5 时引用 B2:B4，在 D6 时引用 B3:B5。这可以通过相对引用来实现，但前提是下拖时区域起始行一起变化。

为了让公式可以安全下拉，一个更稳妥的写法是使用 OFFSET：

=SUMPRODUCT(OFFSET($B$1,ROW()-4,0,3,1), $C$6:$C$8)

其中 ROW()-4 是为了让随着行号变化，OFFSET 的基准点也动态变化。这个写法稍显复杂，对于新手来说，建议直接把每个预测点的公式写出来，不要用过于复杂的动态引用。比如 D5 写 B2:B4，D6 写 B3:B5，D7 写 B4:B6，虽然繁琐但绝对可控。

第二，如果权重之和等于 1，直接用 SUMPRODUCT 即可；如果权重没有归一化，公式要改成：

=SUMPRODUCT($B2:$B4, $C$6:$C$8) / SUM($C$6:$C$8)

4.4 滚动预测的写法

更符合供应链习惯的做法是，每次预测只使用“截至目前”最近 3 个月的数据，然后用预测值去填充未来一期。换句话说，你并不需要为每个月手动写一个不同时期的公式，只需要在每个月末更新一次数据即可。

以 D5 为例，如果当前已经有 2024-04 的实际数据，想预测 2024-05，D5 就引用 B3:B5（2024-02、2024-03、2024-04）。如果到了 2024-05 月底，想预测 2024-06，D6 就引用 B4:B6。手动维护没有问题，但如果你希望下一次只需要复制公式，可以用一个更结构化的表格：

当然，最好的方式是使用 Excel 的数组公式一次性生成整列滚动预测值。但为了保证初学者可读性，这里不做过度抽象。

5. 完整示例与代码实现

下面给出一份可以直接复制的完整演示。这份演示包含两个部分：第一部分是月度采购汇总表，第二部分是移动加权平均预测表，并计算误差指标。

5.1 准备模拟数据

打开一个新的 Excel 工作表，在 Sheet1 中创建以下数据：

月份 实际采购量 2024-01 4500 2024-02 5200 2024-03 4900 2024-04 6100 2024-05 6800 2024-06 6500 2024-07 7200 2024-08 7800 2024-09 8300 2024-10 8600 2024-11 9100 2024-12 9500

这份数据有明显的上升趋势，适合观察移动加权平均与简单移动平均的差异。

5.2 用 SUMPRODUCT 实现 3 期移动加权平均

在 D 列设置权重辅助区域，然后在 E 列计算预测值。

操作步骤：

1. 在 D2:D4 单元格分别输入 0.2、0.3、0.5；
2. 在 E5 单元格输入下面的公式，并向下填充到 E12：

=SUMPRODUCT($B2:$B4, $D$2:$D$4)

注意：E5 对应的是 2024-03 的预测，它使用 B2:B4（2024-01、2024-02、2024-03）的数据来预测 2024-03 本身。严格来说，预测 2024-03 时不应该包含 2024-03 的实际值，否则就是事后预测。真正的滚动预测应该错开一期。

为了避免这个歧义，建议这样设计：E5 引用 B2:B4 来预测 2024-04。这需要在表格里增加一列“预测对象月份”，或者直接约定 E6 对应当前月预测下月。

为了方便使用，下面给出一个更清晰的表格设计：

E4 公式：

=SUMPRODUCT($B2:$B4, $D$2:$D$4)

E5 公式：

=SUMPRODUCT($B3:$B5, $D$2:$D$4)

也就是说，E列第n行 = SUMPRODUCT(B(n-3):B(n-1), D2:D4)。简单理解就是用前3个月的数据做加权平均，预测下个月。

5.3 使用 FORECAST.ETS 做指数平滑对照

Excel 2016 以上版本自带的 FORECAST.ETS 函数可以自动完成指数平滑预测。它的好处是无需手动分配权重，Excel 内部会做季节性和置信区间的计算。

如果你想把预测值和移动加权平均对照，可以使用：

=FORECAST.ETS(A13, $B$2:$B$12, $A$2:$A$12, 1, 1)

其中：

• A13 是要预测的日期，比如 2025-01；
• B2:B12 是历史实际采购量；
• A2:A12 是历史日期；
• 第三个参数 1 表示季节性周期长度，如果数据无明显季节性，可以设为 1；
• 第四个参数 1 表示是否计算置信区间，设置为 1 会返回区间，设置为 0 只返回点预测。

需要提醒的是，FORECAST.ETS 在数据量小于 3 个周期时会报错或给出警告，不建议在历史数据过少时使用。

5.4 计算预测误差指标

预测结果不能光看数值，必须算误差。供应链预测中最常用的两个指标是 MAE 和 MAPE。

MAE（平均绝对误差）：

=AVERAGE(ABS(E4:E12 - B4:B12))

MAPE（平均绝对百分比误差）：

=AVERAGE(ABS((E4:E12 - B4:B12) / B4:B12))

在 Excel 中输入这两个公式时，需要按 Ctrl+Shift+Enter 确认数组公式，或者在 Excel 365 中直接回车即可。

5.5 用数据透视表做采购成本分析

预测做完之后，通常还要辅助做采购成本分析。这里补充一个数据透视表的经典用法。

假设你有采购明细表，字段包括：采购日期、物料编码、供应商、采购数量、单价、金额。要快速分析每个供应商的采购金额占比和月度采购趋势：

1. 选中明细表任意单元格，点击“插入” → “数据透视表”；
2. 行区域放“供应商”，列区域放“月份”，值区域放“采购金额”；
3. 值字段设置汇总方式为“求和”；
4. 再复制一份透视表，把值区域改为“采购数量”，得到数量维度的供应商分布。

这样你不仅有了预测数字，还能看到供应商集中度、金额变动趋势，这些都是采购分析报告中必须的信息。

6. 运行结果与效果验证

6.1 预期输出结果

按上面 5.2 节的公式运行后，E4:E12 会得到一组预测值。以示例数据（4500、5200、4900、6100、6800、6500、7200、7800、8300、8600、9100、9500）计算，前几个预测值大约是：

从数据可以看到，加权移动平均在上升趋势中会持续滞后于实际值，但比简单移动平均要跟得紧一些。如果你用权重 0.5/0.3/0.2，预测值会更贴近近期，但仍无法做到完全跟随。

6.2 如何判断预测是否成功

判断预测模型好坏，不是看它是否每次都预测准，而是看：

• MAPE 是否在可接受范围内。一般制造业供应链，MAPE 在 10%-20% 算可接受，低于 10% 算不错；
• 预测误差是否稳定。如果误差时大时小，可能说明数据存在异常值或业务有间歇性波动；
• 预测方向是否正确。即使数值偏差大，只要方向（上升/下降）与实际一致，说明模型捕捉到了趋势。

如果发现误差很大，先用直观的折线图对比实际值和预测值，观察是否存在系统性的滞后。移动加权平均最常见的失败模式是持续低于实际值（因为趋势向上），或者持续高于实际值（因为趋势向下）。

6.3 失败排查顺序

如果公式计算结果明显不对，按以下顺序排查：

1. 检查 B 列是否是数值格式，如果单元格左上角有绿色三角，说明是文本型数字，需要转换为数值；
2. 检查权重 D2:D4 之和是否为 1，如果不是，需要用 SUMPRODUCT(...) / SUM(D2:D4)；
3. 检查 SUMPRODUCT 两个数组的区域大小是否一致，一个 3 行一个 2 行会返回 #VALUE! 错误；
4. 检查数据是否按日期升序排列，月份乱序会直接影响预测结果；
5. 如果使用了 FORECAST.ETS，检查日期列是否真正是日期类型，文本日期会导致函数报错。

7. 常见问题与排查思路

7.1 SUMPRODUCT 返回 #VALUE! 错误

最常见原因是两个数组大小不一致，或者其中包含文本。比如 B2:B4 有 3 个数，权重区 D2:D3 只有 2 个数。检查方式：直接选中公式中的两个区域，按 F9 查看计算结果。

7.2 预测值明显偏离实际值

通常是因为权重分配不合理，或者历史数据里有异常值。举例：某个月发生了大批量一次性采购，这个值如果被纳入最近 3 期加权平均，下一期预测就会被不可逆地拉高。解决方式是做数据清洗时把一次性大单单独标记，或者用中位数替换异常值。

7.3 数据不足时如何预测

如果新物料只有 1-2 个月的数据，移动加权平均无法使用。这时可以用同类物料的平均消耗率、供应商建议的 MOQ（最小起订量）、或者销售提供的未来需求计划作为替代预测。

7.4 如何选择 N 和权重更科学

比较推荐的做法是做一个简单的滚动测试：用过去 6 个月的已知数据，分别尝试 N=2、N=3、N=4、N=5 和不同的权重组合，计算各自的 MAPE，选择误差最小的那组参数。Excel 里可以用数据表功能做敏感性分析，也可以用最朴素的手动对比。

7.5 数据透视表刷新后预测值不更新

数据透视表不会自动感知底层数据变化。新增采购记录后，需要右键点击透视表选择“刷新”。如果希望打开文件时自动刷新，可以在 VBA 里加一段 Workbook_Open 事件，或者使用 Power Query，但这不是本文重点。

7.6 移动加权平均与指数平滑怎么选

两者的趋势跟随能力接近。如果你不想手动设置权重，或者数据存在一定季节性，推荐直接用 FORECAST.ETS 或 Excel 数据分析库里的指数平滑工具。如果你希望模型完全可解释、方便审计、并且能向业务方清楚讲明每个数字是怎么来的，移动加权平均更合适。

8. 最佳实践与工程建议

8.1 建立预测模板，而不是每次重写公式

实际项目中，最怕的是每次预测都临时打开一张表，手工拖公式，做完就删。建议把预测过程固化成一个 Excel 模板，包含：

• 数据明细区：粘贴原始采购记录；
• 数据清洗区：用公式自动剔除非数值、异常值；
• 月度汇总区：用数据透视表自动按物料、月份汇总；
• 预测区：用 SUMPRODUCT 或 FORECAST.ETS 计算预测值；
• 误差区：自动计算 MAE、MAPE；
• 图表区：用折线图展示实际值、预测值、误差带。

模板化之后，每周只要粘贴新数据、刷新透视表、复制公式，就能快速得到结果。

8.2 注意数据一致性

如果采购明细中同一个物料有多个单位，有的按“箱”记录，有的按“个”记录，必须先统一单位再做汇总。否则预测结果会在单位换算中失真。

另外，采购量并不等于需求量。如果出现了缺货，采购量可能低于实际需求，预测值会系统性偏低；如果出现了过度采购，采购量会高于需求。如果供应链人员手里有销售订单或发货数据，建议优先用“实际消耗量”或“发货量”做需求预测，采购量只作为参考。

8.3 异常值的处理要有记录

不建议直接删掉异常值，更合理的做法是新增一列“异常标记”，用说明文字记录为什么这个值不能参与预测，比如“工程一次性领料”“供应商月底集中到货”。这样每月审查时能看到来龙去脉，不会因为数据被删导致模型黑箱化。

8.4 用滚动预测替代一次性预测

一次性预测 12 个月很容易失真，因为越远的时间，不确定性越大。更推荐滚动预测：每月更新一次，每次只预测未来 1-3 个月。滚动预测与业务计划、采购执行天然对齐，也更容易在每月 S&OP（销售与运营计划）会议上使用。

8.5 多维度交叉验证

移动加权平均的结果不能直接作为最终的采购指令。建议在预测值基础上，叠加以下因素修正：

• 当前库存与在途库存；
• 安全库存水平；
• 供应商交期；
• 促销计划或已知的大客户需求变化；
• 物料生命周期阶段（新品、成熟品、退市品）。

也就是说，移动加权平均给的是一个“基线”，不是“结论”。真正下单时要在这个基线上做判断。

8.6 权限与数据安全

如果这份预测数据涉及核心采购成本或多部门共享，建议把明细数据和预测区域分别放在不同工作表，并设置工作表保护，防止误改公式。生产环境中涉及真实业务数据导出，也要注意权限管理和脱敏处理，尤其是涉及供应商价格、采购金额时，不能随意公开。

9. 总结与后续学习方向

Excel 做供应链预测分析，真正的难点不在于公式本身，而在于对业务数据的理解。移动加权平均这个工具，本质上是在用最朴素的方式表达一个业务常识：近期的需求比远期的需求更能代表未来的走势。它帮你摆脱纯拍脑袋，同时又不至于让模型复杂到无法解释。

这篇文章带你完成了一条完整链路：从采购明细数据清洗，到月度汇总，再到用 SUMPRODUCT 实现移动加权平均预测，最后用 MAE 和 MAPE 验证误差。这套方法可以套用到采购分析、库存补货、物流量参考、财务预算滚动等场景。对于物料维度、供应商维度的预测，只需把数据透视表的分组字段换一下即可，核心逻辑不变。

如果你想继续深入，下一步可以从三个方向展开：

第一，学习指数平滑法的原理，理解它与移动加权平均的关系，以及 Excel 的 FORECAST.ETS 内部机制；第二，学会用 Python 的 pandas 和 statsmodels 处理更大规模的供应链数据，比如上千个 SKU 的批量预测；第三，研究需求预测中常见的季节性分解、趋势调整、安全库存计算，逐步搭建一套完整的供应链计划体系。

对于当下，建议你先把自己手头最常用的一两个物料跑通这个流程，积累两到三个月的数据和误差记录。你会发现，预测精度提升的关键，不是换更复杂的模型，而是持续收集干净的数据和验证每一次预测偏差的原因。数据质量上来了，Excel 里的这个朴素模型也能撑起日常决策。