将数组公式转换为VBA

Question

将数组公式转换为VBA

4

我有一个名为“Cities”和“Data”的Excel电子表格，其中“Data”页面包含108264行数据，并且列延伸到AT列。

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

如果您查看例如City“1”（第4行“Cities”），您会注意到它具有5个计数，并且最常使用的代码是5，然后是4，3，2和最后是1。如果您参考“Data”图像，则可以看到相关性。

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

在“Cities”的D4中：

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

在《城市》的E4中

{=IFERROR(MODE(IFERROR(SMALL(IF(ISNUMBER(SEARCH($B$4, 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)))),"")),"")}

然后我从E4开始拖动公式，它会根据前一列的数据自动计算常用代码的频率。

目标是：对于“Cities”工作表中记录的每个城市，我想通过在“Data”工作表中搜索B列和K到AT列来返回那些使用最广泛的20个代码。因此，它将在B列中查找城市，然后查找哪些代码通常在K到AT列中使用。

我有两个数组公式可以用于此（即计算最常用的代码，然后根据前一列的值返回下一个最常用的代码）。问题在于，由于数据集太大，为每个单元格创建一个数组公式变得耗时，并且明显减慢了Excel的速度。

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

1. 数组公式（请参见附件） 2. 下面的VBA。第一个返回运行时错误“1004”，无法设置Range类的FormulaArray属性，而第二个则什么也不做。

如果您对加快数组公式的速度或相应地修改VBA有任何建议或帮助，我将不胜感激。如果您还有其他VBA，请告诉我。

谢谢。

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$2:$B$108264)),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$2:$B$108264)),IF(ISNUMBER(Data!$K2:$AT108264),Data!$K2:$AT108264)))),"")"

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

End Sub

- user2722253

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Dmitry Pavliv · Accepted Answer

第一条建议：

在你的VBA公式的结尾部分，都有""：

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

在VBA中，如果您想在公式中包含引号，则应使用两个引号："""" 而不是 ""。

第二个技巧：

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

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

如果您使用了以下代码（第210列是HB），则您的代码将会运行得更快：

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

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

在HB4中，您将有=IFERROR(...C4,...)
在HB5中，您将有=IFERROR(...C5,...)
...
在HB214中，您将有=IFERROR(...C214,...)

因此，可行的代码是：

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