2012-06-15 18:29:24 +0000 2012-06-15 18:29:24 +0000
14
14

Comparação de cadeias de texto semelhantes em Excel

Estou actualmente a tentar reconciliar os campos “Nome” de duas fontes de dados separadas. Tenho um número de nomes que não são uma correspondência exacta mas que estão suficientemente próximos para serem considerados compatíveis (exemplos abaixo). Têm alguma ideia de como posso melhorar o número de correspondências automáticas? Já estou a eliminar as iniciais médias dos critérios de concordância.

=IFERROR(IF(LEFT(SYSTEM A,IF(ISERROR(SEARCH(" ",SYSTEM A)),LEN(SYSTEM A),SEARCH(" ",SYSTEM A)-1))=LEFT(SYSTEM B,IF(ISERROR(SEARCH(" ",SYSTEM B)),LEN(SYSTEM B),SEARCH(" ",SYSTEM B)-1)),"",IF(LEFT(SYSTEM A,FIND(",",SYSTEM A))=LEFT(SYSTEM B,FIND(",",SYSTEM B)),"Last Name Match","RESEARCH")),"RESEARCH")

Fórmula de concordância actual:

0x1&

Respostas (7)

12
12
12
2012-06-15 18:51:25 +0000

Pode considerar a utilização do Microsoft Fuzzy Lookup Addin .

A partir do site MS:

Visão geral & > O Fuzzy Lookup Add-In para Excel foi desenvolvido pela Microsoft Research e efectua a correspondência fuzzy de dados textuais no Microsoft Excel. Pode ser utilizado para identificar linhas duplas difusas dentro de uma única tabela ou para juntar linhas semelhantes entre duas tabelas diferentes. A correspondência é robusta a uma grande variedade de erros, incluindo erros ortográficos, abreviaturas, sinónimos e dados adicionados/falecidos. Por exemplo, pode detectar que as linhas “Mr. Andrew Hill”, “Hill, Andrew R.” e “Andy Hill” se referem todas à mesma entidade subjacente, devolvendo uma pontuação de similaridade juntamente com cada partida. Embora a configuração padrão funcione bem para uma grande variedade de dados textuais, tais como nomes de produtos ou endereços de clientes, a correspondência também pode ser personalizada para domínios ou idiomas específicos.

6
6
6
2012-06-15 19:47:53 +0000

Eu estudaria a utilização de isto lista (apenas na secção inglesa) para ajudar a eliminar os encurtamentos comuns.

Adição, talvez queira considerar a utilização de uma função que lhe dirá, em termos exactos, como “fechar” duas cordas. O seguinte código veio de aqui e graças a smirkingman .

Option Explicit
Public Function Levenshtein(s1 As String, s2 As String)

Dim i As Integer
Dim j As Integer
Dim l1 As Integer
Dim l2 As Integer
Dim d() As Integer
Dim min1 As Integer
Dim min2 As Integer

l1 = Len(s1)
l2 = Len(s2)
ReDim d(l1, l2)
For i = 0 To l1
    d(i, 0) = i
Next
For j = 0 To l2
    d(0, j) = j
Next
For i = 1 To l1
    For j = 1 To l2
        If Mid(s1, i, 1) = Mid(s2, j, 1) Then
            d(i, j) = d(i - 1, j - 1)
        Else
            min1 = d(i - 1, j) + 1
            min2 = d(i, j - 1) + 1
            If min2 < min1 Then
                min1 = min2
            End If
            min2 = d(i - 1, j - 1) + 1
            If min2 < min1 Then
                min1 = min2
            End If
            d(i, j) = min1
        End If
    Next
Next
Levenshtein = d(l1, l2)
End Function

O que isto fará é dizer-lhe quantas inserções e supressões uma deve fazer a uma cadeia de caracteres para chegar à outra. Eu tentaria manter este número baixo (e os sobrenomes devem ser exactos).

5
5
5
2015-10-09 14:26:12 +0000

Tenho uma fórmula (longa) que pode utilizar. Não é tão bem aperfeiçoada como as anteriores - e só funciona pelo apelido, em vez de um nome completo - mas pode achá-la útil.

Portanto, se tiver uma linha de cabeçalho e quiser comparar A2 com B2, coloque isto em qualquer outra célula dessa linha (por exemplo, C2) e copie até ao fim.

=IF(A2=B2, “EXACTO”,IF(SUBSTITUTO(A2,“-”,“ ”)=SUBSTITUTO(B2,“-”,“ ”), “Hífen”,IF(LEN(A2)>LEN(B2),IF(LEN(A2)>LEN(SUBSTITUTO(A2,B2,“”)), “Cordão Inteiro”,IF(MID(A2,1, 1)=MID(B2,1,1),1,0)+IF(MID(A2,2,1)=MID(B2,2,1),1,0)+IF(MID(A2,3,1)=MID(B2,3,1),1,0)+IF(MID(A2,LEN(A2),1)=MID(B2,LEN(B2),1),1,0)+IF(MID(A2,LEN(A2)-1,1)=MID(B2,LEN(B2)-1,1),1, 0)+IF(MID(A2,LEN(A2)-2,1)=MID(B2,LEN(B2)-2,1),1,0)&“°”),IF(LEN(B2)>LEN(SUBSTITUTO(B2,A2,“)), "Whole String”,IF(MID(A2,1,1)=MID(B2,1,1),1,0)+IF(MID(A2,2,1)=MID(B2,2,1),1, 0)+IF(MID(A2,3,1)=MID(B2,3,1),1,0)+IF(MID(A2,LEN(A2),1)=MID(B2,LEN(B2),1),1,0)+IF(MID(A2,LEN(A2)-1,1)=MID(B2,LEN(B2)-1,1),1,0)+IF(MID(A2,LEN(A2)-2,1)=MID(B2,LEN(B2)-2,1),1,0)&“°”))))

Isto irá regressar:

  • EXACT* - se for um par exacto
  • Hyphen - se for um par de nomes com duas barras, mas o outro tiver um hífen e o outro um espaço
  • Whole string* - se todo um apelido fizer parte do outro (por exemplo, se um Smith se tiver tornado um Francês-Smith)

Depois disso, dar-lhe-á um grau de 0° a 6°, dependendo do número de pontos de comparação entre os dois. (i.e., 6° compara melhor).

Como eu digo um pouco áspero e pronto, mas esperemos que nos coloque aproximadamente no lugar certo da bola.

2
2
2
2016-06-23 06:12:19 +0000

Estava à procura de algo semelhante. Encontrei o código abaixo. Espero que isto ajude o próximo utilizador que chegar a esta pergunta

Retorna 91% para Abracadabra / Abrakadabra, 75% para Hollywood Street/Hollyhood Str, 62% para Florence/France e 0 para Disneyland

Eu diria que está suficientemente perto do que queria :)

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
1
1
1
2015-10-30 10:56:53 +0000

Este código escaneia a coluna a e a coluna b, se encontrar alguma semelhança em ambas as colunas, mostra-se a amarelo. Pode usar um filtro de cor para obter o valor final. Não adicionei essa parte ao código.

Sub item_difference()

Range("A1").Select

last_row_all = Range("A65536").End(xlUp).Row
last_row_new = Range("B65536").End(xlUp).Row

Range("A1:B" & last_row_new).Select
With Selection.Interior
    .Pattern = xlSolid
    .PatternColorIndex = xlAutomatic
    .Color = 65535
    .TintAndShade = 0
    .PatternTintAndShade = 0
End With

For i = 1 To last_row_new
For j = 1 To last_row_all

If Range("A" & i).Value = Range("A" & j).Value Then

Range("A" & i & ":B" & i).Select
With Selection.Interior
    .Pattern = xlSolid
    .PatternColorIndex = xlAutomatic
    .ThemeColor = xlThemeColorDark1
    .TintAndShade = 0
  .PatternTintAndShade = 0
End With

End If
Next j
Next i
End Sub
1
1
1
2016-09-14 12:14:05 +0000

Embora a minha solução não permita identificar cordas muito diferentes, é útil para a correspondência parcial (correspondência de substrato), por exemplo “isto é uma corda” e “uma corda” resultará como “correspondência”:

** basta adicionar “*” antes e depois da cadeia de caracteres para procurar na tabela.**

Fórmula habitual:

  • vlookup(A1,B1:B10,1,0)
  • cerca.vert(A1;B1:B10;1;0)

  • vlookup(“*” & A1 & “*”,B1:B10;1,0)

  • cerca. vert(“*” & A1 & “*”;B1:B10;1;0)

“&” é a “versão curta” para concatenate()

1
1
1
2015-02-05 02:42:16 +0000

Pode usar a função de semelhança (pwrSIMILARITY) para comparar as cordas e obter uma correspondência percentual das duas. Pode fazer com que seja ou não sensível a maiúsculas e minúsculas. Terá de decidir que percentagem de uma correspondência é “suficientemente próxima” para as suas necessidades.

Existe uma página de referência em http://officepowerups.com/help-support/excel-function-reference/excel-text-analyzer/pwrsimilarity/ .

Mas funciona bastante bem para comparar texto na coluna A contra coluna B.