时间戳

首页 > 解决方案 > 基于数据透视表过滤动态更新切片器选择

excel - 基于数据透视表过滤动态更新切片器选择

问题描述

我有 3 个使用相同源数据的数据透视表。我有按月计算的预算和实际数字（3 列 - 月、预算、实际）。

我为月份创建了一个切片器，只想显示具有实际数字的月份（例如一月到六月）。现在，它显示所有月份，因为全年都有预算数字。如何使用 VBA 代码实现这一点？

作为第一步，我在数据透视表上尝试了一个值过滤器，但我收到了一个错误。我的计划是首先过滤实际不等于零月份的数据透视表，然后将月份的选择应用于切片器。

    Sub filtermonth()
    '
    ' filtermonth Macro
    '
  ActiveSheet.PivotTables("PivotTable4").PivotFields("Month").PivotFilters.Add2 _
            Type:=xlValueDoesNotEqual, DataField:=ActiveSheet.PivotTables("PivotTable4" _
            ).PivotFields("Actuals"), Value1:=0
    End Sub

运行此程序时出现以下错误

运行时错误 1004：无法获取数据透视表类的 PivotFields 属性

标签： excelvbapivot-table

解决方案

我猜，您将“月份”作为行字段，将“实际总和”（重命名为“实际值”）作为数据字段。

如果值已被过滤，则会出现错误，因此您需要先清除过滤器。

通过以下代码，您可以在切片器中打开/关闭月份名称的可见性：

Sub HideUnnecessaryMonths()
    Dim pt As PivotTable
    Dim pfMonth As PivotField, pfActuals As PivotField
    Dim r As Range
    Dim i As Long

    Set pt = ActiveSheet.PivotTables("PivotTable4")
    Set pfMonth = pt.PivotFields("Month")
    Set pfActuals = pt.PivotFields("Actuals")

    pfMonth.ClearAllFilters
    pfMonth.PivotFilters.Add2 _
        Type:=xlValueDoesNotEqual, _
        DataField:=pfActuals, _
        Value1:=0

    For i = 1 To 12  ' each month
        On Error Resume Next
        Set r = pt.GetPivotData("Actuals", pfMonth.Name, i)
        If Err.Number <> 0 Then
            On Error GoTo 0
            pfMonth.PivotItems(i + 1).Visible = False
        End If
        On Error GoTo 0
    Next i
End Sub

备注，如果要在切片器中完全隐藏月份名称：

只要您也将预算作为数据字段（例如“预算总和”），您就不能在切片器中完全隐藏月份名称：

  • 您可以打开/关闭各个月份的可见性，
    但切片器仍会显示所有月份（标记或不标记）
  • 您可以使用日期过滤器来减少数据透视行，
    但切片器仍会显示所有月份
  • 您可以使用值过滤器，例如“Actuals”> 0，
    但切片器仍显示所有月份

只有当您将“预算”显示为第二行字段（这将是无用的，因为您看不到汇总预算）时，您才能编辑切片器的设置并禁用“隐藏没有数据的项目”。然后你的切片器会显示一个减少的月份名称列表。

推荐阅读