Question

我要说的是,我有9个记录。三个行各有相同的价值。例如:

Mike  
Mike  
Mike  
John  
John  
John  
Ryan  
Ryan  
Ryan

Is there a way I can search for similarities of these records? For example spelling mistakes, additional characters, missing characters, etc. So, for example, the correct version is Mike, but there might be a record down in the list with value Mke which is incorrect (spelling mistake). How can I find this and replace it with the correct one?

上述例子显然简化了。我实际上有大约1毫升。现在,实现我刚才所描述的内容分类的权利。

Answer 1

I was facing the exact same problem! With a few searches I could get and modify the following VBA code that will enable a function named =Similarity(). This function will output a number that goes from 0 to 1, according to the similarity of the two input cells.

How I used it:

我用字母字母顺序排列,应用公式。然后,我建立了<条码>有条件格式规则,以突出具有高度相似性(即至少65%)的标准。然后,我搜索了每个突出的事件,并人工确定了我的记录。

Usage:
```
=Similarity(cell1, cell2)
```

Obs: 类似指标从0到1(0%至100%)。

Example:

www.un.org/spanish/ecosoc 为了加以利用,你必须:。
1. Open VBE (Alt+F11)
2. Insert Module
3. Paste the following code into the Module Window

http://www.ohchr.org。

Public Function Similarity(ByVal String1 As String, _
    ByVal String2 As String, _
    Optional ByRef RetMatch As String, _
    Optional min_match = 1) As Single

Dim b1() As Byte, b2() As Byte
Dim lngLen1 As Long, lngLen2 As Long
Dim lngResult As Long

If UCase(String1) = UCase(String2) Then
    Similarity = 1
Else:
    lngLen1 = Len(String1)
    lngLen2 = Len(String2)
    If (lngLen1 = 0) Or (lngLen2 = 0) Then
        Similarity = 0
    Else:
        b1() = StrConv(UCase(String1), vbFromUnicode)
        b2() = StrConv(UCase(String2), vbFromUnicode)
        lngResult = Similarity_sub(0, lngLen1 - 1, _
        0, lngLen2 - 1, _
        b1, b2, _
        String1, _
        RetMatch, _
        min_match)
        Erase b1
        Erase b2
        If lngLen1 >= lngLen2 Then
            Similarity = lngResult / lngLen1
        Else
            Similarity = lngResult / lngLen2
        End If
    End If
End If

End Function

Private Function Similarity_sub(ByVal start1 As Long, ByVal end1 As Long, _
                                ByVal start2 As Long, ByVal end2 As Long, _
                                ByRef b1() As Byte, ByRef b2() As Byte, _
                                ByVal FirstString As String, _
                                ByRef RetMatch As String, _
                                ByVal min_match As Long, _
                                Optional recur_level As Integer = 0) As Long
 * CALLED BY: Similarity *(RECURSIVE)

Dim lngCurr1 As Long, lngCurr2 As Long
Dim lngMatchAt1 As Long, lngMatchAt2 As Long
Dim I As Long
Dim lngLongestMatch As Long, lngLocalLongestMatch As Long
Dim strRetMatch1 As String, strRetMatch2 As String

If (start1 > end1) Or (start1 < 0) Or (end1 - start1 + 1 < min_match) _
Or (start2 > end2) Or (start2 < 0) Or (end2 - start2 + 1 < min_match) Then
    Exit Function  (exit if start/end is out of string, or length is too short)
End If

For lngCurr1 = start1 To end1
    For lngCurr2 = start2 To end2
        I = 0
        Do Until b1(lngCurr1 + I) <> b2(lngCurr2 + I)
            I = I + 1
            If I > lngLongestMatch Then
                lngMatchAt1 = lngCurr1
                lngMatchAt2 = lngCurr2
                lngLongestMatch = I
            End If
            If (lngCurr1 + I) > end1 Or (lngCurr2 + I) > end2 Then Exit Do
        Loop
    Next lngCurr2
Next lngCurr1

If lngLongestMatch < min_match Then Exit Function

lngLocalLongestMatch = lngLongestMatch
RetMatch = ""

lngLongestMatch = lngLongestMatch _
+ Similarity_sub(start1, lngMatchAt1 - 1, _
start2, lngMatchAt2 - 1, _
b1, b2, _
FirstString, _
strRetMatch1, _
min_match, _
recur_level + 1)
If strRetMatch1 <> "" Then
    RetMatch = RetMatch & strRetMatch1 & "*"
Else
    RetMatch = RetMatch & IIf(recur_level = 0 _
    And lngLocalLongestMatch > 0 _
    And (lngMatchAt1 > 1 Or lngMatchAt2 > 1) _
    , "*", "")
End If


RetMatch = RetMatch & Mid$(FirstString, lngMatchAt1 + 1, lngLocalLongestMatch)


lngLongestMatch = lngLongestMatch _
+ Similarity_sub(lngMatchAt1 + lngLocalLongestMatch, end1, _
lngMatchAt2 + lngLocalLongestMatch, end2, _
b1, b2, _
FirstString, _
strRetMatch2, _
min_match, _
recur_level + 1)

If strRetMatch2 <> "" Then
    RetMatch = RetMatch & "*" & strRetMatch2
Else
    RetMatch = RetMatch & IIf(recur_level = 0 _
    And lngLocalLongestMatch > 0 _
    And ((lngMatchAt1 + lngLocalLongestMatch < end1) _
    Or (lngMatchAt2 + lngLocalLongestMatch < end2)) _
    , "*", "")
End If

Similarity_sub = lngLongestMatch

End Function

Output according to your data set:

Answer 2

案文的相似性可能会变得相当复杂,取决于你们想要走到什么路。关于所有不同算法的完整调查,见本文件https://pdfs.semanticscholar.org/5b5c/a878c534aee3882a038ef9e82f46e102131b.pdf> rel=“nofollow noreferer” 。 (Gomaa & Fahmy, IJCA2013)。它可能伤害你的头部,但却是好的。

具体针对VBA, 您可参阅https://stackoverflow.com/q/3576211/4717755。

Answer 3

我不知道这样做是完全自动化的。有一个“Fuzzy马卡”附加——可能有助于:。 https://www.microsoft.com/en-us/download/details.aspx?id=15011 。

我使用了这个词。它在大部分时间工作,但努力工作。

Answer 4

冰雪如何发挥作用。感谢!

与清单相比,案文如何。我希望有人能够帮助我。

友情链接