数组公式VBA [英] Array formula to VBA
问题描述
我有一个Excel US preadsheet与名为城市和数据两个工作表。
数据页面包含108264行数据和列前进一路攀升列AT。
在城市工作，我从行到B4 210 B214城市列表。在它旁边（C列）是codeS用于每个城市（即多个codeS是怎么做的城市使用）计数的列表。在接下来的20列（列D至W）的应该显示最常用的codeS序列的每个城市（即最常见到最不常见）。我已经封闭样本伪数据的图像提供什么，我指的是一个图形化的重新presentation。
如果您将看到城1，例如（行4城市），你会发现它有5计数，以及最常用的code是5，那么4，然后3，然后2和最后1。如果你指的是数据的形象，你可以看到的相关性。
是我用这个样本集的数组公式如下：
在城市的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拖式开始，它会自动计算常用的codeS的基础上在previous列中的数据的频率。
的目标是这样的：为每个城市在城市工作表中所指出的，我想从搜索列B和列K至AT返回的20个最常用的codeS 数据工作表。所以它会查找在列B城，再看看对面到codeS中的列K至AT被普遍使用。
我有我使用该双数组公式（即计数最常用的code，不是取决于在previous列中的值，返回下一个最常用的code）。问题是，由于这样大的数据集，从而为每一个阵列式和每个细胞变得费时，而且减慢了Excel的US $ p $垫片相当
所以，这是我到目前为止已经试过：
- 数组公式（请参阅随附表）
- 下面VBAS。第一个返回的运行时错误'1004'无法设置范围类的的FormulaArray属性，而第二什么都不做。
任何建议或任加快数组公式，或修改VBA相应会大大AP preciated帮助。如果你有一个备用的VBA为好，那简直太pciated AP $ P $。
感谢。
子选项1（）
昏暗ř只要
当r = 4 214
Sheet2.Cells（R，210）.FormulaArray = _
= IFERROR（（MODE（IF（ISNUMBER（SEARCH（C＆放大器; CStr的（R）及数据$ B $ 2：$ B $一十万八千二百六十四）），IF（ISNUMBER（数据$ K2：$ AT108264）！ ！数据$ K2：$ AT108264）））），）
接下来ř
结束小组子选项2（） Sheet1.Range（C4）。FormulaArray = _
= IFERROR（（MODE（IF（ISNUMBER（SEARCH（C4，数据$ B $ 2：$ B $十万八千二百六十四）），IF（ISNUMBER（数据$ K2：$ AT108264），数据$ K2：$ AT108264））） ），） Sheet1.Range（D4：D214）FillDown。结束小组
第一个提示：
在的端部都你的 VBA公式的有，：
...数据$ K2：$ AT108264）））），）
在VBA中，如果你想在配方报价，你应该使用双qoutes：而不是，。
第二个技巧：
有没有必要使用循环应用公式给每个小区的范围：
当r = 4 214
Sheet2.Cells（R，210）.FormulaArray == IFERROR（... C4，...）
接下来ř
您code会的多的速度更快如果您要使用（列№ 210 是 HB ）
Sheet2.Range（HB4：HB214）FormulaArray == IFERROR（... C4，......）。
此方法将自动调整公式中的所有相关/混合引用：
- 在
HB4你会= IFERROR（... C4，...） - 在
HB5你会= IFERROR（... C5，...） - ...
- 在
HB214你会= IFERROR（... C214，...）
因此，工作code将是：
Sheet2.Range（HB4：HB214）FormulaArray == IFERROR（（MODE（IF（ISNUMBER（SEARCH（C4，数据$ B $ 2：！$ B $一〇八二六四） ），IF（ISNUMBER（数据$ K2：$ AT108264）！，$数据K2：$ AT108264）））），）
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.
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.
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.
The array formulas I've used for this sample set are as follows:
In D4 of "Cities"
{=IFERROR((MODE(IF(ISNUMBER(SEARCH(B4,Data!$B2:$B6)),IF(ISNUMBER(Data!$K2:$AT6),Data!$K2:$AT6)))),"")}
In E4 of "Cities"
{=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)))),"")),"")}
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.
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.
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.
So, this is what I’ve tried so far:
- Array formulas (please also refer to the enclosed sheet)
- 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.
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.
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$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
First tip:
In the end part of both your VBA formulas you have "":
...Data!$K2:$AT108264)))),"")"
In VBA if you want to include quotes in formula, you should use double qoutes: """" instead "".
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 much faster if you would use (column № 210 is 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,...)
So, working code would be:
Sheet2.Range("HB4:HB214").FormulaArray = "=IFERROR((MODE(IF(ISNUMBER(SEARCH(C4, Data!$B$2:$B$108264)),IF(ISNUMBER(Data!$K2:$AT108264),Data!$K2:$AT108264)))),"""")"
这篇关于数组公式VBA的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
