数据透视表如何将筛选器中的所有项目设置为false? [英] PivotTable how to set all items in filter to false?
问题描述
我有一个创建数据透视表的MS Excel宏.在数据透视表中,我要过滤多个项目.因此,首先需要将所有项目设置为false,然后将要包括的项目设置为true.所以目前我有:
I have a MS Excel macro that creates a PivotTable. Within the PivotTable I want to filter on multiple items. So first you need to set all items to false and then set the items you want to include to true. So currently I have:
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Test Cycle")
.PivotItems("FXA BI_MH Pre UAT C1").Visible = False
.PivotItems("FXA BI_MH Pre UAT C2").Visible = False
.PivotItems("FXA Reg C1").Visible = False
.PivotItems("MC3").Visible = False
.PivotItems("PT Cycle 1").Visible = False
.PivotItems("Regression Test (APO)").Visible = False
.PivotItems("SIT CR").Visible = False
.PivotItems("SIT Cycle 2").Visible = False
.PivotItems("UAT - Data Conv").Visible = False
.PivotItems("UAT Pre-Test - Additional").Visible = False
.PivotItems("UAT Pre-Test - Final").Visible = False
.PivotItems("UAT Pre-Test - Iteration 2").Visible = False
End With
ActiveSheet.PivotTables("PivotTable2").PivotFields("Test Cycle"). _
EnableMultiplePageItems = True
但是,测试周期"项会更改,如果引入了新项,则会自动包括在内,因为我尚未将其设置为false.有没有一种方法可以将所有选择都设置为false,就像这样(不起作用):
However, the "Test Cycle" items change and if a new one is introduced it is automatically included as I have not set it to false. Is there a way to just set all selections to false, something like (which does not work):
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Test Cycle")
.PivotItems("(All)").Visible = False
End With
ActiveSheet.PivotTables("PivotTable2").PivotFields("Test Cycle"). _
EnableMultiplePageItems = True
在@Josh的帮助下,我有:
With the help of @Josh I have:
ActiveSheet.PivotTables("PivotTable2").PivotFields("Test Cycle"). _
EnableMultiplePageItems = True
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Test Cycle")
For i = 1 To .PivotItems.Count - 1
If .PivotItems(i).Name <> "UAT Pre-test (FXA)" _
And .PivotItems(i).Name <> "UAT C2 (FXA)" Then
.PivotItems(.PivotItems(i).Name).Visible = False
End If
Next i
End With
推荐答案
与其手动列出每个项目,不如尝试通过以下方法关闭过滤器:
Rather than listing each item manually, try to turn off the filter with the following:
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Test Cycle")
For i = 1 To .PivotItems.Count - 1
.PivotItems(.PivotItems(i).Name).Visible = False
Next i
End With
请记住,使用excel界面时,必须至少选择一项才能保存过滤器.
Keep in mind, when using the excel interface, you must have at least 1 item selected in order to save the filter.
这篇关于数据透视表如何将筛选器中的所有项目设置为false?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!