用vba写个自定义表格函数，两列数据能够进行模糊匹配

在VBA中创建自定义表格函数（也称为User Defined Function, UDF）来实现两列数据之间的模糊匹配，你需要定义一个新的Excel函数，并利用`Find`、`Replace`等方法来查找相似的值。下面是一个简单的示例，我们假设你想创建一个名为`FuzzyMatch`的函数，输入两个范围，返回匹配的结果：

Function FuzzyMatch(SearchRange As Range, DataRange As Range, Threshold As Double) As Variant
    ' 检查输入是否合法
    On Error Resume Next
    If Not IsNumeric(Threshold) Or Threshold < 0 Or Threshold > 1 Then
        FuzzyMatch = CVErr(xlErrNum) ' 返回错误值
        Exit Function
    End If
    On Error GoTo 0
    
    ' 定义结果数组
    Dim ResultArray() As Variant
    ReDim ResultArray(1 To SearchRange.Rows.Count, 1 To 2)
    
    ' 遍历搜索范围
    For i = 1 To SearchRange.Rows.Count
        Dim MatchRow As Long
        Dim SearchValue As String
        SearchValue = Trim(SearchRange.Cells(i, 1).Value)
        
        ' 在数据范围内查找相似值
        For j = 1 To DataRange.Columns.Count Step 2 ' 假设数据按两列匹配
            Dim DataCell As Range
            DataCell = DataRange.Cells(1, j + 1)
            
            ' 使用Levenshtein距离计算相似度
            Dim Similarity As Double
            Similarity = LevenshteinDistance(SearchValue, DataCell.Value)
            
            ' 如果相似度超过阈值，则保存匹配信息
            If Similarity <= (DataRange.Columns.Count / 2 * Threshold) Then ' 这里假设相似度阈值基于数据列数
                MatchRow = DataCell.Row
                ResultArray(i, 1) = i ' 搜索范围的行号
                ResultArray(i, 2) = MatchRow ' 匹配的数据行号
                Exit For ' 找到匹配就停止内层循环
            End If
        Next j
        
        ' 如果未找到匹配，结果数组相应位置填入#N/A
        If MatchRow = 0 Then
            ResultArray(i, 1) = CVErr(xlErrNA)
            ResultArray(i, 2) = CVErr(xlErrNA)
        End If
    Next i
    
    ' 将结果转换为二维数组并返回
    FuzzyMatch = ResultArray
End Function

' 常见的Levenshtein距离计算函数（这里仅作为示例，实际应用中可能需要引入专门库）
Private Function LevenshteinDistance(s1 As String, s2 As String) As Double
    Dim n As Integer, m As Integer, temp As Integer
    n = Len(s1)
    m = Len(s2)
    
    ReDim dp(n + 1, m + 1)
    For i = 0 To n
        dp(i, 0) = i
    Next i
    For j = 0 To m
        dp(0, j) = j
    Next j
    
    For i = 1 To n
        For j = 1 To m
            If Mid(s1, i, 1) = Mid(s2, j, 1) Then
                dp(i, j) = dp(i - 1, j - 1)
            Else
                dp(i, j) = Min(dp(i - 1, j), dp(i, j - 1), dp(i - 1, j - 1)) + 1
            End If
        Next j
    Next i
    
    LevenshteinDistance = dp(n, m)
End Function

要使用这个函数，你在公式栏中输入`=FuzzyMatch(A1:B10, C1:D20, 0.5)`（这里的数字0.5代表50%的相似度），其中`A1:B10`和`C1:D20`是你要比较的两个范围。