表亲们都知道,数据透视表是Excel中比较强大的功能。它提供了可交互、自定义、多视角数据分析功能,是众多数据分析人员的最爱。


虽然它集众多优点于一身,但是人无完人,数据透视表也有自己的一些缺陷.比如操作起来步骤多。


那么能不能借助VBA来操作数据透视表,这样对数据透视表更有驾驭感。今天我们一起看一下,VBA操作数据透视表的大致流程。在后续的文章中,我们将一起学习数据透视表操作涉及到的对象以及各个对象之间的关系。


好啦,下面正式开启我们的连载环节。在开始介绍VBA操作数据透视表我们可以先回顾下,我们是如何人工操作数据表的。


Step1

选择数据源

支持如下图所示的四种数据源类型(可以通过ALT+D+P进行选择) 

数据透视表(八) 教程 第1张

选择数据透视表放置的位置

支持放在当前sheet的某个位置

支持新建一个新的sheet  

数据透视表(八) 教程 第2张

 

Step2

设置

报表筛选字段

行、列标签

计算字段、方法

数据透视表(八) 教程 第3张

上面讲述的是人工操作透视表的过程。操作起来还是有些繁琐,下面讲给大家介绍如何借助VBA创建一个透视表。

在介绍使用VBA创建透视表前,先看下面的透视表对象层次关系

数据透视表(八) 教程 第4张

在上图的基础上,我们一起学习下用VBA创建透视表的过程。在代码的书写过程中,将概要地介绍各个函数的用法,在后面的连载中,我们将一起学习每个对象的使用方法。


对透视表对象不熟悉的同学,暂时可以先录制一段宏。然后在宏的基础上修修补补形成自己的代码就可以了。


目前阶段我们先理解概念,后续等介绍完各个对象以后我们就可以熟练使用透视表相关的对象了,现在我们不生产代码,我们只是代码的搬运工。


 ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _

       "Sheet2!R1C1:R3C4",Version:=xlPivotTableVersion12).CreatePivotTable _

        TableDestination:="Sheet2!R4C13",TableName:="数据透视表1", defaultversion:= _

        xlPivotTableVersion12


代码说明:

1._ 目的是将代码分成多行

2.ActiveWorkbook.PivotCaches取到当前活动workbook下的PivotCaches对象。有了这个对象可以提升透视表的访问效率。可以理解它是PivotCache资源池用于管理创建PivotCache

3.ActveWorkbook.PivotCaches.Create 用来创建一个PivotCache对象,参数说明如下

SourceType 指定数据源类型

SourceData 指定数据源,这里使用区域的RC表示法

Version 指定Cache的版本


4.Cache有了,接下来CreatePivotTable 用来创建一个透视表,参数说明如下

TableDestination 用来指定透视表对应的目标位置

Tablename 用来定义透视表的名称

Defaultversion 用来指定透视表的版本


  至此透视表创建完毕,接下来设置行列标签


 With ActiveSheet.PivotTables("数据透视表1").PivotFields("性别")

        .Orientation =xlRowField

        .Position = 1

    End With

      With ActiveSheet.PivotTables("数据透视表1").PivotFields("年龄")

        .Orientation =xlColumnField

        .Position = 1

    End With



ActiveSheet.PivotTables("数据透视表1") 找到刚才创建的透视表

.PivotFields("性别")用来添加行列标签

Orientation 用来指定是行标签还是列标签

因为行列标签支持多个字段,Position 指定排在对应标签的第几个。

   

 ActiveSheet.PivotTables("数据透视表1").AddDataFieldActiveSheet.PivotTables("数据透视表1" _ ).PivotFields("考试成绩"),"求和项:考试成绩", xlSum


ActiveSheet.PivotTables("数据透视表1").AddDataField 用于添加计算字段

ActiveSheet.PivotTables("数据透视表1" _

       ).PivotFields("考试成绩"), "求和项:考试成绩", xlSum 用于定义一个计算字段

这里得定义清楚计算字段在数据源中的列名,显示出的名称,计算方式 。   


对上面代码有什么疑问都可以在文章底部给勇哥留言,期待您的留言哦。


大家有没有发现,宏录出来的代码实在太复杂。在后面的连载,将给大家介绍每个透视表对象的使用方法,将复杂的代码拆分成简单的代码。