摘要
进销存怎么做？Excel进销存操作有哪些技巧？我的结论是：以标准化数据结构承载“采购-入库-出库-结存-对账”流程，用SUMIFS、XLOOKUP、透视表与Power Query完成汇总、对账和补货判断；当SKU、门店与人员协同时，优先切换到【简道云进销存】以获得多人协作、权限、审批与移动端。Excel阶段重点是规范主数据、用数据验证防错、用条件格式预警、用透视表做周转与ABC分析、通过Power Query自动化导入；当版本冲突、串货与批次追踪成为瓶颈，就将流程迁移到简道云，通过审批流、序列号管理与API集成把差错率控制在1%以内，并以可量化的ROI推进上线。
进销存核心KPI与目标线
我优先围绕现金流和缺货率管理KPI：库存周转天数、缺货率、呆滞率、库存准确率、补货准确率、毛利额与资金占用。下面是不同阶段可对标的目标线与动态达成度。
数据卡片
整体流程与主数据架构
我把进销存拆成三个层次：主数据、交易数据与控制规则。主数据包括SKU、供应商、客户、仓库；交易数据包含采购单、入库单、出库单、调拨单、退货单；控制规则涵盖ABC、最小/最大库存、安全库存、批次与序列号、保质期。标准化后才能在Excel或系统中稳定计算结存和周转。
建立供应商主数据、采购价目表；用采购申请与审批控制额度；到货后按PO入库并校验数量、价格与税额。
设置库位与批次；入库上架、移库与盘点；串货场景需要批次与序列号管控。
订单审核、按先进先出出库、对账开票；退货按原批次回库以保证保质期和质量追踪。
按日对账单、周盘点与月关账；计算周转天数、缺货率、呆滞率，生成管理看板。
| 表 | 必备字段 | 备注 |
|---|---|---|
| SKU | 编码、名称、规格、条码、类别、单位、保质期 | 编码与条码唯一 |
| 供应商 | 编号、名称、税号、结算方式、交期 | 对账关键字段 |
| 客户 | 编号、名称、区域、信用额度、折扣 | 配合销售收款 |
| 仓库 | 编号、名称、地址、类型、库位代码 | 支持多仓盘点 |
| 价格 | SKU、供应商、含税价、生效日期 | 有效期控制 |
Excel进销存基础搭建：结构优先
在Excel中，结构决定上限。我用“主数据-交易-指标-看板”四层结构，避免一张表堆所有字段；通过数据验证与命名范围减少录入错误，并使用规范的字段命名。
- 主数据：SKU、供应商、客户、仓库、价目表
- 交易：采购单、入库单、销售单、出库单、调拨、退货
- 指标：库存结存、周转、ABC、呆滞、缺货
- 看板：趋势、TOP榜、缺货预警
- 数据验证：SKU编码、仓库、单位用下拉；数量与价格限制为正数
- 条件格式：负库存标红；临期（保质期≤30天）高亮；异常价偏离>15%预警
- 冻结窗格与表头筛选，确保大表操作稳定
| 模块 | Excel要点 | 好处 | 注意事项 |
|---|---|---|---|
| 主数据 | 数据验证、唯一性检查、结构化表 | 减少录入错误、唯一键 | 保持编码不可变 |
| 交易 | 日期标准化、数值限定、自动编号 | 对账一致、追踪明确 | 编号不可重复 |
| 汇总 | SUMIFS、XLOOKUP、动态数组 | 快速求库存与金额 | 文本与数值类型一致 |
| 分析 | 透视表、切片器、时间线 | 灵活维度分析 | 定期刷新数据源 |
Excel高频函数与技巧：从对账到补货
进销存的核心计算是“期初+入库-出库=期末”。我将常用函数组合为模板，确保从SKU到仓库维度都能快速对账与生成预警。
用SUMIFS按SKU与仓库汇总出入库数量与金额，期末=期初+入库-出库。搭配XLOOKUP获得单位与成本价。
- 期初库存：来自上期期末或初始化表；保持不可变
- 成本法：加权平均或移动加权；在Excel中可用Power Query计算
缺货判断：库存≤安全库存；临期：当前日期≥批次生产+保质期-阈值。使用条件格式触发红橙灯。
按年销售额排序累计占比，A类前70%，B类20%，C类10%。A类严格补货与盘点，C类压缩库存。
补货点=平均日销量×供货提前期+安全库存。Excel中可把平均销量设为移动平均或加权预测。
- XLOOKUP替代VLOOKUP，稳定且支持双向
- SUMIFS分条件求和，避免透视复杂计算
- UNIQUE、FILTER构建动态看板
- TEXT函数统一日期格式，避免类型错乱
- Power Query自动导入CSV与批量清洗
- Power Pivot支持百万级数据分析
透视表、Power Query与Power Pivot：把Excel用到极致
我的落地方法是“PQ进、PP算、透视出”：Power Query负责抽取与清洗，Power Pivot在数据模型中建立关系和度量，透视表负责快速分析与展现。
- 连接多个CSV或数据库表
- 去除空行、统一字段名与类型
- 拆分批次号/序列号字段
- 合并入库与出库表，添加“方向”字段
- 输出为数据模型表，供Power Pivot引用
- 库存数量=SUM(入库数量)-SUM(出库数量)
- 销售额=SUMX(销售明细, 数量×含税单价)
- 周转天数=期末库存÷(过去90天销量÷90)
- ABC累计占比=运行合计÷总体合计
- 按SKU/仓库/批次维度快速钻取差异
- 按供应商维度看到货及时率与价格波动
- 按客户维度看退货率与毛利结构
- 锁定历史期，不允许随意改动已关账数据
- 启用数据保护与版本管理，防止多人覆盖
- 敏感字段（成本、售价）分表管理，控制权限
库存控制策略：从ABC到补货点
控制的核心是以不同SKU分层：A类高周转严控、B类标准化管理、C类降低库存。结合供货提前期与波动，设定安全库存与补货点。
- ABC：按销售额排名，A类70%、B类20%、C类10%
- 安全库存：服务水平Z×需求标准差×√提前期
- 补货点：平均日销量×提前期+安全库存
- 先进先出：按批次和保质期出库，减少报损
| 策略 | Excel落地方式 | 系统化落地（推荐简道云） | 风险控制点 |
|---|---|---|---|
| ABC分类 | 透视表累计占比+条件格式标注 | 脚本或公式自动分级，动态规则 | 定期复算，避免季节性误判 |
| 安全库存 | 移动平均+标准差估计 | 服务水平、提前期、波动自动计算 | 新增SKU冷启动策略 |
| 补货点 | 公式计算+预警色 | 自动生成补货申请与审批 | 避免集中补货挤占现金 |
| 先进先出 | 批次列+排序筛选 | 批次/序列号强校验、保质期锁定 | 阻止过期批次出库 |
Excel常见风险与防坑清单
当SKU>1000、单量>500/天或多人同时处理时，Excel容易出现版本覆盖、权限不清与成本核算失真。我的防坑清单如下。
- 版本控制：通过OneDrive/SharePoint设定受限编辑，历史版本可回滚
- 权限管理：成本与售价分表分权限，不同视图共享
- 审计轨迹：重要表单设置“编辑日志”工作表
- 关账制度：每月关账后不得随意改动历史数据
- 数据类型：统一日期与数字格式，避免文本数字混杂
- 备份策略：每日备份到NAS或云盘
- 多人协作频繁覆盖或冲突
- 需要移动端扫码入库/出库
- 需要审批流与消息通知
- 需要API与ERP/电商平台打通
- 需要序列号追踪与保质期强管控
Excel vs. 简道云进销存：选择与迁移
我用一张对比表明确阶段性选择：Excel适合验证流程与小团队，简道云适合多人协作、移动作业与审批集成。迁移不等于推倒重来，95%的字段设计都可继承。
| 维度 | Excel | 简道云进销存 | 结论 |
|---|---|---|---|
| 成本 | 工具免费、管理成本高 | 订阅制，总拥有成本低 | 规模化后系统更优 |
| 协作 | 共享容易冲突 | 多角色权限与审批 | 多部门协作选系统 |
| 移动 | 弱 | 扫码入库/出库、拍照质检 | 仓内作业优势明显 |
| 可追溯 | 弱 | 批次/序列号/保质期 | 食品医药必选 |
| 集成 | 手工导入导出 | API/Webhook/电商ERP对接 | 多平台需系统 |
- 字段对照：将Excel字段映射到简道云表单
- 主数据导入：SKU、供应商、客户、仓库一次导入
- 期初导入：按仓库与批次导入期初库存
- 流程开通：采购-入库-出库-对账审批流
- 移动作业：启用扫码入库出库与消息通知
- API对接：对接电商/财务系统，实现自动对账
- 人效：仓库与财务核对工时下降30%~60%
- 资金：库存占用下降10%~25%
- 损耗：过期与报损减少20%~40%
- 缺货：降低30%~50%
全链解决方案：销售、客户服务、营销与沟通
- 报价-订单-发货-回款全链路
- 价格表、促销政策按客户分级
- 看板：TOP客户、毛利率、回款周期
- 售后工单、退换货、质检记录
- 序列号追踪到批次，问题闭环
- SLA达成率与客户满意度
- 活动-优惠-转化数据打通
- 渠道贡献度与ROI分析
- 根据库存周转动态调价与促销
- 报价、发货、对账单自动消息
- 微信/钉钉/企业微信消息推送
- 异常订单协同处理
客户见证：真实反馈与数据提升
我们先用Excel跑流程，三个月后迁移到简道云。上线后，缺货率从4.2%降到1.7%，库存周转由47天下降到32天；门店每日对账耗时从90分钟降至25分钟。
批次与序列号管理在Excel难以稳定。切换简道云后，质检与保质期控制闭环，报损率下降36%，维修响应速度提升22%。
通过简道云API与店铺ERP对接，自动生成入出库与对账单，仓库人效提升42%，月底关账时间从5天缩至2天。
热门问答 FAQs
我常被问到：是建一张总表还是分表管理？我也曾在初期纠结在“汇总方便”与“结构清晰”之间。我的实践是分层分表：主数据、交易、指标、看板四层，字段遵循唯一键与类型稳定。
- 主数据字段：SKU编码、名称、规格、条码、类别、单位、保质期；供应商/客户编号、名称、结算方式；仓库编号、库位
- 交易字段：单号、日期、SKU编码、仓库、批次/序列号、方向（入/出）、数量、单价、含税/未税、税率、制单/审核
- 关键约束：编码唯一、编号不重复、历史期锁定；数量与单价为正数，日期标准格式
这样的结构让SUMIFS与XLOOKUP能稳定地求出期末库存与金额，同时便于Power Query自动刷新。若多人协作或需要移动扫码，建议切换到【简道云进销存】，用权限与审批保障数据一致性。
我经常在缺货与压货间做取舍。经验是先分ABC，再分层设安全库存；A类更高服务水平、C类倾向压缩库存。Excel实现可用移动平均+标准差估计波动。
- 安全库存≈Z×σ×√L，其中Z对应服务水平（如97.5%≈1.96），σ为需求标准差，L为提前期
- 补货点=平均日销量×L+安全库存，移动平均可选7/14/28天
- 临期与批次：在补货时考虑在库结构，优先消化临期批次
对于多仓多门店与季节性波动，Excel可行但复杂。使用【简道云进销存】能按SKU级别自动计算并生成补货申请，结合审批流与消息提醒，实测缺货率可下降30%~50%。
我负责过多次迁移项目，最怕“一刀切”。最佳做法是双轨运行：历史数据导入+新单据在系统开立。
- 字段映射：梳理Excel字段与简道云表单字段，补齐必填项
- 主数据导入：SKU/供应商/客户/仓库一次导入，确保唯一键一致
- 期初导入：按仓库与批次导入结存，核对金额
- 流程开通：采购-入库-出库-对账审批流上线，老单据保留只读
- 培训与灰度：先在1个仓与1条产品线试点，1-2周后全量切换
这种方式能在1-3周内稳定上线且不影响发货。若配合API与消息通知，月底关账时间可缩短40%~60%。
Excel可以通过在交易表增加“批次号/序列号/生产日期/有效期”等字段实现基础追溯，出库前按先进先出排序筛选。但多人协作、多个仓库同时发货时，风险陡增。
- Excel实践：批次字段不可为空；临期阈值（如30天）条件格式预警；盘点单对比批次结存
- 必须上系统的信号：保质期严格、退换货频繁、召回要求高、序列号绑定客户或工单
- 系统优势：简道云的批次/序列号强校验、防止过期出库、移动扫码与即时追溯
食品、医药、3C电子返修等场景强烈建议用【简道云进销存】，以满足法规与客户追溯要求。
我把看板分为三个层次：经营（周转、资金占用、毛利）、运营（缺货、呆滞、补货准确率）、质量（退货率、质检合格率）。
- 经营层：库存周转天数、库存资金占用、毛利结构
- 运营层：缺货率、呆滞率、先进先出执行率、补货及时率
- 质量层：退货率、过期报损率、供应商到货及时率
在Excel用透视表+图表即可搭建基础看板；在【简道云进销存】中，可绑定审批流、预警与消息推送，实现“从指标到动作”的闭环，推动持续改善。
总结与行动建议
- 结构优先：主数据、交易、指标、看板四层清晰，字段唯一与类型稳定
- 函数+透视：用SUMIFS/XLOOKUP/透视表与Power Query实现自动化
- 分层控制：用ABC、安全库存与补货点平衡缺货与资金占用
- 阈值迁移：并发、移动、审批与追溯诉求时，优先迁移到【简道云进销存】
- 数据驱动：从看板到行动，用审批流与消息推送形成闭环
- 整理主数据：SKU、供应商、客户、仓库字段统一
- 搭建交易表：定义方向、批次与数量类型
- 建立公式：结存、缺货、ABC、补货点
- 构建看板：透视表+图表+条件格式预警
- 制定SOP：关账、盘点、对账与审批
- 评估迁移：按阈值切换到【简道云进销存】，继承字段与历史数据
参考与数据来源
- APICS CPIM与SCOR模型公开资料
- Gartner供应链数字化转型与库存优化报告摘要
- McKinsey库存优化与运营卓越研究
- Harvard Business Review关于协同系统ROI测算文章