Cómo crear un informe automático en Excel a partir de una exportación de datos

Muchos informes empiezan igual: alguien exporta datos de un sistema (ventas, gastos, pedidos), los abre en Excel y pasa una tarde ordenando, filtrando y sumando a mano. La semana siguiente hay que repetirlo desde cero. La buena noticia es que casi siempre el trabajo se puede dejar montado una sola vez.

Esta guía describe cómo estructurar el informe para que baste con pegar la exportación nueva, y cómo comprobar la calidad de los datos antes de confiar en las cifras.

Qué columnas necesita la exportación

Casi cualquier exportación útil tiene cinco datos: fecha, categoría, contraparte (cliente, proveedor), cantidad e importe.

Si su sistema usa otros nombres («Concepto», «Cliente»), basta con indicar en una hoja de parámetros cómo se llama cada columna.

Columnas auxiliares de normalización

Los datos exportados rara vez vienen limpios. Cree junto a la tabla columnas que los normalicen:

• Mes: =MES(A2)
• Año: =AÑO(A2)
• Clave mes y categoría: =B2&"|"&MES(A2)

Con ellas, las sumas se vuelven simples. El total de «Material de oficina» en enero de 2025:

=SUMAR.SI.CONJUNTO(Importe;Categoría;"Material de oficina";Mes;1;Año;2025)

Totales por mes y crecimiento

Una fila por mes con el total: =SUMAR.SI.CONJUNTO(Importe;Mes;1;Año;2025). El crecimiento respecto al mes anterior:

=SI.ERROR(C3/C2-1;"")

Si enero suma 5.000 € y febrero 5.600 €, el crecimiento es 5.600/5.000 - 1 = 12 %. Para comparar con el mismo mes del año pasado, divida entre la celda del año anterior. SI.ERROR evita mostrar errores cuando no hay datos del período previo.

Rankings

Un top 5 de categorías necesita primero el total de cada una y luego ordenar. Con K.ESIMO.MAYOR(Totales;1) obtiene el mayor, y con INDICE y COINCIDIR el nombre asociado:

=INDICE(Categorías;COINCIDIR(K.ESIMO.MAYOR(Totales;1);Totales;0))

Cuidado con los empates: dos categorías con el mismo total devolverían el mismo nombre. Un pequeño truco es sumar una fracción mínima, como la fila dividida entre un millón, para desempatar.

Matriz categoría por mes

Es la tabla que más gusta a la dirección: categorías en filas, meses en columnas. Una sola fórmula con referencias mixtas ($A2 y B$1) se copia a todas las celdas.

Revisión de la calidad de los datos

Antes de presentar nada, compruebe:

• Fechas vacías: =CONTAR.BLANCO(A2:A5001).
• Texto en lugar de números: =SUMAPRODUCTO(--ESTEXTO(E2:E5001)).
• Fechas fuera del período del informe.
• Duplicados: =CONTAR.SI(Clave;Clave2)>1.

Un solo importe pegado como texto («1.250,00» con un espacio) hace que la suma se quede corta sin ningún aviso. Por eso conviene tener un panel con estos contadores.

Errores frecuentes

• Pegar los datos encima de las fórmulas auxiliares.
• Dejar espacios al final de los nombres de categoría: «Servicios » y «Servicios» cuentan por separado.
• Informar sin revisar duplicados tras unir dos exportaciones.
• Usar rangos fijos demasiado cortos, con lo que las filas nuevas quedan fuera.

Plantilla lista para usar: informe universal desde una exportación

La plantilla de pago «Informe universal a partir de una exportación» ya está montada con cinco hojas:

• Parámetros: año del informe y nombres de las columnas de su exportación.
• Datos: encabezado y 5000 filas para pegar, con columnas auxiliares de normalización (mes, año, claves).
• Resumen: totales por mes, crecimiento respecto al mes anterior y al mismo mes del año pasado, top 5 de categorías, top 10 de contrapartes, matriz «categoría × mes» y gráficos de columnas y circular.
• Selección de período: importe y cantidad de cualquier período para las categorías elegidas.
• Revisión de datos: fechas vacías, texto en lugar de números, fechas fuera del período y duplicados, con contadores y explicaciones.

Sirve a contables, gerentes, jefes de departamento y a cualquiera que haga resúmenes a mano de forma periódica. Límites: hasta 5000 filas, 30 categorías y 200 contrapartes. Funciona en Excel 2007 o posterior y en LibreOffice, sin macros ni funciones nuevas, con el archivo en español.

Preguntas frecuentes

¿Sirve la exportación de cualquier programa?

Sí, mientras tenga fecha, categoría, cantidad e importe. Los nombres de las columnas se ajustan en la hoja de parámetros.

¿Por qué mi total no coincide con el del sistema?

Suele deberse a importes pegados como texto, filas fuera del período o duplicados. La revisión de datos los localiza.

¿Hace falta una tabla dinámica?

No siempre. Las fórmulas con SUMAR.SI.CONJUNTO se actualizan solas al pegar, sin tener que refrescar nada.

¿Qué hago si tengo más de 5000 filas?

Divida la exportación por trimestre o por semestre y use un archivo por tramo.