2003Excel技巧之数据透视表精华

1、数据透视表”报表101 （精华）打印-全部隐臧适用于Microsoft Office Excel 2003Microsoft Excel 2002假设您已编辑了一个大的 数据列表（例如公司每种产品的销售额）。您现在可以从这 些数据中提取一些有意义的信息并找到下列问题的答案：按区域划分，每种产品的总销售额是多少？过去的时间内哪些产品销量最好？业绩最好的销售人员是谁？要回答这些问题以及其他问题，可以创建PivotTable? 报表（自动提取、组织和汇总数据的交互表）。然后可以使用此报表分析数据、进行比较、检测模式和关系以及分析趋势。例如，您可以使用下图中的数据 .AB I CD5g83101112

2、13月nr nrn rrn; 二二二二3 RT nT 3 二二二份月月月 月二一销售人员销售能单位Dodsworth 2686 56113 3 3 3 3o o o o O0 0 0 0 02 2 2 2 2AHH口蛇 口用HB 机类rfl料机科类类一律 奶肉农饮奶饮肉肉的域部部部部部部部部区西北南东北西南南Davdlo87256890Buchanan6715265ZSuyama435&5636Suyama96623400C-aolio13232861Buchanan3571517SDodsworth675a78171Buchanan45535889西部Davolio4%3124南加S

3、uyama2666744西部Buchanan 1150 _ 234.创建数据透视表”报表，以显示当前每种产品在各销售区域的销售状况（如下图所 示）。A B C D E | F 厂 4幅额总让类型 Y -E®奶制晶 肉类农产品I汇总部部部部东北南西3 一 4-5 -635,233 39,444 330651,332 63.418 17,06230,172 27,809 58,02540.392 41110a43528/75 瓯799 39,074 36,466136,758159,111155/6。12403汇总 15乙 629 1 葩 1 1161928 131U1458。432卜列

4、各部分提供了概述并给出了有效使用数据透视表”报表的顺序步骤数据透视表”报表使用概述通过汇总和分析数据， 数据透视表”报表可以帮助您抓住关键信息。 可以控制Excel汇 总数据的方式（例如，求总和、平均值或计数），而无需输入任何公式。数据透视表”报表中，可以迅速：添加或删除数据数据透视表”报表中的信息不太充分吗？可能您还希望报表中包括各销售人员的销售额等数据。在数据透视表”报表中可以轻松添加或删除数据类别。例如，可以添加销售人员 字段，如下图所示。AcD匚11销售额总讨整销售人员jL.依料区域BuchananDavolioDodsworthSuyama ；T东部1J3213,7614,09716

5、d3 ；耳北部14JS66,48122Q49 ;E南都3,5716,71113.E506,0401了西部12 刘 6616,977兀7823,5671汇总25炳551,65532,210包 879 |重新排列布局 对数据透视表”报表的布局不满意吗？例如，您可能更愿意按 行而不是按列显示销售人员信息。利用 数据透视表”报表的交互特性，可以翻转表格: 您可以轻松地移动（或旋转）行和列以查看不同的汇总数据。例如，您可以移动 销售人员”字段，使该项向下按行显示而不是跨列显示BcDE1销售额总计.类型区域销售人员，饮料奶制品肉类农产品口东部Buchanan1,132% 136Davolio13,7612

6、2,953Ij44112,5991C IDodsworth4,097BJ139,99616 1Suyaroa16,22373557,6566,08。|东部总讨35,23339M443330&现675查看数据的子集要筛选数据以便您在某一时候能按需查看某部分数据吗？要这样做，可用 页面字段将数据透视表”报表分成若干独立的 页面”。每个页面 都包含一个在 数据透视表”报表中显示为汇总数据的数据子集。例如，可添加一个年”类别的页面字段。这样可以查看某一年度（例如2003年） 或综合所有年度的销售数据。年年 |加03,销售额总计.|类型11 一区域 捎告人一屈一奶制品肉类东部n3234%13 8

7、 5 J . J i 18 17g东剖总讨18,90210J03O17,397II北部itLBuchanan_3j5224j5621Davolio8,7259Z911 -JBuchanan Davolio DodsworthSuyama如果数据透视表”报表包含很多字段，可用页面字段来保持报表的紧凑性和易读 性。在检索某些大型外部数据库中的数据时，也可以添加并设置页面字段以限制 检索数据量。这样可以避免长时间的等待和内存问题。显示您需要的细节是否关注数据透视表”报表中的特定详细信息？您可以显示或隐藏行字段或列字段中的项目，例如特定产品或销售人员。 您也可以显示或隐藏行字段或列字段中项目的详细信息

8、。例如，在 类型”字段中，可以隐藏除饮料之外的所有产品项目。Fj销售额总计上区域梢售人员-饮料汇息 J东部Buchanan1,132lj132JDavoko13J3113,731Dodsworth4,0974,0976SuyamaI6j22316,2237东部总计电23335,233您可能会急于开始编制第一个数据透视表。但在开始之前，最好确保您的数据组织良好并且已准备就绪，如下面部分所述。- 第1步：准备源数据阅读此部分，了解在数据透视表中可以使用哪些数据及如何准备这些数据。准备好数据后，您可以使用 数据透视表和数据透视图？ ”向导创建数据透视表。向导的开始部分是选择源数据。可以选择已在Exc

9、el中输入的数据或数据库中的数据（称为外部数据源）。如果选择了外部数据源，向导将启动名为Microsoft Query 的单独程序，可以使用该程序连接到数据源并选择要显示的数据。单击下列任一源数据类型，了解如何使用这些数据类型：- Excel列表或数据库使用创建列表 命令，可以使用包含相关数据的多个连续行，也可使用指定用作数据表的多个连续行。要轻松准备 Excel数据，应考虑执行下列任一或全部操作：确保列表组织良好务必整洁！例如，确保列表的第一行包含列标签，因为Excel要在报表中将此数据用作字段名。还要确保每一列都包含类似项目，例如，一列中包含文本而另一列中包含数字值。删除任何自动的分类汇总

10、不要担心，数据透视表将为您计算分类汇总和总计。命名区域如果计划稍后添加其他数据，请确保为源数据中输入的单元格区域指定名称。这样，无论在何时向数据区域添加数据，都可以更新数据透视表，将新数据包括在内，而不必指定新区域引用。使用 高级筛选”命令要使用经过筛选的数据，指向 数据菜单中的筛选，单击高级筛选，然后单击复制到其他位置。这样可将筛选后的数据提取至其他工作表位置， 从而可以在您的数据透视表中使用这些数据。注释 不要就地筛选数据或使用 自动筛选 命令。此操作会隐藏列表中的一些数据，但数据透视表仍将包括全部数据。- 外部数据源可以使用数据库文件、文本文件或Internet数据源。要准备外部数据，可

11、能需要执行下列操作：安装必要的工具和驱动程序您可能已完全准备就绪，只需确保安装了相应的开放式数据库连接 （ODBC）驱动程序或所需的数据源驱动程序。初次使用时，将自动 安装 Microsoft Query （包括帮助和驱动程序）。然后，当您使用数据透视表和数据透视图向导”创建数据透视表时，将使用 Microsoft Query 实际地检索外部数据。如果需要，在启动向导之前检索数据如果下表包含了您使用的数据检索方法，您将无法在 数据透视表和数据透视图向导 ”中用Query来检索数据。相反，在启 动向导来选择包含外部数据的工作表范围之前，应按照以下说明将数据插入Excel工作簿。Office数据连

12、接建议使用Office数据连接（.odc）文件为您的报表检索外部数据。您可以在仅从单个数据库表中或者从OLAP数据库中检索数据时使用这些文件，这样，在您创建报表之前，您无需合并外部数据库中多个表 的数据，或是筛选数据以选择特定记录。查询文件和报表模板要使用查ttj文件（.dqy、.oqy或.rqy ）检索数据，请在Excel中打开查询文件。要使用没有包括数据透视表的报表模板（.xlt）,请打开该模板。参数查询要使用参数查询检索数据，必须首先在Microsoft Query 中创建参数查询，以便它把数据返回Excel o请注意，您不能创建参数查询来从OLAP数据库中检索源数据。有关创建参数查询的

13、详细信息，请参阅Microsoft Query中的帮助。Web查询 要使用 Web查询来检索Internet上的数据，您必须首先 创建 Web查询并将数据检索到 Excel中。要创建 Web查询，请指向数据菜 单上的导入外部数据，然后单击新建 Web查询。- 多重合并计算数据区域您可以合并与汇总几个不同Excel列表的数据。要准备来自多个 Excel列表的数据，可能需要执行下列操作：确保列表组织良好务必整洁！例如，确保列表有与您要汇总的项目相匹配的行和列名称。删除任何自动的分类汇总不要担心，数据透视表将为您计算分类汇总和总计。命名区域如果计划稍后添加其他数据，请确保为源数据中输入的单元格区域指

14、定名称。这样，无论何时向数据区域添加其他数据，都可以更新数据透视表，将 新数据包括在内，而不必指定新区域引用。- 另一个数据透视表如果计划使用同样的数据创建多个数据透视表，可以重新使用现有的数据透视表，来创建新的数据透视表，从而节省内存和磁盘空间。 此操作将把原始的和新的数据透视表链 接起来。（如果您刷新了原数据透视表的数据，新报表的数据也会刷新，反之亦然。）要准备来自其他数据透视表的数据，可能需要执行下列操作：确保两个数据透视表在同一工作簿中如果原数据透视表位于不同的工作簿，请将原报表复制到您要显示新报表的工作簿中。检查页面字段设置 您可能已在原数据透视表中更改了页面字段设置， 以便分 别检

15、索每个页面的外部数据。 在这种情况下，您需要重新设置页面字段， 以便一次为全 部项目检索外部数据。要检查设置，双击每一个页面字段，然后单击高级。注释要了解有关页面字段的其他信息，参见本文稍后部分的数据透视表解析”。- 第2步：创建数据透视表要创建数据透视表，请首先使用数据透视表和数据透视图向导该向导可帮助您指定要使用的数据及创建报表框架。然后您可使用数据透视表工具栏在该框架内排列数据。例如，您可以选择要包括的数据类别（衬以蓝色底纹）和要汇总的相关数据值（衬以黄 色底纹）。AB月份 一月 一月年度200320032003一月 一月 一月 一月 二月 二月 二月 二月二月 二月2003200320

16、03200320032003200320032003品品品品 料产制类产科制料类类 饮农奶肉衣饮期饮肉肉一品篇希售人员DavolioBiichanciriSuyama Suyama DavolioBjchananDodsworthBuchananI DavoloI SuyamaBuchanan销售融单位区，2696 5611 西部6725 6890 j北一6715 2652 |南部43s6 5636 东部9662 3400 H 麻1323史61西部1 S 3 37 5 5 25 7 9 93 6 4 48 7 9 47 18 21 OQ B 15 7 54741450234然后，告诉Excel

17、如何在数据透视表的行和列中排列数据类别。Excel创建完报表后,它将自动汇总和总计数据值。BDEZ£"1销售额总计类型区域饮料奶制品肉类农产品汇总3东部35,23339,444S34口628,675136/58.北部51,33263,41817晔267gg159,111南部30,17227.BS958,02539,074155/6。西部40,39244/10833536366129303汇总157,629174,361116.92813L014581M32要创建数据透视表，执行下列步骤：-启动数据透视表和数据透视图向导1. 打开您要创建数据透视表的工作簿。如果您的报表要基于

18、 Excel列表或数据库,请单击列表或数据库中的一个单元格。2. 在数据菜单上，单击数据透视表和数据透视图。3. 在数据透视表和数据透视图向导 ”的第1步中，请选择将要使用的数据类型，然后单击所需创建的报表类型 下的数据透视表。4. 在向导的第2步中，按照定位数据的说明进行操作。如果要为数据透视表检索外部数据，可以从 Excel帮助或 Query帮助中获得有关使用 Microsoft Query 或 Query向导的详细信息。5. 在向导的第3步中单击完成。一定义数据透视表的布局1. 将字段按钮从数据透视表字段列表窗口拖到数据透视表图表的标签区域。2. 要重新排列各字段，请在区域间拖动这些字段

19、。要删除字段，请将其拖至数据透视表之外。注释有些情况下，您可能不希望通过在工作表上直接拖动字段来手动定义数据透视在这表布局。例如，如果源数据来自大型外部数据库，手动定义布局则可能过于费时种情况下，可以使用向导定义布局。 在数据透视表和数据透视图向导 ”的第3步中单击 布局来设置数据透视表布局。数据透视表解析您可能已经注意到，Microsoft Excel使用了特定的术语来标识数据透视表的各部分。如果您尚不熟悉这些术语，请阅读此部分，迅速获得初步知识。3234骐38 13 8 5 J- J J J18 17653%工页面字段此字段来自源数据，用于数据透视表中的页面（或筛选）。例如， 年”是一个页

20、面字段，可以使用 年”字段仅显示2003年、2004年等的汇总数据。N数据字段此字段来自源数据，其中包含要汇总的值。 例如，销售总额”是一个数据字段。对于多数源数据类型，可以选择汇总数据的方式（例如，求总和、平均值或计数）。数据字段一般用于汇总数字， 但也可汇总用于文本。例如，可以对某字段中特定文本项 （例 如 是“或否”）出现的次数进行计数。3：列字段此字段来自源数据，用于数据透视表中的列。例如，类型”是一个列字段。项目 行、列或页面字段的子类别。例如，类型”字段包含了下列项目：饮料、 奶制品和肉制品。销售人员”字段包含了这些项目：Buchanan、Davolio、Dodsworth和Suy

21、ama。行字段此字段来自源数据，用于数据透视表中的行。例如 地区”和销售人员”都是行字段。数据区域数据透视表中包含汇总数据白单元格。例如单元格C5中的值汇总了2003年Buchanan在东部地区的饮料销售额。也可以说，该单元格是源数据中包含 Buchanan、饮料、东部和 2003等项目的各行的销售额汇总。-第3步：自定义数据透视表您现在已经创建了数据透视表，但这还只是开始。是否还记得有关数据的那些问题？数 据透视表不仅是静态的汇总， 还可以使用该表以交互的方式探索数据的含义。可以查看详细信息，也可进行大局观察。可以按照地区或时间段查看销售额。自定义类型由您自己确定。要自定义数据透视表，请执行

22、下列一项或多项操作：- 添加或删除数据 是否希望在数据透视表中查看多一些或少一些的数据类别？可以方便地添加或删除字 段（包括行、列或页面字段） 要添加字段，请从 数据透视表字段列表”窗口中将字段拖至数据透视表报表中的相应区 域。要删除字段，请将字段拖至数据透视表之外。注释 不得在数据透视表中直接编辑数据。而是要编辑源数据，然后刷新数据透视表- 快速重新排列布局是否需要重新组织数据透视表以获得不同的数据汇总结果？通过移动字段或重新安排字段内各项的顺序，可以快速 旋转”数据透视表。要移动字段，请将字段拖至数据透视表中相应区域。要重新安排字段内各项的顺序，将 项目拖至字段内的新位置。- 使用页面字段

23、查看数据子集数据透视表中是否包括了页面字段？如果是，该数据透视表即是由多个页面”组成的三维报表。其中每页包含了在报表中已经汇总的数据子集。要查看其他页面，请单击页面字段项旁边的箭头，然后单击列表中的项目。- 显示所需的详细信息在数据透视表中，可以查看详细信息，也可隐藏信息以获得全局认识。可以快速地：显示或隐藏行或列中的项目单击字段中的箭头，然后根据自己的需要选择或清除复选框。例如，在 类型”字段中，可以隐藏除 饮料”外的全部产品项目:全部显示）饮料二肉类 农产品确定东部总计D工二汇总1J32143213,73113J314耐由。97162233, 23335送33取消显示或隐藏项目的特定详细信

24、息 具栏上的显示详细信息或隐藏详细信息 的字段。单击字段中的项目，然后单击数据透视表 工 按钮。如果出现提示，单击希望显示其详细信息例如，对于 类型”字段中的 饮料 内容。，-项，通过显示每年销售额可以增加其详细信息的显示详细费料45 &员人甑 份展售W位域 月醺销销单区C D E姬年度三饮料饮料总计确定 取消2003 200432Dadsworth7 9 54 Q- 84 16 为乙现一总比1达刃2显示特定数据单元格的基础源数据例如，双击东部地区的饮料分类汇总,1J3Z： 131311 4097； 16223 英名31只需双击单元格即可查看其详细信息可以查看用于计算该单元格的源数据记

25、录n七月20口3 E欠料Buchanan11323326东部三月2003饮料DavolioB3342933东部J 4七月2004饮料Davolio54479069东部5六月2004饮料Dodsworth21998496东部6四月2叩3饮料Dodsworth1898917东部“月2004饮料Suyama51223299东部c一月2003饮粉Suyama7S392690东部一月2004饮料Suyama35S34183东部年度类型销售人员单位区蜂I EExcel插入分页符的可以选择在特定位置插入分页符，例如，在 符，以避免将一个月的项目分隔开来。例如, 41和行42之间，将四月的项目分隔开来,月”字

26、段中每个月开始之前插入分页 在下图中，自动分页符插入到了行 要使四月的项目打印在一起，可以ABc1年度诠部）销售颜总计类型引1区域销售人员饮料明制闾东部Buchanan1J329J366DavolioL3JS122,9531Dodsworth4,0978Suyama16衣37,35519东部总汁| +到444!北部D月份-第4步：打印数据透视表是否要打印数据透视表？如果该报表是一个多页报表，可以设置打印选项以控制报表的打印外观，如下所示：为方便浏览多页的数据透视表，可以在各页顶端自动重新打印标签。例如，在打印页面上， 可以重复行1和行2中的标签，也可重复外行字段项目 四月销售额总讨类型月份区域销售人员饮料奶制品肉类衣产品汇总四月北部Davolio5uvanid乙76891沏1,5001079127鸵北部总计9,2911,50013559南部DavolioDodswcrth2,476450450278南部总计2478一 一4531商12,明 1,861销售额汇总-第芝页为确保在每页中能打印数据透视表的逻辑区块，可以指定位置。指定将分页符放置在行 38主月总计”的正下方。AE C0E |销售额类型月份区域销售人员饮料甥制品3一月东部Suyom53,5634,35614东部总计3,56