Excel VBA利用Transform函数+SQL交叉汇总数据

一、VBA中transform函数基本语法：

Creates a crosstab query.
Syntax
TRANSFORM aggfunction
selectstatement
PIVOT pivotfield [IN (value1[, value2[, ...]])]
The TRANSFORM statement has these parts:

二、我们有”sales”表，交叉汇总到”汇总”表，两表在同一工作簿中

部门  员工  月份  销售额     部门  员工  1月  2月  3月  4月
销售1部    张三  1月  9000        销售1部    张三  9000    4800    7800    6900
销售1部    张三  2月  4800        销售1部    李四  4000    4800    7800    8000
销售1部    张三  3月  7800        销售2部    小米  8000    4580    8570    4000
销售1部    张三  4月  6900        销售2部    苹果  4800    7800    8000    4870
销售1部    李四  1月  4000                            
销售1部    李四  2月  4800        汇总表                 
销售1部    李四  3月  7800                            
销售1部    李四  4月  8000                            
销售2部    小米  1月  8000                            
销售2部    小米  2月  4580                            
销售2部    小米  3月  8570                            
销售2部    小米  4月  4000                            
销售2部    苹果  1月  4800                            
销售2部    苹果  2月  7800                            
销售2部    苹果  3月  8000                            
销售2部    苹果  4月  4870                            

sales表                                      

三、Excel VBA实现代码如下：

Private Sub 交叉汇总()
Dim cnn As Object, rst As Object, sql$, n As Byte
Set cnn = CreateObject("adodb.connection")
cnn.Open "dsn=excel files;dbq=" & ThisWorkbook.FullName
Application.ScreenUpdating = False
sql = "transform sum(销售额) select 部门,员工 from [sales$] group by 部门,员工 pivot 月份"
Set rst = cnn.Execute(sql)
With Worksheets("汇总")
    .[A1].CurrentRegion.ClearContents
For n = 1 To rst.Fields.Count
    .Cells(1, n) = rst.Fields(n - 1).Name
Next
    .[A2].CopyFromRecordset rst
End With
cnn.Close
Set cnn = Nothing
Set rst = Nothing
Application.ScreenUpdating = True
End Sub