excel - 如何根据列表过滤数据透视表？优化高效 - VBA

Question

我正在尝试构建一个数据透视表，并且我正在根据动态列表过滤项目。该列表通常包含 est. 20 个项目，但数据透视表将包含 5,000 个以上的项目。
我现在拥有的代码循环遍历所有 5,000 个项目，并使这 20 个项目可见。但是，由于数据集非常大(5,000 项)，运行时间非常长。是否有更好的方法来运行此代码以实现更快、更有效的结果？
我在想可能是按照“取消选择所有”5,000 个项目的方式，然后找到这 20 个项目并使它们可见。
这是我现在拥有的代码。

Dim PI as PivotItem 
lrow = Main.Cells(Rows.Count, "E").End(xlUp).Row
Set Rng = Main.Range("E1:E" & lrow)

With Main.PivotTables("PivotTable2").PivotFields("Details")
    .ClearAllFilters
    For Each PI In .PivotItems
        PI.Visible = WorksheetFunction.CountIf(Rng, PI.Name) > 0
    Next PI
End With

Answer 1

这是我能得到的最快的速度。隐藏具有大约 5000 个唯一值的字段的几乎所有项目大约需要 5-6 秒。

Sub Test()
    Dim pt As PivotTable, x As Long, t
    
    Set pt = ActiveSheet.PivotTables(1)
    t = Timer
    pt.ManualUpdate = True                 'tweak #1
    Application.ScreenUpdating = False     'tweak #2
    With pt.PivotFields("Col1")
        .ClearAllFilters
        .AutoSort xlManual, .SourceName    'tweak #3
        For x = 1 To .PivotItems.Count     'tweak #4 - slightly faster than For each
            If x Mod 1000 = 0 Then Debug.Print x
            If Rnd() > 0.007 Then .PivotItems(x).Visible = False 'hiding most of the items...
        Next x
        .AutoSort xlAscending, .SourceName
    End With
    pt.ManualUpdate = False
    Debug.Print Timer - t
End Sub

应用于您的用例时，我会使用 IsError(Application.Match(...))因为这可能比运行 CountIf 更快.