Mapas del tesoro 3

Esta es la tercera publicación de la serie Treasure Maps donde hablamos de varias formas de implementar tablas de mapeo. En esta serie, utilizamos tablas de mapeo para ayudar a traducir etiquetas entre los datos y el informe. En la primera publicación , usamos SUMIFS para extraer los valores a la tabla de mapeo. En la segunda publicación , utilizamos Power Query. Ahora usaremos Power Pivot. Hagámoslo.

Objetivo

Tenemos una gran cantidad de datos almacenados en un archivo CSV, como este:

Nos gustaría utilizar los datos para crear un informe similar a este:

Dado que las etiquetas entre los datos (1000, 1001, 1002,…) y el informe (Efectivo y equivalentes de efectivo,…) son diferentes, creamos una tabla de mapeo que incluye las líneas del informe y los grupos que necesitaremos, así:

Veamos cómo podemos usar Power Pivot para conectar los puntos.

Video

Narrativo

Abordaremos esto en los siguientes pasos:

• Recuperar tablas
• Relacionar tablas
• Informe de tabla dinámica

Hagámoslo.

Recuperar tablas

Una vez más usaremos Power Query para recuperar las tablas, pero esta vez las cargaremos en el modelo de datos.

En lugar de repetir los pasos detallados de Power Query en esta publicación, solo proporcionaré un resumen rápido. No dudes en consultar la publicación anterior si deseas consultar los pasos detallados.

Al igual que antes, usamos el comando Datos Obtener datos Desde archivo Desde texto/CSV y buscamos el archivo de datos. Pero, a diferencia de antes, cuando cargamos en una consulta de solo conexión, debemos marcar la casilla Agregar estos datos al modelo de datos , así:

Marcar la casilla nos permite relacionar la tabla de datos con la tabla de asignación en Power Pivot, lo cual haremos en breve. Pero, antes de hacerlo, debemos tomar la tabla de mapeo.

Entonces, nuevamente, usamos Power Query para recuperar la tabla de asignación, cargamos en una consulta de solo conexión y nuevamente marcamos la casilla Agregar estos datos al modelo de datos.

Ahora que hemos recuperado los datos y el mapa, es momento de relacionarlos usando Power Pivot.

Relacionar tablas

Ahora es el momento de relacionar los datos y las tablas de mapeo. Es decir, necesitamos decirle a Excel cómo se relacionan las dos tablas entre sí. Una vez más, nuestras tablas están relacionadas a través de las columnas AcctID.

Si no ha usado Power Pivot antes, querrá hacer clic en el comando administrar modelo de datos en la pestaña Datos… que se ve así:

La primera vez que haga clic en este botón, se le pedirá que habilite Power Pivot. Una vez que lo haga, debería tener la pestaña Power Pivot en la parte superior de su ventana de Excel.

En el futuro, puede abrir Power Pivot haciendo clic en ese comando en la pestaña Datos, o puede hacer clic en el comando Administrar en la pestaña Power Pivot que se ve así:

Con la ventana de Power Pivot abierta, hacemos clic en el comando Inicio Vista de diagrama y luego podemos ver las tablas de datos y mapas:

Para definir la relación, simplemente hacemos clic y arrastramos la columna AcctID desde la tabla de datos a la columna AcctID en el mapa. La relación se ilustra así:

Ahora que hemos definido la relación, estamos listos para crear nuestro informe resumido. Podemos cerrar Power Pivot y regresar a Excel.

Informe de tabla dinámica

En Excel, usamos el comando Insertar Tabla dinámica para abrir el cuadro de diálogo Crear tabla dinámica:

Para la fuente de datos, utilizaremos el modelo de datos de este libro . Podemos colocar la tabla dinámica en una hoja nueva o existente según lo deseemos.

Notamos que tenemos las tablas de datos y mapas enumeradas en nuestro panel Campos de tabla dinámica. Cuando expandimos ambas tablas, vemos las columnas, como esta:

Verificamos los campos Sub , Línea FS y Monto (en ese orden), y nuestra tabla dinámica se parece un poco a esto:

Modifiquemos el diseño del informe para mostrar solo Activos. (Más adelante, podemos crear otra tabla dinámica para mostrar Pasivos y Patrimonio).

Comencemos filtrando el patrimonio y los pasivos. Hay varias formas de lograr esto… una forma es hacer clic derecho en cada una y seleccionar Filtro Ocultar . Entonces, haga clic derecho en Equidad, Filtro Ocultar. Haga clic con el botón derecho en Pasivos, Filtro Ocultar. Nuestro informe actualizado se ve así:

A continuación, podemos hacer clic y arrastrar las etiquetas FS para ordenarlas como deseemos:

A continuación, activamos los subtotales haciendo clic derecho en la celda Actual y seleccionando Subtotal en el menú contextual. Nuestro informe actualizado se ve así:

Dado que la estructura básica de nuestro informe se ve bastante bien, en este momento todo es una cuestión cosmética.

Podemos usar la galería de Estilos de tabla dinámica para elegir el estilo que nos guste:

Podemos insertar una fila en blanco entre grupos haciendo clic en el comando Diseño de tabla dinámica Filas en blanco Insertar línea en blanco después de cada elemento :

Desactive los botones +/- y los encabezados de campo :

Podemos cambiar los formatos de números haciendo clic derecho en cualquier valor y seleccionando Formato de número :

Podemos ingresar nuevas etiquetas para el subtotal o las celdas del encabezado si lo deseamos:

Luego podemos hacer lo mismo para crear el lado de Pasivos y Patrimonio del informe:

Sí… ¡creo que lo tenemos!

Conclusión

Lo bueno de este enfoque es que las tablas dinámicas son dinámicas… por lo que si hay nuevos recursos en la tabla de mapeo el próximo período, el informe se expandirá automáticamente para mostrarlos cuando se actualice.

Aunque esta serie se ha centrado en una ilustración para mapear tablas (un estado financiero), tenga en cuenta que esta misma técnica se puede aplicar en otras situaciones en las que las etiquetas son diferentes entre dos tablas. Por ejemplo, descarga la actividad bancaria y necesita asignar las descripciones de la actividad a las cuentas. O bien, tiene una descarga de Expensify que debe asignarse a códigos de gastos. O bien, tiene SKU de inventario que deben asignarse a códigos de producto en su sistema de contabilidad. Etcétera. Siempre que las etiquetas sean diferentes entre dos lugares, las tablas de mapeo pueden ser una forma eficaz de llegar del punto A al punto B.

Espero que esta serie de Mapas del Tesoro te haya sido útil… y espero que las tablas cartográficas te ayuden a descubrir el tesoro de la eficiencia en tu trabajo

Archivo de muestra

Mapasdeltesoro3Descargar