Excel VBA单词匹配填充缩写功能准确率不足问题排查

需求：在Sheet1的A列文本中查找Sheet2 A列的完整单词，匹配成功后将Sheet2对应B列的缩写填入Sheet1的B列。现有VBA代码匹配准确率不足，需优化解决。

• 子字符串误匹配：使用InStr函数会匹配部分字符，比如Sheet2中存在"Apple"，Sheet1中的"App"会被错误识别为匹配。
• 结果被覆盖：Sheet1的一行如果匹配到多个Sheet2条目，后续匹配会覆盖之前的结果，最终只保留最后一个匹配项。
• 拆分逻辑不严谨：仅用空格拆分文本，无法处理标点、多空格等情况，导致单词拆分不完整。
• 大小写敏感：默认的字符串匹配区分大小写，可能漏匹配大小写不同的相同单词。

Sub MatchFullWordsAndFillAbbr()
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim lastRow1 As Long, lastRow2 As Long
    Dim i As Long
    Dim txt1 As String, word As Variant
    Dim abbrDict As Object
    
    ' 初始化字典存储单词-缩写映射，不区分大小写
    Set abbrDict = CreateObject("Scripting.Dictionary")
    abbrDict.CompareMode = vbTextCompare
    
    Set ws1 = ThisWorkbook.Worksheets("Sheet1")
    Set ws2 = ThisWorkbook.Worksheets("Sheet2")
    
    lastRow1 = ws1.Cells(Rows.Count, 1).End(xlUp).Row
    lastRow2 = ws2.Cells(Rows.Count, 1).End(xlUp).Row
    
    ' 批量加载Sheet2的单词与缩写到字典
    For i = 1 To lastRow2
        Dim word2 As String
        word2 = Trim(ws2.Cells(i, 1).Value)
        If word2 <> "" And Not abbrDict.Exists(word2) Then
            abbrDict(word2) = ws2.Cells(i, 2).Value
        End If
    Next i
    
    ' 遍历Sheet1处理每一行
    For i = 1 To lastRow1
        txt1 = ws1.Cells(i, 1).Value
        ' 处理标点，替换为空格，确保单词边界清晰
        txt1 = Replace(Replace(Replace(txt1, ".", " "), ",", " "), ";", " ")
        ' 合并多空格为单个空格
        Do While InStr(txt1, "  ") > 0
            txt1 = Replace(txt1, "  ", " ")
        Loop
        ' 拆分处理后的文本为单词数组
        Dim words1 As Variant
        words1 = Split(Trim(txt1), " ")
        
        ' 检查每个单词是否在字典中
        For Each word In words1
            If abbrDict.Exists(word) Then
                ' 若需保留所有匹配项，改用：ws1.Cells(i, 2).Value = ws1.Cells(i, 2).Value & ", " & abbrDict(word)
                ' 若仅取第一个匹配项，直接赋值后退出循环
                ws1.Cells(i, 2).Value = abbrDict(word)
                Exit For
            End If
        Next word
    Next i
    
    ' 释放对象
    Set abbrDict = Nothing
    Set ws1 = Nothing
    Set ws2 = Nothing
End Sub

• 字典映射提升效率：将Sheet2的单词与缩写存入字典，查询速度更快，同时支持不区分大小写的匹配。
• 完整单词匹配逻辑：通过替换标点、合并空格，确保匹配的是独立完整的单词，避免部分字符误匹配。
• 灵活结果处理：可选择保留第一个匹配的缩写，或者用分隔符连接所有匹配的缩写（代码中有注释说明）。

内容的提问来源于stack exchange，提问作者Sudharsan