Deja de perder el tiempo 1

Esta es la primera publicación de una serie que muestra cómo transformar un informe mensual de actualización manual a automática. A lo largo del camino, veremos cómo las características clásicas de Excel, como tablas y SUMIFS, pueden ayudar en cierta medida, y luego cómo las características modernas de Excel, como Power Query y el modelo de datos, pueden ayudar aún más. Cada publicación de la serie proporciona el siguiente paso en la secuencia de automatización.

Llamé a esta serie “Deja de perder el tiempo” porque cuando dedicamos más tiempo del necesario a realizar una tarea, estamos perdiendo el tiempo. Tendemos a actualizar nuestros libros de Excel mediante pasos manuales, como el mes pasado. Y nos sentimos demasiado ocupados para aprender una forma más eficiente. Así que seguimos realizando los mismos pasos manuales todos los meses. Mientras tanto, Excel sigue ahí sentado, esperando para ayudar.

Si te gustan los deportes, imagina que eres el entrenador y Excel está sentado en el banquillo. Excel es como tu jugador estrella y no estás poniendo Excel en el juego. Excel está sentado en el banquillo, agitando los brazos diciendo “entrenador, entrenador… ¡por favor pónganme en el juego!”

Esta serie está diseñada para demostrar cómo incorporar Excel al juego. A lo largo de la serie, descubriremos conocimientos de Excel que nos ayudarán a pasar nuestros libros de trabajo de manual a automatizado. Después de todo, si estamos ocupados, ciertamente no querríamos dedicar más tiempo del necesario a hacer algo. Entonces, ese es el panorama general de la serie, ¡y espero que lo disfrutes!

Informe

Antes de llegar demasiado lejos, echemos un vistazo a nuestro informe de muestra. Como tengo experiencia en contabilidad, ilustraré este proceso con un estado financiero clásico. Sin embargo, los pasos y el proceso se pueden aplicar ampliamente a muchos otros tipos de informes.

Aquí está el informe que mejoraremos en las próximas publicaciones:

Y aquí están los datos de origen, que cambian cada mes:

Nuestro objetivo es incluir automáticamente los nuevos datos mensuales en el informe.

Comenzaremos de manera fácil y asumiremos que los datos ya están limpios y en Excel. Pero a medida que avance la serie, aumentaré la complejidad de la ilustración para que podamos aprender cuán poderosas son las herramientas modernas de Excel.

Dado que vamos a mostrar la progresión completa de este informe, desde manual a automatizado, comenzaremos revisando el proceso de actualización manual.

Actualizaciones manuales

Todos los usuarios de Excel están en un viaje y nos encontramos en diferentes lugares a lo largo del camino. Pero todos empezamos por el principio. Todos abrimos Excel por primera vez y vemos la cuadrícula. Entramos y comenzamos a jugar y a descubrir cosas. Al principio de nuestro viaje, terminamos haciendo muchas cosas manualmente. ¿Por qué? Simplemente porque no hemos aprendido opciones más eficientes. A medida que aprendemos más, podemos automatizar más. Y nuestro viaje continúa así… aprendiendo y mejorando a medida que avanzamos.

Pero, a veces, podemos llegar a cierto punto y sentirnos lo suficientemente cómodos como para dejar de mejorar nuestros cuadernos. Quizás estemos ocupados en el trabajo y decidamos que los libros de trabajo son lo suficientemente buenos. Sabemos lo suficiente como para actualizar los informes mensuales. Aunque se necesitan muchos pasos manuales, al menos sabemos cómo hacerlos. Claro, puede haber un montón de pasos manuales y seguro que tendremos que trabajar fuera de horario y los fines de semana. Pero al menos podemos hacerlo.

Pero aquí está la cuestión: podemos delegar MUCHAS tareas manuales a Excel. Es decir, podemos automatizar muchas cosas con Excel. Me gusta pensarlo así. Al comienzo de nuestro viaje a Excel, hacemos las cosas manualmente. Pero nuestro objetivo es llegar al lugar donde nuestros libros estén automatizados. Y lo que nos lleva de lo “manual” a lo “automatizado” es el conocimiento de Excel. Yo lo visualizo así:

A lo largo de nuestro recorrido por el conocimiento de Excel, aprendemos muchas cosas interesantes que nos ayudan a trabajar más rápido. Al comienzo de nuestro viaje, podemos actualizar nuestro informe utilizando Excel como un teclado digital de 10 teclas. Quizás usaríamos fórmulas como esta:

Y ese enfoque nos permite terminar el informe. Entonces lo usamos… por un tiempo. Hasta que descubrimos que cada vez que los datos cambian, tenemos que literalmente reescribir cada fórmula. Al tratar de encontrar una manera mejor, decidimos adoptar un enfoque diferente. Un enfoque más eficiente. Uno que no requiera que reescribamos cada fórmula cuando los valores cambian. Usamos referencias de celda directa, como esta:

Esta es una mejora porque a medida que los valores de las celdas cambian, fluyen hacia el informe automáticamente y no tenemos que reescribir las fórmulas.

Pero luego descubrimos que este enfoque es frágil y se rompe fácilmente. Por ejemplo, nuestras fórmulas fallan cuando los valores de los datos ocupan celdas diferentes, los datos se clasifican en un orden diferente o se agrega una nueva cuenta. Cuando las fórmulas fallan, tenemos que reescribirlas… manualmente.

Entonces, nos propusimos mejorar el libro con algunas características clásicas de Excel.

Excel clásico al rescate

Nuestro enfoque actual tiene algunos problemas… básicamente, cambiar el orden de clasificación rompe las fórmulas y agregar nuevas cuentas requiere que reescribamos las fórmulas. Entonces, veámoslos uno por uno. Abordaremos el orden de clasificación con SUMIFS y agregaremos nuevas cuentas con Tablas. Comencemos convirtiendo nuestro rango de datos ordinario en una tabla.

Mesas

Cuando tenemos un rango de datos que puede expandirse, por ejemplo, se agregan nuevas cuentas o transacciones, es una buena idea almacenar esos datos en una tabla en lugar de un rango normal. ¿Por qué? Porque las tablas se expanden automáticamente para incluir las nuevas transacciones. Y podemos hacer referencia a los datos en una tabla con referencias de tabla estructuradas en lugar de referencias de rango de estilo A1.

Para convertir nuestro rango ordinario en una tabla, comience haciendo clic en cualquier celda del rango de datos, así:

Luego seleccione el comando Insertar Tabla . Excel muestra un cuadro de diálogo de confirmación. Damos clic en Aceptar y se crea nuestra tabla:

Las mesas tienen muchas ventajas. Una ventaja es que se expanden automáticamente. Cuando escribe o pega nuevos valores inmediatamente debajo (o a la derecha), la tabla se expandirá automáticamente para incluirlos. Esto significa que si ingresamos una nueva cuenta, por ejemplo Nómina de Cheques que se asigna a la Línea FS de Efectivo y Equivalentes de Efectivo, se incluirá automáticamente en la tabla. Pero debemos entender cómo hacer referencia a los rangos de tablas en nuestras fórmulas. Entonces, hablemos de nombres.

Las tablas tienen nombres. Puede ver/establecer el nombre con el campo TableTools Nombre de tabla . Nuestra tabla se llama Table1 y podemos usar ese nombre en nuestras fórmulas para hacer referencia a los datos de la tabla. Pero también podemos hacer referencia a una sola columna de la tabla utilizando una referencia de tabla estructurada. Para hacer referencia a una columna específica, usamos el nombre de la tabla y luego el nombre de la columna entre [corchetes], así: Table1[Amount] o Table1[FS Line] .

Ahora, veamos cómo podemos usar SUMIFS para resumir los datos en nuestra nueva tabla.

SUMIFS

SUMIFS es una función maravillosa y está diseñada para sumar números en una columna basándose en etiquetas de fila coincidentes. El primer argumento identifica la columna de números a sumar y los argumentos restantes vienen en pares. Cada par define una condición. Oficialmente, se ve así:

=SUMIFS(rango_suma, rango_criterio1, valor_criterio1, ...)

Pero se me ocurrió una narrativa que utilizo para ayudarme a recordar los argumentos. Cada palabra en negrita en la narrativa representa un argumento:

“Suma esta columna de números, pero incluye solo aquellas filas donde esta columna es igual a este valor”.

Por ejemplo, si quisiéramos sumar la columna de monto de la tabla , pero solo incluir aquellas filas donde la columna Línea FS coincide con la etiqueta de nuestro informe , podríamos usar algo como esto:

Completamos la fórmula para calcular los valores restantes del informe:

El mismo informe que antes, pero este enfoque es más eficiente si se actualiza cada mes porque no estamos reescribiendo fórmulas cuando cambia el orden de clasificación. ¡Hurra!

Resumen

Comenzamos nuestro viaje realizando actualizaciones manuales. Para mejorar nuestro informe, agregamos una tabla para que, a medida que se agregan nuevas filas a la tabla, las fórmulas hagan referencia a ellas automáticamente. Luego usamos la función SUMIFS para calcular los valores del informe.

Movimos el informe un poco hacia la derecha… de actualizarse manualmente a ser más automatizado:

Todavía tenemos muchas mejoras por hacer y las cubriremos en las próximas publicaciones de la serie… ¡estad atentos!

Siguiente publicación

• Deja de perder el tiempo 2

Recursos adicionales

• Archivo de muestra: StopWasting1.xlsx
• Más información sobre SUMIFS
• Obtenga más información sobre el uso de tablas con SUMIFS