有没有更快的 CountIF [英] Is there a faster CountIF

问题描述

正如标题所说.是否有任何函数或 VBA 代码与 countif 具有相同的功能并且速度要快得多.目前在大量的countif中间，它只是在吃我的CPU.

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.

它只是工作表中的一个基本计数.不是在 VBA 中.=countif(X:X,Y) 然而，列表是巨大的.所以两个列表都在 100,000~ 行左右

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

推荐答案

如果您可以不计算出现次数，而只想检查 x 值是否存在于 y's，然后使用 ISNUMBER 函数 评估 MATCH 函数 查找将大大加快进程.

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.

附录:

显然有.MATCH 函数的巨大改进 COUNTIF 函数的计算时间 让我怀疑是否无法将 MATCH 放入循环中，将其 lookup_array 参数中的第一个单元格推进到先前返回的行号加一，直到没有更多匹配项.此外，通过按返回的行号调整(缩小)列的高度，可以使随后的 MATCh 调用查找相同的数字(增加计数)以逐渐缩小 lookup_array 单元格范围.如果将处理后的值及其计数作为键和项存储在脚本字典中，则无需处理计数即可立即解决重复值.

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

我使用了 10K 行，其中 A 列和 B 列由 RANDBETWEEN(1, 999) 填充，然后复制并粘贴为值.

I used 10K rows with columns A and B filled by RANDBETWEEN(1, 999) then copied and pasted as values.

已用时间:

测试 1¹ - 10K 行 × 2 列填充 RANDBETWEEN(1, 999)
COUNTIF 公式: 15.488 秒
匹配公式: 1.592 秒

测试 2² - 10K 行 × 2 列填充 RANDBETWEEN(1, 99999)
COUNTIF 公式: 14.722 秒
匹配公式: 3.484 秒

我还将 COUNTIF 公式中的值复制到另一列中，并将它们与编码的 MATCH 函数返回的值进行比较.它们在 10K 行中是相同的.
¹ ^{更多倍数；减少零计数}
² ^{更多的零计数，更少的倍数}

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} 

虽然数据的性质明显有显着差异，但编码的 MATCH 函数每次都优于原生的 COUNTIF 工作表函数.

While the nature of the data clearly makes a significant difference, the coded MATCH function outperformed the native COUNTIF worksheet function every time.

不要忘记 VBE 的工具 ► 参考资料 ► Microsoft 脚本词典.

Don't forget the VBE's Tools ► References ► Microsoft Scripting Dictionary.

这篇关于有没有更快的 CountIF的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！