点击查看博主技术栈
http://club.excelhome.net/thread-1147804-1-1.html
小弟不才,初来乍到EH论坛...在学习VBA学习E表的过程中,对于数据透视表的操作,这两天处处碰壁。不过在从网上查询资料解决问题之后,也想把这次遇到的问题总结下来,分享一下心得。希望能以此使其他的朋友在刚接触VBA数据透视表的时候,能少走我走过的弯路。主要还是描述性的介绍相关的语法含义,谈不上什么技巧。小弟才识不高,水平有限。有纰漏的地方还望前辈们不吝指正。书归正传,先上代码,创建数据透视表:
- ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
- strSourceData, Version:=xlPivotTableVersion10).CreatePivotTable _
- TableDestination:="输出表!R1C1", TableName:="考勤", DefaultVersion:= _
- 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帮我们把剩下的工作也处理好。
举例来说,附件中的表格我们使用了五个字段来建立数据透视表,分别是“工号”、“姓名”、“申请日”、“缺勤时数”、“缺勤类别”。※需要注意一点的是:在对我们创建好的数据透视表进行操作的时候,一定要在输入后面的代码之前,先加上一行代码
- Sheets("输出表").Activate
这可是让小弟吃了不少的苦头!一开始的时候没有注意,但是后来发现(可能也跟研究素材是宏录制代码有关),由于之后进行操作的代码都是以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
其中,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)