arrays - 用 VBA 内存数组替换工作表数组公式

Question

我的工作表需要 BG2 中的以下数组公式。

=INDEX('Client'!O$2:O$347473,
       MATCH(1, (('Client_Cost'!D$2:D$347473='Client'!BC2)*
                 ('Client_Cost'!E$2:E$347473='Client'!BE2)), 0))

这提供了两列匹配(Client_Cost!D:D 到 Client!BC2 AND Client_Cost!E:E 到 Client!BE2)，并从 Client!O:O 返回相应的值。

大量的行数使得数组公式的计算量非常大。我可以处理几百行(大约 90 秒处理 500 行)，但我需要一直到 Client'!BG347473 的结果，我希望在今年的某个时候得到它们。

我尝试过使用Application Evaluate将数组公式的结果返回到变体数组中，然后将结果数组返回到工作表中，但这并不是我所希望的改进。寻找替代方案。

Answer 1

首先，我建议开发具有较小数据集的替代方法。 5K 或 10K 行要么会表现出明显的改进，要么不会；一旦您确信自己不会陷入必须崩溃的长时间“无响应”状态，您随时可以扩展到原始数据集。

从该类型的数组公式 1 中删除数组的一种常见方法是使用“辅助”列，它将 Client_Cost 工作表中 D 列和 E 列中的两个值连接成单个分隔值。例如，在 Client_Cost!Z2 中，

=CONCATENATE(Client_Cost!D2, "|", Client_Cost!E2)

填充至 Client_Cost!Z347473 应该只需要一两秒。

设置完成后，会出现一个 INDEX/MATCH函数对可以在类似串联的 Client!BC2 和 Client'!BE2 上提供更加高效的查找。在 Client!BG2 中，

=INDEX(Client!O$2:O$347473,
   MATCH(CONCATENATE(Client!BC2, "|", Client!BE2), 
         Client_Cost'!Z$2:Z$347473, 0))

对于 350K 行，这将需要 1 小时 51 分钟。虽然还不是最优的，但与原始版本估计的约 17.5 小时相比已经有了很大的进步。

优化该方法的下一个逻辑步骤将是使用 VBA Scripting.Dictionary目的。字典在其键上保留自己的唯一索引，并且可以将连接的值填充到字典对象中，以方便对大量项目(即行)进行几乎即时的查找。

Sub JR_CSE_in_Array()
    Dim olr As Long, rws As Long, JR_Count As Long, JR_Values As Variant
    Dim v As Long, vTMP As Variant, vTMPs As Variant, dVALs As Object

    Debug.Print Timer
    Set dVALs = CreateObject("Scripting.Dictionary")

    'get some dimensions to the various data ranges
    With Worksheets("Client_Cost")
        'only use as many rows as absolutely necessary
        olr = Application.Min(.Cells(Rows.Count, "D").End(xlUp).Row, _
                              .Cells(Rows.Count, "E").End(xlUp).Row)
        'store D & E
        vTMPs = .Range(.Cells(2, 4), .Cells(olr, 5)).Value2

    End With
    With Worksheets("Client")
        rws = Application.Min(.Cells(Rows.Count, "BC").End(xlUp).Row, _
                              .Cells(Rows.Count, "BE").End(xlUp).Row, _
                              UBound(vTMPs, 1))
        'override the above statement for sampling
        'rws = 5000

        'building the Dictionary object takes a fair bit of time but it is worth it
        vTMP = .Range(.Cells(2, 15), .Cells(olr, 15)).Value2
        For v = LBound(vTMPs, 1) To UBound(vTMPs, 1)
            If Not dVALs.Exists(Join(Array(vTMPs(v, 1), vTMPs(v, 2)), ChrW(8203))) Then _
                dVALs.Add Key:=Join(Array(vTMPs(v, 1), vTMPs(v, 2)), ChrW(8203)), Item:=vTMP(v, 1)
        Next v

        'store BC and BE
        vTMPs = .Range(.Cells(2, 55), .Cells(olr, 57)).Value2
    End With

    ReDim JR_Values(1 To rws, 1 To 1)   'force a two-dimension, one-based index on the array
    'Debug.Print LBound(JR_Values) & ":" & UBound(JR_Values)

    For JR_Count = LBound(JR_Values, 1) To UBound(JR_Values, 1) Step 1
        If dVALs.Exists(Join(Array(vTMPs(JR_Count, 1), vTMPs(JR_Count, 3)), ChrW(8203))) Then
            JR_Values(JR_Count, 1) = dVALs.Item(Join(Array(vTMPs(JR_Count, 1), vTMPs(JR_Count, 3)), ChrW(8203)))
        End If
    Next JR_Count

    With Worksheets("Client")
        .Range("BG2").Resize(UBound(JR_Values), 1) = JR_Values
    End With

    'Debug.Print dVALs.Count
    dVALs.RemoveAll: Set dVALs = Nothing
    Debug.Print Timer
End Sub

该例程运行所用的时间(没有辅助列)为 45.72 秒。分解一下，仅仅构建字典就花了整整 13.4 秒，剩下的大部分时间都被实际查找所占用，其中半秒的时间归因于工作表值中变体数组的批量播种。

         

所以 Scripting.Dictionary 是这里明显的赢家。不幸的是，当值发生变化时，它不会自动计算各个列中的更新，但在开发的这个阶段，工作表应设置为手动计算。将基于公式的解决方案之一设置为来自单个重新输入值的重新计算事件似乎是一种低效的时间消耗。

总而言之，这是完全有道理的。原始数组公式类似于在两个字段上使用 INNER JOIN 的 SQL SELECT 语句，如果我的 SELECT 语句运行效率低下，我要做的第一件事就是查看表的索引。

On a related note, any workbook with this much data should be saved as a Excel Binary Workbook regardless of whether it is macro-enabled or not. The file size of a binary workbook (.XLSB) is typically ¹⁄₃ the size of an equivalent .XLSX or .XLSM. Beyond a faster initial load time, many bulk operations should prove faster.

任何想要测试自己优化的人都可以找到我的示例 .XLSB 工作簿 here暂且。在没有先了解您要进入的内容之前，不要盲目地运行这些过程。

<小时/>

1 _{数组公式需要使用 Ctrl+Shift+Enter↵ 来完成。一旦正确输入第一个单元格，就可以像任何其他公式一样向下或向右填充或复制它们。尝试将整列引用减少到更接近地代表实际数据范围的范围。数组公式以对数方式消耗计算周期，因此最好将引用范围缩小到最小值。请参阅Guidelines and examples of array formulas了解更多信息。}