有没有更快的 CountIF

正如标题所说。是否有任何函数或 VBA 代码可以执行与 countif 相同的功能并且速度更快。目前正处于大规模计数阶段，它正在耗尽我的 CPU。

它只是工作表中的基本计数。不在 VBA 中。=countif(X:X,Y)然而，名单非常庞大。所以两个列表大约有 100,000~ 行

如果您可以不计算出现次数而只是想检查该值是否x存在于列中y's，然后返回布尔值 TRUE 或 FALSEISNUMBER 函数评价一个匹配功能查找将大大加快该过程。

=ISNUMBER(MATCH(S1, Y:Y, 0))

根据需要填写以获取所有回报。对返回的值进行排序和/或过滤以将结果制成表格。

附录：

显然是有的。的巨大改进匹配功能计算时间超过COUNTIF 函数让我想知道 MATCH 是否不能放入循环中，从而推进其中的第一个单元格查找数组参数为先前返回的行号加一，直到不再有匹配项。此外，后续查找相同数字（增加计数）的 MATCh 调用可能会变得越来越小查找数组通过按返回的行号调整（缩小）列的高度来调整单元格范围。如果处理后的值及其计数作为键和项存储在脚本字典中，则可以立即解决重复值，而无需处理计数。

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)然后复制并粘贴为值。

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 工作表函数。

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