Deja de perder el tiempo 2

Esta es la segunda publicación de la serie Deja de perder el tiempo. En esta publicación, aprendemos cómo Power Query puede ayudarnos a actualizar nuestro informe en menos tiempo. Después de todo, ¿por qué querríamos dedicar más tiempo del necesario a actualizarlo? Eso se llama “perder el tiempo” y simplemente no nos gusta desperdiciar un recurso tan valioso. Vamos a por ello.

Resumen rápido

Como resumen rápido, comenzamos con un informe como este:

Comenzó actualizándose manualmente con una fórmula como esta:

Pero tuvimos que reescribir todas las fórmulas cada mes a medida que cambiaban los valores. Entonces lo mejoramos con fórmulas que hacían referencia a las celdas de datos, como esta:

Eso lo mejoró, pero nuestras fórmulas fallaron cuando los valores se movieron de celda. Por ejemplo, cuando se ordenaron los datos. Además, si se agregaran nuevas filas al rango de datos, necesitaríamos reescribir las fórmulas. Entonces, mejoramos el libro almacenando los datos en una tabla y usando SUMIFS, así:

Y eso es hasta donde llegaremos usando elementos “clásicos” de Excel. Es hora de pasar a algunas herramientas de Excel “modernas”.

Los términos Excel clásico y Excel moderno me los presentó mi amigo de Excel, Rob Collie. Básicamente, Excel clásico representa las cosas de Excel que hemos estado usando durante mucho tiempo… fórmulas, funciones, características, tablas dinámicas, BUSCARV, etc. El Excel moderno ha estado aquí durante las últimas versiones de Excel e incluye herramientas eléctricas como Power Query, Power Pivot, el modelo de datos, etc.

Consulta de energía

Ahora, daremos el siguiente paso en nuestro viaje y usaremos Power Query. En la primera publicación de la serie, simplifiqué los datos. Como recordatorio, se veía así:

Pero en la práctica, nuestros datos suelen dividirse en varias tablas. Por ejemplo, una tabla de datos que almacena los valores y una tabla de búsqueda que almacena los nombres de las cuentas.

Aquí hay un ejemplo de nuestra tabla de datos :

Y aquí hay un ejemplo de nuestra tabla de búsqueda :

Entonces, antes de que podamos preparar nuestro informe, primero debemos combinar estas tablas. Ahora bien, ¿cómo abordaríamos esto con las funciones clásicas de Excel? Mmm. Quizás podríamos usar BUSCARV para recuperar los nombres de cuentas y las líneas FS. Y eso funcionaría muy bien… pero… cuando escribimos una fórmula, terminamos “cuidándola” cada mes. En otras palabras, si hay más filas que el mes pasado, debemos confirmar que las fórmulas se completen en las nuevas filas. Si la fuente de datos cambia, debemos confirmar que las fórmulas hacen referencia al nuevo rango. Entonces, sí, BUSCARV funcionaría aquí… pero… probemos Power Query y veamos si ofrece ventajas adicionales.

Pista: lo hace. Al principio, usaremos Power Query para hacer lo que podríamos haber hecho con BUSCARV, pero a medida que continúa la serie, descubriremos cómo Power Query proporciona beneficios mucho más allá de lo que puede hacer BUSCARV.

Usaremos los siguientes tres pasos para combinar nuestras tablas con Power Query:

• Obtener tabla de datos
• Obtener tabla de búsqueda
• combinarlos

Veámoslos uno por uno.

Nota: dependiendo de su versión de Excel, es posible que tenga o no Power Query o los mismos comandos, pantallas y opciones que mis capturas de pantalla a continuación, que se crearon con Excel O365 para Windows.

Obtener tabla de datos

Para obtener la tabla de datos en Power Query, seleccionamos cualquier celda de la tabla de datos y hacemos clic en Datos Desde tabla/rango . La tabla de datos se carga en Power Query como se muestra a continuación:

Ahora, hacemos clic en Inicio Cerrar y cargar en… para mostrar el siguiente cuadro de diálogo:

Seleccionamos Sólo Crear Conexión y hacemos clic en Aceptar.

Y hacemos lo mismo para la siguiente mesa.

Obtener tabla de búsqueda

Seleccionamos cualquier celda en la tabla de búsqueda y hacemos clic en Datos De tabla/rango . Cerramos y cargamos hasta… y solo creamos conexión .

Con nuestros datos y tablas de búsqueda dentro de Power Query, ahora podemos combinarlos.

combinarlos

Necesitamos combinar los datos y las tablas de búsqueda. En Excel clásico, probablemente usaríamos algún tipo de función de búsqueda, como BUSCARV, para hacer esto. Pero aquí usaremos Power Query. Hacemos clic en Datos Obtener datos Combinar consultas Fusionar . En el cuadro de diálogo Combinar resultante, seleccionamos nuestra tabla de Datos del primer menú desplegable y nuestra tabla de Búsqueda del segundo menú desplegable. Luego, necesitamos identificar la columna compartida o de búsqueda en ambas tablas. Para hacer esto, simplemente usamos el mouse para seleccionar las columnas AcctID de ambas tablas, como se muestra a continuación.

En este caso, el tipo de unión predeterminado (exterior izquierdo) es perfecto ya que queremos todas las filas de la tabla de datos y cualquier fila coincidente de la tabla de búsqueda. Damos clic en Aceptar y se abre la ventana del Editor de Power Query:

Hacemos clic en el ícono Expandir en la parte superior derecha de la etiqueta de la columna de búsqueda y seleccionamos las columnas de la tabla de búsqueda que queremos ver en nuestros resultados. En este caso, queremos ver las columnas AcctName y FS Line, como se muestra a continuación.

Damos clic en Aceptar… y bam:

En este punto, nos vemos bien. Hemos combinado los datos y las tablas de búsqueda y estamos listos para devolver los resultados a una hoja de trabajo.

Hacemos clic en Inicio Cerrar y cargar en… y decidimos enviar los resultados a una Tabla en una hoja de trabajo existente , como se muestra a continuación:

Damos clic en Aceptar y los resultados se envían a nuestra hoja de cálculo de Excel:

En este punto, podemos actualizar esta tabla haciendo clic en el comando Datos Actualizar todo . Power Query importará cualquier cambio, incluidos nuevos datos y filas de búsqueda, los combinará y actualizará la tabla de resultados… ¡sin que tengamos que cuidar las fórmulas!

Por ahora, incluiremos los valores en nuestro informe usando SUMIFS como lo hicimos antes.

Pero, al usar Power Query para combinar estas tablas en lugar de BUSCARV, tenemos opciones y beneficios adicionales. Y daremos nuestro siguiente paso hacia la automatización en la próxima publicación de la serie.

Resumen

Comenzamos nuestro viaje con pasos manuales. A medida que aprendimos más, pudimos avanzar hacia la derecha… hacia nuestro objetivo de automatización.

Aprendimos cómo las tablas abordan nuevas filas de datos y la función SUMIFS aborda el orden de clasificación. En esta publicación, aprendimos cómo Power Query puede importar y combinar datos y tablas de búsqueda sin fórmulas. Y, en el futuro, podemos simplemente hacer clic en el comando Actualizar todo a medida que cambian los valores de la tabla o se agregan nuevas filas.

Daremos un paso más hacia la derecha en nuestra próxima publicación de la serie

Siguiente publicación

• Deja de perder el tiempo 3

Recursos adicionales

• Archivo de muestra: StopWasting2.xlsx
• Publicaciones anteriores sobre Power Query