使用 Excel VBA 计算精确匹配

如果您不希望“Go”与（例如）匹配“Google”，则需要考虑单词边界。您最好使用正则表达式方法来查找匹配项。

注意：为了避免将“Go”与“go”匹配，您需要匹配区分大小写，但为了避免遗漏其他术语，您可能需要传递所有可能的大小写变体，例如（例如）“mySQL|MySQL” .

Sub matchTester()
    
    Dim s As String,skill
    
    s = "I go to school now and have used R,MySQL,noSQL and " & _
        "SQL and Go in my RaTings job at Google. I like R"
    
    For Each skill In Array("SQL","noSQL|NoSQL","mySQL|MySQL","R","Go","VBA","C#")
        Debug.Print skill,CountMatches(s,skill)
    Next skill

End Sub

Function CountMatches(sIn As String,countThis) As Long
    Dim regEx As Object,matches As Object
    With CreateObject("vbscript.regexp")
        .Global = True
        .IgnoreCase = false
        .Pattern = "\b(" & countThis & ")\b" 'add word boundaries
        Set matches = .Execute(sIn)
    End With
    CountMatches = matches.Count
End Function

输出：

SQL            1 
noSQL|NoSQL    1 
mySQL|MySQL    1 
R              2 
Go             1 
VBA            0 
C#             0