示例
如图所示,该工作表是某公司工资记录。现希望通过数据透视表按员工编号和姓名汇总应发合计和基本工资,如何用VBA实现?
代码
创建一个原始数据的数据透视表缓存(PivotCache),然后利用该缓存对象创建一个数据透视表(PivotTable)。
数据透视表缓存对象(PivotCache)
在Excel中,数据透视表依赖于数据透视表缓存(PivotCache)而存在。数据透视表缓存是基于原始数据而创建的一个专门的对象,其可以快速实现数据查询和汇总。当需要创建数据透视表时,首先应创建一个基于原数据的缓存对象。在一个工作簿中可以创建多个数据透视表缓存,这些缓存的集合为PivotCaches对象。该对象是依赖于工作簿而存在的,是工作簿对象的一个子对象。创建数据透视表缓存需要使用数据透视表缓存集合(PivotCaches)的Create方法,其语法如下:
PivotCaches.Create(SourceType,[[SourceData],Version])
该方法可以返回一个数据透视表缓存对象(PivotCache)。
参数SourceType表示数据透视表的数据源类型,可以为xIConsolidation(多个区域)、xIDatabase(数据表)或者xIExternal(外部数据)中的任何一个。 参数SourceData为文本,表示数据源,其值可以为多个单元格区域地址的数组(多个区域)、单元格区域的地址(数据表)或者外部连接字符串(外部数据)。 参数Version表示创建数据透视表的版本,其值可以为下表中的任意常量。
访问数据透视表缓存对象(PivotCache)
通过数据透视表缓存集合对象可以访问数据透视表缓存对象,其语法为:
PivotCaches.Item (index)
或者
PivotCaches (index)
其中,PivotCaches为数据透视表缓存集合对象,若要访问当前工作簿,则应使用Thisworkbook. PivotCaches。参数index为数据透视表缓存的顺序号,其编号从1开始。
数据透视表对象(PivotTable)
数据透视表对象是依赖于工作表而存在的,可以在一个工作表中创建多个数据透视表对象,其集合为PivotTables对象。当需要创建数据透视表时,可以使用数据透视表集合(PivotTables)的Add方法或者数据透视表缓存对象(PivotCache)的CreatePivotTable
方法。无论何种方法,必须首先创建针对原数据的数据透视表缓存。
创建数据透视表
可以使用两种方法创建数据透视表,其语法非常相似,具体如下。
使用PivotTables的Add方法创建数据透视表的语法为:
PivotTables.Add(PivotCache,TableDestination,TableName, ReadData,DefaultVersion)
使用PivotCache的CreatePivotTable方法创建数据透视表的语法为:
PivotCache.CreatePivotTable(TableDestination,TableName, ReadData,DefaultVersion)
上述两个方法均可以返回一个数据透视表对象。
其中,PivotCache表不一个数据透视表缓存对象。
参数TableDestination为一个Range对象,表示数据透视表创建的位置。该参数也可以为某个单元格的地址。
参数TableName为可选参数,表示数据透视表的名称。同一个工作表内不允许创建名称相同的两个以上的数据透视表。若该参数省略,则由Excel自动指定一个名称。
参数ReadData为可选,是一个逻辑值,表示外部数据连接的数据透视表缓存的创建方式。当该参数为True时,表示创建一个完整的数据透视表缓存,当为False时,则允许选择部分字段作为缓存。
参数DefaultVersion为数据透视表的版本。
访问数据透视表对象
数据透视表对象可以通过工作表的PivotTables集合进行访问,其语法为:
PivotTables (index)
或者
PivotTables(tableName )
其中,参数index为数据透视表的顺序号,该编号从1开始。参数tableName为数据透视表的名称。
数据透视表的字段(PivotField)
数据透视表字段可以通过集合PivotFields来进行访问,其表示方法为:
PivotTable.PivotFields(FieldName)
其中PivotTable为数据透视表对象。参数FieldName为所要访问的字段名称。
数据透视表的字段可以分为页字段、行字段、列字段、数据字段等,其不同的字段是由数据透视表字段(PivotField)的Orientation属性决定的,见下表。
PivotField对象的Orientation属性
本例中,需要将“姓名”字段放置于行标题处,因而设置其位置为行字段(即Orienation属性设置为xIRowField);而“应发合计”字段需要进行汇总求和,因而将其放置于数据字段位置(即Orienation属性设置为xIDataField)。
数据字段
对于数据区域的字段而言,除了可以将PivotField的Orientation属性设置成xIDataField之外,也可以使用数据透视表对象(PivotTable)的AddDataField方法进行添加,其语法为:
PivotTable.AddDataField(Field,Caption,Function)
其中,PivotTable为数据透视表对象。
参数Field为一个数据透视表字段对象(PivotField)。
参数Caption表示汇总字段的标题,该参数可省略,省略后由Excel指定一个标题。该参数相当于给该数据字段另起了一个字段名称,因而该参数不能设置与当前现有的字段同名。
参数Function表示数据的汇总方式,可以为下表中的任何一个常量。
AddDataField方法的Function参数
当使用Orientation参数将字段设置成数据字段后,同样可以使用字段的Function属性对汇总方式进行设置,其值与AddDataField方法的Function参数一致。