Deja de perder el tiempo 4

En esta publicación, utilizaremos el modelo de datos para mejorar nuestro proceso de generación de informes. Esta es la cuarta publicación de la serie Deja de perder el tiempo y aquí es donde se reúne todo lo que hemos aprendido. Cuando hayamos terminado esta publicación, actualizar nuestro informe será extremadamente fácil. De hecho, solo requerirá un solo clic… o ningún clic si lo prefiere

Revisar

Como revisión rápida, aquí está el informe que estamos intentando automatizar:

En la primera publicación aprendimos cómo mejorar nuestro informe con tablas y SUMIFS. En la segunda publicación , aprendimos cómo usar Power Query para combinar tablas. En la tercera publicación , aprendimos cómo usar Power Query para importar datos y buscar archivos csv, combinarlos sin BUSCARV y agregar los valores sin SUMIFS. Luego, cargamos los datos combinados en una tabla en una hoja de trabajo existente con las siguientes opciones de Importar datos:

Sin embargo, en esta publicación, cargaremos los datos en el modelo de datos .

Cargar en modelo de datos

Entonces, en lugar de usar las opciones en la captura de pantalla anterior, solo creamos una conexión y la cargamos en el modelo de datos usando las siguientes opciones:

En este punto, los datos se almacenan en el modelo de datos en lugar de mostrarse en la hoja de trabajo. Entonces, hmmm… si no podemos ver los datos en la hoja de trabajo, ¿cómo accedemos a ellos? Bueno, una forma sencilla es crear una tabla dinámica.

Tabla dinámica

Para construir una tabla dinámica con datos del modelo de datos, usamos el comando Insertar Tabla dinámica . En el cuadro de diálogo resultante, optamos por utilizar el modelo de datos de este libro como fuente de datos:

Damos clic en Aceptar y ahora solo nos falta construir la tabla dinámica.

Lo hacemos insertando campos de la tabla combinada (la que fusionamos en la publicación anterior). Insertamos el campo Línea FS en el área Filas y el campo Importe en el área Valores . Podemos hacer clic y arrastrar o simplemente marcar cada casilla de verificación.

Nota: verá que el nombre del campo en la tabla es “Importe” pero el nombre en el área Valores es “Suma del monto”. Cuando insertamos el campo Monto en el informe, Excel creó automáticamente la medida implícita “Suma del monto” (a la que nos referiremos en breve).

Los resultados se muestran a continuación:

Y en este punto, estamos muy cerca. La única cuestión que queda es la estructura o formato del informe. Nuestra tabla dinámica anterior no se parece en nada a nuestro informe objetivo.

Entonces… mmm. Es como si necesitáramos un informe de tabla dinámica para extraer valores del modelo de datos… pero necesitamos un informe basado en fórmulas para obtener el formato exacto que necesitamos.

Una tabla dinámica recupera valores del modelo de datos. Está bien. Pero no proporciona el formato que necesitamos. Pero las fórmulas sí… porque podemos colocar fórmulas en cualquier celda que deseemos, insertar filas en cualquier lugar que necesitemos y aplicar cualquier formato que necesitemos.

¿Existe alguna manera de tener lo mejor de ambos mundos? ¿Usar de alguna manera fórmulas para recuperar valores del modelo de datos? O mejor aún… ¿hay alguna manera de convertir simplemente la tabla dinámica en fórmulas? Las respuestas son: ¡sí, sí y sí!

Fórmulas

Mi amigo de Excel, Rob Collie, me enseñó este truco… ¡¡¡gracias Rob!!!!!

Dado que creamos nuestra tabla dinámica con el modelo de datos, tenemos un comando disponible que convertirá la tabla dinámica en fórmulas. Se llama, apropiadamente, Convertir a fórmulas. Para usarlo, seleccionamos cualquier celda de la tabla dinámica y luego seleccionamos Herramientas de tabla dinámica Herramientas OLAP Convertir a fórmulas . Bam:

Nota: Si la tabla dinámica utiliza un rango o tabla normal como fuente de datos, el comando Convertir a fórmulas estará deshabilitado.

La tabla dinámica desapareció y en su lugar hay un montón de fórmulas… ¡guau! Estas fórmulas utilizan funciones cúbicas para recuperar valores del modelo de datos.

En este punto, estamos en buena forma y tenemos un par de opciones. Simplemente podríamos formatear este informe como deseemos insertando filas y cortando y pegando líneas de informe en el orden correcto. Y eso funcionaría bien.

Pero, como ya tenemos el informe básico configurado, modifiquemos las fórmulas para que podamos pegarlas en nuestro informe formateado a continuación:

Cuando inspeccionamos la primera fórmula de cantidad, vemos una función CUBEVALUE como esta:

=VALORCUBO("Estemodelodedatosdellibro",$A4,B$3)

Se muestra en contexto a continuación.

El primer argumento “ThisWorkbookDataModel” le dice a la función que recupere valores del modelo de datos. Eso tiene sentido. Luego, vemos una referencia a la etiqueta de fila “Cuentas por pagar” en A4 y la medida implícita “Suma del monto” en B3. Podemos modificar estos argumentos para poder pegar la fórmula en nuestra estructura básica de informe.

Comencemos reemplazando la referencia a B$3 con esto: “[Suma del monto]” … y tenga en cuenta que las “comillas” y los [corchetes] son ​​necesarios. La fórmula actualizada se ve así:

=CUBEVALUE("ThisWorkbookDataModel",$A4,"[Suma del importe]")

Ahora, solo necesitamos hacer el mismo tipo de cosas para la etiqueta de la fila y encerrar la etiqueta entre comillas y corchetes. Podemos hacer esto con el operador de concatenación de la siguiente manera:

=CUBEVALUE("ThisWorkbookDataModel","["$A4"]","[Suma del importe]")

Además, como queremos poder pegar la fórmula en cualquier columna, eliminaremos el signo de dólar $ delante de la referencia A4 para que sea relativa, como se muestra a continuación:

=CUBEVALUE("ThisWorkbookDataModel","["A4"]","[Suma del importe]")

Una vez realizadas estas actualizaciones, ahora podemos copiar esa fórmula y pegarla en la estructura de nuestro informe.

¡Si, funciona! Y podemos pegarlo en las celdas restantes:

¡Y lo conseguimos!

El informe es el mismo que el de la primera publicación. Pero la versión original de este informe era muy manual para actualizar cada mes. Este, que parece idéntico, es más fácil de actualizar y mantener cada mes. Solo necesitamos hacer clic en el comando Datos Actualizar todo . Eso inicia la secuencia de actualización… Power Query recupera los datos actualizados, combina los datos y las tablas de búsqueda, agrega los valores, los envía al modelo de datos y nuestras fórmulas recuperan los valores actualizados.

Entonces, nuestro proceso de actualización mensual pasó de manual a automatizado. ¡Así dejas de perder el tiempo amigo!

Pero espera, Jeff, todavía tenemos un paso manual. Quiero decir, después de abrir el libro, aún debe hacer clic manualmente en el botón Actualizar todo. Vamos… ¿no podemos automatizar esto por completo? Bueno, seguro. Si no desea tener que hacer clic en el comando Actualizar todo, puede indicarle a Power Query que se actualice cuando abra el libro.

Actualización al abrir

Para que la consulta de combinación se actualice automáticamente al abrir el libro, haga clic con el botón derecho en el nombre de la consulta en el panel Consultas y conexiones y seleccione Propiedades .

Nota: puede alternar la visualización del panel Consultas y conexiones haciendo clic en Datos Consultas y conexiones.

En el cuadro de diálogo Propiedades de consulta resultante , marque Actualizar datos al abrir el archivo como se muestra a continuación.

Ahora, cuando abra el libro, la consulta se actualizará automáticamente.

Y así es como pasamos un informe de manual a automático

Resumen

Al final del día, espero que las cosas que cubrimos en esta serie te ayuden a moverte hacia la derecha en la escala a continuación:

Los usuarios de Excel estamos todos en diferentes lugares de nuestro viaje a Excel. Independientemente de dónde se encuentre ahora, si desea ascender en la escala, ofrecemos cursos de capacitación a pedido que pueden ayudarlo.

Contamos con dos itinerarios: Pregrado y Maestría . Los cursos de pregrado cubren características “clásicas” de Excel… cosas como funciones, fórmulas, tablas dinámicas, formato condicional, BUSCARV, SUMIFS, ÍNDICE/COINCIDENCIA, etc. Los cursos de maestría cubren temas más técnicos, incluidos Power Query, Power Pivot, Power BI y VBA/Macros.

Puedes saltar dondequiera que estés sin tener que empezar desde el principio. Por ejemplo, si ya se siente cómodo con las funciones clásicas de Excel, puede pasar a la pista de Maestría.

• Curso 1: los fundamentos
• Curso 2: fórmulas y funciones
• Curso 3: Tablas dinámicas
• Curso 4: papeles de trabajo de uso interno y funciones relacionadas

Vea los cursos, temas y detalles de pregrado: Cursos de pregrado

• Curso 1: Power Query, Power Pivot, Power BI, diseño de tablas y gráficos
• Curso 2: VBA y Macros

Ver cursos de maestría, temas y detalles: Cursos de maestría

Aquí hay una imagen que le ayuda a ver la progresión y cómo encajan los cursos:

Si podemos brindarle información adicional sobre nuestros cursos de capacitación, contáctenos … ¡estaremos encantados de ayudarle!

Y gracias por consultar la serie de blogs Stop Wasting Time. ¡Espero que las cosas que discutimos te ayuden a hacer tu trabajo más rápido!

Recursos adicionales

Archivo de muestra: StopWasting4.xlsx