数据透视表仅过滤掉 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:

太多的续行！

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

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/en-us/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屋！