时间戳

首页 > 解决方案 > Excel-VBA-Query 有没有办法通过 Refresh.All 来提高 Excel VBA 的速度？

sql - Excel-VBA-Query 有没有办法通过 Refresh.All 来提高 Excel VBA 的速度？

问题描述

我有一个标签和几个数据透视表。一切正常，但现在因为我在选项卡中添加了 vlookup 以及一些公式，它使文件非常慢。请注意，我与 ODBC 和 SQL 建立了连接，可以提取 30,000 行和 32 列的数据。

有没有办法可以提高性能？

我试过：取消选中后台更新添加等待查询

它仍然很慢......大约需要 15 分钟才能完成并打开！任何有助于减少这种情况的帮助将不胜感激！

谢谢，

Private Sub Workbook_Open()

Application.ScreenUpdating = False
Application.DisplayStatusBar = False
ActiveSheet.DisplayPageBreaks = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False

DoEvents

ActiveWorkbook.RefreshAll
Application.CalculateUntilAsyncQueriesDone
If Not Application.CalculationState = xlDone Then
DoEvents
End If
DoEvents

    Range("A3:P3").Select
    Sheets("FRQ-4").Select
     Range("A6").Select
    With ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotFields("STATUT")
        .PivotItems("INACTIF").Visible = False
        .PivotItems("(blank)").Visible = False
    End With
    ActiveSheet.Range("$A$3:$P$385").AutoFilter Field:=14, Criteria1:="=1", _
    Operator:=xlOr, Criteria2:="=BESOIN-ACHAT " & Chr(10) & "1=OUI 0=NON"

    DoEvents
    Range("A3:P3").Select
    Sheets("FRQ-3").Select
        With ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotFields("STATUT")
        .PivotItems("INACTIF").Visible = False
        .PivotItems("(blank)").Visible = False
    End With
    ActiveSheet.Range("$A$3:$P$385").AutoFilter Field:=14, Criteria1:="=1", _
    Operator:=xlOr, Criteria2:="=BESOIN-ACHAT " & Chr(10) & "1=OUI 0=NON"
        Range("A6").Select


    DoEvents
    Range("A3:P3").Select
    Sheets("FRQ-2").Select
        With ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotFields("STATUT")
        .PivotItems("INACTIF").Visible = False
        .PivotItems("(blank)").Visible = False
    End With
    ActiveSheet.Range("$A$3:$P$385").AutoFilter Field:=14, Criteria1:="=1", _
    Operator:=xlOr, Criteria2:="=BESOIN-ACHAT " & Chr(10) & "1=OUI 0=NON"
        Range("A6").Select


    DoEvents
    Range("A3:P3").Select
    Sheets("FRQ-1").Select
        With ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotFields("STATUT")
        .PivotItems("INACTIF").Visible = False
        .PivotItems("(blank)").Visible = False
    End With
    ActiveSheet.Range("$A$3:$P$385").AutoFilter Field:=14, Criteria1:="=1", _
    Operator:=xlOr, Criteria2:="=BESOIN-ACHAT " & Chr(10) & "1=OUI 0=NON"
        Range("A6").Select
    
    Sheets("FRQ-4").Select
    With ActiveSheet.Columns("E:S")
      .HorizontalAlignment = xlCenter
    End With
    With ActiveSheet.Columns("A:D")
        .HorizontalAlignment = xlLeft
    End With

    Sheets("FRQ-3").Select
    With ActiveSheet.Columns("E:S")
      .HorizontalAlignment = xlCenter
    End With
    With ActiveSheet.Columns("A:D")
        .HorizontalAlignment = xlLeft
    End With
    
    Sheets("FRQ-2").Select
    With ActiveSheet.Columns("E:S")
      .HorizontalAlignment = xlCenter
    End With
    With ActiveSheet.Columns("A:D")
        .HorizontalAlignment = xlLeft
    End With
    
    Sheets("FRQ-1").Select
    With ActiveSheet.Columns("E:S")
      .HorizontalAlignment = xlCenter
    End With
    With ActiveSheet.Columns("A:D")
        .HorizontalAlignment = xlLeft
    End With

MsgBox "Ready"

End Sub

标签： sqlvbaexcel-formulaodbcms-query

解决方案

提高性能的方法

（没有工作簿的帮助，很难找出真正的罪魁祸首）......

似乎您已经涵盖了大多数通用 VB 部分：

Application.screenupdating=falss
Application.calculationmode = xlManualClaculation

等等，所以转向可能是罪魁祸首*：

*（虽然你意识到'Doevents'会减慢宏

1）VLOOKUP

1.1) 使用索引匹配

我个人很讨厌 VLOOKUP 并且自从 Excel 2000 之类的之后就没有使用过它，因为那时我不知道更好。虽然 XLOOKUP 看起来很有希望，但通常情况下 VLOOKUP 对索引/匹配的限制过于严格，因为它只能搜索查找列的右侧（排除特殊情况）。

你猜怎么着？它也比索引/匹配慢很多！

见这里（Kyd，na，有超过 40k 的浏览量，可下载的 excel 工作簿/示例）

"对于未排序的数据，VLOOKUP 和 INDEX-MATCH 的计算时间大致相同。...对于已排序的数据和近似匹配，INDEX-MATCH 比 VLOOKUP 快约 30%。使用排序的数据和快速查找精确数据的技术匹配，INDEX-MATCH 比 VLOOKUP 快 13% 左右。 ”

可以在此处找到关于偏移/匹配与索引匹配的优点（性能方面）的讨论（Ed，2003）

1.2) 限制范围

无论您使用 VLOOKUP/INDEX-MATCH/OTHER，请尝试：

  • 限制适用的范围 - 例如，如果您已经在这样做，请不要突出显示整个列，或者使用动态范围（Cheusheva，2021）

2.1) 其他

  • 重新您的 VB - 您是否尝试过执行程序的不同部分（第一、第二、第三关键区域）？即发现哪个花费时间最长？
  • 它什么时候开始“陷入困境”？在导入阶段，还是在进行计算时？或创建/更新枢轴
  • excel文件有多大？如果超过 40MB，这可能是个问题。如果您还没有保存为文件类型 .xlsb，您将拥有相同的 VB 访问权限，除非您使用 SQL 输出（您可能是），否则您可以将文件大小减少约 40-50%！
  • 鉴于您确实有 SQL 连接，最近您的网络连接是否有一些奇怪/有趣的事情（如果适用？）您能否将数据导入完全独立的工作簿，然后使用数据导入来操作本地文件中的数据（我知道你可以，我的意思是，试试这个，看看它是否减少了时间）
  • 输入枢轴然后“重新枢轴”的计算可能会导致实质性延迟

推荐阅读