如何用Python实现不同格式字符串相似度匹配并展示Excel商品名最优匹配？

Got it, let's tackle this problem step by step—since you're dealing with messy, similar product names across two Excel files and need human validation at the end, here's a practical, actionable approach:

First, eliminate trivial formatting differences that throw off matching. This ensures we're comparing the actual content, not just punctuation or case:

• Standardize case: Use Excel's LOWER() or UPPER() function to convert all names to the same case (e.g., =LOWER(A2)). This fixes issues like "KIDS-Piano" vs "kids piano".
• Clean special characters: Use nested SUBSTITUTE() functions to strip or unify separators. For example, =SUBSTITUTE(SUBSTITUTE(A2,"-","")," ","") will turn "Butter Gel 100mg" and "Butter-Gel-100MG" into identical strings (buttergel100mg). Alternatively, you could replace all separators with spaces if that makes more sense for your data.
• Normalize metrics (like doses): If names include quantities (e.g., 100mg/100MG), use UPPER() or LOWER() on those segments, or extract them into a separate column to ensure consistency.

You have two solid options here—one no-code, one with custom control:

Option 1: Microsoft's Fuzzy Lookup Add-In (Quick & Easy) #

This is the simplest route for Excel 365/2019/2021 users:

1. Install the add-in: Go to File > Options > Add-Ins, select COM Add-Ins from the bottom dropdown, click Go, check Fuzzy Lookup Add-In, and hit OK. A new Fuzzy Lookup tab will appear in Excel.
2. Prepare data: Move both product name columns into the same workbook (e.g., Sheet1 for File A, Sheet2 for File B).
3. Run the lookup:
   • Open the Fuzzy Lookup pane, select Sheet1 as your Left Table and Sheet2 as your Right Table.
   • Choose the product name column from each table as the matching key.
   • Adjust the Similarity Threshold (start with 0.7, or 70% similarity—tweak this later based on your results).
   • Click Go, and the add-in will generate a new sheet with:
     • Original name from File A
     • Best matching name from File B
     • A similarity score (0 to 1, higher = more similar)
4. Sort results: Order by similarity score descending so your team prioritizes the most likely matches first.

Option 2: Custom VBA Function (Full Control) #

If you prefer not to use add-ins, write a simple VBA function to calculate string similarity (using Levenshtein Distance, which counts edits needed to turn one string into another):

1. Open the VBA editor with Alt + F11, insert a new module, and paste this code:

' Calculates edits needed to convert s1 to s2
Function LevenshteinDistance(s1 As String, s2 As String) As Integer
    Dim len1 As Integer, len2 As Integer, i As Integer, j As Integer, cost As Integer
    Dim d() As Integer
    
    len1 = Len(s1)
    len2 = Len(s2)
    ReDim d(len1, len2)
    
    For i = 0 To len1: d(i, 0) = i: Next i
    For j = 0 To len2: d(0, j) = j: Next j
    
    For i = 1 To len1
        For j = 1 To len2
            cost = IIf(Mid(s1, i, 1) = Mid(s2, j, 1), 0, 1)
            d(i, j) = Application.WorksheetFunction.Min(d(i - 1, j) + 1, d(i, j - 1) + 1, d(i - 1, j - 1) + cost)
        Next j
    Next i
    
    LevenshteinDistance = d(len1, len2)
End Function

' Returns a 0-1 similarity score after cleaning strings
Function SimilarityScore(s1 As String, s2 As String) As Double
    Dim cleaned1 As String, cleaned2 As String, maxLen As Integer
    ' Normalize case and remove separators
    cleaned1 = LCase(Replace(Replace(s1, "-", ""), " ", ""))
    cleaned2 = LCase(Replace(Replace(s2, "-", ""), " ", ""))
    
    maxLen = Application.WorksheetFunction.Max(Len(cleaned1), Len(cleaned2))
    If maxLen = 0 Then
        SimilarityScore = 1
    Else
        SimilarityScore = 1 - (LevenshteinDistance(cleaned1, cleaned2) / maxLen)
    End If
End Function

2. Back in Excel (Sheet1 for File A):
   • In cell B2, enter this formula to get the best match from Sheet2:
     =INDEX(Sheet2!$A:$A, MATCH(MAX(SimilarityScore(A2, Sheet2!$A:$A)), SimilarityScore(A2, Sheet2!$A:$A), 0))
     (For pre-365 Excel, press Ctrl + Shift + Enter to run as an array formula.)
   • In cell C2, enter =MAX(SimilarityScore(A2, Sheet2!$A:$A)) to get the similarity score.
3. Drag formulas down to apply to all names, then sort by column C descending.

Make review efficient for your team:

• Conditional Formatting: Color-code rows by score (e.g., green for >0.8, yellow for 0.6-0.8, red for <0.6) to highlight high-priority matches.
• Validation Column: Add a column like "Verification Result" with dropdown options ("Match", "No Match", "Needs Review") to track decisions.
• Filter Options: Use Excel's filter to show only high-score matches first, or filter by verification status to follow up on unresolved items.

Remember, fuzzy matching can never be 100% perfect, but this setup cuts down manual work drastically by presenting the most likely pairs upfront—letting your team focus on validation instead of searching for potential matches.

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