Excel pour le contrôle de gestion : 10 formules à connaître absolument
Recommandés
Excel reste l’outil incontournable pour piloter la performance financière et opérationnelle d’une organisation. Que l’on travaille dans une direction financière, un service de planification budgétaire ou un service de reporting, la maîtrise des formules Excel est essentielle pour produire des analyses fiables et gagner en efficacité.
Voici 10 formules Excel que tout professionnel du pilotage et de l’analyse de la performance devrait maîtriser :
1. SOMME()
Additionne une série de valeurs rapidement.
Utilité : Parfait pour consolider les dépenses, recettes ou écarts budgétaires.
=SOMME(B2:B10)
2. MOYENNE()
Calcule la moyenne d’un ensemble de données.
Utilité : Permet d’estimer les performances moyennes, par exemple d’un centre de coût.
=MOYENNE(C2:C12)
3. SI()
Crée une logique conditionnelle.
Utilité : Indispensable pour simuler des scénarios ou générer des alertes (ex. : dépassement budgétaire).
=SI(D2>E2;"Dépassement";"OK")
4. RECHERCHEV() / RECHERCHEX()
Recherche une valeur dans une table et renvoie un résultat associé.
Utilité : Utile pour croiser les données de différents tableaux (ex. : rattacher un code analytique à un libellé).
=RECHERCHEV(A2;Feuil2!A:B;2;FAUX)
5. NB.SI()
Compte combien de fois une valeur apparaît.
Utilité : Idéal pour suivre la fréquence d’apparition d’une dépense ou d’un code analytique.
=NB.SI(B2:B100;"Voyage")
6. SOMME.SI()
Additionne des valeurs en fonction d’un critère.
Utilité : Sert à calculer des sous-totaux ciblés (ex. : total des charges pour un département).
=SOMME.SI(A2:A100;"Marketing";C2:C100)
7. INDEX() + EQUIV()
Recherche avancée combinée, plus souple que RECHERCHEV.
Utilité : Puissant pour construire des tableaux dynamiques et éviter les limites de RECHERCHEV.
=INDEX(B2:B10;EQUIV("Code123";A2:A10;0))
8. DECALER()
Référence dynamique à une cellule décalée.
Utilité : Très utilisé dans les tableaux de bord automatisés ou les modèles prévisionnels glissants.
=DECALER(A1;3;1)
9. NBVAL()
Compte le nombre de cellules non vides.
Utilité : Permet de mesurer la complétude des données ou le nombre d’éléments à analyser.
=NBVAL(D2:D100)
10. ARRONDI()
Arrondit un nombre à un certain nombre de décimales.
Utilité : Pratique pour la présentation des données financières.
=ARRONDI(C2;0)
⬇️
Exploiter pleinement Excel dans les missions de contrôle de gestion
Enfin, outre les formules de base, l’approche à Excel repose sur un processus systématique et méthodique précis. Aujourd’hui, le contrôleur de gestion est obligé de se transformer pour devenir de plus en plus un visionnaire, un innovateur, un expert qui doit produire des prévisions et des modèles prédictifs, calculer divers scénarios, puis préparer des tableaux de bord pour toutes les parties prenantes.
🔍 1. Structurer ses fichiers comme un professionnel
Un bon fichier Excel de gestion commence par une structure claire :
- Feuilles séparées pour les données brutes, les traitements et les restitutions.
- Nommer les plages (via les plages nommées) pour simplifier les formules.
- Verrouiller certaines cellules pour éviter les erreurs de manipulation.
2. Construire des tableaux de bord dynamiques
Les outils comme les Tableaux Croisés Dynamiques ou les segments (slicers) permettent de :
- Filtrer les données rapidement,
- Suivre les écarts budgétaires par axe (mois, département, produit…),
- Visualiser les tendances via des graphiques dynamiques.
3. Intégrer des visualisations claires
Des graphes bien choisis (courbes, barres, thermomètres…) rendent les présentations plus compréhensibles :
- Utilisez la mise en forme conditionnelle pour faire ressortir les dérives.
- Adoptez une charte graphique simple pour une meilleure lisibilité.
⚙️ 4. Automatiser avec Power Query ou VBA
Pour gagner du temps dans le traitement récurrent de données :
- Power Query permet d’importer, transformer et consolider des données automatiquement.
- VBA (Visual Basic for Applications) peut automatiser des tâches comme l’envoi d’un reporting par mail ou l’actualisation de plusieurs feuilles en un clic.
Astuces pour progresser
- Reproduisez vos propres modèles (budgets, suivis d’activité, simulations).
- Utilisez les raccourcis clavier Excel pour aller plus vite.
- Participez à des formations avancées ou suivez des tutoriels sur YouTube et LinkedIn Learning.
🔢 Formules avancées à connaître absolument
1. SOMMEPROD() — Calcul conditionnel avec plusieurs critères
Permet d’effectuer des somme pondérées ou conditionnelles sans passer par une formule matricielle.
👉 Exemple : Calculer le total des ventes où le produit est « A » et la région est « Nord » :
=SOMMEPROD((A2:A100="A")*(B2:B100="Nord")*(C2:C100))
2. SI.CONDITIONS() — Alternative puissante aux SI imbriqués
Simplifie la gestion de plusieurs conditions logiques dans une seule formule.
👉 Exemple : Afficher un commentaire selon le taux de marge :
=SI.CONDITIONS(D2>0, "Rentable", D2=0, "Équilibre", D2<0, "Perte")
3. XLOOKUP() (RECHERCHEX) — Remplace RECHERCHEV et RECHERCHEH
Plus flexible, recherche dans toutes les directions avec un résultat par défaut.
👉 Exemple : Trouver le coût d’un code produit :
=XLOOKUP("P123";A2:A100;B2:B100;"Non trouvé")
4. FILTRE() — Extraire dynamiquement des données filtrées
Permet de filtrer automatiquement une table selon un ou plusieurs critères.
👉 Exemple : Extraire toutes les lignes dont le département est « Finance » :
=FILTRE(A2:C100;B2:B100="Finance")
5. NB.SI.ENS() — Comptage conditionnel multicritères
Idéal pour compter des occurrences selon plusieurs conditions croisées.
👉 Exemple : Compter les lignes dont le statut est « Clos » et la date est en 2025 :
=NB.SI.ENS(B2:B100;"Clos";C2:C100;">=01/01/2025";C2:C100;"<=31/12/2025")
6. SOMME.SI.ENS() — Somme multicritères
Parfait pour faire une somme croisée, par exemple pour un centre de coûts et une période.
👉 Exemple : Somme des montants pour le centre « MKT » et le mois de janvier :
=SOMME.SI.ENS(C2:C100;A2:A100;"MKT";B2:B100;"Janvier")
7. DECALER() + NBVAL() — Gammes dynamiques automatiques
Utile pour créer des plages évolutives dans les graphiques ou les moyennes mobiles.
👉 Exemple : Moyenne sur toutes les valeurs remplies d’une colonne :
=MOYENNE(DECALER(B2;0;0;NBVAL(B2:B100)))
8. INDEX() + EQUIV() — Recherche croisée très performante
Évite les limites de RECHERCHEV, notamment quand la colonne de recherche est à droite.
👉 Exemple : Trouver la quantité correspondant à un produit :
=INDEX(C2:C100;EQUIV("Produit X";A2:A100;0))
9. TEXTE() — Mise en forme personnalisée des dates, montants, etc.
Permet de contrôler la présentation dans des dashboards ou des exports.
👉 Exemple : Afficher une date en format texte :
=TEXTE(A2;"mmmm aaaa") → "juillet 2025"
10. LET() — Améliorer lisibilité et performance
Définit des variables dans la formule, plus propre et plus rapide sur les gros modèles.
👉 Exemple : Calcul d’une marge avec variable de coût :
=LET(coût;B2;revenu;C2;(revenu-coût)/revenu)
Recommandés