简介面向中小企业、门店或需要轻量化库存管理的Excel VBA进销存管理系统无需依赖专业ERP即可在Excel中完成进货、销售与库存的日常跟踪。资源包为zip压缩格式共3个文件核心是一个xlsm启用宏的工作簿另含rels与xml配置文件用于自定义功能区界面整体仅85KB轻巧易部署。已有2014人学习下载适合有一定VBA基础或正在寻找Excel自动化方案的读者。通过阅读和运行该系统可重点学习VBA在数据验证、库存自动计算、低库存预警、销售与采购订单处理、报表生成及工作簿安全保护等方面的具体实现。还能借鉴其自定义用户界面的设计思路并在此基础上按自身业务需求进行二次开发将手工台账升级为半自动化的进销存管理工具减少重复录入提升库存流转效率。1. 进销存管理系统用 Excel VBA 实现能解决哪些人的账搜索“进销存管理系统Excel VBA实现”的人多半不是买不起进销存软件而是现成软件的数据口径和自家业务对不上。整进零出、先货后票、同一商品既当原料又做赠品这些场景里 Excel VBA 的优势不在界面而在表结构自己定、单据规则能写进代码、库存和成本按同一份流水重算。这套方案适合单账套、单机或少数人轮流操作的小团队真要多人同时录入Excel VBA 反而会成为库存重算互相覆盖的诱因。下面的做法围绕一个核心原则展开流水是账本库存是结果所有宏只做反复算和校验这两件事。2. 进销存的数据模型在写 VBA 代码前先把 Excel 表拆开不少 VBA 进销存卡在后期改不动是因为把库存当成了手工维护的表格而不是把所有业务先写进流水。给贸易类小公司做这类工具时我第一次不会碰代码先把工作表一层层拆好拆完之后每个过程只负责往固定区域读写行字段不会到处乱拼。2.1 商品表、往来单位表、流水表、库存表、参数表这五张表各自存什么以单仓库、无批次的普通进销存为例建议在同一个工作簿里保留五个工作表字段职责分开改错表的可能性才会降低。工作表关键字段在 VBA 里的作用商品商品编码、品名、规格、单位、分类、参考进价下拉数据源、合法性校验往来往来编码、名称、类型客户/供应商单据上的业务对手流水日期、单据号、业务类型、往来单位、商品编码、数量、单价、金额、备注唯一只追加的台账库存商品编码、数量、成本金额、库存下限VBA 重算写入的结果表禁止手工修改参数账套启用日期、当前单据号、默认业务类型决定流水起点和单据编号规则参数单独立表的意义在于VBA 里很多地方要读“从哪天开始重算库存”“下一张单据号是多少”与其把常量散落在代码里不如让操作人员也能在 Excel 里直接看到并调整。2.2 流水表是正负数量的账本库存表只是计算结果进出库统一用“数量带符号”写进流水表是一条值得坚持的设计原则入库为正出库为负盘点差异、领料、赠品出库也都写成负数。这样无论以后做结存、做月汇总还是做移动加权成本所有判断只对着数量的正负符号做而不是每种业务各写一套计算公式。只要流水表每行保持“单据号商品编码数量成本金额”库存表随时可以重建。手工去改库存表是月底对账时最常见的错误来源因为第二天宏一重算手工值就被覆盖掉还会让账面误差无从追查。2.3 用 Excel 表格区域代替普通 Range避免统计总行号出错写过 VBA 的人大概率见过Cells(ws.Rows.Count, 1).End(xlUp).Row这种写法它在新导入的数据里如果出现连续空行统计结果就会出错。更稳的做法是先把业务表区域转成 Excel 的“表”也就是按 CtrlT然后在 VBA 里通过ListObjects按名字取数据区。Dim wsFlow As Worksheet Dim lo As ListObject Dim rngBody As Range Set wsFlow ThisWorkbook.Worksheets(流水) Set lo wsFlow.ListObjects(tblFlow) Set rngBody lo.DataBodyRange 流水表应保留“日期、单据号、业务类型、往来、商品编码、数量、单价、金额、备注”九列 Debug.Print 流水数据区行数: rngBody.Rows.Count Debug.Print 最后一行第2列(单据号): rngBody.Cells(rngBody.Rows.Count, 2).Value上面代码先取DataBodyRange再读取行数和最后一行内容。相比End(xlUp)它不依赖某一列是否有数据而且 Excel 表格自带的筛选和扩展样式对不熟悉系统的同事也是一种视觉辅助。注意表格名称不要带空格统一用tbl前缀例如tblFlow、tblGoods。ListObjects(表名)对大小写不敏感但符号要完全一致。3. 用 VBA 做核心单据流保存进销存单据同时更新库存表单据录入是整个进销存 VBA 方案最容易做成花瓶的部分。按钮画得再大商品下拉选不到、数量和单价类型没处理、日期格式和系统区域设置冲突整套工具就废了。我一般不在第一版弹 UserForm而是把录入区直接放在工作表里配一个“保存单据”按钮这样已经能覆盖八成业务。3.1 录入区怎么摆单元格画表单比 UserForm 在实际环境更稳UserForm 看起来精致但在高 DPI 屏幕、64 位 Office、Mac 版 Excel 上经常出现控件错位或失效。现实做法是专门放一张“录入”工作表预留字段做数据验证下拉单据号、业务类型、往来单位、商品编码、数量、单价、备注。这样业务人员能看见要填什么宏只负责把录入区域读出来、写进流水表。录入表单元格内容数据验证来源E2单据号自动生成引用参数表当前单号E3业务类型序列入库,出库,退货,领料,盘亏E4往来单位序列往来编码列E5商品编码序列商品编码列E6数量正数大于0E7单价0到1000000E8备注无保存按钮建议用“表单控件”里的按钮不用 ActiveX 按钮。ActiveX 按钮在部分安全设置下默认禁用换一台机器就出现“无法运行文档中的宏”的错觉排查起来很耗时。3.2 保存单据一次数据库式写入加一次库存刷新下面是保存按钮点击事件的精简写法。核心步骤是校验必填项把录入区字段按列取出来追加到流水表 Excel 表格区域末尾再调用库存重算过程。Private Sub btnSave_Click() Dim wsIn As Worksheet: Set wsIn ThisWorkbook.Worksheets(录入) Dim wsFlow As Worksheet: Set wsFlow ThisWorkbook.Worksheets(流水) Dim lo As ListObject: Set lo wsFlow.ListObjects(tblFlow) Dim r As Long Dim qty As Double, price As Double, bizType As String If Trim(wsIn.Range(E5).Value) Or wsIn.Range(E6).Value Then MsgBox 商品编码和数量必填, vbExclamation Exit Sub End If bizType Trim(wsIn.Range(E3).Value) qty CDbl(wsIn.Range(E6).Value) price CDbl(wsIn.Range(E7).Value) 把业务类型转成带符号的数量入库为正出库为负 Select Case bizType Case 出库, 领料, 盘亏 qty -qty Case 入库, 退货入库, 盘盈 qty qty Case Else MsgBox 业务类型不正确, vbExclamation Exit Sub End Select 新行位置由表格首行加现有数据行数推出来 r lo.Range.Row lo.ListRows.Count 1 wsFlow.Cells(r, 1).Value wsIn.Range(E2).Value 单据号 wsFlow.Cells(r, 2).Value bizType 业务类型 wsFlow.Cells(r, 3).Value Date 日期 wsFlow.Cells(r, 4).Value wsIn.Range(E4).Value 往来单位 wsFlow.Cells(r, 5).Value wsIn.Range(E5).Value 商品编码 wsFlow.Cells(r, 6).Value qty 带符号数量 wsFlow.Cells(r, 7).Value price 单价 wsFlow.Cells(r, 8).Value Round(qty * price, 2) 金额 wsFlow.Cells(r, 9).Value wsIn.Range(E8).Value 备注 RecalcInventory wsIn.Range(E6).ClearContents MsgBox 单据已保存, vbInformation End Sub保存结束后立刻重算库存比“保存后还要手动点刷新”可靠因为月底对账时最怕的就是库存结果和流水不一致。CDbl用来防止数字被当成文本带进来但前提是录入格本身不能带货币符号。参数说明lo.Range.Row lo.ListRows.Count 1计算新写入位置表格表头在第 1 行且已有 10 条数据时新行就是第 12 行。这个公式在表格筛选状态下也不会错行用End(xlUp)时容易被中间空行带偏。3.3 库存重算与移动加权成本用字典按商品汇总流水量一大数据透视表就能完成基本汇总需要写 VBA 的原因通常是出库成本自动结转、负库存预警、月底全量重算。我习惯把重算过程写成一个公开过程RecalcInventory按钮和保存过程都能调用。它以流水表作为唯一输入清空库存结果表后按商品重新累计。下面代码用Scripting.Dictionary做按商品编码的合并同时维护数量和成本金额两个值。出库行的成本按当前结存平均价推算然后累计金额再除以累计数量得到新的平均成本。Public Sub RecalcInventory() Dim wsFlow As Worksheet: Set wsFlow ThisWorkbook.Worksheets(流水) Dim wsStock As Worksheet: Set wsStock ThisWorkbook.Worksheets(库存) Dim dict As Object: Set dict CreateObject(Scripting.Dictionary) Dim bodyRng As Range, i As Long, sp As String Dim qty As Double, avgPrice As Double, costAmount As Double Dim tmpV As Variant Set bodyRng wsFlow.ListObjects(tblFlow).DataBodyRange If bodyRng Is Nothing Then Exit Sub Dim lastStockRow As Long lastStockRow wsStock.Cells(wsStock.Rows.Count, 1).End(xlUp).Row If lastStockRow 2 Then wsStock.Rows(2: lastStockRow).ClearContents For i bodyRng.Row To bodyRng.Row bodyRng.Rows.Count - 1 sp Trim(CStr(wsFlow.Cells(i, 5).Value)) If sp Then GoTo next_row qty CDbl(wsFlow.Cells(i, 6).Value) If dict.Exists(sp) Then tmpV dict(sp) avgPrice 0 If tmpV(0) 0 Then avgPrice tmpV(1) / tmpV(0) If qty 0 Then costAmount qty * CDbl(wsFlow.Cells(i, 7).Value) tmpV(1) tmpV(1) costAmount Else costAmount qty * avgPrice End If tmpV(0) tmpV(0) qty dict(sp) tmpV Else If qty 0 Then tmpV Array(qty, qty * CDbl(wsFlow.Cells(i, 7).Value)) Else tmpV Array(qty, 0) End If dict.Add sp, tmpV End If next_row: Next i Dim rw As Long: rw 2 Dim ks As Variant For Each ks In dict.Keys wsStock.Cells(rw, 1).Value ks wsStock.Cells(rw, 2).Value dict(ks)(0) wsStock.Cells(rw, 3).Value Round(dict(ks)(1), 2) rw rw 1 Next ks End Sub这段代码里tmpV(0)存商品累计数量tmpV(1)存累计成本金额。入库时用单据单价计算成本出库时用上次结存平均价结转所以整个过程等价于移动加权平均。真正的加权成本还需要按日期排序但先跑通数量再升级成本业务验收通常够用。负库存的处理建议只预警不禁止。出库后累加值为负数时弹一次确认框确认后仍写入同时把商品编码标记为负库存。现实中“先出库后补票”比想象中常见硬性禁止会导致流程中断。4. 把 Excel 表变成能查能看的报表多条件筛选和字典汇总报表阶段的需求通常不是一张漂亮图表而是“按日期和客户筛选的月度进销存”。只要流水表设计干净VBA 里的做法就分两层一是用 AdvancedFilter 做界面上的多条件筛选二是用字典按业务键聚合并写回结果表。4.1 用 AdvancedFilter 做多条件筛选时VBA 里的日期要先转成数字AdvancedFilter 比 AutoFilter 强在可以把完整条件写进一个区域筛选结果直接输出到其他工作表。最常见的坑是日期在 VBA 里把“2026/1/1”直接做字符串比较会得到空结果因为单元格里的日期本质是序列值。构造条件区域时日期要用CDbl转成数字。Sub AdvFilterByDate() Dim wsData As Worksheet: Set wsData ThisWorkbook.Worksheets(流水) Dim wsOut As Worksheet: Set wsOut ThisWorkbook.Worksheets(查询结果) Dim critRng As Range, dataRng As Range, outRng As Range Dim dStart As Double, dEnd As Double dStart CDbl(CDate(2026/1/1)) dEnd CDbl(CDate(2026/3/31)) wsData.Range(K1).Value 日期 wsData.Range(K2).Value dStart wsData.Range(L1).Value 日期 wsData.Range(L2).Value dEnd Set critRng wsData.Range(K1:L2) Set dataRng wsData.Range(A1).CurrentRegion Set outRng wsOut.Range(A1) dataRng.AdvancedFilter Action:xlFilterCopy, CriteriaRange:critRng, CopyToRange:outRng, Unique:False wsData.Range(K1:L2).ClearContents End Sub同一列如果有两个条件写在条件区的上下两行表示“与”关系。CopyToRange最好是空区域的 A1 单元格第二次筛选会把旧结果顶掉。这里用CDbl(CDate(...))把日期转成序列值避免不同电脑的区域设置把日期解析成不同格式。4.2 用 VBA 字典按月份分组生成月度进销存汇总表月度汇总的需求通常是按商品编码和月份两个维度看出库、入库和期末数量。字典天然适合这种合并场景因为商品编码和月份的拼接字符串可以直接当作键使用。Sub BuildMonthlySummary() Dim wsFlow As Worksheet: Set wsFlow ThisWorkbook.Worksheets(流水) Dim wsSum As Worksheet: Set wsSum ThisWorkbook.Worksheets(月汇总) Dim dict As Object: Set dict CreateObject(Scripting.Dictionary) Dim i As Long, rKey As String, sp As String, mon As String Dim qty As Double For i 2 To wsFlow.Cells(wsFlow.Rows.Count, 1).End(xlUp).Row sp Trim(CStr(wsFlow.Cells(i, 5).Value)) mon Format(wsFlow.Cells(i, 3).Value, yyyy-mm) If sp Then GoTo next_row rKey sp | mon qty CDbl(wsFlow.Cells(i, 6).Value) If dict.Exists(rKey) Then dict(rKey) dict(rKey) qty Else dict.Add rKey, qty End If next_row: Next i Dim rw As Long: rw 2 wsSum.Cells(1, 1).Resize(1, 4).Value Array(商品编码, 月份, 入库量, 出库量) Dim ks As Variant, arrTmp() As String For Each ks In dict.Keys arrTmp Split(ks, |) wsSum.Cells(rw, 1).Value arrTmp(0) wsSum.Cells(rw, 2).Value arrTmp(1) If dict(ks) 0 Then wsSum.Cells(rw, 3).Value dict(ks) wsSum.Cells(rw, 4).Value 0 Else wsSum.Cells(rw, 3).Value 0 wsSum.Cells(rw, 4).Value -dict(ks) End If rw rw 1 Next ks End Sub这个汇总把流水里的正负数量拆成入库量和出库量两列实际上是在报表层还原业务方向而不是改流水表。如果流水表本身用了入库列和出库列分开存而不是带正负的数量列这段代码就需要先统一表结构再运行否则正负判断会全部失效。4.3 报表脚本执行慢优先调这四个开关多条件筛选和字典分组都建议放在不带公式的独立工作表中逐行写入前关掉屏幕刷新和自动计算能明显缩短运行时间。优化点推荐写法作用关闭屏幕刷新Application.ScreenUpdating False停止每次重绘关闭自动计算Application.Calculation xlCalculationManual公式不随循环反复刷新禁止事件再入Application.EnableEvents False防止工作表 Change 事件嵌套触发关闭状态栏刷新Application.DisplayStatusBar False减少视觉刷新开销代码结束前一定要恢复这四个开关且要放在Exit Sub之前异常退出会留下手动计算状态用户第一反应是“Excel 卡了”。如果数据量在几万行以上建议把整列读入Variant数组内存计算完再一次写回比逐单元格读写快出一个数量级。5. 真正交付给同事前要处理的事宏安全、WPS 兼容性、复核步骤5.1 提示“无法运行宏”或“未安装 VBA 支持库”时的处理顺序这类提示通常不是文件损坏而是安全级别或运行环境不匹配。先把文件另存为.xlsm再检查“文件→选项→信任中心→信任中心设置→宏设置”把文件所在目录加入受信任位置。如果同事用的是 WPSVBA 支持依赖对应版本的 VBA 插件而且 32 位和 64 位环境必须匹配位数不对时宏会静默失效。5.2 用一条测试宏核对库存数量发布前我会准备三笔最短业务一笔商品入库、一笔相同商品出库、一笔跨月出库。核对库存表的数量是否等于流水表按商品编码筛选后的加总这段公式可以替代手工对账。Sub TestInventory() Dim actual As Double, expect As Double, sp As String Dim wsStock As Worksheet: Set wsStock ThisWorkbook.Worksheets(库存) Dim wsFlow As Worksheet: Set wsFlow ThisWorkbook.Worksheets(流水) sp wsStock.Range(A2).Value actual wsStock.Range(B2).Value 数量列 G 列已经是带正负号的存数按商品编码求和即可 expect WorksheetFunction.SumIf(wsFlow.Range(E:E), sp, wsFlow.Range(G:G)) Debug.Assert Abs(actual - expect) 0.0001 End Sub这只能防止“商品编码写错”“出库录成入库”这类基础误操作真正的月末核对还要看金额列和存本是否一致。5.3 最后一个防呆动作把保存按钮加一次负库存确认在MsgBox 单据已保存之前加一个vbYesNo判断当出库后库存小于零时弹窗让操作者二次确认。确认通过后单据照常保存同时把该商品编码写入“负库存备查表”。自动重算、保存、校验这三步能串起来Excel VBA 进销存才算从“会记录”进化成“敢背账”。本文还有配套的精品资源点击获取 
长河君 · 2026/9/15 22:37:03 · 阅读 1.2k Excel VBA进销存管理系统实战：数据表设计、库存重算与自动报表
简介面向中小企业、门店或需要轻量化库存管理的Excel VBA进销存管理系统无需依赖专业ERP即可在Excel中完成进货、销售与库存的日常跟踪。资源包为zip压缩格式共3个文件核心是一个xlsm启用宏的工作簿另含rels与xml配置文件用于自定义功能区界面整体仅85KB轻巧易部署。已有2014人学习下载适合有一定VBA基础或正在寻找Excel自动化方案的读者。通过阅读和运行该系统可重点学习VBA在数据验证、库存自动计算、低库存预警、销售与采购订单处理、报表生成及工作簿安全保护等方面的具体实现。还能借鉴其自定义用户界面的设计思路并在此基础上按自身业务需求进行二次开发将手工台账升级为半自动化的进销存管理工具减少重复录入提升库存流转效率。1. 进销存管理系统用 Excel VBA 实现能解决哪些人的账搜索“进销存管理系统Excel VBA实现”的人多半不是买不起进销存软件而是现成软件的数据口径和自家业务对不上。整进零出、先货后票、同一商品既当原料又做赠品这些场景里 Excel VBA 的优势不在界面而在表结构自己定、单据规则能写进代码、库存和成本按同一份流水重算。这套方案适合单账套、单机或少数人轮流操作的小团队真要多人同时录入Excel VBA 反而会成为库存重算互相覆盖的诱因。下面的做法围绕一个核心原则展开流水是账本库存是结果所有宏只做反复算和校验这两件事。2. 进销存的数据模型在写 VBA 代码前先把 Excel 表拆开不少 VBA 进销存卡在后期改不动是因为把库存当成了手工维护的表格而不是把所有业务先写进流水。给贸易类小公司做这类工具时我第一次不会碰代码先把工作表一层层拆好拆完之后每个过程只负责往固定区域读写行字段不会到处乱拼。2.1 商品表、往来单位表、流水表、库存表、参数表这五张表各自存什么以单仓库、无批次的普通进销存为例建议在同一个工作簿里保留五个工作表字段职责分开改错表的可能性才会降低。工作表关键字段在 VBA 里的作用商品商品编码、品名、规格、单位、分类、参考进价下拉数据源、合法性校验往来往来编码、名称、类型客户/供应商单据上的业务对手流水日期、单据号、业务类型、往来单位、商品编码、数量、单价、金额、备注唯一只追加的台账库存商品编码、数量、成本金额、库存下限VBA 重算写入的结果表禁止手工修改参数账套启用日期、当前单据号、默认业务类型决定流水起点和单据编号规则参数单独立表的意义在于VBA 里很多地方要读“从哪天开始重算库存”“下一张单据号是多少”与其把常量散落在代码里不如让操作人员也能在 Excel 里直接看到并调整。2.2 流水表是正负数量的账本库存表只是计算结果进出库统一用“数量带符号”写进流水表是一条值得坚持的设计原则入库为正出库为负盘点差异、领料、赠品出库也都写成负数。这样无论以后做结存、做月汇总还是做移动加权成本所有判断只对着数量的正负符号做而不是每种业务各写一套计算公式。只要流水表每行保持“单据号商品编码数量成本金额”库存表随时可以重建。手工去改库存表是月底对账时最常见的错误来源因为第二天宏一重算手工值就被覆盖掉还会让账面误差无从追查。2.3 用 Excel 表格区域代替普通 Range避免统计总行号出错写过 VBA 的人大概率见过Cells(ws.Rows.Count, 1).End(xlUp).Row这种写法它在新导入的数据里如果出现连续空行统计结果就会出错。更稳的做法是先把业务表区域转成 Excel 的“表”也就是按 CtrlT然后在 VBA 里通过ListObjects按名字取数据区。Dim wsFlow As Worksheet Dim lo As ListObject Dim rngBody As Range Set wsFlow ThisWorkbook.Worksheets(流水) Set lo wsFlow.ListObjects(tblFlow) Set rngBody lo.DataBodyRange 流水表应保留“日期、单据号、业务类型、往来、商品编码、数量、单价、金额、备注”九列 Debug.Print 流水数据区行数: rngBody.Rows.Count Debug.Print 最后一行第2列(单据号): rngBody.Cells(rngBody.Rows.Count, 2).Value上面代码先取DataBodyRange再读取行数和最后一行内容。相比End(xlUp)它不依赖某一列是否有数据而且 Excel 表格自带的筛选和扩展样式对不熟悉系统的同事也是一种视觉辅助。注意表格名称不要带空格统一用tbl前缀例如tblFlow、tblGoods。ListObjects(表名)对大小写不敏感但符号要完全一致。3. 用 VBA 做核心单据流保存进销存单据同时更新库存表单据录入是整个进销存 VBA 方案最容易做成花瓶的部分。按钮画得再大商品下拉选不到、数量和单价类型没处理、日期格式和系统区域设置冲突整套工具就废了。我一般不在第一版弹 UserForm而是把录入区直接放在工作表里配一个“保存单据”按钮这样已经能覆盖八成业务。3.1 录入区怎么摆单元格画表单比 UserForm 在实际环境更稳UserForm 看起来精致但在高 DPI 屏幕、64 位 Office、Mac 版 Excel 上经常出现控件错位或失效。现实做法是专门放一张“录入”工作表预留字段做数据验证下拉单据号、业务类型、往来单位、商品编码、数量、单价、备注。这样业务人员能看见要填什么宏只负责把录入区域读出来、写进流水表。录入表单元格内容数据验证来源E2单据号自动生成引用参数表当前单号E3业务类型序列入库,出库,退货,领料,盘亏E4往来单位序列往来编码列E5商品编码序列商品编码列E6数量正数大于0E7单价0到1000000E8备注无保存按钮建议用“表单控件”里的按钮不用 ActiveX 按钮。ActiveX 按钮在部分安全设置下默认禁用换一台机器就出现“无法运行文档中的宏”的错觉排查起来很耗时。3.2 保存单据一次数据库式写入加一次库存刷新下面是保存按钮点击事件的精简写法。核心步骤是校验必填项把录入区字段按列取出来追加到流水表 Excel 表格区域末尾再调用库存重算过程。Private Sub btnSave_Click() Dim wsIn As Worksheet: Set wsIn ThisWorkbook.Worksheets(录入) Dim wsFlow As Worksheet: Set wsFlow ThisWorkbook.Worksheets(流水) Dim lo As ListObject: Set lo wsFlow.ListObjects(tblFlow) Dim r As Long Dim qty As Double, price As Double, bizType As String If Trim(wsIn.Range(E5).Value) Or wsIn.Range(E6).Value Then MsgBox 商品编码和数量必填, vbExclamation Exit Sub End If bizType Trim(wsIn.Range(E3).Value) qty CDbl(wsIn.Range(E6).Value) price CDbl(wsIn.Range(E7).Value) 把业务类型转成带符号的数量入库为正出库为负 Select Case bizType Case 出库, 领料, 盘亏 qty -qty Case 入库, 退货入库, 盘盈 qty qty Case Else MsgBox 业务类型不正确, vbExclamation Exit Sub End Select 新行位置由表格首行加现有数据行数推出来 r lo.Range.Row lo.ListRows.Count 1 wsFlow.Cells(r, 1).Value wsIn.Range(E2).Value 单据号 wsFlow.Cells(r, 2).Value bizType 业务类型 wsFlow.Cells(r, 3).Value Date 日期 wsFlow.Cells(r, 4).Value wsIn.Range(E4).Value 往来单位 wsFlow.Cells(r, 5).Value wsIn.Range(E5).Value 商品编码 wsFlow.Cells(r, 6).Value qty 带符号数量 wsFlow.Cells(r, 7).Value price 单价 wsFlow.Cells(r, 8).Value Round(qty * price, 2) 金额 wsFlow.Cells(r, 9).Value wsIn.Range(E8).Value 备注 RecalcInventory wsIn.Range(E6).ClearContents MsgBox 单据已保存, vbInformation End Sub保存结束后立刻重算库存比“保存后还要手动点刷新”可靠因为月底对账时最怕的就是库存结果和流水不一致。CDbl用来防止数字被当成文本带进来但前提是录入格本身不能带货币符号。参数说明lo.Range.Row lo.ListRows.Count 1计算新写入位置表格表头在第 1 行且已有 10 条数据时新行就是第 12 行。这个公式在表格筛选状态下也不会错行用End(xlUp)时容易被中间空行带偏。3.3 库存重算与移动加权成本用字典按商品汇总流水量一大数据透视表就能完成基本汇总需要写 VBA 的原因通常是出库成本自动结转、负库存预警、月底全量重算。我习惯把重算过程写成一个公开过程RecalcInventory按钮和保存过程都能调用。它以流水表作为唯一输入清空库存结果表后按商品重新累计。下面代码用Scripting.Dictionary做按商品编码的合并同时维护数量和成本金额两个值。出库行的成本按当前结存平均价推算然后累计金额再除以累计数量得到新的平均成本。Public Sub RecalcInventory() Dim wsFlow As Worksheet: Set wsFlow ThisWorkbook.Worksheets(流水) Dim wsStock As Worksheet: Set wsStock ThisWorkbook.Worksheets(库存) Dim dict As Object: Set dict CreateObject(Scripting.Dictionary) Dim bodyRng As Range, i As Long, sp As String Dim qty As Double, avgPrice As Double, costAmount As Double Dim tmpV As Variant Set bodyRng wsFlow.ListObjects(tblFlow).DataBodyRange If bodyRng Is Nothing Then Exit Sub Dim lastStockRow As Long lastStockRow wsStock.Cells(wsStock.Rows.Count, 1).End(xlUp).Row If lastStockRow 2 Then wsStock.Rows(2: lastStockRow).ClearContents For i bodyRng.Row To bodyRng.Row bodyRng.Rows.Count - 1 sp Trim(CStr(wsFlow.Cells(i, 5).Value)) If sp Then GoTo next_row qty CDbl(wsFlow.Cells(i, 6).Value) If dict.Exists(sp) Then tmpV dict(sp) avgPrice 0 If tmpV(0) 0 Then avgPrice tmpV(1) / tmpV(0) If qty 0 Then costAmount qty * CDbl(wsFlow.Cells(i, 7).Value) tmpV(1) tmpV(1) costAmount Else costAmount qty * avgPrice End If tmpV(0) tmpV(0) qty dict(sp) tmpV Else If qty 0 Then tmpV Array(qty, qty * CDbl(wsFlow.Cells(i, 7).Value)) Else tmpV Array(qty, 0) End If dict.Add sp, tmpV End If next_row: Next i Dim rw As Long: rw 2 Dim ks As Variant For Each ks In dict.Keys wsStock.Cells(rw, 1).Value ks wsStock.Cells(rw, 2).Value dict(ks)(0) wsStock.Cells(rw, 3).Value Round(dict(ks)(1), 2) rw rw 1 Next ks End Sub这段代码里tmpV(0)存商品累计数量tmpV(1)存累计成本金额。入库时用单据单价计算成本出库时用上次结存平均价结转所以整个过程等价于移动加权平均。真正的加权成本还需要按日期排序但先跑通数量再升级成本业务验收通常够用。负库存的处理建议只预警不禁止。出库后累加值为负数时弹一次确认框确认后仍写入同时把商品编码标记为负库存。现实中“先出库后补票”比想象中常见硬性禁止会导致流程中断。4. 把 Excel 表变成能查能看的报表多条件筛选和字典汇总报表阶段的需求通常不是一张漂亮图表而是“按日期和客户筛选的月度进销存”。只要流水表设计干净VBA 里的做法就分两层一是用 AdvancedFilter 做界面上的多条件筛选二是用字典按业务键聚合并写回结果表。4.1 用 AdvancedFilter 做多条件筛选时VBA 里的日期要先转成数字AdvancedFilter 比 AutoFilter 强在可以把完整条件写进一个区域筛选结果直接输出到其他工作表。最常见的坑是日期在 VBA 里把“2026/1/1”直接做字符串比较会得到空结果因为单元格里的日期本质是序列值。构造条件区域时日期要用CDbl转成数字。Sub AdvFilterByDate() Dim wsData As Worksheet: Set wsData ThisWorkbook.Worksheets(流水) Dim wsOut As Worksheet: Set wsOut ThisWorkbook.Worksheets(查询结果) Dim critRng As Range, dataRng As Range, outRng As Range Dim dStart As Double, dEnd As Double dStart CDbl(CDate(2026/1/1)) dEnd CDbl(CDate(2026/3/31)) wsData.Range(K1).Value 日期 wsData.Range(K2).Value dStart wsData.Range(L1).Value 日期 wsData.Range(L2).Value dEnd Set critRng wsData.Range(K1:L2) Set dataRng wsData.Range(A1).CurrentRegion Set outRng wsOut.Range(A1) dataRng.AdvancedFilter Action:xlFilterCopy, CriteriaRange:critRng, CopyToRange:outRng, Unique:False wsData.Range(K1:L2).ClearContents End Sub同一列如果有两个条件写在条件区的上下两行表示“与”关系。CopyToRange最好是空区域的 A1 单元格第二次筛选会把旧结果顶掉。这里用CDbl(CDate(...))把日期转成序列值避免不同电脑的区域设置把日期解析成不同格式。4.2 用 VBA 字典按月份分组生成月度进销存汇总表月度汇总的需求通常是按商品编码和月份两个维度看出库、入库和期末数量。字典天然适合这种合并场景因为商品编码和月份的拼接字符串可以直接当作键使用。Sub BuildMonthlySummary() Dim wsFlow As Worksheet: Set wsFlow ThisWorkbook.Worksheets(流水) Dim wsSum As Worksheet: Set wsSum ThisWorkbook.Worksheets(月汇总) Dim dict As Object: Set dict CreateObject(Scripting.Dictionary) Dim i As Long, rKey As String, sp As String, mon As String Dim qty As Double For i 2 To wsFlow.Cells(wsFlow.Rows.Count, 1).End(xlUp).Row sp Trim(CStr(wsFlow.Cells(i, 5).Value)) mon Format(wsFlow.Cells(i, 3).Value, yyyy-mm) If sp Then GoTo next_row rKey sp | mon qty CDbl(wsFlow.Cells(i, 6).Value) If dict.Exists(rKey) Then dict(rKey) dict(rKey) qty Else dict.Add rKey, qty End If next_row: Next i Dim rw As Long: rw 2 wsSum.Cells(1, 1).Resize(1, 4).Value Array(商品编码, 月份, 入库量, 出库量) Dim ks As Variant, arrTmp() As String For Each ks In dict.Keys arrTmp Split(ks, |) wsSum.Cells(rw, 1).Value arrTmp(0) wsSum.Cells(rw, 2).Value arrTmp(1) If dict(ks) 0 Then wsSum.Cells(rw, 3).Value dict(ks) wsSum.Cells(rw, 4).Value 0 Else wsSum.Cells(rw, 3).Value 0 wsSum.Cells(rw, 4).Value -dict(ks) End If rw rw 1 Next ks End Sub这个汇总把流水里的正负数量拆成入库量和出库量两列实际上是在报表层还原业务方向而不是改流水表。如果流水表本身用了入库列和出库列分开存而不是带正负的数量列这段代码就需要先统一表结构再运行否则正负判断会全部失效。4.3 报表脚本执行慢优先调这四个开关多条件筛选和字典分组都建议放在不带公式的独立工作表中逐行写入前关掉屏幕刷新和自动计算能明显缩短运行时间。优化点推荐写法作用关闭屏幕刷新Application.ScreenUpdating False停止每次重绘关闭自动计算Application.Calculation xlCalculationManual公式不随循环反复刷新禁止事件再入Application.EnableEvents False防止工作表 Change 事件嵌套触发关闭状态栏刷新Application.DisplayStatusBar False减少视觉刷新开销代码结束前一定要恢复这四个开关且要放在Exit Sub之前异常退出会留下手动计算状态用户第一反应是“Excel 卡了”。如果数据量在几万行以上建议把整列读入Variant数组内存计算完再一次写回比逐单元格读写快出一个数量级。5. 真正交付给同事前要处理的事宏安全、WPS 兼容性、复核步骤5.1 提示“无法运行宏”或“未安装 VBA 支持库”时的处理顺序这类提示通常不是文件损坏而是安全级别或运行环境不匹配。先把文件另存为.xlsm再检查“文件→选项→信任中心→信任中心设置→宏设置”把文件所在目录加入受信任位置。如果同事用的是 WPSVBA 支持依赖对应版本的 VBA 插件而且 32 位和 64 位环境必须匹配位数不对时宏会静默失效。5.2 用一条测试宏核对库存数量发布前我会准备三笔最短业务一笔商品入库、一笔相同商品出库、一笔跨月出库。核对库存表的数量是否等于流水表按商品编码筛选后的加总这段公式可以替代手工对账。Sub TestInventory() Dim actual As Double, expect As Double, sp As String Dim wsStock As Worksheet: Set wsStock ThisWorkbook.Worksheets(库存) Dim wsFlow As Worksheet: Set wsFlow ThisWorkbook.Worksheets(流水) sp wsStock.Range(A2).Value actual wsStock.Range(B2).Value 数量列 G 列已经是带正负号的存数按商品编码求和即可 expect WorksheetFunction.SumIf(wsFlow.Range(E:E), sp, wsFlow.Range(G:G)) Debug.Assert Abs(actual - expect) 0.0001 End Sub这只能防止“商品编码写错”“出库录成入库”这类基础误操作真正的月末核对还要看金额列和存本是否一致。5.3 最后一个防呆动作把保存按钮加一次负库存确认在MsgBox 单据已保存之前加一个vbYesNo判断当出库后库存小于零时弹窗让操作者二次确认。确认通过后单据照常保存同时把该商品编码写入“负库存备查表”。自动重算、保存、校验这三步能串起来Excel VBA 进销存才算从“会记录”进化成“敢背账”。本文还有配套的精品资源点击获取