Cómo buscar (BUSCARV) registros similares (pero que no coinciden) en Excel?

Buenas a todos. Hace unos meses mirando por la web, en mis foros y demás, descubrí algo genial, una de esas cosas que ojalá hubiera sabido hace tiempo. 

En fin, como ya sabemos NUNCA es tarde.

Veamos el problema de hoy. Algo raro pero muy relevante! No es así? Un aviso rápido sobre cómo surge?

Imaginemos por un momento que la persona encargada de crear clientes nuevos en el sistema de gestión de información no es muy robusta, cada vez que llega un cliente (incluso si es el mismo cliente) se crea un nuevo registro.

Los caprichos de esta persona llevaron a demasiados nombres de empresas similares y ahora es imposible que BUSCARV coincida con nombres de compañías similares. 

Algún analista habrá tenido este problema muchas veces, verdad? Y cada uno lo habremos resuelto a nuestra manera, quitando espacios, extrayendo la primera palabra o la última, usando ENCONTRAR o HALLAR, concatenando, ordenando etc.

Siempre pensé que a lo mejor debería crear en una solución basada en Macro (codigo VBA) pero nunca lo hice. Unos meses más tarde, para mi sorpresa, descubrí que Microsoft ya lo había pensado y le había dado una hermosa solución. Hoy escribo sobre eso (mejor tarde que nunca 😛)

Sin reinventar la rueda, veamos qué tiene para ofrecer Microsoft. Listo para eso?



Primero veamos los datos

Aquí hay una lista de 70 registros con nombres de compañías similares pero no coincidentes. Se observa que para cada registro similar se ha generado una ID de cliente por separado.

En mi lista SÓLO hay 70, pero ahora imagina que tenemos 500, 1000, o 10000. Y es posible!!!


Y tambíen tenemos otro conjunto de nombres de compañías (sin duplicación similar). Esto es obligatorio tenerlo.

Objetivo:

Eliminar los nombres de compañías similares y retener sólo uno con un ID de cliente único


Pues aquí es donde viene útil lo que Microsoft creó!


El complemento Fuzzy Lookup para Excel.


Desafortunadamente no está o al menos no pude encontrar la versión en Español, pero da igual porque vamos a ver que es fácil de usar y muy útil.

Para empezar explicar que la palabra Fuzzy significa en inglés varias cosas, pero aquí lo que nos quiere decir es borroso o difuso. 

La parte Lookup se refiere a las funciones BUSCARV/BUSCARH/BUSCAR, ya que éstas en inglés son VLOOKUP/HLOOKUP/LOOKUP.

El complemento Fuzzy Lookup para Excel fue desarrollado por Microsoft Research y realiza una coincidencia difusa de datos de texto en Microsoft Excel. Se puede usar para identificar filas duplicadas difusas dentro de una sola tabla o para unir difusamente filas similares entre dos tablas diferentes. La coincidencia es robusta a una amplia variedad de errores, incluidos errores ortográficos, abreviaturas, sinónimos y datos añadidos / faltantes. Por ejemplo, podría detectar que las filas "Sr. Andrew Hill "," Hill, Andrew R. "y" Andy Hill "se refieren a la misma entidad subyacente, y devuelven una puntuación de similitud junto con cada coincidencia. Si bien la configuración predeterminada funciona bien para una gran variedad de datos textuales, como nombres de productos o direcciones de clientes, la coincidencia también puede personalizarse para dominios o idiomas específicos.


Esta página tiene todo lo que desea saber sobre cómo instalar este complemento en su Excel

• Tiene el enlace de descarga
• Y las instrucciones y otros detalles que puedas necesitar a lo largo del camino

Para descargarse el complemento se ha de ir a esta direccion:

Fuzzy Lookup Excel

1. Se ha clic en Download.

2. Se hace clic en el complemento descargado

3. Hacemos clic en Instalar

4. Otra vez hacer clic en Instalar y aceptar las condiciones

5. El complemento para Excel se instaló. Hacemos clic en Close. Y también cerramos Excel (guardando tus archivos antes claro está! :) )

Ahora abrimos Excel.

Así es como aparece en Excel después de la instalación. Notemos que ha aparecido un menú más llamado Fuzzy Lookup.

Ahora convierta sus datos en tablas . (Tutorial sobre tablas: Guía sobre las tablas)

• Utilizamos el atajo de teclado Ctrl + T para guardar ambos conjuntos de datos en una tabla
• Proporcionamos a las tablas un nombre adecuado en la pestaña Diseño (aparece cuando se hace clic en cualquier celda dentro de la tabla)

Bueno pues hagamos el Fuzzy Lookup

• Seleccionamos la configuración correcta (1)
• Hacemos clic en el botón Relación entre 'Columna izquierda' y 'Columna derecha' (2)
• Establecemos la relación (Match columns) (3) 
• Marcamos los elementos que se desea en las Columnas de salida (output columns) (4)
• Establecemos el umbral de similitud (Similarity Threshold) (5). Cuanto más alto sea el umbral, más estrictamente coincidirán los registros y cuanto menor sea el umbral, más vagamente se igualarán los registros
• Antes de hacer clic en Ir. Elija la celda donde desea colocar la nueva tabla. Haga clic en GO
• Hecho!

Mira a continuación.

• Estos son los nombres de clientes similares
• ID cliente ha sido extraído como está de la tabla 'NombresSimilares'
• Nombre del cliente único se han agregado en frente de cada nombre de cliente similar
• Los registros vacíos no pudieron ser igualados 
• Reducir el umbral de similitud puede ayudar
• Esto es lo que queríamos. Ahora podemos filtrar fácilmente los similares
• Puntuación de similitud (similarity): cuanto mayor sea el puntuación, mayor será la posibilidad de que Excel haya dado la coincidencia correcta

Bueno pues cambiando el umbral se puede tener mucha diferencia.

1. Umbral de 0.5

2. Umbral de 0.3

3. Umbral de 0.1

Mira las diferencias.

Qué te pareció el Fuzzy Lookup?

Déjame abajo un comentario con tus impresiones y también comparte si has resuelto un problema complejo con un simple complemento en Excel.

Descárgate mi archivo

Fuzzy Lookup

Te ha gustado el artículo?

No te olvides de mostrar tu gratitud hacia el autor (yo) mediante una de las 3 siguientes acciones:

1. Puedes dejar una donación pinchando en la siguiente imagen.

2. Puedes hacer clic en los anuncios mostrados en la página

3. Puedes recomendar el artículo en Google haciendo clic en g+1 (justo después del artículo), también puedes compartirlo en Facebook, y/o dejar comentarios.

Quieres aprender más en Excel o tienes algún problema que necesita solución?

Puedo ofrecer servicios de consultoría así como cursos personalizados online. Mírate el siguiente enlace si estás interesado/a.

Consultoría Excel / Cursos de Excel

El Sabio de Excel busca colaborades del blog

Si te interesa compartir tu conocimiento sobre Excel de tal manera que puedas ayudar a otras personas, por favor mándame un email a elsabiodeexcel@gmail.com

Subscríbete y recibe todos las entradas por email

Para ello regístrate suministrando tu email en el sitio "Recibe los trucos en tu email" del menú de la derecha.

O sígueme por las redes sociales

Facebook, Twitter, Youtube y Google+