采购要知道的22个采购Excel公式！（建议收藏） | 帆软九数云

九数云BI小编 | 发表于：2026-01-12 13:44:24

采购工作琐碎又繁杂，从对账结算、下单跟催，到库存管控、成本分析，几乎天天离不开 Excel。

今天整理了采购人员全流程高频使用的采购Excel公式，覆盖对账与结算、成本波动与费用分摊等六大核心模块，收藏起来直接套用！

一、对账与结算

1、自动对账差异查找

公式：IF(VLOOKUP(订单号,供应商对账单!A:D,4,FALSE)<>实收数量,"差异","一致")

使用逻辑：通过订单号跨表匹配供应商数据，直接对比系统实收数量与对账单数量，快速定位异常订单。

适用场景：月度大批量对账、订单数据核验。

2、账期到期提醒

公式：IF((付款截止日期-TODAY())<=7,"即将到期","")

使用逻辑：提前 7 天预警即将到期的款项，避免被动催款。

实务建议：搭配条件格式，让临近到期的单元格自动高亮，方便优先处理。

3、增值税反算（不含税价拆分）

不含税金额公式：ROUND(含税金额/(1+税率),2)

税额公式：不含税金额 * 税率

适用场景：供应商含税报价拆分、财务核算、成本分析统一不含税口径。

二、采购分析与价格判断

1、采购品类 ABC 分类

公式：IF(累计占比<=80%,"A类",IF(累计占比<=95%,"B类","C类"))

操作步骤：① 按采购金额降序排序；② 计算采购金额累计百分比；③ 套用公式分类。

价值：快速区分核心品类与次要品类，优化采购策略。

2、供应商集中度分析

公式：SUM(LARGE(采购金额列,{1,2}))/SUM(采购金额列)

解读：计算采购金额 TOP2 供应商的占比，判断是否存在供应商依赖风险。

3、价格趋势预测

公式：FORECAST.ETS(下月日期,价格列,日期列)

说明：利用指数平滑法预测未来价格走势，提前应对价格波动。

4、批量生成采购订单号

公式：TEXT(日期,"YYMM")&"-"&ROW()-1

示例：自动生成 “2407-001” 格式的订单编号，避免手动编号重复或遗漏。

5、物料编码智能补全

公式：IFERROR(VLOOKUP(""&输入码&"",编码表,2,0),"未找到")

价值：输入物料简称或编码片段，自动匹配完整物料编码，提升下单效率。

6、多条件筛选供应商

公式：UNIQUE(FILTER(供应商表,(地区="华东")*(等级="A")))

价值：按需筛选符合多条件的优质供应商，比如 “华东地区 A 级供应商”。

采购要知道的22个采购Excel公式！（建议收藏）插图

三、订单执行与交期预警

1、交期预警提示

前置公式（计算剩余天数）：剩余天数=交货日期-TODAY()

预警公式：IF(剩余天数<=3,"紧急",IF(剩余天数<=7,"预警","正常"))

设置技巧：搭配条件格式设置红黄绿三色提醒，紧急订单标红，一目了然。

2、采购订单状态跟踪

公式：IF(已交货数量=0,"未开始",IF(已交货数量<订单数量,"进行中","已完成"))

进阶操作：添加数据条进度条（开始 - 选项卡 - 数据条），直观展示订单完成进度。

3、缺货紧急采购标识

公式：IF(库存量<安全库存,"★紧急采购","")

价值：库存低于安全线时自动标注，及时触发补货流程。

四、库存控制与呆滞识别

1、经济订货批量（EOQ）

计算公式：ROUND(SQRT((2*年需求量*单次订货成本)/(单位库存成本)),0)

参数说明：年需求量：产品年度采购总量；单次订货成本：每次下单产生的人工、物流等费用；单位库存成本：产品单价 × 库存持有费率

价值：计算最优订货量，平衡订货成本与库存成本。

2、库存周转率计算

核心公式：库存周转率=销售成本/平均库存金额

辅助公式：销售成本 = 期初库存 + 采购成本 - 期末库存；平均库存 =(期初库存 + 期末库存)/2

价值：衡量库存周转效率，避免库存积压或缺货。

3、呆滞库存标记

公式：IF(最后出库日期<TODAY()-180,"呆滞","")

说明：默认超过 180 天未出库的物料为呆滞库存，可根据企业实际调整天数阈值。采购要知道的22个采购Excel公式！（建议收藏）插图1

五、供应商评分与黑名单

1、供应商自动评分

公式：SUMPRODUCT((质量得分*40%)+(交期得分*30%)+(服务得分*30%))

说明：按质量（40%）、交期（30%）、服务（30%）权重计算综合得分，权重可按需调整。

2、供应商等级划分

公式：IF(综合得分>=90,"A级",IF(综合得分>=80,"B级","C级"))

效果：自动标注供应商等级，搭配条件格式让不同等级供应商显示不同颜色。

3、黑名单供应商筛选

公式：FILTER(供应商表,(交货延迟次数>3)*(质量投诉率>5%),"无")

价值：自动筛选出交货延迟频繁、质量投诉率高的不合格供应商，动态更新黑名单。

供应商管理库存

六、成本波动与费用分摊

1、采购价波动监控

公式：([本月单价]-[上月单价])/[上月单价]

应用：自动计算价格涨跌百分比，及时发现异常涨价情况。

2、批量采购折扣计算

公式：IF(采购量>=1000,单价*0.9,IF(采购量>=500,单价*0.95,单价))

示例：采购量 500 件以上享 95 折，1000 件以上享 9 折，可根据企业折扣政策调整阈值。

3、运输成本分摊

公式：ROUND(总运费*(产品体积/总体积),2)

技巧：按产品体积或重量比例分摊物流费用，成本核算更精准。

采购要知道的22个采购Excel公式！（建议收藏）插图3

以上就是对账与结算、成本波动与费用分摊等六大核心模块的采购Excel公式。

免费注册

咨询方案