跳至主要内容

Kutools for Office — 一套工具，五种功能。事半功倍。

如何根据Excel中的特定单元格值筛选数据透视表？

Author Siluvia Last modified

在Excel中，数据透视表被广泛用于高效地汇总、分析和探索数据。默认情况下，数据透视表内的筛选通常是通过从筛选下拉菜单中选择所需的项目来完成的。尽管这种方法提供了灵活性，但在某些场景下需要更动态的筛选方法——例如，您可能希望数据透视表的结果根据某个工作表单元格中输入的值自动更改。这在准备仪表板、自动化工作流程或为不熟悉手动筛选的最终用户构建交互式报告时特别有用。

Excel 并未提供直接将单元格值与数据透视表筛选器关联的标准功能（不使用代码的情况下）。然而，有几种实用的技术可以满足这一需求，每种方法都有其自身的优势和需要注意的地方。本教程首先介绍一种直接的 VBA 方法，通过它可将单元格与数据透视表筛选器连接起来，当单元格值发生变化时，数据透视表会即时更新。此外，我们还将介绍其他替代方法，例如使用 Excel 公式（如 GETPIVOTDATA、FILTER）显示筛选结果，以及使用切片器作为图形化筛选控件。了解这些选项有助于您为自己的 Excel 工作流和用户体验选择最佳方法。

A screenshot showing a Pivot Table with a drop-down filter in Excel

使用 VBA 代码根据特定单元格值筛选数据透视表

如果您希望实现真正的动态交互——即在单元格中输入一个值时，数据透视表筛选器会自动响应此变化——VBA 提供了直接解决方案。这在仪表板、为同事准备的模板或需要通过更改单个单元格快速调整筛选条件的情况下非常有用。不过，该方法需要对 VBA 编辑器有一定的基本了解，并且，与其他所有宏一样，您的工作簿必须保存为启用宏的格式（.xlsm）。

以下 VBA 代码允许您将工作表单元格动态链接到数据透视表筛选器。请仔细按照以下步骤操作，并根据您的工作簿需求修改工作表名称、数据透视表名称和字段引用：

步骤 1: 在工作表单元格中输入您想用作筛选条件的值（例如，在单元格 H6 中键入或选择筛选值）

步骤 2: 打开包含目标数据透视表的工作表。右键单击 Excel 底部的表格标签，然后从上下文菜单中选择“查看代码”。这将打开工作表的 VBA 编辑器窗口。

A screenshot showing the View Code option for a worksheet in Excel

步骤 3: 在打开的 Microsoft Visual Basic for Applications (VBA) 窗口中，将以下代码粘贴到工作表的代码模块中（而不是标准模块）：

VBA 代码：根据单元格值筛选数据透视表

Private Sub Worksheet_Change(ByVal Target As Range)
'Update by Extendoffice 20180702
    Dim xPTable As PivotTable
    Dim xPFile As PivotField
    Dim xStr As String
    On Error Resume Next
    If Intersect(Target, Range("H6:H7")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Set xPTable = Worksheets("Sheet1").PivotTables("PivotTable2")
    Set xPFile = xPTable.PivotFields("Category")
    xStr = Target.Text
    xPFile.ClearAllFilters
    xPFile.CurrentPage = xStr
    Application.ScreenUpdating = True
End Sub

📝 注意事项：

  • “Sheet1” 是包含数据透视表的工作表。根据需要进行调整。
  • “PivotTable2” 是您的数据透视表名称。您可以在“数据透视表分析”选项卡中找到它。
  • “Category” 是要筛选的字段。它必须与字段名称完全匹配。
  • H6 是筛选单元格。确保其值与筛选列表中的某一项匹配。
  • 筛选值必须逐字符匹配。多余的空格或拼写错误可能导致错误或空白结果。

步骤 4: 按 Alt + Q 关闭 VBA 编辑器并返回 Excel。

现在，您的数据透视表应该能够根据 H6 单元格中输入的值自动筛选以仅显示符合条件的数据。每当 H6 的值发生变化时，此宏都会运行，便于动态调整数据汇总。

Pivot Table filtered based on a specific cell value

您可以随时修改筛选单元格中的值——每当单元格内容发生更改或替换时，数据透视表将立即更新。

Result of changing the filter cell value for the Pivot Table

故障排除：

  • 确保您的工作簿中已启用宏。
  • 仔细检查工作表、数据透视表和字段名称是否与实际设置一致。
  • 确保 H6 中的筛选值与数据透视表中的值完全匹配。
  • 此 VBA 方法适用于单字段筛选。对于多字段筛选，需要额外的脚本支持。

Excel 公式 – 根据单元格值显示筛选后的数据透视表结果

对于不想启用宏的用户，Excel 提供了基于公式的解决方案，可根据特定单元格值显示数据透视表结果。虽然 GETPIVOTDATA 和 FILTER 等函数实际上不会更改数据透视表的筛选设置，但它们可以动态引用并呈现响应用户输入的汇总结果。

当构建反映用户输入变化标准的自定义汇总表、仪表板或报告时，这种解决方案特别有用——无需更改原始数据透视表视图。

使用 GETPIVOTDATA：

假设您的数据透视表（名为 “PivotTable2”）按类别汇总销售额，筛选值输入在单元格 H6 中。您可以使用 GETPIVOTDATA 显示 H6 中指定类别的总销售额：

1. 选择您想要显示汇总结果的单元格（例如 I6）：

=GETPIVOTDATA("Sum of Sales", $A$4, "Category", $H$6)

2. 按 Enter 键确认。当您更改 H6 中的值时，I6 中的结果会自动更新以反映数据透视表中对应的汇总结果。

如果您的数据透视表使用不同的字段名称或布局，请相应调整公式。若要自动生成 GETPIVOTDATA 公式，先在单元格中键入 = ，然后点击数据透视表中的某个值单元格。Excel 将插入适当的公式，之后您可以根据需要编辑该公式。

结合辅助表使用 FILTER 函数：

如果您想从原始数据集中提取详细记录（而不仅仅是数据透视表摘要），并且正在使用 Excel 365 或 Excel 2019，FILTER 函数可以根据单元格值进行动态筛选：

假设您的源数据位于 A1:C100 范围内，类别位于 A 列。

1. 选择筛选记录应出现的起始单元格（例如 J6）：

=FILTER(A2:C100, A2:A100 = H6, "No data")

2. 按 Enter 键确认。匹配的行将溢出到相邻单元格中，列出所有类别与 H6 中值匹配的记录。更新 H6 将立即刷新结果。

为了匹配数据透视表分组或按多个条件筛选，可以考虑结合使用 GETPIVOTDATA 和 FILTER，或者通过添加额外的逻辑条件扩展公式。

📝 提示与警告：

  • 这些公式不会修改实际的数据透视表筛选器。它们只是基于单元格值提供了一个独立的动态视图。
  • 要直接更改数据透视表筛选器，需要使用 VBA。
  • 确保 GETPIVOTDATA 中使用的字段名称与数据透视表中的字段名称完全匹配（包括大小写和空格）。
  • 如果看到 #REF! 错误，请验证您的引用是否有效，以及数据透视表结构是否未改变。

其他内置 Excel 方法 – 使用切片器作为交互式数据透视表筛选器

如果 VBA 或基于公式的解决方案无法完全适应您的工作流程，Excel 的切片器提供了另一种交互式筛选数据透视表的方法。切片器是视觉化的筛选控件，允许用户通过简单的点选界面筛选数据。虽然它们不能直接链接到单元格值——也就是说，您无法通过更改单元格来控制切片器——但对于非技术用户来说，它们直观且高效，非常适合用于仪表板和报告。

如何添加和使用切片器：

  1. 选择数据透视表中的任意单元格。
  2. 转到“数据透视表分析”选项卡（或旧版本中的“分析”选项卡），然后点击“插入切片器”。
  3. 在“插入切片器”对话框中，勾选您要筛选的字段（例如，类别），然后点击“确定”。
  4. 切片器将出现在您的工作表上。点击某个按钮即可按该值筛选数据透视表。按住 Ctrl 可以选择多个项目。

切片器可以进行格式化、调整大小，并链接到多个数据透视表以实现跨不同报表的同步筛选。它们在仪表板或共享工作簿中特别有用，因为用户可能不熟悉下拉筛选器，但仍需轻松筛选数据而不使用 VBA 或编辑公式。

限制：切片器不支持与单元格值的原生链接。如果您的工作流程需要通过单元格输入控制的动态筛选，切片器应被视为补充工具而非 VBA 或基于公式方法的替代品。

此外，如果您的数据存储在 Excel 表格（不是数据透视表）中，您仍然可以通过选择表格并转到“表格设计”选项卡 > 插入切片器来使用切片器。

故障排除：如果切片器似乎没有筛选数据透视表，请检查（切片器或分析选项卡下的）报表连接，确保它正确连接到目标数据透视表。

上述每种方法都服务于不同的目的：VBA 允许直接链接单元格的筛选，公式提供动态结果显示，而切片器则提供用户友好的图形化筛选。选择最适合您自动化、灵活性和易用性需求的方法。传统的数据透视表下拉筛选器仍可作为基本备选方案。

相关文章：

最佳Office办公效率工具

🤖 Kutools AI 助手：以智能执行为基础，彻底革新数据分析 |代码生成 |自定义公式创建|数据分析与图表生成 |调用Kutools函数……
热门功能：查找、选中项的背景色或标记重复项 | 删除空行 | 合并列或单元格且不丢失数据 | 四舍五入……
高级LOOKUP：多条件VLookup|多值VLookup|多表查找|模糊查找……
高级下拉列表：快速创建下拉列表 |依赖下拉列表 | 多选下拉列表……
列管理器： 添加指定数量的列 | 移动列 | 切换隐藏列的可见状态 | 比较区域与列……
特色功能：网格聚焦 |设计视图 | 增强编辑栏 | 工作簿及工作表管理器 | 资源库（自动文本） | 日期提取 | 合并数据 | 加密/解密单元格 | 按名单发送电子邮件 | 超级筛选 | 特殊筛选（筛选粗体/倾斜/删除线等）……
15大工具集：12项 文本工具（添加文本、删除特定字符等）|50+种 图表 类型（甘特图等）|40+实用 公式（基于生日计算年龄等）|19项 插入工具（插入二维码、从路径插入图片等）|12项 转换工具（小写金额转大写、汇率转换等）|7项 合并与分割工具（高级合并行、分割单元格等）| ……
Kutools支持多种语言——可选择英语、西班牙语、德语、法语、中文等40多种语言！

通过Kutools for Excel提升您的Excel技能，体验前所未有的高效办公。 Kutools for Excel提供300多项高级功能，助您提升效率并节省时间。 点击此处获取您最需要的功能……

Office Tab为Office带来多标签界面，让您的工作更加轻松

  • 支持在Word、Excel、PowerPoint中进行多标签编辑与阅读。
  • 在同一个窗口的新标签页中打开和创建多个文档，而不是分多个窗口。
  • 可提升50%的工作效率，每天为您减少数百次鼠标点击！

所有Kutools加载项，一键安装

Kutools for Office套件包含Excel、Word、Outlook和PowerPoint的插件，以及Office Tab Pro，非常适合跨Office应用团队使用。

Excel Word Outlook Tabs PowerPoint
  • 全能套装——Excel、Word、Outlook和PowerPoint插件+Office Tab Pro
  • 单一安装包、单一授权——数分钟即可完成设置（支持MSI）
  • 协同更高效——提升Office应用间的整体工作效率
  • 30天全功能试用——无需注册，无需信用卡
  • 超高性价比——比单独购买更实惠