Сводная таблица отфильтровывает только 1 опцию

Question

У меня проблема с удалением одного параметра из фильтра в моей сводной таблице. Если я записываю макрос и пытаюсь применить изменения, удалив только один из ~20 параметров, Excel выводит сообщение:

Слишком много продолжений строки!

Похоже, он пытается объявить каждую из возможных опций фильтра по имени, как здесь:

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 одной командой?

Answer 1

Отличный вопрос Поскольку это сводная таблица OLAP, ключ должен установить для свойства CubeField.IncludeNewItemsInFilter PivotField значение TRUE или FALSE в зависимости от того, что вы хотите сделать. См. https://msdn.microsoft.com/en-us/vba/excel-vba/articles/pivotfield-includenewitemsinfilter-property-excel

Допустим, мы заинтересованы в этих двух пунктах:

• [Компонент продукта].[(C) Сегмент 4]. & [31558]
• [Компонент продукта].[(C) Сегмент 4].&[315516]

Если вы хотите, чтобы были видны только эти две вещи, задайте для свойства CubeField.IncludeNewItemsInFilter объекта PivotField значение FALSE, а затем передайте массив вещей, которые должны быть видимы для pf.VisibleItemsList, например так:

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

Если вы хотите, чтобы все, кроме этих двух вещей, было видимым, установите для свойства CubeField.IncludeNewItemsInFilter в PivotField значение TRUE, а затем передайте массив вещей, которые должны быть видимы для pf.HidenItemsList, например так:

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

Answer 2

Если вы хотите удалить только один элемент из фильтра, то сделайте именно это:

Dim Table as PivotTable
Set PivotTable = YourPivotTable

Dim Field as PivotField
Set Field = Table.PivotFields("YourFieldName")

With Field
    .EnableMultiplePageItems = True
    .ClearAllFilters
End With

Field.PivotItems("YourItemName").Visible = False

Так как очистка всех фильтров означает, что все элементы будут видны, мы можем затем удалить каждый элемент один за другим.

Стоит отметить, что если вы делаете это много, вы должны установить Table.ManualUpdate = False пока вы не закончили с изменением фильтров, а затем Table.ManualUpdate = True как только вы закончите. Это предотвратит его обновление каждый раз при смене фильтра (что может занять очень много времени).

РЕДАКТИРОВАТЬ: В случае, если вы должны передать массив, просто создайте его так:

Dim ItemToHide as String
ItemToHide = "YourItemToHide"

' Assumes only one is being hidden
Dim VisibleItems(1 to Field.PivotItems.Count - 1) as Variant

Dim Item as PivotItem
For each Item in Field.PivotItems
    If Item.Value <> ItemToHide Then
        Dim Count as Long
        Count = Count + 1

        VisibleItems(Count) = Item.Value
    End If
Next

Table.VisibleItemsList = VisibleItems