Excel VBA - 具有多个值过滤器的数据透视表

Excel VBA - Pivot Table with Multiple Value Filters

提问by T_Higgins

Is there a way in Excel VBA to allow for multiple value filters in a Pivot Table? I'm trying to filter values for >0 AND for Bottom Ten items. In the sample code below (generated by recording a Macro), the second filter step overrides the first. If multiple value filters were supported, it seems I would only need to add the Boolean logic AND between these two expressions to get the product of both filters. Please suggest any changes to the code or let me know if this is not supported. Thanks!

Excel VBA 中是否有允许在数据透视表中使用多个值过滤器的方法？我正在尝试过滤 >0 和底部十项的值。在下面的示例代码中（通过录制宏生成），第二个过滤步骤覆盖了第一个过滤步骤。如果支持多个值过滤器，似乎我只需要在这两个表达式之间添加布尔逻辑 AND 即可获得两个过滤器的乘积。请建议对代码进行任何更改，如果不支持，请告诉我。谢谢！

Sub Multiple_Value_Filters()

    ActiveSheet.PivotTables("PivotTable1").PivotFields("Full Name").PivotFilters.Add _       
        Type:=xlValueIsGreaterThan, _
        DataField:=ActiveSheet.PivotTables("PivotTable1").PivotFields("Days"), _
        Value1:=0
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Full Name").ClearAllFilters
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Full Name").PivotFilters.Add _
        Type:=xlBottomCount, _
        DataField:=ActiveSheet.PivotTables("PivotTable1").PivotFields("Days"), _
        Value1:=10
End Sub

回答by Archias

I can't really tell what your pivottable should be doing without seeing some sample data but I believe you should be able to do what you are trying to. Try messing around with this:

如果没有看到一些示例数据，我真的无法判断您的数据透视表应该做什么，但我相信您应该能够做您想做的事情。尝试解决这个问题：

Sub Multiple_Value_Filters()
    Dim pvt As PivotTable
    Set pvt = ActiveSheet.PivotTables("PivotTable1")

    With pvt.PivotFields("Full Name")
        .ClearAllFilters
        .PivotFilters.Add Type:=xlValueIsGreaterThan, DataField:=pvt.PivotFields("Days"), Value1:=0
        .PivotFilters.Add Type:=xlBottomCount, DataField:=pvt.PivotFields("Days"), Value1:=10
    End With
End Sub

Have discovered the PivotTable Option to allow multiple filters, however it didn't quite work for me even when it would work as I manually did it. For whatever reason Excel seems to not like the code. This functionality only works for Excel 2007 PivotTables and newer but I am running mine from Excel 2010 so I am not sure what the issue is here.

已经发现允许多个过滤器的数据透视表选项，但是即使它可以像我手动那样工作，它也不太适合我。无论出于何种原因，Excel 似乎都不喜欢这些代码。此功能仅适用于 Excel 2007 数据透视表和更新版本，但我从 Excel 2010 运行我的数据透视表，所以我不确定这里的问题是什么。

Sub Multiple_Value_Filters()
    Dim pvt As PivotTable
    Set pvt = ActiveSheet.PivotTables("PivotTable1")

    With pvt.PivotFields("Full Name")
        .ClearAllFilters
        .AllowMultipleFilters = True ' This is the main key to getting this to work but mine still errors out whenever I add the 2nd filter. 
        .PivotFilters.Add Type:=xlValueIsGreaterThan, DataField:=pvt.PivotFields("Days"), Value1:=0
        .PivotFilters.Add Type:=xlBottomCount, DataField:=pvt.PivotFields("Days"), Value1:=10
    End With
End Sub

回答by Axel

I know this question is already quite old, but I stumbled upon it recently and nothing was really working for me. I could only use one filter for the pivot table. For the second or third I always got Run Time Errors. However, I found a simple workaround, which I will describe below. I hope other people coming here with similar issues might find it helpful.

我知道这个问题已经很老了，但我最近偶然发现了它，没有什么对我有用。我只能为数据透视表使用一个过滤器。对于第二个或第三个，我总是遇到运行时错误。但是，我找到了一个简单的解决方法，我将在下面进行介绍。我希望其他遇到类似问题的人可能会发现它有帮助。

Step 1:Add Helper Columns to the data source with a new heading and any constant value in every row. (You need one helper column per extra filter. If you want to use 3 filters, you need two helper columns)

步骤 1：将 Helper Columns 添加到数据源，并在每一行中添加一个新标题和任何常量值。（每个额外的过滤器需要一个辅助列。如果要使用 3 个过滤器，则需要两个辅助列）

Step 2:Add the Helpercolumn attributes to your row-fields of the pivot table.

第 2 步：将 Helpercolumn 属性添加到数据透视表的行字段中。

Step 3:Choose the tabular layout where all row attributes are in one row.

第 3 步：选择所有行属性都在一行中的表格布局。

Step 4:Now you can apply different filters, one for each attribute in the row-field. This will yield the same result as if you use multiple filters for the "Heading 1". If you don't like the extra columns, you can also just hide them away.

第 4 步：现在您可以应用不同的过滤器，针对行字段中的每个属性应用一个过滤器。这将产生与对“标题 1”使用多个过滤器相同的结果。如果您不喜欢额外的列，您也可以将它们隐藏起来。

Step 5:If you now apply this to VBA, the code could look like this:

第 5 步：如果您现在将其应用于 VBA，代码可能如下所示：

Dim pvt As PivotTable
Set pvt = ActiveSheet.PivotTables("PivotTable1")

ActiveSheet.PivotTables("PivotTable1").AllowMultipleFilters = True

With pvt.PivotFields("Heading 1")
    .ClearAllFilters
    .PivotFilters.Add2 Type:=xlValueIsGreaterThan, DataField:=ActiveSheet.PivotTables("PivotTable1").PivotFields("Sum of Heading 2"), Value1:=2
End With
With pvt.PivotFields("Help 1")
    .ClearAllFilters
    .PivotFilters.Add2 Type:=xlValueIsSmallerThan, DataField:=ActiveSheet.PivotTables("PivotTable1").PivotFields("Sum of Heading 2"), Value1:=11
End With
With pvt.PivotFields("Help 2")
    .ClearAllFilters
    .PivotFilters.Add2 Type:=xlValueIsGreaterThan, DataField:=ActiveSheet.PivotTables("PivotTable1").PivotFields("Sum of Heading 3"), Value1:=4
End With

I hope this is useful.

我希望这是有用的。