Hacer un presupuesto es la parte fácil. Lo difícil llega a final de mes, cuando el gasto real no cuadra con lo previsto y toca explicar por qué. Una hoja de Excel bien montada te dice en segundos qué partidas se han desviado, cuánto y si la diferencia es preocupante o simplemente ruido.
En esta guía vas a preparar una tabla de presupuesto frente a real, calcular la desviación en importe y en porcentaje, resumir los gastos por partida con SUMAR.SI.CONJUNTO, resaltar de forma automática lo que supera un umbral y evitar los errores que más falsean el análisis.
Prepara la tabla: presupuesto y real lado a lado
El análisis de desviaciones solo funciona si cada partida aparece una sola vez y con el mismo nombre en el presupuesto y en el registro de gastos. Antes de escribir fórmulas, organiza la hoja así:
- En la columna A, el nombre de cada partida (Alquiler, Marketing, Suministros…), sin repeticiones.
- En la columna B, el importe presupuestado para el periodo.
- En la columna C, el gasto real del mismo periodo.
- Reserva las columnas D y E para la desviación en importe y en porcentaje.
- Selecciona el rango y pulsa Ctrl + T para convertirlo en tabla: así las fórmulas se copiarán solas a cada fila nueva.
Con datos de ejemplo, la hoja quedaría así:
| Partida | Presupuesto | Real | Desviación | Desviación % |
|---|---|---|---|---|
| Alquiler | 1.200 | 1.200 | 0 | 0 % |
| Marketing | 800 | 950 | 150 | 18,8 % |
| Suministros | 300 | 270 | -30 | -10 % |
| Viajes | 500 | 640 | 140 | 28 % |
| Formación | 400 | 120 | -280 | -70 % |
| Total | 3.200 | 3.180 | -20 | -0,6 % |
Fíjate en que el total casi cuadra (solo 20 por debajo), pero dentro hay partidas muy desviadas en los dos sentidos. Por eso nunca basta con mirar la última fila.
Calcula la desviación en importe y en porcentaje
Desviación absoluta
Es la diferencia entre lo que has gastado y lo que tenías previsto. En la celda D2 escribe:
=C2-B2
Con este criterio, un resultado positivo significa que has gastado más de lo presupuestado, y uno negativo, que has gastado menos. Lo importante es elegir un criterio y mantenerlo en todo el libro.
Desviación porcentual
El importe por sí solo engaña: 150 de más en una partida de 800 no pesa igual que 150 de más en una de 10.000. Para comparar partidas de distinto tamaño, divide la desviación entre el presupuesto. En E2:
=SI(B2=0;"";D2/B2)
La condición evita el error #¡DIV/0! cuando una partida no tiene presupuesto. Después aplica formato de porcentaje con Inicio › Estilo porcentual y añade un decimal si lo necesitas.
¿Favorable o desfavorable?
El signo no siempre significa lo mismo. Depende de si la fila es un gasto o un ingreso:
- Gastos: una desviación positiva es desfavorable (has gastado de más) y una negativa suele ser favorable, salvo que indique que algo previsto no se ha hecho.
- Ingresos: es al revés. Ingresar más de lo previsto es favorable; ingresar menos, desfavorable.
- Partidas sin presupuesto: cualquier gasto real es una desviación que hay que justificar, aunque el porcentaje no se pueda calcular.
Del detalle de gastos al resumen por partida
Lo normal es que el gasto real no llegue resumido, sino como una lista de movimientos: cada factura o pago en una fila. Supón una hoja llamada Gastos con la fecha en la columna A, la partida en la B, el importe en la C y el mes en la D. Para llevar el total real de cada partida a la tabla de análisis, escribe en C2:
=SUMAR.SI.CONJUNTO(Gastos!$C:$C;Gastos!$B:$B;$A2)
Si quieres analizar un mes concreto, escribe el mes en una celda de control, por ejemplo H1, y añade un segundo criterio:
=SUMAR.SI.CONJUNTO(Gastos!$C:$C;Gastos!$B:$B;$A2;Gastos!$D:$D;$H$1)
Al cambiar H1 de Enero a Febrero, toda la tabla se recalcula. SUMAR.SI.CONJUNTO existe desde Excel 2007, así que funciona en prácticamente cualquier versión, también en Excel para la web.
Para comprobar que no se queda nada fuera, suma la columna C de la tabla y compárala con la suma total del importe en la hoja Gastos. Si no coinciden, hay movimientos con una partida mal escrita o que no existe en el presupuesto.
Resalta las desviaciones que importan
No todas las desviaciones merecen una reunión. Define un umbral, por ejemplo un 10 % arriba o abajo, y deja que Excel clasifique cada partida. En F2:
=SI(E2="";"Sin presupuesto";SI(E2>0,1;"Revisar";SI(E2<-0,1;"Infrautilizado";"Correcto")))
Con los datos del ejemplo, Marketing (18,8 %) y Viajes (28 %) aparecen como Revisar, Formación (-70 %) como Infrautilizado y el resto como Correcto. Suministros, justo en -10 %, queda dentro del margen porque la comparación es estricta.
Para verlo de un vistazo, añade color:
- Selecciona los valores de la columna E, sin el encabezado.
- Ve a Inicio › Formato condicional › Reglas para resaltar celdas › Mayor que.
- Escribe 0,1 y elige un relleno rojo claro.
- Repite con Menor que, el valor -0,1 y un relleno amarillo, para detectar el gasto que no se ha ejecutado.
Errores habituales al comparar presupuesto y real
- Nombres de partida distintos: "Marketing" en el presupuesto y "Marketing " con un espacio final en los gastos no coinciden, y la suma devuelve 0. Limpia los textos con
ESPACIOSo usa una lista desplegable para escribir las partidas. - Periodos que no se corresponden: comparar el presupuesto anual con el gasto de tres meses produce desviaciones enormes y falsas. Prorratea el presupuesto o compara acumulado con acumulado.
- Importes guardados como texto: si los gastos vienen de un CSV o de un extracto bancario, algunos números pueden quedar como texto y
SUMAR.SI.CONJUNTOlos ignora. Comprueba que estén alineados a la derecha. - Movimientos duplicados: una factura registrada dos veces infla el gasto real. Antes del análisis, revisa que no haya filas repetidas.
- Fijarse solo en el total: como has visto en el ejemplo, un total casi perfecto puede esconder partidas muy desviadas que se compensan entre sí.
Compara versiones del presupuesto
El presupuesto también cambia: alguien ajusta una partida, se añade una línea nueva o se corrige un importe a mitad de ejercicio. Si trabajas con varias versiones del archivo, conviene saber exactamente qué se ha modificado antes de analizar desviaciones, porque parte de la diferencia puede deberse a que el presupuesto ya no es el mismo. Con nuestra herramienta gratuita para comparar archivos de Excel subes la versión anterior y la nueva y obtienes un archivo con los cambios. Y si el gasto real te llega repartido en un archivo por mes o por departamento, puedes combinar todos los archivos en uno antes de aplicar las fórmulas.
Conclusión
Analizar desviaciones no exige un programa financiero. Basta con una tabla bien estructurada, una resta, una división protegida contra el cero y una regla que marque lo que supera tu umbral. Lo que de verdad marca la diferencia es la disciplina: los mismos nombres de partida, los mismos periodos y un criterio de signo claro. Con eso, cada cierre de mes se convierte en una revisión de pocos minutos en lugar de una búsqueda a ciegas.
Preguntas frecuentes
¿Es mejor calcular la desviación como real menos presupuesto o al revés?
Las dos formas son válidas. Real menos presupuesto es la más intuitiva para gastos, porque un número positivo indica que te has pasado. Lo importante es usar siempre la misma y dejarla clara en el encabezado de la columna.
¿Qué umbral de desviación debería usar?
Depende del tamaño y la estabilidad de cada partida. Un margen del 5 al 10 % es habitual para gastos fijos y previsibles; en partidas variables puedes tolerar más. Combinar un porcentaje con un importe mínimo evita perder tiempo con diferencias pequeñas.
¿Cómo comparo el acumulado del año con el presupuesto?
Añade columnas de presupuesto acumulado y real acumulado hasta el mes actual y aplica sobre ellas las mismas fórmulas de desviación. Así, una partida que se adelantó un mes y se compensó en el siguiente no aparece como problema.
¿Por qué SUMAR.SI.CONJUNTO devuelve 0 en algunas partidas?
Casi siempre es porque el nombre de la partida no coincide exactamente entre las dos hojas (espacios sobrantes, tildes o una palabra distinta) o porque los importes están guardados como texto. Revisa ambos puntos antes de buscar otros fallos.
Prueba nuestras herramientas gratuitas
Compara, convierte, combina y limpia tus hojas de cálculo desde el navegador, sin instalar nada.
