Agrupamiento difuso en Excel: cómo combinar datos similares pero no idénticos

¿Alguna vez has tenido que agrupar manualmente datos similares pero no idénticos en Excel? Descubre cómo Power Query ofrece una solución efectiva a través del agrupamiento difuso, una funcionalidad potente pero poco conocida que automatiza este tedioso proceso.

Trabajar con datos no estandarizados en Excel puede convertirse en una actividad frustrante. A menudo nos encontramos con valores que deberían considerarse iguales pero presentan pequeñas diferencias de escritura, formato o sintaxis. Agrupar manualmente estos elementos requiere tiempo valioso y aumenta el riesgo de errores. Afortunadamente, Excel ofrece una solución eficaz pero poco conocida: la agrupación difusa en Power Query, una técnica avanzada de «fuzzy matching» que simplifica notablemente este proceso.

Comprender la agrupación difusa en Excel

La agrupación difusa es una funcionalidad avanzada de Power Query que permite identificar y unir automáticamente elementos similares pero no idénticos. Pero antes de profundizar, aclaremos: ¿qué es Power Query? Se trata de una potente herramienta de preparación de datos integrada en Excel que permite transformar y limpiar los datos de forma eficiente. A diferencia de las funciones estándar de Excel que requieren coincidencias exactas, la agrupación difusa utiliza algoritmos de similitud para reconocer variantes del mismo dato. Esta funcionalidad es particularmente útil cuando trabajas con:

• Listas de nombres con variantes ortográficas (ej. «Juan Pérez» y «Pérez Juan»)
• Datos insertados por diferentes personas con convenciones distintas
• Información proveniente de diferentes sistemas con formatos no uniformes
• Respuestas a preguntas abiertas en encuestas o cuestionarios

La agrupación difusa se basa en una «puntuación de similitud» configurable que determina cuánto deben ser similares dos cadenas para considerarse equivalentes. Además, puedes crear tablas de traducción personalizadas para mapear términos específicos que deseas tratar como idénticos. En los próximos párrafos, te guiaré a través de todos los pasos necesarios para implementar esta potente funcionalidad en tus hojas de cálculo, incluyendo la preparación inteligente de los datos y la operación de agregación.

Preparar los datos para la agrupación difusa

Antes de utilizar la agrupación difusa, es fundamental organizar correctamente los datos. La preparación adecuada garantizará resultados óptimos y simplificará todo el proceso. El primer paso consiste en convertir tus datos en una tabla de Excel. Este es un requisito esencial para utilizar Power Query y acceder a las funcionalidades de agrupación difusa.

Para crear una tabla:

1. Selecciona cualquier celda dentro de tus datos
2. Pulsa Ctrl+T o ve a la pestaña «Insertar» y haz clic en «Tabla»
3. Verifica que la opción «La tabla tiene encabezados» esté seleccionada si la primera fila contiene los nombres de las columnas
4. Confirma haciendo clic en «Aceptar»

Una vez creada la tabla, es aconsejable asignarle un nombre significativo y conciso. Esto facilitará la referencia a ella en las fórmulas y en Power Query. Para renombrar la tabla:

1. Selecciona cualquier celda en la tabla
2. En la pestaña «Diseño de tabla» que aparece, modifica el nombre en el campo «Nombre de tabla» en la esquina superior izquierda

También es importante verificar la calidad de los datos antes de continuar. Comprueba la presencia de celdas vacías, errores de formato o caracteres especiales que podrían influir en el proceso de agrupación. Si es necesario, realiza una limpieza de datos eliminando espacios extra, unificando mayúsculas/minúsculas o corrigiendo errores evidentes. Esta fase de limpieza de datos es crucial para garantizar la integridad del conjunto de datos y mejorar la eficacia de la agrupación difusa.

Finalmente, identifica qué columnas contienen los valores que deseas agrupar. La agrupación difusa funciona mejor cuando se aplica a una sola columna a la vez, por lo que es posible que debas reorganizar tus datos en consecuencia. Esta preparación inteligente de los datos te ayudará a obtener resultados más precisos en las fases posteriores.

Crear una tabla de traducción personalizada

Una de las características más potentes de la agrupación difusa es la posibilidad de utilizar una tabla de traducción personalizada. Esta tabla, que funciona como tabla de referencia, permite definir explícitamente qué términos deben considerarse equivalentes, independientemente de su puntuación de similitud. La tabla de traducción debe tener una estructura específica con dos columnas:

• De: contiene los valores originales que deseas mapear
• A: contiene los valores a los que deseas convertir los términos originales

Por ejemplo, podrías querer considerar «email», «e-mail» y «correo electrónico» como el mismo concepto. En este caso, la tabla de traducción podría aparecer así:

De	A
email	correo electrónico
e-mail	correo electrónico
correo	correo electrónico

Para crear esta tabla de transformación:

1. Inserta los encabezados «De» y «A» en dos celdas adyacentes
2. Rellena las filas con los pares de valores que deseas mapear
3. Selecciona toda el área y convierte a tabla (Ctrl+T)
4. Asigna un nombre significativo a la tabla, por ejemplo, «Traducción»

La tabla de traducción es particularmente útil para:

• Estandarizar terminologías específicas del sector
• Unificar abreviaturas y formas extendidas
• Gestionar sinónimos o términos equivalentes en diferentes contextos
• Corregir errores comunes de escritura o formato

Cuanto más completa y precisa sea tu tabla de traducción, mejores serán los resultados del agrupamiento difuso. Vale la pena dedicar tiempo a crear una tabla de traducción completa, especialmente si planeas realizar operaciones de unión frecuentemente en datos similares.

Importar los datos en Power Query

Una vez preparados los datos y creada la tabla de traducción, es el momento de importar todo en Power Query para iniciar el proceso de agrupamiento difuso. La carga de datos en Power Query es un paso fundamental que te permitirá aplicar transformaciones avanzadas antes de volver a cargar los resultados en Excel.

Para importar la tabla principal:

1. Selecciona cualquier celda dentro de la tabla de datos
2. Ve a la pestaña «Datos» en la cinta de opciones
3. Haz clic en «De tabla/rango» en el grupo «Obtener y transformar datos»

Se abrirá el Editor de Power Query con tus datos. Este entorno te permite aplicar transformaciones avanzadas antes de volver a cargar los resultados en Excel. A continuación, también debes importar la tabla de traducción (si la has creado). El proceso es idéntico:

1. Vuelve a Excel sin cerrar el Editor de Power Query
2. Selecciona una celda en la tabla de traducción
3. Ve a la pestaña «Datos» y haz clic en «De tabla/rango»

Ahora tendrás dos consultas separadas en el Editor de Power Query, visibles en el panel «Consultas» a la izquierda. Es importante que ambas consultas estén disponibles en el entorno de Power Query antes de proceder con el agrupamiento difuso.

Antes de continuar, es recomendable verificar que los tipos de datos sean correctos en ambas tablas. Power Query asigna automáticamente tipos de datos en función del contenido, pero a veces puede ser necesario corregirlos:

1. Selecciona la columna a modificar
2. Haz clic derecho y elige «Cambiar tipo»
3. Selecciona el tipo de datos apropiado (generalmente «Texto» para los datos a agrupar)

Con los datos correctamente importados y formateados, estás listo para aplicar el agrupamiento difuso. Si necesitas cargar más archivos de una carpeta, Power Query también ofrece esta funcionalidad, que puede ser útil para proyectos más complejos que involucran múltiples fuentes de datos.

Aplicar el agrupamiento básico en Power Query

Antes de usar el agrupamiento difuso, es útil comprender cómo funciona el agrupamiento estándar en Power Query. Esto nos proporcionará la base para luego modificar la fórmula e implementar el agrupamiento difuso. Para aplicar un agrupamiento estándar:

1. En el Editor de Power Query, selecciona la columna que contiene los valores a agrupar
2. Ve a la pestaña «Transformar» en la cinta de opciones
3. Haz clic en el botón «Agrupar por»

Se abrirá el cuadro de diálogo «Agrupar por» con varias opciones:

• Agrupar por: selecciona la columna a usar para el agrupamiento
• Nueva columna: introduce un nombre para la columna que contendrá los resultados del agrupamiento
• Operación: elige «Todas las filas» para mantener todos los datos originales

Después de configurar estos ajustes, haz clic en «Aceptar» para aplicar el agrupamiento estándar. Power Query generará una fórmula M que utiliza la función Table.Group(). Esta fórmula aparecerá en la barra de fórmulas en la parte superior del editor. El resultado será una nueva tabla con dos columnas:

• La columna con los valores únicos encontrados en el campo seleccionado
• Una columna que contiene tablas anidadas con todas las filas que corresponden a cada valor único

Este agrupamiento estándar, sin embargo, solo funciona con coincidencias exactas. Para obtener un agrupamiento basado en la similitud, debemos modificar la fórmula generada y transformarla en un agrupamiento difuso, implementando así una coincidencia difusa más flexible.

Modificar la fórmula para el agrupamiento difuso

El paso crucial para implementar el agrupamiento difuso consiste en modificar manualmente la fórmula generada por el agrupamiento estándar. Esto es necesario porque la interfaz de usuario de Power Query no ofrece un botón directo para el agrupamiento difuso. Después de aplicar el agrupamiento estándar, observa la barra de fórmulas en la parte superior del editor. Verás una fórmula similar a esta:

= Table.Group(#"Tipo modificato", {"NomeColonna"}, {{"Dati", each _, type table [NomeColonna=nullable text]}})

Para convertirla en un agrupamiento difuso, debes:

1. Cambiar Table.Group a Table.FuzzyGroup
2. Añadir un cuarto parámetro que defina las opciones del agrupamiento difuso

La fórmula modificada debería aparecer así:

= Table.FuzzyGroup(#"Tipo modificato", {"NomeColonna"}, {{"Dati", each _, type table [NomeColonna=nullable text]}}, [IgnoreCase=true, IgnoreSpace=true, Threshold=0.8, TransformationTable=Traduzione])

Las opciones en el cuarto parámetro controlan el comportamiento del agrupamiento difuso:

• IgnoreCase: cuando se establece en verdadero, el agrupamiento ignora las diferencias entre mayúsculas y minúsculas
• IgnoreSpace: cuando se establece en verdadero, los espacios se ignoran durante la comparación
• Threshold: un valor entre 0 y 1 que determina cuán similares deben ser dos cadenas para ser agrupadas (0.8 es un buen punto de partida)
• TransformationTable: el nombre de la consulta que contiene la tabla de traducción

Después de modificar la fórmula, presiona Enter o haz clic en el signo de verificación junto a la barra de fórmulas para aplicar el cambio. Power Query realizará el agrupamiento difuso según los parámetros especificados.

Es importante tener en cuenta que el valor del Umbral requiere experimentación. Un valor demasiado alto (cercano a 1) requerirá una similitud casi perfecta, mientras que un valor demasiado bajo (cercano a 0) podría agrupar elementos que no deberían considerarse similares. Esta operación de agregación basada en la similitud de las cadenas es el corazón del agrupamiento difuso.

Configurar las opciones de similitud

El éxito del agrupamiento difuso depende en gran medida de la configuración correcta de las opciones de similitud. Estas opciones determinan qué elementos se considerarán similares y, por lo tanto, se agruparán. La opción Umbral es particularmente importante. Representa la puntuación mínima de similitud (de 0 a 1) necesaria para que dos cadenas se consideren equivalentes:

• Un valor de 1.0 requiere una coincidencia exacta (equivalente al agrupamiento estándar)
• Un valor de 0.0 agruparía todos los elementos juntos (raramente útil)
• Los valores entre 0.7 y 0.9 son generalmente más efectivos para la mayoría de las aplicaciones

La elección del valor óptimo depende de la naturaleza de tus datos:

• Para datos con pequeñas variaciones ortográficas: prueba con 0.8-0.9
• Para variaciones más significativas en la redacción: prueba con 0.6-0.8
• Para conceptos relacionados, pero expresados de manera diferente: prueba con 0.5-0.7

Las opciones IgnoreCase e IgnoreSpace son más sencillas de configurar:

• IgnoreCase=true: útil en la mayoría de los casos, ya que las diferencias entre mayúsculas y minúsculas rara vez indican significados diferentes
• IgnoreSpace=true: útil cuando los espacios son inconsistentes (ej. «data base» vs «database»)

Es aconsejable empezar con configuraciones conservadoras (umbral alto) y reducir gradualmente el valor si es necesario. Después de cada modificación, examina cuidadosamente los resultados para verificar que el agrupamiento sea lógico y coherente con tus expectativas.

Recuerda que siempre puedes volver atrás y modificar estas configuraciones si los resultados no son satisfactorios. El proceso de optimización de las opciones de similitud es a menudo iterativo y requiere experimentación. Algunos algoritmos de similitud, como el algoritmo de similitud de Jaccard, pueden ser particularmente eficaces para ciertos tipos de datos, por lo que vale la pena explorar diferentes opciones.

Expandir los resultados del agrupamiento

Después de aplicar el agrupamiento difuso, obtendrás una tabla con dos columnas: la columna de los valores agrupados y una columna que contiene tablas anidadas con todos los datos originales. Para que estos resultados sean más utilizables, debes expandir las tablas anidadas.

Para expandir los resultados:

1. En la columna que contiene las tablas anidadas, haz clic en el icono de expansión (dos flechas divergentes) en el encabezado de la columna.
2. En el cuadro de diálogo que aparece, selecciona las columnas que deseas incluir en los resultados expandidos.
3. Elige si deseas mantener o eliminar el prefijo del nombre de la columna original.
4. Haz clic en «OK» para aplicar la expansión.

Este proceso de expansión de tabla transformará la estructura anidada en una tabla plana con todos los datos originales, pero ahora organizados según el agrupamiento difuso aplicado. Cada fila mostrará el valor agrupado junto con los datos originales correspondientes.

Si la tabla original contenía muchas columnas, es posible que desees seleccionar solo las más relevantes durante la expansión para mantener los resultados manejables. Siempre puedes modificar esta selección más tarde si es necesario. En algunos casos, también podría ser útil considerar la eliminación de columnas innecesarias para simplificar aún más el conjunto de datos.

La expansión de los resultados es especialmente útil cuando deseas:

• Ver todos los valores originales que han sido agrupados.
• Verificar la exactitud del agrupamiento difuso.
• Realizar análisis adicionales sobre los datos agrupados.

Preparar los datos para la visualización o la elaboración de informes.

Después de la expansión, es aconsejable reordenar las columnas de manera lógica para facilitar la interpretación de los resultados. Puedes hacerlo arrastrando los encabezados de las columnas a la posición deseada o utilizando la opción «Mover» en el menú contextual de las columnas. Este paso es importante para crear un conjunto de datos bien dispuesto y ordenado que será más fácil de analizar y presentar.

En esta etapa, también podrías considerar la estandarización de los valores en algunas columnas para garantizar la coherencia en tus informes. Por ejemplo, podrías querer uniformar el formato de los campos de fecha o asegurarte de que todos los nombres estén en un formato consistente (ej. «Apellido, Nombre»). Estas operaciones de limpieza final contribuirán a mejorar la calidad general de tu conjunto de datos.

Cargar los resultados en Excel

Una vez completado el agrupamiento difuso y configurada la salida como desees, es el momento de cargar los resultados nuevamente en Excel para el análisis final o la presentación. Para cargar los resultados:

1. En el Editor de Power Query, ve a la pestaña «Inicio» en la cinta de opciones.
2. Haz clic en el botón «Cerrar y cargar» para enviar los datos directamente a Excel.
3. Alternativamente, haz clic en la flecha debajo de «Cerrar y cargar» y selecciona «Cerrar y cargar en…» para más opciones.

En el cuadro de diálogo «Importar datos», puedes elegir:

• Tabla: carga los datos como una tabla de Excel formateada (opción recomendada).
• Tabla dinámica: crea directamente una tabla dinámica a partir de los datos agrupados.
• Solo conexión: crea solo una conexión a los datos sin cargarlos en una hoja.
• Añadir estos datos al modelo de datos: útil para análisis más complejos o para usar con Power Pivot.

También selecciona la ubicación donde deseas cargar los datos:

• Hoja de cálculo existente: especifica una celda en una hoja existente.
• Nueva hoja de cálculo: crea una nueva hoja para los resultados.

Después de confirmar tus elecciones, Excel cargará los datos agrupados en la ubicación especificada. Los datos mantendrán un vínculo dinámico con la consulta de Power Query, lo que significa que podrás actualizar los resultados si los datos de origen cambian.

Para actualizar los datos en el futuro:

1. Selecciona cualquier celda en la tabla de resultados.
2. Ve a la pestaña «Datos» en la cinta de opciones.
3. Haz clic en «Actualizar todo» o «Actualizar» en el grupo «Consultas y conexiones».

Esto volverá a ejecutar la consulta de Power Query, aplicando nuevamente el agrupamiento difuso a cualquier dato actualizado. Esta funcionalidad de actualización automática es particularmente útil cuando trabajas con datos que cambian con frecuencia o cuando deseas combinar consultas de diferentes fuentes.

Verificar y refinar los resultados

Después de cargar los resultados en Excel, es fundamental verificar la precisión del agrupamiento difuso y realizar los refinamientos necesarios. Incluso con las mejores configuraciones, el agrupamiento automático podría no ser perfecto en el primer intento. Aquí tienes algunas estrategias para verificar y mejorar los resultados:

1. Examina los grupos creados: ordena los datos por el valor agrupado y verifica que todos los elementos en cada grupo estén realmente relacionados. Busca anomalías o elementos que parezcan fuera de lugar.
2. Identifica falsos positivos: elementos diferentes que han sido agrupados erróneamente. Esto indica que el umbral de similitud podría ser demasiado bajo.
3. Busca falsos negativos: elementos similares que no han sido agrupados como se esperaba. Esto sugiere que el umbral podría ser demasiado alto.
4. Actualiza la tabla de traducción: si encuentras errores recurrentes, agrega nuevos mapeos a la tabla de traducción para corregirlos explícitamente.
5. Modifica la configuración de similitud: regresa al Editor de Power Query y cambia el valor de Threshold u otras opciones de similitud para mejorar los resultados.

Para modificar la consulta y refinar el agrupamiento:

1. Selecciona cualquier celda en la tabla de resultados.
2. Ve a la pestaña «Consulta» o «Datos» en la cinta de opciones
3. Haz clic en «Editar» para reabrir el Editor de Power Query
4. Modifica la fórmula de agrupación difusa o la tabla de traducción
5. Cierra y vuelve a cargar para actualizar los resultados

El perfeccionamiento de la agrupación difusa es a menudo un proceso iterativo que requiere varios intentos para obtener los resultados óptimos. No dudes en experimentar con diferentes configuraciones hasta encontrar la combinación que funcione mejor para tus datos específicos. Este proceso de refinamiento contribuirá a garantizar la integridad del conjunto de datos y la calidad de tus resultados finales.

Casos de uso prácticos de la agrupación difusa

La agrupación difusa en Excel es una herramienta versátil con numerosas aplicaciones prácticas en varios sectores. Aquí tienes algunos casos de uso comunes donde esta funcionalidad puede marcar la diferencia:

• Limpieza de bases de datos de clientes: Identificar duplicados con pequeñas variaciones en los nombres (Ej. «Rossi S.p.A.» y «Rossi SpA»)
  • Estandarizar nombres de empresas adquiridas o con marcas diferentes
  • Unificar registros de clientes procedentes de sistemas diferentes
• Análisis de comentarios y encuestas: Agrupar respuestas a preguntas abiertas con un significado similar
  • Identificar temas comunes en reseñas o comentarios de clientes
  • Clasificar sugerencias o quejas para priorizarlas
• Gestión de inventario: Estandarizar nombres de productos introducidos manualmente
  • Identificar productos similares o equivalentes de diferentes proveedores
  • Consolidar categorías de productos con nomenclaturas ligeramente diferentes
• Análisis financiero: Agrupar partidas de gastos similares pero registradas con nombres diferentes
  • Estandarizar descripciones de transacciones bancarias
  • Consolidar categorías de costos para informes más precisos
• Investigación y análisis de mercado: Agrupar nombres de competidores con diferentes variantes ortográficas
  • Estandarizar nombres de localidades o regiones geográficas
  • Unificar términos del sector o jerga técnica

Para cada uno de estos casos de uso, la agrupación difusa ofrece un ahorro de tiempo significativo en comparación con la categorización manual, al mismo tiempo que reduce el riesgo de errores humanos. La clave del éxito es adaptar la configuración de similitud y la tabla de traducción a las necesidades específicas de tu escenario.

Limitaciones y alternativas a la agrupación difusa

A pesar de su potencia, la agrupación difusa en Power Query presenta algunas limitaciones que es importante conocer:

Principales limitaciones:

• Funciona mejor con textos relativamente cortos; las frases largas pueden producir resultados impredecibles
• Requiere Power Query, que puede no estar disponible en todas las versiones de Excel
• El rendimiento puede degradarse con conjuntos de datos muy grandes (decenas de miles de filas)
• El algoritmo de similitud no es completamente transparente ni personalizable
• No maneja bien las comparaciones multilingües o caracteres especiales

Para situaciones en las que la agrupación difusa no es adecuada, considera estas alternativas:

• Funciones de búsqueda aproximada: CERCA.VERT combinado con funciones como SIMILE o DISTANZA.TESTO
• Fórmulas de matriz complejas para identificar coincidencias aproximadas
• Complementos de terceros especializados en coincidencias difusas
• Enfoques externos a Excel: Software especializado para la deduplicación de datos
• Herramientas ETL (Extract, Transform, Load) con funcionalidades de coincidencia difusa
• Soluciones de bases de datos con capacidades de búsqueda difusa
• Lenguajes de programación como Python o R con bibliotecas para el emparejamiento difuso
• Métodos híbridos: Preprocesamiento de datos para estandarizar formatos comunes
• • Agrupación inicial basada en partes del texto (ej. primeras letras)
  • Combinación de agrupación automática y revisión manual

Si la agrupación difusa en Power Query no satisface tus necesidades, evalúa si una de estas alternativas podría ser más adecuada para tu caso específico. En muchos escenarios, un enfoque combinado que utiliza varias técnicas puede ofrecer los mejores resultados.

Conclusiones y mejores prácticas

La agrupación difusa en Power Query representa una herramienta potente pero a menudo subestimada en el arsenal de Excel. Permite automatizar un proceso que de otro modo requeriría horas de trabajo manual y sería propenso a errores. Para obtener los mejores resultados con la agrupación difusa, considera estas mejores prácticas:

1. Prepara adecuadamente los datos: limpia los datos antes de la agrupación, eliminando formatos inconsistentes o caracteres especiales innecesarios.
2. Invierte en la tabla de traducción: una tabla de traducción bien construida puede mejorar significativamente los resultados, especialmente para términos específicos del sector o abreviaturas comunes.
3. Itera y perfecciona: no esperes resultados perfectos en el primer intento. Prepárate para experimentar con diferentes configuraciones de similitud y perfeccionar el proceso.
4. Verifica los resultados: comprueba siempre manualmente una muestra de los resultados para asegurarte de que la agrupación sea lógica y coherente con tus expectativas.
5. Documenta el proceso: toma nota de las configuraciones utilizadas y las decisiones tomadas, especialmente si planeas repetir el proceso en el futuro.
6. Considera el contexto: adapta las configuraciones de similitud al contexto específico de tus datos y al nivel de precisión requerido.
7. Mantén los datos originales: conserva siempre una copia de los datos originales sin agrupar para futuras referencias o para iteraciones alternativas.

La agrupación difusa es especialmente valiosa en una era de creciente volumen y variedad de datos. Dominar esta técnica te permitirá transformar datos desordenados e inconsistentes en información estructurada y utilizable, mejorando significativamente la calidad de tus análisis e informes en Excel.

Pubblicato in Excel