如何模糊匹配仅相邻的单元格? [英] How do I fuzzy match just adjacent cells?

问题描述

我在对应的两列中有10,000个名称行，每列10,000个. A列中的每个单元格都对应于B列中的相邻单元格.我想进行模糊匹配，并获得所有这些单元格与相邻单元格的兼容性得分.我不希望它搜索整个列还是整个列，仅搜索相邻的单元格，而我似乎无法使用Fuzzy Match Excel插件来实现这一点?

I have a row of 10,000 names in two corresponding columns, 10,000 in each. Each cell in Column A corresponds to the adjacent cell in Column B. I want to do a fuzzy match and get a compatibility score on all of them just with the adjacent cell. I do not want it to search entire column versus entire column, just adjacent cells, which I don't seem to be able to do with the Fuzzy Match Excel add in, ideas?

示例:

Column A:       Column B:        Value:
Apple           Aplle            80%
Banana          Banana           100%
Orange          Ornge            85%   

推荐答案

好吧，我不了解Fuzzy Match Addin，但是您可以使用UDF完成与您的要求类似的操作.

Well, I don't know about Fuzzy Match Addin but you can accomplish similar to your requirement using UDF.

基于您的样本数据之类的东西

Something like this based on your sample data

Function FuzzyComparision(String1 As String, String2 As String) As Double
Dim intStringLength As Integer
Dim dblScore As Double
Dim dblUnitScore As Double
Dim intCounter As Integer

intStringLength = WorksheetFunction.Max(Len(String1), Len(String2))
dblUnitScore = 1 / intStringLength
dblScore = 0#

For intCounter = 1 To intStringLength
    If Mid(UCase(String1), intCounter, 1) = Mid(UCase(String2), intCounter, 1) Then
        dblScore = dblScore + dblUnitScore
    Else
        If Len(String1) <> Len(String2) And intCounter < intStringLength Then
            If Mid(UCase(String1), intCounter + 1, 1) = Mid(UCase(String2), intCounter, 1) Then
                dblScore = dblScore + dblUnitScore
            End If
        End If
    End If
Next

FuzzyComparision = dblScore
End Function

当然，可以根据需要进一步简化比较. 让我知道是否有帮助

Of course the comparison can be further improvised as required. Let me know if that helps

正如培根成绩所指出的(感谢您的观察)，这对于缺少字母的情况不起作用.我对函数进行了一些微调，以进行不区分大小写的比较并像示例中那样补偿丢失的字母.

as Grade Bacon pointed (thanks for that observation), this does not work as expected for missing letters. I have tweaked the function a bit to do a case insensitive comparison and compensate for missing letters as in example.

即使有效，实用程序也取决于可接受的分数偏差+/-. OP可能会有所启发.

Even though it works, utility depends on acceptable score deviation +/-. OP may shed some light.

这篇关于如何模糊匹配仅相邻的单元格?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！