19中文 Excel 2000 编程 24 学时教程.pdf

《19中文 Excel 2000 编程 24 学时教程.pdf》由会员分享，可在线阅读，更多相关《19中文 Excel 2000 编程 24 学时教程.pdf（7页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、下载第19学时数据透视表某些工作表拥有数百行的数据和数十个列，处理这种情况的一个方式是固定标题。但是固定标题对于分析大量的数据并没有多少帮助。 E x c e l提供了一个功能强大的工具，能够解决浏览和分析大量数据时需要解决的问题。这个学时的重点包括： 为什么需要使用数据透视表 数据透视表概念概述 录制宏来创建数据透视表 修改录制的数据透视表代码19.1 使用数据透视表E x c e l中最没有得到充分利用的数据分析特性很可能就是数据透视表。数据透视表通过把数据用三维格式表示出来的方式来对大量的数据进行总结。数据显示的格式允许用户把信息筛选到不同层次的细节。图1 9 - 1给出了一个数据透视表

2、的示例。数据透视表拥有几个特性，正是这些特性使得应用透视表来分析数据对用户形成了很大的吸引力。 内置筛选数据透视表自动地内置了筛选，从而允许用户只浏览自己关心的细节。 动态布局数据透视表具有动态布局功能，只要通过拖放数据透视表中的字段到数据透视表的其他区域，就能够轻易地改变数据的显示格式。 自动汇总报表数据不管用户决定采用什么字段作为总结，数据都能够自动汇总。用户还能够改变数据汇总的计算类型。 支持各种数据源用户能够基于各种各样的数据源创建数据透视表，这些数据源包括图19-1 图中的数据透视表用来查看和分析销售信息E x c e l程序清单、任何带有标签列的工作表区域和外部的数据库文件等等。1

3、9.2 数据透视表概述当创建数据透视表时，系统会要求用户为下面列出的四个区域选择字段： 页字段页字段是数据表中主要层次的筛选。当用户选择页字段时，一定要弄清楚到底想怎样组织自己的数据。 行字段行字段是更低一级的细节筛选，也是第二个层次的细节筛选。 列字段列字段的筛选层次跟行字段一样。 数据项数据项就是想要汇总的字段，通常比较适合用来作为数据项的字段，比如销售额、费用、库存数量等等。现在，我们来看图 1 9 - 2中给出的工作表，在这张工作表中，能够作为页字段、行字段或者列字段的有三个候选参量： Ye a r，R e g i o n和C a t e g o r y。用户只需要决定自己到底最关心的

4、是哪个参量就可以了。数据项的最佳候选参量是 S a l e s字段。162使用中文Excel 2000编程 2 4学时教程下载图19-2 区域中的数据即将用来形成数据透视表图19-3 选定了各级的筛选字段后所得到的数据透视表如果已经决定把R e g i o n作为页字段、Ye a r作为行字段、C a t e g o r y作为列字段的话，这样得到的数据透视表如图1 9 - 3所示。如果需要的话，可以拥有多个页/行/列字段，这在处理具有很多行和列的数据时非常有用，因为多个筛选字段能够提供多个筛选选项。19.3 使用代码来创建数据透视表在第1 8学时中，我们使用了宏录制器来生成创建图表的代码，现

5、在准备学习类似的内容，我们将录制一个宏来生成创建数据透视表的基本代码。打开本书的We b站点上的S a l e s . x l s文件，你将注意到这个工作簿中的工作表有四列： Ye a r，R e g i o n，C a t e g o r y和S a l e s。因为想要E x c e l把这个工作表上的数据当作数据程序清单来处理，所以工作表上的标题行采用了与其他数据行不同的字体，以示区别。我们准备创建一个基于数据清单的数据透视表，所以应该选择“ Microsoft Excel数据清单或数据库”选项按钮。因为正在创建数据透视表，所以“数据透视表”选项按钮也应该选中。单击“下一步”按钮以进入到

6、数据透视表创建中的下一步，确认选中的数据区域是 A1: D37后，单击“下一步”按钮。因为准备把新创建的数据透视表放置到新工作表上，所以选择“新建工作表”选项按钮，然后单击“完成”按钮。新建的工作表就添加到了工作簿中，而且一个空数据透视表也显示出来了，如图 1 9 - 4所示。从“数据透视表”工具栏上把 Ye a r字段拖到数据透视表上标记有“请将页字段拖至此处”的地方，接下来，从“数据透视表”工具栏上把 R e g i o n字段拖到数据透视表上标记有“请将行字段拖至此处”的地方，再从“数据透视表”工具栏上把 C a t e g o r y字段拖到数据透视表上标记有“请将列字段拖至此处”的地

7、方，创建数据透视表布局最后要做的一件事情是从“数据透视表”工具栏上把 S a l e s字段拖到数据透视表上标记有“请将数据项拖至此处”的地方。完成后的数据透视表如图1 9 - 5所示，现在停止录制宏。第19学时 数据透视表使用163下载图19-4 当向导完成时，就生成了一个空数据透视表，现在准备创建布局了在查看录制的代码之前，先来试验一下数据透视表的功能。 Ye a r字段当前显示的是“全部” ，在这个字段旁边，有一个带有向下箭头的按钮，单击这个按钮，然后选择1 9 9 7，单击“确定”按钮，现在数据透视表中就只显示1 9 9 7年的销售额信息。单击数据透视表上部C a t e g o r

8、y字段旁边的带有向下箭头的按钮，取消选中 A u t o m o t i v e和L a rge Appliance复选框，然后单击“确定”按钮，现在数据透视表中就只显示 1 9 9 7年的Sporting Goods的数字。再次单击C a t e g o r y字段旁边的向下箭头按钮，选中 A u t o m o t i v e和L a rge Appliance复选框，单击“确定”按钮，再从Ye a r字段中选择“全部” ，现在所有的信息都再次显示出来了。19.4 查看录制的代码按下A l t + F 11切换到Visual Basic编辑器中，从“工程资源管理器”中打开“模块”文件夹，然

9、后打开M o d u l e s，就可以看到录制的宏代码程序清单。程序清单 1 9 - 1给出了完整的宏过程：程序清单19-1 MyPivotTa b l e过程代码1: Sub MyPivotTable ()2: ActiveWo r k b o o k . P i v o t C a c h e s . A d d ( S o u r c e Type:=xlDatabase,SourceData:= _3: Sheet1!R1C1:R37C4).CreatePivotTable _TableDestination:= ,TableName:= _4: PivotTa b l e 6 5:

10、 ActiveSheet.PivotTa b l e Wizard TableDestination:=ActiveSheet.Cells(3, 1)6: ActiveSheet.Cells(3,1).Select7: ActiveSheet.PivotTa b l e s ( P i v o t Table6).SmallGrid = False8: With ActiveSheet.PivotTa b l e s ( P i v o t Ta b l e 6 ) . P i v o t F i e l d s ( Ye a r )9: .Orientation = xlPageField1

11、0: .Position = 111: End Wi t h12: With ActiveSheet.PivotTa b l e s ( P i v o t Ta b l e 6 ) . P i v o t F i e l d s ( R e g i o n )13: .Orientation = xlRowField14: .Position = 115: End Wi t h16: With ActiveSheet.PivotTa b l e s ( P i v o t Ta b l e 6 ) . P i v o t F i e l d s ( C a t e g o r y )164使

12、用中文Excel 2000编程 2 4学时教程下载图19-5 在数据透视表中放置S a l e s字段后 ，数据就显示出来了17: .Orientation = xlColumnField18: .Position = 119: End Wi t h20: With ActiveSheet.PivotTa b l e s ( P i v o t Ta b l e 6 ) . P i v o t F i e l d s ( S a l e s )21: .Orientation = xlDataField22: .Position = 123: End Wi t h24: End Sub这个宏过

13、程中的第一条语句执行了 P i v o t C a c h e s集合上的A d d方法，有趣的是这条语句实际上执行了两个方法，同A d d 方法一起执行的还有C r e a t e P i v o t Ta b l e方法。注意Ta b l e D e s t i n a t i o n参数设置为空字符串，这就告诉E x c e l是在新工作表上创建数据透视表。A c t i v e Workbook. PivotCaches. Add (SourceType := xlDatabase, SourceData := _Sheet1!R1C1: R37C4). CreatePivotTabl

14、e TableDestination := , TableName := _ P i v o t Ta b l e 1 新工作表和数据透视表创建以后，代码设置了一系列的属性：ActiveSheet. PivotTa b l e Wizard TableDestination := ActiveSheet. Cells(3, 1)ActiveSheet. Cells(3, 1). SelectActiveSheet. PivotTables (PivotTable6). SmallGrid = False过程中其余的代码跟设置数据透视表的字段有关：With ActiveSheet.PivotTa

15、 b l e s ( P i v o t Ta b l e 6 ) . P i v o t F i e l d s ( Ye a r ).Orientation = xlPageField.Position = 1End Wi t hWith ActiveSheet.PivotTa b l e s ( P i v o t Ta b l e 6 ) . P i v o t F i e l d s ( R e g i o n ).Orientation = xlRowField.Position = 1End Wi t hWith ActiveSheet.PivotTa b l e s ( P i

16、 v o t Ta b l e 6 ) . P i v o t F i e l d s ( C a t e g o r y ).Orientation = xlColumnField.Position = 1End Wi t hWith ActiveSheet.PivotTa b l e s ( P i v o t Ta b l e 6 ) . P i v o t F i e l d s ( S a l e s ).Orientation = xlDataField.Position = 1 End Wi t h如果准备再次运行这个宏过程的话，将得到一个错误，这是因为这个录制的宏在数据透视表创

17、建的时候传递了一个值给 Ta b l e N a m e参数。为了使这个过程更具通用性，请按照程序清单1 9 - 2对代码进行修改（要修改的代码用粗体标出） 。程序清单19-2 MyPivotTa b l e过程的通用版本1: Sub MyPivotTable ()2: Dim ptSales As PivotTa b l e3 :4: Set ptSales = ActiveWo r k b o o k . P i v o t C a c h e s . A d d ( S o u r c e Type:=xlDatabase, _SourceData:= _5: S h e e t 1 !

18、 R 1 C 1 : R 3 7 C 4) . C r e a t e P i v o t Ta b l e ( TableDestination:= )6: ActiveSheet.PivotTa b l e Wizard Ta b l e D e s t i n a t i o n : = A c t i v e S h e e t . C e l l s ( 3 , 1 )第19学时 数据透视表使用165下载7: ActiveSheet.Cells(3,1).Select8: p t S a l e s .SmallGrid = False9: With ptSales.PivotFie

19、lds(Ye a r )10: .Orientation = xlPageField11: .Position = 112: End Wi t h13: With p t S a l e s. P i v o t F i e l d s ( R e g i o n )14: .Orientation = xlRowField15: .Position = 116: End Wi t h17: With p t S a l e s. P i v o t F i e l d s ( C a t e g o r y )18: .Orientation = xlColumnField19: .Posi

20、tion = 120: End Wi t h21: With p t S a l e s. P i v o t F i e l d s ( S a l e s )22: .Orientation = xlDataField23: .Position = 124: End Wi t h25: End Sub现在，在创建新数据透视表时，就不用担心名称问题了。如果现在已经切换到了工作簿中，那么就可以以任意次数运行这个过程了。19.5 学时小结现在，你已经知道怎样使用数据透视表来更简单地处理大量数据了。为了进一步学习V B A，你录制了一个用来创建数据透视表的宏，接着对录制的宏代码进行了修改，以便能够

21、多次使用这个宏过程。19.6 专家答疑问题：当改变用来创建数据透视表的数据时，数据透视表能够自动刷新吗？解答：不能。用户可以通过单击“数据透视表”工具栏上的“刷新”按钮，也可以通过执行R e f r e s h Ta b l e方法来刷新数据透视表。问题：如果每次创建某个数据透视表时都想使用同一个名称的话，需要怎样做呢？解答：处理这种情况的最简单方式是在用户退出工作簿时删除数据透视表所驻留的工作表，然后在工作簿的 A u t o _ O p e n过程中放置代码来创建数据透视表，以便当用户打开工作簿时就能够生成数据透视表。问题：可以使用P i v o t Ta b l e s集合上的A d d

22、方法来创建数据透视表对象，以取代结合使用P i v o t C a c h e s集合上的C r e a t e P i v o t Ta b l e方法和A d d方法来创建数据透视表吗？解答：可以。首先执行P i v o t C a c h e s集合上的A d d方法，然后把得到的对象作为 P i v o t Ta b l e s集合上A d d方法中P i v o t C a c h e参数的值传递过去就可以了。19.7 课外作业思考题和练习题是为了使你能够进一步地理解所学内容，答案请参考附录。166使用中文Excel 2000编程 2 4学时教程下载19.7.1 思考题1）判断题：只

23、能够创建基于E x c e l数据清单或区域的数据透视表。2）列举出创建数据透视表时使用到的两个集合的名称。3）P i v o t F i e l d对象的哪个属性分配了字段的位置（就是页、行、列字段和数据项的位置）？4）判断题：只能够拥有一个行字段。5）列举出数据透视表四个区域的名称。6）哪个方法创建了实际的数据透视报表？7）判断题：采用宏录制器可以录制创建数据透视表的处理过程。19.7.2 练习题打开本书We b站点上的I n S t o c k工作簿，开始录制一个命名为 I n S t o c k P i v o t的宏。以图1 9 - 6为参照，创建一个数据透视表，选择 H o u s e w a r e s作为D e p t .，选择A t l a n t a作为L o c a t i o n，停止录制宏。修改录制的代码，使代码能够把数据透视表命名为 I n s t o c k，并且能够删除工作簿中除Stock Info工作表之外的所有其他工作表。第19学时 数据透视表使用167下载图19-6 I n S t o c k P i v o t数据透视表示例