2003Excel技巧之数据透视表.docx

《2003Excel技巧之数据透视表.docx》由会员分享，可在线阅读，更多相关《2003Excel技巧之数据透视表.docx（9页珍藏版）》请在三一办公上搜索。

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

2、 二月 二月 二月 二月年度200320032003200320032003200320032003200320032003类型品品品品品 制料产制类产料制料类类 奶饮农奶肉农饮奶饮肉肉农产品销售人员 Dodsworth Davolio Buchanan 5uyama 5uyama Davolio Buchanan Dodsworth Buchanan Davolio 5uyamaBuchanan销售额2686 8725 6715 435696621328357167584953492326661450单位56116890265256363400286151787817588912474423

3、4G区域10 r-:- r- r-Tz- r- r- r- 新部新部哥新部Bfsf部ar 西北南东北西南南西南西西部_.创建“数据透视表”报表，以显示当前每种产品在各销售区域的销售状况（如下图所示）。ABCDEF1销售额总计类型2区域 F饮料奶制品肉类农产品汇总335,2333%啊4330628,67513658451,83263,41817,06226,799159,111530,17240,39227,88958应S3% 074 36,46612903644,1108351汇总15729174,8611169明1311458032下列各部分提供了概述并给出了有效使用“数据透视表”报表的顺序

4、步骤。“数据透视表”报表使用概述通过汇总和分析数据，“数据透视表”报表可以帮助您抓住关键信息。可以控制Excel汇总数据的方式（例如，求 总和、平均值或计数），而无需输入任何公式。“数据透视表”报表中，可以迅速：添加或删除数据“数据透视表”报表中的信息不太充分吗？可能您还希望报表中包括各销售人员的销售 额等数据。在“数据透视表”报表中可以轻松添加或删除数据类别。例如，可以添加销售人员字段，如下图所示。重新排列布局对“数据透视表”报表的布局不满意吗？例如，您可能更愿意按行而不是按列显示销售人 员信息。利用“数据透视表”报表的交互特性，可以翻转表格：您可以轻松地移动（或旋转）行和列以查看不同的 汇

5、总数据。例如，您可以移动“销售人员”字段，使该项向下按行显示而不是跨列显示。查看数据的子集 要筛选数据以便您在某一时候能按需查看某部分数据吗？要这样做，可用页面字段 将“数据透视表”报表分成若干独立的“页面”。每个页面都包含一个在“数据透视表”报表中显示为汇总数据的数据子 集。例如，可添加一个“年”类别的页面字段。这样可以查看某一年度（例如2003年）或综合所有年度的销售 数据。ABcDE1年度200323销售祯总计类型 E4区域销售人员饮料奶制品肉类5东部Buchanan1/321691L4416Davolio8,33414,4747Dodsworth1,89826505uyama7,538

6、4,3569东部总计18,90218,830179710北部Buchanan3,5224,56211Davolio8,7259,291如果“数据透视表”报表包含很多字段，可用页面字段来保持报表的紧凑性和易读性。在检索某些大型外部 数据库中的数据时，也可以添加并设置页面字段以限制检索数据量。这样可以避免长时间的等待和内存问 题。显示您需要的细节是否关注“数据透视表”报表中的特定详细信息？您可以显示或隐藏行字段或列字段 中的项目，例如特定产品或销售人员。您也可以显示或隐藏行字段或列字段中项目的详细信息。例如，在“类型”字段中，可以隐藏除饮料之外的所有产品项目。ABCD1销售额总计类型2区域销售人员

7、巳饮料汇总3东部Buchanan1,1321,1324Davolio13,78113,7815Dodsworth4,0974,09765uyama16,22316,2237东部总计35,23335,233您可能会急于开始编制第一个数据透视表。但在开始之前，最好确保您的数据组织良好并且已准备就绪，如下面 部分所述。第1步：准备源数据阅读此部分，了解在数据透视表中可以使用哪些数据及如何准备这些数据。准备好数据后，您可以使用“数据透视 表和数据透视图”向导创建数据透视表。向导的开始部分是选择源数据。可以选择已在Excel中输入的数据或数据库中的数据（称为外部数据源）。如 果选择了外部数据源，向导将启

8、动名为Microsoft Query的单独程序，可以使用该程序连接到数据源并选择要显 示的数据。单击下列任一源数据类型，了解如何使用这些数据类型:Excel列表或数据库使用创建列表命令，可以使用包含相关数据的多个连续行，也可使用指定用作数据表的多个连续行。要轻松准备 Excel数据，应考虑执行下列任一或全部操作：确保列表组织良好 务必整洁！例如，确保列表的第一行包含列标签，因为Excel要在报表中将此数据 用作字段名。还要确保每一列都包含类似项目，例如，一列中包含文本而另一列中包含数字值。删除任何自动的分类汇总 不要担心，数据透视表将为您计算分类汇总和总计。命名区域 如果计划稍后添加其他数据，

9、请确保为源数据中输入的单元格区域指定名称。这样，无论 在何时向数据区域添加数据，都可以更新数据透视表，将新数据包括在内，而不必指定新区域引用。使用“高级筛选”命令 要使用经过筛选的数据，指向数据菜单中的筛选，单击高级筛选，然后单击复制 到其他位置。这样可将筛选后的数据提取至其他工作表位置，从而可以在您的数据透视表中使用这些数据。注释 不要就地筛选数据或使用自动筛选命令。此操作会隐藏列表中的一些数据，但数据透视表仍将包括全部数据。外部数据源 可以使用数据库文件、文本文件或Internet数据源。要准备外部数据，可能需要执行下列操作:安装必要的工具和驱动程序您可能已完全准备就绪，只需确保安装了相应

10、的开放式数据库连接 （ODBC）驱动程序或所需的数据源驱动程序。初次使用时，将自动安装Microsoft Query（包括帮助和驱动程序）。 然后，当您使用“数据透视表和数据透视图向导”创建数据透视表时，将使用Microsoft Query实际地检索外部数 据。如果需要，在启动向导之前检索数据 如果下表包含了您使用的数据检索方法，您将无法在“数据透视表 和数据透视图向导”中用Query来检索数据。相反，在启动向导来选择包含外部数据的工作表范围之前，应按照 以下说明将数据插入Excel工作簿。 Office数据连接建议使用Office数据连接（.ode）文件为您的报表检索外部数据。您可以在仅从单

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

12、检索Internet上的数据，您必须首先创建Web查询并将数据 检索到Excel中。要创建Web查询，请指向数据菜单上的导入外部数据，然后单击新建Web查询。多重合并计算数据区域 您可以合并与汇总几个不同Excel列表的数据。要准备来自多个Excel列表的数据，可能需要执行下列操作:确保列表组织良好 务必整洁！例如，确保列表有与您要汇总的项目相匹配的行和列名称。删除任何自动的分类汇总 不要担心，数据透视表将为您计算分类汇总和总计。命名区域 如果计划稍后添加其他数据，请确保为源数据中输入的单元格区域指定名称。这样，无论 何时向数据区域添加其他数据，都可以更新数据透视表，将新数据包括在内，而不必指

13、定新区域引用。另一个数据透视表 如果计划使用同样的数据创建多个数据透视表，可以重新使用现有的数据透视表，来创建新的数据透视表，从而 节省内存和磁盘空间。此操作将把原始的和新的数据透视表链接起来。（如果您刷新了原数据透视表的数据，新 报表的数据也会刷新，反之亦然。） 要准备来自其他数据透视表的数据，可能需要执行下列操作:确保两个数据透视表在同一工作簿中 如果原数据透视表位于不同的工作簿，请将原报表复制到您要显 示新报表的工作簿中。检查页面字段设置您可能已在原数据透视表中更改了页面字段设置，以便分别检索每个页面的外部数 据。在这种情况下，您需要重新设置页面字段，以便一次为全部项目检索外部数据。要检

14、查设置，双击每一个页 面字段，然后单击高级。注释要了解有关页面字段的其他信息，参见本文稍后部分的“数据透视表解析”。第2步：创建数据透视表要创建数据透视表，请首先使用“数据透视表和数据透视图向导”。该向导可帮助您指定要使用的数据及创建报表 框架。然后您可使用数据透视表工具栏在该框架内排列数据。例如，您可以选择要包括的数据类别（衬以蓝色底纹）和要汇总的相关数据值（衬以黄色底纹）。ABCD1月陪年度类型销售人员2一月2003奶制品Dodsworth3一月2003饮料Davolio4一月2003农产品Buchanan5一月2003奶制品5uyama6一月2003肉类5uyama1一月2003农产品D

15、avolio0二月2003饮料Buchanan9二月2003奶制品Dodsworth10二月2003饮料Buchanan11二月2003肉类Davolio12二月2003肉类5uyama13二月2003农产品Buchanan268687256715435696621328357167584953492326661450FG单位区域5611西部6890北部2652南部5636东部3400北部2861西部5178南部7817南部5889西部124南部744西部234西部然后，告诉Excel如何在数据透视表的行和列中排列数据类别。Excel创建完报表后，它将自动汇总和总计数据 值。ABCDEF1销售

16、额总计类型2区域饮料奶制品肉类农产品汇总3矛部35,23333/0628,675136,7584七部51,83263,418176226,799159J115南部30,17227,88958,0253% 074155,1606西部40,39244,11083536,466124031汇总157,629174,86111628131,01458032要创建数据透视表，执行下列步骤：启动数据透视表和数据透视图向导1. 打开您要创建数据透视表的工作簿。如果您的报表要基于Excel列表或数据库，请单击列表或数据库中 的一个单元格。2. 在数据菜单上，单击数据透视表和数据透视图。3. 在“数据透视表和数

17、据透视图向导”的第1步中，请选择将要使用的数据类型，然后单击所需创建的报表 类型下的数据透视表。4. 在向导的第2步中，按照定位数据的说明进行操作。如果要为数据透视表检索外部数据，可以从Excel帮 助或Query帮助中获得有关使用Microsoft Query或Query向导的详细信息。5. 在向导的第3步中单击完成。定义数据透视表的布局1. 将字段按钮从数据透视表字段列表窗口拖到数据透视表图表的标签区域。2. 要重新排列各字段，请在区域间拖动这些字段。要删除字段，请将其拖至数据透视表之外。注释 有些情况下，您可能不希望通过在工作表上直接拖动字段来手动定义数据透视表布局。例如，如果源数据来自

18、大型外部数据库，手动定义布局则可能过于费时。在这种情况下，可以使用向导定义布局。在数据透视表和 数据透视图向导”的第3步中单击布局来设置数据透视表布局。数据透视表解析您可能已经注意到，Microsoft Excel使用了特定的术语来标识数据透视表的各部分。如果您尚不熟悉这些术语， 请阅读此部分，迅速获得初步知识。:3/ JABcDE1三度200323销售额总计类型4销售大员F饮料奶制品肉类5东部 、Buchanan1,13216,1916Davolio8,33414,4741,4411Dodsworth1,89805uyama7,5384,3562659东部总计18,90218,83017,8

19、9710北部Buchanan3,5224,56211Davolio8,725% 291II1页面字段 此字段来自源数据，用于数据透视表中的页面（或筛选）。例如，“年”是一个页面字段，可以使用 “年”字段仅显示2003年、2004年等的汇总数据。?数据字段 此字段来自源数据，其中包含要汇总的值。例如，“销售总额”是一个数据字段。对于多数源数据类型，可以选择汇总数据的方式（例如，求总和、平均值或计数）数据字段一般用于汇总数字， 但也可汇总用于文本。例如，可以对某字段中特定文本项（例如“是”或“否”）出现的次数进行计数。3列字段此字段来自源数据，用于数据透视表中的列。例如，“类型”是一个列字段。项目

20、 行、列或页面字段的子类别。例如，“类型”字段包含了下列项目：饮料、奶制品和肉制品。“销售人员” 字段包含了这些项目：Buchanan、Davolio、Dodsworth 和 Suyama。行字段此字段来自源数据，用于数据透视表中的行。例如“地区”和“销售人员”都是行字段。数据区域 数据透视表中包含汇总数据的单元格。例如单元格C5中的值汇总了 2003年Buchanan在东 部地区的饮料销售额。也可以说，该单元格是源数据中包含Buchanan、饮料、东部和2003等项目的各行的销 售额汇总。第3步：自定义数据透视表您现在已经创建了数据透视表，但这还只是开始。是否还记得有关数据的那些问题？数据透

21、视表不仅是静态的汇 总，还可以使用该表以交互的方式探索数据的含义。可以查看详细信息，也可进行大局观察。可以按照地区或时 间段查看销售额。自定义类型由您自己确定。要自定义数据透视表，请执行下列一项或多项操作：添加或删除数据是否希望在数据透视表中查看多一些或少一些的数据类别？可以方便地添加或删除字段（包括行、列或页面字段）要添加字段，请从“数据透视表字段列表”窗口中将字段拖至数据透视表报表中的相应区域。要删除字段，请将字 段拖至数据透视表之外。Q注释不得在数据透视表中直接编辑数据。而是要编辑源数据，然后刷新数据透视表。快速重新排列布局是否需要重新组织数据透视表以获得不同的数据汇总结果？通过移动字段

22、或重新安排字段内各项的顺序，可以快 速“旋转”数据透视表。要移动字段，请将字段拖至数据透视表中相应区域。要重新安排字段内各项的顺序，将项目拖至字段内的新位置。使用页面字段查看数据子集数据透视表中是否包括了页面字段？如果是，该数据透视表即是由多个页面”组成的三维报表。其中每页包含了 在报表中已经汇总的数据子集。要查看其他页面，请单击页面字段项旁边的箭头，然后单击列表中的项目。显示所需的详细信息 在数据透视表中，可以查看详细信息，也可隐藏信息以获得全局认识。可以快速地:显示或隐藏行或列中的项目 单击字段中的箭头，然后根据自己的需要选择或清除复选框。例如，在“类型”字段中，可以隐藏除“饮料”外的全部

23、产品项目：显示或隐藏项目的特定详细信息单击字段中的项目，然后单击数据透视表工具栏上的显示详细信息或隐藏详细信息按钮。如果出现提示，单击希望显示其详细信息的字段。例如，对于“类型”字段中的“饮料”项，通过显示每年销售额可以增加其详细信息的内容。显示特定数据单元格的基础源数据 只需双击单元格即可查看其详细信息。例如，双击东部地区的饮料分类汇总，可以查看用于计算该单元格的源数据记录。ABcD 11年度筐部）123销售额总计类型 14区域T销售人员-饮料奶制品5东部BuchananDavolio Dodsworth 5uyama132 1381 4,097 16j2239,13622,95375567

24、09东部总计3彖3339,444ABCDEFG1月时年度类型销售人员销售额单位区域2七月2003饮料Buchanan11323326末部3三月2003饮料Davolio83342933东部4七月2004诙料Davolio54479069东部5六月2004饮料Dodsworth21998496末部6四月2003饮料Dodsworth1898817东部7十月2004饮料5uyama51223299末部8十月2003饮料5uyama75382898东部9一月2004饮料5uyama35634183东部第4步：打印数据透视表 是否要打印数据透视表？如果该报表是一个多页报表，可以设置打印选项以控制报表的

25、打印外观，如下所示:为方便浏览多页的数据透视表，可以在各页顶端自动重新打印标签。例如，在打印页面上，可以重复行1和行2中的标签，也可重复外行字段项目“四月”。销售额汇总-第页销售额总计类型月份区域销售人员饮料奶制品肉类农产品汇总四月北部Davolio5uyama2J68您11,500107912J68北部总计2768您11,5001359南部DavolioDodsworth孕78450450278南部总计1J2784501,8612,9281,861为确保在每页中能打印数据透视表的逻辑区块，可以指定Excel插入分页符的位置。可以选择在特定位置插入分页符，例如，在“月”字段中每个月开始之前插入

26、分页符，以避免将一个月的项 目分隔开来。例如，在下图中，自动分页符插入到了行41和行42之间，将四月的项目分隔开来。要使 四月的项目打印在一起，可以指定将分页符放置在行38 “三月总计”的正下方。ABCDE1销售甄类型2月份区域销售人员饮料奶制品3一月东部5uyama3633564东部总计3633565北部Buchanan55946Davolio8257Dodsworth您138三月总计25j47539四月东部 Buchanan% 13640Dodsworth19841廊部总计皿.9,13642北部 iDavolio9,29143|5uyama276844北部总计2J68145南部Davoli

27、o46Dodsworth237847南部总计278打印之前，执行下列步骤以指定报表的打印区域：1. 在数据透视表内单击。2. 在数据透视表工具栏中，单击数据透视表，指向选择，然后单击整个表格。3. 在文件菜单中，指向打印区域，然后单击设置打印区域。要设置数据透视表的打印选项，请执行下列任一或所有步骤：在每个打印页面上重复行标签1. 在文件菜单中，单击页面设置，然后单击工作表选项卡。确保顶端标题行框和左端标题列框是空白框，然 后单击确定。2. 在数据透视表内单击。3. 在数据透视表工具栏中单击数据透视表，然后单击表格选项。4. 在格式选项下选择设置打印标题复选框。在每个打印页面上重复外行字段项目

28、1. 在数据透视表内单击。2. 在数据透视表工具栏中单击数据透视表，然后单击表格选项。3. 在格式选项下选择在每一打印页上重复项目复选框。在单页上打印数据透视表的各部分1. 双击要在单页上打印其项目的外行字段的标题。例如，要在每类产品之间插入自动分页符，双击类型”行 的标题。2. 在数据透视表字段对话框中单击布局。注释如果外行字段中仅有一项，布局将不可用。3. 在打印选项下选择每项后面插入分页符复选框，然后单击两次确定。4. 在视图菜单中，单击分页预览，然后根据需要调整自动分页。可以通过拖动来移动分页符，或按DELETE 键来删除分页符。提示要使所打印的数据透视表易于浏览，在数据透视表工具栏中单击设置报表格式按钮，从而快速切换 为缩进格式。使用这种格式，各行字段的数据都会缩进（类似于文字大纲），所有数据字段的汇总数据显 示在一列中。打印选项设置完成之后，可以单击文件菜单上的打印预览，以预览打印的报表。准备好打印报表之后，单击打印。