function - 自定义查找功能

Question

我正在尝试创建一个函数，在搜索整个事件工作表后，该函数将返回包含特定字符串的单元格总数。很像“查找和替换”中“找到的 x 个单元格”的工作原理。

到目前为止我有这个:

Function FINDIST(stringToFind)
Dim counter As Integer: counter = 0
For Each Cell In ActiveSheet.UsedRange.Cells
If InStr (Cell, stringToFind) > 0
Then counter = counter + 1
End If
Next
End Function

Answer 1

另一种方法:

Function FINDIST(stringToFind) As Long
    FINDIST = Evaluate("SUM(IFERROR(SEARCH(" & Chr(34) _
        & "*" & stringToFind & "*" & Chr(34) & "," _
            & ActiveSheet.UsedRange.Address & ",1),0))")
End Function

这会在使用范围内的每个单元格中搜索stringToFind，如果在单元格中找到该字符串，则返回一个包含 1 的数组；如果未找到，则返回错误。使用 IFERROR 部分将错误强制转换为零，并且 SUM 对生成的二进制数组求和。

这只会计算每个单元格中 stringToFind 的出现一次，即使它出现多次，但查看您的代码，我认为这就是您正在寻找的内容。

希望对您有帮助!

更新

出于好奇，我做了一些测试来看看这两种方法如何比较(直接从范围读取与使用评估)。这是我使用的代码:

Option Explicit
Private Declare Function GetTickCount Lib "kernel32.dll" () As Long

Sub test()
Dim ticks As Long
Range("A1:AA100000").Value = "adlrkjgalbabyajglakrjg"

ticks = GetTickCount
FINDIST1 ("baby")
Debug.Print "Read from range: ", GetTickCount - ticks

ticks = GetTickCount
FINDIST ("baby")
Debug.Print "Evaluate: ", GetTickCount - ticks

End Sub

Function FINDIST(stringToFind) As Long
    FINDIST = Evaluate("SUM(IFERROR(SEARCH(" & Chr(34) _
    & "*" & stringToFind & "*" & Chr(34) & "," _
      & ActiveSheet.UsedRange.Address & ",1),0))")
End Function


Function FINDIST1(stringToFind) As Long
Dim counter As Long: counter = 0
Dim c As Range
Dim firstAddress As String

With ActiveSheet.UsedRange
    Set c = .Find(stringToFind, LookIn:=xlValues, LookAt:=xlPart)
    If Not c Is Nothing Then
        firstAddress = c.Address
        Do
            counter = counter + 1
            Set c = .FindNext(c)
        Loop While Not c Is Nothing And c.Address <> firstAddress
    End If
End With

FINDIST1 = counter

End Function

更新2

克里斯·尼尔森在下面的评论中提出了两个非常好的观点:

• ActiveSheet.Evaluate 比 Application.Evaluate 更快。评论中 Charles Williams 的文本链接解释了这种行为。
• 好的旧Variant数组将比任何其他方法执行得更好。

为了完整起见，我发布了我测试的variant数组方法的版本:

Function FINDIST_looping(stringToFind) As Long
    Dim vContents, lRow As Long, lCol As Long, lCounter As Long

    vContents = ActiveSheet.UsedRange.Value2
    For lRow = LBound(vContents, 1) To UBound(vContents, 1)
        For lCol = LBound(vContents, 2) To UBound(vContents, 2)
            lCounter = IIf(InStr(vContents(lRow, lCol), stringToFind), _ 
               lCounter + 1, lCounter)
        Next lCol
    Next lRow

FINDIST_looping = lCounter

End Function

Doug Glancy 提出了另一个非常好的观点，即可以使用 COUNTIF 代替 SEARCH。这导致了一个非数组公式解决方案，并且在性能方面应该主导我的原始公式。

这是道格的公式:

FINDIST_COUNTIF = ActiveSheet.Evaluate("COUNTIF(" _
        & ActiveSheet.Cells.Address & "," & Chr(34) & "*"  _ 
          & stringToFind & "*" & Chr(34) & ")")

事实上，Doug 的观点意味着 Evaluate() 不是必需的。我们可以从 WorksheetFunction 对象调用 Countif。因此，如果目标是从电子表格调用此函数，则无需使用 Evaluate() 或将其包装在 UDF 中 - 这是典型的带有通配符的 COUNTIF 应用程序。

结果:

  Read from range:           247,495 ms (~ 4 mins 7 secs)
  Application.Evaluate:        3,261 ms (~ 3.2 secs)
  Variant Array:               1,706 ms (~ 1.7 secs)
  ActiveSheet.Evaluate:        1,257 ms (~ 1.3 secs)
  ActiveSheet.Evaluate (DG):     602 ms (~ 0.6 secs)
  WorksheetFunction.CountIf (DG):550 ms (~ 0.55 secs)

与使用 Range.Find() 相比，Application.Evaluate 的速度似乎快了约 75 倍(？!)此外，原始代码(使用 Integer 更改为 Long)在约 8 秒内运行。

此外，在这种特殊情况下，Activesheet.Evaluate 似乎实际上比 Variant 数组更快。作为 WorksheetFunction 方法调用 CountIf 与 Evaluate 方法之间的区别似乎很小。

警告:在 UsedRange 中找到 stringToFind 的频率可能会影响几种方法的相对性能。我使用上述范围 (A1:AA100000) 运行了 Activesheet.Evaluate 和 Variant Array 方法，但只有前十个单元格具有匹配的值字符串。

结果(6 次运行的平均值，方差几乎很小):

  Activesheet.Evaluate:        920 ms (~  1. sec)
  Variant Array:               1654 ms (~ 1.7 secs)

这很有趣 - 在这种情况下，ActiveSheet.Evaluate 的性能似乎比变体数组稍好(除非我在循环代码中做了一些可怕的事情，在这种情况下，请告诉我)。此外，Variant 方法的性能实际上......相对于字符串的频率是不变的。

在Win7下的EXCEL 2010上运行。