Utilice Obtener y transformar para conciliar dos listas

En esta publicación, usaremos una consulta Obtener y transformar para ayudar con nuestra conciliación. La idea de esta publicación surgió de una pregunta de Laura (¡gracias Laura!). La idea básica es que tenemos dos hojas de trabajo. Uno contiene los totales de las facturas y el otro contiene los detalles de las partidas, donde hay muchas partidas por factura.

Este es un proyecto de conciliación básico, donde queremos saber si alguna factura en el resumen no aparece en el detalle y si los totales de alguna factura no coinciden con la suma de las partidas. ¿Suena como un proyecto grande y tedioso? ¡No con una consulta Obtener y transformar! Échale un vistazo.

Objetivo

Antes de pasar a la mecánica, asegurémonos de comprender los detalles de nuestra tarea.

En una hoja de trabajo, tenemos una lista de totales de facturas. Este resumen de factura, almacenado en una tabla llamada SummaryTable, se muestra a continuación.

En otra hoja de trabajo, hemos almacenado los detalles de la partida de la factura en una tabla llamada DetailTable, que se muestra a continuación.

Tenemos un par de objetivos con esta reconciliación. Primero, queremos identificar las facturas que aparecen en el resumen pero no en el detalle. Por ejemplo, la factura 1004 está en el resumen pero no en el detalle. A continuación, queremos comparar los totales entre ambas listas e identificar cualquier factura en la que el total del resumen no esté vinculado a la suma de los detalles de la partida.

Dado que se trata de Excel, existen varios enfoques para realizar esta tarea. En esta publicación, usaremos una consulta Obtener y transformar para automatizar esta conciliación, no solo para este mes, sino para todos los meses futuros.

Detalles

Los pasos básicos para preparar la hoja de conciliación son:

• Crear la consulta de resumen
• Crear la consulta detallada
• Crear la consulta de conciliación

Hagámoslo.

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 la consulta de resumen

Para crear la consulta de resumen, haga clic en cualquier celda de datos del rango y haga clic en el icono de la cinta Datos Desde tabla . Si los datos ya están almacenados en una tabla, como la nuestra, se abrirá el Editor de consultas. Si los datos están en un rango normal, Excel le pedirá que cree la tabla primero.

Confirmamos que los datos del resumen de la factura llegaron al Editor de consultas, como se muestra a continuación.

Dado que nuestros datos están listos para funcionar, no necesitamos realizar ninguna transformación. Si es necesario limpiar los datos con los que está trabajando, puede eliminar columnas, agregar filtros o realizar cualquier transformación adicional necesaria fácilmente.

Cuando los datos se ven bien, usamos el menú desplegable Inicio Cerrar y cargar (no el botón Cerrar y cargar) y luego seleccionamos Cerrar y cargar en .

En el cuadro de diálogo Cargar en resultante, seleccionamos Solo crear conexión y luego hacemos clic en Cargar. Seleccionamos esta opción porque no queremos que los resultados de la consulta se devuelvan a las celdas del libro de trabajo, pero queremos poder acceder a los resultados en otra consulta (en nuestra próxima consulta de conciliación).

Crear la consulta detallada

Ahora, necesitamos crear la consulta detallada. Para hacer esto, seleccionamos cualquier celda de datos en el rango y seleccionamos Datos De tabla .

Los datos fluyen hacia el Editor de consultas, como se muestra a continuación.

Nuestros datos deben agregarse por ID. Entonces, primero, eliminaremos la columna Artículo. Para ello, hacemos clic derecho en el encabezado de la columna Elemento y seleccionamos Eliminar .

A continuación, debemos crear una fila para cada ID y calcular la suma relacionada. Entonces, hacemos clic derecho en el encabezado de la columna ID y seleccionamos Agrupar por . Excel muestra el cuadro de diálogo Agrupar por, donde confirmamos que el campo Agrupar por es ID y que queremos crear una nueva columna llamada Total que suma la columna Importe, como se muestra a continuación.

Hacemos clic en Aceptar y los resultados actualizados aparecen en el Editor de consultas, como se muestra a continuación.

Nuestros datos se ven bien, por lo que seleccionamos Cerrar y cargar en y , una vez más, seleccionamos la opción Solo crear conexión en el cuadro de diálogo Cargar en.

En este punto, nuestro libro de trabajo contiene una consulta SummaryTable y una consulta DetailTable como se muestra en el panel siguiente.

El último paso es combinarlos y poner los resultados en una nueva hoja de trabajo de conciliación.

Crear la consulta de conciliación

Para comparar estas consultas, seleccionamos el comando Datos Nueva consulta Combinar consultas Combinar .

En el cuadro de diálogo Combinar resultante, seleccionamos SummaryTable en el primer menú desplegable y DetailTable en el segundo menú desplegable. Luego, debemos decirle a Excel qué campo es el campo común entre ellos. Hacemos esto haciendo clic en la columna ID de ambas ventanas de vista previa. Esto se muestra a continuación.

Ya casi terminamos. La decisión final es cómo debe unir Excel las dos tablas. Especificamos esto con el menú desplegable Unirse a tipo. En nuestra conciliación, queremos que nuestra conciliación final incluya todas las facturas en el resumen, incluso si la factura no aparece en la tabla de detalles. Entonces, la unión externa izquierda predeterminada es perfecta. Hay muchas opciones adicionales que puede elegir, por ejemplo, incluir todos los elementos en el detalle incluso si no aparecen en el resumen (Exterior derecho), incluir todas las facturas de ambas listas (Exterior completo) o incluir solo facturas que aparecen en ambas listas (Interior). Una vez que seleccionamos el tipo de unión, hacemos clic en Aceptar.

El Editor de consultas muestra los resultados de la combinación, como se muestra a continuación.

A continuación, debemos indicarle a Excel que muestre las columnas de DetailTable, por lo que hacemos clic en el ícono en el encabezado NewColumn y seleccionamos Expandir (o alternativamente, el ícono Transformar Columna estructurada Expandir). Elegimos qué columnas mostrar; en nuestro caso, elegiremos las columnas ID y Total y haremos clic en Aceptar.

Los resultados actualizados se muestran a continuación.

Hasta el momento podemos identificar fácilmente qué facturas del resumen no aparecen en el detalle por el valor nulo . Ahora, sólo necesitamos facilitar la comparación de los dos totales. Haremos esto calculando la diferencia entre las dos columnas Total.

Creamos una nueva columna calculada haciendo clic en el icono Agregar columna Columna personalizada . El cuadro de diálogo Agregar columna personalizada se muestra a continuación.

Nuestro nuevo nombre de columna es Diff, y para crear la fórmula hacemos doble clic en la columna Total en la lista Columnas disponibles, escribimos un operador de resta (-) y luego hacemos doble clic en la columna NewColumn.Total. Hacemos clic en Aceptar y la nueva columna se mostrará en el Editor de consultas, como se muestra a continuación.

Como queremos ver los resultados de esta consulta en Excel, hacemos clic en el comando Cerrar y cargar . Los resultados aparecen en Excel como se muestra a continuación.

En este punto, la parte divertida del proceso de reconciliación ha terminado. Ahora comienza la parte no tan divertida en la que tenemos que profundizar en los detalles y comprender por qué ciertas facturas no aparecen en la hoja de detalles, como la factura 1004, y por qué algunas tienen un monto diferente (como la factura 1002).

Actualizar el próximo período

Ahora bien, esto puede parecer que son muchos pasos para generar nuestra reconciliación. Sin embargo, el beneficio de invertir tiempo es que la conciliación se generará en períodos futuros muy rápidamente al actualizar este libro de trabajo en lugar de crear un libro de conciliación nuevo. Simplemente pegue los datos de resumen en la SummaryTable existente, los datos detallados en la DetailTable y luego haga clic derecho y actualice la tabla de resultados verde. Excel genera instantáneamente una tabla de conciliación actualizada, ¡sin necesidad de abrir el Editor de consultas!

Nota: Si la estructura de los datos cambia, o desea actualizar el tipo de unión o las transformaciones, puede abrir el Editor de consultas para actualizar, pero, cuando la estructura básica sea la misma, estará listo.

Si tiene otros enfoques divertidos para las conciliaciones, o algún otro consejo divertido para consultas de Obtener y Transformar, compártalo publicando un comentario a continuación… ¡gracias!

Recursos

• Descargar archivo Excel: ReconList.xlsx