数组公式到 VBA

Array formula to VBA

提问by user2722253

I have an Excel spreadsheet with two worksheets titled “Cities” and “Data”. The "Data" page contains 108264 rows of data, and columns progress all the way up to column AT.

我有一个 Excel 电子表格，其中包含两个名为“城市”和“数据”的工作表。“数据”页面包含 108264 行数据，列一直进展到列 AT。

Under the Cities worksheet, I have a list of 210 cities from rows B4 to B214. Next to it (Column C) is a list of the counts of codes used for each city (i.e. how many codes did that city use). The next 20 columns (Columns D to W) should show a sequence of the most commonly used codes for each city (i.e. most common to least common). I've enclosed images with sample pseudo dataset to provide a graphical representation of what I'm referring to.

在城市工作表下，我有一个从 B4 行到 B214 行的 210 个城市的列表。旁边（C 列）是每个城市使用的代码计数列表（即该城市使用了多少代码）。接下来的 20 列（D 列到 W 列）应显示每个城市最常用的代码序列（即最常见到最不常见）。我用示例伪数据集附上了图像，以提供我所指内容的图形表示。

If you'll look at City "1" for example (row 4 "Cities") you will notice it has a Count of 5, and the most frequently used code is 5, then 4, then 3, then 2 and finally 1. If you refer to the "Data" image, you can see the correlation.

例如，如果您查看城市“1”（第 4 行“城市”），您会注意到它的计数为 5，最常用的代码是 5，然后是 4，然后是 3，然后是 2，最后是 1。如果您参考“数据”图像，您可以看到相关性。

The array formulas I've used for this sample set are as follows:

我用于此样本集的数组公式如下：

In D4 of "Cities"

在“城市”的D4

      {=IFERROR((MODE(IF(ISNUMBER(SEARCH(B4,Data!$B2:$B6)),IF(ISNUMBER(Data!$K2:$AT6),Data!$K2:$AT6)))),"")}

In E4 of "Cities"

《城市》E4

{=IFERROR(MODE(IFERROR(SMALL(IF(ISNUMBER(SEARCH($B, Data!$B2:$B6))*ISNUMBER(1/Data!$K2:$AT6)*ISNA(MATCH(Data!$K2:$AT6,$D4:D4,0)),Data!$K2:$AT6,""),ROW(INDEX($A:$A,1):INDEX($A:$A,COUNT(Data!$K2:$AT6)))),"")),"")}

Then I drag the formula from E4 onwards, and it automatically counts the frequency of the commonly used codes based on the data in the previous column.

然后我把公式从E4往上拖，它会根据上一栏的数据自动统计常用代码出现的频率。

The objective is this:for each city noted in “Cities” worksheet, I'd like to return those 20 most commonly used codes by searching Columns B and Columns K to AT from the “Data” Worksheet. So it would look up the city in Column B, then look across to which codes were commonly used in Columns K to AT.

目标是这样的：对于“城市”工作表中注明的每个城市，我想通过从“数据”工作表中搜索 B 列和 K 列到 AT 来返回这 20 个最常用的代码。所以它会在 B 列中查找城市，然后查看 K 列到 AT 列中常用的代码。

I do have two array formulas I use for this (i.e. that counts the most used code, than depending on the value in the previous column, returns the next most commonly used code). The problem is, due to such a large dataset, creating an array formula for each and every cell becomes time consuming, and slows down the Excel spread sheet considerably.

我确实为此使用了两个数组公式（即计算最常用的代码，而不是根据上一列中的值，返回下一个最常用的代码）。问题是，由于数据集如此庞大，为每个单元格创建数组公式变得非常耗时，并且会大大降低 Excel 电子表格的速度。

So, this is what I've tried so far:

所以，这是我迄今为止尝试过的：

1. Array formulas (please also refer to the enclosed sheet)
2. Below VBAs. First one returns a Run-time error ‘1004' Unable to set the FormulaArray property of the Range Class, while the second does nothing.

1. 数组公式（另请参阅随附的表格）
2. 低于 VBA。第一个返回运行时错误 '1004' Unable to set the FormulaArray 属性的 Range Class，而第二个什么也不做。

Any suggestions or help on either speeding up the array formulas, or modifying the VBA accordingly would be greatly appreciated. If you have an alternate VBA as well, that'd be appreciated too.

任何有关加速数组公式或相应修改 VBA 的建议或帮助将不胜感激。如果您也有备用 VBA，那也将不胜感激。

Thanks.

谢谢。

Sub Option1()
     Dim r As Long
     For r = 4 To 214
        Sheet2.Cells(r, 210).FormulaArray = _
        "=IFERROR((MODE(IF(ISNUMBER(SEARCH(C" & CStr(r) & ", Data!$B:$B8264)),IF(ISNUMBER(Data!$K2:$AT108264),Data!$K2:$AT108264)))),"")"
    Next r
 End Sub



Sub Option2()

    Sheet1.Range("C4").FormulaArray = _
        "=IFERROR((MODE(IF(ISNUMBER(SEARCH(C4, Data!$B:$B8264)),IF(ISNUMBER(Data!$K2:$AT108264),Data!$K2:$AT108264)))),"")"

    Sheet1.Range("D4:D214").FillDown

End Sub

回答by Dmitry Pavliv

First tip:

第一个提示：

In the end part of both your VBA formulasyou have "":

在你的VBA 公式的最后部分，你有""：

...Data!$K2:$AT108264)))),"")"

In VBA if you want to include quotes in formula, you should use double qoutes: """"instead "".

在 VBA 中，如果你想在公式中包含引号，你应该使用 double qoutes:""""代替""。

Second tip:

第二个提示：

There is no need to use loop to apply formula to each cell in the range:

无需使用循环将公式应用于范围内的每个单元格：

For r = 4 To 214
    Sheet2.Cells(r, 210).FormulaArray = "=IFERROR(...C4,...)"
Next r

Your code would be muchfasterif you would use (column № 210is HB):

您的代码将是多快，如果你会使用（列№210是HB）：

 Sheet2.Range("HB4:HB214").FormulaArray = "=IFERROR(...C4,...)"

This approach would automatically adjust all relative/mixed references in your formula:

这种方法会自动调整公式中的所有相对/混合引用：

• in HB4you would have =IFERROR(...C4,...)
• in HB5you would have =IFERROR(...C5,...)
• ...
• in HB214you would have =IFERROR(...C214,...)

• 在HB4你会有=IFERROR(...C4,...)
• 在HB5你会有=IFERROR(...C5,...)
• ...
• 在HB214你会有=IFERROR(...C214,...)

So, working code would be:

因此，工作代码将是：

Sheet2.Range("HB4:HB214").FormulaArray = "=IFERROR((MODE(IF(ISNUMBER(SEARCH(C4, Data!$B:$B8264)),IF(ISNUMBER(Data!$K2:$AT108264),Data!$K2:$AT108264)))),"""")"