采购与供应链人员在日常工作中最常遇到的场景就是采购价格每个月都在波动供应商交付时快时慢库存有时候积压、有时候断料老板还总问“下个月该备多少货”。这些问题听起来不复杂但落到 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、COUNTIFSExcel 2007 之后基本可用。IFERRORExcel 2007 之后可用低版本请改用IF(ISERROR(...))。XLOOKUPExcel 365 或 Excel 2021 才支持旧版本建议使用VLOOKUP。FORECAST.ETSExcel 2016 之后才支持主要用来做带季节性的时间序列预测。NORM.S.INVExcel 2010 之后可用用于计算安全库存中的服务水平系数。如果你使用的是 WPS 表格大部分基础函数都能用但FORECAST.ETS等较新的动态数组函数可能存在兼容性差异建议先做小范围测试再嵌入到正式模板中。2.2 数据清洗分析前最重要的一步很多 Excel 数据分析项目失败不是函数用错了而是源数据太乱。采购系统的导出数据经常出现以下问题同一物料在表格里有多个名称、供应商名称前后有空格、日期列不是标准日期格式、单价有部分为空、单位有“个”“箱”“件”混用。这些问题如果不处理统计结果会产生偏差。建议在分析之前建立一套数据清洗流程第一日期统一成真正的日期格式不能用文本。选中日期列在“数据”选项卡下使用“分列”功能或者用DATEVALUE函数转换。第二去除文本前后空格。可以用TRIM函数。供应商名称、物料编码这种文本字段尤其需要处理。第三统一单位和编码。如果采购记录里既有“箱”又有“件”需要先建立单位换算关系否则数量汇总会失真。第四处理空值和重复项。数量为空或单价为空的行要标记出来先确认是漏填还是本来就不需要重复的采购单号要去重否则会虚增采购金额。2.3 分析框架从原始数据到决策建议在开始做表之前先确定分析框架避免想到哪算到哪。本文的完整分析链路如下建立采购明细表和出库/销售表字段要规范。使用移动加权平均计算真实采购成本。对供应商交付、价格波动、品类成本做采购数据分析。用历史数据构建需求预测模型。根据预测结果和补货周期计算安全库存与建议采购量。每一步之间是有依赖关系的。采购成本算不准后面分析价格波动就没有依据需求预测不准确安全库存算出来也不可靠。所以做 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.ETSExcel 做需求预测比较常用的方法有三类。第一类是移动平均法用最近 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 中新建一个工作表命名为“采购明细”字段如下采购日期物料编码采购数量采购单价采购金额2025/1/10M001200022.00440002025/3/12M001150024.50367502025/5/08M001300023.0069000需要注意采购金额列建议用公式自动计算而不是手工填写减少出错概率C2*D24.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), 未到货)如果列出的实际到货日期小于等于要求到货日期则视为准时。统计供应商准时交付率可以使用COUNTIFSCOUNTIFS(订单表!$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$100A2, 采购明细!$D$2:$D$100))STDEV.S是样本标准差函数用来衡量价格离散程度。这个公式涉及数组运算在 Excel 中需要按CtrlShiftEnter输入旧版本会看到公式两端多出花括号。如果觉得数组公式麻烦也可以先筛选出某个 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 列放置对应月份月份销量2025/112002025/213502025/312806.2 移动平均法预测在 C 列存放 3 期移动平均预测值。C4 单元格是AVERAGE(B2:B4)这个公式表示用 1 到 3 月的平均值预测 4 月销量用 2 到 4 月的平均值预测 5 月销量。这种方法的优点是对随机波动有平滑作用缺点是存在滞后性如果销量上升趋势明显预测值会偏低。6.3 指数平滑法预测指数平滑法同样需要辅助列。假设平滑系数取 0.3D 列存放平滑值D2 输入B2D3 输入0.3*B30.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.6599% 服务水平对应 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 高频问题汇总问题现象常见原因解决思路移动加权平均单价计算错误数据未按物料编码和日期排序先对物料编码日期升序排序再填充公式SUMIF 结果超出预期条件区域和求和区域没有绝对引用检查起始行是否加上$绝对引用日期无法按月份在数据透视表中分组日期列是文本格式用“分列”把文本日期转为真日期VLOOKUP 返回 #N/A查找值有空格或格式不一致使用 TRIM 清洗文本统一编码格式FORECAST.ETS 返回错误日期序列不等间隔或版本过旧补全缺失日期检查 Excel 版本是否 2016 以上除数为 0 报 #DIV/0!分母累计数量为 0用 IFERROR 包裹公式返回空值8.2 排查方法论遇到分析结果明显不对时不要急着改公式。先按以下顺序排查第一检查源数据。确认是否有重复行、空格、异常值。这一步能解决大多数“结果对不上”的问题。第二检查公式填充范围。下拉填充之后确认每个单元格的区域引用是否符合预期。比如$A$2:A2这种混合引用下拉后终点会变化。第三检查数据排序。移动加权平均对排序要求很高必须在同一 SKU 内按日期排序。第四检查格式。Excel 里“数字变文本”是经典问题单元格左上角的绿色三角标记要特别留意。第五检查版本兼容性。如果你把XLOOKUP或FORECAST.ETS公式发给同事对方电脑版本过低就看不到结果建议使用低版本兼容函数或注明版本要求。9. 最佳实践与学习建议9.1 数据管理规范Excel 做数据分析和预测最基础但最容易被忽视的是数据管理规范。建议做到以下几点文件命名用“项目日期版本”比如“采购成本分析_20250115_v2”原始数据表单独放一个工作表不要直接在原表上改公式“参数表”集中管理平滑系数、服务水平、补货周期等可变参数公式中用参数单元格引用而不是把数字写死在公式里。正式模板上线前一定要用历史数据做一次结果校验。比如把移动加权平均的计算结果与上个月财务给出的库存成本对比差异超过一定比例就要检查原因。9.2 公式设计经验在 Excel 预测分析与供应链分析中公式不要太长也不要在一行公式里嵌套十几层。更推荐的做法是第一多用辅助列。比如计算移动加权平均单价时先算累计数量再算累计金额最后算平均单价每一步单独成一列方便检查哪一步出了问题。第二善用命名区域。选中数据区域后在“公式”选项卡中定义名称比如把“采购明细数量”定义好公式可读性会高很多。第三避免硬编码。不要写B2*0.3C2*0.7这种公式而应该把 0.3 放在参数单元格中用B2*$K$1C2*(1-$K$1)。第四合并单元格会严重影响公式区域引用尽量避免在数据区域使用合并单元格。9.3 后续学习路线如果这篇文章的示例你已经能够独立复现接下来可以沿着以下方向继续深入第一步把数据透视表彻底学透。透视表能把采购分析中的供应商、品类、月份多个维度灵活组合是 Excel 数据分析最核心的技能。第二步学习 Power Query。当 Excel 的数据量接近几万行或者需要反复清洗同一类报表时Power Query 能把清洗步骤固化下来下次直接刷新。第三步学习 Power Pivot 与 DAX。它能处理更大数据量能建立表关系适合做更复杂的供应链计算。第四步如果未来想转向专业数据岗位可以开始学 Python 和 Pandas但要注意Python 是在 Excel 模型基础上进一步提高效率和精度不会 Excel 直接学 Python业务理解容易断层。回到最初的问题用 Excel 做采购与供应链分析其实不复杂难的是把思路理顺把数据规范好再把模型一步步搭起来。希望这篇文章里的移动加权平均、采购成本分析、需求预测和安全库存公式能帮你把 Excel 从“登记工具”变成“分析工具”。你可以直接复制这些公式到自己的表格中测试再根据实际业务字段做调整。如果遇到公式计算或版本兼容的问题欢迎按文章里的排查思路对照检查。