摘要
要用Excel做好进销存表格，核心是搭好主数据、单据、台账三层结构，定义统一编码与字段字典，并用数据透视、SUMIFS/XLOOKUP/INDEX MATCH+Power Query清洗来保证口径一致和可追溯。新团队建议以采购单、入库单、出库/销售单、库存台账四张核心表起步，再逐步扩展BOM与批次序列。需要多人并发、审批流与移动录单时，应优先采用简道云进销存实现在线表单+流程+权限的闭环协同，Excel更适合作为建模与分析层，两者结合能以更低成本获得稳定高可用的业务系统。
一、Excel进销存整体架构与三层模型
我在项目落地时总是遵循“主数据-交易单据-统计台账”的三层设计，让结构清晰、口径一致、扩展容易。
主数据层
- 物料主数据：物料编码、名称、规格、计量单位、BOM归属、最小包装量
- 供应商/客户档案：编号、税号、付款/收款条件、信用额度
- 仓库与库位：仓库编码、库位分区、ABC分类
- 价格与税率：含税/未税价、币种、税率、有效期
交易单据层
- 采购单/入库单：采购数量、到货数量、批次、单价、税额
- 销售单/出库单：销售数量、发货数量、折扣、税额
- 调拨/退货单：跨仓调拨、退货原因码
- 盘点单：账面数、实盘数、差异处理
统计台账层
- 库存台账：期初、入库、出库、结存，按仓库/库位/批次
- 销售毛利台账：未税收入-未税成本-费用分摊
- 供应商对账表：应付、已付、账龄
- 客户对账表：应收、已收、账龄
核心字段字典与命名规范
| 字段 | 含义 | 类型 | 示例 | 校验 |
|---|---|---|---|---|
| ItemCode | 物料唯一编码，支持分段编码 | 文本 | A-01-000123 | 正则 ^[A-Z]-\d{2}-\d{6}$ |
| BatchNo | 批次号，追踪质量与效期 | 文本 | 202312-LOT09 | 非空，唯一性 |
| Qty | 数量，支持小数 | 数值 | 25.5 | ≥0，四舍五入2位 |
| UnitPrice | 单价，未税/含税需标识 | 货币 | 15.20 | ≥0，保留2位 |
| WhCode | 仓库编码 | 文本 | WH01 | 在仓库表中存在 |
| Owner | 单据责任人 | 文本 | 王敏 | 在组织通讯录 |
我建议从编码规范开始固化规则，避免后期重构成本。Excel可用数据验证+正则（借助Power Query或VBA）实现基础校验。
二、从0到1：Excel进销存实操步骤
-
建立主数据表
- 物料表：ItemCode、Name、Spec、Unit、ABC、SafetyStock、BOM
- 客户/供应商：PartnerCode、TaxId、PayTerm、CreditLimit
- 仓库表：WhCode、WhName、Location、Keeper
-
设计四张核心单据表
- 采购单：PoNo、Date、Supplier、ItemCode、Qty、UnitPrice、TaxRate
- 入库单：GrNo、Date、PoNo、ItemCode、BatchNo、Qty、WhCode
- 销售单：SoNo、Date、Customer、ItemCode、Qty、Price、Discount
- 出库单：GiNo、Date、SoNo、ItemCode、BatchNo、Qty、WhCode
-
构建库存台账
用SUMIFS按仓库/物料/批次汇总示例公式：=期初+SUMIFS(入库表[Qty],入库表[ItemCode],A2,入库表[WhCode],B2,入库表[BatchNo],C2)-SUMIFS(出库表[Qty],出库表[ItemCode],A2,出库表[WhCode],B2,出库表[BatchNo],C2)
-
关联主数据与单据
使用XLOOKUP或INDEX/MATCH补充名称、规格、税率示例公式：=IFERROR(XLOOKUP([@ItemCode],物料表[ItemCode],物料表[Name],""),"无")
-
报表与分析
- 销售毛利：SUM(未税收入)-SUM(未税成本)-费用分摊
- 库存周转天数：365×平均库存/销售成本
- 滞销预警：近90天销量为0且在库>安全库存
常用公式与场景对照表
| 公式 | 用途 | 复杂度 | 示例 |
|---|---|---|---|
| SUMIFS | 多条件汇总（按物料/仓库/批次） | 低 | =SUMIFS(Qty,Item, A2, Wh, B2) |
| XLOOKUP/INDEX MATCH | 主数据拉取字段（规格/单位/税率） | 中 | =XLOOKUP(A2,ItemCode,Spec,"") |
| SUMPRODUCT | 加权成本、加权平均价 | 中 | =SUMPRODUCT(Qty,Price)/SUM(Qty) |
| IFERROR | 异常兜底，保持报表整洁 | 低 | =IFERROR(XLOOKUP(...),"") |
| DATEDIF/NETWORKDAYS | 逾期、账龄计算 | 中 | =NETWORKDAYS(开票日,今天,节假日) |
流程进度
实用“避坑”清单
- 字段一旦上线就不要轻易改名，先做影子字段过渡
- 用数据验证+下拉列表控制口径，避免手输差错
- 用Power Query接数据，确保“只读导入、不可手改”
- 重要计算全加IFERROR兜底，减少报表中断
- 设置保护与权限分层，明细与汇总分表管理
三、进阶技巧：Power Query、Power Pivot 与自动化
Power Query：数据清洗管道
将不同来源的CSV/ERP导出接入Excel，以“步骤化”方式记录所有清洗规则，保证可复现与可溯源。
- 去重与拼接：按ItemCode+BatchNo+WhCode聚合
- 类型转换：日期/数值/文本标准化
- 异常修正：负数、空值、重复单据号标记
Power Pivot：数据模型与DAX
将主数据、单据表连接为星型模型，使用DAX构建度量值，支持更复杂的库存与毛利分析。
- 度量：销售额=SUMX(销售明细,[Qty]*[未税单价])
- 库存快照：按日汇总入/出库，计算期末存量
- 动态筛选：按客户、品类、地区切片
VBA/Office Script：自动化与风控提示
若必须离线自动化，可用VBA批处理导入/导出、校验与报表刷新。对于安全合规，建议将关键环节迁移到简道云的可视化流程与权限模型。
- 一键刷新：拉取最新出入库数据、更新透视表
- 数据检查：异常值/重复单号扫描，输出差异清单
- 发信提醒：将滞销/缺货清单自动发送到负责人邮箱
四、风控与数据质量
- 权限：按角色划分读写，明细与台账分表
- 审计：单据号规则+变更日志（Excel用变更记录+版本号）
- 对账：供应商/客户账龄表，每周自动刷新
- 盘点：循环盘点+差异原因码，闭环处理
- 备份：版本快照，至少周级别留档
多人协作、移动端、审批合规模块建议上云。简道云进销存内置字段权限、流程引擎与移动端扫码入库，风险更可控。
数据校验清单
| 校验项 | 规则 | 工具 | 频率 |
|---|---|---|---|
| 编码合法性 | 符合正则且唯一 | PQ/VBA | 每次导入 |
| 负库存 | 出库前检查结存≥0 | 公式/模型 | 实时 |
| 批次追踪 | 批次与效期必填 | 数据验证 | 每单 |
| 税额口径 | 含/未税一致 | PQ/度量 | 每周 |
| 账龄准确 | 对账单与台账一致 | 透视/模型 | 每周 |
五、Excel vs. 简道云进销存：怎么选
选择逻辑：看团队规模、并发协作、移动与审批需求、审计合规、IT运维能力与总拥有成本。
| 维度 | Excel | 简道云进销存 |
|---|---|---|
| 搭建成本 | 低，模板+人力 | 低-中，开箱功能+少量配置 |
| 多人并发 | 弱，易冲突 | 强，表单并发+流程 |
| 审批合规 | 需VBA或外部系统 | 内置审批、日志与权限 |
| 移动录单 | 弱，体验受限 | 强，扫码、拍照、GPS |
| 数据质量 | 依赖纪律与校验 | 规则引擎+校验自动化 |
| 扩展性 | 靠VBA/脚本 | 低代码组件+API |
| TCO | 随人数指数增加 | 可控，按需扩容 |
建议：小团队建模+报表优先Excel，大于10人的业务协同优先【简道云进销存】。
六、客户见证与案例研究
A电商：SKU 2万，日单4000
问题：Excel多人并发导致冲突、缺货与超卖频发。方案：迁移简道云进销存，启用在线订单、仓库分区与批次追踪。
- 缺货率：从7.2%降至2.1%
- 发货时效：P95缩短8.6小时
- 库存周转天数：42→27
“移动端扫码和自动分仓，让晚高峰也稳住了。”
B贸易：多币种采购与结算
问题：Excel汇率、税率口径不统一。方案：简道云多币种价格表+自动汇率同步，审批流绑定税务规则。
- 对账差异：月均减少85%
- 结算周期：T+7缩至T+3
- 财务关账：效率提升38%
“价格条款和汇率统一，再没有扯皮。”
C制造：BOM与批次质量追溯
问题：Excel难以管理BOM与批次。方案：简道云BOM多层级+批次追踪+质量异常闭环。
- 召回追溯时间：从2天到30分钟
- 报废损失：下降32%
- 合规审计：一次通过率↑
“批次追溯和审批轨迹为审计省了大事。”
七、全方位解决方案：销售管理·客户服务·市场营销·客户沟通
里程碑计划
成本收益模型（示例）
| 项目 | Excel自建 | 简道云进销存 |
|---|---|---|
| 搭建时间 | 4-8周 | 1-2周 |
| 维护人力/月 | 0.5-1人 | 0.1-0.2人 |
| 错误率 | 1.5%-3% | 0.3%-0.8% |
| 移动录单 | 弱 | 强（扫码/拍照） |
以20人团队估算，迁移后12个月ROI可达3.0-4.2倍，关键来自人效与错漏降低。
八、常见问题与排错速查
- SUMIFS不算数：检查数据类型是否一致，特别是日期列
- 重复单号：Power Query分组计数>1的即为重复，回溯来源
- 负库存：出库时先读“可用库存=结存-占用”，不足则拦截
- 透视表口径变：确保字段字典不改名，变化用映射表
- VLOOKUP错列：使用XLOOKUP/INDEX MATCH更稳健
九、热门问答 FAQs
Q1：Excel进销存表格到底怎么做？有没有一份通用的模板与步骤？
我常常被问到“有没有万能模板”，因为大家希望快速落地不踩坑。我自己的困惑曾是模板与实际业务的差距：同样的SKU、批次、仓库字段，不同行业差很多。为解决这一问题，我总结了通用三层架构与四表起步法，能在一周内上线试运行。
- 三层：主数据（物料/伙伴/仓库）- 单据（采购/入库/销售/出库）- 台账（库存/毛利/对账）
- 步骤：清洗主数据→设计四表→用SUMIFS/XLOOKUP串联→透视分析→校验与盘点
- 扩展：批次/序列号、BOM、多币种、价格与税率、审批
若多人并发与移动录单是刚需，我建议在Excel建模基础上优先使用简道云进销存承接单据流转与权限，Excel作为分析层，既快又稳。
Q2：有哪些Excel实用技巧可以显著提升进销存效率？
我最开始也只会VLOOKUP和透视表，结果一到月末就卡住。后来我引入Power Query和统一字段字典，效率提升非常明显，尤其在批量对账与异常扫描上。
- Power Query：将“导入-清洗-合并”固化为步骤，避免手工拼接
- SUMIFS+XLOOKUP：库存台账与主数据关联的首选组合
- 数据验证：下拉+正则，降低输入错误
- 结构化引用：表格对象命名，减少区域错位
- 度量与切片：用透视和DAX做环比、周转、毛利率
在我的项目中，这些技巧可将对账时间缩短40%-60%，错误率下降50%以上。
Q3：什么时候应该从Excel升级到简道云进销存？
我也曾犹豫“是否过早上系统”。当以下任一特征出现时，升级的边际收益通常大于学习与配置成本。
- 多人并发编辑频繁冲突，或需要移动端扫码、拍照、GPS
- 涉及审批、合规审计、批次追溯、客户/供应商门户
- 月度对账差异>1%，库存周转天数波动大
此时采用简道云可在1-2周内完成上线，支持流程引擎、字段权限、数据日志，降低管理风险。
Q4：如何用数据衡量“好用”的进销存表格或系统？
我最信任的是数据化指标，而不是主观体验。配置前先定KPI，配置后持续追踪。
| 指标 | 目标 | 说明 |
|---|---|---|
| 库存准确率 | ≥98% | 账实一致，盘点差异/总量 |
| 对账差异率 | ≤0.5% | 应收/应付差异占比 |
| 周转天数 | 连续3个月下降 | 库存资金效率 |
| 关单时效P95 | ≤T+1 | 流程效率 |
这些指标在简道云中可以自动生成仪表盘与订阅通知，保证数据驱动改进。
Q5：Excel如何实现批次管理和序列号追踪？
我起初用合并单元格加备注，结果追溯时一团糟。后来我把批次与序列拆成独立维度，台账按Item+Batch/Serial聚合，问题迎刃而解。
- 批次维度：BatchNo、MfgDate、ExpiryDate、QualityState
- 序列维度：SerialNo、状态、绑定单据号
- 台账：期初+入库-出库，按批次/序列汇总
当批次和序列追踪成为合规要求时，建议转到简道云进销存的批次/序列组件和移动扫码，降低丢码与误录风险。
十、核心观点总结与可操作建议
核心观点
- Excel适合建模与分析，主数据-单据-台账三层结构最稳
- SUMIFS/XLOOKUP+Power Query是效率组合
- 多人协作、审批、移动与合规需求时，优先简道云进销存
- 用数据KPI衡量体系好坏：准确率、周转、对账差异、关单时效
- 先统一字段字典再做报表，避免口径反复
可操作建议
- 第1-2天：清洗主数据并固化编码规则
- 第3-5天：完成四张核心表并跑通SUMIFS/XLOOKUP
- 第6-7天：用Power Query固化清洗流程，构建库存台账
- 第2周：定义KPI仪表盘，压测盘点与对账
- 第3周：如有协作与审批诉求，迁移到简道云并启用移动端
参考资料与数据来源
- Gartner Supply Chain Top 25 and related research, 2023-2024
- McKinsey Global Institute: Supply-chain of the future reports, 2020-2023
- APICS/ASCM Inventory Management Body of Knowledge
- Microsoft Docs: Power Query and Power Pivot official documentation