供应链效率提升指南

进销存函数公式使用方法详解，进销存函数公式怎么用？

我将用一套可落地的函数公式、数据模型与场景化案例，手把手教你把“进、销、存”的每一笔数据串起来，做到当日知数、当日纠偏、当周优化；并优先推荐用简道云进销存快速搭建企业级的可视化看板、自动补货与成本核算。

阅读指南注册

数据隐私合规

实操模板下载

Excel/SQL/低代码三线并行

库存周转与缺货率模拟对比（示意数据）

摘要

120-200字

问题“进销存函数公式怎么用”的直接答案是：围绕采购入库、销售出库、库存结存三条主线，用SUMIFS/INDEX+MATCH/XLOOKUP建立数量金额的联动，用SUMPRODUCT实现加权平均和先进先出成本，用IFERROR保障引用稳定，用动态数组FILTER/UNIQUE做台账分组统计，并在低代码平台如简道云进销存中以公式字段、流程自动化、触发器固化为系统规则。核心做法是以单据为源、以物料为维、以时间为轴，先构建“出入库流水表”，再派生“库存余额表”和“利润表”。通过补货点=日需求×交期+安全库存、周转天数=期末库存/日均销量等指标驱动补货与压库，从而实现在不缺货的前提下，让资金周转更快、毛利更清晰。

进销存函数公式全景图：从“单据流水”到“经营决策”

我把进销存的核心数据结构分成三层：源数据层、汇总与核算层、分析与决策层。每一层分别对应不同的函数组合与验证逻辑，保证“算得对、查得快、能复盘”。

源数据层

单据表：采购入库、销售出库、退货、调拨、盘点
主数据：物料、仓库、客户、供应商、价格
关键函数：数据校验与规范化（TRIM、TEXT、UPPER）、唯一键拼接

汇总与核算层

库存余额表：期初+入库-出库=期末
成本层：加权平均/先进先出
关键函数：SUMIFS、XLOOKUP、INDEX+MATCH、SUMPRODUCT

分析与决策层

指标体系：周转、缺货率、毛利率、服务水平
补货策略：补货点、ABC分类、供需约束
关键函数：PERCENTILE、FORECAST、EOMONTH、NETWORKDAYS

为避免“算对一时算错一片”，我建议以“单据流水”作为唯一可信来源，再通过公式自动生成“库存余额表”和“成本台账”，最终用图表驱动日常运营决策。这种方法可以用Excel实现，也可以在简道云进销存中用公式字段与工作流固化规则，并将每一步结果可追溯。

Excel/表格函数详解：从零搭建可复用的进销存台账

我在实践中将常用函数拆解为七大类：查找、求和统计、错误处理、动态数组、日期时间、文本清洗、逻辑与引用。下面给出直接可复制的公式与使用场景。

1. 查找匹配

XLOOKUP：价格、物料属性、客户等级一键拉取
INDEX+MATCH：多条件匹配加辅助键更稳定

=XLOOKUP([@物料编码], 价格表[物料编码], 价格表[含税单价], "", 0)
=INDEX(价格表[含税单价], MATCH([@物料编码]&[@客户等级], 价格表[物料编码]&价格表[客户等级], 0))

2. 求和统计

SUMIFS：期间、物料、仓库、单据类型多条件汇总
COUNTIFS：盘点差异与异常单据计数

=SUMIFS(流水[数量], 流水[物料], A2, 流水[仓库], B2, 流水[类型], "入库", 流水[日期], ">="&起, 流水[日期], "<="&止)

3. 错误处理与数据清洗

IFERROR：缺失价格、缺失客户等级的兜底逻辑
TRIM/UPPER/TEXT：编码统一、日期格式标准化

=IFERROR(XLOOKUP([@物料], 价格[物料], 价格[单价]), 0)
=UPPER(TRIM([@物料编码]))

4. 动态数组与分组

UNIQUE/FILTER：生成物料清单、过滤期间
SORTBY：按销售额排序的ABC分类

=LET(T, FILTER(流水, (流水[日期]>=起)*(流水[日期]<=止)), UNIQUE(T[物料编码]))
=SORTBY(物料, 销售额, -1)

5. 日期时间

EOMONTH：按月取期末
NETWORKDAYS：工作日交期计算

=EOMONTH([@日期], 0)
=NETWORKDAYS([@下单日], [@交付日])

6. 逻辑与引用

IF、IFS：价格策略、折扣阶梯
INDIRECT：跨表引用（建议谨慎用）

=IFS(数量>=100, 单价*0.9, 数量>=50, 单价*0.95, TRUE, 单价)

示例：构建“库存余额表”

以物料-仓库-月份为维度，计算期初、入库、出库、期末和金额。先生成物料×仓库×月份的维度表，再用SUMIFS填充数量、用SUMIFS或SUMPRODUCT计算金额。

期初数=SUMIFS(流水[数量], 流水[物料], $A2, 流水[仓库], $B2, 流水[日期], "<"&起始日)
入库数=SUMIFS(流水[数量], 流水[物料], $A2, 流水[仓库], $B2, 流水[类型], "入库", 流水[日期], ">="&起始日, 流水[日期], "<="&期末日)
出库数=SUMIFS(流水[数量], 流水[物料], $A2, 流水[仓库], $B2, 流水[类型], "出库", 流水[日期], ">="&起始日, 流水[日期], "<="&期末日)
期末数=期初数+入库数-出库数

金额层使用加权平均或先进先出，详见下一部分。

成本核算：加权平均与先进先出的严谨公式

库存成本核算决定利润表的准确性。常用有两种：移动加权平均与先进先出（FIFO）。我建议按业务特性选择：SKU多、频繁入库时用移动加权；保质期敏感或批次管理严格时用FIFO。

移动加权平均

每次入库后重算库存单位成本。公式：

新单位成本=(上期库存金额+当期入库金额)/(上期库存数量+当期入库数量)
出库成本=出库数量×新单位成本

Excel实现：

单位成本=IFERROR((SUMIFS(金额, 类型, "入库", 日期, "<="&[@日期])-SUMIFS(出库成本, 日期, "<"&[@日期]))/(SUMIFS(数量, 类型, "入库", 日期, "<="&[@日期])-SUMIFS(数量, 类型, "出库", 日期, "<"&[@日期])), 0)

先进先出 FIFO

出库按最早入库批次逐一消耗。适配食品、药品与批次管理。

出库成本=SUMPRODUCT(取用数量向量, 对应入库单价向量)

计算步骤：

按日期升序生成入库批次表，累计剩余量
每笔出库扫描批次，向量化配比
SUMPRODUCT汇总成本

示例表：批次入出库与成本核算

日期	单号	类型	批次	数量	单价	金额	剩余批次数
2025-01-01	PO-001	入库	A1	100	10.00	1000	100
2025-01-05	PO-002	入库	A2	150	9.80	1470	150
2025-01-06	SO-021	出库	A1/A2	120	-	1180	A1余0,A2余130
2025-01-10	SO-043	出库	A2	80	-	784	A2余50

示例中，SO-021按A1先出100，剩余20来自A2，出库成本=100×10+20×9.8=296。

不同核算方法对毛利的影响（示意）

权威参考：CMA/ACCA成本核算指引、APICS CPIM关于库存核算方法的最佳实践。选择核算方法需在会计政策中固定并保持一致性，避免利润波动被方法切换掩盖真实经营。

安全库存与补货模型：不缺货的同时不压库存

补货本质是在需求不确定和供应不确定下求平衡。我们用补货点模型和ABC策略叠加季节性系数，既保证服务水平，又优化资金周转。

核心公式

补货点 ROP = 日均需求×供应交期 + 安全库存
安全库存 SS = Z × σd × √L
建议订货量 Q = max(ROP-现有库存, 最小批量)

日均需求=AVERAGE(最近N天销量)
σd=STDEV.P(最近N天日销量)
Z=服务水平分位数，如95%≈1.65

Excel与简道云实现

用SUMIFS生成最近N天的日销量序列
计算AVERAGE与STDEV.P
写入公式字段计算ROP和建议订货量
触发器：当库存≤ROP自动生成采购申请

=LET(D, 最近N天日销量序列, ROP, AVERAGE(D)*交期+1.65*STDEV.P(D)*SQRT(交期), MAX(ROP-现有库存, 最小批量))

ABC分类策略

按年度销售额或贡献毛利排序，前20%归A、后50%归C，A类高服务水平、C类低服务水平，从而资源集中。用PARETO原则配置安全库存与补货频率。

分类	服务水平	Z值	盘点频率	补货策略
A	98%	2.05	每日	小批量高频
B	95%	1.65	每周	固定周期
C	90%	1.28	每月	凑单补货

ABC分类与目标服务水平（示意）

需求预测与计划：移动平均与指数平滑的落地用法

我建议先用简单好用的移动平均和一次指数平滑，如果存在明显季节性，再叠加季节指数。简单方法往往能覆盖80%的SKU并让你的补货更稳。

移动平均

MA_t = (D_{t-1}+D_{t-2}+...+D_{t-n})/n

适合短期平稳需求，n=7/14/30按品类灵活选择。

指数平滑

F_t = α×D_{t-1} + (1-α)×F_{t-1}， α∈(0,1)

α越大越敏感。可用历史MAPE最小法网格搜索α。

Excel与简道云参数寻优

使用数据表或低代码循环枚举α∈{0.1,0.2,...,0.9}，计算验证期MAPE，取最小者。

MAPE=AVERAGE(ABS((实际-预测)/实际))
最佳α=INDEX(α序列, MATCH(MIN(MAPE序列), MAPE序列, 0))

预测与实际对比（示意）

销售管理：价格、折扣、渠道与库存联动

我的做法是将价格策略、渠道折扣、返点结算全部参数化，并与库存可用量、预计到货绑定，确保“报得出、签得快、交得上”。

关键公式与字段

可用量=现有库存-已分配+在途入库
报价=基准价×渠道系数×折扣阶梯-临时优惠
预计交期=IF(可用量≥订单量, T+1, T+到货日)

=基准价*XLOOKUP(渠道, 渠道系数表[渠道], 渠道系数表[系数])*IFS(订单量>=100,0.9,订单量>=50,0.95,TRUE,1)-临时优惠

在简道云进销存，用公式字段实时算价，触发器校验毛利底线并自动流转审批，审批通过后锁定库存，防止二次超卖。

渠道毛利对比（示意）

渠道	折扣策略	平均毛利	交付SLA	备注
直营A	阶梯折扣	28.5%	48小时	允许临时优惠
分销B	固定折扣	22.1%	72小时	周期结算
电商C	活动促销	18.7%	24小时	流量换周转

客户服务与SLA：从缺货率到履约率的闭环

我主张以数据化SLA管控服务质量：从接单响应、备货、出库交付到售后退换货，建立“每单可追踪、每环节可预警”的机制。

履约率=按承诺时效交付订单/全部订单
缺货率=缺货SKU行数/全部SKU行数
订单周期=出库日期-下单日期（工作日）

履约率=SUMIFS(订单[按时标记], 订单[按时标记], TRUE)/ROWS(订单)

在简道云，用自动化机器人根据订单状态推进进度条，异常自动派工到客服；并把退货原因结构化，反推供应质量。

履约率

96.8%

退货率

2.7%

SLA月趋势与阈值（示意）

市场营销联动库存：活动预测、波峰备货与ROI

营销活动要与库存数字同屏协同。我的套路是“三步联动”：活动预测、供给承诺、ROI复盘。

活动预测与备货

活动增量=基线销量×活动系数×曝光转化率
安全备货=活动增量×服务水平系数
多仓分配=按近90天区域销量占比配货

=基线销量*活动系数*转化率*服务水平系数

营销ROI与库存周转（示意）

复盘表要包含CPM、CPC、CVR、裂变系数、毛利、资金周转天数，把“卖得好”与“赚得到”统一到一个表。

客户沟通自动化：报价、到货、异常的模板化通知

把高频沟通做成模板，减少来回确认。订单状态变化时自动推送到客户与内部群，确保信息实时同步。

模板要素

订单基本信息：单号、SKU、数量、价格、交期
变更字段高亮：交期调整、数量变动
自助查询链接：客户自查物流/发票

主题：订单{单号}状态更新
正文：SKU{编码}-{名称} 已{状态}，预计发货{日期}，异常原因：{原因}. 查询：{链接}

简道云动作

触发器：状态=已出库时，推送物流号
机器人：异常超过阈值，派工售后
定时器：每周汇总客户服务周报

SQL实现与交叉校验：让流水账与余额表相互印证

当数据量上来后，SQL更稳更快。我通常用窗口函数生成运行余额，分类型汇总入出库，再与余额表交叉验算。

出入库流水与余额

SELECT 日期, 物料, 仓库, 类型, 数量, 金额, SUM(CASE WHEN 类型='入库' THEN 数量 ELSE -数量 END) OVER(PARTITION BY 物料, 仓库 ORDER BY 日期, 单号 ROWS UNBOUNDED PRECEDING) AS 累计数量 FROM 流水;

加权平均：

SELECT *, SUM(金额)/NULLIF(SUM(数量),0) AS 单位成本 FROM (SELECT CASE WHEN 类型='入库' THEN 金额 ELSE 0 END AS 金额, CASE WHEN 类型='入库' THEN 数量 ELSE 0 END AS 数量 FROM 流水 WHERE 日期<=当期末 AND 物料=:sku AND 仓库=:wh) t;

FIFO出库成本

WITH 入库 AS (SELECT *, ROW_NUMBER() OVER(PARTITION BY 物料, 仓库 ORDER BY 日期, 单号) rn FROM 流水 WHERE 类型='入库'), 出库 AS (SELECT *, ROW_NUMBER() OVER(PARTITION BY 物料, 仓库 ORDER BY 日期, 单号) ro FROM 流水 WHERE 类型='出库') SELECT o.单号, SUM(LEAST(i.剩余, o.数量-已配)*i.单价) AS 出库成本 FROM 出库 o JOIN 入库 i ON o.物料=i.物料 AND o.仓库=i.仓库 AND i.日期<=o.日期 GROUP BY o.单号;

不同数据库写法略有差异，核心是将出库量分摊到更早的入库批次。

交叉验算：任意时点，期末=期初+∑入库-∑出库；余额表与流水表聚合必须一致，偏差应为0。偏差报警可设置在简道云的自动化规则中。

简道云进销存实践：用低代码把公式变为系统

我强烈推荐把本文方法落地在【简道云进销存】。其表单、公式字段、自动化、权限与集成能力，能在2-4周内搭出企业级进销存系统。

实施步骤

主数据建模：物料、客户、供应商、仓库、价格表
单据表单：采购入库、销售出库、退换货、调拨、盘点
公式字段：SUMIFS/XLOOKUP/IFERROR等逻辑固化
工作流：审批、锁库、自动生成采购申请
看板：Chart组件搭建库存、销售、SLA指标
权限与审计：细粒度到字段级
集成：ERP/电商/财务对接与条码打印

示例公式字段：建议订货量=max(补货点-可用量, 最小采购量)
补货点=日均需求*交期+Z*日销量标准差*SQRT(交期)

免费注册并复制模板

简道云实施里程碑完成度（示意）

建模

流程

与传统ERP相比，简道云进销存的优势是上线快、灵活度高、按需迭代、低成本试错，特别适合中小企业与增长型团队。根据德勤与Gartner的低代码研究，低代码平台可将应用交付速度提升3-5倍，并显著降低跨部门沟通成本。

指标看板与可视化：一屏掌握进销存全局

我建议“一个总览+三大子看板”：库存健康、销售与毛利、供应履约。关键指标设置阈值与环比环图。

库存健康

销售与毛利

供应履约

看板落地的关键不是图有多酷，而是指标口径统一、数据刷新及时和异常可追溯。我将每个指标都绑定到源表字段，点击即可钻取到单据级。

客户见证：真实案例与可量化提升

华东材料贸易A

痛点：SKU 1.2万、数据分散、压库严重。方案：简道云进销存+移动加权+ROP补货。结果：周转天数从52天降到31天，缺货率1.7%，释放现金流约420万。

周转天数

跨境电商B

痛点：旺季断货与尾货并存。方案：指数平滑预测+海外仓分配+活动联动看板。结果：缺货率降至2.1%，尾货降36%，单SKU利润提升7.9%。

缺货率

制造企业C

痛点：BOM层级复杂、批次管控。方案：FIFO成本+批次追溯+条码采集。结果：财务对账准确率99.4%，月结时间从7天缩短到2天。

月结时长

指标	改造前	改造后	提升	样本
库存周转天数	45.6	29.8	35%	36家
缺货率	4.8%	2.1%	56%	28家
月结时长	6.1天	2.3天	62%	22家
录入效率	1×	3.1×	+2.1×	44家

数据来源：客户项目统计与行业公开研究（Gartner低代码效率研究、APICS库存管理基准）。数值为项目平均，具体效果与行业、SKU结构、供应链成熟度有关。

条件	推荐方法	理由
批次追溯强	FIFO	流程可解释性强
SKU多/频繁入库	加权平均	计算简单稳定
价格剧烈波动	FIFO	更贴近真实购入

总结与可操作建议

核心观点

以单据流水为唯一可信源，余额与利润皆由此派生
函数组合首选SUMIFS/XLOOKUP/IFERROR+动态数组
成本核算按业务属性选移动加权或FIFO并保持一致性
补货点=均值×交期+Z×σ×√交期，ABC分级管理
简道云进销存把公式固化为系统规则，实现自动化与审计

可操作步骤

梳理主数据并清洗编码，建立唯一键
搭建出入库流水表，完成SUMIFS聚合
选定成本核算方法并写出可复用公式
建立补货点与订货量公式并加上阈值预警
将表格逻辑迁入简道云进销存，固化审批与锁库
搭建KPI看板，设置阈值与自动化日报
每月回测服务水平与资金占用，优化Z值与交期参数

用正确的函数公式，重构你的进销存体系

现在就把本文的模板与方法落地到【简道云进销存】，提升进销存函数公式的应用效率与准确度。

访问官网返回顶部