☰

优化数据透视表上的每个循环

tag-icon • tag-icon microsoft-excel performance vba macros optimize
优化数据透视表上的每个循环

我在尝试优化 VBA 循环时遇到了困难For Each，该循环必须解析基于 Excel 表的数据透视表中的 10-15k 行，所有行都位于同一工作簿中。目前，这需要大约 2 分钟才能完成，由于我在会议中使用它，所以我想改进这一点。

我搜索并找到了一些有趣的建议，例如禁用屏幕更新、手动计算以及将dim我的变量“”设置为其他数据类型，但我没有发现速度有任何变化。我假设循环For Each不是为此目的而设计的。

在读的时候这超级用户帖子，看起来我可以将相关值放入“词典”中，而且速度会非常快。事实上，我有两个项目需要过滤，这对我来说有点复杂。

我正在“边做边学”，并不声称自己是 VBA 程序员，所以非常感谢任何帮助！

Dim pvtTable As PivotTable
Dim pvtField1, pvtField2 As PivotField
Dim pvtItem1, pvtItem2 As PivotItem

Set ws = ActiveSheet
Set pvtTable = ws.PivotTables("PTReport")
Set pvtField1 = pvtTable.PivotFields("callNummer")
Set pvtField2 = pvtTable.PivotFields("Destination")

Application.ScreenUpdating = False

For Each pvtItem1 In pvtField1.PivotItems
  If InStr(UCase(pvtItem1), "STORE") > 0 Then
    pvtItem1.Visible = True
  Else
    pvtItem1.Visible = False
  End If
Next

For Each pvtItem2 In pvtField2.PivotItems
  If InStr(UCase(pvtItem2), "221") > 0 Then
    pvtItem2.Visible = True
  Else
    pvtItem2.Visible = False
  End If
Next

Application.ScreenUpdating = True

如果我可以澄清任何内容或提供更多详细信息，请告诉我。

答案1

我怀疑 20-30K 次呼叫InStr(UCase())是问题所在。我建议您定义几个辅助列。例如，如果您的“callNummer”和“Destination”数据位于 Columns A和 中B，请设置

  • Y1→=IFERROR(SEARCH("store", A1), 0)
  • Z1→=IFERROR(SEARCH("221", B1), 0)

然后更改 VBA 代码以检查和是否。这样，每当修改“callNummer”和“Destination”数据时，都会进行字符串中的字符串检查，VBA 例程要做的工作就少了很多。当然，一旦一切正常，您就可以隐藏辅助列。YnZn> 0

我不确定如何将您的陈述翻译成这个框架。pvtItemN.Visible = …

但即使您无法让其工作，您也可以将InStr(UCase(pvtItem2), "221")测试更改为 InStr(pvtItem2, "221")。如果您要查找的只是一个数字，则没有必要将单元格的字母内容转换为大写。

答案2

在第一个 for...next 循环之前，添加以下代码：

Dim saveCalc as xlCalculation

With Application
   .ScreenUpdating = False
   saveCalc = .Calculation
   .Calculation = xlCalculationManual
End With

然后，在第二个 for...next 循环之后添加以下代码：

With Application
    .ScreenUpdating = True
    .Calculation = saveCalc
End With

相关内容