关于VBA对数据透视表的操作使用

 点击查看博主技术栈

http://club.excelhome.net/thread-1147804-1-1.html

小弟不才，初来乍到EH论坛...在学习VBA学习E表的过程中，对于数据透视表的操作，这两天处处碰壁。不过在从网上查询资料解决问题之后，也想把这次遇到的问题总结下来，分享一下心得。希望能以此使其他的朋友在刚接触VBA数据透视表的时候，能少走我走过的弯路。主要还是描述性的介绍相关的语法含义，谈不上什么技巧。小弟才识不高，水平有限。有纰漏的地方还望前辈们不吝指正。书归正传，先上代码，创建数据透视表:

1. ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
2.         strSourceData, Version:=xlPivotTableVersion10).CreatePivotTable _
3.         TableDestination:="输出表!R1C1", TableName:="考勤", DefaultVersion:= _
4.         xlPivotTableVersion10

复制代码

       首先是创建数据表，对于创建数据表来说，不需要将我们的目标worksheet进行activate即可创建。ActiveWorkbook.PivotCaches.Create()个人理解是建立一块缓存，来保存要处理成数据透视表的数据信息，括号内输入获取数据的所需参数：SourceType:=后跟数据源类型(术语是不是这么叫？还望大大指点，我这是直译哈)一般就是xlDatabase(基于xl的数据库类型的意思吧)；
SourceData:=后跟所要选取的数据区域。比如我想使用表"请假明细"中的A1：D100区域的数据，那么这里的strSourceData就可以替换为"请假明细!R1C1:R100C4"；(至于我为什么没有直接写"请假明细!R1C1:R100C4"，先卖个关子，在后面再讲哈O(∩_∩)O)
Version:=后跟创建数据透视表的表类型。通常.xls结尾的文件即97-03工作簿，是使用xlPivotTableVersion10，即Excel数据透视表版本10。样图如下：
 
 
xlsx及xlsm等07之后版本的，数据透视表版本就不一样了，小弟印象中07貌似是xlPivotTableVersion12，不过没有确认过。本地计算机是使用Office2010，数据透视表版本是xlPivotTableVersion14，样图如下：
 
 
版本不同，使用起来还是略有不同的。具体细节小弟在此不一一赘述了，各位看官可以自己体验一下。

我们继续说，ActiveWorkbook.PivotCaches.Create得到的是一个PivotCaches实例，后面加.CreatePivotTable是表示用这块缓存数据来建立数据透视表。 .CreatePivotTable后面跟的是创建数据透视表所需的参数：
TableDestination:=后跟生成的数据透视表的位置，假如我们要在"输出表"worksheet的A5单元格为左上角生成数据透视表，那么这个位置就可以写"输出表!R5C1"。
TableName:=后跟创建数据透视表的名称，这个很重要，如果要用宏创建多个数据表的时候，要注意这里很可能后面跟的是一个变量。Excel在创建数据透视表的时候，会检查此Workbook内所包含的所有数据表的名称，是否有重复名称，如果有重复名称的情况(即当前创建的数据透视表与其他表重名)，则会报错。
DefaultVersion:=后跟数据透视表的版本号，必须与之前创建缓存时版本号相同，例子中使用xlPivotTableVersion10。
至此，数据透视表就暂时建好了，也就是从"输出表"worksheet中，我们可以进行熟悉的拖拽了。

       当然，如果"拖拽"总是重复的数据汇总处理，我们还可以对宏进行扩充，让Excel帮我们把剩下的工作也处理好。
举例来说，附件中的表格我们使用了五个字段来建立数据透视表，分别是“工号”、“姓名”、“申请日”、“缺勤时数”、“缺勤类别”。※需要注意一点的是：在对我们创建好的数据透视表进行操作的时候，一定要在输入后面的代码之前，先加上一行代码

1. Sheets("输出表").Activate

复制代码

这可是让小弟吃了不少的苦头！一开始的时候没有注意，但是后来发现(可能也跟研究素材是宏录制代码有关)，由于之后进行操作的代码都是以ActiveSheet为前提的，也就是要操作之前，先要对要操作的目标worksheet工作表进行选中，故加此代码。这样就能选中"输出表"worksheet，并对此工作表上我们刚建立好的数据透视表进行操作了。


一个小插曲，我们继续。
例子中，我们对人员的请假时间进行汇总。列字段添加“缺勤类别”

1. '   添加缺勤类别字段
2.     With ActiveSheet.PivotTables("考勤").PivotFields("缺勤类别")                '表示对于名称为"考勤"的数据透视表中名为"缺勤类别"的字段进行操作(可见之前提到的数据透视表名称唯一的重要性)
3.         .Orientation = xlColumnField                '表示"缺勤类别"字段在数据透视表中的位置为列字段(行字段是xlRowField）
4.         .Position = 1                                                                        '表示该字段所在列字段的位置为第1个位置
5.     End With

复制代码

相关解释我填写在每句语句后面注释当中了。

有了列字段，还需要行字段，怎么添加呢？我要看人员的各类别请假时间，那么，行字段自然就是人名，或者是工号了。要不两个就都一起来吧！

1. '   添加姓名、工号字段
2.     With ActiveSheet.PivotTables("考勤").PivotFields("姓名")
3.         .Orientation = xlRowField
4.         .Position = 1
5.     End With
6.     With ActiveSheet.PivotTables("考勤").PivotFields("工号")
7.         .Orientation = xlRowField
8.         .Position = 2
9.     End With
10. '   添加姓名、工号字段完成

复制代码

添加行字段与列字段很类似，就不一一赘述了。至此，行字段和列字段的框架是搭完了。
有些朋友在使用xlPivotTableVersion14的时候，会遇到行字段“姓名”和“工号”不能再同一行显示的问题，很不美观，更不方便观看。于是我们可以在后面再加上一句：

1. ActiveSheet.PivotTables("考勤").RowAxisLayout xlTabularRow

复制代码

现在工号和姓名就在同一行显示了。最後，我们要把查看的数据添加进来，就基本上大功告成了。

1.     ActiveSheet.PivotTables("考勤").AddDataField ActiveSheet.PivotTables("考勤" _
2.             ).PivotFields("缺勤时数"), "求和：缺勤时数", xlSum

复制代码

其中，ActiveSheet.PivotTables("考勤").AddDataField表示的是当前工作表的名为“考勤”的数据透视表，要对它添加数据，后面是三个参数：
第一个参数PivotTables("考勤").PivotFields("缺勤时数")，表示添加的数据来自"考勤"透视表中的"缺勤时数"字段；
第二个参数"求和：缺勤时数"，表示在xlPivotTableVersion10中，在不添加列标签的时候，会显示的"求和项：XXX"/"计数项：XXX"的自定义名称；
第三个参数xlSum，表示数据汇总方式为求和。另外还有参数xlCount(计数)，等等。该处设置对应在数据透视表中，数据处鼠标右键-值字段设置-值汇总方式里面的参数。

这里在多说一句，ActiveSheet.PivotTables("考勤").PivotFields是当前工作表中数据透视表"考勤"里面包含全部字段的集合，它是一个数组...我们是可以进行foreach循环的。我们可以更方便的对字段很多的表格进行操作。

接下来，请各位看官回想一下，开头的地方，在选择数据源的时候，我没有直接使用常量，而是使用了一个变量strSourceData，到现在还没有解释，不知还有印象否。这是我想要提到的一个小技巧，由于数据的行数通常是不确定的(列数可能稍微还好一点，不过也有会出现源数据字段的数量会有变化的情况，比如以一个月的每日日期为字段，就会出现这个月30个字段，下个月31个字段的现象)，那么怎么应对呢。我们可以加入这么一段代码：



至此，我们的数据透视表生成的宏基本上就写完了。

小弟主要还是针对版本为xlPivotTableVersion10进行的测试。由于xlPivotTableVersion14有很多其他的功能，小弟也是半试半学，不是很了解，就没有对其他功能的代码进行描述。希望小弟以后能够再发一篇有关xlPivotTableVersion14介绍的更详细的帖子。小弟第一次来ExcelHome发帖，希望各位前辈多提宝贵意见。大家一起交流，共同进步！


附件为事例文件，xls可供2003版用户了解，xlsm可供07及以上版本用户了解。
 数据透视表说明xlsm.7z (35.59 KB, 下载次数: 128) 
 数据透视表说明xls.7z (15.82 KB, 下载次数: 78)