vba - VBA Pivot 过滤器在每个过滤器项上重新计算表 - 如何避免这种情况？

Question

我有一个 Excel 电子表格，其中有一个更大的数据透视表。该表必须能够使用帐号列表上的宏进行过滤。我尝试以 2 种不同的方式进行过滤（两种方式都有效，但速度很慢），因此只有一定数量的帐户被设置为“可见”。我试图在工作表中创建一个列表并为其提供一个动态命名范围，然后将其过滤掉（方法＃1）。

我还尝试直接在我的代码中制作硬编码的数组（方法＃2），但两者都有非常烦人的功能，即为每个数组/列表项重新计算数据透视表 - 并且有 +50 项。有没有办法同时设置这些过滤器，然后进行一次重新计算？

我尝试了以下 2 个宏：

方法#1：

Sub Test ()
    Dim PI As PivotItem
    With Worksheets ("Debt"). PivotTables ("pivottabel1"). PivotFields ("GL_AccNo")
        .ClearAllFilters
        For Each PI In .PivotItems
            PI.Visible = WorksheetFunction.CountIf (Range ("Debt AccountsRange"), PI.Name)> 0
        Next PI
    End With
End Sub

在此，另一个工作表中的命名范围被称为

方法#2：

Private Sub PivotFilterTest ()
    Dim pf As PivotField
    Dim myArray () As Variant
    Dim and As Long

    myArray = Array ("42270", "62515", "62520", "72110", "72120", "72140", "72150", "72180", "72181", "72185", "72210", "72220 "," 72230 "," 72250 "," 72260 "," 72270 "," 72275 "," 72280 "," 72285 "," 72310 "," 72315 "," 72320 "," 72330 "," 72340 ", "73110", "73120", "73121", "73125", "73130", "73131", "73133", "73134", "73140", "73150", "73155", "73160", "73610 "," 73615 "," 73620 "," 73625 "," 73630 "," 73635 "," 73640 "," 73645 "," 73650 "," 73655 "," 76210 "," 76220 "," 76230 ", "76235", "76240", "76250", "76260", "76265", "76989")
    Set pf = Worksheets ("Debt"). PivotTables ("pivottabel1"). PivotFields ("GL_AccNo")
    With pf
        .ClearManualFilter
        .EnableMultiplePageItems = True
        For i = LBound (myArray) To UBound (myArray)
            .PivotItems (myArray (i)). Visible = True
        Next i
    End With
End Sub

在这里，帐号被直接编码到一个数组中。

有谁知道如何在重新计算之前设置过滤器？

标签： vbapivot