自定义查找功能 [英] A custom find function

问题描述

我正在尝试创建一个功能，在搜索整个活动工作表后，将返回包含某个字符串的单元格的总数。非常喜欢查找和替换中的x单元格的工作原理。

我到目前为止：

 函数FINDIST（stringToFind）
 Dim counter As Integer：counter = 0 
对于ActiveSheet.UsedRange.Cells中的每个单元格
如果InStr （Cell，stringToFind）> 0 
然后counter = counter + 1 
 End If 
 Next 
结束函数
  

解决方案

另一种做法：

 功能FINDIST StringToFind）As Long 
 FINDIST = Evaluate（SUM（IFERROR（SEARCH（& Chr（34）_ 
&*& stringToFind&*& Chr（34） &，_ 
& ActiveSheet.UsedRange.Address&，1），0）））
结束函数
  

在所使用范围的每个单元格中搜索 stringToFind ，如果找到该字符串，则返回一个数组1在一个单元格和错误，如果没有找到。使用 IFERROR 部分将错误转换为零，并且 SUM 将生成的二进制数组相加。

这将只计算每个单元格中 stringToFind 的出现次数，即使它发生不止一次，但查看代码我假设这是你正在寻找的。

我希望它有帮助！

更新

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

  Option Explicit 
私有声明函数GetTickCount Libkernel32.dll（）As Long 
 
 Sub test（）
 Dim ticks As Long 
 Range（A1：AA100000）。Value =adlrkjgalbabyajglakrjg
 
 ticks = GetTickCount 
 FINDIST1（baby）
 Debug.Print从范围读取，GetTickCount  -  ticks 
 
 ticks = GetTickCount 
 FINDIST（宝贝）
 Debug.PrintEvaluate：，GetTickCount  -  ticks 
 
 End Sub 
 
函数FINDIST（stringToFind）As Long 
 FINDIST = Evaluate（SUM（IFERROR （SEARCH（& Chr（34）_ 
&*& stringToFind&*& Chr（34）&，_ 
& ActiveSheet.UsedRange。地址&，1），0）））
结束函数
 
 
函数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，Look在：= xlPart）
如果不是c不是然后
 firstAddress = c.Address 
 Do 
计数器=计数器+ 1 
设置c = .FindNext（c） 
循环，而不是c不是，c.Address<> firstAddress 
如果
结束
 
 FINDIST1 =柜台
 
结束功能
  

更新2

Chris Nielsen在下面的评论中提出了两个非常好的点： / p>

• ActiveSheet.Evaluate 比 Application.Evaluate 。


• Good old Variant 数组将比任何其他方法执行得更好

为了完整，我发布了变体数组方法的版本我测试了：

 函数FINDIST_looping（stringToFind）As Long 
 Dim vContents，lRow As Long，lCol As Long，lCounter As Long 
 
 vContents = ActiveSheet.UsedRange.Value2 
对于lRow = LBound（vContents，1）到UBound（vContents，1）
对于lCol = LBound（vContents，2）对于UBound（vContents，2）
 lCounter = IIf（InStr（vContents（lRow，lCol），stringToFind），_ 
 lCounter + 1，lCounter）
 Next lCol 
 Next lRow 
 
 FINDIST_looping = lCounter 
 
结束功能
  

Doug Glancy提出了另一个很好的观点，即可以使用 COUNTIF 而不是搜索。这导致了一个非数组公式解决方案，应该主导我的原始公式，在性能方面。

这是Doug的公式：

  FINDIST_COUNTIF = ActiveSheet.Evaluate（COUNTIF（_ 
& ActiveSheet.Cells.Address&，& Chr（34）& *_ 
& stringToFind&*& Chr（34）&））
  

事实上，Doug的观点暗示，不需要 Evaluate（）。我们可以从 WorksheetFunction 对象调用 Countif 。因此，如果目标是从电子表格中调用此函数，则不需要使用 Evaluate（）或将其包装在 UDF中 - 它是一个典型的 COUNTIF 应用程序与通配符。

结果：

 从范围读取：247,495毫秒（〜4分7秒）
应用程序评估：3,261毫秒（〜3.2秒）
 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）
  

似乎 Application.Evaluate 比使用 Range.Find（）（?!）的速度快约75倍另外，原始代码（带整数更改为 Long ）运行在〜8秒内。

另外，似乎 Activesheet.Evaluate 实际上比 Variant array在这种特殊情况。调用 CountIf 作为 WorksheetFunction 方法vs 评估

CAVEAT ：找到 stringToFind 的频率 UsedRange 可能会影响几种方法的相对性能。我运行 Activesheet.Evaluate 和 Variant Array 方法与上述范围（A1：AA100000 ），但只有十个第一个单元格具有匹配的字符串。

结果（平均6次运行，差异几乎非常小）：

  Activesheet.Evaluate：920 ms（〜1. sec）
 Variant Array：1654 ms（〜1.7 secs）
  

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

EXCEL 2010 Win7 。

I am trying to create a function which, after having search the entire active worksheet, will return the total number of cells that contain a certain string. A lot like how the "x cell(s) found" within Find and Replace works.

I have this so far:

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

解决方案

Another way of doing this:

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

This searches for stringToFind in every cell in used range, and returns an array with a 1 if that string is found in a cell and error if it is not found. The error is casted to zero with the IFERROR part, and the SUM sums the resulting binary array.

This will count only once the occurrence of stringToFind within each cell, even if it occurs more than once, but looking at your code I assume that this is what you are looking for.

I hope it helps!

UPDATE

Out of curiosity, I did some testing to see how the two approaches compare (read from range directly vs using evaluate). Here is the code I used:

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

UPDATE 2

Chris Nielsen made two very good points in the comments below:

• ActiveSheet.Evaluate is faster than Application.Evaluate. The link to the text by Charles Williams in the comment explains this behavior.
• Good old Variant array will perform better than any other method.

For completeness, I post the version of the variant array method that I tested:

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 made another very good point, namely that COUNTIF can be used instead of SEARCH. This leads to a non-array formula solution and should dominate my original formula, performance-wise.

Here is Doug's formula:

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

In fact, Doug's point implies that no Evaluate() is necessary. We can call Countif from the WorksheetFunction object. Therefore, if the goal is to call this function from a spreadsheet, there is no need to use Evaluate() or to wrap it up in a UDF - it is a typical COUNTIF application with wildcards.

Results:

  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)

It appears that Application.Evaluate is about 75 times faster compared to using Range.Find()(?!) Also, the original code (with Integer changed to Long) runs in ~8 seconds.

Also, it seems that Activesheet.Evaluate is actually faster than the Variant array in this particular case. The difference between calling CountIf as a WorksheetFunction method vs Evaluateing it seems quite small.

CAVEAT: the frequency by which stringToFind is found within the UsedRange might affect the relative performance of the several methods. I ran the Activesheet.Evaluate and Variant Array methods with the above range (A1:AA100000) but with only the ten first cells having the matching string.

Results (average of 6 runs, variance pretty much minimal):

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

This is interesting - it seems that ActiveSheet.Evaluate in this case has a slightly better performance than variant arrays (unless I have done something horrible in the looping code, in which case please let me know). Also, the Variant method's performance is actually.. invariant with respect to the string's frequency.

runs were made on EXCEL 2010 under Win7.

这篇关于自定义查找功能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！