Сводная таблица фильтров (подключенная к внешнему источнику) с диапазоном случайных элементов

Question

У меня есть список случайных элементов, которые я хочу отфильтровать в Excel pivot table. Сводная таблица подключена к внешнему источнику данных. Есть ли способ отфильтровать весь мой список в сводной таблице, не добавляя в фильтр по одному элементу за раз?

Я пробовал приведенный ниже код в VBA, который работает с локальным источником данных, но не с внешним.

"56607016", "84000110", "8A20371" - это некоторые примеры элементов из моего списка.

Если здесь есть лучшее решение, чем VBA, скажите, пожалуйста.

Sub FilterPivotItems()

Dim PT          As PivotTable
Dim PTItm       As PivotItem
Dim FiterArr()  As Variant

' use an array to select the items in the pivot filter you want to keep visible
FiterArr = Array("56607016", "84000110", "8A20371")

' set the Pivot Table
Set PT = ActiveSheet.PivotTables("PivotTable2")

' loop through all Pivot Items in "Product number" Pivot field
For Each PTItm In PT.PivotFields("[Released products].[Product number].[Product number]").PivotItems
    If Not IsError(Application.Match(PTItm.Caption, FiterArr, 0)) Then ' check if current item is not in the filter array
        PTItm.Visible = True
    Else
        PTItm.Visible = False
    End If
Next PTItm

End Sub

Сообщение об ошибке, которое я получаю при запуске:

"Ошибка выполнения '1004': невозможно получить свойство _NewEnum класса PivotItems"

Answer 1

Вместо использования цикла For Each измените FilterArr на сводную ссылку, а затем вы можете использовать значение параметра .VisibleItemsListв ваш FilterArr. Вот пример.

FilterArr = Array( _
        "[myTableName].[myPivotField].&[myPivotItem1]", _
        "[myTableName].[myPivotField].&[myPivotItem2]")


    ActiveSheet.PivotTables("PivotTable2").PivotFields( _
        "[myTableName].[myPivotField].[myPivotField]").VisibleItemsList = FilterArr

В этом примере предполагается, что у вас есть рабочий лист в той же книге с таблицей, содержащей значения, для которых необходимо отфильтровать сводную таблицу. Строка сводного фильтра для каждого значения фильтра создается с помощью формулы в соседнем столбце и затем считывается в массив. Дополнительные переменные создаются со значениями из именованных диапазонов.

Sub FilterPivot_WithListOfValues()


'---------------------------------------------------------------------------------------------------------
'   Purpose:    Dynamically create an array based on the values of one column of a table.
'
'   Customize:  Inputs in this sub come from 6 named ranges on a worksheet in this workbook.
'               myPivotTableName, myPivotFieldName, mySheetName,
'               myFilterTableName, smyDataTableName, myFilterCol
'
'   Revisions:
'   09/30/19    Sub created
'
'---------------------------------------------------------------------------------------------------------


'~~~> Set the data types for the variables.
Dim oPT As PivotTable
Dim oPF As PivotField
Dim oPI As PivotItem
Dim strPT As String 'pivot table name
Dim strPF As String 'pivot field name
Dim strWS As String 'pivot table sheet name
Dim oWS As Worksheet
Dim oFTable As ListObject
Dim oDTable As ListObject
Dim strFTable As String 'filter table name
Dim strDTable As String 'data table name
Dim strFilterString As String 'pivot table filter string
Dim FilterArr() As Variant
Dim TempArray
Dim i As Long
Dim iCol As Long

'~~~> Assign the variables.
strPT = [myPivotTableName]
strPF = [myPivotFieldName]
strWS = [mySheetName]
strFTable = [myFilterTableName]
strDTable = [myDataTableName]
iCol = [myFilterCol]

'~~~> Build the filter string.  Don't include the quotation marks wrapper.
'     That is automatically added by .PivotFields
strFilterString = "[" & strDTable & "].[" & strPF & "].[" & strPF & "]"

'~~~> Check the variables in the Immediate Window.
Debug.Print "Variable Set: strPT = " & strPT
Debug.Print "Variable Set: strPF = " & strPF
Debug.Print "Variable Set: strWS = " & strWS
Debug.Print "Variable Set: strDTable = " & strDTable
Debug.Print "Variable Set: strFTable = " & strFTable
Debug.Print "Variable Set: iCol = " & iCol
Debug.Print "Variable Set: strFilterString = " & strFilterString


    '~~~> Set the path for the table variable
    Set oFTable = Range(strFTable).ListObject

    '~~~> Set the path for the sheet variable
    Set oWS = Worksheets(strWS)

    '~~~> Create an array list from a table column.
    TempArray = oFTable.DataBodyRange.Columns(iCol)

    '~~~> Convert from vertical array to horizontal array list.
    FilterArr = Application.Transpose(TempArray)


DisplayArrayValues:
'~~~> Loop through each item in the table array and display in Immediate Window [ctrl + g]

    For i = LBound(FilterArr) To UBound(FilterArr)

        Debug.Print FilterArr(i)

    Next i


ApplyPivotFilters:
'~~~> Apply filters to the pivot table.

    Set oPF = oWS.PivotTables(strPT).PivotFields(strFilterString)

    With oPF

        .VisibleItemsList = FilterArr

    End With


ReleaseVariables:
'~~~> Release the variables from memory.

Set oPT = Nothing
Set oPF = Nothing
Set oPI = Nothing
Set oWS = Nothing
Set oFTable = Nothing
Set oDTable = Nothing
strPT = vbNullString
strPF = vbNullString
strWS = vbNullString
strFTable = vbNullString
strDTable = vbNullString
strFilterString = vbNullString
i = vbNull
iCol = vbNull

End Sub

Это фрагмент таблицы фильтров и именованных диапазонов.

Это формула, которая используется для создания строки фильтра.

="["&myDataTableName&"].["&myPivotFieldName&"].&["&[@[Pivot Items]]&"]"