用VBA创建数据透视表

以下内容摘录国外一本书上：

一.用数据透视表向导创建透视表的方法：

Worksheets(“Sheet2”).PivotTableWizard SourceType:=xlDatabase,SourceData:=Range(“Sheet1!A4:E250”)

用PivotTableWizard方法，创建一个透视表，2个参数，一个SourceType数据源类型，采用excel数据库；另外一个SourceData数据源，采用工作表Sheet1!A4:E250单元格区域，透视表的放置位置在当前活动的单元格。

当然，你也可以给数据透视表指定放置的详细位置，例如，把它放在D4单元格，透视表命名为:=”My Pivot Table”，代码变为：

Worksheets(“Sheet2”).PivotTableWizard SourceType:=xlDatabase, SourceData:=Range(“Sheet1!A4:E250”), _

TableDestination:=Range(“D4”), TableName:=”My Pivot Table”

最后，你还可以指定“行合计”和“列合计”，则代码变为:

Worksheets(“Sheet2”).PivotTableWizard SourceType:=xlDatabase, SourceData:=Range(“Sheet1!A4:E250”), _

TableDestination:=Range(“D4”),TableName:=”My Pivot Table”, _

RowGrand:=True, ColumnGrand:=True

我们一般使用3个参数即可，其它的省略：

Worksheets(“Sheet2”).PivotTableWizard SourceType:=xlDatabase, _

SourceData:=Range(“Sheet1!A4:C28”),TableDestination:=Range(“B2”)

二.用PivotCache透视表内存缓存创建一个数据透视表：

Public Sub CreatePivotTable()

Dim wb As Workbook    ’声明3个变量！

Dim pt As PivotTable

Dim pc As PivotCache

On Error GoTo ErrorHandler

‘ Open the workbook.即设置打开一个工作簿的变量。

Set wb = Workbooks.Open(“c:\\PivotData\\VideoStoreRawData.xls”)

‘ Create the PivotCache.创建一个透视表内存缓存！

Set pc = wb.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=”[VideoStoreRawData.xls]Sheet1!A4:C28”)

‘ 采用CreatePivotTable方法，利用透视表缓存创建一个数据透视表！

Set pt = pc.CreatePivotTable TableDestination:=”[VideoStoreRawData.xls]Sheet2! “,TableName:=”Video Data”

‘下面是错误处理程序，可以借鉴一下，很常用的方法！

wb.Worksheets(“Sheet2”).Activate

EndOfSub:

Exit Sub

ErrorHandler:

If Err.Number = 5 Or Err.Number = 9 Then

MsgBox “The file could not be found”

ElseIf Err.Number = 1004 Then

MsgBox “There is already a PivotTable at that location”

Else

MsgBox “Error “ & Err & “ - “ & Err.Description

End If

Resume EndOfSub

End Sub

以上2种方法，创建出一个奇怪的4个单元格形状的数据透视表，透视表中没有任何字段！

下面是添加行字段，列字段和页字段的方法：

用Addfields方法，来添加，它的语法是：

AddFields(RowFields, ColumnFields, PageFields, _ AddToTable)

如果你只添加一个字段，例如：

Worksheets(“Sheet1”).PivotTables(1).AddFields _

(ColumnFields:=”Region”, AddToTable:=True)

如果添加多个字段，则用Array来添加：

myPivotTable.AddFields(RowFields:=Array(“Status”, “DueDate”))

下面的代码是一个完整的例子，包括行，列和页字段的添加：

myPivotTable.AddFields(RowFields:=”Region”, _

ColumnFields:=”Quarter”, _

PageFields:=Array(“Status”, “DueDate”))

下面继续介绍向数据区域添加数据字段的方法：

如果你只添加一个数据字段，则用AddDataField方法，它的语法是：

AddDataField(Field,Caption,Function)

Field参数的表示方法：PivotFields(Name)

例子如下：

Dim pt As PivotTable

Set pt = Worksheets(“Sheet1”).PivotTables(“PivotTable1”)

pt.AddDataField pt.PivotFields(“Sales”), “Total Sales”

下面是一个创建数据透视表的完整例子：

Public Sub CreateCompletePivotTable()

Dim wb As Workbook

Dim pt As PivotTable

On Error GoTo ErrorHandler

‘ Open the workbook.

Set wb = Workbooks.Open(“c:\\PivotData\\VideoStoreRawData.xls”)

‘ Create the PivotTable and get a reference to it.

Set pt = Worksheets(“Sheet2”).PivotTableWizard(SourceType:=xlDatabase, _

SourceData:=Range(“Sheet1!A4:C28”), _

TableDestination:=Range(“Sheet2!B2”))

‘ Add row and column fields.

pt.AddFields RowFields:=”Store”, ColumnFields:=”Category”

‘ Add data field.

pt.AddDataField pt.PivotFields(“Titles”), “Total Titles”

EndOfSub:

Exit Sub

ErrorHandler:

If Err.Number = 5 Or Err.Number = 9 Then

MsgBox “The file could not be found”

ElseIf Err.Number = 1004 Then

MsgBox “There is already a PivotTable at that location”

Else

MsgBox “Error “ & Err & “ - “ & Err.Description

End If

Resume EndOfSub

End Sub