使用 VBA 筛选具有多个条件的 Excel 数据透视表

Filter Excel pivot table with multiple criteria using VBA

提问by GAB

Edited

已编辑

IF Select Screen Field (worksheet) Range N3 = Monthly,

如果选择屏幕字段（工作表）范围 N3 = 每月，

I need Worksheets 'Tickets by Group', 'Top 10 Bill tos', 'Top 10 CSRs', Top 10 Categories', Top 10 Created by' to remove the value in the pivot table and then insert 'Created Month'. Then have all refresh.

我需要工作表 'Tickets by Group'、'Top 10 Bill tos'、'Top 10 CSR'、Top 10 Categories'、Top 10 Created by' 来删除数据透视表中的值，然后插入 'Created Month'。然后全部刷新。

IF Select Screen Field (worksheet) Range N3 = Weekly,

如果选择屏幕字段（工作表）范围 N3 = 每周，

I need Worksheets 'Tickets by Group', 'Top 10 Bill tos', 'Top 10 CSRs', Top 10 Categories', Top 10 Created by' to remove the value in the pivot table and then insert 'Week Number'. Then have all refresh where also the only fields to show would be from Select Screen Range A2, B2, C2, D2, E2 and F2 which will never be blank.

我需要工作表 'Tickets by Group'、'Top 10 Bill tos'、'Top 10 CSR'、Top 10 Categories'、Top 10 Created by' 来删除数据透视表中的值，然后插入 'Week Number'。然后全部刷新，其中唯一要显示的字段来自选择屏幕范围 A2、B2、C2、D2、E2 和 F2，它们永远不会为空白。

There will never be blanks in any field that it would look at and those are the only 2 options for the Select Screen Field(Worksheet)

它将查看的任何字段中都不会出现空白，这些是选择屏幕字段（工作表）的唯一 2 个选项

Max....

最大限度....

On the first sheet I am adding a drop down where either Monthly or Weekly can be selected. I have that value on the 'Select Screen Field' (worksheet). I then have 5 other worksheets that have pivot tables on them. If they select Monthly I would want the pivot table on each to clear out all filters and then clear out the column label and add Created Month... When running a macro and then looking at the results I get:

在第一张纸上，我添加了一个下拉菜单，可以选择每月或每周。我在“选择屏幕字段”（工作表）上有这个值。然后我有 5 个其他工作表，上面有数据透视表。如果他们选择每月，我希望每个数据透视表清除所有过滤器，然后清除列标签并添加创建的月份...当运行宏然后查看结果时，我得到：

ActiveSheet.PivotTables("Volume").PivotFields("Week Number").Orientation = _ xlHidden With ActiveSheet.PivotTables("Volume").PivotFields("Created Month") .Orientation = xlColumnField .Position = 1

ActiveSheet.PivotTables("Volume").PivotFields("Week Number").Orientation = _ xlHidden With ActiveSheet.PivotTables("Volume").PivotFields("Created Month") .Orientation = xlColumnField .Position = 1

But when Weekly is selected I need it to do the same but instead to show 'Week Number' and then have the Pivot table filter to only showing 6 weeks. That value will be based off 'Select Screen Field', A2, B2, C2, D2, E2 and F2. Which currently shows, 39, 38, 37, 36, 35, 34.

But when Weekly is selected I need it to do the same but instead to show 'Week Number' and then have the Pivot table filter to only showing 6 weeks. 该值将基于“选择屏幕字段”、A2、B2、C2、D2、E2 和 F2。目前显示，39、38、37、36、35、34。

回答by Max

I think we have it now... if the pivottablename is not "volume" in one of the Sheets, change that. the week filter is not searching all of the mentioned fields, but filtering weeks greater/equal A2

我想我们现在已经有了……如果其中一张表中的数据透视表名称不是“卷”，请更改它。周过滤器不是搜索所有提到的字段，而是过滤大于/等于 A2 的周

Sub start()
Call pivotchange("Tickets by Group", "Volume")
Call pivotchange("Top 10 Bill tos", "Volume")
Call pivotchange("Top 10 CSRs", "Volume")
Call pivotchange("Top 10 Categories", "Volume")
Call pivotchange("Top 10 Created by' ", "Volume")
End Sub

Sub pivotchange(sheetname As String, pivottablename As String)
On Error Resume Next
Dim week_filter As String
Dim pt As PivotTable
Set pt = Sheets(sheetname).PivotTables(pivottablename)
    pt.ClearAllFilters
    pt.PivotFields("week").Orientation = xlHidden
    pt.PivotFields("month").Orientation = xlHidden

If Sheets("Select Screen Field").Range("N3").Value = "weekly" Then
    With pt.PivotFields("week")
        .Orientation = xlColumnField
        .Position = 1
    End With
week_filter = Sheets("Select Screen Field").Range("A2").Value
    pt.PivotFields("week").PivotFilters.Add _
        Type:=xlCaptionIsGreaterThanOrEqualTo, Value1:=week_filter
    pt.PivotFields("week").AutoSort xlAscending, "week"
End If
If Sheets("Select Screen Field").Range("N3").Value = "monthly" Then
    With pt.PivotFields("month")
        .Orientation = xlColumnField
        .Position = 1
    End With
    pt.PivotFields("week").AutoSort xlAscending, "month"
End If
pt.RefreshDataSourceValues
pt.RefreshTable
End Sub

回答by Tim Williams

You need at least one valid value in G3:G7 or this will error:

您需要在 G3:G7 中至少有一个有效值，否则会出错：

Dim pf As PivotField, pi As PivotItem

Set pf = ActiveSheet.PivotTables("Tickets by Group").PivotFields("Volume")
pf.ClearAllFilters
pf.EnableMultiplePageItems = True

For Each pi In pf.PivotItems
    pi.Visible = Not IsError(Application.Match(pi.Caption, Range("G3:G7"), 0))
Next pi