Cómo automatizar un informe mensual en Excel sin saber programar

Si cada mes alguien de tu equipo abre varios ficheros, copia los datos, los pega en una hoja, arregla columnas y actualiza un par de tablas dinámicas, ese informe se puede automatizar. Y en muchos casos sin escribir ni una línea de código.

En este artículo te explico el método que uso con mis clientes para que un informe mensual pase de llevar horas a actualizarse con un clic.

1. Ordena las fuentes de datos

El primer paso no es técnico: es decidir de dónde salen los datos. Lo ideal es que cada mes los ficheros de origen (exportaciones del ERP, extractos del banco, listados de ventas) se guarden siempre en la misma carpeta y con la misma estructura de columnas.

• Una carpeta por informe, por ejemplo Ventas mensuales.
• Un fichero por mes, con el mismo formato.
• Nada de filas de totales ni celdas combinadas dentro de los datos.

2. Une los ficheros con Power Query

Power Query viene incluido en Excel y es la herramienta clave para automatizar sin programar. Desde la pestaña Datos > Obtener datos > Desde un archivo > Desde una carpeta seleccionas la carpeta de origen y Excel combina todos los ficheros en una sola tabla.

En el editor de Power Query aplicas una sola vez las transformaciones que hoy haces a mano: quitar columnas que sobran, cambiar formatos de fecha, filtrar filas vacías o renombrar encabezados. Power Query graba cada paso y lo repetirá automáticamente cada vez que actualices.

3. Carga los datos en una tabla o en el modelo de datos

Con Cerrar y cargar los datos llegan a Excel como una tabla. Si el volumen es grande o necesitas cruzar varias tablas (ventas con clientes y productos, por ejemplo), cárgalos en el modelo de datos y trabaja con Power Pivot.

4. Construye el informe una sola vez

Sobre esa tabla montas las tablas dinámicas, los gráficos y los segmentadores del informe. Como todo apunta a la tabla de Power Query, ya no hay que tocar nada cuando lleguen datos nuevos.

5. Actualiza con un clic

Cada mes basta con dejar el fichero nuevo en la carpeta y pulsar Datos > Actualizar todo (Ctrl + Alt + F5). Power Query lee la carpeta, aplica los pasos y refresca tablas dinámicas y gráficos.

¿Y cuándo hace falta una macro?

Power Query resuelve la mayoría de los casos. Una macro en VBA tiene sentido cuando además necesitas:

• Generar un PDF por cliente, tienda o comercial y enviarlo por correo.
• Guardar copias con fecha en otra carpeta.
• Rellenar plantillas de Word o PowerPoint con los datos del informe.
• Lanzar todo el proceso desde un botón propio en la cinta de opciones.

En esos casos la macro se apoya en lo que ya hace Power Query y añade solo los pasos que faltan.

Errores habituales

• Cambiar el formato de los ficheros de origen sin avisar: si una columna cambia de nombre, la consulta falla. Conviene pactar el formato con quien exporta los datos.
• Mezclar datos y cálculos en la misma hoja: la tabla de datos debe quedar limpia y los cálculos, en otras hojas.
• Fórmulas con rangos fijos como A2:A500: usa tablas, que crecen solas.

Conclusión

Automatizar un informe mensual en Excel es sobre todo una cuestión de orden: fuentes estables, Power Query para preparar los datos y un informe que se construye una vez. Si quieres que lo hagamos juntos con tus propios ficheros, echa un vistazo a mi servicio de automatización de tareas con Excel o cuéntame tu caso.