使用VBA编程自动化处理数据透视表，距离你一步之遥

前面内容介绍的都是在Excel界面中通过手动执行命令来创建和设置数据透视表。如果想要以更加自动的方式操作数据透视表，那么可以编写VBA代码。虽然在使用VBA编程处理数据透视表时，用户只需单击一个按钮即可在瞬间完成数据透视表的创建和设置工作。

在VBE窗口中编写代码，为了让宏可以在不同的环境下稳定地工作，并具有更高的执行效率，用户可以修改录制好的宏中所包含的VBA代码；下面将介绍VBE窗口的各个组成部分。​

VBA程序的基本结构，一个VBA程序由过程框架和主代码组成。过程框架定义了一个过程的类型、名称和过程的作用范围，由过程的第一条语句和最后一条语句组成，不同类型的过程具有不同的过程框架。下面是Sub过程的过程框架，以Sub关键字开头，其后跟过程的名称，过程的最后以End Sub结尾。

1、创建基本的数据透视表

使用VBA创建数据透视表之前，需要先创建数据透视表缓存，在其中存储着用于创建数据透视表的数据源，然后使用已创建好的数据透视表缓存来创建数据透视表。可以使用PivotCaches集合的Create方法创建数据透视表缓存，该方法包含3个参数，语法格式如下：

PivotCaches.Create(SourceType, SourceData, Version)

创建好数据透视表缓存后，可以使用PivotCache对象的CreatePivotTable方法创建数据透视表。该方法包含4个参数，语法格式如下：

PivotCache.CreatePivotTable(TableDestination, TableName, ReadData, DefaultVersion)

下面的代码以如图所示的数据区域作为数据源，创建一个空白的数据透视表，如图所示。数据源所在工作表的名称是“数据源”，将放置数据透视表的工作表命名为“数据透视表”，将创建的数据透视表命名为“销量分析”。pvc变量表示数据透视表缓存，pvt变量表示数据透视表，rngSource变量表示数据源区域，rngPvt变量表示放置所创建的数据透视表区域左上角的单元格。

Sub创建数据透视表缓存和数据透视表（）
Dim pvc As PivotCache, pvt As PivotTable
Dim rngSource As Range, rngPvt As Range
Set rngSource=Worksheets("数据源").Range("A1").CurrentRegion
Worksheets.Add
ActiveSheet.Name="数据透视表"
Set rngPvt = ActiveSheet.Range("A3")
Set pvc = ActiveWorkbook.PivotCaches.Create(xlDatabase, rngSource)
pvc.CreatePivotTable rngPvt,"销量分析"
End Sub

2、将字段添加到数据透视表中

下面的代码对数据透视表中的字段进行布局，将“负责人”字段添加到报表筛选区域，将“地区”和“商品”两个字段添加到行区域，将“销量”和“销售额”两个字段添加到值区域。对字段进行布局后的数据透视表如图所示。首先将活动工作表中的数据透视表赋值给pvt变量，然后使用PivotField对象的Orientation属性将字段添加到那个区域。由于本例中行区域包含两个字段，因此需要使用PivotField对象的Position属性指定字段的排列顺序。使用PivotTable对象的AddDataField方法将值字段添加到数据透视表中。

sub将字段添加到数据透视表中（）
Dim pvt As PivotTable
Set pvt=Worksheets("数据透视表").PivotTables(1)
With pvt
.PivotFields("负责人").Orientation=xlPageField
With.PivotFields("地区")
.Orientation = xlRowField
.Position = 1
End With
With.PivotFields（"商品"）
.Orientation = xlRowField
.Position = 2
End With
.AddDataField.PivotFields("销量")
.AddDataField.PivotFields("销售额")
End With
End Sub

3、调整和删除字段

在对数据透视表中的字段进行布局之后，可能需要调整字段的位置或删除不需要的字段。调整数据透视表中现有字段的位置仍然需要使用PivotField对象的Orientation和Position属性，而从数据透视表中删除字段则需要将Orientation属性的值设置为xlHidden。

下面的代码将“地区”字段从行区域移动到报表筛选区域，并将该字段置于“负责人”字段的下方，然后将“销量”字段从数据透视表中删除，如图所示。

sub调整和删除字段（）
Dim pvt As PivotTable
Set pvt=Worksheets("数据透视表").PivotTables(1)
With pvt
With.PivotFields("地区")
.Orientation = xlPageField
.Position = 1
End With
.PivotFields("求和项：销量").Orientation=xlHidden
End With
End Sub

4、修改字段的名称

可以修改数据透视表中字段的显示名称，但是该名称不能与数据透视表字段列表窗格的同一个字段的名称相同。为了让同一个字段获得两个完全相同的名称，可以在数据透视表中的字段名称的末尾添加一个空格，这样Excel会将其看作另一个不同的名称。可以使用PivotTable对象的Name属性设置字段的名称。

下面的代码将数据透视表中的“求和项:销售额”字段的名称改为“销售额”，如图所示。

sub修改字段的名称（）
Dim pvt As PivotTable
Set pvt=Worksheets("数据透视表").PivotTables(1)
pvt.PivotFields("求和项：销售额").Name="销售额"
End Sub

5、设置数据透视表的布局形式

使用PivotTable对象的RowAxisLayout方法可以设置数据透视表的布局形式，该方法的参数值由XlLayoutRowType常量提供，如表所示。

sub设置数据透视表的布局形式（）
Dim pvt As PivotTable
Set pvt=Worksheets("数据透视表").PivotTables(1)
pvt.RowAxisLayout xlTabularRow
End Sub

6、刷新数据透视表

如果对数据源中的数据进行了修改，那么可以通过刷新命令使修改后的结果反映到已创建好的数据透视表中，以便于数据源中的数据保持同步。下面的代码将刷新名为“数据透视表”的工作表中的数据透视表数据。

Worksheets("数据透视表").PivotTables(1).RefreshTable

如果你还有很多不明白的地方，暂时不用纠结，接下来会详细讲解：

在excel的日常办公中，图表并不需要太多创意，这节课所学，应该就够用。但是，在PPT中用excel图表进行创意呈现反而更多，如果你想学更多脑洞大开的创意图表，去找Excel教案吧。​

好啦！快乐的时间总是非常短暂的，又到了和大家说再见的时候，如果大家感觉本文如果对你有所帮助的话，请帮忙转发点赞，谢谢您能观看到现在，我们下期再见！

我是Excel教案，关注我持续分享更多的Excel技巧！

如果有不明白的或者不懂的可以在下方留言，我们会一一解答的。