匹配字符串数组中的值 [英] Matching values in string array

问题描述

问题:寻找一种更有效的方法来查找一维数组中是否存在精确匹配的值——本质上是一个布尔值true/false.

Problem: Looking for a more efficient way of finding whether there is an exact matching value in a 1d array -- essentially a boolean true/false.

我是否忽略了一些明显的东西?或者我只是使用了错误的数据结构，在我可能应该使用集合对象或字典时使用了数组?在后者中，我可以分别检查 .Contains 或 .Exists 方法

Am I overlooking something obvious? Or am I simply using the wrong data structure, by using an array when I probably should be using a collection object or a dictionary? In the latter I could check the .Contains or .Exists method, respectively

在 Excel 中，我可以检查向量数组中的值，例如:

In Excel I can check for a value in a vector array like:

If Not IsError(Application.Match(strSearch, varToSearch, False)) Then
' Do stuff
End If

这将返回一个完全匹配的索引，显然受到 Match 函数的限制，该函数只能在此上下文中找到 first 匹配值.这是常用的方法，也是我用了很久的方法.

This returns an exact match index, obviously subject to limitations of Match function which only finds the first matching value in this context. This is a commonly used method, and one that I have been using for a long time, too.

这对于 Excel 来说已经足够了——但其他应用程序呢?

在其他应用程序中，我可以做基本相同的事情，但需要启用对 Excel 对象库的引用，然后:

In other applications, I can do basically the same thing but requires enabling reference to the Excel object library, and then:

   If Not IsError(Excel.Application.match(...))

不过，这似乎很愚蠢，而且由于权限/信任中心等原因，很难管理分布式文件.

That seems silly, though, and is difficult to manage on distributed files because of permissions/trust center/etc.

我尝试使用 Filter()功能:

 If Not Ubound(Filter(varToSearch, strSearch)) = -1 Then
    'do stuff
 End If

但这种方法的问题在于 Filter 返回部分匹配的数组，而不是精确匹配的数组.(我不知道为什么返回子字符串/部分匹配会很有用.)

But the problem with this approach is that Filter returns an array of partial matches, rather than an array of exact matches. (I have no idea why it would be useful to return substring/partial matches.)

另一种替代方法是逐字迭代数组中的每个值(我认为这也是非常常用的)——这似乎比调用 Excel 的 Match 函数更为麻烦.>

The other alternative is to literally iterate over each value in the array (this also is very commonly used I think) -- which seems even more needlessly cumbersome than calling on Excel's Match function.

For each v in vArray
   If v = strSearch Then
    ' do stuff
   End If
Next

推荐答案

如果我们要谈论性能，那么就没有什么可以替代运行一些测试了.根据我的经验 Application.Match() 比调用使用循环的函数慢十倍.

If we're going to talk about performance then there's no substutute for running some tests. In my experience Application.Match() is up to ten times slower than calling a function which uses a loop.

Sub Tester()

    Dim i As Long, b, t
    Dim arr(1 To 100) As String

    For i = 1 To 100
        arr(i) = "Value_" & i
    Next i

    t = Timer
    For i = 1 To 100000
        b = Contains(arr, "Value_50")
    Next i
    Debug.Print "Contains", Timer - t

    t = Timer
    For i = 1 To 100000
        b = Application.Match(arr, "Value_50", False)
    Next i
    Debug.Print "Match", Timer - t

End Sub


Function Contains(arr, v) As Boolean
Dim rv As Boolean, lb As Long, ub As Long, i As Long
    lb = LBound(arr)
    ub = UBound(arr)
    For i = lb To ub
        If arr(i) = v Then
            rv = True
            Exit For
        End If
    Next i
    Contains = rv
End Function

输出:

Contains       0.8710938 
Match          4.210938 

这篇关于匹配字符串数组中的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！