数据透视表

OfficeStudy论坛教程

作者：凌风使者

第一章：数据透视表

第一节：创建数据透视表

练习 1：创建报表视图

1.在工作表中，选择包含数据的任意单元格。例如，单击单元格 A4。在

“数据”菜单上，单击“数据透视表和数据透视图”。将显示向导。

2.在向导的步骤 1 中，确保选择“Microsoft Excel 数据列表或数据

库”作为第一个问题的答案。

3.确保选择“数据透视表”作为下一个问题的答案。

4.单击“完成”。

注意正确！单击“完成”让向导使用默认设置为数据透视表设置区域。单击“下一步”而不单击“完成”，您可能要花费更多时间设置向导，但是现在不需要这样操作。

向下滚动，阅读后面的练习步骤，布局区域内的文本将消失。在工作表上单击时，文本重新显示。

新工作表“Sheet1”插入到练习单元工作簿中。新工作表包括三个项目：“数据透视表字段列表”、报表的布局区域（其中包括各个用于放置拖放字段的带外边框的独立拖放区域）和“数据透视表”工具栏。假如不能看到类似于如下所示的字段列表：

单击带外边框的区域。如果还是不能看到字段列表，则在“数据透视表”工具栏上（该工具栏浮动在工作表上，或者固定在一边。），单击“显示字段列表”

按钮。

注意如果“数据透视表”工具栏不可见，在“视图”菜单上，指向“工具栏”，然后单击“数据透视表”。

完成后面的练习时，如果愿意，您可以将字段列表和工具栏拖动到其他位置。练习 2：设置报表布局

现在将设置报表布局，以算出每位销售人员的销量。将字段放置到布局中时，报表布局的外观将发生更改。

1.从“数据透视表字段列表”中，

将“销售人员”字段拖动到标签为“将行字段拖至此处”的拖放区域。

可以通过选择字段名称或字段名称前面的按钮拖动字段。如果将字段拖放到了错误的拖放区域，只需把它拖放到正确的拖放区域即可。

注意拖放字段名称后，它们仍保留在列表上，但是更改为黑体。

2.从“数据透视表字段列表”中，将“订单金额”字段拖动到标签为

“请将数据项拖至此处”的拖放区域。

将字段拖放到数据项的拖放区域中时，彩色外框将消失，并将显示报表，其中显示有每位销售人员的销售总额。

数据透视表的前几行如下图所示：

要查看该报表，且工作表顶部不显示空白的页字段拖放区域：

单击布局区域外的任意位置。例如，在单元格 C4 内单击，页字段区域消失，“数据透视表字段列表”也消失。

练习 3：转动报表

在这个练习中，您将把数据从行方向转到列方向。

1.单击“销售人员”字段标题（在单元格 A4 中）。

2.拖放“销售人员”字段标题到单元格 B3，B3 是在“总额”正上方的单

元格。拖动时，只要获得灰色框光标并把光标指向目标单元格即可。

报表的外观发生更改，将按列方向显示销售人员姓名，而不是按行方向。

这种特殊的数据按列格式读起来非常不容易，但是列格式与其他数据一起非常有用。您需要了解如何将数据从一个方向移动到另一个方向。

下一步，将“销售人员”字段移回到行方向。

操作步骤单击“销售人员”字段标题，将其拖放到单元格 A4。

练习 4：创建页视图

现在可以进行新的尝试：创建页视图。在本示例中，将显示销售人员的订单金额，不同国家/地区的数量显示在不同页面上。将字段拖放到标签为“将页字段拖至此处”的拖放区域。

将“国家/地区”字段从“数据透视表字段列表”拖放到“将页字段拖至此处”区域。

注意如果看不到字段列表，单击布局区域。如果还不能看到，则在“数据

透视表”工具栏上单击“显示字段列表”按钮。

现在报表将如下图所示：

可见页面的名称显示在“国家/地区”字段旁的单元格中。在本示例中，可见页面具有列表中所有国家/地区的数据。

要查看另一页面，单击页面名称旁边的箭头，选择一个页面，然后单击“确定”。

可以查看三个页面。在国家/地区页面上，按国家/地区显示每个销售人员的销售数量，在“（全部）”页面上显示他们在所有国家/地区销售的总量。

练习 5：从报表中拖出字段

我们已经讲过，可以很容易地更改报表中包含的信息。现在可以看到如何删除字段。开始之前，注意现在在报表视图中显示的字段在“数据透视表字段列表”中用粗体字母显示。这将帮助您追踪已拖放到报表中的字段。

1.单击工作表（单元格 A1）上的“国家/地区”字段标题，然后拖放到外

边框外面的任何位置。例如，将它拖放到单元格 D2 中。

注意将字段拖动到布局区域外面后，一个红“X”显示在插入点下方。这个 X 表示该字段已从报表布局中删除。

2.单击工作表（单元格 A3）上的“订单金额总额”字段标题，然后拖放

到外边框外面的任何位置。

拖放字段时，外边框将扩张。

3.单击工作表（单元格 A4）上的“销售人员”字段标题，然后拖放到外

边框外面的任何位置。

外边框看上去和开始操作之前一样。字段列表上的任何名称都不用粗体字母表示，因为该列表中没有字段用于数据透视表布局区域。

练习 6：多个层面的数据分析-数据透视表和数据透视图

当销售人员将数据保存在Excel工作表中后，却发现得到的只是一张流水账的表格，这是因为多数人把注意力都放在了数据本身，而忽略了表格的外观，使表格数据杂乱、毫无层次，如图1所示。

图1 保存在Excel中的流水数据

的确，有了计算机和数据库，对于一般的数据分类求和只是举手之劳，然而人们却又提出了快速汇总大量数据和建立交叉列表设计的交互式表格的要求，这对普通的数据库管理系统确有点强人所难，起码程序的编写绝不是容易的事。

其实Microsoft Excel的数据透视表完全可以轻松地解决这个问题。数据透视表是一种用于对数据进行交叉分析的三维表格。它的特点在于表格结构的不固定性，可以随时根据实际需求进行调整，从而得出不同的视图。

为方便用户使用，Microsoft Excel提供了数据透视表和数据透视图向导，在该向导的指导下，用户只要按部就班地一步一步进行操作，就可以轻松地完成数据透视表。它的操作步骤共分三步：选择数据源类型、选择数据源区域、指定数据透视表位置，具体操作步骤如下：

（1）打开销售数据工作表，单击数据清单中的任意单元格，执行菜单栏中的【数据】|【数据透视表和数据透视图】命令，打开对话框“数据透视表和数据透视图向导--3步骤之1”，如图2所示。

图2 建立数据透视表步骤1

该步骤主要确定数据源类型和报表类型。这里指定默认选项，即数据源类型为Excel数据清单，报表类型为数据透视表。

（2）单击【下一步】按钮进入“数据透视表和数据透视图向导--3步骤之2”对话框，该步骤用于选定数据源区域。由于数据清单都是位于某个连续的单元格区域，所以，一般情况下Excel会自动识别数据源所在的单元格区域，并填入到“选定区域”框，如图3所示。

图3 建立数据透视表步骤2

提示：在这个步骤中，我们发现很多朋友都会手工地选择数据源的区域，其实Excel是很智能的，它可以自动将数据源区域选择完成，这对于比较大的工作表来说的确是一件轻松的事情。

（3）再单击【下一步】按钮进入“数据透视表和数据透视图向导--3步骤之3”对话框，选择将数据透视表创始在一个新工作表中，还是在当前工作表，这里选择“新建工作表”，如图4所示。

图4 建立数据透视表步骤3

（4）最后单击【布局】按钮，显示“数据透视表和数据透视图向导—布局”对话框，如图5所示，该步骤也是创建数据透视表的最关键的一步。

图5 调整数据透视表布局

设置数据透视表布局非常简单，用鼠标拖动右边的按钮，将其按自己的设计要求放置在左边图中相应的位置就可以构造出数据透视表。

（5）在此，将“地区”字段拖动到“页”区域；将“城市”字段拖动到“行”区域；将“订货日期”字段拖动到“列”区域；将“订货金额”字段拖动到“数据”区域，如图6所示。

图6 设置完成的布局

（6）单击【确定】按钮返回步骤3所示的对话框，再单击【完成】按钮结束。生成的数据透视表如图7所示。

图7 创建完成的数据透视表

到这里，一个数据透视表就制作完成了，你可以自由地操作它，来查看不同的数据项目。但是，在这个数据表中显示的是每天的数据记录，如果公司要求你完成销售的月报表、季度报表或年报表的话，又该怎么办呢？其实在数据透视表中，这些操作非常简单：

选中“订货日期”按钮，单击鼠标右键，在快捷菜单中选择【组及显示明细数据】｜【组合】命令，打开“分组”对话框，如图8所示。在“起始于”和“终止于”文本框中输入一个时间间隔，然后在“依据”下拉列表中选择“季度”，即要对1997年的销售金额按照季度的方式查阅（如果想生成月报表，就选择“月”；想生成年报表，就选择“年”；当然，如果你希望生成“秒报表”，Excel也是支持的哦）。

图8 创建分组

创建完成数据分组后，数据透视表的结构如图9所示。

图9 销售季度报表

每个公司要求的报表格式可能会不太一样，但借助Excel的数据透视表功能，可以快速改变报表的布局。例如，销售人员想生成如图10所示的一个综合报表，只需要将“地区”字段拖动到“城市”字段前面即可，是不是很方便。使用这种灵活拖动的方式，相信你可以制作完成不同风格、不同用处的报表。

图10 更改布局之后的报表

甚至，如果你想查看某个地区、某个城市的明细数据的话，只需双击相应的数据条目，即可打开“显示明细数据”对话框（如图11所示），然后选择相关字段就可以了。

图11 显示明细字段

这样，数据透视表又有了另外一种布局，如图12所示。

图12 显示某一城市的订单明细

第二节：使用向导和移动字段

练习 1：使用向导创建透视表布局区域

开始前，请先查看源数据。“订单金额”字段包含销售图表数据，“销售人员”字段包含销售人员姓名。在本练习单元中您将使用这两个字段，以及“国家/地区”字段，它标识每位销售人员所在的国家。

操作步骤选择工作表中包含数据的任一单元格。例如，单击单元格 A4。在“数据”菜单上，单击“数据透视表与数据透视图”，再单击“完成”。

单击“完成”会通知向导使用缺省设置来设置数据透视表区域的布局，在本例中这就足够了。

新工作表“sheet1”将插入到练习工作簿中。新的工作表包含透视表的布局区域、“数据透视表”工具栏以及“数据透视表字段列表”。

“数据透视表字段列表”如下所示：

如果看不到此字段列表，则单击分级显示的布局区域。如果还是看不到，请单击“数据透视表”工具栏上的“显示字段列表”按钮（该工具栏应该浮动显示在工作表上，或者固定在一侧。如果它不可见，单击“视图”打开视图菜单，指向“工具栏”，然后单击“数据透视表”。）

练习 2: 设置透视表布局

在本练习中，您将汇总源数据（销售图表）中的 799 行信息，并将之放到一张简短的数据透视表中。

设置透视表布局以回答以下问题：每位销售人员的总销售额是多少？请注意，销售额数据包含在“订单金额”字段中。

操作步骤从“数据透视表字段列表”将“销售人员”字段拖动到标签为“将行字段拖至此处”的拖放区域中的任意位置。将该字段拖到拖放区域时，该区域的分级显示将消失，您会看到每位销售人员的姓名（每行一个姓名）。

注意，“数据透视表字段列表”上的“销售人员”字段的名称现在以粗体显示，表示该字段已被拖放到了布局区域。

接下来从“数据透视表字段列表”将“订单金额”拖放到标签为“请将数据项拖至此处”的拖放区域中的任意位置。

如果数据透视表中的数据如以下所示，您现在就获得了每位销售人员的总销售额了。

注意如果您的透视表中的数据和这幅图不一样，或者您需要了解更详细的说明，可以考虑学习“数据透视表 I：数据透视表为何如此重要？”后再学习本课并做练习。

练习 3：创建页字段

请注意，所有来自英国和美国的销售人员都有相应的销售额。通过页字段，您可以选择是一次查看所有国家或地区的数据，还是一次只看一个国家或地区的数据。

操作步骤在“数据透视表字段列表”中，选择“国家/地区”字段并将其拖放到“请将页字段拖至此处”区域，该区域如下所示：

拖放“国家/地区”字段后，您会看到不同的页视图。

操作步骤单击“国家/地区”字段标题旁的向下箭头：

选择“英国”，然后单击“确定”。现在您将只看到英国的销售额。要再次查看所有国家，再次单击向下箭头，选择“（全部）”，然后单击“确定”。

注意字段标题上的向下箭头表示您可以选择要在透视表中显示的细节。例如，单击“销售人员”上的箭头将显示包含每位销售人员姓名的列表。要仅显示部分销售人员的姓名，请单击“全部显示”旁边的框，以清除所有的复选标记，再单击您要显示的姓名，然后单击“确定”。

要再次显示所有的姓名，请单击“销售人员”字段上的箭头，再单击“全部显示”旁边的框，然后单击“确定”。

练习 4：创建新布局以获取其他问题的答案

创建新的数据透视表来回答这个问题：各个国家或地区的总销售额是多少？将所有字段从布局上拖走，然后再拖回来可以解答这个问题。但本练习将介绍在不从头开始的情况下如何进行更改。

操作步骤在数据透视表布局上，单击“销售人员”字段标题，

然后将它拖到布局区域以外的任何位置。例如，将它拖到单元格 D2。您需要将该字段从透视表上拖走，这是因为我们不再需要销售人员的总销售额了。

注意在该字段被拖离布局后，一个红色的“X”将出现在插入点的下面。X 表示该字段已从透视表布局上移走了。当放开鼠标按钮时，X 将会消失。

注意如果您单击了透视表外的单元格，透视表字段列表将会消失。要让它重新显示，单击透视表内部即可。

从数据透视表布局上将“国家/地区”字段拖放到单元格 A4。

注意，在拖动“国家/地区”字段时，它看上去仍在 A1 中。一旦插入点位于单元格 A4 中，该单元格的边框将成为深灰色线条。这时您就可以将该字段放到单元格 A4 了。您正在用“国家/地区”字段替换“销售人员”字段来查看各国家或地区的总销售额，而不再查看销售人员的总销售额。

透视表刷新后将以行来显示国家或地区的名称，并按国家或地区显示其总销售额。

提示可以将该“国家/地区”字段拖动到列字段区域而不是行字段区域来在列方向显示国家或地区。单击“国家/地区”字段标题，然后将其拖放到单元格 B3，该单元格就在“汇总”的上面。您还记得在将“订单金额”字段放在布局之前“将列字段拖至此处”区域就在此位置吗？虽然该区域的名称不可见，但它还在此处，您仍可以使用它。

透视表刷新后在列方向、而不是行方向上显示国家或地区的名称。

练习 5：使用菜单命令向布局区域添加字段

不喜欢拖放方法吗？请尝试使用命令而不是拖动来将字段移到布局区域。

在开始前，将所有字段从数据透视表拖走。

提示遵循练习 4 中的步骤将字段拖动到布局区域外的任意位置。注意，从布局区域拖走字段时，“数据透视表字段列表”上的字段名不再以粗体显示。

您将创建与练习 2 和练习 3 一样的数据透视表，只是这次拖动字段使用的是菜单命令。

1.首先，将“国家/地区”字段放到“请将页字段拖至此处”：

在“数据透视表字段列表”上，单击“国家/地区”字段。

2.在字段列表的右下角单击箭头，然后选择下拉列表中的“页面区域”。

如果看不到字段列表的底部，可以在工作区中将它往上拉。

3.单击“添加到”

将“国家/地区”字段添加到“请将页字段拖至此处”区域。

4.重复以上步骤，通过在下拉菜单分别选择“行区域”和“数据区域”将

“销售人员”和“订单金额”字段添加到“将行字段拖至此处”和

“请将数据项拖至此处”区域。

第三节：旋转数据透视表

练习 1：重命名字段标题

操作步骤在工作表的底部单击数据透视表选项卡，以便看到数据透视表。然后单击单元格 A3（在“销售人员”字段标题之上的灰色框中的字段标题）中的“订单金额总和”，键入全部订单，按 Enter 键。

练习 2：更改排序次序

在本练习中，您将更改排序次序，使姓名按销售额而不是按姓名字母排序。

1.单击“销售人员”。

2.在“数据透视表”工具栏上，单击“数据透视表”，然后单击“排序

并显示前 10 项”。

注意也许需要单击菜单底部的燕尾箭头才能看到“排序并显示前

10 项”命令。

3.在“自动排序选项”下，单击“降序”。

4.在“使用字段”列表中，单击向下箭头，选择在练习 1 中为“订单金

额总和”字段指定的新标题进行排序。例如，如果将该字段重命名为

“全部订单”，则单击该名称。单击“确定”。

现在透视表以降序次序显示销售人员的销售额，从销售额最高的潘金开始，以销售额最低的林丹结束。

练习 3：设置数字的格式

在本练习中，您将设置销售额的货币格式。

1.单击数据拖放区域中包含数值的任一单元格。例如，单击单元格 B5。

2.单击“数据透视表”上的“字段设置”按钮，然后单击“数字”。

3.在“单元格格式”对话框中，单击“分类”列表中的“货币”。

4.在“小数位数”列表中，单击向下箭头使列表中的值为 0。这将去掉小

数位。

5.单击“确定”两次。

练习 4：刷新数据

在本练习中，您将修改源数据中的一个数字，然后在透视表中反映该变化。

1.在工作表的底部，单击“练习”选项卡切换到源数据。

2.在单元格 C2 中，将“￥440.00”修改成“￥5,000.00”，按 Enter

键。

3.单击工作表底部的“数据透视表”选项卡，切换回数据透视表。

注意，林丹的总额是￥68,792。

4.单击“数据透视表”工具栏上的“刷新数据”按钮。

（顺便提一下，如果“刷新数据”命令不可用，单击数据透视表区域内部也可刷新数据。）

林丹的总额变为￥73,352，他不再是排序列表中的最后一位。现在轮到苏术平不好受了。

练习 5：更改数据汇总的方式

假设您需要统计每位销售人员成交订单的笔数，而不是统计他们的销售额。

1.单击数据区域中的任何单元格。例如，单击单元格 B5。

2.单击“数据透视表”工具栏上的“字段设置”按钮。

3.在“汇总方式”列表中，单击“计数”，然后单击“确定”。

现在透视表中显示每个人成交订单的笔数，但数字是以在练习 3 中指定的货币格式显示的。要消除货币格式，请重复练习 3 中的步骤，并在“分类”列表中选择“常规”，然后单击“确定”两次。

练习 6：删除数据透视表

尝试一些新的操作：删除数据透视表。这只是删除了透视表，对源数据并没有任何影响。

1.单击数据透视表。

2.在“数据透视表”工具栏上，单击“数据透视表”，指向“选定”，

然后单击“整张表格”。

注意也许需要单击菜单底部的燕尾箭头才能看到“选定”命令。

3.在“编辑”菜单上，指向“清除”，然后单击“全部”。

该透视表就被删除了，您将看到一张空的工作表。源数据仍然保存在练习工作表中。

提示用鼠标右键单击工作表标签并单击“删除”，也可以删除透视表和工作表。您会看到所有数据都将永远被删除的警告，继续并单击“删除”。

第四节：高级内容

练习 1：在同一拖放区使用两个字段

1.确定“数据透视表字段列表”已经显示。