分类：Excel2019函数公式大全

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

STEP01：打开“数据透视表4.xlsx”工作簿，切换至“数据透视表”工作表。本例中要对汇总结果进行筛选的数据透视表如图23-50所示。

STEP02：选择“步步高”所在的单元格，即A5单元格，单击鼠标右键，在弹出的隐藏菜单中选择“筛选”选项，然后在展开的级联列表中选择“仅保留所选项目”选项，如图23-51所示。筛选后的结果如图23-52所示。

图23-50　数据透视表

图23-51　选择“仅保留所选项目”选项

STEP03：如图23-53所示，在A5单元格处再次单击鼠标右键，在弹出的隐藏菜单中选择“筛选”选项，然后在展开的级联列表中选择“从‘产品名称’中清除筛选”选项，即可清除筛选结果，此时数据透视表将恢复到打开时的状态。

如果要对数据透视表中的业务员进行筛选，例如筛选出业务员“陈圆圆”和“李红艳”的相关数据，具体操作步骤如下。

STEP01：单击“列标签”处的筛选按钮，在展开的下拉列表中取消勾选“全选”复选框，然后依次勾选“陈圆圆”复选框和“李红艳”复选框，最后单击“确定”按钮即可返回筛选结果，如图23-54所示。

STEP02：此时，筛选结果如图23-55所示。工作表中只显示了“陈圆圆”和“李红艳”两位业务员的销售数据。

图23-52　筛选结果

图23-53　清除筛选

图23-54　筛选业务员

图23-55　业务员筛选结果

STEP03：再次单击“列标签”处的筛选按钮，在展开的下拉列表中选择“从‘业务员’中清除筛选”选项即可清除当前的筛选结果，如图23-56所示。

如果要在数据透视表中筛选出销售额大于或等于30000的业务员，具体操作步骤如下。

STEP01：单击“列标签”处的筛选按钮，在展开的下拉列表中选择“值筛选”选项，然后在展开的级联列表中选择“大于或等于”选项，如图23-57所示。

STEP02：随后会打开如图23-58所示的“值筛选（业务员）”对话框，在数值条件文本框中输入“30000”，单击“确定”按钮便可以返回工作表。此时，筛选结果如图23-59所示。

图23-56　清除业务员筛选结果

图23-57　选择值筛选条件

STEP03：随后会打开如图23-58所示的“值筛选（业务员）”对话框，在数值条件文本框中输入“30000”，单击“确定”按钮便可以返回工作表。此时，筛选结果如图23-59所示。

图23-58　输入筛选条件

图23-59　筛选结果

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

STEP01：打开“数据透视表3.xlsx”工作簿，切换至“数据透视表”工作表。在数据透视表的数值区域选择任意单元格，如B5单元格，单击鼠标右键，在展开的下拉列表中选择“值汇总依据”选项，然后在展开的级联列表中选择“计数”选项，如图23-46所示。

STEP02：更改汇总方式后的数据透视表如图23-47所示。此时的数据透视表按“计数”方式进行汇总。

图23-46　更改值汇总方式

图23-47　计数汇总结果

也可以通过“值字段设置”命令更改汇总方式，具体操作步骤如下。

STEP01：选择B5单元格，单击鼠标右键，在展开的下拉列表中选择“值字段设置”选项，打开“值字段设置”对话框，如图23-48所示。

STEP02：打开“值字段设置”对话框后，切换至“值汇总方式”选项卡，在“计算类型”列表框中选择“计数”选项，然后单击“确定”按钮即可完成数据透视表汇总方式的更改，如图23-49所示。

图23-48　选择“值字段设置”选项

图23-49　选择计算类型

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

STEP01：打开“数据透视表3.xlsx”工作簿，切换至“数据透视表”工作表，本例中要进行排序操作的数据透视表如图23-40所示。

STEP02：单击行标签右侧的筛选按钮，在展开的下拉列表中选择“降序”选项，如图23-41所示。经过上述操作，行标签更改为按降序排列，结果如图23-42所示。

图23-40　数据透视表

图23-41　选择“降序”选项

如果要将行标签的排序方式改回升序排列，按以下步骤进行即可实现。

STEP01：单击行标签右侧的筛选按钮，在展开的下拉列表中选择“其他排序选项”命令，打开“排序（产品名称）”对话框，如图23-43所示。

图23-42　行标签降序排列

图23-43　选择“其他排序选项”命令

STEP02：打开“排序（产品名称）”对话框后，在“排序选项”列表区域中单击选中“升序排序（从A到Z）依据”单选按钮，然后单击下方选择框右侧的下拉按钮，在展开的下拉列表中选择“产品名称”选项，最后单击“确定”按钮将产品名称（行标签）的排序方式改回升序排列，如图23-44所示。

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

图23-44　设置排序方式

图23-45　“其他排序选项（产品名称）”对话框

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

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

打开“数据透视表.xlsx”工作簿，切换至“Sheet4”工作表。选择数据透视表区域中的任意单元格，如B4单元格，单击鼠标右键，在弹出的隐藏菜单中选择“隐藏字段列表”选项，便可以将“数据透视表字段”窗格进行隐藏，如图23-38所示。

如果要将隐藏的字段列表显示出来，可以按照以下步骤进行操作。

打开“数据透视表.xlsx”工作簿，切换至“Sheet4”工作表。选择数据透视表区域中的任意单元格，如C4单元格，单击鼠标右键，在弹出的隐藏菜单中选择“显示字段列表”选项即可，如图23-39所示。

图23-38　隐藏字段列表

图23-39　显示字段列表

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

STEP01：打开“数据透视表.xlsx”工作簿，切换至“Sheet1”工作表，该工作表为创建数据透视表的源数据，如图23-34所示。

STEP02：选中C2单元格，修改第一季度可口可乐的销售额，将销售额“¥3000”修改为“¥5000”，如图23-35所示。

图23-34　源数据

图23-35　修改销售额

STEP03：切换至“Sheet4”工作表，即数据透视表所在的工作表，选择B5单元格，单击鼠标右键，在弹出的隐藏菜单中选择“刷新”选项对当前的数据透视表进行刷新，如图23-36所示。刷新后的数据透视表如图23-37所示，透视表中第一季度可口可乐的销售额被修改为“¥5000”。

图23-36　选择“刷新”选项

图23-37　刷新效果

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

打开“数据透视表.xlsx”工作簿，切换至“Sheet4”工作表。选择数据透视表区域中的任意单元格，如C4单元格，切换至“数据透视表分析”选项卡，在“数据透视表”组中单击“数据透视表名称”文本框，使文本框处于可编辑的状态。然后在文本框中输入新的名称，如“销售分析表”，输入完成后按“Enter”返回即可，如图23-33所示。

图23-33　输入名称

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

打开“数据透视表.xlsx”工作簿，切换至“Sheet4”工作表。选择数据透视表区域中的任意单元格，如B2单元格，切换至“数据透视表分析”选项卡，在“操作”组中单击“清除”下三角按钮，在展开的下拉列表中选择“全部清除”选项，如图23-31所示。

使用“全部清除”命令可以快速重新设置数据透视表，但不会删除数据透视表。如图23-32所示，“全部清除”之后，数据透视表的数据连接、位置和缓存仍然保持不变。如果存在与数据透视表关联的数据透视图，则“全部清除”命令还会删除相关的数据透视图字段、图表自定义和格式。

图23-31　选择“全部清除”选项

图23-32　清除数据透视表效果

要注意的是，如果在两个或多个数据透视表之间共享数据连接或使用相同的数据，然后对其中一个数据透视表使用“全部清除”命令，则同时还会删除其他共享数据透视表中的分组、计算字段或项及自定义项。但是，Excel在删除其他共享数据透视表中的项之前会发出警告，此时可以取消该操作。

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

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

打开“数据透视表.xlsx”工作簿，切换至“Sheet4”工作表。选择数据透视表区域中的任意单元格，如A3单元格，切换至“数据透视表分析”选项卡，单击“操作”组中的“选择”下三角按钮，在展开的下拉列表中选择“整个数据透视表”选项将整个数据透视表选中，然后按“Delete”键便可将选中的数据透视表直接删除。

有时可能需要移动数据透视表的位置，以便在原来的位置插入工作表单元格、行或列等其他内容。以“数据透视表.xlsx”工作簿中的数据为例，移动数据透视表的具体操作步骤如下。

STEP01：打开“数据透视表.xlsx”工作簿，切换至“Sheet4”工作表。选择数据透视表区域中的任意单元格，如C5单元格，切换至“数据透视表分析”选项卡，在“操作”组中单击“移动数据透视表”按钮，打开“移动数据透视表”对话框，如图23-28所示。

图23-28　单击“移动数据透视表”按钮

STEP02：打开“移动数据透视表”对话框后，在“选择放置数据透视表的位置”列表区域中单击选中“现有工作表”单选按钮，选择移动位置为“Sheet4!$A$11”单元格，然后单击“确定”按钮完成数据透视表的移动操作，如图23-29所示。移动后的效果如图23-30所示。

图23-29　选择移动位置

图23-30　移动数据透视表效果

通过复制数据透视表，可以有效地备份数据。以“数据透视表.xlsx”工作簿中的数据为例，复制数据透视表的具体操作步骤如下。

STEP01：打开“数据透视表.xlsx”工作簿，切换至“Sheet4”工作表。选择数据透视表区域中的任意单元格，如A3单元格，切换至“数据透视表分析”选项卡，在“操作”组中单击“选择”下三角按钮，在展开的下拉列表中选择“整个数据透视表”选项将整个数据透视表选中，如图23-24所示。

STEP02：在选中的数据透视表区域处单击鼠标右键，在弹出的隐藏菜单中选择“复制”选项，如图23-25所示。

STEP03：此时，数据透视表周围出现一个虚框。选择要将数据透视表复制到的位置所在单元格，如A11单元格，然后单击鼠标右键，在弹出的隐藏菜单中选择“粘贴”选项，如图23-26所示。复制后的效果如图23-27所示。

图23-24　选择“整个数据透视表”选项

图23-25　复制数据透视表

图23-26　选择粘贴位置

图23-27　复制数据透视表效果