自适应路径和文件名变更的仓库进销存管理表格

楼主 lrlxxqxa	无论是商品流通业还是生产制造业，都离不开商品采购入库、销售出库的动态管理，设计创建个性化的进销存表格，不但能够提高工作效率，而且根据查询统计可以做出及时准确的业务决策。下面结合一个实例，介绍如何综合应用Excel中的函数公式、SQL查询、数据透视表、VBA代码以及数据库函数来创建小型的仓库进销存管理和查询软件。首先说设计思路：实际工作中，需要仓库的工作人员在数据源表中录入流水明细，即可通过查询统计出入库信息。 1、为了满足不同盘点和统计周期的需要，我们将数据源表划分为“期初”、“入库”和“出库”三张，这样无论是汇总或拆分数据都清晰便利； 2、为了避免人为输入错误导致查询出错，需要使品名统一，所以我们将需要录入品名的位置设置数据有效性； 3、为了提高工作效率，设置辅助信息工作表，将品名对应的单价存放起来，当在数据源表中输入品名时，能够自动提取对应的单价； 4、为了使数据源表中的记录添加时，设置的公式能够自动扩展，将数据源区域转化为表（2003中叫列表）； 5、利用SQL语句创建动态的数据透视表，查询统计出、入库的数量和金额信息； 6、在使用过程中，由于地域和使用人不同，Excel工作簿被放置的位置和被设定的文件名也不同，要求工作簿改变路径和文件名后，数据透视表能够自适应刷新。这里利用VBA代码后台辅助实现。 7、为了便于即时查询和统计，也为了打印入库和出库表，还需要设计一个打印界面。这里考虑到数据源记录可能很多，可以利用源表结构的规范性，使用数据库函数来提高运算性能。再来看下界面：数据源界面包括三张工作表，分别为“期初”、“入库”、“出库”，结构如下所示：辅助设置工作表如下：统计界面如下：打印界面如下：进销存统计表.rar
2楼 lrlxxqxa	在辅助工作表‘设置’中，我们定义一个名称“品名”，使添加新的产品时，数据有效性的源能够自动扩展。品名 =OFFSET(设置!$A$2,,,COUNTA(设置!$A:$A)-1) 将需要输入品名的位置设置数据有效性，序列来源=品名。为了美化界面，使用条件格式设置动态边框， =(A$1<>"")($A1<>"") Ctrl+L或者Ctrl+T将数据区域设置为表，包含标题行。然后设置公式。 D列单价根据品名自动提取 D2 =IF(B2="","",VLOOKUP(B2,设置!A:B,2,)) 下拉填充 E列金额根据输入的数量和单价自动生成 E2 =IF(C2,D2C2,"") “使用人”和“类别”也根据实际业务设置数据有效性； “出库”和“入库”表的设置和“期初”表原理相同，不再赘述。
3楼 lrlxxqxa	利用SQL创建动态数据透视表 select * from (select 日期,品名,单价,金额 as 期初金额,0 as 入库金额,0 as 出库金额,使用人,类别,备注,数量 as 期初,0 as 入库,0 as 出库 from [期初$] union all select 日期,品名,单价,0,金额,0,使用人,类别,备注,0,数量,0 from [入库$] union all select 日期,品名,单价,0,0,金额,使用人,类别,备注,0,0,数量 from [出库$]) where 品名 is not null 创建计算字段“结存”和“结存金额” 结存 =期初+入库-出库同法设置“结存金额 =期初金额+入库金额-出库金额调整数据透视表布局，根据需求设置页字段筛选，分两行两列放置。为了便于使用2003版本的网友，数据透视表使用了条件格式设置边框，放置刷新后边框消失 =($A1<>"")*(A$6<>"") 字段行和总计行完成美化。
4楼 lrlxxqxa	为了使SQL创建的数据透视表能够自适应路径和文件名改变，使用VBA代码后台辅助。 Alt+F11进入VBE界面，插入模块，输入代码： '适用于本工作簿中所有工作表的SQL查询表，透视表采用引入外部数据方式对本工作簿中的连接 '适应路径和文件名更改,但基本上不适用于SQL语句中带有路径名或文件名的情形，也不适用于引入其他工作簿的情形。 Sub SQL表透视表适应路径和文件名更改() Dim strCon As String, iPath As String Dim i As Integer, j As Integer, iFlag As String, iStr As String Dim iT As Integer, jT As Integer Dim sht As Worksheet '定义变量 iPath = ActiveWorkbook.FullName '获取当前活动工作簿的完全路径 On Error Resume Next For Each sht In ActiveWorkbook.Worksheets 'SQL查询表的处理 i = sht.QueryTables.Count If i > 0 Then For j = 1 To i strCon = sht.QueryTables(j).Connection '将当前活动数据透视表中缓存连接信息赋值给变量strCon Select Case Left(strCon, 5) 'select case语句,条件为strCon变量中从左侧取5个字符 Case "ODBC;" '判断缓存连接信息中的数据连接方式,如果是ODBC方式 iFlag = "DBQ=" '将"DBQ=" 赋值给变量iFlag Case "OLEDB" '判断缓存连接信息中的数据连接方式,如果是OLEDB方式 iFlag = "Source=" '将"DBQ=" 赋值给变量iFlag Case Else '没有引入外部数据或其他方式，不予处理 Exit Sub End Select iStr = Split(Split(strCon, iFlag)(1), ";")(0) '在变量strCon中截取文件路径信息 With sht.QueryTables(j) '替换SQL查询表中缓存信息中的文件完全路径 .Connection = VBA.Replace(strCon, iStr, iPath) .CommandText = VBA.Replace(.CommandText, iStr, iPath) End With Next j End If '数据透视表的处理 iT = sht.PivotTables.Count If iT > 0 Then For jT = 1 To iT strCon = sht.PivotTables(jT).PivotCache.Connection '将当前活动数据透视表中缓存连接信息赋值给变量strCon Select Case Left(strCon, 5) 'select case语句,条件为strCon变量中从左侧取5个字符 Case "ODBC;" '判断缓存连接信息中的数据连接方式,如果是ODBC方式 iFlag = "DBQ=" '将"DBQ=" 赋值给变量iFlag Case "OLEDB" '判断缓存连接信息中的数据连接方式,如果是OLEDB方式 iFlag = "Source=" '将"DBQ=" 赋值给变量iFlag Case Else '没有引入外部数据或其他方式，不予处理 Exit Sub End Select iStr = Split(Split(strCon, iFlag)(1), ";")(0) '在变量strCon中截取文件路径信息 With sht.PivotTables(jT).PivotCache '替换据透视表缓存信息中的文件完全路径 .Connection = VBA.Replace(strCon, iStr, iPath) .CommandText = VBA.Replace(.CommandText, iStr, iPath) End With Next End If Next End Sub 在ThisWorkbook的open事件中输入代码 Private Sub Workbook_Open() Call SQL表透视表适应路径和文件名更改 End Sub 推荐参考资源：数据透视表自适应的VBA代码 http://www.exceltip.net/thread-7995-1-1.html 如何让EXCEL中所有引入外部数据的透视表和SQL查询表自适应路径和文件名更改 http://www.exceltip.net/thread-9385-1-1.html
5楼 lrlxxqxa	在打印界面中，利用自定义名称“品名”来提取其中的每个元素，多余行置空。 B7输入公式 =IF(ROW(A1)>COUNTA(品名),"",INDEX(品名,ROW(A1))) 下拉填充； A列序号设置，根据品名智能添加 A7输入公式 =IF(B7="","",SUBTOTAL(3,B$7:B7)) 下拉填充 C和D列是日期信息，配合F3:F4的查询起始及终止日期，用以构建数据库函数的条件区域； C7 =">="&$F$3 D7 ="<="&$F$4 下拉填充公式 “数量”和“金额”列使用数据库函数配合SUM函数来统计对应品名的数量和金额信息。如果是2007以及以上版本，可以直接在数据库函数中使用结构化引用功能，直接引用表名称作为DSUM函数的第一参数；推荐资源：什么叫结构化引用 http://www.exceltip.net/thread-3222-1-1.html 结构化引用中“表1”与“表1[#全部]”的区别是什么？ http://www.exceltip.net/thread-5152-1-1.html 如何在数据库函数的条件区域中使用结构化引用功能？ http://www.exceltip.net/thread-26971-1-1.html E7输入公式 =IF($B7="","",DSUM(表3[#全部],E$6,B$6:D7)-SUM(E$6:E6)) 右拖下拉填充如果使用的是2003版本，则需要先定义两个名称，分别引用入库和出库工作表的数据源区域，再将其作为DSUM函数的第一参数。定义“入库” =OFFSET(入库!$A$1,,,COUNTA(入库!$A:$A),COUNTA(入库!$1:$1)) 定义”出库“ =OFFSET(出库!$A$1,,,COUNTA(出库!$A:$A),COUNTA(出库!$1:$1)) E7输入公式 =IF($B7="","",DSUM(入库,E$6,B$6:D7)-SUM(E$6:E6)) 右拖下拉填充即可。
6楼 lrlxxqxa	至此，进销存的主体结构已经完成，基础功能实现。在此基础上，还可以进行细化，比如出入库数量的输入约束，利用数据有效性限制为整数；出库数量的限定，不得亏负数库存；产品信息的单价跨年变更；数据表公式的隐藏与保护等。还有一些个性化的设置，要根据需求设计并完善。这里就不过多阐述了。
7楼 eliane_lei	借鉴学习，慢慢消化中！谢谢分享
8楼 rabbitllu	很强大呢，学习
9楼 rabbitllu	锐版，我在“入库”这张增加了一行，边框有变动，如图：
10楼 lgcmeli	很好的作品分享。收藏了。
11楼 rhr2008	谢谢老师！好好学习下！
12楼 ☆爱咋咋地☆	虽然没看懂，但是要学习
13楼 pcwmmn	好！谢谢！
14楼 MOUSEJAME	但是如果是相同的产品，但是单价不一样，那这样也可以用吗？
15楼 ☆爱咋咋地☆	如何自适应呢
16楼 chenning_0517	非常好，感谢
17楼 isabella9897	谢谢锐版的分享，超级实用的一个进销存库存表，但我在实际应用中碰到一个问题，在实际进货和出货中，我们的进货单价与出货单价是随时变动的，比如说打折促销等。这时，要怎么办呢？请大家帮忙啊
18楼山城一张	好东西
19楼じ☆潴の︵ゞ
20楼 aczkk	看了，感觉不错，谢谢您提供
21楼一聚氨酯	好文章支持一下了
22楼 amylee	感谢版主的无私奉献。收藏了，还得慢慢消化。
23楼 ←☆╮自由メ	不太懂
24楼 drinker007	找个时间好好来学习一下进销存，太有用了
25楼 lrlxxqxa	综合应用Excel中的函数公式、SQL查询、数据透视表、VBA代码以及数据库函数来创建小型的仓库进销存管理
26楼海洋之星	谢谢锐哥分享，学习了
27楼芐雨
28楼 gzdragon	如何回帖。
29楼 gaokaozhu	收藏研究谢谢分享
30楼 lrlxxqxa
31楼 ze001	谢谢分项，慢慢消化
32楼小同学大衣柜	能帮我把我这个填一下公式吗空白库存模板41（鞋、服装）.rar
33楼老百姓	支持支持支持支持支持支持支持支持支持支持