使用多个值筛选器的Excel VBA - Pivot表。

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中是否有方法允许在透视表中使用多个值筛选器?我试图过滤>和下面10项的值。在下面的示例代码中(通过记录宏生成)，第二个过滤器步骤覆盖第一个。如果支持多个值过滤器，那么似乎只需要在这两个表达式之间添加布尔逻辑，就可以得到这两个过滤器的乘积。请建议对代码做任何修改，如果不支持，请告诉我。谢谢!

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

1 个解决方案

#1

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