Resumen
Para calcular el pronóstico frente a la varianza real en función de un conjunto de datos, puede usar la función SUMIFS para recopilar totales y otras fórmulas básicas para calcular la varianza y el porcentaje de varianza. En el ejemplo que se muestra, la fórmula en G5 es:
=SUMIFS(amount,type,G$4,group,$F5)
donde cantidad es el rango con nombre C5: C14, y tipo es el rango con nombre D5: D14, y grupo es el rango con nombre B5: B14.
Explicación
Este es un uso bastante estándar de la función SUMIFS. En este caso, necesitamos sumar los importes en función de dos criterios: tipo (previsto o real) y grupo. Para sumar por tipo, el par rango / criterio es:
type,G$4
donde tipo es el rango con nombre D5: D14, y G4 es una referencia mixta con la fila bloqueada para que coincida con el encabezado de la columna en la fila 4 cuando se copia la fórmula.
Para sumar por grupo, el par rango / criterio es:
group,$F5
donde grupo es el rango con nombre B5: B14 y F5 es una referencia mixta con la columna bloqueada para hacer coincidir los nombres de grupo en la columna F cuando se copia la fórmula.
Fórmulas de varianza
La fórmula de varianza en la columna I simplemente resta el pronóstico del real:
=G5-H5
La fórmula del porcentaje de varianza en la columna J es:
=(G5-H5)/H5
con formato de porcentaje aplicado.
Notas
- Los datos que se muestran aquí funcionarían bien en una tabla de Excel, que se expandiría automáticamente para incluir nuevos datos. Estamos usando rangos con nombre aquí para mantener las fórmulas lo más simples posible.
- Las tablas dinámicas también se pueden utilizar para calcular la varianza. Las fórmulas proporcionan más flexibilidad y control a costa de una mayor complejidad.
