Excel供应链分析实战：从采购成本到需求预测与安全库存

采购与供应链人员在日常工作中，最常遇到的场景就是：采购价格每个月都在波动，供应商交付时快时慢，库存有时候积压、有时候断料，老板还总问“下个月该备多少货”。这些问题听起来不复杂，但落到 Excel 表格里，真正能把数据算清楚、把趋势预测出来的人却不多。很多文章讲到 Excel 预测分析，要么只讲单个函数，要么直接用 Python，对做采购和供应链的人来说，落地门槛还是不低。

这篇文章会围绕 Excel 供应链分析展开，用采购数据分析、采购成本分析、需求预测、安全库存与补货作为主线，完整演示从数据清洗、公式建模到报表输出的全过程。文章会重点讲解移动加权平均的计算思路，也会给出移动平均法、指数平滑法、FORECAST.ETS 函数的具体用法。所有示例都可以直接复制到 Excel 中运行，适合采购专员、供应链计划、运营分析和零基础想转行做数据分析的读者。

1. 为什么采购与供应链分析离不开 Excel 预测

1.1 采购分析、供应链分析与数据预测的关系

先来统一一下概念。很多人会把“采购分析”“供应链分析”“数据分析”当成三个独立的事情，其实它们是一条链路里的不同环节。

采购分析聚焦在“买东西”这件事上，包括采购价格是否合理、供应商报价有没有异常、不同品类的采购金额占比是多少、哪些供应商交付经常延期。供应链分析的范围更大，除了采购，还要看库存周转、物流时效、订单满足率、安全库存是否充足。数据预测则是给前两者提供“未来视角”，比如根据历史采购数据预测未来需求，根据价格上涨趋势判断是否要提前锁价。

三者的关系可以理解为：采购分析解决“当前买贵没有”，供应链分析解决“库存结构是否健康”，数据预测解决“未来该买多少、什么时候买”。Excel 在这三个场景里都能发挥很大作用，因为它的函数体系对采购和供应链场景支持得比较完整，不需要写复杂代码也能建出可用的模型。

1.2 为什么选择 Excel 而不是只依赖 ERP 或 BI 工具

很多公司上了 ERP 系统，但采购人仍然离不开 Excel，原因很简单：ERP 里的报表大多是固定的，字段、维度、时间粒度都写死了。你想按 SKU 维度看移动加权平均成本，想按供应商维度看价格波动率，想在报表里临时加一个预测列，这些操作在 ERP 里往往要提需求、排期、开发，周期很长。

BI 工具比如 Power BI、Tableau 功能很强，但有学习成本，也不是所有同事都会用。Excel 的优势在于：数据透视表可以快速做多维分析，函数公式可以灵活建模，图表可以随数据自动更新，而且绝大多数业务人员已经具备一定基础。

当然，Excel 也有短板。它不适合处理几十万行以上的大数据量，多人协同容易出版本冲突，复杂预测模型的精度也比不上专业工具。所以更合理的思路是：把 Excel 作为日常分析和临时建模的工具，把确定下来的报表方案再固化到正式系统里。

1.3 核心概念提前讲：移动加权平均、需求预测、安全库存

文章中会反复用到几个核心概念，这里先做一个通俗解释。

移动加权平均是采购成本分析里非常重要的概念。它的意思是：每次采购入库后，把原来的库存数量和新的入库数量加起来，用总金额除以总数量，得到一个新的平均单价。这个单价会随着每次采购变化，能更真实地反映当前库存成本，而不是简单地把所有采购价格做算术平均。

需求预测是基于历史销售或出库数据，对未来的需求数量进行估计。它并不要求预测得绝对准确，而是希望通过一定的方法缩小误差，减少缺货和积压的风险。

安全库存是为了应对需求波动和供应商补货周期不稳定而额外准备的库存量。它不是拍脑袋定的，而是可以通过公式计算的，后面会用 Excel 公式演示具体方法。

2. 环境准备与分析思路

2.1 Excel 版本与函数兼容性

本文示例使用 Excel 2016 及以上版本，如果你用的是 Microsoft 365，体验会更好。这里要特别说明版本问题，因为部分函数对版本有要求。

• SUMIFS、AVERAGEIFS、COUNTIFS：Excel 2007 之后基本可用。
• IFERROR：Excel 2007 之后可用，低版本请改用IF(ISERROR(...))。
• XLOOKUP：Excel 365 或 Excel 2021 才支持，旧版本建议使用VLOOKUP。
• FORECAST.ETS：Excel 2016 之后才支持，主要用来做带季节性的时间序列预测。
• NORM.S.INV：Excel 2010 之后可用，用于计算安全库存中的服务水平系数。

如果你使用的是 WPS 表格，大部分基础函数都能用，但FORECAST.ETS等较新的动态数组函数可能存在兼容性差异，建议先做小范围测试再嵌入到正式模板中。

2.2 数据清洗：分析前最重要的一步

很多 Excel 数据分析项目失败，不是函数用错了，而是源数据太乱。采购系统的导出数据经常出现以下问题：同一物料在表格里有多个名称、供应商名称前后有空格、日期列不是标准日期格式、单价有部分为空、单位有“个”“箱”“件”混用。这些问题如果不处理，统计结果会产生偏差。

建议在分析之前建立一套数据清洗流程：

第一，日期统一成真正的日期格式，不能用文本。选中日期列，在“数据”选项卡下使用“分列”功能，或者用DATEVALUE函数转换。

第二，去除文本前后空格。可以用TRIM函数。供应商名称、物料编码这种文本字段尤其需要处理。

第三，统一单位和编码。如果采购记录里既有“箱”又有“件”，需要先建立单位换算关系，否则数量汇总会失真。

第四，处理空值和重复项。数量为空或单价为空的行要标记出来，先确认是漏填还是本来就不需要；重复的采购单号要去重，否则会虚增采购金额。

2.3 分析框架：从原始数据到决策建议

在开始做表之前，先确定分析框架，避免想到哪算到哪。本文的完整分析链路如下：

1. 建立采购明细表和出库/销售表，字段要规范。
2. 使用移动加权平均计算真实采购成本。
3. 对供应商交付、价格波动、品类成本做采购数据分析。
4. 用历史数据构建需求预测模型。
5. 根据预测结果和补货周期计算安全库存与建议采购量。

每一步之间是有依赖关系的。采购成本算不准，后面分析价格波动就没有依据；需求预测不准确，安全库存算出来也不可靠。所以做 Excel 供应链分析时，建议按顺序推进，先搭建底层数据表，再逐步增加分析层和决策层。

3. Excel 预测分析核心函数与方法

3.1 统计汇总函数：分析的基础

在 Excel 采购分析和供应链分析中，SUMIFS、AVERAGEIFS、COUNTIFS这三类函数是使用频率最高的。它们都可以按多个条件进行汇总计算，非常适合处理多维度的业务数据。

以采购明细表为例，如果我们要统计某个供应商在某个时间段内的采购总额，可以用下面的公式：

=SUMIFS(采购明细表!F:F, 采购明细表!D:D, "供应商A", 采购明细表!A:A, ">="&DATE(2025,1,1), 采购明细表!A:A, "<="&DATE(2025,12,31))

这里的F列是采购金额，D列是供应商名称，A列是采购日期。SUMIFS先按供应商名称匹配，再按日期区间筛选，最终得到满足条件的金额合计。同理，AVERAGEIFS可以计算平均单价，COUNTIFS可以统计采购次数。

这类函数看起来简单，但很容易犯区域长度不匹配的错误，比如条件区域写了 1000 行，求和区域却写了 500 行，结果会返回#VALUE!。写公式时所有区域必须保持同样的行范围。

3.2 移动加权平均：核心计算逻辑

Excel 没有现成的“移动加权平均”函数，但可以通过累计数量和累计金额来计算。移动加权平均的基本逻辑是：

每次采购入库后，新的平均单价 = （原有库存总金额 + 本次采购金额） ÷ （原有库存数量 + 本次采购数量）。

在 Excel 中，需要用SUMIF函数实现“按 SKU 从第一行累计到当前行”的效果。假设 A 列是物料编码，C 列是入库数量，D 列是入库金额，则累计数量公式和累计金额公式分别如下：

=SUMIF($A$2:A2, A2, $C$2:C2)

=SUMIF($A$2:A2, A2, $D$2:D2)

然后用累计金额除以累计数量，就是当前批次下的移动加权平均单价：

=SUMIF($A$2:A2, A2, $D$2:D2) / SUMIF($A$2:A2, A2, $C$2:C2)

这里的核心技巧是把区域起点固定为$A$2，终点跟随当前行变化，这样向下填充时，每个 SKU 都能从第一条记录累计到当前记录。需要特别注意两点：一是数据必须先按物料编码和日期排序，否则累计逻辑会乱；二是如果采购明细表里同时有入库和出库，还需要单独处理出库对数量的影响，后面实战部分会展开。

3.3 趋势预测函数：移动平均、指数平滑、FORECAST.ETS

Excel 做需求预测比较常用的方法有三类。

第一类是移动平均法，用最近 N 期的平均值作为下一期的预测值。假设历史销量存放在 B2:B13，则 3 期移动平均的公式是：

=AVERAGE(B2:B4)

移动平均法的优点是很简单，缺点是对趋势变化反应慢，适合需求比较稳定的物料。

第二类是指数平滑法。它给近期数据更高的权重，权重按指数递减，公式可以写成：

=平滑系数 * 本期实际值 + (1 - 平滑系数) * 上一期预测值

在 Excel 中需要借助辅助列来实现递归计算。比如在 D 列存放平滑预测值，D2 填第一期实际值，D3 开始写：

=0.3*B3 + 0.7*D2

这里的 0.3 是平滑系数，系数越大，预测结果对近期变化越敏感。

第三类是FORECAST.ETS，它是 Excel 2016 之后新增的时间序列预测函数，可以自动识别历史数据中的季节性和趋势。格式如下：

=FORECAST.ETS(目标日期, 历史数值区域, 历史日期区域)

例如预测下一期的销量：

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

其中A14是要预测的下一期日期，B2:B13是历史销量，A2:A13是对应的日期序列。这个函数要求日期间隔保持一致，如果中间有缺失月份，最好先补全再预测。

3.4 错误处理与查找引用函数

预测分析过程中，经常遇到除数为零、查找不到对应值、源数据为空等情况。这些错误如果不处理，会导致整个报表出现难看的#DIV/0!或#N/A。

IFERROR是最常用的错误处理函数，用法是：

=IFERROR(原公式, 出错时返回的内容)

比如计算移动加权平均单价时，如果某个 SKU 没有入库记录，分母为 0，可以写成：

=IFERROR(SUMIF($A$2:A2, A2, $D$2:D2) / SUMIF($A$2:A2, A2, $C$2:C2), "")

另一个常用的是VLOOKUP，用于跨表匹配数据。比如根据物料编码从价格表中匹配最新采购单价：

=VLOOKUP(A2, 价格表!$A:$C, 3, FALSE)

XLOOKUP功能更强，但旧版本不支持，所以正文示例仍以VLOOKUP为主。查找类函数最常见的坑是“数据区域第一列不是查找值所在列”以及“查找值前后有空格”，这两个问题后面会在常见问题里再展开。

4. 实战一：移动加权平均与采购成本分析

4.1 业务场景

假设你负责一种电子料的采购。这种料一年内采购了 10 次，每次的采购单价波动较大，从 21 元到 26 元不等。月底库存盘点时，财务需要确定库存成本，老板也想知道“当前平均买入成本到底是多少”。

如果用算术平均，直接把 10 次采购单价加起来除以 10，这个结果忽略了每次采购数量不同的问题。比如某次单价 26 元但只买了 100 个，另一次单价 22 元但买了 5000 个，算术平均显然会高估成本。这时候就需要用移动加权平均。

4.2 创建采购明细表

在 Excel 中新建一个工作表，命名为“采购明细”，字段如下：

需要注意，采购金额列建议用公式自动计算，而不是手工填写，减少出错概率：

=C2*D2

4.3 实现移动加权平均计算公式

接下来在 E、F、G 列分别增加辅助字段。

E 列“累计数量”：

=SUMIF($B$2:B2, B2, $C$2:C2)

F 列“累计金额”：

=SUMIF($B$2:B2, B2, $E$2:E2)

等等，这里有一个细节需要说明。如果直接对“采购金额”列做 SUMIF，那么遇到出库记录时无法反映库存减少。但在只有采购入库记录的简化场景中，直接用采购金额列做累计是可行的。更严谨的做法是判断当前行是入库还是出库，分别处理，这需要引入“出入库类型”字段。

考虑到文章主要面向采购分析，我们这里的案例以“入库流水”为例，先用累计采购数量和累计采购金额计算移动加权平均单价：

H 列“移动加权平均单价”：

=F2/E2

为了保持公式稳健，可以加上IFERROR包裹：

=IFERROR(F2/E2, "")

公式的效果是：第一行采购后，平均单价就是第一批单价；第二行采购后，系统会把两批数量和金额合并计算；第三行采购后继续更新。最终每一行都体现“截至当前批次的最新平均成本”。

4.4 结果解读与应用

表格填充完成后，最后一次采购对应的移动加权平均单价，就是当前库存成本的最佳参考值。

假设最终计算结果为 23.18 元，那么：

• 库存盘点时，可以用 23.18 元乘以现有库存数量得到库存金额。
• 当供应商再次报价时，如果报价显著高于当前移动加权平均成本，需要重点分析原因，比如原材料涨价、加工费调整、采购批量减少等。
• 如果财务要求做成本结转，移动加权平均单价也直接可用。

同时可以插入一张折线图，把每次采购单价和移动加权平均单价放在同一张图中。两条线的差距能很直观地反映价格上涨和下跌的幅度，帮助采购判断价格走势。

5. 实战二：供应商绩效与品类成本分析

5.1 供应商准时交付率统计

供应商绩效分析通常是采购数据分析里比较高阶的部分，但其实用 Excel 就可以落地。比较常用的是“准时交付率”，即供应商按时到货的订单数占全部订单数的比例。这个指标需要两张表：订单表和到货表。

订单表包含：采购订单号、SKU、供应商、下单日期、要求到货日期。

到货表包含：采购订单号、SKU、实际到货日期、到货数量。

通过VLOOKUP把实际到货日期匹配到订单表中，然后判断是否准时。

=IFERROR(VLOOKUP(A2, 到货表!$A:$D, 4, FALSE), "未到货")

如果列出的实际到货日期小于等于要求到货日期，则视为准时。统计供应商准时交付率可以使用COUNTIFS：

=COUNTIFS(订单表!$C:$C, "供应商A", 订单表!$F:$F, "准时") / COUNTIFS(订单表!$C:$C, "供应商A")

这里F列是根据准时判断生成的辅助列。准时率低于 90% 的供应商，在制定下季度采购计划时要重点关注其交付风险。

5.2 采购价格波动分析

采购价格波动分析的核心是判断某类物料的价格稳定性。可以从两个维度来看：一是相同 SKU 在不同采购批次中的价格差异，二是相同 SKU 在不同供应商之间的报价差异。

使用AVERAGEIFS计算某 SKU 的历史平均采购单价：

=AVERAGEIFS(采购明细!$D:$D, 采购明细!$B:$B, A2)

然后计算标准差：

=STDEV.S(IF(采购明细!$B$2:$B$100=A2, 采购明细!$D$2:$D$100))

STDEV.S是样本标准差函数，用来衡量价格离散程度。这个公式涉及数组运算，在 Excel 中需要按Ctrl+Shift+Enter输入，旧版本会看到公式两端多出花括号。如果觉得数组公式麻烦，也可以先筛选出某个 SKU 的子表，再计算标准差。标准差越大，说明价格越不稳定，采购锁价的优先级就越高。

5.3 品类成本 ABC 分类

ABC 分类法也叫帕累托分析，核心思想是少数物料贡献了大多数采购金额。具体做法是：先按采购金额对物料品类降序排序，然后计算累计采购金额和累计占比。

假设采购汇总表中 A 列是品类，B 列是采购金额，C 列是累计金额：

=SUM($B$2:B2)

D 列是累计占比：

=C2/SUM($B$2:$B$100)

累计占比在 0% - 70% 的属于 A 类物料，70% - 90% 属于 B 类，90% - 100% 属于 C 类。A 类物料品种少、金额大，是采购谈判和库存管理的重点；C 类物料品种多但金额小，可以考虑简化采购流程。

6. 实战三：用 Excel 构建需求预测模型

6.1 数据准备与预测思路

需求预测不能拿全公司所有 SKU 混合预测，这会导致个别高销量物料被低销量物料淹没。正确做法是拆分成单一 SKU，并且按月度汇总历史出库或销售数据。月份作为时间轴，数量作为预测对象。

在 B 列放置最近 12 个月的历史销量，A 列放置对应月份：

6.2 移动平均法预测

在 C 列存放 3 期移动平均预测值。C4 单元格是：

=AVERAGE(B2:B4)

这个公式表示用 1 到 3 月的平均值预测 4 月销量，用 2 到 4 月的平均值预测 5 月销量。这种方法的优点是对随机波动有平滑作用，缺点是存在滞后性，如果销量上升趋势明显，预测值会偏低。

6.3 指数平滑法预测

指数平滑法同样需要辅助列。假设平滑系数取 0.3，D 列存放平滑值：

D2 输入：

=B2

D3 输入：

=0.3*B3+0.7*D2

向下填充后，D 列最后一行的值就是下一期预测值。这里的 0.7 是上一期平滑值的权重。平滑系数越大，预测值对最近需求变化越敏感；越小则越平滑。

实际使用中，0.2 到 0.5 是比较常见的区间，具体数值可以通过对比历史预测误差来调整。

6.4 FORECAST.ETS 函数预测

如果数据存在明显的季节性，比如电商大促月份销量明显高于平时，推荐使用FORECAST.ETS。

假设 A2:A13 是月份，B2:B13 是销量，第 13 个月份放在 A15，那么预测公式是：

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

FORECAST.ETS会自动识别趋势和季节性，并返回下一期的预测值。需要注意的是：该函数要求日期序列是等间隔的，如果中间缺了一个月，建议先用AVERAGE补齐或用“数据透视表”分组填充空月，再执行预测。

6.5 预测误差评估与结果解释

预测模型不能只看一个预测数字，还要评估误差。常见的误差指标有两个。

MAD（平均绝对偏差）用来衡量预测值与实际值的平均差距：

=AVERAGE(ABS(B2:B13 - C2:C13))

MAPE（平均绝对百分比误差）用来衡量误差占比：

=AVERAGE(ABS((B2:B13 - C2:C13)/B2:B13))

实际工作中，MAPE 低于 15% 说明预测精度比较好，15% 到 30% 属于可接受范围，超过 30% 则建议重新选择模型或检查基础数据。

7. 实战四：安全库存与补货建议计算

7.1 安全库存逻辑

安全库存的计算依赖两个不确定性：需求波动和补货周期波动。需求波动可以用历史需求的标准差来衡量，补货周期波动则用采购提前期的标准差来衡量。

常规公式是：

安全库存 = 服务水平系数 × 需求标准差 × 开根号(平均补货周期)

服务水品系数 Z 与缺货风险有关，95% 服务水平对应 Z 约为 1.65，99% 服务水平对应 Z 约为 2.33。

7.2 Excel 公式实现

假设每日需求数据在 B 列，平均补货周期为 7 天，需求标准差公式为：

=STDEV.S(B2:B31)

对应的安全库存公式为：

=ROUND(NORM.S.INV(0.95) * STDEV.S(B2:B31) * SQRT(7), 0)

其中NORM.S.INV(0.95)返回 95% 服务水平下的 Z 值，SQRT(7)是补货周期天数的平方根。计算结果表示：为了应对需求波动，需要在正常库存基础上额外准备这么多库存。

7.3 补货建议与订货点

订货点的含义是：当库存降到这个数量时，就需要触发采购补货。计算公式是：

订货点 = 平均每日需求量 × 平均补货周期 + 安全库存

假设平均日需求量为 100 件，平均补货周期为 7 天，安全库存为 230 件，则订货点为：

=100 * 7 + 230

结果为 930 件，也就是说当前库存低于 930 件时，就应该下采购订单。建议采购数量一般根据预测未来的需求总量，再减去当前库存和在途订单量：

=预测下月需求总量 - 当前库存 - 在途订单量

补货建议计算完成后，可以继续做成一张带条件格式的库存预警表，库存低于订货点自动标黄，低于安全库存自动标红。

8. 常见问题与排查思路

8.1 高频问题汇总

8.2 排查方法论

遇到分析结果明显不对时，不要急着改公式。先按以下顺序排查：

第一，检查源数据。确认是否有重复行、空格、异常值。这一步能解决大多数“结果对不上”的问题。

第二，检查公式填充范围。下拉填充之后，确认每个单元格的区域引用是否符合预期。比如$A$2:A2这种混合引用，下拉后终点会变化。

第三，检查数据排序。移动加权平均对排序要求很高，必须在同一 SKU 内按日期排序。

第四，检查格式。Excel 里“数字变文本”是经典问题，单元格左上角的绿色三角标记要特别留意。

第五，检查版本兼容性。如果你把XLOOKUP或FORECAST.ETS公式发给同事，对方电脑版本过低就看不到结果，建议使用低版本兼容函数或注明版本要求。

9. 最佳实践与学习建议

9.1 数据管理规范

Excel 做数据分析和预测，最基础但最容易被忽视的是数据管理规范。

建议做到以下几点：文件命名用“项目+日期+版本”，比如“采购成本分析_20250115_v2”；原始数据表单独放一个工作表，不要直接在原表上改公式；“参数表”集中管理平滑系数、服务水平、补货周期等可变参数，公式中用参数单元格引用，而不是把数字写死在公式里。

正式模板上线前，一定要用历史数据做一次结果校验。比如把移动加权平均的计算结果与上个月财务给出的库存成本对比，差异超过一定比例就要检查原因。

9.2 公式设计经验

在 Excel 预测分析与供应链分析中，公式不要太长，也不要在一行公式里嵌套十几层。更推荐的做法是：

第一，多用辅助列。比如计算移动加权平均单价时，先算累计数量，再算累计金额，最后算平均单价，每一步单独成一列，方便检查哪一步出了问题。

第二，善用命名区域。选中数据区域后，在“公式”选项卡中定义名称，比如把“采购明细数量”定义好，公式可读性会高很多。

第三，避免硬编码。不要写=B2*0.3+C2*0.7这种公式，而应该把 0.3 放在参数单元格中，用=B2*$K$1+C2*(1-$K$1)。

第四，合并单元格会严重影响公式区域引用，尽量避免在数据区域使用合并单元格。

9.3 后续学习路线

如果这篇文章的示例你已经能够独立复现，接下来可以沿着以下方向继续深入：

第一步，把数据透视表彻底学透。透视表能把采购分析中的供应商、品类、月份多个维度灵活组合，是 Excel 数据分析最核心的技能。

第二步，学习 Power Query。当 Excel 的数据量接近几万行，或者需要反复清洗同一类报表时，Power Query 能把清洗步骤固化下来，下次直接刷新。

第三步，学习 Power Pivot 与 DAX。它能处理更大数据量，能建立表关系，适合做更复杂的供应链计算。

第四步，如果未来想转向专业数据岗位，可以开始学 Python 和 Pandas，但要注意：Python 是在 Excel 模型基础上进一步提高效率和精度，不会 Excel 直接学 Python，业务理解容易断层。

回到最初的问题：用 Excel 做采购与供应链分析，其实不复杂，难的是把思路理顺，把数据规范好，再把模型一步步搭起来。希望这篇文章里的移动加权平均、采购成本分析、需求预测和安全库存公式，能帮你把 Excel 从“登记工具”变成“分析工具”。你可以直接复制这些公式到自己的表格中测试，再根据实际业务字段做调整。如果遇到公式计算或版本兼容的问题，欢迎按文章里的排查思路对照检查。