如果你每天要和采购订单、库存水位、供应商报价、物流到货周期打交道那么这篇内容可以直接收藏。Excel 在供应链管理里的角色并不是“做个表”而是一套可以落地跑的预测与成本分析工具。这次我们不聊泛泛的数据分析概念直接用 Excel 公式和数据透视表把需求预测、移动加权平均、采购成本分析、库存与物流分析串成一条可执行的流程。这篇文章不是纯函数字典而是按供应链分析的真实工作顺序来写从历史数据整理开始到移动加权平均预测再到采购成本、库存周转与物流交付分析最后是可视化看板和常见排错。全文不依靠 Python 或专业 BI 工具只用 Excel 自带函数和内置功能适合采购、计划、物流、供应链运营岗位的同事直接套用。1. Excel 供应链预测分析核心能力速览能力项说明主要功能需求预测、移动加权平均、采购成本分析、库存周转、物流交付分析、数据透视表与可视化看板核心方法简单移动平均、加权移动平均、指数平滑、线性趋势预测、ABC 分类、绩效对比使用门槛会基本 Excel 操作即可不需要编程推荐版本Microsoft 365 / Excel 2019 / Excel 2016需要 FORECAST.ETS 函数时建议 2016 以上数据规模适合几千到几十万行的业务数据超大规模建议转专业 BI输出形式表格、公式计算结果、数据透视表、折线图、帕累托图、瀑布图批量任务通过公式下拉、数据透视表刷新、模板化表格实现快速复用接口 API不涉及Excel 可直接连接数据库或导入 CSV/文本文件也支持 Power Query 做数据清洗适合场景月度需求预测、采购价格跟踪、供应商份额分析、库存健康度评估、到货周期监控不适合场景超大规模数据挖掘、非结构化文本分析、复杂的多变量机器学习预测从实际业务角度看Excel 做预测分析的核心价值不是“代替专业预测软件”而是在数据量可控的情况下用最低成本建立一套可追溯、可复算、可交接的预测流程。下面的内容会围绕这个目标展开。2. 适用场景与使用边界2.1 适合用 Excel 的供应链分析场景第一类是需求预测。比如你有过去 24 个月的销售或发货记录想预测未来 2 到 3 个月的用量移动加权平均和指数平滑往往比拍脑袋准确。第二类是采购成本分析。原材料价格经常波动可以用移动加权平均平滑月与月之间的价格跳动再结合 SUMIFS 分类统计不同品类的采购金额、采购数量、供应商份额。第三类是库存与物流分析。库存周转天数、呆滞库存金额、平均到货周期、订单准时交付率这些指标都可以用 SUMIFS、AVERAGEIFS、日期计算和数据透视表做出来。2.2 使用边界Excel 并不适合所有场景。当数据量超过几十万行或者你需要做多因素回归、季节性复杂识别、实时数据流分析时建议换用专业 BI 或 Python 库。另外Excel 的预测模型默认对规律性历史数据效果较好如果业务存在重大市场突变、政策调整或断供风险模型预测只能作为参考不能替代人工判断。这里还要强调一个合规边界采购单价、供应商合同价格、库存余额这些信息通常属于企业内部敏感数据。用 Excel 分析时要注意文件权限控制不要随意共享包含完整供应商价格表的整个工作簿对外输出时做好脱敏。涉及第三方数据时确保数据来源合法。3. 环境准备与数据整理3.1 Excel 版本与基础设置推荐使用 Microsoft 365 或 Excel 2016 以上版本。因为后面要提到 FORECAST.ETS 函数这个函数在旧版本里不可用。打开 Excel 后先做两个基础设置文件 - 选项 - 公式 - 工作簿计算 - 自动计算确认是“自动计算”模式否则修改数据后预测结果不会立即更新。3.2 原始数据表的规范做供应链分析前先把原始数据整理成一维表结构。数据透视表、SUMIFS、预测函数都要求数据是“长表”而不是“宽表”。推荐的字段结构如下日期 物料编码 物料名称 品类 数量 金额 供应商 到货日期 2024-01-05 M001 R22制冷剂 原材料 100 4800 A供应商 2024-01-12 2024-01-08 M002 包装纸箱 包材 500 1250 B供应商 2024-01-15为什么要强调这个格式因为绝大多数 Excel 分析函数和透视表都要求每一行是一条业务记录每一列是一个维度或度量。如果你把日期横着放、物料竖着放后面的预测公式会非常难写。3.3 日期字段的处理日期不能是文本。Excel 中日期的本质是数字如果你发现单元格左上角有绿色三角或者用ISNUMBER(A2)返回 FALSE说明日期是文本。批量处理日期文本的方法是选中日期列点击“数据”选项卡里的“分列”按分隔符或固定宽度进入步骤在第三步选择“日期(YMD)”并设为目标格式。这样文本日期就会转换成真正的日期序列。3.4 数据表转为结构化表格选中有数据的区域按CtrlT创建表格。这有三个好处新数据插入后公式自动扩展。SUMIFS、AVERAGEIFS 可以直接引用“表名”和“列名”公式可读性更强。数据透视表的数据源范围会随表格自动变大新增月份不需要手动改范围。给表格命名也很重要。例如选中整张表后在“表设计”里把它命名为tbl_采购记录。后续公式可以写成SUMIFS(tbl_采购记录[金额], tbl_采购记录[品类], 原材料)这种写法比SUMIFS(C:C, D:D, 原材料)更稳定不会因为插入列导致公式引用错位。4. 移动加权平均预测模型搭建移动加权平均是供应链需求预测中非常实用的一种方法。它的逻辑是用最近 N 期数据的加权平均值来预测下一期越靠近当前的时间点权重越大从而让预测结果对趋势变化更敏感。4.1 移动加权平均的计算逻辑假设你有最近 3 个月的实际需求量月份 需求量 1月 100 2月 110 3月 105如果做简单移动平均预测 4 月为(100 110 105) / 3 105如果做加权移动平均给最近月份更大权重预测值 100 × 0.2 110 × 0.3 105 × 0.5 105.5这里 0.2、0.3、0.5 是权重权重之和等于 1数值越近权重越大。为什么要这么做因为简单移动平均对趋势反应太慢如果数据一直在涨简单平均会明显低估下一期加权移动平均可以滞后性小一些。4.2 Excel 实现加权移动平均第一种方式是使用 SUMPRODUCT 函数。假设按时间升序排列历史需求量在 B2:B13最近 3 期权重放在 D2:D4SUMPRODUCT(B11:B13, D2:D4)需要注意的是权重区域 D2:D4 应该按“从远到近”对应 B11:B13也就是 D2 对应 B11D3 对应 B12D4 对应 B13。如果权重之和不是 1应该再除以 SUM(D2:D4)。更稳妥的写法是SUMPRODUCT(B11:B13, D2:D4) / SUM(D2:D4)第二种方式是使用 OFFSET 实现动态滚动。当历史数据不断增加手动调整 B11:B13 的引用范围很容易出错。可以这样写SUMPRODUCT(OFFSET(B2, COUNT(B2:B100) - 3, 0, 3, 1), D2:D4) / SUM(D2:D4)这段公式的逻辑是用 COUNT(B2:B100) 统计非空数据个数减去 3 得到最后三行的偏移量再从那个位置开始取 3 行 1 列。这样每次新增一行数据公式会自动滚动到最新 3 期。4.3 简易移动平均作为对照组建议不要只做一个预测值而是同时做简单移动平均和加权移动平均方便对比。简单移动平均公式AVERAGE(B9:B12)这里同样可以用 OFFSET 动态扩展。为了判断哪个模型更准可以在历史数据上做“回测”。所谓回测就是假设你知道 1 月到 4 月的真实数据用前 3 个月预测 4 月然后拿预测值和 4 月真实值对比。把所有历史月份的预测误差算出来再比较误差大小这样才能选出更适合当前业务的预测方法。4.4 三个常用预测误差指标在 Excel 里建立三列公式绝对误差 ABS(实际值 - 预测值) 绝对百分比误差 ABS((实际值 - 预测值) / 实际值) 误差平方 (实际值 - 预测值) ^ 2然后计算MAE AVERAGE(绝对误差列) MAPE AVERAGE(绝对百分比误差列) RMSE SQRT(AVERAGE(误差平方列))MAPE 是最直观的指标例如 8% 表示预测值平均偏离真实值 8%。如果 MAPE 在 10% 以内说明模型在这个数据集上表现不错超过 20% 说明数据波动太大或者需要调整窗口期和权重。4.5 指数平滑法的补充如果觉得手动调权重太麻烦可以用 Excel 的 FORECAST.ETS 函数做指数平滑。它能把季节性、趋势和数据填充一起处理。一个基本调用示例FORECAST.ETS(A14, B2:B13, A2:A13, 1, 1)其中 A14 是需要预测的日期B2:B13 是历史需求量A2:A13 是对应的日期列。第四个参数表示季节性的长度设为 1 表示不自动检测季节性第五个参数设为 1 表示自动处理缺失点。不过这个函数要求日期列必须是真正的日期格式并且历史数据要按时间升序排列。如果数据里有重复日期函数可能会报错。5. 采购成本分析实战采购成本分析不只是“算总额”更关键的是跟踪价格波动、分析供应商结构、找出高值物料。下面几个小节分别对应不同的分析动作。5.1 采购金额与均价的月度汇总用 SUMIFS 函数按月份汇总采购金额。假设日期在 A 列金额在 F 列要汇总 2024 年 3 月的采购金额SUMIFS(F2:F1000, A2:A1000, DATE(2024,3,1), A2:A1000, DATE(2024,4,1))同理采购数量加权平均单价可以这样算加权平均单价 SUMIFS(F2:F1000, A2:A1000, DATE(2024,3,1), A2:A1000, DATE(2024,4,1)) / SUMIFS(E2:E1000, A2:A1000, DATE(2024,3,1), A2:A1000, DATE(2024,4,1))这里 E 列是采购数量F 列是采购金额。之所以要用金额除以数量而不是对所有订单单价做简单平均是因为不同批次采购数量差异很大简单平均会被小订单的高单价干扰。5.2 供应商份额分析供应商份额的经典算法是某供应商采购金额占全部采购金额的比例。第一步用数据透视表把“供应商”拖到行区域“金额”拖到值区域并按供应商汇总。第二步在透视表旁边加一个占比字段供应商采购金额 / 总计金额也可以直接在透视表的值区域选择“值显示方式 — 总计的百分比”。如果只看某个品类下的供应商占比在透视表里加一个“品类”作为筛选器即可。5.3 采购成本 ABC 分类ABC 分类用于区分哪些物料最值得精细化管控。计算步骤是按物料汇总出年度采购金额。按采购金额从高到低降序排列。计算每个物料的累计金额占比。设定 A 类为累计占比 0% 到 70%B 类为 70% 到 95%C 类为 95% 到 100%。Excel 中可以用 RANK 函数和 IF 函数辅助打标IF(累计占比70%, A, IF(累计占比95%, B, C))不过直接判断累计占比有个细节这个累计占比不是每个物料在总金额中的独立占比相加后从高到低累计而是按金额排序后建立的新列。建议先做一张物料汇总表再在汇总表上新增“累计占比”列。5.4 采购价格变动与移动加权平均的结合如果采购频繁、单价波动明显可以用移动加权平均的思想计算“当前库存成本”。这不是移动加权平均预测而是移动加权平均计价。公式逻辑是新的加权平均单价 (原有库存金额 本次采购金额) / (原有库存数量 本次采购数量)Excel 中可以按时间顺序维护两列入库数量和入库金额然后逐行计算滚动均价(上期库存数量*上期均价 本期采购金额) / (上期库存数量 本期采购数量)这种算法在 ERP 里很常见Excel 做一个小模型也完全可行。对采购同事来说这个滚动均价比简单的“订单单价平均值”更能反映真实成本。6. 库存与物流数据分析6.1 库存周转天数分析库存周转天数反映从入库到消耗的平均时间。公式是库存周转天数 期间平均库存 / 期间日均出库量Excel 中计算平均库存可以用期初库存和期末库存的平均值平均库存 (期初库存 期末库存) / 2日均出库量 期间总出库量 / 期间天数。如果数据粒度足够可以建一个“每日库存表”然后对每日库存做 AVERAGE结果更准确。库存周转天数越低说明库存占用资金越少但也要结合供应商交期判断是否影响供应连续性。6.2 呆滞库存分析呆滞库存一般指超过设定库龄仍未消耗的库存。Excel 计算库龄的思路是用当前日期减去该批次入库日期库龄天数 TODAY() - 入库日期按库龄区间统计COUNTIFS(库存表[库龄天数], 90, 库存表[库龄天数], , 180)如果想让结果更清晰可以直接把库龄列作为行标签放进数据透视表然后按“30天以内”“30到60天”“60到90天”“90天以上”做分组。6.3 到货周期与准时交付率到货周期可以理解为“下单日期”到“到货日期”之间的天数差。Excel 日期可以直接相减到货周期 到货日期 - 下单日期但要注意如果日期包含时间直接相减会得到小数建议用INT(到货日期 - 下单日期)准时交付率的计算方式是准时交付率 按承诺日期或约定交期到达的订单数 / 总订单数先用 IF 判断是否准时IF(实际到货日期 承诺到货日期, 准时, 延迟)再用 COUNTIF 统计“准时”的频率COUNTIF(结果列, 准时) / COUNTA(结果列)6.4 物流费用分摊与运输成本分析物流费用分摊在 Excel 里常用两个指标每单物流费、每公斤物流费。每单物流费 SUMIFS(物流费用表[费用], 物流费用表[月份], 2024年3月) / COUNTIFS(物流费用表[月份], 2024年3月) 每公斤物流费 物流费用 / 发货总重量如果想把物流费用分摊到 SKU可以使用 SUMIFS 按发货单号匹配再按物料数量拆分。这个场景建议先做“发货单号”维度的汇总再通过 XLOOKUP 或 VLOOKUP 把费用金额关联到物料明细。7. 数据透视表与可视化看板7.1 数据透视表建模在整理好的一维表上插入数据透视表常见布局如下行区域品类、物料编码、供应商、月份。值区域金额、数量、到货周期的平均值、准时率。筛选器年份、采购类型、仓库。如果要做月度采购趋势最简单的就是把“日期”拖到行区域后右键选择“组合”按“月”和“年”分组。这样 Excel 会生成“年”和“月”两级行字段展示非常直观。7.2 折线图与趋势线选中月度金额透视表插入折线图再右键添加趋势线选择线性。这个趋势线可以快速判断采购金额或需求量的整体走向。但趋势线只能显示趋势方向不能代替移动加权平均预测两者定位不同。7.3 帕累托图帕累托图非常适合采购成本 ABC 分析。做法是选中物料汇总表物料名称和金额插入“直方图”中的“排列图”Excel 会自动生成柱形图和累计百分比折线图的组合图。这张图能直观看出前 20% 的物料占了多少金额。7.4 瀑布图做费用变化拆解如果要展示“采购成本为什么比上月高”可以用瀑布图。Excel 2016 以上版本支持直接插入“瀑布图”。数据源列为上月成本、涨价因素增量、采购量增量、新供应商因素、降价因素减量、本月成本。瀑布图能清晰表达每个因素对总成本变化的贡献。7.5 动态交互看板设计一个小看板可以用“切片器”。方法是给数据透视表插入切片器关联月份、品类、供应商字段然后按住 Ctrl 多选多个透视表共享同一切片器。之后点击切片器所有透视表会一起联动形成简易的交互仪表盘。8. 常用函数速查与公式模板8.1 移动加权平均动态模板假设数据长这样A列日期 B列需求量需要求最后 3 期的加权平均值权重放在 D1:D3那么预测公式为SUMPRODUCT(OFFSET(B2, COUNTA(B2:B1000) - 3, 0, 3, 1), D1:D3) / SUM(D1:D3)这组公式的关键在于 COUNTA 确定最后一行位置然后把滚动窗口锁定在最近 3 行。8.2 采购月度均价模板SUMIFS(金额列, 日期列, DATE(2024,3,1), 日期列, DATE(2024,4,1)) / SUMIFS(数量列, 日期列, DATE(2024,3,1), 日期列, DATE(2024,4,1))8.3 安全库存估算模板如果业务追求简单可用安全库存可以按“平均日需求 × 采购提前期 × 安全系数”估算安全库存 AVERAGE(日需求列) * 平均提前期天数 * 1.281.28 对应约 90% 的服务水平。注意这个系数不是普适值需要结合企业的缺货容忍度调整。9. 常见问题与排查方法问题现象可能原因排查方式解决方案移动加权平均结果一直是 0权重列或数据列引用错误文本型数字无法计算检查 SUMPRODUCT 引用的区域是否对应检查单元格格式是否为数值用数值格式刷新单元格重新检查公式区域日期列无法作为时间序列参与预测日期是文本格式使用 ISNUMBER 函数检查查看单元格左上角是否有绿色三角使用“分列”将文本日期转成真日期FORECAST.ETS 返回 #VALUE!日期列未排序或存在重复日期检查日期列升序排列情况排序后重试或使用 FORECAST.ETS.SEASONALITY 辅助确认季节性SUMIFS 汇总金额与原始表对不上日期范围条件写法错误展开条件区域查看日期是否包含首尾日期使用 DATE 函数组合条件避免手写文本日期数据透视表新增数据后不更新数据源范围固定没有用表格右键透视表刷新查看数据源范围将数据源改为 CtrlT 创建的表或使用动态名称管理器供应商占比计算出来超过 100%没有使用“总计的百分比”或分母引用错误检查占比公式的分母引用使用总计单元格锁定或直接改值显示方式库存周转天数出现小数点日期相减后含时间检查是否有一个日期列带时间用 INT 或 DATEDIF 计算天数帕累托图不明显数据未按金额降序排列检查分类轴顺序按金额降序排列后再插入图看板切片器没有关联到所有透视表切片器只连接了单张表右键切片器找到“报表连接”勾选需要联动的所有透视表预测值偏离实际过大窗口期太短或权重设置不合理对比 MAPE 指标增加窗口期或改用指数平滑10. 最佳实践与使用建议如果你要在实际工作中长期用 Excel 做供应链预测与采购分析下面几点值得坚持。第一原始数据与计算模板分离。建议工作簿里至少分成三个 Sheet原始数据、计算模型、看板。原始数据只做数据录入和导入不要在同一个表里写公式避免新增数据把公式区域破坏。计算模型统一引用原始数据看板只引用计算模型的结果。第二预测模型先回测再上线。不要在没有任何验证的前提下直接拿预测结果下采购单。用最近 3 到 6 个月的历史数据做一次回测比较 MAE、MAPE 或 RMSE。即使最终因为时间紧张没有做完整回测也要至少观察最近 3 期的预测偏差判断模型是否稳定。第三多模型对比不要只依赖单一方法。简单移动平均、加权移动平均、指数平滑可以同时算出三个预测值再结合业务对“缺货风险”和“资金占用”的偏好来收敛最终结果。采购计划本来就是权衡不是纯数学。第四模型参数要写在表里。很多 Excel 分析表因为没有记录权重和窗口期过一个月就不知道当时为什么这么设置。建议单独放一个参数区域权重、窗口期、安全系数、服务水平、预测期间。这样其他人接手时可以直接看到逻辑。第五重要数据做好备份和权限控制。采购单价、供应商合同价格、库存金额属于敏感数据发送报告时只发脱敏后的看板页不要发送包含全部原始记录的工作簿。第六遇到大量重复性分析任务时把当前工作簿保存为模板。比如预测模板.xlsx、采购月报模板.xlsx每月替换原始数据后刷新透视表即可。对 Excel 比较熟悉的用户还可以录制一个简单的宏把“刷新数据源 更新图表 另存为 PDF”做成一个按钮。最后再提醒一句Excel 预测结果更适合作为辅助决策而不是直接代替人工判断。供应链环境里的供应商突发断货、客户需求临时变更、运输延误这些非规律因素当前模型无法前置识别。把 Excel 的规律性预测和人的业务判断结合起来才是比较稳妥的使用方式。如果这篇内容对你有用建议收藏备用。先拿一份最近 12 个月的采购或需求数据搭一个移动加权平均模型跑一遍观察预测值和实际值的偏离情况。跑熟了之后再逐步加上采购成本 ABC 分类、库存周转和到货周期分析模板会越用越顺手。