15 votos

Comparación de cadenas de texto similares en Excel

Actualmente estoy intentando conciliar los campos "Nombre" de dos fuentes de datos distintas. Tengo una serie de nombres que no coinciden exactamente, pero que se acercan lo suficiente como para considerarlos coincidentes (los ejemplos son los siguientes). ¿Tiene alguna idea de cómo puedo mejorar el número de coincidencias automáticas? Ya estoy eliminando las iniciales del segundo nombre de los criterios de coincidencia.

enter image description here

Fórmula actual del partido:

=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")

13voto

techturtle Puntos 4297

Puede considerar la posibilidad de utilizar el Complemento de búsqueda difusa de Microsoft .

Del sitio de MS:

Resumen

El complemento Fuzzy Lookup para Excel ha sido desarrollado por Microsoft Research y realiza comparaciones difusas de datos textuales en Microsoft Excel. Puede utilizarse para identificar filas duplicadas difusas dentro de una misma tabla o para unir difusamente filas similares entre dos tablas diferentes. El cotejo es robusto frente a una gran variedad de errores, como faltas de ortografía, abreviaturas, sinónimos y datos añadidos o ausentes. Por ejemplo, puede detectar que las filas "Sr. Andrew Hill", "Hill, Andrew R." y "Andy Hill" se refieren a la misma entidad subyacente, devolviendo una puntuación de similitud junto con cada coincidencia. Aunque la configuración por defecto funciona bien para una amplia variedad de datos textuales, como los nombres de los productos o las direcciones de los clientes, la coincidencia también puede personalizarse para dominios o idiomas específicos.

0 votos

No puedo instalar el addon en office debido a los privilegios de administrador requeridos, debido al .net framework requerido. :-(

0 votos

Esto es genial, pero no puedo conseguir que produzca más de 10 filas. He recorrido la configuración sin éxito. ¿Algún consejo?

6voto

soandos Puntos 17602

Yo miraría de usar este (sólo en la sección inglesa) para ayudar a eliminar los acortamientos comunes.

Además, podría considerar el uso de una función que le diga, en términos exactos, lo "cerca" que están dos cadenas. El siguiente código procede de aquí y gracias 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

Esto le dirá cuántas inserciones y eliminaciones hay que hacer a una cadena para llegar a la otra. Yo trataría de mantener este número bajo (y los apellidos deben ser exactos).

5voto

Coriel-11 Puntos 21

Tengo una fórmula (larga) que puedes utilizar. No está tan perfeccionada como las anteriores -y sólo funciona para el apellido, en lugar de un nombre completo- pero puede resultarte útil.

Así que si tienes una fila de cabecera y quieres comparar A2 con B2 , colócalo en cualquier otra celda de esa fila (por ejemplo C2 ) y copiar hasta el final.

\=IF(A2=B2, "EXACTO",IF(SUBSTITUTE(A2,"-"," ")=SUBSTITUTE(B2,"-"," "), "Guión",IF(LEN(A2)>LEN(B2),IF(LEN(A2)>LEN(SUBSTITUTE(A2,B2,""), "Cadena completa",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(SUBSTITUTE(B2,A2,"")), "Cadena completa",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)&"°"))))

Esto volverá:

  • EXACTO - si es una coincidencia exacta
  • Guión - si se trata de un par de nombres de doble barra pero uno tiene un guión y el otro un espacio
  • Cadena completa - si todo un apellido forma parte del otro (por ejemplo, si un Smith se ha convertido en un French-Smith)

Después le dará un grado de 0° a 6° según el número de puntos de comparación entre los dos. (es decir, 6° se compara mejor).

Como he dicho, es un poco tosco, pero espero que te sirva de orientación.

0 votos

Esto está muy infravalorado a todos los niveles. ¡Muy bien hecho! ¿Por casualidad tienes alguna actualización de esto?

1voto

Xander Puntos 1

Puede utilizar la función de similitud (pwrSIMILARITY) para comparar las cadenas y obtener un porcentaje de coincidencia de las dos. Puede hacer que distinga entre mayúsculas y minúsculas o no. Tendrá que decidir qué porcentaje de coincidencia es "suficientemente cercano" para sus necesidades.

Hay una página de referencia en http://officepowerups.com/help-support/excel-function-reference/excel-text-analyzer/pwrsimilarity/ .

Pero funciona bastante bien para comparar el texto de la columna A con el de la columna B.

1voto

Refaat M. Sayed Puntos 350

Este código escanea la columna a y la columna b, si encuentra alguna similitud en ambas columnas se muestra en amarillo. Puedes usar el filtro de color para obtener el valor final. No he añadido esa parte en el 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

EnMiMaquinaFunciona.com

EnMiMaquinaFunciona es una comunidad de administradores de sistemas en la que puedes resolver tus problemas y dudas.
Puedes consultar las preguntas de otros sysadmin, hacer tus propias preguntas o resolver las de los demás.

Powered by: