VBA+SQL+数据透视表--数据透视表的超级应用之一--仓库管理

Scarlett_88

像是每天都有人在问这个类似的问题，这两天我回了两个贴，要他们整理一下，想周到点。
今天特发此贴，开始我们VBA+SQL+数据透视表或透视图之旅，我将和师傅们共同合作，推出模版式的应用文件，尽量做到通用性，智能性。

材料编码会用控件给你们模糊查询进行选择
供应商编码会用列表给你们选择

现在开始征集数据源，问题要求等等
思维更活跃一点
胆子更大胆一点
你的好的数据结构和要求将会给大家带来高效和准确性
这个可能会要点时间，望大家理解。

这里要感谢3师傅一撇，他提供了数据源，我将他提供的数据源重新整理，做成一个可以套用的仓库管理的系统。你们可以直接套用。
二楼先放上一个附件，大家看看还有什么要求

[ 本帖最后由 Scarlett_88 于 2009-6-12 11:22 编辑 ]

Scarlett_88

丑媳妇总归要同公婆见面的，先小礼送上，由于有其他因素，结余部分先将数据固化了，暂时还要盖上红盖头，将来会给你们揭开的。
该贴就讲解怎么用
1、列表：
客户、供应商、仓库，都是添加了列表，并定义了名称，你可以往下添加内容。这个是为后面表相关字段设置数据有效性用的，如果你们还有其他有效性的要求，可以套用这种方式，举一反三。
2、物料编码：
物料编码表，关键字段是存货编码，要唯一，前5列一定要，后面的，你可以随便加。爱加多少列都可以，不影响我的查询，只有有新的物料增加时，才要求输入该物料的信息，其他时候几乎不用动它。
3、期初余额：
该表单价可以不输，因为有时同一个货物不同时候采购单价不一，我们4项一定要。其他列就随你便，爱添多少列都可以的，不影响我的查询。
4、入库明细表：
存货编码、仓库、入库时间、数量、本币无税单价一定要输，金额是派生的不作要求，其他信息添加或删除都不会影响我的查询。
5、出库明细表：
存货编码、数量、单价、仓库、出库日期一定要输，金额是派生的不作要求，其他信息添加或删除都不会影响我的查询。
6、选择式输入存货编码
期初余额、入库明细表、出库明细表 为记录录入表，甄别物料只用一个存货编码代替，考虑到没人会记得哪个编码代表哪个货物，所以在你要输入的数据行中（也就是记录的下一行的A列）有一个触发事件，就会弹出一个窗口，里面有存货编码、名称、物料属性供你们输入和选择，物料编码要求从第一个开始输入，名称可以取文本中间任意连续文字符输入，实现模糊查询，结果显示在下面的窗口中，双击下面的清单的某一行的任意列，存货代码就会输入到你要输入的行中第一列中-即存货编码列中。
也可以在该行用该窗口实现查询功能，在窗体弹出来，输入存货编码，就会有对应的信息显示在下面的窗口中。
7、结果表为常规的多表多交叉的SQL方式的数据透视表


VAB+SQL+数据透视表--仓库管理.rar (83.41 KB, 下载次数: 2920)

2009-8-10 22:19 上传

点击文件名下载附件

[ 本帖最后由 Scarlett_88 于 2009-8-10 22:19 编辑 ]

Scarlett_88

该帖就讲解代码：

1、在3个录入工作表中都有一个工资表选择改变的事件,只有选一个单元格并且在数据最后的下一行是触发该事件
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
targetrow = Target.Row
If targetrow = 1 + Range("a65536").End(xlUp).Row And Target.Column = 1 And Target.Count = 1 Then
inqury.Show
End If
End Sub

2、 查询窗体中有5个控件，对应存货编码、存货名称、物料属性、一个listview，一个退出按钮，用法2楼已有说明
代码稍后补上
Dim arr, arrdata


Private Sub ComboBox1_Change()
Call getdata
End Sub


Private Sub CommandButton1_Click()
    Unload Me
End Sub


Private Sub ListView1_DblClick()
ActiveSheet.Cells(targetrow, 1).Value = ListView1.SelectedItem.Text
ActiveSheet.Cells(targetrow, 1).NumberFormatLocal = "0000000000"
Unload Me
End Sub

Private Sub TextBox1_Change()
Call getdata
End Sub

Private Sub TextBox2_Change()
Call getdata
End Sub
Sub getdata()
Dim itm As MSComctlLib.ListItem
ListView1.ListItems.Clear
With Sheets("物料编码")
n = .Range("a65536").End(xlUp).Row
arrdata = .Range("a1:e" & n).Value
End With

    For i = 2 To n
       If (TextBox1.Text = Left(arrdata(i, 1), Len(TextBox1.Text)) Or TextBox1 = "") And (ComboBox1.Value = arrdata(i, 5) Or ComboBox1.Value = "") And (InStr(arrdata(i, 3), TextBox2.Text) > 0 Or TextBox2.Text = "") Then
        Set itm = ListView1.ListItems.Add()
        itm.Text = arrdata(i, 1)
        itm.SubItems(1) = arrdata(i, 2)
        itm.SubItems(2) = arrdata(i, 3)
        itm.SubItems(3) = arrdata(i, 4)
        itm.SubItems(4) = arrdata(i, 5)
        End If
    Next i
Set itm = Nothing
End Sub

Private Sub UserForm_Initialize()
Dim n%, i%, arrd()
Dim d  As New Dictionary
On Error Resume Next
With Sheets("物料编码")
n = .Range("e65536").End(xlUp).Row
arr = .Range("e1:e" & n).Value
End With
'Set d = CreateObject("Scripting.Dictionary")
For i = 2 To n
d.Add arr(i, 1), ""
Next
arrd = d.Keys
For i = 0 To d.Count - 1
ComboBox1.AddItem arrd(i)
Next
Call listviewload
End Sub
Sub listviewload()
'Dim ITM As ListItem
Dim itm As MSComctlLib.ListItem
With Sheets("物料编码")
n = .Range("a65536").End(xlUp).Row
arrdata = .Range("a1:e" & n).Value
End With
With ListView1
    .ColumnHeaders.Add 1, , arrdata(1, 1), .Width * 0.2
    .ColumnHeaders.Add 2, , arrdata(1, 2), .Width * 0.1
    .ColumnHeaders.Add 3, , arrdata(1, 3), .Width * 0.5, lvwColumnCenter
    .ColumnHeaders.Add 4, , "单位", .Width * 0.1, lvwColumnCenter
    .ColumnHeaders.Add 5, , arrdata(1, 5), .Width * 0.1, lvwColumnCenter
    .View = lvwReport
    .Gridlines = True
    .FullRowSelect = True    '整行选取
    For i = 2 To n
        Set itm = .ListItems.Add()
        itm.Text = arrdata(i, 1)
        itm.SubItems(1) = arrdata(i, 2)
        itm.SubItems(2) = arrdata(i, 3)
        itm.SubItems(3) = arrdata(i, 4)
        itm.SubItems(4) = arrdata(i, 5)
    Next i
End With
Set itm = Nothing
End Sub



3、模块中主要是把工作表中的所选单元格信息传递给窗体中应用。

[ 本帖最后由 Scarlett_88 于 2009-6-12 12:54 编辑 ]

Scarlett_88

提供两种SQL语句的写法：
写法1：
select * from
(
select W, Z,存货名称,单位,  
(  
select sum(D)  from   
(  
select 存货编码 as W,year('1/1/2000')&"-"& month('1/1/2000') as Z,datevalue('1/1/2000')*1 as T,数量*1 as D,  金额*1 as P  from [期初余额$]   
UNION ALL     
SELECT 存货编码, year(入库日期)&"-"& month(入库日期),datevalue(入库日期)*1 as T,数量*1,单价 *数量  FROM [入库明细表$]     
UNION ALL     
SELECT 存货编码, year(出库日期)&"-"& month(出库日期),datevalue(出库日期)*1 , 数量*(-1),单价*数量*(-1)  
FROM [出库明细表$]  
)  
where T<=b.S and W=b.W  
)  as D2,  
(  
select sum(P) from   
(  
select 存货编码 as W,year('1/1/2000')&"-"& month('1/1/2000') as Z,datevalue('1/1/2000')*1 as T,数量*1 as D,  金额*1 as P  
from [期初余额$]   
UNION ALL     
SELECT 存货编码, year(入库日期)&"-"& month(入库日期),datevalue(入库日期)*1 as T,数量*1,单价 *数量  FROM [入库明细表$]     
UNION ALL     
SELECT 存货编码, year(出库日期)&"-"& month(出库日期),datevalue(出库日期)*1 , 数量*(-1),单价*数量*(-1)  
FROM [出库明细表$]  
)  
where T<=b.S and W=b.W  
) as P2  
from   
(  
select * from  
(  
select Z,max(T) as S from   
(  
SELECT '1/1/2000' as R,year(R)&"-"& month(R) as Z,datevalue(R)*1 as T  
from [期初余额$]   
UNION ALL     
SELECT 入库日期 as R,year(R)&"-"& month(R),datevalue(R)*1  
FROM [入库明细表$]     
UNION ALL     
SELECT 出库日期 as R,year(R)&"-"& month(R),datevalue(R)*1  
FROM [出库明细表$]  
)  
group by Z  
) e,  
(  
SELECT 存货编码  as W  
from [期初余额$]  
UNION   
SELECT 存货编码  
FROM [入库明细表$]   
UNION     
SELECT 存货编码  
FROM [出库明细表$]  
)  
) b, [物料编码$] f  
where b.W=f.存货编码
)
where D2<>0


语句分析：

1、1提取所有存货编码：
SELECT 存货编码  as W  
from [期初余额$]  
UNION   
SELECT 存货编码  
FROM [入库明细表$]   
UNION     
SELECT 存货编码  
FROM [出库明细表$]  
1、2：
提取每个月最大日期：
select Z,max(T) as S from   
(  
SELECT '1/1/2000' as R,year(R)&"-"& month(R) as Z,datevalue(R)*1 as T  
from [期初余额$]   
UNION ALL     
SELECT 入库日期 as R,year(R)&"-"& month(R),datevalue(R)*1  
FROM [入库明细表$]     
UNION ALL     
SELECT 出库日期 as R,year(R)&"-"& month(R),datevalue(R)*1  
FROM [出库明细表$]  
)  
group by Z

1、3 将存货编码、月份和最大日期组合起来，并命名为b表：
select * from  
(  
select Z,max(T) as S from   
(  
SELECT '1/1/2000' as R,year(R)&"-"& month(R) as Z,datevalue(R)*1 as T  
from [期初余额$]   
UNION ALL     
SELECT 入库日期 as R,year(R)&"-"& month(R),datevalue(R)*1  
FROM [入库明细表$]     
UNION ALL     
SELECT 出库日期 as R,year(R)&"-"& month(R),datevalue(R)*1  
FROM [出库明细表$]  
)  
group by Z  
) e,  
(  
SELECT 存货编码  as W  
from [期初余额$]  
UNION   
SELECT 存货编码  
FROM [入库明细表$]   
UNION     
SELECT 存货编码  
FROM [出库明细表$]  
)


1、4
对b表中的最大日期为最大值进行数量累加和金额累加，并提取出物料编码表中的其他字段：
select W, Z,存货名称,单位,  
(  
select sum(D)  from   
(  
select 存货编码 as W,year('1/1/2000')&"-"& month('1/1/2000') as Z,datevalue('1/1/2000')*1 as T,数量*1 as D,  金额*1 as P  from [期初余额$]   
UNION ALL     
SELECT 存货编码, year(入库日期)&"-"& month(入库日期),datevalue(入库日期)*1 as T,数量*1,单价 *数量  FROM [入库明细表$]     
UNION ALL     
SELECT 存货编码, year(出库日期)&"-"& month(出库日期),datevalue(出库日期)*1 , 数量*(-1),单价*数量*(-1)  
FROM [出库明细表$]  
)  
where T<=b.S and W=b.W  
)  as D2,  
(  
select sum(P) from   
(  
select 存货编码 as W,year('1/1/2000')&"-"& month('1/1/2000') as Z,datevalue('1/1/2000')*1 as T,数量*1 as D,  金额*1 as P  
from [期初余额$]   
UNION ALL     
SELECT 存货编码, year(入库日期)&"-"& month(入库日期),datevalue(入库日期)*1 as T,数量*1,单价 *数量  FROM [入库明细表$]     
UNION ALL     
SELECT 存货编码, year(出库日期)&"-"& month(出库日期),datevalue(出库日期)*1 , 数量*(-1),单价*数量*(-1)  
FROM [出库明细表$]  
)  
where T<=b.S and W=b.W  
) as P2  
from   
(  
。。。。。
) b, [物料编码$] f  
where b.W=f.存货编码


1、5 整个语句
对各个月的累加数据做0值处理：

select * from
(
。。。。。。
)
where D2<>0



写法2：
select w,z,D2,P2,存货名称,单位 from
(
select a.w,b.z, sum(D) as D2,sum(P) as P2  
from
(
select 存货编码 as W,datevalue('1/1/2000')*1 as T,format(T,'yyyy-mm') as z,数量*1 as D,  金额*1 as P  
from [期初余额$]   
UNION ALL     
SELECT 存货编码,datevalue(入库日期)*1 as T,format(T,'yyyy-mm'),数量*1,单价 *数量  FROM [入库明细表$]     
UNION ALL     
SELECT 存货编码,datevalue(出库日期)*1 as T,format(T,'yyyy-mm'), 数量*(-1),单价*数量*(-1)
FROM [出库明细表$]
) a,
(  
select Z
from
(
SELECT datevalue('1/1/2000')*1 as T,format(T,'yyyy-mm') as z
from [期初余额$]   
UNION ALL     
SELECT datevalue(入库日期)*1 as T,format(T,'yyyy-mm')
FROM [入库明细表$]     
UNION ALL     
SELECT datevalue(出库日期)*1 as T,format(T,'yyyy-mm')
FROM [出库明细表$]
)  
group by Z  
) b  
where a.z<=b.z
group by a.w,b.z
) j,[物料编码$] f
where D2<>0 and j.W=f.存货编码

语句分析：
2、1 提取年月出来，因为年月用字符型表示，为了进行数据的累加，必须使年月具有相同的字符长度，这里就必须用format(T,'yyyy-mm')，而不能用format(T,'yyyy-m')

SELECT datevalue('1/1/2000')*1 as T,format(T,'yyyy-mm') as z
from [期初余额$]   
UNION ALL     
SELECT datevalue(入库日期)*1 as T,format(T,'yyyy-mm')
FROM [入库明细表$]     
UNION ALL     
SELECT datevalue(出库日期)*1 as T,format(T,'yyyy-mm')
FROM [出库明细表$]

2、2
对年月去唯一值
select Z
from
(
。。。
)  
group by Z  

2、3
对期初余额、入库明细表和出库明细表进行数据组合：
select 存货编码 as W,datevalue('1/1/2000')*1 as T,format(T,'yyyy-mm') as z,数量*1 as D,  金额*1 as P  
from [期初余额$]   
UNION ALL     
SELECT 存货编码,datevalue(入库日期)*1 as T,format(T,'yyyy-mm'),数量*1,单价 *数量  FROM [入库明细表$]     
UNION ALL     
SELECT 存货编码,datevalue(出库日期)*1 as T,format(T,'yyyy-mm'), 数量*(-1),单价*数量*(-1)
FROM [出库明细表$]

2、4
对年月前面的数据进行累加，这里同写法1的最不同点，写法1是里层求和条件是小于或等于外层的条件，此处是将所有记录组合，按条件进行筛选，网撒得更远，再用聚合函数收回来。
select a.w,b.z, sum(D) as D2,sum(P) as P2  
from
(
。。。
) a,
(  
。。。
) b  
where a.z<=b.z
group by a.w,b.z

2、5
做0值处理和关联物料编码的一些字段：
select w,z,D2,P2,存货名称,单位 from
(
。。。
) j,[物料编码$] f
where D2<>0 and j.W=f.存货编码

[ 本帖最后由 Scarlett_88 于 2009-8-10 22:55 编辑 ]

BIN_YANG168

支持新版主，视频演示占位。

BIN_YANG168

视频演示占位。

小蚁雄兵

我先来一个，作为财务，更注重金额的核算，希望能够实现按先进先出法进行核算。

开心萝卜惠州

我是搞成本核算的，期待输入物料编码自动生成对应的物料名称及价格

gxl00

做这一行的兄弟把整好数据源发上去啊,

平帆

出库的材料要能反映领用的部门和用于何处((项目)