数据透视表锁定筛选设置及基于单元格筛选实现方法咨询

一、锁定透视表筛选规则且允许更新 #

直接启用工作表保护会导致透视表无法更新，推荐两种替代方案：

• VBA锁定指定筛选字段
  在对应工作表的代码模块中添加以下代码，可禁止用户修改目标字段的筛选规则，但保留透视表刷新权限：

  Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
      ' 替换"目标字段"为你要锁定的透视表字段名
      Target.PivotFields("目标字段").EnableItemSelection = False
  End Sub
  
  Private Sub Worksheet_Activate()
      ' 替换"数据透视表1"为你的透视表名称
      Me.PivotTables("数据透视表1").PivotFields("目标字段").EnableItemSelection = False
  End Sub
  

  保存时选择启用宏的工作簿格式（.xlsm），用户仍能刷新数据，但无法修改指定字段的筛选设置。
• Power Query预处理数据源
  先用Power Query筛选出原始数据中的特定行，再以预处理后的结果作为透视表数据源。透视表基于筛选后的数据集生成，用户无需修改透视表筛选就能保证一致性，刷新时会同步Power Query的固定筛选结果。

二、基于指定单元格值动态筛选透视表 #

有两种实用方法实现：

• VBA动态同步筛选
  假设控制单元格为A1，要匹配的透视表字段为"类别"，在工作表代码模块中添加：

  Private Sub Worksheet_Change(ByVal Target As Range)
      If Target.Address = "$A$1" Then
          With Me.PivotTables("数据透视表1").PivotFields("类别")
              .ClearAllFilters
              .CurrentPage = Target.Value
          End With
      End If
  End Sub
  

  修改A1的值时，透视表会自动筛选出"类别"字段与A1值一致的数据。
• 切片器+单元格链接（Excel 365/2021适用）
  给目标字段添加切片器，右键点击切片器选择「链接单元格」，指定到控制单元格（如A1）。修改A1的内容，切片器会自动同步，透视表也会随之筛选对应数据，无需启用宏。

内容的提问来源于stack exchange，提问作者snaut