它只是工作表中的一个基本的countif。不在VBA = countif（X：X，Y）然而，列表是巨大的。所以这两个列表都是大约10万行
解决方案如果你可以没有计数的发生，只需要检查价值 x 存在于 y的列中，然后返回一个布尔值为TRUE或FALSE的 ISNUMBER函数评估 MATCH功能查找将极大地加快进程。
= ISNUMBER（MATCH（S1，Y：Y，0））根据需要填写以捕获所有回报。排序和/或过滤返回的值以列出结果。
附录：
我用过10K行，列A和B由 RANDBETWEEN（1，999）填充然后复制并粘贴为值。
经过时间： 测试1¹ - 10K行×2列填充RANDBETWEEN（1，999） COUNTIF公式：15.488秒_ MATCH公式：1.592秒 测试2² - 10K行×2列填充RANDBETWEEN（1，99999） COUNTIF公式：14.722秒 MATCH公式：3.484秒_ 我还将COUNTIF公式中的值复制到另一列中，并将其与编码MATCH函数返回的值进行比较。它们在10K行中是相同的。 ¹更多倍数少零计数 ²更多零计数，较少倍数
虽然数据的性质明显地有所不同，但编码的MATCH函数每次都优于本地COUNTIF工作表函数。
不要忘记VBE的工具►参考文献►Microsoft Scripting Dictionary。
As the title says. Is there any function or VBA code which does the same function as a countif and is a lot faster. Currently in the middle of massive countif and it is just eating up my CPU.
It is just a basic countif inside the worksheet. Not in VBA. =countif(X:X,Y) However the lists are massive. So both lists are around 100,000~ rows
解决方案If you can do without a count of the occurances and simply wish to check if the value x exists in the column of y's, then returning a boolean TRUE or FALSE with the ISNUMBER function evaluating a MATCH function lookup will greatly speed up the process.
=ISNUMBER(MATCH(S1, Y:Y, 0))Fill down as necessary to catch all returns. Sort and/or filter the returned values to tabulate results.
Addendum:
Apparently there is. The huge improvement in the MATCH function calculation times over the COUNTIF function made me wonder if MATCH couldn't be put into a loop, advancing the first cell in its lookup_array parameter to the previously returned row number plus one until there were no more matches. Additionally, subsequent MATCh calls to lookup the same number (increasing the count) could be made to increasingly smaller lookup_array cell ranges by resizing (shrinking) the height of the column by the returned row number as well. If the processed values and their counts were stored as keys and items in a scripting dictionary, duplicate values could be instantly resolved without processing a count.
Sub formula_countif_test() Dim tmr As Double appOFF tmr = Timer With Sheet2.Cells(1, 1).CurrentRegion With .Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count) 'skip header .Cells(1, 3).Resize(.Rows.Count, 1).FormulaR1C1 = _ "=countif(c1, rc2)" 'no need for calculate when blocking in formulas like this End With End With Debug.Print "COUNTIF formula: " & Timer - tmr appON End Sub Sub formula_match_test() Dim rw As Long, mrw As Long, tmr As Double, vKEY As Variant 'the following requires Tools, References, Microsoft Scripting Dictionary Dim dVALs As New Scripting.dictionary dVALs.CompareMode = vbBinaryCompare 'vbtextcompare for non-case sensitive appOFF tmr = Timer With Sheet2.Cells(1, 1).CurrentRegion With .Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count) 'skip header For rw = 1 To .Rows.Count vKEY = .Cells(rw, 2).Value2 If Not dVALs.Exists(vKEY) Then dVALs.Add Key:=vKEY, _ Item:=Abs(IsNumeric(Application.Match(vKEY, .Columns(1), 0))) If CBool(dVALs.Item(vKEY)) Then mrw = 0: dVALs.Item(vKEY) = 0 Do While IsNumeric(Application.Match(vKEY, .Columns(1).Offset(mrw, 0).Resize(.Rows.Count - mrw + 1, 1), 0)) mrw = mrw + Application.Match(vKEY, .Columns(1).Offset(mrw, 0).Resize(.Rows.Count - mrw + 1, 1), 0) dVALs.Item(vKEY) = CLng(dVALs.Item(vKEY)) + 1 Loop End If .Cells(rw, 3) = CLng(dVALs.Item(vKEY)) Else .Cells(rw, 3) = CLng(dVALs.Item(vKEY)) End If Next rw End With End With Debug.Print "MATCH formula: " & Timer - tmr dVALs.RemoveAll: Set dVALs = Nothing appON End Sub Sub appON(Optional ws As Worksheet) Application.ScreenUpdating = True Application.EnableEvents = True Application.Calculation = xlCalculationAutomatic End Sub Sub appOFF(Optional ws As Worksheet) Application.ScreenUpdating = False Application.EnableEvents = False Application.Calculation = xlCalculationManual End Sub
I used 10K rows with columns A and B filled by RANDBETWEEN(1, 999) then copied and pasted as values.
Elapsed times: Test 1¹ - 10K rows × 2 columns filled with RANDBETWEEN(1, 999) COUNTIF formula: 15.488 seconds MATCH formula: 1.592 seconds Test 2² - 10K rows × 2 columns filled with RANDBETWEEN(1, 99999) COUNTIF formula: 14.722 seconds MATCH formula: 3.484 seconds I also copied the values from the COUNTIF formula into another column and compared them to the ones returned by the coded MATCH function. They were identical across the 10K rows. ¹ More multiples; less zero counts ² More zero counts, less multiples
While the nature of the data clearly makes a significant difference, the coded MATCH function outperformed the native COUNTIF worksheet function every time.
Don't forget the VBE's Tools ► References ► Microsoft Scripting Dictionary.
更多推荐
有更快的CountIF吗
发布评论