如何记录Excel多个Power Query运行时长并生成时长统计表格？

最优方案：VBA 宏逐个刷新并精准计时 #

该方案无需复制查询、不会因 Power Query 内部执行顺序产生误差，完全匹配「Refresh All 逻辑+生成统计表格」的需求：

1. 实现步骤 #

打开 Excel 按 Alt+F11 进入 VBA 编辑器，插入新模块后粘贴以下代码：

Sub RecordQueryRefreshTimes()
    Dim qry As WorkbookQuery
    Dim startTime As Double, endTime As Double
    Dim ws As Worksheet
    Dim lastRow As Long
    
    ' 创建/获取统计工作表
    On Error Resume Next
    Set ws = ThisWorkbook.Worksheets("QueryRefreshStats")
    On Error GoTo 0
    If ws Is Nothing Then
        Set ws = ThisWorkbook.Worksheets.Add
        ws.Name = "QueryRefreshStats"
        ws.Range("A1:B1").Value = Array("查询名称", "刷新时长(秒)")
        ws.Range("A1:B1").Font.Bold = True
    End If
    
    ' 清空历史统计数据（保留表头）
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    If lastRow > 1 Then ws.Range("A2:B" & lastRow).ClearContents
    
    ' 遍历所有查询并计时刷新
    lastRow = 1
    For Each qry In ThisWorkbook.Queries
        startTime = Timer
        ' 同步刷新当前查询，避免异步导致计时偏差
        qry.Refresh BackgroundQuery:=False
        endTime = Timer
        
        ' 写入统计结果
        lastRow = lastRow + 1
        ws.Cells(lastRow, "A").Value = qry.Name
        ws.Cells(lastRow, "B").Value = Round(endTime - startTime, 2)
    Next qry
    
    ws.Columns("A:B").AutoFit
    MsgBox "统计完成，结果已保存至 QueryRefreshStats 工作表", vbInformation
End Sub

2. 核心优势 #

• 逐个同步刷新查询，用 Timer 函数精准记录单查询的实际运行时长
• 自动生成独立统计表格，无需手动维护结构
• 无额外重复刷新，总耗时与手动执行「Refresh All」基本一致

现有方法的改进思路 #

方法1（表格内计时）优化 #

若坚持用 Power Query 内部逻辑计时，可通过以下方式降低误差：

• 关闭 Excel 「允许后台刷新」选项，强制查询串行执行
• 创建一个主控制查询，按指定顺序调用所有子查询，在主查询中为每个子查询添加独立的计时步骤（通过 DateTime.LocalNow() 记录前后时间差）
• 缺点：仍受 Power Query 缓存复用、内部优化逻辑影响，误差无法完全消除，仅适合低精度需求场景

方法2（表格外计时）优化 #

针对原方法需复制查询导致耗时翻倍的问题，可通过缓存复用优化：

• 复制查询后，将副本的数据源设置为原查询的加载结果，而非重新执行数据源逻辑
• 计时逻辑改为：刷新原查询→读取缓存→记录副本的加载时间（而非刷新时间）
• 缺点：配置复杂，缓存机制易受 Excel 版本、设置影响，稳定性远不如 VBA 方案

方案对比 #

内容的提问来源于stack exchange，提问作者user20396087