Excel数据透视表技巧说明.docx

Excel数据透视表技巧说明.docx

《Excel数据透视表技巧说明.docx》由会员分享，可在线阅读，更多相关《Excel数据透视表技巧说明.docx（15页珍藏版）》请在冰豆网上搜索。

Excel数据透视表技巧说明

数据透视表”报表101

打印

全部显示

全部隐藏

适用于

MicrosoftOfficeExcel2003

MicrosoftExcel2002

假设您已编辑了一个大的数据（列表:

包含相关数据的一系列行，或使用“创建列表”命令作为数据表指定给函数的一系列行。

）列表（例如公司每种产品的销售额）。

您现在可以从这些数据中提取一些有意义的信息并找到下列问题的答案：

∙按区域划分，每种产品的总销售额是多少？

∙过去的时间内哪些产品销量最好？

∙业绩最好的销售人员是谁？

要回答这些问题以及其他问题，可以创建PivotTable®报表（自动提取、组织和汇总数据的交互表）。

然后可以使用此报表分析数据、进行比较、检测模式和关系以及分析趋势。

例如，您可以使用下图中的数据...

...创建“数据透视表”报表，以显示当前每种产品在各销售区域的销售状况（如下图所示）。

下列各部分提供了概述并给出了有效使用“数据透视表”报表的顺序步骤。

“数据透视表”报表使用概述    

通过汇总和分析数据，“数据透视表”报表可以帮助您抓住关键信息。

可以控制Excel汇总数据的方式（例如，求总和、平均值或计数），而无需输入任何公式。

“数据透视表”报表中，可以迅速：

∙添加或删除数据    “数据透视表”报表中的信息不太充分吗？

可能您还希望报表中包括各销售人员的销售额等数据。

在“数据透视表”报表中可以轻松添加或删除数据类别。

例如，可以添加销售人员字段（域:

存储在表中的一类数据，例如，姓氏或订货量。

当Query在其“数据”窗格中显示结果集时，域表示为列。

），如下图所示。

∙重新排列布局    对“数据透视表”报表的布局不满意吗？

例如，您可能更愿意按行而不是按列显示销售人员信息。

利用“数据透视表”报表的交互特性，可以翻转表格：

您可以轻松地移动（或旋转）行和列以查看不同的汇总数据。

例如，您可以移动“销售人员”字段，使该项向下按行显示而不是跨列显示。

∙查看数据的子集    要筛选（筛选:

只显示数据清单中满足条件的行。

可用“自动筛选”命令显示与一个或多个指定值、计算值或条件相匹配的行。

）数据以便您在某一时候能按需查看某部分数据吗？

要这样做，可用页面字段（页字段:

在数据透视表或数据透视图中指定为页方向的字段。

在页字段中，既可以显示所有项的汇总，也可以一次显示一个项，而筛选掉所有其他项的数据。

）将“数据透视表”报表分成若干独立的“页面”。

每个页面都包含一个在“数据透视表”报表中显示为汇总数据的数据子集。

例如，可添加一个“年”类别的页面字段。

这样可以查看某一年度（例如2003年）或综合所有年度的销售数据。

如果“数据透视表”报表包含很多字段，可用页面字段来保持报表的紧凑性和易读性。

在检索某些大型外部数据库中的数据时，也可以添加并设置页面字段以限制检索数据量。

这样可以避免长时间的等待和内存问题。

∙显示您需要的细节    是否关注“数据透视表”报表中的特定详细信息？

您可以显示或隐藏行字段或列字段中的项目，例如特定产品或销售人员。

您也可以显示或隐藏行字段或列字段中项目的详细信息。

例如，在“类型”字段中，可以隐藏除饮料之外的所有产品项目。

您可能会急于开始编制第一个数据透视表。

但在开始之前，最好确保您的数据组织良好并且已准备就绪，如下面部分所述。

第1步：

准备源数据    

阅读此部分，了解在数据透视表中可以使用哪些数据及如何准备这些数据。

准备好数据后，您可以使用“数据透视表和数据透视图®”向导创建数据透视表。

向导的开始部分是选择源数据。

可以选择已在Excel中输入的数据或数据库中的数据（称为外部数据源）。

如果选择了外部数据源，向导将启动名为MicrosoftQuery的单独程序，可以使用该程序连接到数据源并选择要显示的数据。

单击下列任一源数据类型，了解如何使用这些数据类型：

Excel列表或数据库

使用创建列表命令，可以使用包含相关数据的多个连续行，也可使用指定用作数据表的多个连续行。

要轻松准备Excel数据，应考虑执行下列任一或全部操作：

∙确保列表组织良好    务必整洁！

例如，确保列表的第一行包含列标签，因为Excel要在报表中将此数据用作字段名。

还要确保每一列都包含类似项目，例如，一列中包含文本而另一列中包含数字值。

∙删除任何自动的分类汇总    不要担心，数据透视表将为您计算分类汇总和总计。

∙命名区域    如果计划稍后添加其他数据，请确保为源数据（区域:

工作表上的两个或多个单元格。

区域中的单元格可以相邻或不相邻。

）中输入的单元格区域（源数据:

用于创建数据透视表或数据透视图的数据清单或表。

源数据可以来自Excel数据清单或区域、外部数据库或多维数据集，或者另一张数据透视表。

）指定名称。

这样，无论在何时向数据区域添加数据，都可以更新数据透视表，将新数据包括在内，而不必指定新区域引用。

∙使用“高级筛选”命令    要使用经过筛选的数据，指向数据菜单中的筛选，单击高级筛选，然后单击复制到其他位置。

这样可将筛选后的数据提取至其他工作表位置，从而可以在您的数据透视表中使用这些数据。

 注释   不要就地筛选数据或使用自动筛选命令。

此操作会隐藏列表中的一些数据，但数据透视表仍将包括全部数据。

外部数据源

可以使用数据库文件、文本文件或Internet数据源。

要准备外部数据，可能需要执行下列操作：

∙安装必要的工具和驱动程序    您可能已完全准备就绪，只需确保安装了相应的开放式数据库连接（ODBC）驱动程序或所需的数据源驱动程序。

初次使用时，将自动安装MicrosoftQuery（包括帮助和驱动程序）。

然后，当您使用“数据透视表和数据透视图向导”创建数据透视表时，将使用MicrosoftQuery实际地检索外部数据。

∙如果需要，在启动向导之前检索数据    如果下表包含了您使用的数据检索方法，您将无法在“数据透视表和数据透视图向导”中用Query来检索数据。

相反，在启动向导来选择包含外部数据的工作表范围之前，应按照以下说明将数据插入Excel工作簿。

Office数据连接    建议使用Office数据连接（.odc）文件为您的报表检索外部数据。

您可以在仅从单个数据库表中或者从OLAP数据库中检索数据时使用这些文件，这样，在您创建报表之前，您无需合并外部数据库中多个表的数据，或是筛选数据以选择特定记录。

查询文件和报表模板    要使用查询文件（.dqy、.oqy或.rqy）检索数据，请在Excel中打开查询文件。

要使用没有包括数据透视表的报表模板（.xlt），请打开该模板。

参数查询    要使用参数查询检索数据，必须首先在MicrosoftQuery中创建参数查询，以便它把数据返回Excel。

请注意，您不能创建参数查询来从OLAP数据库中检索源数据。

有关创建参数查询的详细信息，请参阅MicrosoftQuery中的帮助。

Web查询    要使用Web查询来检索Internet上的数据，您必须首先创建Web查询并将数据检索到Excel中。

要创建Web查询，请指向数据菜单上的导入外部数据，然后单击新建Web查询。

多重合并计算数据区域

您可以合并与汇总几个不同Excel列表的数据。

要准备来自多个Excel列表的数据，可能需要执行下列操作：

∙确保列表组织良好    务必整洁！

例如，确保列表有与您要汇总的项目相匹配的行和列名称。

∙删除任何自动的分类汇总    不要担心，数据透视表将为您计算分类汇总和总计。

∙命名区域    如果计划稍后添加其他数据，请确保为源数据（区域:

工作表上的两个或多个单元格。

区域中的单元格可以相邻或不相邻。

）中输入的单元格区域（源数据:

用于创建数据透视表或数据透视图的数据清单或表。

源数据可以来自Excel数据清单或区域、外部数据库或多维数据集，或者另一张数据透视表。

）指定名称。

这样，无论何时向数据区域添加其他数据，都可以更新数据透视表，将新数据包括在内，而不必指定新区域引用。

另一个数据透视表

如果计划使用同样的数据创建多个数据透视表，可以重新使用现有的数据透视表，来创建新的数据透视表，从而节省内存和磁盘空间。

此操作将把原始的和新的数据透视表链接起来。

（如果您刷新了原数据透视表的数据，新报表的数据也会刷新，反之亦然。

）

要准备来自其他数据透视表的数据，可能需要执行下列操作：

∙确保两个数据透视表在同一工作簿中    如果原数据透视表位于不同的工作簿，请将原报表复制到您要显示新报表的工作簿中。

∙检查页面字段设置    您可能已在原数据透视表中更改了页面字段设置，以便分别检索每个页面的外部数据。

在这种情况下，您需要重新设置页面字段，以便一次为全部项目检索外部数据。

要检查设置，双击每一个页面字段，然后单击高级。

 注释   要了解有关页面字段的其他信息，参见本文稍后部分的“数据透视表解析”。

第2步：

创建数据透视表    

要创建数据透视表，请首先使用“数据透视表和数据透视图向导”。

该向导可帮助您指定要使用的数据及创建报表框架。

然后您可使用数据透视表工具栏在该框架内排列数据。

例如，您可以选择要包括的数据类别（衬以蓝色底纹）和要汇总的相关数据值（衬以黄色底纹）。

然后，告诉Excel如何在数据透视表的行和列中排列数据类别。

Excel创建完报表后，它将自动汇总和总计数据值。

要创建数据透视表，执行下列步骤：

启动数据透视表和数据透视图向导

1.打开您要创建数据透视表的工作簿。

如果您的报表要基于Excel列表或数据库，请单击列表或数据库中的一个单元格。

2.在数据菜单上，单击数据透视表和数据透视图。

3.在“数据透视表和数据透视图向导”的第1步中，请选择将要使用的数据类型，然后单击所需创建的报表类型下的数据透视表。

4.在向导的第2步中，按照定位数据的说明进行操作。

如果要为数据透视表检索外部数据，可以从Excel帮助或Query帮助中获得有关使用MicrosoftQuery或Query向导的详细信息。

5.在向导的第3步中单击完成。

定义数据透视表的布局

1.将字段按钮从数据透视表字段列表窗口拖到数据透视表图表的标签区域。

2.要重新排列各字段，请在区域间拖动这些字段。

要删除字段，请将其拖至数据透视表之外。

 注释   有些情况下，您可能不希望通过在工作表上直接拖动字段来手动定义数据透视表布局。

例如，如果源数据来自大型外部数据库，手动定义布局则可能过于费时。

在这种情况下，可以使用向导定义布局。

在“数据透视表和数据透视图向导”的第3步中单击布局来设置数据透视表布局。

数据透视表解析

您可能已经注意到，MicrosoftExcel使用了特定的术语来标识数据透视表的各部分。

如果您尚不熟悉这些术语，请阅读此部分，迅速获得初步知识。

 页面字段    此字段来自源数据，用于数据透视表中的页面（或筛选）。

例如，“年”是一个页面字段，可以使用“年”字段仅显示2003年、2004年等的汇总数据。

 数据字段    此字段来自源数据，其中包含要汇总的值。

例如，“销售总额”是一个数据字段。

对于多数源数据类型，可以选择汇总数据的方式（例如，求总和、平均值或计数）。

数据字段一般用于汇总数字，但也可汇总用于文本。

例如，可以对某字段中特定文本项（例如“是”或“否”）出现的次数进行计数。

 列字段    此字段来自源数据，用于数据透视表中的列。

例如，“类型”是一个列字段。

 项目    行、列或页面字段的子类别。

例如，“类型”字段包含了下列项目：

饮料、奶制品和肉制品。

“销售人员”字段包含了这些项目：

Buchanan、Davolio、Dodsworth和Suyama。

 行字段    此字段来自源数据，用于数据透视表中的行。

例如“地区”和“销售人员”都是行字段。

 数据区域    数据透视表中包含汇总数据的单元格。

例如单元格C5中的值汇总了2003年Buchanan在东部地区的饮料销售额。

也可以说，该单元格是源数据中包含Buchanan、饮料、东部和2003等项目的各行的销售额汇总。

第3步：

自定义数据透视表    

您现在已经创建了数据透视表，但这还只是开始。

是否还记得有关数据的那些问题？

数据透视表不仅是静态的汇总，还可以使用该表以交互的方式探索数据的含义。

可以查看详细信息，也可进行大局观察。

可以按照地区或时间段查看销售额。

自定义类型由您自己确定。

要自定义数据透视表，请执行下列一项或多项操作：

添加或删除数据

是否希望在数据透视表中查看多一些或少一些的数据类别？

可以方便地添加或删除字段（包括行、列或页面字段）

要添加字段，请从“数据透视表字段列表”窗口中将字段拖至数据透视表报表中的相应区域。

要删除字段，请将字段拖至数据透视表之外。

 注释   不得在数据透视表中直接编辑数据。

而是要编辑源数据，然后刷新数据透视表。

快速重新排列布局

是否需要重新组织数据透视表以获得不同的数据汇总结果？

通过移动字段或重新安排字段内各项的顺序，可以快速“旋转”数据透视表。

要移动字段，请将字段拖至数据透视表中相应区域。

要重新安排字段内各项的顺序，将项目拖至字段内的新位置。

使用页面字段查看数据子集

数据透视表中是否包括了页面字段？

如果是，该数据透视表即是由多个“页面”组成的三维报表。

其中每页包含了在报表中已经汇总的数据子集。

要查看其他页面，请单击页面字段项旁边的箭头，然后单击列表中的项目。

显示所需的详细信息

在数据透视表中，可以查看详细信息，也可隐藏信息以获得全局认识。

可以快速地：

∙显示或隐藏行或列中的项目    单击字段中的箭头，然后根据自己的需要选择或清除复选框。

例如，在“类型”字段中，可以隐藏除“饮料”外的全部产品项目：

∙显示或隐藏项目的特定详细信息    单击字段中的项目，然后单击数据透视表工具栏上的显示详细信息或隐藏详细信息按钮。

如果出现提示，单击希望显示其详细信息的字段。

例如，对于“类型”字段中的“饮料”项，通过显示每年销售额可以增加其详细信息的内容。

∙显示特定数据单元格的基础源数据    只需双击单元格即可查看其详细信息。

例如，双击东部地区的饮料分类汇总，可以查看用于计算该单元格的源数据记录。

第4步：

打印数据透视表    

是否要打印数据透视表？

如果该报表是一个多页报表，可以设置打印选项以控制报表的打印外观，如下所示：

∙为方便浏览多页的数据透视表，可以在各页顶端自动重新打印标签。

例如，在打印页面上，可以重复行1和行2中的标签，也可重复外行字段项目“四月”。

∙为确保在每页中能打印数据透视表的逻辑区块，可以指定Excel插入分页符的位置。

可以选择在特定位置插入分页符，例如，在“月”字段中每个月开始之前插入分页符，以避免将一个月的项目分隔开来。

例如，在下图中，自动分页符插入到了行41和行42之间，将四月的项目分隔开来。

要使四月的项目打印在一起，可以指定将分页符放置在行38“三月总计”的正下方。

打印之前，执行下列步骤以指定报表的打印区域：

1.在数据透视表内单击。

2.在数据透视表工具栏中，单击数据透视表，指向选择，然后单击整个表格。

3.在文件菜单中，指向打印区域，然后单击设置打印区域。

要设置数据透视表的打印选项，请执行下列任一或所有步骤：

在每个打印页面上重复行标签

1.在文件菜单中，单击页面设置，然后单击工作表选项卡。

确保顶端标题行框和左端标题列框是空白框，然后单击确定。

2.在数据透视表内单击。

3.在数据透视表工具栏中单击数据透视表，然后单击表格选项。

4.在格式选项下选择设置打印标题复选框。

在每个打印页面上重复外行字段项目

1.在数据透视表内单击。

2.在数据透视表工具栏中单击数据透视表，然后单击表格选项。

3.在格式选项下选择在每一打印页上重复项目复选框。

在单页上打印数据透视表的各部分

1.双击要在单页上打印其项目的外行字段的标题。

例如，要在每类产品之间插入自动分页符，双击“类型”行的标题。

2.在数据透视表字段对话框中单击布局。

 注释   如果外行字段中仅有一项，布局将不可用。

3.在打印选项下选择每项后面插入分页符复选框，然后单击两次确定。

4.在视图菜单中，单击分页预览，然后根据需要调整自动分页。

可以通过拖动来移动分页符，或按DELETE键来删除分页符。

 提示   要使所打印的数据透视表易于浏览，在数据透视表工具栏中单击设置报表格式按钮，从而快速切换为缩进格式。

使用这种格式，各行字段的数据都会缩进（类似于文字大纲），所有数据字段的汇总数据显示在一列中。

打印选项设置完成之后，可以单击文件菜单上的打印预览，以预览打印的报表。

准备好打印报表之后，单击打印。