|
|
本帖最后由 最後のGillian 于 2014-8-26 12:07 编辑
小弟不才，初来乍到EH论坛...在学习VBA学习E表的过程中，对于数据透视表的操作，这两天处处碰壁。不过在从网上查询资料解决问题之后，也想把这次遇到的问题总结下来，分享一下心得。希望能以此使其他的朋友在刚接触VBA数据透视表的时候，能少走我走过的弯路。主要还是描述性的介绍相关的语法含义，谈不上什么技巧。小弟才识不高，水平有限。有纰漏的地方还望前辈们不吝指正。书归正传，先上代码，创建数据透视表:
- ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
- strSourceData, Version:=xlPivotTableVersion10).CreatePivotTable _
- TableDestination:="输出表!R1C1", TableName:="考勤", DefaultVersion:= _
- xlPivotTableVersion10
SourceData:=后跟所要选取的数据区域。比如我想使用表"请假明细"中的A1：D100区域的数据，那么这里的strSourceData就可以替换为"请假明细!R1C1:R100C4"；(至于我为什么没有直接写"请假明细!R1C1:R100C4"，先卖个关子，在后面再讲哈O(∩_∩)O)
Version:=后跟创建数据透视表的表类型。通常.xls结尾的文件即97-03工作簿，是使用xlPivotTableVersion10，即Excel数据透视表版本10。样图如下：
xlPivotTableVersion10-1
xlPivotTableVersion10-2
xlsx及xlsm等07之后版本的，数据透视表版本就不一样了，小弟印象中07貌似是xlPivotTableVersion12，不过没有确认过。本地计算机是使用Office2010，数据透视表版本是xlPivotTableVersion14，样图如下：
xlPivotTableVersion14-1
xlPivotTableVersion14-2
版本不同，使用起来还是略有不同的。具体细节小弟在此不一一赘述了，各位看官可以自己体验一下。
我们继续说，ActiveWorkbook.PivotCaches.Create得到的是一个PivotCaches实例，后面加.CreatePivotTable是表示用这块缓存数据来建立数据透视表。 .CreatePivotTable后面跟的是创建数据透视表所需的参数：
TableDestination:=后跟生成的数据透视表的位置，假如我们要在"输出表"worksheet的A5单元格为左上角生成数据透视表，那么这个位置就可以写"输出表!R5C1"。
TableName:=后跟创建数据透视表的名称，这个很重要，如果要用宏创建多个数据表的时候，要注意这里很可能后面跟的是一个变量。Excel在创建数据透视表的时候，会检查此Workbook内所包含的所有数据表的名称，是否有重复名称，如果有重复名称的情况(即当前创建的数据透视表与其他表重名)，则会报错。
DefaultVersion:=后跟数据透视表的版本号，必须与之前创建缓存时版本号相同，例子中使用xlPivotTableVersion10。
至此，数据透视表就暂时建好了，也就是从"输出表"worksheet中，我们可以进行熟悉的拖拽了。
当然，如果"拖拽"总是重复的数据汇总处理，我们还可以对宏进行扩充，让Excel帮我们把剩下的工作也处理好。
举例来说，附件中的表格我们使用了五个字段来建立数据透视表，分别是“工号”、“姓名”、“申请日”、“缺勤时数”、“缺勤类别”。※需要注意一点的是：在对我们创建好的数据透视表进行操作的时候，一定要在输入后面的代码之前，先加上一行代码
这可是让小弟吃了不少的苦头！一开始的时候没有注意，但是后来发现(可能也跟研究素材是宏录制代码有关)，由于之后进行操作的代码都是以ActiveSheet为前提的，也就是要操作之前，先要对要操作的目标worksheet工作表进行选中，故加此代码。这样就能选中"输出表"worksheet，并对此工作表上我们刚建立好的数据透视表进行操作了。
一个小插曲，我们继续。
例子中，我们对人员的请假时间进行汇总。列字段添加“缺勤类别”- ' 添加缺勤类别字段
- With ActiveSheet.PivotTables("考勤").PivotFields("缺勤类别") '表示对于名称为"考勤"的数据透视表中名为"缺勤类别"的字段进行操作(可见之前提到的数据透视表名称唯一的重要性)
- .Orientation = xlColumnField '表示"缺勤类别"字段在数据透视表中的位置为列字段(行字段是xlRowField）
- .Position = 1 '表示该字段所在列字段的位置为第1个位置
- End With
相关解释我填写在每句语句后面注释当中了。
有了列字段，还需要行字段，怎么添加呢？我要看人员的各类别请假时间，那么，行字段自然就是人名，或者是工号了。要不两个就都一起来吧！
- ' 添加姓名、工号字段
- With ActiveSheet.PivotTables("考勤").PivotFields("姓名")
- .Orientation = xlRowField
- .Position = 1
- End With
- With ActiveSheet.PivotTables("考勤").PivotFields("工号")
- .Orientation = xlRowField
- .Position = 2
- End With
- ' 添加姓名、工号字段完成
有些朋友在使用xlPivotTableVersion14的时候，会遇到行字段“姓名”和“工号”不能再同一行显示的问题，很不美观，更不方便观看。于是我们可以在后面再加上一句：- ActiveSheet.PivotTables("考勤").RowAxisLayout xlTabularRow
- ActiveSheet.PivotTables("考勤").AddDataField ActiveSheet.PivotTables("考勤" _
- ).PivotFields("缺勤时数"), "求和：缺勤时数", xlSum
第一个参数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, 下载次数: 2438)
数据透视表说明xls.7z
(15.82 KB, 下载次数: 2315)
|
评分
-
15
查看全部评分
-
|
[复制链接]
沪公网安备 31011702000001号 沪ICP备11019229号-2
发表于 2014-8-26 12:07
楼主
