数据透视表一次学会.docx

1、数据透视表一次学会创建数据透视表练习 1：创建报表视图1. 在工作表中，选择包含数据的任意单元格。例如，单击单元格 A4。在“数据”菜单上，单击“数据透视表和数据透视图”。将显示向导。 2. 在向导的步骤 1 中，确保选择“Microsoft Excel 数据列表或数据库”作为第一个问题的答案。 3. 确保选择“数据透视表”作为下一个问题的答案。 4. 单击“完成”。 注意 正确！单击“完成”让向导使用默认设置为数据透视表设置区域。单击“下一步”而不单击“完成”，您可能要花费更多时间设置向导，但是现在不需要这样操作。向下滚动，阅读后面的练习步骤，布局区域内的文本将消失。在工作表上单击时，文本重

2、新显示。新工作表“Sheet1”插入到练习单元工作簿中。新工作表包括三个项目：“数据透视表字段列表”、报表的布局区域（其中包括各个用于放置拖放字段的带外边框的独立拖放区域）和“数据透视表”工具栏。假如不能看到类似于如下所示的字段列表： 单击带外边框的区域。如果还是不能看到字段列表，则在“数据透视表”工具栏上（该工具栏浮动在工作表上，或者固定在一边。），单击“显示字段列表”按钮 。注意 如果“数据透视表”工具栏不可见，在“视图”菜单上，指向“工具栏”，然后单击“数据透视表”。完成后面的练习时，如果愿意，您可以将字段列表和工具栏拖动到其他位置。练习 2：设置报表布局现在将设置报表布局，以算出每位销

3、售人员的销量。将字段放置到布局中时，报表布局的外观将发生更改。1. 从“数据透视表字段列表”中， 将“销售人员”字段拖动到标签为“将行字段拖至此处”的拖放区域。 可以通过选择字段名称或字段名称前面的按钮拖动字段。如果将字段拖放到了错误的拖放区域，只需把它拖放到正确的拖放区域即可。注意 拖放字段名称后，它们仍保留在列表上，但是更改为黑体。2. 从“数据透视表字段列表”中，将“订单金额”字段拖动到标签为“请将数据项拖至此处”的拖放区域。 将字段拖放到数据项的拖放区域中时，彩色外框将消失，并将显示报表，其中显示有每位销售人员的销售总额。数据透视表的前几行如下图所示： 要查看该报表，且工作表顶部不显示

4、空白的页字段拖放区域：单击布局区域外的任意位置。例如，在单元格 C4 内单击，页字段区域消失，“数据透视表字段列表”也消失。练习 3：转动报表在这个练习中，您将把数据从行方向转到列方向。1. 单击“销售人员” 字段标题（在单元格 A4 中）。 2. 拖放“销售人员”字段标题到单元格 B3，B3 是在“总额”正上方的单元格。拖动时，只要获得灰色框光标并把光标指向目标单元格即可。 报表的外观发生更改，将按列方向显示销售人员姓名，而不是按行方向。这种特殊的数据按列格式读起来非常不容易，但是列格式与其他数据一起非常有用。您需要了解如何将数据从一个方向移动到另一个方向。下一步，将“销售人员”字段移回到行

5、方向。操作步骤 单击“销售人员”字段标题，将其拖放到单元格 A4。练习 4：创建页视图现在可以进行新的尝试：创建页视图。在本示例中，将显示销售人员的订单金额，不同国家/地区的数量显示在不同页面上。将字段拖放到标签为“将页字段拖至此处”的拖放区域。 将“国家/地区”字段从“数据透视表字段列表”拖放到“将页字段拖至此处”区域。 注意 如果看不到字段列表，单击布局区域。如果还不能看到，则在“数据透视表”工具栏上单击“显示字段列表”按钮 。现在报表将如下图所示：可见页面的名称显示在“国家/地区”字段旁的单元格中。在本示例中，可见页面具有列表中所有国家/地区的数据。 要查看另一页面，单击页面名称旁边的箭

6、头，选择一个页面，然后单击“确定”。 可以查看三个页面。在国家/地区页面上，按国家/地区显示每个销售人员的销售数量，在“（全部）”页面上显示他们在所有国家/地区销售的总量。练习 5：从报表中拖出字段我们已经讲过，可以很容易地更改报表中包含的信息。现在可以看到如何删除字段。开始之前，注意现在在报表视图中显示的字段在“数据透视表字段列表”中用粗体字母显示。这将帮助您追踪已拖放到报表中的字段。1. 单击工作表（单元格 A1）上的“国家/地区”字段标题，然后拖放到外边框外面的任何位置。例如，将它拖放到单元格 D2 中。 注意 将字段拖动到布局区域外面后，一个红“X”显示在插入点下方。这个 X 表示该字

7、段已从报表布局中删除。2. 单击工作表（单元格 A3）上的“订单金额总额” 字段标题，然后拖放到外边框外面的任何位置。 拖放字段时，外边框将扩张。3. 单击工作表（单元格 A4）上的“销售人员”字段标题，然后拖放到外边框外面的任何位置。 外边框看上去和开始操作之前一样。字段列表上的任何名称都不用粗体字母表示，因为该列表中没有字段用于数据透视表布局区域。这样，如果您想从头再试一次，将会很简单。几秒之内就可获得明确的结论。使用向导和移动字段练习 1：使用向导创建透视表布局区域开始前，请先查看源数据。“订单金额”字段包含销售图表数据，“销售人员”字段包含销售人员姓名。在本练习单元中您将使用这两个字段

8、，以及“国家/地区”字段，它标识每位销售人员所在的国家。操作步骤 选择工作表中包含数据的任一单元格。例如，单击单元格 A4。在“数据”菜单上，单击“数据透视表与数据透视图”，再单击“完成”。单击“完成”会通知向导使用缺省设置来设置数据透视表区域的布局，在本例中这就足够了。新工作表“sheet1”将插入到练习工作簿中。新的工作表包含透视表的布局区域、“数据透视表”工具栏以及“数据透视表字段列表”。“数据透视表字段列表”如下所示：如果看不到此字段列表，则单击分级显示的布局区域。如果还是看不到，请单击“数据透视表”工具栏上的“显示字段列表”按钮 （该工具栏应该浮动显示在工作表上，或者固定在一侧。如果

9、它不可见，单击 “视图”打开视图菜单，指向“工具栏”，然后单击“数据透视表”。）练习 2: 设置透视表布局在本练习中，您将汇总源数据（销售图表）中的 799 行信息，并将之放到一张简短的数据透视表中。设置透视表布局以回答以下问题：每位销售人员的总销售额是多少？请注意，销售额数据包含在“订单金额”字段中。操作步骤 从“数据透视表字段列表”将“销售人员”字段拖动到标签为“将行字段拖至此处”的拖放区域中的任意位置。将该字段拖到拖放区域时，该区域的分级显示将消失，您会看到每位销售人员的姓名（每行一个姓名）。 注意，“数据透视表字段列表”上的“销售人员”字段的名称现在以粗体显示，表示该字段已被拖放到了布

10、局区域。接下来从“数据透视表字段列表”将“订单金额”拖放到标签为“请将数据项拖至此处”的拖放区域中的任意位置。如果数据透视表中的数据如以下所示，您现在就获得了每位销售人员的总销售额了。注意 如果您的透视表中的数据和这幅图不一样，或者您需要了解更详细的说明，可以考虑学习“数据透视表 I：数据透视表为何如此重要？”后再学习本课并做练习。练习 3：创建页字段请注意，所有来自英国和美国的销售人员都有相应的销售额。通过页字段，您可以选择是一次查看所有国家或地区的数据，还是一次只看一个国家或地区的数据。操作步骤 在“数据透视表字段列表”中，选择“国家/地区”字段并将其拖放到“请将页字段拖至此处”区域，该区

11、域如下所示：拖放“国家/地区”字段后，您会看到不同的页视图。操作步骤 单击“国家/地区”字段标题旁的向下箭头：选择“英国”，然后单击“确定”。现在您将只看到英国的销售额。要再次查看所有国家，再次单击向下箭头，选择“（全部）”，然后单击“确定”。注意 字段标题上的向下箭头表示您可以选择要在透视表中显示的细节。例如，单击“销售人员”上的箭头将显示包含每位销售人员姓名的列表。要仅显示部分销售人员的姓名，请单击“全部显示”旁边的框，以清除所有的复选标记，再单击您要显示的姓名，然后单击“确定”。 要再次显示所有的姓名，请单击“销售人员”字段上的箭头，再单击“全部显示”旁边的框，然后单击“确定”。练习 4

12、：创建新布局以获取其他问题的答案创建新的数据透视表来回答这个问题：各个国家或地区的总销售额是多少？将所有字段从布局上拖走，然后再拖回来可以解答这个问题。但本练习将介绍在不从头开始的情况下如何进行更改。操作步骤 在数据透视表布局上，单击“销售人员”字段标题，然后将它拖到布局区域以外的任何位置。例如，将它拖到单元格 D2。您需要将该字段从透视表上拖走，这是因为我们不再需要销售人员的总销售额了。注意 在该字段被拖离布局后，一个红色的“X”将出现在插入点的下面。X 表示该字段已从透视表布局上移走了。当放开鼠标按钮时，X 将会消失。注意 如果您单击了透视表外的单元格，透视表字段列表将会消失。要让它重新显

13、示，单击透视表内部即可。从数据透视表布局上将“国家/地区”字段拖放到单元格 A4。注意，在拖动“国家/地区”字段时，它看上去仍在 A1 中。一旦插入点位于单元格 A4 中，该单元格的边框将成为深灰色线条。这时您就可以将该字段放到单元格 A4 了。您正在用“国家/地区”字段替换“销售人员”字段来查看各国家或地区的总销售额，而不再查看销售人员的总销售额。透视表刷新后将以行来显示国家或地区的名称，并按国家或地区显示其总销售额。提示 可以将该“国家/地区”字段拖动到列字段区域而不是行字段区域来在列方向显示国家或地区。单击“国家/地区”字段标题，然后将其拖放到单元格 B3，该单元格就在“汇总”的上面。您

14、还记得在将“订单金额”字段放在布局之前“将列字段拖至此处”区域就在此位置吗？虽然该区域的名称不可见，但它还在此处，您仍可以使用它。透视表刷新后在列方向、而不是行方向上显示国家或地区的名称。练习 5：使用菜单命令向布局区域添加字段不喜欢拖放方法吗？请尝试使用命令而不是拖动来将字段移到布局区域。在开始前，将所有字段从数据透视表拖走。提示 遵循练习 4 中的步骤将字段拖动到布局区域外的任意位置。注意，从布局区域拖走字段时，“数据透视表字段列表”上的字段名不再以粗体显示。您将创建与练习 2 和练习 3 一样的数据透视表，只是这次拖动字段使用的是菜单命令。 1. 首先，将“国家/地区”字段放到“请将页字

15、段拖至此处”： 在“数据透视表字段列表”上，单击“国家/地区”字段。 2. 在字段列表的右下角单击箭头，然后选择下拉列表中的“页面区域”。 如果看不到字段列表的底部，可以在工作区中将它往上拉。3. 单击“添加到” 将“国家/地区”字段添加到“请将页字段拖至此处”区域。 4. 重复以上步骤，通过在下拉菜单分别选择“行区域”和“数据区域”将“销售人员”和“订单金额”字段添加到“将行字段拖至此处”和“请将数据项拖至此处”区域。 旋转数据透视表练习 1：重命名字段标题操作步骤 在工作表的底部单击数据透视表选项卡，以便看到数据透视表。然后单击单元格 A3（在“销售人员”字段标题之上的灰色框中的字段标题）

16、中的“订单金额总和”，键入全部订单，按 Enter 键。练习 2：更改排序次序在本练习中，您将更改排序次序，使姓名按销售额而不是按姓名字母排序。1. 单击“销售人员”。 2. 在“数据透视表”工具栏上，单击“数据透视表”，然后单击“排序并显示前 10 项”。 注意 也许需要单击菜单底部的燕尾箭头才能看到“排序并显示前 10 项”命令。3. 在“自动排序选项”下，单击“降序”。 4. 在“使用字段”列表中，单击向下箭头，选择在练习 1 中为“订单金额总和”字段指定的新标题进行排序。例如，如果将该字段重命名为“全部订单”，则单击该名称。单击“确定”。 现在透视表以降序次序显示销售人员的销售额，从销

17、售额最高的潘金开始，以销售额最低的林丹结束。练习 3：设置数字的格式在本练习中，您将设置销售额的货币格式。1. 单击数据拖放区域中包含数值的任一单元格。例如，单击单元格 B5。 2. 单击“数据透视表”上的“字段设置”按钮 ，然后单击“数字”。 3. 在“单元格格式”对话框中，单击“分类”列表中的“货币”。 4. 在“小数位数”列表中，单击向下箭头使列表中的值为 0。这将去掉小数位。 5. 单击“确定”两次。 练习 4：刷新数据在本练习中，您将修改源数据中的一个数字，然后在透视表中反映该变化。1. 在工作表的底部，单击“练习”选项卡切换到源数据。 2. 在单元格 C2 中，将“￥440.00”

18、修改成“￥5,000.00”，按 Enter 键。 3. 单击工作表底部的“数据透视表”选项卡，切换回数据透视表。 注意，林丹的总额是 ￥68,792。4. 单击“数据透视表”工具栏上的“刷新数据”按钮 。 （顺便提一下，如果“刷新数据”命令不可用，单击数据透视表区域内部也可刷新数据。）林丹的总额变为 ￥73,352，他不再是排序列表中的最后一位。现在轮到苏术平不好受了。练习 5：更改数据汇总的方式假设您需要统计每位销售人员成交订单的笔数，而不是统计他们的销售额。1. 单击数据区域中的任何单元格。例如，单击单元格 B5。 2. 单击“数据透视表”工具栏上的“字段设置”按钮 。 3. 在“汇总方

19、式”列表中，单击“计数”，然后单击“确定”。 现在透视表中显示每个人成交订单的笔数，但数字是以在练习 3 中指定的货币格式显示的。要消除货币格式，请重复练习 3 中的步骤，并在“分类”列表中选择“常规”，然后单击“确定”两次。练习 6：删除数据透视表尝试一些新的操作：删除数据透视表。这只是删除了透视表，对源数据并没有任何影响。1. 单击数据透视表。 2. 在“数据透视表”工具栏上，单击“数据透视表”，指向“选定”，然后单击“整张表格”。 注意 也许需要单击菜单底部的燕尾箭头才能看到“选定”命令。3. 在“编辑”菜单上，指向“清除”，然后单击“全部”。 该透视表就被删除了，您将看到一张空的工作表

20、。源数据仍然保存在练习工作表中。提示 用鼠标右键单击工作表标签并单击“删除”，也可以删除透视表和工作表。您会看到所有数据都将永远被删除的警告，继续并单击“删除”。高级内容练习 1：在同一拖放区使用两个字段1. 确定“数据透视表字段列表”已经显示。 如果看不到字段列表，请单击数据透视表。如果仍然看不到字段列表，请在“数据透视表”工具栏（该工具栏应该浮动显示在工作表上，或者固定在一侧）上单击“显示字段列表”按钮 。如果“数据透视表”工具栏不可见，单击“视图”打开菜单，指向“工具栏”，然后单击“数据透视表”。2. 要按季度比较单个销售人员的销售额，请首先将“订单日期”字段添加到报告的“行”拖放区，这

21、会使得“订单日期”成为报告的第二行字段。操作步骤 从“数据透视表字段列表”将“订单日期”字段拖动到“销售人员”字段的左边。当灰色框出现在单元格 A4 的最左边时则说明该位置是正确的：销售结果按日显示，然后是每天的汇总。在下一次练习中，您将看到如何将每日信息汇总到季度。练习 2：按季度组合订单日期1. 在“订单日期”字段的单元格 A7 中单击鼠标右键。注意，本步骤中不能单击报告中的任何单元格，必须单击包含在“订单日期”字段中的某个单元格。例如，不能单击列 C 的分类汇总，或者分类汇总的标题，例如 A6，否则就不能执行步骤 2。 2. 在快捷键菜单上指向“组及显示明细数据”，单击“组合”。 3.

22、在“组合”对话框中，单击“季度”。如果同时选择了“月”，请单击取消选择它，然后单击“确定”。 现在销售额数据在左边按季度分开，每位销售人员的姓名列在每季度的右边。还记得本课的内部和外部字段吗？在内部字段中的项可以按需重复，但外部字段的项只能显示一次。在本报告中，“销售人员”是内部字段，所以所有销售人员的姓名列在每个季度的旁边。“订单日期”是外部字段，所以一次只列出一个季度。练习 3：重命名订单日期字段要使标题与新的组合匹配，需要重命名订单日期字段。操作步骤 选择“订单日期”字段。键入新名字“季度”，然后按 Enter。练习 4：交换位置以获得不同的视图在本次练习中，您将透视报告，以便按季度比较

23、每个人的业绩。操作步骤 将“季度”字段拖到“销售人员”字段的右边。记住，该字段在报告中是灰色框，包含“季度”。在列 B 与列 C 的中间看到灰色框时释放鼠标键。注意 如果不知道如何操作，请回到本课开始，查看“交换位置可以得到不同的视图”动画。动画会显示如何进行本次练习的操作。透视报告，转换内部与外部字段的位置。在本报告中，“季度”是内部字段，依次列出了所有四个季度。现在“销售人员”是外部字段，所以销售人员的姓名只列出了一次。然后，对报告执行透视操作，以回到原始视图。操作步骤 将“季度”字段拖动到“销售人员”字段的左边。当看到列 A 的左边出现灰色框时释放鼠标键。练习 5：尝试新的技术Bucha

24、nan 在第一季度取得成功的背后是什么呢？在本次练习中，您将发现这个秘密。操作步骤 双击单元格 C5，它包含了 Buchanan 第一季度的销售数据。新工作表 Sheet1 会添加到工作簿中。它包含所有用来汇总以得到 Buchanan 第一季度结果的销售数据。同样的数据在原始源数据中也有，没有从该数据中删除任何东西来构成新的工作表。可以删除或保留新工作表。其他汇总方法练习 1：再次使用同一数据字段并更改字段汇总方式在本次练习中，您将确定每位销售人员每季度的销售数，方法是将“订单金额”字段再次拖动到“数据”拖放区，然后选择不同的汇总函数。 1. 如果有必要，在报告内部任一位置单击以显示“数据透视

25、表字段列表”。 如果仍看不到字段列表，请单击“数据透视表”工具栏上的“显示字段列表”按钮 。2. 在“数据透视表字段列表”上单击“订单金额”，并将它拖动到包含订单金额字段的任何地方。例如，将它拖动到单元格 C6。 该报告现在有两个数据字段：“订单金额总和”与“订单金额总和2”。3. 单击任何标为“订单金额总和2”的单元格，然后单击“数据透视表”工具栏上的“字段设置”按钮 。 4. 在“汇总方式”列表中，单击“计数”，然后单击“确定”。 现在这两个数据字段是“订单金额总和”与“订单金额计数2”。“订单金额计数2”单元格现在显示每位销售人员每季度的销售数。练习 2：格式化报告既然报告不是很方便阅读

26、，并且我们已经完成了透视报告，现在可以更改格式了。1. 单击报告，然后单击“数据透视表”工具栏上的“设置报告格式”按钮 。 2. 在“自动套用格式”对话框中，单击“报告 5”，然后单击“确定”。 现在更改“订单金额计数2”字段的名称。操作步骤 选择“订单金额计数2”字段标题。键入新的名称“订单 #”，然后按 Enter。如果希望列 D 更窄些以放置新的名字，请双击列标题的“D”右边的边界。注意 更改字段标题名称的另一个方法是在报告中选中字段标题，在“数据透视表”工具栏上单击“字段设置”，然后在“数据透视表字段”对话框的“名称”框中键入新的名字。现在，报告看起来好多了，更方便阅读。练习 3：以总

27、和的百分比显示数值数据在本次练习中，您将以总和的百分比显示数据，方法是使用自定义计算来显示每位销售人员的销售额与销售总量之间的关系。可以将“订单金额”字段再次拖动到报告上，但在本次练习中，我们将更改 Excel 计算报告上已有的“订单 #”字段。1. 单击“订单 #”字段中的任一单元格。 2. 在“数据透视表”工具栏上，单击“字段设置”按钮 。 3. 在“汇总方式”列表中，单击“求和”将汇总方式由“计数”更改为“求和”。我们希望显示总计的百分比，而不是计数。 4. 单击“选项”按钮。 5. 在“数据显示方式”列表中，单击箭头，向下滚动列表，然后单击“占总和的百分比”。单击“确定”。 订单数现在

28、以订单总数的百分比显示。现在将字段标题从“订单 #”更改为“订单 %”。也可以将“订单金额”更改为“订单 ￥”以节省一点空间。请参看练习 2 最后的更改名称步骤。提示 如果您希望显示百分比，但没有两位小数，请单击“订单 %”字段标题，再单击“数据透视表”工具栏上的“字段设置”按钮 ，然后单击“数字”。在“小数位数”列表中单击箭头以显示“0”。然后单击“确定”两次。更多数据透视表计算练习 1：确定奖金百分比销售人员每季度会得到 7% 的奖金，但今年每个销售额超过 ￥25,000 的销售人员会在任何季度获得 10% 的奖金。在本次练习中您将使用计算字段输入公式。这里是公式： =IF(订单金额250

29、00,10%,7%)1. 单击报告内部。然后在“数据透视表”工具栏上单击“数据透视表”，指向“公式”，然后单击“计算字段”。 （如果菜单上看不到“公式”，请单击底部的展开按钮将菜单展开。)2. 在“名称”框中输入公式名。例如，输入“奖金 %”。 3. 删除“公式”框中的文字。 4. 在框中，输入=IF(。 5. 在“字段”列表中，单击“订单金额”，然后单击“插入字段”。 注意 尽管“订单金额”字段重新命名为“订单 ￥”，但该字段名在“插入计算字段”对话框中仍为“订单金额”。6. 接着输入公式的其他部分：25000,10%,7%)，然后单击“确定”。 名为“奖金 %”的新字段将插入报告中。结果看

30、上去不像百分比，不是吗？带有美圆符号，而不是百分号。接着，将奖金百分比格式化。操作步骤 单击“奖金 %”字段内部。例如，单击单元格 D6。单击“数据透视表”工具栏上的“字段设置”按钮 。然后单击“数字”。在“分类”列表中单击“百分比”，并在“小数点”位数列表中单击向下箭头到达“0”（消除小数位数）。单击“确定”两次。练习 2：关闭自动分类汇总Buchanan 四季度的奖金百分比是 10%，虽然她没有每个季度都达到 10%。 Excel 将列 C 中的 Buchanan 的四季度销售额累加起来，因为自动分类汇总打开了。又由于总和大于 ￥25,000，所以计算字段公式在带有分类汇总的行上输入 10

31、%。为了避免混淆，应该关闭分类汇总。1. 单击“销售人员”字段中的任何地方，因为您希望关闭该字段的分类汇总。例如，单击单元格 A6。 2. 在“数据透视表”工具栏上单击“数据透视表”，然后单击“分类汇总”清除复选标记。（如果在菜单上看不到“分类汇总”，请单击底部的展开按钮以展开菜单。） 这样将关闭分类汇总。练习 3: 确定奖金额创建另一个计算字段公式以确定每位销售人员每季度的奖金额。这里是公式：=订单金额*奖金 %操作步骤 在“数据透视表”工具栏上，单击“数据透视表”，指向“公式”，然后单击“计算字段”。在“名称”框中输入公式名。例如，键入奖金 ￥。删除“公式”框中的文本。在“公式”框中，输入公式：=订单金额*奖金 %提示 在“字段”列表中，单击“订单金额”，然后单击“插入字段”（或者只是双击“订单