Excel 数据透视表过滤数据
你可能必须对数据透视表数据的子集进行深入分析。这可能是因为你拥有大量数据,并且需要关注数据的较小部分,或者无论数据大小如何,都需要关注某些特定数据。你可以根据一个或多个字段的值的子集过滤数据透视表中的数据。有以下几种方法可以做到这一点:
- 使用切片器过滤。
- 使用报告过滤器过滤。
- 手动过滤数据。
- 使用标签过滤器过滤。
- 使用值过滤器进行过滤。
- 使用日期过滤器过滤。
- 使用 Top 10 过滤器进行过滤。
- 使用时间轴过滤。
你将在下一章学习使用切片器过滤数据。你将了解本章上面提到的其他方法的过滤。
考虑以下数据透视表,其中你有汇总的销售数据区域、销售人员和月份。
报告过滤器
你可以将筛选器分配给其中一个字段,以便你可以根据该字段的值动态更改数据透视表。
将区域从行拖到数据透视表区域中的筛选器。
带有区域标签的过滤器出现在数据透视表上方(如果你的数据透视表上方没有空行,数据透视表将被下推以为过滤器腾出空间。
你会观察到
-
销售人员值显示在行中。
-
月份值显示在列中。
-
区域过滤器出现在顶部,默认选择为 ALL。
-
汇总值是订单金额的总和。
-
销售人员的订单金额总和出现在总计列中。
-
每月订单金额总和出现在总计行中。
-
-
单击过滤器区域右侧框中的箭头。
将出现一个下拉列表,其中包含字段 Region 的值。选中该框 选择多个项目 .
默认情况下,所有框都被选中。取消选中该框 ( All )。所有的框都将被取消选中。
然后选中复选框 - South 和 West,然后单击 OK。
将仅汇总与南部和西部地区有关的数据。
在过滤区域旁边的单元格中显示 - (Multiple Items),表示你选择了多个项目。但是,从显示的报告中不知道有多少项目和/或哪些项目。在这种情况下,使用 Slicers 是更好的过滤选择。
手动过滤
你还可以通过手动选择字段的值来过滤数据透视表。你可以通过单击箭头来执行此操作 在“行标签”或“列标签”单元格中。
假设你只想分析 2 月份的数据。你需要按字段“月”过滤值。如你所见,月份是列标签的一部分。
点击箭头 在列标签单元格中。
如你所见,下拉列表中有一个搜索框,在该框下方,你可以看到所选字段的值列表,即月份。所有值的框都被选中,表明该字段的所有值都被选中。
-
取消选中值列表顶部的(全选)框。
-
选中要在数据透视表中显示的值的框,在本例中为二月,然后单击确定。
数据透视表仅显示与所选月份字段值相关的值 - 二月。可以观察到过滤箭头变为图标 表示应用了过滤器。将光标放在 icon.
你可以看到显示的内容表明手动过滤器已应用于字段 - 月。
如果要更改过滤器选择值,请执行以下操作:
-
点击 icon.
-
选中/取消选中值的框。
如果该字段的所有值在列表中均不可见,请拖动下拉列表右下角的手柄以将其放大。或者,如果你知道该值,请在“搜索”框中键入它。
假设你想在上面过滤的数据透视表上应用另一个过滤器。例如,你想要显示 Walters, Chris 2 月份的数据。你需要通过为字段 Salesperson 添加另一个过滤器来优化你的过滤。如你所见,销售员是行标签的一部分。
-
点击箭头 在行标签单元格中。
显示字段值列表 - 区域。这是因为,Region 在嵌套顺序中位于 Salesperson 的外层。你还有一个附加选项——选择字段。单击选择字段框。
-
从下拉列表中单击销售人员。将显示字段值列表 - 销售人员。
-
取消选中(全选)并选中 Walters, Chris。
-
单击确定。
数据透视表仅显示与所选月份字段值相关的值 - 二月和销售人员字段值 - Walters, Chris。
行标签的过滤箭头也更改为图标 表示应用了过滤器。将光标放在 行标签或列标签上的图标。
将显示一个文本框,指示手动筛选器应用于字段 - 月份和销售人员。
因此,你可以根据任意数量的字段和任意数量的值手动过滤数据透视表。
按文本过滤
如果你有包含文本的字段,则可以按文本过滤数据透视表,前提是相应的字段标签是基于文本的。例如,考虑以下 Employee 数据。
数据包含员工的详细信息 - EmployeeID、Title、BirthDate、MaritalStatus、Gender 和 HireDate。此外,数据还具有员工的经理级别(级别 0 - 4)。
假设你必须对按职称向给定员工报告的员工人数进行一些分析。你可以创建如下所示的数据透视表。
你可能想知道有多少头衔中带有“经理”的员工有员工向他们汇报。由于标签标题是基于文本的,你可以在标题字段上应用标签过滤器,如下所示:
-
点击箭头 在行标签单元格中。
-
从下拉列表中选择“选择字段”框中的“标题”。
-
单击标签过滤器。
-
单击第二个下拉列表中的包含。
出现标签过滤器(标题)对话框。在包含旁边的框中键入 Manager。单击确定。
数据透视表将被过滤为包含“经理”的标题值。
-
点击 icon.
你可以看到 显示如下:
- 标签过滤器应用于字段 - 标题,以及
- 应用的标签过滤器是什么。
按值过滤
你可能想知道向他们报告的员工超过 25 名的员工的头衔。为此,你可以在 Title 字段上应用 Value Filter,如下所示:
-
点击箭头 在行标签单元格中。
-
Select Title 在下拉列表中的“选择字段”框中。
-
单击值过滤器。
-
从第二个下拉列表中选择大于或等于。
出现值过滤器(标题)对话框。在右侧框中键入 25。
将过滤数据透视表以显示向其报告的员工超过 25 名的员工职位。
按日期过滤
你可能希望显示在 2015-15 财政年度雇用的所有员工的数据。你可以使用数据过滤器,如下所示:
-
在数据透视表中包含 HireDate 字段。现在,你不需要经理数据,因此从数据透视表中删除 ManagerLevel 字段。
现在你在数据透视表中有一个日期字段,你可以使用日期筛选器。
-
点击箭头 在行标签单元格中。
-
从下拉列表的选择字段框中选择 HireDate。
-
单击日期过滤器。
-
Seelct Between 从第二个下拉列表中。
出现日期过滤器 (HireDate) 对话框。在两个日期框中键入 4/1/2014 和 3/31/2015。单击确定。
数据透视表将被过滤以仅显示 HireDate 介于 1 之间的数据 st 2014 年 4 月和 31 日 st 2015 年 3 月。
你可以将日期分组为季度,如下所示:
-
右键单击任何日期。这 Grouping 对话框出现。
-
在“开始于”框中键入 4/1/2014。选中该框。
-
在结尾处输入 3/31/2015。选中该框。
-
在下面的框中单击季度 By .
日期将在数据透视表中按季度分组。你可以通过将字段 HireDate 从 ROWS 区域拖动到 COLUMNS 区域来使表格看起来紧凑。
你将能够知道本财年按季度雇用了多少员工。
使用前 10 个过滤器进行过滤
你可以使用前 10 个筛选器来显示数据透视表中某个字段的前几个或后几个值。
-
点击箭头 在行标签单元格中。
-
单击值过滤器。
-
单击第二个下拉列表中的前 10 个。
出现前 10 个过滤器(标题)对话框。
-
在第一个框中,单击顶部(你也可以选择底部)。
-
在第二个框中,输入一个数字,例如 7。
-
在第三个框中,你可以通过三个选项进行筛选。
-
单击项目以按项目数过滤。
-
单击百分比以按百分比过滤。
-
单击 Sum 以按总和过滤。
-
-
由于你有 EmployeeID 的计数,请单击 Items。
-
在第四个框中,单击 EmployeeID 计数字段。
-
单击确定。
EmployeeID 计数的前七个值将显示在数据透视表中。
如你所见,本财年招聘人数最多的是生产技术人员,其中大部分是在第一季度。
使用时间轴过滤
如果你的数据透视表有日期字段,你可以使用时间轴过滤数据透视表。
从你之前使用的员工数据创建数据透视表,并将数据添加到“创建数据透视表”对话框中的数据模型。
-
将字段 Title 拖到 ROWS 区域。
-
将字段 EmployeeID 拖到 ∑ VALUES 区域并选择计数进行计算。
-
单击数据透视表。
-
单击插入选项卡。
-
单击过滤器组中的时间轴。将出现“插入时间线”对话框。
- 选中 HireDate 框。
- 单击确定。时间线出现在工作表中。
- 时间线工具出现在功能区上。
如你所见,所有期间 - 以月为单位显示在时间轴上。
-
单击 - MONTHS 旁边的箭头。
-
从下拉列表中选择季度。时间线显示更改为所有期间 - 以季度为单位。
-
点击 2014 年第一季度。
-
按住 Shift 键并拖动到 2014 Q4。时间线周期选择为 2014 年第一季度至第四季度。
-
数据透视表被过滤到此时间线周期。
清除过滤器
你可能需要不时清除你设置的过滤器,以便在数据的不同组合和投影之间切换。你可以通过以下几种方式执行此操作:
清除数据透视表中的所有筛选器
你可以一次性清除数据透视表中设置的所有过滤器,如下所示:
- 单击功能区上的主页选项卡。
- 单击编辑组中的排序和筛选。
- 从下拉列表中选择清除。
清除标签、日期或值过滤器
要清除标签、日期或值过滤器,请执行以下操作:
-
单击行标签或列标签中的图标。
-
点击 <字段名称>,你要从中清除下拉列表中选择字段框中的过滤器。
-
单击下拉列表中显示的从
清除过滤器。 -
单击确定。特定过滤器将被清除。