Excel 做供应链和采购数据分析最容易被忽略的不是图表也不是透视表而是“数据预测”这个环节怎么落地。很多人以为预测就是加一条趋势线或者下拉一个 FORECAST 函数真到月度采购计划里又会发现结果没法用。这次围绕采购供应链场景来写核心方法放在移动加权平均上。它既能用来算采购成本也能用来估下一期需求对做采购分析、物流分析、采购成本分析的人都很实用。后面按照“数据怎么准备、公式怎么写、结果怎么验证、报错怎么排查”的顺序完整拆一遍。展开之前先说结论如果你面对的是几十个 SKU、价格波动明显、单据还在持续增加的采购明细表移动加权平均比普通平均值更接近真实库存成本和采购价格变化。前提是数据要干净、时间顺序要正确公式区域要锁定准确。下面这些步骤我都按普通 Excel 版本能跑的写法来写遇到新版动态数组函数会单独说明。1. 先把采购供应链分析的问题拆清楚预测什么、算哪笔成本1.1 供应链管理里最常见的几个数据分析目标做采购数据分析先不要急着套公式。要先搞明白这次分析到底要回答什么问题。同样一张采购明细表至少能回答四类问题。第一类叫需求预测。也就是下个月、下个季度某类物料或者某个 SKU 大概要采购多少。这类分析通常用历史采购数量作为基础数据按周或按月汇总后再做移动平均、指数平滑或者更复杂的统计预测。第二类叫采购成本分析。重点不是数量而是单价和总金额的变化。采购单价是否上涨、哪家供应商的价格更稳定、某个 SKU 的库存成本到底是多少这些问题都要落到“成本”两个字上。第三类叫物流费用分析。很多采购单里含有运费、装卸费、仓储费如果只看物料单价会把真实成本低估。尤其做采购成本分析时只有把物流费按批次分摊到 SKU 上移动加权平均算出来才接近实际成本。第四类叫供应商绩效分析。用数据透视表就能做把供应商放在行区域把采购金额和采购数量放进行区域按采购金额降序排序就能很直观地看到采购金额集中度。再往下拆还能计算出不同供应商的加权单价、订单延迟次数、到货天数平均值。这四类目标不需要一次全做完。先确定哪一类是当前最急的再决定表结构怎么设计。我见过不少同学一开始就把采购明细整理成几十列看起来非常全但真正计算时发现字段类型混乱、日期不统一辅助列也不知道该加在哪里。问题不在数据量而在没有先想清楚“要预测什么”。1.2 为什么移动加权平均适合采购成本分析很多做数据分析的人习惯用 AVERAGE 函数算平均价。但采购单价波动的场景里普通平均值有一个明显问题它没有考虑数量权重。举个例子。某 SKU 第一次采购 100 个单价 10 元第二次采购 200 个单价 12 元。如果按普通平均平均单价是 11 元。但如果按数量加权总金额是 3400 元总数量是 300 个加权平均单价约 11.33 元。这两个数哪个更接近真实库存成本显然是 11.33 元。因为它真正反映了库存里“大多数物料”的成本结构。移动加权平均比加权平均还多一个时间维度。它不是把所有历史采购一次性平均而是随着每一笔采购发生逐步更新平均单价。换句话说最新一次采购完成后当前库存的持有成本是多少移动加权平均能直接给出来。这对采购成本分析很有用。比如你要评估一批在途物料入库后库存成本会不会大幅上涨只要把在途数量和预计单价加入计算就能看到移动加权平均单价从多少变成多少。而不需要等单据全部入账后才去翻台账。还有一层原因是可解释性。Excel 里的移动加权平均可以用辅助列一步一步算出来累计数量、累计金额、当前平均单价。每一列都有明确业务含义审核数据和排查问题时都很方便。相比之下线性回归或者指数平滑虽然预测能力更强但普通业务人员很难快速验证结果对不对。1.3 分析前先确认数据维度不管用什么方法都要先确认自己的数据到底停留在哪个维度。采购数据分析最常用的数据粒度是一行代表“某天某个 SKU 从某个供应商采购入库一次”。如果一行代表“某月某 SKU 的总采购量”也能做分析但会丢失价格波动信息到货周期和物流费分摊也可能不准确。我建议先建一个基础数据表字段至少包含这些采购日期物料编码SKU 编码物料名称供应商名称采购数量采购单价采购金额物流费用可选用于成本分摊到货周期天数可选用于物流分析采购金额可以自动计算也可以用公式生成。关键是不能让“金额”和“数量、单价”同时出现手工录入否则一旦数量或单价修改金额很容易对不上。遇到必须手工调整单价的情况最好单独加一列“调整说明”不要把原始单价直接覆盖。主键粒度确认后还要规定日期格式。Excel 处理日期时如果能识别成真正的日期类型排序、筛选、按月汇总都会很稳定。如果日期是文本格式比如“2025.01.05”或者“2025年1月5日”排序时经常出现错乱后面做移动加权平均时也容易把顺序搞错。2. 准备采购明细表清洗字段和数据顺序是关键2.1 先把数据清洗成“可计算”的格式采购明细表进入 Excel 后第一步不是算移动加权平均而是清洗。最优先检查字段类型。选择“采购数量”这一列用一个最简单的公式判断ISNUMBER(C2)。如果返回 FALSE就说明这一格不是数值。采购数量列里出现文本数字、空格、全角数字都会让后续求和公式出错。采购单价同理。单价列经常出现“含税单价”“不含税单价”混用的情况还有单元格里含有货币符号比如“¥12”Excel 会把它识别成文本。处理方法是选中列后统一设置为数值格式或者用查找替换去掉多余字符。SKU 编码也要检查。常见问题是首尾空格、全角半角、字母大小写不一致。比如“SKU-A01”和“sku-a01”在 SUMIFS 条件判断时会被当成不同物料。可以先对物料编码列做一次去空格TRIM(B2)再用新列辅助判断。日期列建议统一为 Excel 日期格式。直接单元格格式改成日期还不够需要用可识别格式重新输入或分列转换。对已经混入文本日期的列可以用“数据—分列—日期”的方式转换。转换完成后再检查是否有无法识别为日期的单元格比如“2025/1/5”和“2025-1-5”混在一起时Excel 通常能自动识别但如果有“20250105”就需要注意了。空值也要处理。采购数量为空、采购单价为空、供应商为空都会影响汇总结果。不要直接删除行先用筛选把空值行标出来逐行确认是数据缺失还是本来就不需要字段。如果某条记录单价缺失但历史价格可以推断就用最近一次采购单价补全并在备注列写明“补录价”。2.2 数据排序移动加权平均的“先后顺序”决定一切移动加权平均对数据顺序非常敏感。同一张表、同一种 SKU如果日期乱序算出来的平均成本会完全不同。原因在于累计逻辑。移动加权平均每计算一行都要把“从第一行到当前行”的所有采购记录加起来。如果后发生的采购单排在了前面累计金额和累计数量就会被脏顺序带偏。所以在计算之前必须先对物料编码和日期做排序。我一般分两步先按物料编码排序让同一个 SKU 的所有记录排在一起。再按采购日期升序排列。可以用 Excel 的“自定义排序”主要关键字选“物料编码”次要关键字选“采购日期”排序依据都选“单元格值”次序选“升序”。排序时要注意如果同一天同一 SKU 有多笔采购订单需要按单据编号或入库批次再排一次。否则同一天内多张单据的顺序不稳定也会导致移动加权平均结果出现偏差。有一个更稳妥的做法是在基础表里加一列“序号”按“物料编码日期单据号”生成唯一序号。后续公式判断时只用序号递增来扩展区域逻辑更清楚。但如果只是入门阶段先保证“物料编码日期”排序正确也足够跑通流程。2.3 用 Excel 表格功能建立动态数据区域数据准备完成后我建议把区域转换成“Excel 表格”。选中数据区域按 CtrlT勾选“表包含标题”。转换后有几种好处。新增数据时表格会自动扩展不需要手动拖动公式范围。如果表格里已经建好了公式列新增行也会自动填充。做数据透视表时数据源直接用表名以后新增记录刷新即可。要注意的是Excel 表格里的公式用结构化引用刚开始可能不习惯。比如[采购数量]*[采购单价]而不是普通单元格引用。如果你不想用结构化引用可以先在普通区域写好公式再转成表格或者在表格里删除公式列重新手动输入让 Excel 自动填充。实际经验是结构化引用一旦熟悉维护起来比普通区域省心很多。但新手排查公式时可能会被[列名]这种写法弄混建议先在表格外验证公式再放进表格。3. 用 Excel 实现移动加权平均从单 SKU 到批量预测3.1 移动加权平均的计算原理移动加权平均的核心是“逐笔累计”。每当有一笔新采购发生时公式需要知道到当前行为止这个 SKU 累计采购了多少数量。到当前行为止这个 SKU 累计发生了多少采购金额。用累计金额除以累计数量得到当前移动加权平均单价。这里的“当前行”不是整个表最后一行而是从该 SKU 首次出现的那一行开始一直延伸到当前行。所以公式里经常出现“$区域首行:当前行”的写法。下拉填充时区域首行被绝对引用固定当前行会随行号变化形成累计效果。这是移动加权平均比普通平均更适合 Excel 落地的地方。它的每一步都能用列和公式表示不用辅助插件也不用写 VBA。3.2 单 SKU 的公式落地下面直接给一个可复制的示例。先把数据整理成这样的结构日期物料编码采购数量采购单价采购金额2025-01-05SKU-A1001010002025-01-12SKU-A2001224002025-01-20SKU-A1501116502025-02-02SKU-A180132340第 1 行是标题数据从第 2 行开始。E2 输入采购金额公式C2*D2F2 输入累计数量SUM($C$2:C2)G2 输入累计金额SUM($E$2:E2)H2 输入移动加权平均单价IF(F20,,G2/F2)这里用IF(F20,,...)是为了避免除零错误。只要有采购数量F2 不会为 0但后续数据若出现负数或退货冲减累计数量有可能归零。写完第 2 行后选中 F2:H2双击右下角填充柄Excel 会自动填充到数据末尾。看到的结果应该是日期物料编码采购数量采购单价采购金额累计数量累计金额移动加权平均单价2025-01-05SKU-A1001010001001000102025-01-12SKU-A200122400300340011.3332025-01-20SKU-A150111650450505011.2222025-02-02SKU-A180132340630739011.730这个结果说明当第四笔采购入库后当前该 SKU 的库存平均成本是 11.73 元而不是普通平均的 11.5 元。3.3 多 SKU 批量计算的写法真实业务里不可能只有一个 SKU。如果表里有多个物料编码直接下拉上面的公式累计区域会把所有 SKU 混在一起结果完全无效。多 SKU 场景下累计区域需要增加一个条件只累加当前物料编码相同的记录。这时用 SUMIFS 比 SUM 更合适。假设数据从第 2 行开始F2 累计数量SUMIFS($C$2:C2,$B$2:B2,B2)G2 累计金额SUMIFS($E$2:E2,$B$2:B2,B2)H2 移动加权平均单价IF(F20,,G2/F2)公式里的逻辑是在从第 2 行到当前行的范围内只对“物料编码等于当前行 B2”的记录求和采购数量和采购金额。下拉后当物料编码从 SKU-A 变成 SKU-B累计区域会自动清零重算因为条件变为“物料编码等于 SKU-B”。多 SKU 计算前务必先按“物料编码 采购日期”排序。否则同一 SKU 的记录分散在表里累计公式虽然能按条件过滤但日期顺序不对移动加权平均的含义就变了。如果数据量超过几千行我会推荐 SUMIFS 而不是 SUMPRODUCT。也可以用 SUMPRODUCT 实现同样的累计但它在计算时会按数组方式遍历行数一多就明显变慢。SUMIFS 在性能上更稳定也更容易看懂。3.4 如果需要做需求预测用移动平均而不是移动加权平均这里要区分两个概念。上面算的是“移动加权平均单价”用来算采购成本。如果要做“需求预测”比如预测下个月某 SKU 要采购多少通常用的是移动平均法。移动平均法的思路很简单用最近几期的实际需求平均值作为下一期预测值。比如用最近 3 个月的采购数量平均预测下个月需求。假设有一个月度采购数量汇总表B 列是月份C 列是实际采购数量。在 D 列预测IF(COUNT($C$2:C2)3,,AVERAGE(OFFSET(C2,-2,0,3,1)))这个公式的含义是如果当前行之前不足 3 期数据不预测否则从当前行往上数 2 行开始取 3 个连续单元格的平均值。OFFSET 的优点是滚动窗口不用手动改区域但它是易失函数。数据量大时文件计算速度会变慢。如果只有几十行完全没问题如果有几万行建议改成 INDEX 写法或者直接手动维护辅助列。如果希望近期数据权重更高可以用加权移动平均。比如最近一期权重 0.5上一期权重 0.3再上一期权重 0.2IF(COUNT($C$2:C2)3,,C2*0.5C1*0.3C0*0.2)不过这种写法需要小心行号引用公式不太适合下拉扩展。更简单的方式是把权重放在三个固定单元格里再用绝对引用。比如IF(COUNT($C$2:C2)3,,C2*$K$1C1*$K$2C0*$K$3)权重系数怎么设没有固定答案。经验是业务周期越短近期权重越高如果需求波动受季节性影响大就不能只用 3 期移动平均需要把去年同期数据一起考虑。4. 从采购成本分析到补货预测把计算结果变成业务动作4.1 用数据透视表做供应商和采购金额分析移动加权平均解决了“单个 SKU 当前成本是多少”的问题。但采购数据分析还需要看整体结构比如哪些供应商占了主要采购金额、不同供应商的价格差异有多大。用数据透视表可以很快完成。选择基础数据区域插入数据透视表。字段设置如下行区域物料编码、供应商名称值区域采购数量求和、采购金额求和如果还要看供应商的加权价格不要用“采购单价”的平均值。透视表里的平均值是算术平均会把不同数量混在一起。需要在值区域加入一个计算字段。在透视表分析菜单中找到“字段、项目和集”选择“计算字段”名称写“加权单价”公式写采购金额/采购数量这样得到的才是按数量加权的单价和移动加权平均的视角一致。这一步分析能回答很多采购问题。比如某 SKU 有两家供应商报价A 供应商单价低但交货不稳定B 供应商单价高但采购数量大。如果只看单价会误判 A 更划算。但如果结合采购金额占比和加权单价可能发现 B 才是主力供应商。4.2 用预测数据生成补货建议有了需求预测下一步就是补货计划。补货建议的简化公式是建议采购量 预测需求量 安全库存 - 当前库存 - 在途数量这里的安全库存可以用一个简单公式估算安全库存 日均需求 × 备货周期天数 × 安全系数安全系数通常取 1.2 到 1.5 之间。如果供应不稳定可以取更高但库存持有成本也会上升。举一个具体例子。某个 SKU 的下月预测需求是 1000 件日均需求就是 1000 / 30 ≈ 33 件。备货周期是 15 天安全系数取 1.3那安全库存约为33 × 15 × 1.3 ≈ 644 件如果当前库存是 300 件在途数量是 100 件建议采购量就是1000 644 - 300 - 100 1244 件这个结果可以直接作为采购申请数量。后续每次更新实际销售或采购数据预测需求变了建议采购量也会自动变化。只要数据源刷新决策才是动态的。4.3 判断预测结果是否可用预测结果不能只看“算出来了”。需要判断预测精度。最简单的方法是计算 MAPE也就是平均绝对百分比误差。先把实际值和预测值放在两列误差列公式ABS((实际值-预测值)/实际值)然后对误差列取平均AVERAGE(误差区域)如果 MAPE 在 15% 以内对大多数采购场景来说是可用的。如果超过 30%说明模型和历史数据不匹配可能要考虑增加历史期数、加入季节性拆分或者换成其他预测方法。还要检查移动加权平均单价是否合理。方法很简单随便挑一个 SKU按手工方式把它的采购单据按日期一条条累加最后用累计金额除以累计数量和 Excel 算出的最后一行对比。如果一致公式没问题如果不一致大概率是数据排序、SKU 编码或累计区域出了问题。5. 常见问题与排查链路Excel 数据分析结果不对时先看哪5.1 结果不对先查顺序和数据格式遇到移动加权平均单价和台账对不上最优先排查三件事。第一数据是否已经按“物料编码 采购日期”排序。移动加权平均依赖顺序日期乱序是第一个嫌疑点。第二SKU 编码是否完全一致。肉眼看起来相同的“SKU-A01”可能存在末尾空格导致 SUMIFS 匹配条件判断为不同物料。第三数量和单价是否为数值。在新增一列输入ISNUMBER(C2)下拉后如果有 FALSE说明该行存在文本格式数字公式计算时会忽略或报错。实际排查时建议不要只盯着报错单元格而是先对“物料编码 日期”排序再重新下拉公式最后抽查最终行。5.2 公式报错、空白和除零问题怎么处理常见的报错有几种。#DIV/0!表示累计数量为 0。先用 IF 判断规避。如果出现负数采购或退货冲减累计数量可能变成 0这时移动加权平均单价本身没有意义需要单独标记。#VALUE!表示公式里出现了文本类型的数据。通常集中在数量和单价列。把这两列格式改成数值或者重新输入干净数据。公式下拉完成后有些行显示空白可能是区域前面还没填充。更常见的问题是由于基础表使用了 Excel 表格双击填充时新行公式没有自动扩展。检查方法选中最后一行公式看是否和上面几行一致。5.3 批量计算时的典型坑批量计算最容易踩的坑是“辅助列公式没有随排序同步”。如果你先写好公式再对整列排序辅助列的引用区域可能被破坏导致每个 SKU 的累计结果错乱。正确顺序是先排序再写入公式或者用 Excel 表格在表内添加公式列让表格自动维护。另一个坑是数据区域被“自动筛选”或“隐藏行”干扰。SUMIFS 不会受隐藏行影响但 SUM 会受到筛选影响导致筛选状态下累计区域结果异常。移动加权平均的计算区域建议用 SUMIFS不要用筛选状态下的 SUM。如果数据有几十万行公式全部实时计算会比较慢。可以先把基础表数据粘贴为值再在另外的工作表里做汇总。或者把自动计算模式改为手动计算确认数据更新后再刷新。5.4 数据量大导致卡顿的优化方向数据量大了以后每个单元格都写 SUMIFS整个工作表会变慢。这里有几个可执行的优化方向。尽量把原始采购明细和计算区域分开。原始表只保留数据不要堆太多公式。移动加权平均的结果可以先粘贴为值再用于透视表和图表。把 SUMIFS 公式只写到数据末尾不要整列引用。如果公式范围是整列比如$C:$CExcel 会扫描非常多空行计算量成倍增加。如果必须保留公式建议用 Excel 表格固定范围并关闭“自动计算”中的某些触发。真正需要更新结果时按 F9 手动计算。还有一个思路是改用“分组累计列”。先按物料编码和日期排序再用“当日数量 上日累计数量”的方式生成累计列避免 SUMIFS 每次都从头扫一遍。这在数据量极大时能明显提速但公式设计更复杂适合有经验的读者。如果要把这套流程长期用起来我最关心的是三件事数据是否有固定模板、日期是否可排序、SKU 编码是否统一。移动加权平均本身不复杂复杂的是数据在进入 Excel 之前就已经乱了。实际投入使用时建议先用一个 SKU 跑完整条链路确认每一步结果都和台账对得上再铺开到全量数据。这样既不会把问题放大也能让你对每个字段的含义和每个公式的边界有更清楚的判断。