Excel 数据透视表筛选汇总结果

在使用数据透视表时,除了可以对汇总数据进行排序和更改汇总方式之外,还可以对汇总的结果进行筛选。使用筛选功能可以完成许多复杂的操作,下面通过实例说明筛选汇总结果的具体操作步骤。

步骤1:打开要对汇总结果进行筛选的数据透视表,如图24-35所示。

步骤2:右击“步步高”,然后指向弹出菜单中的“筛选”命令,并从子菜单中选择一种筛选方式,例如此处单击“仅保留所选项目”命令,如图24-36所示。筛选结果如图24-37所示。

图24-35 打开数据透视表

图24-36 单击“仅保留所选项目”命令

步骤3:右击“步步高”,然后选择“筛选”|“从‘产品名称’中清除筛选”命令,清除筛选结果,此时数据透视表将恢复到打开时的状态。

步骤4:单击“业务员”右侧的箭头,然后在弹出框中只选中“陈圆圆”和“李红艳”两个复选框,如图24-38所示。

图24-37 筛选结果

图24-38 只选中“陈圆圆”和“李红艳”两个复选框

步骤5:单击“确定”按钮,可以看到筛选结果如图24-39所示。

步骤6:单击“业务员”右侧的箭头,然后在弹出框中单击“从‘业务员’中清除筛选”命令,清除筛选结果。

步骤7:单击“业务员”右侧箭头,然后在弹出框中选择“值筛选”|“大于”命令,如图24-40所示。

步骤8:在“值筛选(业务员)”对话框中设置各选项,以筛选销售额大于或等于30000的业务员,如图24-41所示。

步骤9:单击“确定”按钮,可以看到筛选结果如图24-42所示。

图24-39 筛选结果

图24-40 选择“值筛选”|“大于”命令

图24-41 设置值筛选选项

图24-42 筛选结果

Excel 更改数据透视表的汇总方式

默认情况下,数据透视表的汇总方式为求和汇总,也可以根据需要将其更改为其他汇总方式,如平均值、最大值、最小值、计数等。下面通过实例说明如何更改数据透视表的汇总方式。

步骤1:打开要更改汇总方式的数据透视表。

步骤2:右击数值区域的任一单元格,然后指向弹出菜单中的“值汇总依据”,从子菜单中选择一种汇总方式,如此处单击“计数”命令,如图24-32所示。更改汇总方式后的数据透视表如图24-33所示。

提示:也可以右击数值区域的任一单元格,然后单击弹出菜单中的“值字段设置”命令,打开“值字段设置”对话框,在“值汇总方式”选项卡“计算类型”列表中选择一种汇总方式,如图24-34所示。

图24-32 单击“计数”命令

图24-33 更改汇总方式后的数据透视表

图24-34 从“计算类型”列表中选择一种汇总方式

Excel 更改数据透视表的排序方式

在数据透视表中可以方便地对数据进行排序,下面通过实例介绍具体操作步骤。

步骤1:打开要进行排序操作的数据透视表文件,如图24-27所示。

步骤2:单击“产品名称”列右侧箭头,然后单击弹出菜单中的“降序”命令,如图24-28所示。

图24-27 打开数据透视表文件

图24-28 单击“降序”命令

步骤3:经过以上操作,产品名称更改为按降序排列,如图24-29所示。

图24-29 产品名称更改为按降序排列

步骤4:单击“产品名称”列右侧箭头,然后单击弹出菜单中的“其他排序选项”命令,打开“排序(产品名称)”对话框。

提示:也可以单击“产品名称”列下方的任一单元格,然后选择“数据透视表工具”|“选项”|“排序”命令,打开“排序(产品名称)”对话框。

步骤5:选中“升序排序(从A到Z)依据”单选按钮,并在下方列表中选择“产品名称”,如图24-30所示。

步骤6:单击“确定”按钮,将产品名称的排序方式改回升序。

提示:如果要设置更多的排序选项,可以单击“排序(产品名称)”对话框中的“其他选项”按钮,打开“其他排序选项(产品名称)”对话框,然后设置自动排序、主关键字排序次序、排序依据、方法等选项,如图24-31所示。例如,如果希望每次更新报表时都自动排序数据,则选中“每次更新报表时自动排序”复选框。

图24-30 选中“升序排序(从A到Z)依据”单选按钮

图24-31 “其他排序选项(产品名称)”对话框

Excel 显示与隐藏字段列表

在默认情况下,当选中数据透视表中的任一单元格时,在窗口右侧就会显示数据透视表字段列表。如果数据透视表占用屏幕空间比较大,而暂时又不需要使用字段列表时,可以将其隐藏,当需要时再将其显示出来。

如果要隐藏字段列表,可以按照以下步骤进行操作。

步骤1:右击数据透视表中的任一单元格。

步骤2:单击弹出菜单中的“隐藏字段列表”命令,如图24-25所示。隐藏字段列表后的窗口如图24-26所示。

图24-25 单击“隐藏字段列表”命令

图24-26 隐藏字段列表后的窗口

如果要显示字段列表,可以按照以下步骤进行操作。

步骤1:右击数据透视表中的任一单元格。

步骤2:单击弹出菜单中的“显示字段列表”命令。

Excel 刷新数据透视表

如果修改了工作表中数据透视表的源数据,数据透视表并不会自动随之发生相应的变化,需要用户手动进行刷新,下面通过实例介绍刷新数据透视表的具体操作步骤。

步骤1:打开包含要修改的数据透视表及源数据的工作表,如图24-22所示。

步骤2:修改工作表中的源数据,如图24-23所示。

图24-22 打开工作表

图24-23 修改源数据

步骤3:右击数据透视表中的任意单元格,然后单击弹出菜单中的“刷新”命令。刷新数据透视表前后的结果如图24-24所示。

图24-24 刷新数据透视表前后的结果

Excel 重命名数据透视表

在创建数据透视表时,默认情况下Excel会使用“数据透视表1”、“数据透视表2”这样的名称为数据透视表命名,可以更改数据透视表的名称以使其更有意义,具体操作步骤如下。

步骤1:单击要重命名的数据透视表。

步骤2:在“分析”选项卡的“数据透视表”组中,单击“数据透视表名称”文本框。

步骤3:输入新的名称。

步骤4:按Enter键。

Excel 清除与删除数据透视表

如果要从数据透视表中删除所有的报表筛选、行标签和列标签、值以及格式,然后重新设计数据透视表的布局,可以使用“全部清除”命令,具体操作步骤如下。

步骤1:单击要清除的数据透视表。

步骤2:单击“分析”选项卡中“操作”组中的“清除”|“全部清除”命令。

使用“全部清除”命令可以快速重新设置数据透视表,但不会删除数据透视表。

“全部清除”之后,数据透视表的数据连接、位置和缓存仍然保持不变。如果存在与数据透视表关联的数据透视图,则“全部清除”命令还会删除相关的数据透视图字段、图表自定义和格式。

要注意的是,如果在两个或多个数据透视表之间共享数据连接或使用相同的数据,然后对其中一个数据透视表使用“全部清除”命令,则同时还会删除其他共享数据透视表中的内容。但是,如果在Excel试图删除其他共享数据透视表中的项之前发出警告,则可以取消该操作。

提示:如果包含数据透视表的工作表有保护,则不会显示“全部清除”命令。如果为工作表设置了保护,并选中了“保护工作表”对话框中的“使用数据透视表”复选框,则“全部清除”命令将无效,因为“全部清除”命令需要刷新操作。

如果要删除数据透视表,则可以按照以下步骤进行操作。注意体会“全部清除”与“删除”的不同。

步骤1:单击要删除的数据透视表。

步骤2:单击“分析”选项卡中“操作”组中的“选择”|“整个数据透视表”命令。

步骤3:按Delete键。

Excel 移动数据透视表

有时可能需要移动数据透视表的位置,以便在原来的位置插入工作表单元格、行或列等其他内容。如果要移动数据透视表,可以按照以下步骤进行操作。

步骤1:单击要移动的数据透视表。

步骤2:单击“分析”选项卡“操作”组中的“移动数据透视表”命令,打开“移动数据透视表”对话框,如图24-21所示。

图24-21 “移动数据透视表”对话框

步骤3:在“选择放置数据透视表的位置”下,执行以下操作之一。

  • 如果要将数据透视表放入一个新的工作表(从单元格A1开始),则单击“新工作表”单选按钮。
  • 如果要将数据透视表放入现有工作表,则选择“现有工作表”单选按钮,然后在要放入数据透视表的单元格区域中单击第一个单元格。

步骤4:单击“确定”按钮。

Excel 复制数据透视表

通过复制数据透视表,可以有效地备份数据。如果要复制数据透视表,可以按照以下步骤进行操作。

步骤1:打开要复制的数据透视表,并单击数据透视表中的任一单元格,此时在功能区将显示“数据透视表工具”。

步骤2:单击“分析”选项卡中“操作”组的“选择”|“整个数据透视表”命令,将整个数据透视表选中,如图24-19所示。

图24-19 单击“整个数据透视表”命令

步骤3:在数据透视表中右击,然后单击弹出菜单中的“复制”命令,此时的数据透视表周围出现一个虚框,如图24-20所示。

图24-20 数据透视表周围出现一个虚框

步骤4:选择要将数据透视表复制到的位置所在的单元格,然后右击单元格并单击弹出菜单中的“粘贴”命令,将数据透视表粘贴到该位置。

Excel 自定义数据透视表的字段与布局应用技巧

创建数据透视表后,可以使用数据透视表字段列表向数据透视表中添加或删除字段,从而更改数据透视表的布局。数据透视表字段列表可以停靠在窗口的任一段,也可以取消停靠,而且不管停靠与否,都可以改变其大小以方便操作。

添加数据透视表字段

如果要将字段添加到数据透视表中,可以执行下列操作之一。

·在数据透视表字段列表的字段部分中选中要添加的字段旁边的复选框。此时字段会放置在布局部分的默认区域中,也可以在需要时重新排列这些字段。

提示:默认情况下,非数值字段会被添加到“行标签”区域,数值字段会被添加到“值”区域,而OLAP日期和时间字段会被添加到“列标签”区域。

·在数据透视表字段列表的字段部分右键单击字段名称,然后从弹出菜单中选择相应的命令:“添加到报表筛选”、“添加到列标签”、“添加到行标签”和“添加到值”,从而将该字段放置在布局部分中的某个特定区域中,如图24-6所示。

·在数据透视表字段列表的字段部分单击并按住某个字段名,然后将其拖放到布局部分中的某个区域。如果要多次添加某个字段,则重复该操作。

图24-6 选择将字段添加到的区域

删除数据透视表字段

如果要删除数据透视表字段,可以执行下列操作之一。

  • 在布局区域之一中单击字段名称,然后从弹出菜单中单击“删除字段”命令。
  • 清除字段部分中各个字段名称旁边的复选框。

提示:在字段部分中清除复选框会从报表中删除该字段的所有实例。

例如,清除字段部分“季度”字段左侧的复选框前后的数据透视表,如图24-7所示。

图24-7 删除字段前后的数据透视表

改变数据透视表字段列表的视图方式

数据透视表共有5种不同的视图方式,在修改数据透视表字段时,可以更改视图以满足不同的需要。如果要改变视图,可以单击数据透视表字段列表顶部的“视图”按钮,然后从弹出菜单中选择下列选项之一,如图24-8所示。

·字段节和区域节层叠:这是默认视图,是为少量字段而设计的。

图24-8 5种不同的视图方式

  • 字段节和区域节并排:当在各区域中有四个以上字段时可以使用这种视图,如图24-9所示。
  • 仅字段节:此视图是为添加和删除多个字段而设计的,如图24-10所示。

图24-9 字段节和区域节并排

  • 仅2×2区域节:此视图只是为重新排列多个字段而设计的,如图24-11所示。
  • 仅1×4区域节:此视图只是为重新排列多个字段而设计的,如图24-12所示。

图24-10 仅字段节

图24-11 仅2×2区域节

提示:在“字段节和区域节层叠”和“字段节和区域节并排”视图中,可以调整每一部分的宽度和高度以方便查看与操作,方法是将鼠标指针悬停在两个部分的分隔线上,当指针变为垂直双箭头“”或水平双箭头“”时,将双箭头向上下左右拖动到所需位置,然后单击双箭头或按Enter键。

图24-12 仅1×4区域节

设置数据透视表选项

创建数据透视表后,可以像设置单元格格式一样设置数据透视表的选项,如果要打开“数据透视表选项”对话框,可以按照以下步骤进行操作。

步骤1:打开数据透视表,右击数据透视表中的任一单元格。

步骤2:单击弹出菜单中的“数据透视表选项”命令,如图24-13所示。此时将打开“数据透视表选项”对话框,如图24-14所示。

图24-13 单击“数据透视表选项”命令

图24-14 “数据透视表选项”对话框

打开“数据透视表选项”对话框后,即可根据需要设置数据透视表的布局和格式、汇总和筛选、显示、打印、数据等选项。

字段设置

字段设置可以控制数据透视表中字段的各种格式、打印、分类汇总和筛选器设置。如果要进行字段设置,可以按照以下步骤进行操作。

步骤1:打开要进行字段设置的数据透视表。

步骤2:在数据透视表字段列表中单击要进行设置的字段,然后单击弹出菜单中的“字段设置”命令;或者右击数据透视表中要进行设置的字段名称,然后单击弹出菜单中的“字段设置”命令,如图24-15所示。

步骤3:在“字段设置”对话框中选择不同的选项卡,并设置相关选项,如图24-16所示。

图24-15 单击弹出菜单中的“字段设置”命令

图24-16 “字段设置”对话框

值字段设置

如果要格式化数据透视表中的数据,以便将数据统一为相同样式,可以进行值字段设置,下面介绍具体操作步骤。

步骤1:打开要进行字段设置的数据透视表。

步骤2:右击数据透视表中包含数值数据的任一单元格,然后单击弹出菜单中的“值字段设置”命令,如图24-17所示。

图24-17 单击弹出菜单中的“值字段设置”命令

步骤3:在“值字段设置”对话框中选择不同的选项卡,并设置相关选项,如图24-18所示。

图24-18 “值字段设置”对话框