VBA实现Excel的数据透视表

前言

本节会先容通过VBA的PivotCaches.Create方法实现Excel创建新的数据透视表、修改原有的数据透视表的数据源以及刷新数据透视表内容。
本节测试内容以下表信息为例

---

1、创建数据透视表

语法：PivotCaches.Create(SourceType, [SourceData], [Version])

阐明：

SourceType:必填参数，可以是以下 XlPivotTableSourceType 常量之一： xlConsolidation、 xlDatabase 或 xlExternal
SourceData:非必填，新数据透视表缓存的数据。
Version:版本，非必填，可以是常量xlPivotTableVersion2000，对应Excel 2000，也可以是xlPivotTableVersion10、xlPivotTableVersion11、xlPivotTableVersion12、xlPivotTableVersion14、xlPivotTableVersion15分别表现Excel 2002、2003、2007、2010、2013
示例：

根据上表内容，在原sheet2上创建一个数据透视表，起始位置为J1，透视表设置举动名称、产品编号，列设置为生产年代，值为销售数目求和，完备的代码如下：

1. Sub CreatePivot()
2.    
3.     ' 声明工作簿、工作表变量
4.     Dim wb As Workbook
5.     Dim ws As Worksheet
6.     ' 声明数据源、透视表目标起始位置、数据透视表变量
7.     Dim dataSource As Range
8.     Dim datePivot As Range
9.     Dim newPivot  As PivotTable
10.    
11.     '设置工作簿为当前文件
12.     Set wb = ThisWorkbook
13.     Set ws = ThisWorkbook.Worksheets("Sheet2")
14.    
15.     ' 通过A列获取最大行数
16.     Dim lastRow As Long
17.     lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
18.     ' 定义数据源范围
19.     Set dataSource = ws.Range("A1:F" & lastRow)
20.     ' 定义透视表目的起始位置
21.    
22.     ' 创建一个新的数据透视表
23.     Set newPivot = wb.PivotCaches.Create(xlDatabase, dataSource).CreatePivotTable(ws.Range("J1"), "PivotTable123")
24.    
25.     ' 定义透视表的行列值
26.     With newPivot
27.         .PivotFields("名称").Orientation = xlRowField
28.         .PivotFields("商品编号").Orientation = xlRowField
29.         .PivotFields("生产年月").Orientation = xlColumnField
30.         With .PivotFields("销售数量")
31.             .Orientation = xlDataField
32.             .Function = xlSum
33.         End With
34.     End With
35.       
36. End Sub

复制代码

  代码阐明：
注意 PivotCaches.Create 是用在workbook反面的方法属性
CreatePivotTable 用来指定创建的透视表的位置以及透视表的名称，若想要在一张新的工作表创建，如想在sheet3中创建，则可以将上述代码中的ws.Range(“J1”)改为ThisWorkbook.Worksheets(“Sheet3”).Range(“A1”),前提是该工作簿中存在Sheet3工作表
  

2. 修改数据透视表的数据源

如上例类似，修改已有的数据透视表的数据源，修改为A1：F20,完备的代码如下：

1. Sub UpdatePivotSourceData()
3.     ' 声明工作簿、工作表变量
4.     Dim wb As Workbook
5.     Dim ws As Worksheet
6.     ' 声明数据源、透视表目标起始位置、数据透视表变量
7.     Dim dataSource As Range
8.     Dim datePivot As Range
9.     Dim pt As PivotTable
10.    
11.     '设置工作簿为当前文件
12.     Set wb = ThisWorkbook
13.     Set ws = ThisWorkbook.Worksheets("Sheet2")
14.    
15.     ' 设置要修改的数据透视表名称
16.     Set pt = ws.PivotTables("PivotTable123")
17.    
18.     ' 修改数据透视表的数据范围
19.     pt.sourceData = ws.Range("A1:F20").Address(True, True, xlR1C1, True)
20.    
21.     ' 刷新数据透视表
22.     pt.RefreshTable
24. End Sub

复制代码

3. 刷新数据透视表

pt.RefreshTable
pt表现对应的数据透视表，如以下代码：

1. Sub RefreshPivot
2.         Dim pt As PivotTable
3.         Dim ws As Worksheet
5.         Set ws = ThisWorkbook.Worksheets("Sheet2")
6.         ' 设置要修改的数据透视表名称
7.     Set pt = ws.PivotTables("PivotTable123")
9.         ' 刷新数据透视表
10.     pt.RefreshTable
11.    
12. End Sub

复制代码

对应的数据透视表名称

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。