Sommaire
Pour calculer la prévision par rapport à la variance réelle en fonction d'un ensemble de données, vous pouvez utiliser la fonction SUMIFS pour rassembler les totaux et d'autres formules de base pour calculer la variance et le pourcentage de variance. Dans l'exemple illustré, la formule en G5 est:
=SUMIFS(amount,type,G$4,group,$F5)
où montant est la plage nommée C5: C14 et type est la plage nommée D5: D14, et group est la plage nommée B5: B14.
Explication
Il s'agit d'une utilisation assez standard de la fonction SUMIFS. Dans ce cas, nous devons additionner les montants en fonction de deux critères: le type (prévu ou réel) et le groupe. Pour additionner par type, la paire plage / critères est:
type,G$4
où type est la plage nommée D5: D14 et G4 est une référence mixte avec la ligne verrouillée afin de correspondre à l'en-tête de colonne de la ligne 4 lorsque la formule est copiée vers le bas.
Pour additionner par groupe, la paire plage / critères est:
group,$F5
où groupe est la plage nommée B5: B14 et F5 est une référence mixte avec la colonne verrouillée afin de faire correspondre les noms de groupe dans la colonne F lorsque la formule est copiée.
Formules de variance
La formule de variance de la colonne I soustrait simplement la prévision du réel:
=G5-H5
La formule de pourcentage de variance dans la colonne J est:
=(G5-H5)/H5
avec le format de nombre de pourcentage appliqué.
Remarques
- Les données présentées ici fonctionneraient bien dans un tableau Excel, qui se développerait automatiquement pour inclure de nouvelles données. Nous utilisons ici des plages nommées pour garder les formules aussi simples que possible.
- Les tableaux croisés dynamiques peuvent également être utilisés pour calculer la variance. Les formules offrent plus de flexibilité et de contrôle au prix d'une plus grande complexité.
