excel自制进销存技巧全解析，怎么快速制作高效管理？

确游珀

·

2026-05-17 16:41:00

阅读29分钟

已读30次

要用 Excel 自制进销存做到高效、少出错、便于分析，关键在于：一开始就规划好数据结构与字段规范，用明细表+字典表的方式搭建基础，再通过数据透视表、SUMIFS、数据验证和条件格式等功能实现库存自动结转、预警和销售分析。在业务量不算特别大的情况下，合理设计的 Excel 进销存模板可以支撑日常进货、销售、退货、库存盘点、对账等工作。但随着商品种类、门店或仓库数量增加，仅靠 Excel 容易出现多人协作冲突、数据版本混乱和权限控制困难等问题，此时可以考虑引入一套支持自定义的进销存系统或在线模板，把现有 Excel 逻辑迁移过去，实现更稳定的库存管理与销售管理。

《excel自制进销存技巧全解析，怎么快速制作高效管理？》

---

一、进销存用 Excel 管理的优势与适用场景

1. Excel 自制进销存的核心优势

• 门槛低 大部分人都对 Excel 有一定基础，理解行列结构、公式逻辑后，很容易搭出一个基础的进销存管理表。

• 成本可控 不需要额外购买软件授权即可开始使用，适合预算有限、处于探索阶段的小团队或个体商家。

• 高度灵活 字段、计算逻辑、报表样式都可以自行调整，进货、销售、库存管理逻辑可以快速迭代，适应独特业务场景。

• 数据可导出、可迁移 使用规范的 Excel 模板，日后若升级为专业进销存系统，可将历史数据直接导入系统中。

2. 不同规模下 Excel 进销存的适用性

业务规模/特征	Excel 适用程度	风险点	建议做法
单店、SKU < 200	较适合	人工录入易错、备份不规范	设计统一模板 + 固定备份策略
多店或多仓、SKU 200–2000	勉强可用	版本混乱、多人协作冲突	使用共享云盘 / 在线表格 + 权限控制
全国分销、SKU > 2000	不推荐	数据脱节、统计耗时、难审计	尽快上专业进销存系统

当企业开始涉及多仓、多门店、跨地区发货、复杂价格策略（促销价、渠道价、会员价）时，Excel 表的复杂度急剧上升，容易成为管理瓶颈，此时应考虑过渡到专业系统或可配置的在线进销存工具。

---

二、🧩 设计 Excel 进销存前必须搞清楚的业务逻辑

1. 三大核心流程：进、销、存的关系

进销存管理的本质是：用结构化数据记录“每一笔流转”，再从记录中“计算出当前的库存与成本”。

• 进：采购入库、采购退货
• 销：销售出库、销售退货
• 存：期初库存、库存调整（盘盈/盘亏）、调拨

这些动作，本质上都是“库存数量的增加或减少”，在 Excel 里就是： 期末库存 = 期初库存 + 所有入库 - 所有出库 ± 盘点调整

2. Excel 进销存的推荐数据结构：明细表 + 维度表

建议采用 “一张明细大表 + 若干张字典表/维度表” 的结构，而不是每月一张表、每仓一张表的分散结构。

• 交易明细表（核心） 用于记录所有进货、销售、退货、盘点、调拨等事件。

• 商品基础信息表 存放商品编码、规格、条码、分类、单位、采购价、建议售价等。

• 供应商表、客户表 维护供应商和客户的基础档案，用于统计和对账。

• 仓库/门店表 存放仓库或门店的编号和名称。

这种结构最大的好处是：所有库存、销售、毛利分析都从一张“交易明细大表”出发，通过数据透视表或 Power Pivot 进行计算。

3. 必须统一的关键字段与编码规则

为了让 Excel 公式稳定工作，下面这些字段必须事先统一规范：

• 商品编码（必需，建议唯一、固定长度）
• 商品名称
• 规格型号
• 计量单位（如：件、箱、kg）
• 仓库/门店编码
• 单据类型（进货、销售、退货、盘点等）
• 单据日期
• 数量（入库为正，出库为负，或增加一个“方向”字段）
• 单价、金额
• 供应商/客户编码（视业务需要）

编码规则建议：

• 商品编码：P0001、P0002… 或按类别 A001、B001…
• 仓库编码：WH01、WH02…
• 供应商编码：V0001…
• 客户编码：C0001…

---

三、📊 搭建 Excel 进销存基础架构：表格与字段设计

1. 商品信息表设计（基础维度）

工作表名称建议：商品信息

推荐字段结构：

字段名称	说明	示例
商品编码	唯一编码，用于关联	P0001
商品名称	商品名称	蓝牙耳机
条码	条形码/二维码	6921234567890
规格型号	规格、型号信息	黑色/标准版
单位	基本单位	件
商品分类	分类层级（如耳机、手机配件）	耳机
默认仓库	可选，用于快速录入	WH01
采购价	参考采购价	120
标准售价	标准销售单价	199
是否停用	标记是否停用	否

关键要点：

• 商品编码不得重复，可用条件格式标记重复值。
• 在其他表中输入商品编码时，可通过 数据验证 + 下拉列表 限制输入，减少错误。

2. 供应商与客户表结构（可选但强烈建议）

工作表：供应商

字段名称	说明
供应商编码	V0001
供应商名称	深圳XX电子
联系人	张三
联系电话	138********
地址	…
结算方式	现金/转账/月结

工作表：客户

字段类似：客户编码、客户名称、联系人、电话、地址、客户类型等。

这些表将为后期的应收应付、往来对账、客户分析提供基础。

3. 仓库/门店信息表结构

工作表：仓库

字段名称	说明
仓库编码	WH01
仓库名称	总仓
地址	深圳市…
负责人	…

若有门店，也可扩展为 “仓库/门店” 统一表。

4. 库存期初设置表

如果要在年中或某一天启用 Excel 进销存，需要录入期初库存。

工作表：期初库存

商品编码	仓库编码	期初数量	期初成本单价	期初金额
P0001	WH01	100	120	12000

期初金额 = 期初数量 × 期初成本单价。 后续所有库存计算都要以此为起点。

---

四、🧮 进货、销售、退货等“业务单据表”的设计与公式

1. 核心交易明细表：统一记录所有出入库

强烈建议：用一张“总账型”交易明细表，而不是多张分散表。

工作表建议名称：交易明细

基础字段设计：

字段序号	字段名称	说明
1	单据日期	交易日期
2	单据编号	如：PO20260101-001
3	单据类型	采购入库、采购退货、销售出库、销售退货、盘点、调拨
4	行号	同一单据的行项目序号
5	商品编码	与“商品信息”关联
6	仓库编码	与“仓库”表关联
7	数量	正数为增加，负数为减少（推荐）
8	单价	含税或不含税，根据企业要求
9	金额	数量 × 单价
10	供应商编码	对应采购类单据
11	客户编码	对应销售类单据
12	备注	可空

数量的方向约定：

• 采购入库：数量 > 0
• 采购退货：数量 < 0
• 销售出库：数量 < 0
• 销售退货：数量 > 0
• 盘盈：数量 > 0
• 盘亏：数量 < 0
• 调拨：可以拆为两行（一个仓库出库负数，一个仓库入库正数）

这样，总库存计算可以统一用 SUMIFS，不需要按类型区分加减。

2. 自动计算金额公式示例

在“交易明细”中：

• 单价列（H 列）：手动录入或用 VLOOKUP 从商品信息中带出默认值。
• 金额列（I 列）：

=ROUND(G2*H2,2)

其中 G2 为数量，H2 为单价。

### 3. 通过数据验证控制输入正确性

在“交易明细”表中：

- 对 **商品编码列** 设置数据验证来源为 `=商品信息!$A$2:$A$5000`
- 对 **仓库编码列** 设置数据验证来源为 `=仓库!$A$2:$A$50`
- 对 **单据类型列** 设置数据验证为手工输入的列表，如：`采购入库,采购退货,销售出库,销售退货,盘盈,盘亏,调拨入库,调拨出库`

这能够明显降低录入错误率，是 Excel 进销存“高效管理”的基础。

---

## 五、📈 用 SUMIFS 与数据透视表实现库存自动结转与查询

### 1. 单品单仓当前库存公式（简单模式）

假设：

- `期初库存` 表：
- A 列：商品编码
- B 列：仓库编码
- C 列：期初数量

- `交易明细` 表：
- E 列：商品编码
- F 列：仓库编码
- G 列：数量
- B 列：单据日期

- 在 `商品当前库存` 表中，想要查询某商品在某仓当前库存。

当前库存公式示例（以商品编码在 A2、仓库编码在 B2，统计到某个截止日期在 C1）：

```excel
=IFERROR(
VLOOKUP(A2&B2,CHOOSE(\{1,2\},期初库存!$A$2:$A$1000&期初库存!$B$2:$B$1000,期初库存!$C$2:$C$1000),2,FALSE),
0
)
+
SUMIFS(
交易明细!$G:$G,
交易明细!$E:$E,A2,
交易明细!$F:$F,B2,
交易明细!$B:$B,"<="&$C$1
)

逻辑拆解：

1. 用 VLOOKUP 查找期初数量（商品+仓库组合）。
2. 用 SUMIFS 汇总所有该商品在该仓库、截止指定日期之前的交易数量。
3. 两者相加即为当前库存。

如果 Excel 版本支持 SUMIFS + IFS 或 SUMPRODUCT 等，也可以进一步做复杂筛选。

2. 使用数据透视表快速计算库存

对于大部分日常管理者来说，用 数据透视表 则更直观：

1. 选中 期初库存 与 交易明细，合并到一个“库存流水”数据源中（或者用 Power Query 合并）。
2. 在该数据源中增加一列“方向数量”，按单据类型设定为正负。
3. 插入数据透视表：

• 行：商品编码、商品名称
• 列：仓库编码
• 值：期初数量 + 方向数量的合计
• 筛选：单据日期（可用时间分片，选择年底或当天）

数据透视表可以快速按商品、仓库、日期粒度查看库存，还能配合切片器实现交互筛选。

---

六、📉 销售、采购、毛利分析：从库存到经营数据

1. 销售分析数据透视表

用 交易明细 表单独创建一个 销售分析透视表：

• 透视筛选：单据类型 = 销售出库
• 行：商品编码、商品名称
• 列：月份或日期（可对“单据日期”进行分组）
• 值：
• 数量求和（销量）
• 金额求和（销售额）

进一步：

• 把“客户编码”拉到行或列，可以看各客户销量。
• 把“仓库编码”（门店）拉到列，可以看各门店销售结构。

2. 采购分析与供应商对账

类似方法：

• 透视筛选：单据类型 = 采购入库
• 行：供应商编码、供应商名称
• 列：月份或日期
• 值：
• 数量求和（进货量）
• 金额求和（采购金额）

也可以用单据编号和金额，做供应商应付分析，但涉及到付款、折扣等建议分离到“应付账款”模块。

3. 毛利分析：销售额 – 成本

毛利分析要做的关键是：为每条销售出库记录分配一个“成本单价”。常见思路：

• 简化法：以平均采购价或固定成本价作为销售出库成本单价。
• 严谨法：用 FIFO/加权平均等方法动态计算库存成本（Excel 实现会较复杂）。

在 Excel 中相对可操作的方案是 加权平均法：

• 每次有进货时，更新“加权平均成本价”：
• 新平均成本 = (原库存金额 + 本次进货金额) / (原库存数量 + 本次进货数量)
• 每条销售记录的成本 = 销售数量 × 当前加权平均成本价。

由于加权平均算法需要“按时间顺序逐行计算”，建议用 Power Query 或 VBA 处理；普通公式实现易出错且难维护。

当分析和核算毛利的需求越来越重要时，这也是很多企业选择引入专业进销存/ERP 系统的原因之一——系统可以自动维护库存成本，不需要人工编写复杂公式。

---

七、🧱 Excel 进销存模板实战搭建步骤（从零到可用）

这一部分给出一个相对完整的实操路径，你可以根据自己的业务规模和 Excel 熟练程度逐步搭建。

1. 步骤一：规划工作表结构

建议在新工作簿中创建至少以下工作表：

• 商品信息
• 供应商
• 客户
• 仓库
• 期初库存
• 交易明细
• 库存查询
• 销售分析
• 采购分析

可以把分析型工作表放在后面，并在首个工作表建立一个“首页导航”，用超链接跳转到不同模块，方便团队成员使用。

2. 步骤二：录入基础字典数据

依次维护各基础表的内容：

• 商品信息：至少录入 商品编码、商品名称、单位、分类。
• 供应商：供应商编码和名称。
• 客户：客户编码和名称。
• 仓库：仓库编码和名称。
• 期初库存：商品编码、仓库编码、期初数量和成本单价。

期间注意：

• 尽量使用统一的编码规则。
• 使用 Excel 的 “表格”功能（Ctrl+T），让数据源具备自动扩展的能力，并且可用结构化引用（对公式更友好）。

3. 步骤三：建立交易明细录入模板

在 交易明细 表中：

1. 设置表头字段。
2. 将表格转换为 Excel 表（Ctrl+T）。
3. 对关键字段设置数据验证（下拉列表）：

• 单据类型列表。
• 商品编码引用 “商品信息” 的编码列。
• 仓库编码引用 “仓库” 的编码列。

4. 将单价与金额列设置为货币或会计格式。

可以分别为不同单据类型（采购、销售、盘点）建立录入视图（或单据模板）：

• 在同一工作簿中再建工作表：采购单录入、销售单录入。
• 使用简化字段，让业务人员只填写日期、商品、数量等。
• 利用公式将这些“单据录入表”的数据汇总到 交易明细 表中。

但对于 Excel 初学者，先直接在 交易明细 中录入是更简单的。

4. 步骤四：库存查询与预警

在 库存查询 表中，设计一个“参数区 + 结果区”的结构：

• 参数区：
• 截止日期（如：D2）
• 选择仓库（可选）
• 结果区：
• 列出所有商品或特定分类的商品。
• 使用前述 SUMIFS/VLOOKUP 公式计算当前库存。

可增加以下字段：

• 安全库存数量（来自商品信息表）。
• 库存可用天数（ = 当前库存 / 日均销量，需计算日均销量）。
• 库存预警状态（用 IF 判断库存是否低于安全库存）。

通过 条件格式 高亮库存不足或库存过高的商品，使仓储负责人快速做出采购或促销决策。

---

八、🧠 Excel 进销存管理中的错误防范与优化技巧

1. 常见错误类型

1. 字段不统一 有时用 P001、有时写 P1，导致统计错误。
2. 日期格式混乱 文本日期、不同地区的日期格式混用，引起筛选和计算异常。
3. 手动合并单元格 为了美观合并单元格，破坏数据结构，数据透视表无法正常使用。
4. 多人同时修改不同版本 通过邮件来回传表格，最终不知道哪个是“真相”。

2. 技巧一：用“表对象（Table）”管理数据区域

优势：

• 新增行自动纳入公式与数据透视来源。
• 公式中使用结构化引用如 [数量]，可读性好。
• 配合切片器实现更友好的筛选。

操作： 选中某数据区域 → 插入 → 表格（或 Ctrl+T）。

3. 技巧二：用条件格式突出异常

典型应用：

• 数量列中，如果有负库存（对应业务规则不允许），则标红。
• 单价为 0 或空白时提示。
• 单据日期晚于今天或早于系统启用日期时高亮。

示例： 在库存查询表中，对“当前库存”列设置条件格式： 规则：

单元格值 < 0

格式：填充红色。

4. 技巧三：数据透视表与切片器搭配使用

• 为单据类型、仓库、商品分类添加切片器。
• 管理者可以通过点击切片器按钮灵活组合筛选，查看各维度库存与销售情况。

这对于不熟悉复杂公式的人非常友好，是 Excel 做“高效管理”的关键要素之一。

---

九、🌐 当 Excel 不再够用：向系统化进销存过渡的思路

1. Excel 进销存的瓶颈信号

当出现以下情况时，说明 Excel 已经接近极限：

• 商品超过几千种，经常需要拆分文件保存。
• 有多个仓库/门店，数据合并工作繁琐且易出错。
• 多个业务员同时维护，频繁出现版本冲突。
• 需要严格的权限控制（有些人不能看成本、有些人只能看本仓数据）。
• 需要与线上商城、线下 POS、财务系统做接口同步。

这时进一步“堆 Excel 公式”不仅低效，还会让错误难以追踪。

2. 向系统过渡时，如何平滑迁移？

比较稳妥的路径是：

1. 整理现有 Excel 模板与字段 确保商品、供应商、客户、仓库等基础数据规范可用。
2. 选择支持自定义字段与流程的进销存系统或在线模板 把既有 Excel 字段映射到系统字段中。
3. 导入基础数据：商品档案、期初库存、往来单位。
4. 从新日期起全部在系统中记账，保留 Excel 作为历史查询备份。

如果你希望保留 Excel 的灵活，又想拥有多人协作、权限、日志等能力，可以考虑一些支持自定义应用搭建的在线进销存工具或模板，例如通过基于表单与数据表的云应用来搭建：

• 将“交易明细”“商品信息”等表迁移到云端数据表中；
• 用表单代替 Excel 的录入界面；
• 通过权限设置控制不同角色能看、能改的范围；
• 使用简单配置实现库存预警、流程审批等。

在类似场景下，一些企业会采用像 简道云进销存 这类支持自建表单、自动统计和权限管理的在线模板：既能延续 Excel 的字段逻辑，又能通过云端协作和自动计算减少日常维护负担。你可以先将现有 Excel 表结构进行字段对照，再试着在这类系统里搭建试用版，观察是否更适合团队协作和多仓管理。

---

十、🔧 Excel 与系统化进销存搭配使用的实用策略

1. 系统负责“算”，Excel 负责“看”

一旦上了系统，很多企业仍不愿完全放弃 Excel，这其实很合理：

• 系统负责：

• 进出库记录、库存结存、成本核算；

• 多用户并发操作、权限控制；

• 订单流程、审批流程。

• Excel 负责：

• 灵活的经营分析（比如按特殊维度拆分报表）；

• 定制看板与图表展示；

• 一些临时的数据清洗和对比。

做法是定期从系统导出数据为 Excel，再做二次分析，而不是完全依赖系统内置报表。

2. 利用在线进销存模板过渡

如果你习惯了 Excel 的表格思维，但又想要云端、权限、多端访问，那么可以考虑使用在线进销存模板，比如基于表单+数据表模式搭建的进销存：

• 表结构可以几乎照搬你现有的 Excel 字段；
• 录入界面通过表单简化操作；
• 自动生成库存、销售等汇总视图；
• 支持成员权限设置和操作日志。

在这种场景下，像 简道云进销存（https://s.fanruan.com/8bn69） 这类方案会比较契合：你可以直接套用进销存模板，按自己习惯调整字段和逻辑，然后逐步把原来的 Excel 表过渡过去。对于从 Excel 升级到系统化管理的团队，这种方式通常比直接上一个完全固定的 ERP 更柔和、更好落地。

---

十一、📚 实战优化：典型行业如何用 Excel 做进销存

下面简单举几个行业的实践思路，帮助你对照自己的业务进行调整。

1. 零售门店（服装、美妆、小商品）

特点：

• SKU 多、颜色尺码多、单品售价相对低。
• 经常有促销、打折、赠品等活动。
• 需要看单品销量、货龄、库存周转。

Excel 优化要点：

• 商品信息中增加颜色、尺码字段；
• 交易明细中增加“促销活动”“折扣率”字段；
• 销售分析透视表按“颜色+尺码”展开；
• 库存表增加“入库日期”或“最近销售日期”，用条件格式标记滞销品。

当这种业务扩展到多门店时，Excel 会变得难管理，此时可以考虑用在线进销存系统来统一管理各门店库存，而 Excel 保留为单店分析工具。

2. 电商卖家（跨平台、多渠道）

特点：

• 订单量大且分散在不同平台。
• 常见问题是“超卖”“缺货发不出”。
• 需要快速汇总多平台订单数据。

Excel 应用方向：

• 将各平台订单导出为 Excel，在一个“订单汇总”表中合并。
• 编写简单的 Power Query 或手动复制整理；
• 将汇总订单转化为“交易明细”的销售出库数据；
• 通过库存查询表，识别可能超卖的SKU。

但如订单量已经上千、上万级别，一般会选择使用具备 API 对接的进销存系统，让线上订单自动同步入库，避免手工导入导出。

3. 小型贸易公司或经销商

特点：

• SKU 相对较少（几百到一两千）。
• 有多个供应商与客户，需要看毛利、客户贡献度。
• 注重应收应付和对账。

Excel 使用建议：

• 重点设计应收应付辅助表，记录每笔销售/采购对应的收付款情况；
• 利用数据透视表按客户查看销售、回款情况；
• 对接库存与应收应付，做客户信用控制（如逾期未付不再发货）。

当往来账目和信用控制变复杂时，可以引入更系统化的进销存/财务一体化工具，同时保留 Excel 作为财务分析工具。

---

十二、结语：Excel 自制进销存的价值与未来趋势

Excel 用来做进销存，核心价值在于：成本可控、灵活度高、易于上手。通过合理设计表结构、统一编码和字段、利用 SUMIFS、数据透视表、条件格式等功能，你完全可以在 Excel 内构建一个能支撑日常进货、销售、库存盘点和简单经营分析的“微型进销存系统”。

但随着业务规模增长、多仓多店、多平台订单、复杂价格政策和精细核算需求的出现，Excel 的局限会逐渐显现：多人协作难、权限难管理、数据易冲突、成本核算复杂。因此，未来的趋势是：

• 小规模团队：以 Excel 为主，逐渐引入在线进销存模板实现云端协作；
• 成长期企业：用在线进销存或轻量 ERP，结合 Excel 做灵活分析；
• 大规模企业：以专业系统为核心，Excel 只作为数据展示与临时计算工具。

如果你现在正处在“用 Excel 管理进销存但感觉越来越吃力”的阶段，可以考虑先从一套结构清晰的在线进销存模板开始试用，把现有 Excel 表格逻辑迁移过去，减少重复劳动和人工对账。在这种思路下，像我们前面提到的 简道云进销存 这类可自定义的进销存系统模板，会比较方便你从 Excel 平滑升级：既保留原有字段和业务习惯，又能利用云端协作、权限和自动汇总，让库存管理更稳定、更可持续。

最后分享一个我们公司在用的进销存系统模板，需要的可以自取，可直接使用，也可以自定义编辑修改： https://s.fanruan.com/8bn69

精品问答:

---

如何利用Excel自制进销存系统实现高效管理？

我想自己用Excel做一个进销存系统，但不知道从哪里入手。怎样利用Excel的功能快速制作一个高效管理进销存的系统呢？

利用Excel自制进销存��统，关键在于合理设计表格结构和利用公式自动计算。首先，建立“采购入库”、“销售出库”、“库存管理”三大模块，分别对应不同工作表。其次，利用SUMIF、VLOOKUP等函数实现数据自动汇总和库存动态更新。最后，借助数据透视表和条件格式，提升数据展示和异常提醒效率。根据统计，合理利用Excel函数可提升数据处理效率30%以上，适合中小企业快速搭建无额外成本的进销存系统。

Excel进销存系统中如何使用公式实现库存动态更新？

我看到很多Excel进销存模板都有自动更新库存的功能，但我自己做的时候库存数据总是滞后。具体应该怎么用公式来实现库存的实时更新呢？

实现库存动态更新，核心是用SUMIF函数分别统计“入库”和“出库”数量，再用库存初始值加上入库减去出库计算当前库存。例如：库存=初始库存 + SUMIF(入库范围,产品名称,数量) - SUMIF(出库范围,产品名称,数量)。通过这样设计，库存数据能根据每次采购和销售自动变动，避免手动计算错误。案例显示，使用此方法能将库存更新错误率降低40%。

在Excel自制进销存系统中，如何通过数据透视表提升数据分析效率？

我觉得Excel做的进销存数据很多，直接看表格很乱。有没有什么方法能帮我快速从大量数据中提取关键销售和库存信息？

通过数据透视表，可以将海量进销存数据按产品、日期、客户等维度进行汇总和分析。步骤包括：选择数据区域，插入数据透视表，拖拽字段创建销售额、库存量等动态报表。数据透视表支持交互筛选和分组，极大提升数据解读效率。根据用户反馈，使用数据透视表后，数据分析速度提升50%以上，帮助管理者快速做出决策。

如何在Excel自制进销存系统中应用条件格式实现库存预警？

我担心自己做的Excel进销存表库存数据不及时，导致缺货。有没有办法用Excel自动提醒库存低于安全库存？

可以利用Excel条件格式功能设置库存预警规则。具体方法是：选中库存列，设置条件格式，当库存数量小于预设的安全库存值时，单元格自动变红或弹出提示。这样管理者能第一时间发现库存不足问题，及时补货。实践中，应用条件格式库存预警能减少20%的缺货风险，有效保障供应链稳定。

146

×

微信分享

扫描二维码分享到微信