更换VBA存储阵列的工作表数组公式 [英] Replace worksheet array formula with VBA memory array

问题描述

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

My worksheet requires the following array formula in 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！给客户BC2和Client_Cost E：！E！给客户BE2），并从客户端返回对应的值○：0

This provides a two-column match (Client_Cost!D:D to Client!BC2 AND Client_Cost!E:E to Client!BE2) and returns the corresponding value from Client!O:O.

在大量的行使得数组公式非常密集型计算。我可以处理几百行（〜500行90秒），但我需要的结果到客户端一路'！BG347473，我希望他们在今年某个时候。

The large number of rows makes the array formula very calculation-intensive. I can deal with a few hundred rows (~90 seconds for 500 rows) but I need results all the way down to Client'!BG347473 and I would like them sometime this year.

我试过href=\"https://msdn.microsoft.com/en-us/library/office/ff193019.aspx\" rel=\"nofollow\">应用评估返回从数组公式到一个变量数组的结果，随后的结果数组返回到工作表的集体的，但它不是我所期待的改善。寻找替代品。

I've tried using Application Evaluate to return a result from the array formula into an variant array and subsequently returning the array of results to the worksheet en masse but it isn't the improvement I was hoping for. Looking for alternatives.

推荐答案

首先，我会建议制定一个较小的数据集的替代方法。 5K或10K行要么表现出明显的改善与否;你总是可以扩展到原始数据集，一旦你确信你不会进入一个长期的'没有响应'的状态，你必须要崩溃掉的。

First off, I would recommend developing alternative methods with a smaller data set. 5K or 10K rows will either demonstrate a noticeable improvement or not; you can always expand to the original data set once you are confident you are not going to get into a long 'Not responding' state that you have to crash out of.

从阵列formula¹的那种风格删除的阵列的的常用方法是，并置从Client_Cost工作表中的列D和E两个值成一个单一的，分隔值'帮手'列。例如，在Client_Cost！Z2如，

A common method of removing the array from that style of array formula¹ is a 'helper' column that concatenates the two values from column D and E in the Client_Cost worksheet into a single, delimited value. For example, in Client_Cost!Z2 as,

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

填充到Client_Cost！Z347473应该只需要一两秒钟。

Filled down to Client_Cost!Z347473 should only take a second or two.

一旦建立，单一的 INDEX / MATCH 功能对可以在同样的客户端连接起来提供一个浩大地更加有效的查找！BC2和客户端'！BE2。在客户端！BG2为，

Once that is set up, a single INDEX/MATCH function pair can provide a vastly more efficient lookup on a similarly concatenated Client!BC2 and Client'!BE2. In Client!BG2 as,

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

这将需要1小时，51分钟350K行。虽然没有达到最佳效果，这是在预计〜17.5小时，原来花了很大的改进。

That will take 1 hr, 51 minutes for 350K rows. While not yet optimal, it is a big improvement over the estimated ~17.5 hours that the original took.

在优化该方法的一个合乎逻辑的步骤将用VBA 的Scripting.Dictionary对象。字典持有其键自己独特的索引和串联的值可以塞进一个字典对象，以方便对大量项目（即的行的）。

The next logical step in optimizing that method would be working with a VBA Scripting.Dictionary object. A dictionary holds its own unique index on its keys and the concatenated values could be stuffed into a dictionary object to facilitate virtually instantaneous lookups on a large number of items (i.e. rows).

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秒刚刚建立字典，其余主要是采取了由实际查找了半秒在这里和那里的工作表值​​归因于变长数组的散装种子。

The elapsed time for that routine to run (without helper column(s)) was 45.72 seconds. Breaking it down, it took a full 13.4 seconds just to build the dictionary and the remainder was largely taken up by the actual lookup with a half-second here and there attributed to the bulk seeding of the variant arrays from the worksheets' values.

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

So the Scripting.Dictionary is the clear winner here. Unfortunately, it is not automatically calculating updates in the various columns when values change but at this stage of development, the worksheet should be set to manual calculation. Setting one of the formula-based solutions into a recalculation event from a single retyped value seems an inefficient expenditure of time.

ALL-IN-一切，这非常有意义。原来数组公式类似于与内两个字段，如果我的SELECT语句的JOIN正在运行低效我会做些什么来改善它会看看表索引的第一件事，一个SQL SELECT语句。

All-in-all, this makes perfect sense. The original array formula is analogous to an SQL SELECT statement with an INNER JOIN on two fields and if my SELECT statement was running inefficiently the first thing I would do to improve it would be to look at the tables' indexes.

在一个相关的说明，有了这么多数据的工作簿，应不论它是或未启用宏保存为Excel的二进制工作簿。二进制工作簿（.XLSB）的文件大小通常为¹/₃等效.XLSX或.XLSM的大小。除了更快的初始加载时间，许多批量操作应该被证明更快。

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工作簿 暂时。不要盲目地运行程序没有看到你进入第一个东西。

Anyone wishing to test their own optimizations can find my sample .XLSB workbook here for the time being. Don't blindly run the procedures without seeing what you're getting into first.

¹<子>数组公式需要用<大骨节病>控制 + <大骨节病>移 + <大骨节病>Enter↵敲定。一旦进入第一小区正确，它们可以填充或者复制向下或向右就像任何其他公式。尝试更加紧密地降低全列引用到的范围重新presenting实际数据的范围。数组公式嚼起来计算周期对数，因此是缩小范围内引用到最小好的做法。见<一href=\"https://support.office.com/en-ca/article/guidelines-and-examples-of-array-formulas-7d94a64e-3ff3-4686-9372-ecfd5caa57c7\"相对=nofollow>准则和数组公式示例了解详情。

¹ _{Array formulas need to be finalized with Ctrl+Shift+Enter↵. Once entered into the first cell correctly, they can be filled or copied down or right just like any other formula. Try and reduce your full-column references to ranges more closely representing the extents of your actual data. Array formulas chew up calculation cycles logarithmically so it is good practise to narrow the referenced ranges to a minimum. See Guidelines and examples of array formulas for more information.}

这篇关于更换VBA存储阵列的工作表数组公式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！