阵列COUNTIF替补 - 计数(MATCH()) [英] Array CountIf Substitute - Count(Match())
问题描述
核心问题
我怎么能执行重复 COUNTIF()
在尽可能的范围内尽可能高效(性能明智)■?
结果
详情关注
范围阵列
由于每个读/写,以较慢的VBA code A S preadsheet结果,最好是尽可能少的次数尽可能地这样做。通常情况下,如果有人反复的读/写一个范围,他或她应该首先这个范围内保存到一个数组,执行操作的数组,然后做最后的读或必要时写到S preadsheet。
结果
示例值和code
我如何可以使用的范围内执行
上面计算计数每个值,并将其写入 COUNTIF()
取值A2:A11 D2:D7
?我希望下面的code的工作:
子M1ArrayCount()昏暗的arrNumbers()为Variant
昏暗Long1只要
昏暗循环1只要arrNumbers()= ThisWorkbook.Sheets(1).Range(A2:A11)值与ThisWorkbook.Sheets(1)
对于循环1 = 1到6
.Cells(循环1 + 1,4)。价值= Application.CountIf(arrNumbers(),循环1)
下一步循环1
结束与结束小组
COUNTIF()不使用数组
不过,由于 Application.CountIf()
只能使用范围,而不是数组, D2:D7
所有节目运行上面的code后 VALUE!
错误。替代品必须被发现。
解决方案 - 计数(MATCH())
我们正在寻找解决的办法是:
Application.Count(Application.Match(SavedArray(),数组([Lookup_Array中]),0))
结果
什么?这是如何工作的?
这通常会返回行号与阵列配对和功能怎么能算到返回正确的答案?你怎么能指望行号?
力学解释
使用非常感谢@Jeeped,这里的工作原理是:
这是一个无证功能匹配(查找值,查找数组,0)
,如果你把一个数组作为查找值,它将 MATCH()
您对查找阵列输入数组中的每个值。因此,上面的例子中,对于环路= 1
,就会变成:
{匹配(A2,阵列(1),0),匹配(A3,阵列(1),0),...匹配(A11,阵列(1 ),0)}
然后,每@Jeeped:
每个比赛或者返回一个数字或一个错误。在
计数()
函数计算数字,而不是错误。所以,你得到的是否有任何值的计数A1:A11
匹配循环1
块引用>最后code和值
子M1ArrayCount()昏暗的arrNumbers()为Variant
昏暗Long1只要
昏暗循环1只要arrNumbers()= ThisWorkbook.Sheets(1).Range(A2:A11)值与ThisWorkbook.Sheets(1)
对于循环1 = 1到6
.Cells(循环1 + 1,4)。价值= Application.Count(Application.Match(arrNumbers(),阵列(循环1),0))
下一步循环1
结束与结束小组结果
参考
<一个href=\"http://stackoverflow.com/questions/28507501/vba-why-does-application-countif-return-an-array-or-error-424#comment45355497_28507501\">This评论
<一个href=\"http://www.mrexcel.com/forum/excel-questions/739653-countif-visual-basic-applications-array-without-loop.html#post3635486\"相对=nofollow>这个答案
问
例如:
myArray的=阵列(第一,第二,第二,第三,第四)
然后会在
COUNTIF(myarray中,第二)
语法是什么? (结果
应该等于2个字)
块引用>
答
也尝试:
MSGBOX Application.Count(Application.Match(myarray的,阵列(二),0))
块引用>
块引用>Core Question
How can I perform repeated
CountIf()
s on a range as efficiently (performance-wise) as possible?
Concerns in Detail
Range to Array
Since each read/write to a spreadsheet results in slower VBA code, it is advisable to do so as few times as possible. Normally, if someone is repeatedly reading/writing to a range, he or she should first save that range to an array, perform the operations to the array, and then do a final read or write to the spreadsheet if necessary.
Example Values and CodeHow can I use perform
CountIf()
s on the range ofA2:A11
above to calculate the count of each value and write them toD2:D7
? I would expect the below code to work:Sub M1ArrayCount() Dim arrNumbers() As Variant Dim Long1 As Long Dim Loop1 As Long arrNumbers() = ThisWorkbook.Sheets(1).Range("A2:A11").Value With ThisWorkbook.Sheets(1) For Loop1 = 1 To 6 .Cells(Loop1 + 1, 4).Value = Application.CountIf(arrNumbers(), Loop1) Next Loop1 End With End Sub
CountIf() Doesn't Work with Arrays
However, because
Application.CountIf()
only works with ranges and not arrays,D2:D7
all show#VALUE!
errors after running the above code. A substitute must be found.解决方案Solution - Count(Match())
The solution we are looking for is:
Application.Count(Application.Match(SavedArray(), Array([lookup_value]), 0))
What? How does that work?
How can a function that normally returns a row number be paired with an array and count to return the correct answer? How can you count row numbers?
Explanation of Mechanics
With many thanks to @Jeeped, here's how it works:
It's an undocumented feature of
Match(lookup value, lookup array, 0)
that if you put an array as the lookup value, it willMatch()
each value in the array you entered against the lookup array. Thus, for the above example, forLoop = 1
, it will become:
{match(A2, Array("1"), 0),match(A3, Array("1"), 0), ... match(A11, Array("1"), 0)}
Then, per @Jeeped:
Each match will either return a number or an error. The
Count()
function counts numbers, not errors. So you get a count of whether any of the values inA1:A11
matchLoop1
.Final Code and Values
Sub M1ArrayCount() Dim arrNumbers() As Variant Dim Long1 As Long Dim Loop1 As Long arrNumbers() = ThisWorkbook.Sheets(1).Range("A2:A11").Value With ThisWorkbook.Sheets(1) For Loop1 = 1 To 6 .Cells(Loop1 + 1, 4).Value = Application.Count(Application.Match(arrNumbers(), Array(Loop1), 0)) Next Loop1 End With End Sub
References
Question
Example:
myarray = array("First","Second","Second","Third","Fourth")
then what would the
countif(myarray,"second")
syntax be? (the result should equal 2 counts)Answer
Try also:
MsgBox Application.Count(Application.Match(myArray, Array("Second"), 0))
这篇关于阵列COUNTIF替补 - 计数(MATCH())的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!