Obtener y transformar: una alternativa a las comparaciones de listas BUSCARV

Recientemente recibí una pregunta de Excel sobre cómo realizar una comparación de listas específica y pensé en demostrar cómo usar una consulta Obtener y transformar como alternativa a las comparaciones de listas basadas en fórmulas que normalmente se realizan con funciones como BUSCARV y CONTAR.SI. La pregunta original es: “Estoy intentando utilizar una lista de 79 números de tarjetas de crédito para encontrar todas las apariciones de cada número de tarjeta en una lista de más de 400.000 transacciones. Sólo estamos probando transacciones para esas tarjetas de crédito”. Este tipo de comparación de listas se puede realizar con fórmulas, pero en esta publicación usaremos una consulta Obtener y transformar. Gracias María por tu pregunta!

Objetivo

Antes de llegar demasiado lejos, tomemos un momento para visualizar los datos y nuestro objetivo. La primera lista contiene las tarjetas de crédito seleccionadas para su revisión. La tabla se llama CreditCards y se parece a esto.

La segunda lista contiene todas las transacciones. Se llama Transacciones y se parece a esto.

Nuestro objetivo es que Excel cree una lista de transacciones para los CCNums que aparecen en la lista de tarjetas de crédito.

Ahora, si solo tuviera unos pocos números de tarjeta, esta tarea podría realizarse fácilmente utilizando un filtro. Pero, a medida que crece el número de tarjetas de crédito, este enfoque se vuelve más tedioso y difícil de actualizar en períodos futuros. Otra opción sería escribir una fórmula auxiliar a la derecha de la tabla de transacciones. La fórmula podría usar una función como BUSCARV o CONTAR.SI para ver si el número de tarjeta de crédito de cada transacción aparece en la lista de tarjetas de crédito. Entonces podríamos aplicar un filtro y considerarlo bueno. Antes de Excel 2016, ese es exactamente el enfoque que usaría, y tengo un artículo al que se hace referencia a continuación que explica los pasos del proceso. Pero, con las herramientas Obtener y Transformar integradas en Excel 2016 para Windows, tenemos otra opción. Veamos cómo realizar esta tarea con una consulta Get Transform.

Pasos

Procederemos con los siguientes pasos:

• Crear una conexión a cada lista
• Crear una consulta de combinación
• Devolver los resultados

Hagamos esto.

Nota: Los pasos a continuación se presentan con Excel para Windows 2016. Si está utilizando una versión diferente de Excel, tenga en cuenta que es posible que las funciones presentadas no estén disponibles o que deba descargar e instalar el complemento Power Query.

Crear una conexión a cada lista

Necesitamos crear una conexión con cada lista. Primero la lista de números de tarjetas de crédito seleccionados. Seleccione cualquier celda de la tabla y use el siguiente comando Obtener y transformar.

• Datos Desde tabla

Esto abre el Editor de consultas, como se muestra a continuación.

Nota: No necesitamos preocuparnos por el formato de los datos CCNum, porque los valores almacenados no cambian.

Ahora, para crear una conexión en lugar de devolver los datos a una hoja de cálculo de Excel, usamos el comando Cerrar y cargar en (en lugar de Cerrar y cargar), seleccionando el siguiente comando.

• Inicio Cerrar y cargar Cerrar y cargar en…

Esto muestra el cuadro de diálogo Cargar en, donde seleccionamos Solo crear conexión, como se muestra a continuación.

Damos clic en el botón Cargar y ahora tenemos la consulta de solo conexión. Se muestra en el panel Consultas del libro a la derecha de la ventana de Excel, como se muestra a continuación.

Ahora, creamos otra consulta de conexión para la tabla de transacciones y seguimos los mismos pasos. Seleccione una celda en la tabla de transacciones, use el comando Obtener y transformar de la tabla, seleccione Cerrar y cargar en, Solo crear conexión y Cargar. Ahora tenemos dos consultas de conexión, como se muestra a continuación.

Ahora estamos listos para fusionarlos.

Crear una consulta de combinación

Podemos combinar consultas de dos formas. Podemos “fusionarlos” o podemos “añadirlos”. Si las agregamos, las combinamos verticalmente y apilamos las dos tablas una encima de la otra. Si los fusionamos, los combinamos horizontalmente y colocamos las columnas una al lado de la otra. Esto es para lo que tradicionalmente usaríamos BUSCARV. Para crear nuestra consulta de combinación, seleccionamos el siguiente comando.

• Datos Nueva consulta Combinar consultas Fusionar

Esto abre el cuadro de diálogo Fusionar, donde podemos definir cómo Excel debe fusionar las dos tablas.

En nuestro caso, queremos todas las tarjetas de crédito en la lista de tarjetas de crédito y solo las transacciones que coincidan en la tabla de transacciones. Entonces, debemos decirle a Excel que la primera tabla es la tabla Tarjetas de crédito y la segunda tabla es la tabla Transacciones, como se muestra a continuación.

Ahora necesitamos decirle a Excel qué columna se debe usar para que coincida con las tablas. Para ello seleccionamos las columnas deseadas de cada tabla, en nuestro caso es la columna CCNum como se muestra a continuación.

En nuestro caso, la combinación se denomina técnicamente combinación externa izquierda, lo que significa que toma todos los registros de la primera tabla y solo los registros coincidentes de la segunda tabla. Existen otros tipos de uniones que son útiles en otro tipo de situaciones. Hacemos clic en Aceptar para regresar al Editor de consultas, como se muestra a continuación.

En este punto, debemos decirle a Excel que expanda la nueva columna y debemos identificar qué columnas de la tabla de transacciones queremos mostrar. Podemos hacer esto haciendo clic en el pequeño botón expandir/agregar en el lado superior derecho de la etiqueta de la columna o haciendo clic en el siguiente ícono de cinta:

• Transformar Columna estructurada Expandir

Esto muestra el cuadro de diálogo Expandir, donde podemos elegir qué columnas de transacciones nos gustaría incluir en los resultados de la consulta, como se muestra a continuación.

Queremos todas las columnas, así que simplemente hacemos clic en Aceptar. La columna se expande y los resultados se muestran en el Editor de consultas, como se muestra a continuación.

¡Guau! Ahora, todo lo que tenemos que hacer es devolver los resultados a Excel.

Devolver los resultados

Para devolver los resultados a Excel, usamos el comando Cerrar y cargar. Bam… tenemos una nueva tabla de resultados en nuestro libro de trabajo como se muestra a continuación.

Y podemos limpiarlo como deseemos, por ejemplo, formateando las columnas CCNum y fecha.

Y la mejor parte es que en períodos futuros, cuando se actualice la lista de tarjetas de crédito o la tabla de transacciones, no necesitamos volver a realizar estos pasos… ¡simplemente hacemos clic derecho en la tabla de resultados y actualizamos!

Si tiene alguna otra comparación de lista divertida u obtener y transformar ideas de consulta, compártala publicando un comentario a continuación.

Recursos

• Archivo de muestra: ListCompare
• Artículo sobre comparaciones de listas basadas en fórmulas: Comparar listas con facilidad