Las búsquedas habitual de Excel son perfeccionistas y fallan por errores tipográficos o espacios adicionales. Si proporcionadamente se puede piratear una opción utilizando las funciones BUSCAR XL, RECORTAR y LOWER anidadas, es frágil y difícil de ayudar. La coincidencia aproximada de Power Query es la forma más inteligente y “suficientemente cercana” de trabajar.

Power Query utiliza un sofisticado cálculo de coincidencia difusa para calcular una puntuación de similitud entre dos cadenas, salvando la brecha entre “Microsoft” y “Mcrosoft” sin una sola tirada manual. Esto significa que puede combinar exportaciones desordenadas con bases de datos maestras más limpias en segundos.

El objetivo: una historia de dos mesas

Imagine que tiene un noticia de ventas (T_Sales, naranja) realizado de entradas manuales y necesita extraer la región de ventas de una tabla maestra (T_Master, verde).

Sin confiscación, oportuno a errores tipográficos y diferentes convenciones de nomenclatura en T_Sales, una BUSCAR XL habitual devolvería #N/A para prácticamente todas las búsquedas. Por lo tanto, necesitamos que Excel mire los nombres en T_Sales, encuentre la coincidencia más cercana en T_Master y devuelva la región, incluso si la ortografía es tenuemente incorrecta.

Para manejar taquigrafía empresarial específica que ningún cálculo puede adivinar (como “MSFT”), todavía necesita una pequeña tabla puente (T_Abbrev, azur) con encabezados de columna Desde y Hasta. Asegúrese de que esta tabla esté serie antiguamente de comenzar.

Paso 1: Importa tus datos usando conexiones

Antaño de soltar el cálculo, debe incorporar las tres tablas al motor Power Query. Comience asegurándose de que sus rangos de datos tengan el formato de tablas de Excel (Ctrl+T). Luego, cuantía la primera tabla seleccionando cualquier celda de la tabla y haciendo clic en Datos > Desde tabla/rango.

Relacionado

Si no cambia el nombre de las tablas en Excel, hoy es el día para comenzar

¿Qué hay en un nombre? Bueno, sobrado.

Para evitar saturar su manual, no haga clic en el yema habitual “Cerrar y cargar” posteriormente de importar cada tabla a Power Query. En su oficio, haga clic en la flecha cerca de debajo y seleccione “Cerrar y cargar en”.

Luego, en el cuadro de diálogo Importar datos, seleccione “Solo crear conexión” y haga clic en “Aceptar”.

Luego de tener trillado este proceso para las tres tablas, las verá como conexiones solo en el panel Consultas y conexiones (que se abre automáticamente posteriormente de crear la primera conexión), listas para la combinación.

Paso 2: fusiona tus tablas para comenzar la agilidad

Para comenzar la agilidad, haga clic derecho en “T_Sales” en el panel Consultas y conexiones y seleccione “Fusionar”.

Luego, en el cuadro de diálogo Combinar, seleccione “T_Master” como segunda tabla y, en ambas vistas previas de tablas, seleccione la columna “Nombre”. Básicamente, le estás diciendo a Excel que quieres unir las tablas T_Sales y T_Master, y la columna Nombre es lo que las conecta.

Ahora, marque “Usar coincidencia aproximada para realizar la combinación” para indicarle a Excel que algunas de las conexiones no son perfectas oportuno a errores tipográficos o convenciones de nomenclatura inconsistentes. Debajo del capó, la opción Ignorar caso está marcada de forma predeterminada, lo que significa que “MICROSOFT” coincidirá correctamente con “microsoft” sin ningún paso adicional. Para el tipo de unión, quédese con “Foráneo izquierdo”: esto le garantiza conservar cada transacción de ventas mientras simplemente marbete la información regional correspondiente.

Relacionado

Cómo puede utilizar comodines en Microsoft Excel para refinar su búsqueda

Encuentra coincidencias parciales en un instante.

Paso 3: establezca el comienzo de similitud

Proporcionadamente, entonces le ha dicho a Excel qué tablas está fusionando, la variable de conexión y que las coincidencias no son exactas. Ahora necesitas establecer el comienzo.

Haga clic en la flecha contiguo a “Opciones de coincidencia aproximada”.

El comienzo de similitud es una escalera de 0,00 a 1,00, donde 0,00 coincide con todos los títulos con cualquier nivel de similitud y 1,00 coincide solo con los títulos exactos. Observe en la captura de pantalla susodicho que tres de las seis filas coinciden. Esto se debe a que Power Query tiene como valía predeterminado un comienzo de similitud de 0,80. En este nivel, el motor es precioso: detecta fácilmente “Microsoft Inc”, pero rotura en errores tipográficos más agresivos como “Appel” o “Gogle”.

En este caso, un comienzo de 0,50 es lo suficientemente bajo como para detectar “Appel” y “Gogle” sin que coincidan accidentalmente palabras no relacionadas. Encuentra cinco de seis filas, dejando solo la sigla “MSFT” sin igual.

Paso 4: Aplicar la tabla de transformación para abreviaturas

Ningún cálculo es lo suficientemente inteligente como para retener que “MSFT” significa Microsoft. Aquí es donde entra en gozne la tabla T_Abbrev: en el menú desplegable Tabla de transformación, haga clic en “T_Abbrev” y esto obligará al motor a tratar sus anulaciones manuales como coincidencias perfectas. Una vez seleccionado, el recuento de coincidencias en la parte inferior aumentará a un consumado seis de seis.

Paso 5: limpie y resuma su noticia final

Cuando hace clic en “Aceptar”, se le dirige al Editor de Power Query con una nueva columna llena de resultados de la tabla.

Haga clic en el icono “Expandir” en el encabezado de la columna, desmarque “Usar el nombre de la columna llamativo como prefijo” para evitar encabezados torpes como T_Master.Name y haga clic en “Aceptar”.

Puede ver que los nombres ahora están estandarizados en una nueva columna Nombre.1 y las regiones se agregan en una nueva columna Región.

Desde aquí, elimine la desordenada columna Nombre llamativo (haga clic con el yema derecho en el encabezado de la columna y seleccione “Eliminar”), haga clic y tiro la nueva columna Nombre cerca de la izquierda para que sea la primera columna de la tabla y cámbiele el nombre (haga doble clic en el encabezado de la columna) a poco como Nombre oficial. Mientras esté allí, asegúrese de que se asignen los tipos de datos correctos a cada columna haciendo clic en los íconos en los encabezados de las columnas.

Relacionado

Cómo suprimir e importar datos usando Power Query en Excel

¡No pases por suspensión esta increíble útil de Excel!

Adecuado a que este proceso marbete cada transacción individual, termina con varias filas para la misma empresa. Para convertir esto en un noticia despejado, en la pestaña Alterar, haga clic en “Agrupar por”.

Luego, en el cuadro de diálogo Agrupar por, seleccione el yema de opción “Renovador” en la parte superior. Esto le permite agrupar por varias columnas a la vez. En este caso, queremos agrupar por las columnas Nombre y Región. Para hacer esto, para la primera agrupación, seleccione “Nombre oficial”. Luego, haga clic en “Juntar agrupación” y seleccione “Región”.

Ahora, establezca el cálculo. En el campo Nombre de nueva columna, escriba Ventas totalesen el campo Operación, seleccione “Suma” y en el campo Columna, seleccione “Ventas”.

Cuando hace clic en “Aceptar”, se agregan todas las filas repetidas.

Una vez que haya revisado los resultados para confirmar que todos los datos aparecen como se esperaba, haga clic en “Cerrar y cargar” en la pestaña Inicio.

Ahora, sus datos bellamente resumidos y perfectamente escritos aparecen en una nueva hoja de Excel. Aquí hay una captura de pantalla del antiguamente y el posteriormente que muestra el beneficio de usar la coincidencia aproximada de Power Query para fusionar y suprimir conjuntos de datos desordenados en Excel.

Las búsquedas habitual son demasiado rígidas para la verdad de los datos confusos. Al utilizar la coincidencia difusa, se cambia la entrada manual de datos por un sistema automatizado y escalable que comprende la intención en oficio de solo la sintaxis. Esta es solo una de las muchas formas de rendir las herramientas de Power Query para ordenar los datos desordenados de las hojas de cálculo. Una vez que comience, no podrá detener.

SO

Windows, macOS, iPhone, iPad, Android

Prueba gratuita

1 mes

Microsoft 365 incluye paso a aplicaciones de Office como Word, Excel y PowerPoint en hasta cinco dispositivos, 1 TB de almacenamiento OneDrive y más.