数据透视表仅过滤掉1个选项 [英] Pivot Table filter out only 1 option

问题描述

我在从数据透视表中的过滤器中删除一个选项时遇到问题，如果我记录了一个宏并尝试应用更改而仅删除了〜20个选项之一，则Excel会弹出一条消息:

I'm having a problem with removing one option from the filter in my Pivot Table, if I record a macro and try to apply changes with only one of ~20 options removed, Excel pops up a message:

行继续太多！

Too many line continuations!

似乎它正试图通过名称声明每个可能的过滤器选项，就像这里一样:

Seems like it is trying to declare each of the possible filter options by name, just like here:

ActiveSheet.PivotTables("PivotTable1").PivotFields( _
    "[Product Component].[(c) Segment 4].[(c) Segment 4]").VisibleItemsList = Array _
    ("[Product Component].[(c) Segment 4].&[31558]", _
    "[Product Component].[(c) Segment 4].&[315516]", _
    "[Product Component].[(c) Segment 4].&[3152027]", _
    "[Product Component].[(c) Segment 4].&[3152028]")

是否可以仅删除一个选项并用一个命令显示其余的〜20个?

Is it possible to remove only one option and show the rest ~20 with one command?

推荐答案

好问题.因为这是OLAP数据透视表，所以关键是根据要执行的操作将PivotField的CubeField.IncludeNewItemsInFilter属性设置为TRUE或FALSE.请参见 https://msdn.microsoft .com/zh-CN/vba/excel-vba/articles/pivotfield-includenewitemsinfilter-property-excel

Great question. Because this is an OLAP PivotTable, the key is to set the PivotField's CubeField.IncludeNewItemsInFilter Property to either TRUE or FALSE depending on what you want to do. See https://msdn.microsoft.com/en-us/vba/excel-vba/articles/pivotfield-includenewitemsinfilter-property-excel

假设我们对以下两项感兴趣:

Let's say we're interested in these two items:

• [产品组件].[(c)第4段].& [31558]
• [产品组件].[(c)第4段].& [315516]

如果只希望这两个东西可见，则将PivotField的CubeField.IncludeNewItemsInFilter属性设置为 FALSE ，然后将对pf.VisibleItemsList应该可见的东西数组进行填充，如下所示:

If you want only those two things to be visible, set the PivotField's CubeField.IncludeNewItemsInFilter Property to FALSE, and then feed an array of things that should be visible to pf.VisibleItemsList, like this:

Sub ShowOLAPItems()
'
Dim pt As PivotTable
Dim pf As PivotField

Set pt = ActiveSheet.PivotTables("PivotTable1")
Set pf = pt.PivotFields("[Product Component].[(c) Segment 4].[(c) Segment 4]")
pf.CubeField.IncludeNewItemsInFilter = FALSE 'This is the default property
pf.VisibleItemsList = Array("[Product Component].[(c) Segment 4].&[31558]", _
    "[Product Component].[(c) Segment 4].&[315516]")

End Sub

如果要使所有 可见，请将PivotField的CubeField.IncludeNewItemsInFilter属性设置为 TRUE ，然后输入pf.HidenItemsList应该可见的东西数组，像这样:

If you want everything except those two things to be visible, set the PivotField's CubeField.IncludeNewItemsInFilter Property to TRUE, and then feed an array of things that should be visible to pf.HidenItemsList, like this:

Sub HideOLAPItems()
'
Dim pt As PivotTable
Dim pf As PivotField

Set pt = ActiveSheet.PivotTables("PivotTable1")
Set pf = pt.PivotFields("[Product Component].[(c) Segment 4].[(c) Segment 4]")
pf.CubeField.IncludeNewItemsInFilter = TRUE 
pf.HiddenItemsList = Array("[Product Component].[(c) Segment 4].&[31558]", _
    "[Product Component].[(c) Segment 4].&[315516]")

End Sub

这篇关于数据透视表仅过滤掉1个选项的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！