logo标签列表

有没有更快的CountIF函数？

vbaexcel
7

正如标题所说，是否有任何函数或VBA代码可以执行与countif相同的功能，且速度更快。当前正在执行大量的countif操作，而且它只是消耗我的CPU。

这只是工作表中的基本countif。而不是在VBA中。 =countif(X:X,Y) 然而列表非常庞大。因此，两个列表大约有100,000个行。

不确定是否更快，但您可以尝试在“IF”部分筛选列，然后获取“Range.SpecialCells(xlVisible).Count”。注意：不确定“xlVisible”是否是正确的枚举，但您明白我的意思。 - FreeMan
我所要做的就是查看x列表是否在y列表中。我只想要任何零。所以不确定我能否这样分割它。 - Sam
这只是一个简单的工作簿函数，位于实际工作表内部的=countif(X:X,y)。但是列表非常庞大。 - Sam
你是想获取两个列表中都出现的值的数量吗？ - Tom
我正在尝试计算列表X中列表Y的元素出现次数。 - Sam
显示剩余12条评论
4个回答
18
如果您不需要计算出现次数，只是想检查值 x 是否存在于 y 列中，则使用 ISNUMBER 函数 评估 MATCH 函数 查找并返回一个布尔值 TRUE 或 FALSE 将大大加快进程。请保留 HTML 标签。
=ISNUMBER(MATCH(S1, Y:Y, 0))

根据需要填充以捕捉所有回报。对返回的值进行排序和/或筛选，以制表结果。

附加说明：

显然是有的。MATCH函数计算时间大幅提高，比COUNTIF函数更快，这让我想知道是否可以将MATCH放入循环中，将其lookup_array参数中的第一个单元格前进到先前返回的行号加一，直到没有更多匹配为止。此外，后续的MATCh调用来查找相同的数字（增加计数）也可以通过调整列的高度来缩小lookup_array单元格范围，并随着返回的行数而逐渐变小。如果处理的值及其计数被存储为脚本字典中的键和项，则可以立即解决重复值，而无需处理计数。

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

        Sample Data for MATCH_COUNTIF

我使用了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。
1
我使用以下技巧来代替COUNTIF。我有115k行数据，计算步骤基本上是瞬时完成的，但是设置需要花费更多时间。
1. 将要计数的数据复制到一个新表格的A列中。 2. 对要计数的数据进行排序（使所有相同的项目相邻）。 3. 在B列中放入以下公式=IF(A2=A1,B2+1,1)。用公式填充该列，然后粘贴值。 4. 在C列中放置一个连续的数字（只需从1、2、3、4...一直到您拥有的行数）。 5. 按C列降序对所有内容进行排序。结果是在B列中，最大的计数排在第一位。 6. 选择A列和B列，然后使用“删除重复项”功能。现在您只剩下每个不同数据行的一个条目和每个最大计数。 7. 回到您的实际数据表中，使用=VLOOKUP(A2,Sheet2!A:B,2,false)获取计数。
如果您想将此转换为宏，只需在执行上述操作时使用记录宏即可。
0

在排序数据后，COUNTIF有一个简单的解决方法。您可以将此添加到您的VB脚本中并运行。对于大约1万行项目的数据，普通的COUNTIF需要近10-15分钟。这个脚本将在小于10秒内获取计数。

Sub alternateFunctionForCountIF()
    Dim DS As Worksheet
    Set DS = ThisWorkbook.ActiveSheet
    
    Dim lcol As Integer
    lcol = DS.Cells(1, Columns.Count).End(xlToLeft).Column
    Dim fieldHeader As String
    
    Dim lrow As Long, i As Long, j As Long
    Dim countifCol As Integer, fieldCol As Integer
    
    fieldHeader = InputBox("Enter the column header to apply COUNTIF")
    If Len(fieldHeader) = 0 Then
        MsgBox ("Invalid input. " & Chr(13) & "Please enter the column header text and try again")
        Exit Sub
    End If
    For i = 1 To lcol
        If fieldHeader = DS.Cells(1, i).Value Then
            fieldCol = i
            Exit For
        End If
    Next i
    If fieldCol = 0 Then
        MsgBox (fieldHeader & " could not be found among the headers. Please enter a valid column header")
        Exit Sub
    End If
    
    countifCol = fieldCol + 1
    lrow = DS.Cells(Rows.Count, "A").End(xlUp).Row
    DS.Range(DS.Cells(1, countifCol).EntireColumn, DS.Cells(1, countifCol).EntireColumn).Insert
    DS.Cells(1, countifCol) = fieldHeader & "_count"
    
    DS.Sort.SortFields.Clear
    DS.Sort.SortFields.Add Key:=Range(DS.Cells(2, fieldCol), DS.Cells(lrow, fieldCol)), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With DS.Sort
        .SetRange Range(DS.Cells(1, 1), DS.Cells(lrow, lcol))
        .header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
    Dim startPos As Long, endPos As Long
    Dim checkText As String
    For i = 2 To lrow
        checkText = LCase(CStr(DS.Cells(i, fieldCol).Value))
        
        If (checkText <> LCase(CStr(DS.Cells(i - 1, fieldCol).Value))) Then
            startPos = i
        End If
        If (checkText <> LCase(CStr(DS.Cells(i + 1, fieldCol).Value))) Then
            endPos = i
            For j = startPos To endPos
                 DS.Cells(j, countifCol) = endPos - startPos + 1
            Next j
        End If
    Next i
    MsgBox ("Done")
End Sub
你好，有几个问题： 1）这个函数实际上在做什么？ 2）如果不对列进行排序，它能正常工作吗？（我有20列要计算） 3）你能否摆脱消息框导入，只需在顶部编码为icol = 1？ - Scottyp
0
尝试使用sumproduct(countif(x:x,y:y))，这样会稍微快一些，但具体快多少我不确定。
如果你找到了更好的选项，请告诉我们。
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接