Excel进销存管理技巧，如何高效制作进销存表格？

暑肠碑

·

2026-05-20 02:57:19

阅读39分钟

已读33次

在 Excel 中进行进销存管理，关键是用好「结构化表格 + 函数公式 + 数据验证 +透视表统计」。通过合理拆分「商品档案、供应商/客户、采购明细、销售明细、库存台账」等核心表格，并为每个表格设置标准字段和唯一编码，可以明显降低出错率。结合 SUMIFS、IFERROR、VLOOKUP/XLOOKUP 等函数，能实现自动汇总「当前库存、成本、毛利」等指标。再配合数据透视表制作「商品销售排行、库存预警、供应商对账」等动态报表，基本能覆盖中小企业日常进销存管理需求。当业务复杂到多仓、多规格、多人员协同时，可以考虑在 Excel 模板基础上迁移到在线进销存系统，例如通过类似简道云进销存这类可视化模板快速搭建云端进销存方案，实现手机/网页多端协同。

《Excel进销存管理技巧，如何高效制作进销存表格？》

---

Excel进销存管理技巧，如何高效制作进销存表格？

😀 一、进销存管理在 Excel 中的定位与适用场景

1. 为什么很多企业仍然习惯用 Excel 做进销存？

在企业信息化发展中，Excel 进销存表格长期是常见方案，原因包括：

• Office 普及，员工上手快；
• 无额外授权费用；
• 模型易修改，灵活试错；
• 适合中小企业尝试规范化管理。

在「采购-入库-销售-出库-库存盘点」等业务场景中，只要设计合理，Excel 完全可以支撑：

• 商品档案管理（SKU、条码、单位、规格等）
• 采购记录和采购入库
• 销售订单、销售出库及对账
• 库存结存、库存调拨、盘亏盘盈记录
• 简单的利润分析、销量排行统计

2. Excel 进销存的优势与天然局限

维度	Excel 进销存优势	Excel 进销存局限
成本	无额外软件成本，利用现有 Office 即可	人工维护成本高，容易因误操作导致数据损坏
灵活性	结构可随业务调整，自由加字段、加工作表	缺乏统一规范，不同人建表风格差异大，难以长期维护
上手门槛	员工普遍会用 Excel，培训成本低	函数、透视表等高级能力有门槛，高度依赖关键人员
协同	可通过网盘共享、邮件发送	多人同时编辑容易冲突，版本混乱，缺乏权限控制
安全与审计	支持简单的密码、保护工作表	安全防护有限，数据篡改难以追踪，审计记录几乎为零
业务复杂度	能覆盖单仓、多 SKU 的基础进销存需求	多仓、多门店、复杂审批、批次序列号管理等场景很难仅靠 Excel 实现

适用场景（Excel 仍然是高性价比选择）：

• 单仓或少量仓库；
• 商品数量（SKU）在几百到三千左右；
• 参与管理的人员不多，流程相对简单；
• 不涉及复杂批次管理、保质期追踪、序列号追踪等精细化要求。

不太适合仅用 Excel 的情况：

• 多门店、多仓、多部门并行操作；
• 需要与财务系统、线上商城、ERP深度集成；
• 对「权限、日志、审批流」有严格要求；
• 对实时库存准确性要求极高（例如医药、冷链、精密设备等）。

在这些场景下，可以考虑在 Excel 模板基础上逐步上云，例如把现有结构迁移到像简道云进销存这样的在线应用中，通过可视化表单和流程来固化规则，同时保留类似 Excel 的灵活度。

---

📊 二、Excel 进销存表格的整体信息架构设计

1. 为什么要先设计信息架构？

很多人做 Excel 进销存一上来就建一个「进销存总表」，所有东西堆在一起：日期、供应商、客户、数量、单价、金额、库存…… 结果：

• 表格列多、逻辑混乱；
• 无法清楚区分「采购、销售、库存」数据来源；
• 后期统计报表极其困难。

正确做法：用数据库的思想来拆分表格：

• 主数据表：商品档案、供应商档案、客户档案；
• 交易明细表：采购明细、销售明细、调拨明细、盘点明细；
• 统计/分析表：库存汇总、销量分析、毛利分析等。

2. 标准的 Excel 进销存工作簿结构

推荐的工作簿（Workbook）结构示例：

1. 商品档案（Product_Master）
2. 供应商档案（Supplier_Master）
3. 客户档案（Customer_Master）
4. 采购明细（Purchase_Detail）
5. 销售明细（Sales_Detail）
6. 库存收发明细（Stock_Ledger，可选）
7. 库存汇总（当前库存）（Stock_Balance）
8. 分析报表（Analysis，例如销量排行、毛利分析）
9. 基础设置（Settings，例如税率、单位换算、下拉选项）

可以按「主数据 → 业务记录 → 分析报表」进行逻辑分区，避免混乱。

---

🧱 三、核心基础表格：商品、供应商、客户

1. 商品档案表：所有进销存的基础

核心关键词：商品档案、SKU、基础资料、条码、单位

商品档案表建议字段设计如下：

字段名	说明	示例
商品编码	唯一编码（手工编码或自动编码）	P0001、P202405001
条形码/货号	若使用条码枪推荐必填	6920012345678
商品名称	商品简称或标准名称	500ml 矿泉水
规格型号	规格参数，如 500ml、1kg、M/L/XL 等	500ml
计量单位	件、箱、包、瓶、kg 等	瓶
品牌	品牌信息（可选）	Brand A
类别	用于统计、筛选	饮料/日用品
成本价（参考）	最近或标准成本价（可选，用于报价和分析）	1.2
建议售价	对应标准零售价（可选）	2.5
是否停用	用于控制下拉列表，只给在用商品下单	是/否
备注	其他说明

实施建议：

• 把这一块设置为 Excel 表格（Ctrl+T），命名为 tbl_Product;
• 对 商品编码 列开启「不允许重复」的数据验证，确保唯一性；
• 用筛选（Filter）和分类汇总便于维护。

2. 供应商与客户档案表：主体信息统一管理

供应商档案表字段建议：

字段名	说明
供应商编码	唯一编码，如 S0001
供应商名称	公司名称或单位名称
联系人	对接人姓名
联系电话	联系方式
结算方式	现金结算、月结 30 天、货到付款等
税号/地址	用于开票（视需求）
是否停用	控制下拉是否出现
备注	补充信息

客户档案表字段建议：

字段名	说明
客户编码	唯一编码，如 C0001
客户名称	公司/门店/个人名称
客户类型	批发/零售/电商平台等
联系人
联系电话
结算方式
是否停用
备注

用途：

• 在采购/销售明细表中通过数据验证引用「供应商名称/客户名称」，减少手工输入错误；
• 后续做「供应商对账、客户对账、往来余额分析」时直接汇总。

---

🧾 四、采购明细表与销售明细表的结构设计

1. 采购明细表：从订单到入库的关键记录

核心关键词：采购明细、入库记录、进货记录

采购明细表建议字段设计：

字段名	说明
采购单号	唯一单号，如 PO20240518001
采购日期	交易发生日期
供应商名称	数据验证下拉，引用供应商档案
商品编码	数据验证下拉，引用商品档案
商品名称	可用公式从商品档案自动带出
规格型号	同上，从商品档案带出
单位	从商品档案带出
采购数量	本次采购数量
采购单价	含税或不含税单价，根据企业习惯
金额	=采购数量*采购单价
税率	可选，若需要税额与价税合计
税额	=金额*税率（如有）
价税合计	=金额+税额
仓库	多仓时标记入库仓
经手人	采购员或仓管人员
备注

关键技巧：

• 使用表格（Ctrl+T），命名为 tbl_Purchase;
• 采购单号 可以用日期 + 流水号方式，例如：="PO"&TEXT(采购日期,"yyyymmdd")&TEXT(行号,"000");
• 利用 数据验证+VLOOKUP/XLOOKUP 自动带出商品名称、规格、单位。

自动带出商品信息的典型公式示例（假设使用 XLOOKUP）：

在「商品名称」单元格（例如 E2）：

=XLOOKUP([@商品编码], tbl_Product[商品编码], tbl_Product[商品名称], "")

在「规格型号」单元格：

=XLOOKUP([@商品编码], tbl_Product[商品编码], tbl_Product[规格型号], "")

在「单位」单元格：

=XLOOKUP([@商品编码], tbl_Product[商品编码], tbl_Product[计量单位], "")

若使用 VLOOKUP，需要保证 商品编码 位于商品档案表第一列。

2. 销售明细表：出库与收入的基础数据

核心关键词：销售明细、出库记录、销售记录

销售明细表结构与采购类似：

字段名	说明
销售单号	唯一单号，如 SO20240518001
销售日期	交易发生日期
客户名称	下拉选择，引用客户档案
商品编码	下拉选择，引用商品档案
商品名称	从商品档案自动带出
规格型号	从商品档案自动带出
单位	从商品档案自动带出
销售数量	本次出库数量
销售单价	含税/不含税销售单价
金额	=销售数量*销售单价
税率/税额	如需要税务分析时可填写
价税合计	如需要
仓库	多仓时标注出货仓
业务员	销售员/业务员
备注

同样：

• 销售明细表设置为 tbl_Sales；
• 利用 XLOOKUP 或 VLOOKUP 带出商品名称、规格、单位；
• 利用数据验证限制「商品编码」只能选择已存在商品，避免乱填。

---

🔁 五、库存台账与库存结存的核心逻辑

1. Excel 中常见的两种库存思路

核心关键词：库存结存、库存台账、收发存

1. 按明细计算库存 不维护单独的「库存表」，只保留「采购明细（入库）+ 销售明细（出库）」，通过公式汇总得到当前库存：

• 当前入库总数 = 所有采购数量之和
• 当前出库总数 = 所有销售数量之和
• 当前库存 = 入库总数 - 出库总数

2. 维护库存台账/收发存明细 单独维护一张「库存收发明细表」，将采购入库、销售出库、盘点调整、调拨等统一记账，每行代表一次库存变动，包含「数量与方向」，再汇总到「库存汇总表」。

第二种方式更接近专业进销存系统，但对 Excel 用户来说操作略复杂。对于中小企业，常见做法是：

• 核心底层仍以“采购明细 + 销售明细”为准；
• 若有盘点/盘盈盘亏，再单独加一张「盘点明细表」进行调整；
• 库存汇总表通过 SUMIFS 或 数据透视表 动态计算。

2. 使用 SUMIFS 计算当前库存（按商品）

新建工作表 库存汇总，字段建议：

字段名	说明
商品编码	从商品档案引用
商品名称	通过公式自动带出
单位	从商品档案带出
累计入库数量	从采购明细表汇总
累计出库数量	从销售明细表汇总
期末库存数量	入库 - 出库（如有盘点，再加盘点调整）
参考成本价	可取最近采购价或标准成本
库存金额	期末库存数量 * 参考成本价

假设 tbl_Purchase 中：

• 入库数量列为 采购数量;
• 商品编码 列为 商品编码;

在库存汇总表中，「累计入库数量」可用：

=SUMIFS(tbl_Purchase[采购数量], tbl_Purchase[商品编码], [@商品编码])

同理，tbl_Sales 中销售数量列为 销售数量，则「累计出库数量」公式：

=SUMIFS(tbl_Sales[销售数量], tbl_Sales[商品编码], [@商品编码])

然后「期末库存数量」：

=[@累计入库数量]-[@累计出库数量]

如有盘点调整表 tbl_StockAdjust，把盘盈记正数、盘亏记负数，则：

= [@累计入库数量] - [@累计出库数量] + [@盘点调整数量]

盘点调整数量可通过类似 SUMIFS 从盘点表汇总。

3. 多仓库库存计算：增加仓库维度

若涉及多个仓库，需要在所有相关表格（采购明细、销售明细、库存汇总）中增加「仓库」字段。

库存汇总字段：

字段名	商品编码	商品名称	仓库	累计入库数量	累计出库数量	期末库存数量

入库数量公式示例（增加仓库条件）：

=SUMIFS(
tbl_Purchase[采购数量],
tbl_Purchase[商品编码], [@商品编码],
tbl_Purchase[仓库], [@仓库]
)

出库数量公式同理：

=SUMIFS(
tbl_Sales[销售数量],
tbl_Sales[商品编码], [@商品编码],
tbl_Sales[仓库], [@仓库]
)

这样即可得到「每个商品在每个仓库」的实时库存。

---

📐 六、常用函数与公式技巧：让进销存自动化

1. 查找与匹配：VLOOKUP / INDEX-MATCH / XLOOKUP

核心关键词：查找函数、匹配函数、自动带出

在进销存表格中，最典型的需求是「根据商品编码带出商品名称、规格、单位」，推荐使用：

• 新版 Excel（Microsoft 365 / 2021）：XLOOKUP；
• 旧版 Excel（2019及以前）：VLOOKUP + MATCH 或 INDEX-MATCH。

XLOOKUP 示例（推荐）

=XLOOKUP([@商品编码], tbl_Product[商品编码], tbl_Product[商品名称], "")

优势：

• 不要求查找列在第一列；
• 默认精确匹配；
• 可以设置找不到时的返回值（第四参数）。

VLOOKUP 示例

=IFERROR(
VLOOKUP([@商品编码], 商品档案!$A:$H, 2, FALSE),
""
)

商品档案!$A:$H 是商品档案表的区域，2 表示���二列（假设是商品名称）。

建议配合 IFERROR 使用，避免错误值影响后续计算。

2. 条件汇总：SUMIF / SUMIFS

核心关键词：条件汇总、统计函数、库存计算

SUMIFS 是库存汇总和业务分析中最关键的函数之一。

单条件示例：按商品编码汇总采购数量：

=SUMIFS(tbl_Purchase[采购数量], tbl_Purchase[商品编码], [@商品编码])

多条件示例：按商品 + 仓库 + 日期范围汇总某期间的进货量：

=SUMIFS(
tbl_Purchase[采购数量],
tbl_Purchase[商品编码], [@商品编码],
tbl_Purchase[仓库], [@仓库],
tbl_Purchase[采购日期], ">=" & 开始日期,
tbl_Purchase[采购日期], "<=" & 结束日期
)

这里「开始日期」和「结束日期」可以是库存汇总表中的参数单元格。

3. 处理异常与容错：IFERROR

IFERROR(公式, 错误时的替代值) 是进销存表格中不可或缺的「防崩溃」工具：

示例：

=IFERROR(
XLOOKUP([@商品编码], tbl_Product[商品编码], tbl_Product[商品名称]),
"未登记商品"
)

或者更简单地返回空字符串：

=IFERROR(公式, "")

这样不会出现大面积 #N/A 或 #VALUE!，方便打印和阅读。

---

🧮 七、使用数据验证与下拉列表降低出错率

1. 为商品、供应商、客户设置下拉列表

核心关键词：数据验证、下拉菜单、有效性检查

在「采购明细」和「销售明细」中，强烈建议使用数据验证：

1. 选中「商品编码」列（或「商品名称」列）；
2. 数据 → 数据验证 → 允许：序列；
3. 来源：选择商品档案表中「商品编码」列的区域，如 =商品档案!$A$2:$A$5000；
4. 同理，对「供应商名称 / 客户名称、仓库」等字段设置下拉。

好处：

• 避免手工输入错字导致统计失败；
• 确保数据统一规范，后续直接用 SUMIFS、透视表统计。

2. 使用数据验证防止数量和单价填错

可以对「采购数量、销售数量、单价」设置合理区间：

• 数量 ≥ 0；
• 单价 ≥ 0 且 ≤ 一个合理最大值（比如 100000）。

设置方式：

• 选中列 → 数据验证 → 允许：十进制/整数；
• 设定最小值、最大值；
• 设置「输入信息」提示用户填写规则。

3. 使用条件格式标记异常值

可以利用条件格式突出显示：

• 单价异常偏高/偏低的交易；
• 负库存记录；
• 库存低于安全库存的商品。

示例：在库存汇总表中，对「期末库存数量」列设置条件格式： 公式：

=[@期末库存数量] < [@安全库存]

将满足条件的行高亮为黄色或红色，作为库存预警。

---

📈 八、用数据透视表做库存与销售分析

1. 数据透视表在进销存中的作用

核心关键词：透视表、分析报表、动态图

数据透视表可以从采购明细、销售明细、库存汇总中快速生成：

• 某时间段内的商品销量排行；
• 按客户、按区域、按业务员的销售额分析；
• 按供应商的采购额、退货率等统计；
• 每月库存周转情况。

相比手写公式，透视表更直观、调整维度更灵活。

2. 基于销售明细做“商品销售排行”透视表

步骤示例：

1. 在 Sales_Detail 工作表（销售明细）中保证数据为 Excel 表格形式 tbl_Sales；
2. 插入 → 数据透视表 → 选择表/区域：tbl_Sales；
3. 新建透视表放到工作簿中的新工作表；
4. 字段拖拽：

• 行：商品名称
• 列：月份（可基于销售日期通过分组生成）
• 值：销售数量、销售金额（可各一个字段）

5. 排序：按销售数量或销售金额降序排列，即为商品销售排行。

3. 基于采购明细做“供应商采购分析”

同理，在 tbl_Purchase 上建立数据透视表：

• 行：供应商名称；
• 列：月份/季度；
• 值：采购金额；

可以评估各供应商的采购占比、季节性特点，辅助谈判和战略合作。

4. 利用透视图制作可视化仪表板

在透视表基础上：

1. 选中透视表 → 插入 → 透视图；
2. 选择柱状图、折线图、饼图等；
3. 使用切片器（Slicer）按时间、商品类别、仓库进行筛选；
4. 将多个图表和关键指标组合在一个「仪表板」工作表内，形成进销存管理驾驶舱。

这个仪表板可以按月或按周更新，只需刷新数据透视表即可。

---

🧱 九、高阶设计：收发存台账、批次与序列号（进阶）

1. 库存收发存台账表设计

对于希望更精细管理库存移动的企业，可以引入「收发存台账」：

字段名	说明
单号	对应采购单/销售单/盘点单等
日期	业务日期
业务类型	采购入库 / 销售出库 / 调拨 / 盘点调整等
仓库	仓库名称
商品编码
商品名称
单位
入库数量	入库时填写正数
出库数量	出库时填写正数
结存数量	可选，每次记一笔后计算库存余额（困难）
备注

在 Excel 中维护逐笔结存较费力，通常改为：

• 将收发存台账作为所有库存变动的明细来源；
• 库存汇总表中通过 SUMIFS 从收发存台账汇总 入库数量 和 出库数量 即可。

2. 批次管理与序列号管理（Excel 的现实边界）

若企业需要：

• 管理商品生产批次、保质期；
• 对每个设备的唯一序列号进行追踪；

可以在明细表/收发存台账中增加：

• 批次号、生产日期、有效期至；
• 或 序列号 字段（有时需要一行一台设备）。

这类管理在 Excel 中会迅速变得复杂：

• 明细记录急剧膨胀；
• 批次和序列号很难用透视表做追踪；
• 容易产生多版本文件和数据不一致。

此时建议评估把核心进销存逻辑迁移到在线系统，例如使用可自定义字段和流程的进销存系统模板，既保留批次、序列号、保质期等细节，又能做权限控制和日志记录。这类云端应用中，像简道云进销存这样的模板方案，通过类似 Excel 的表格界面和可配置字段，支持按需求扩展批次、仓库、维度等信息，对于从 Excel 升级到系统化管理的团队会更友好。

---

🧰 十、Excel 进销存模板搭建步骤（从零到可用）

下面给出一个从零搭建 Excel 进销存模板的步骤指南，便于落地实践。

步骤 1：创建主数据表

1. 新建工作簿；
2. 创建工作表：商品档案、供应商档案、客户档案；
3. 为每张表设计字段并设置为「表格」（Ctrl+T），分别命名为 tbl_Product、tbl_Supplier、tbl_Customer；
4. 设置数据验证确保编码唯一，可在录入时用重复值警告；
5. 录入基础数据（可从现有系统/文件导入）。

步骤 2：搭建采购明细表

1. 新建「采购明细」工作表；
2. 设计字段并设置为表格 tbl_Purchase；
3. 使用数据验证为「商品编码」「供应商名称」「仓库」等字段设置下拉；
4. 使用 XLOOKUP / VLOOKUP 自动带出商品名称、规格、单位；
5. 设置金额、税额、价税合计等字段的公式，确保自动计算；
6. 设置数据验证防止数量、单价填写异常；
7. 设置条件格式高亮金额异常大的行。

步骤 3：搭建销售明细表

与采购明细表同样方式：

1. 新建「销售明细」表 tbl_Sales；
2. 字段：销售单号、销售日期、客户名称、商品编码、商品名称、规格、单位、数量、单价、金额、仓库、业务员等；
3. 设置下拉、查找公式、金额公式和数据验证。

步骤 4：创建库存汇总表

1. 新建「库存汇总」工作表 Stock_Balance；
2. 用公式从 tbl_Product 复制商品编码、商品名称、单位，可用如下方式：

• 商品编码 列：引用或复制 tbl_Product[商品编码]；
• 商品名称 列：用 XLOOKUP 从商品档案带出；

3. 使用 SUMIFS 计算每个商品的累计入库、累计出库数量；
4. 计算期末库存数量 = 入库 - 出库；
5. 填写安全库存字段，对低于安全库存的商品使用条件格式预警；
6. 如需库存金额，增加「参考成本价」列，用手工维护或用公式取最近采购价。

获取最近采购价的思路（简单版本）：

• 可以通过筛选排序手工更新；
• 或使用较复杂的公式/Power Query 查找最新日期的采购记录。

步骤 5：制作分析报表与可视化

1. 在销售明细上创建数据透视表：销量排行、按客户分析、按业务员分析；
2. 在采购明细上创建数据透视表：按供应商分析、月度采购趋势；
3. 组合透视图与切片器，生成一个「进销存分析」仪表板工作表；
4. 设置统一刷新按钮（或每次打开文件后手动刷新透视表）。

---

🌐 十一、从 Excel 过渡到在线进销存系统的思路

当 Excel 进销存使用一段时间后，常见痛点包括：

• 多人协作导致版本冲突；
• 更新数据需要不断传文件、合并表格；
• 权限控制难，不能限制不同岗位看到不同数据；
• 无法满足手机端录入、异地实时更新等需求；
• 批次管理、多仓管理、审批流难以在 Excel 中安全可靠落地。

此时可以考虑：

1. 保留现有 Excel 表结构作为原型；
2. 将结构表和数据迁移到在线进销存系统；
3. 在系统中定义字段、流程和权限规则；
4. 用手机或浏览器随时录入采购、销售、盘点等业务；
5. 将复杂报表交由系统自动生成。

对于希望在不写代码的前提下快速搭建自有进销存工具的团队，可以考虑使用可配置模板的平台，例如通过「表单 + 流程 + 报表」的方式，依然用类似 Excel 的表格逻辑进行配置。像简道云进销存这类模板，在进销存业务模型上已经预置了常用字段、流程与报表，中小企业可以按照自己现有的 Excel 表头做适度调整，即可快速上线线上化进销存应用。

---

🔮 十二、总结与未来趋势：Excel 进销存的角色与升级方向

Excel 在进销存管理中，短期内依然会是很多中小企业的主力工具。通过本文介绍的架构设计和技巧：

• 拆分主数据表（商品、供应商、客户）与业务明细表（采购、销售、盘点）；
• 通过 SUMIFS、XLOOKUP、数据验证、条件格式构建自动化程度较高的模板；
• 利用数据透视表快速生成库存与销售分析报表；

可以让 Excel 进销存从「简单记账表」进化为「可分析、可预警的管理工具」，提高库存管理效率，减少缺货与积压。

从趋势看：

• 越来越多企业需要「多端协同、实时库存、权限审计」等能力，仅依赖 Excel 越来越吃力；
• 在线进销存系统、低代码平台会逐渐成为主流载体，而 Excel 更适合担任「数据分析和轻量场景」的角色；
• 企业最佳路径往往是：先用 Excel 规范结构和流程 → 再迁移到在线进销存系统，在保证业务稳定的前提下完成数字化升级。

如果你已经有一套进销存 Excel 表格，想在保持“表格思维”的前提下实现云端协同，可以把现有字段映射到在线模板中，减少重建成本。

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

精品问答:

---

276

×

微信分享

扫描二维码分享到微信