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

打印全部隐藏适用于Microsoft Office Excel 2003Microsoft Excel 2002假设您已编辑了一个大的数据列表（例如公司每种产品的销售额）。

您现在可以从这些数据中提取一些有意义的信息并找到下列问题的答案：∙按区域划分，每种产品的总销售额是多少？∙过去的时间内哪些产品销量最好？∙业绩最好的销售人员是谁？要回答这些问题以及其他问题，可以创建PivotTable® 报表（自动提取、组织和汇总数据的交互表）。

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

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

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

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

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

“数据透视表”报表中，可以迅速：∙添加或删除数据“数据透视表”报表中的信息不太充分吗？可能您还希望报表中包括各销售人员的销售额等数据。

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

例如，可以添加销售人员字段，如下图所示。

∙重新排列布局对“数据透视表”报表的布局不满意吗？例如，您可能更愿意按行而不是按列显示销售人员信息。

利用“数据透视表”报表的交互特性，可以翻转表格：您可以轻松地移动（或旋转）行和列以查看不同的汇总数据。

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

∙查看数据的子集要筛选数据以便您在某一时候能按需查看某部分数据吗？要这样做，可用页面字段将“数据透视表”报表分成若干独立的“页面”。

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

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

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

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

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

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

显示您需要的细节是否关注“数据透视表”报表中的特定详细信息？您可以显示或隐藏行字段或列字段中的项目，例如特定产品或销售人员。

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

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

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

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

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

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

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

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

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

单击下列任一源数据类型，了解如何使用这些数据类型：Excel 列表或数据库使用创建列表命令，可以使用包含相关数据的多个连续行，也可使用指定用作数据表的多个连续行。

要轻松准备Excel 数据，应考虑执行下列任一或全部操作：∙确保列表组织良好务必整洁！例如，确保列表的第一行包含列标签，因为Excel 要在报表中将此数据用作字段名。

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

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

∙命名区域如果计划稍后添加其他数据，请确保为源数据中输入的单元格区域指定名称。

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

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

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

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

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

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

要准备外部数据，可能需要执行下列操作：∙安装必要的工具和驱动程序您可能已完全准备就绪，只需确保安装了相应的开放式数据库连接(ODBC) 驱动程序或所需的数据源驱动程序。

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

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

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

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

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

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

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

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

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

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

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

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

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

多重合并计算数据区域您可以合并与汇总几个不同Excel 列表的数据。

要准备来自多个Excel 列表的数据，可能需要执行下列操作：∙确保列表组织良好务必整洁！例如，确保列表有与您要汇总的项目相匹配的行和列名称。

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

∙命名区域如果计划稍后添加其他数据，请确保为源数据中输入的单元格区域指定名称。

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

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

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

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

）要准备来自其他数据透视表的数据，可能需要执行下列操作：∙确保两个数据透视表在同一工作簿中如果原数据透视表位于不同的工作簿，请将原报表复制到您要显示新报表的工作簿中。

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

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

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

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

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

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

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

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

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

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

要创建数据透视表，执行下列步骤：启动数据透视表和数据透视图向导1. 打开您要创建数据透视表的工作簿。

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

例如，“类型”字段包含了下列项目：饮料、奶制品和肉制品。

“销售人员”字段包含了这些项目：Buchanan、Davolio、Dodsworth 和Suyama。

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

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

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

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

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

第 3 步：自定义数据透视表您现在已经创建了数据透视表，但这还只是开始。

是否还记得有关数据的那些问题？数据透视表不仅是静态的汇总，还可以使用该表以交互的方式探索数据的含义。