Excel pour les Contrôleurs de gestion
Les formules et techniques Excel pour construire tes budgets, analyser les écarts et piloter la performance (2026)
En contrôle de gestion, Excel est ton atelier de travail principal et ton outil le plus polyvalent. Budgets prévisionnels par centre de coût, suivi des écarts mensuels, reporting de clôture, calcul des coûts de revient, tableaux de bord de direction, prévisions de trésorerie : tout passe par Excel. Que tu travailles dans une ETI industrielle avec 20 centres de coût ou dans une filiale de groupe avec un reporting consolidé, c'est dans Excel que tu construis les analyses ad hoc et les simulations que le DAF te demande pour demain matin, même quand tu as un ERP ou un outil BI.
Le contrôleur de gestion est probablement le métier qui pousse Excel le plus loin. Tu manipules des tableaux à 50 colonnes et 10 000 lignes, tu croises des données de 10 sources différentes (ERP, paie, CRM, comptabilité), tu construis des modèles de simulation avec des scénarios multiples pour anticiper l'impact d'une hausse des matières premières ou d'une réduction d'effectif. Les formules basiques ne suffisent plus. Un CDG passe en moyenne 60% de son temps sur Excel, et la différence entre un fichier bien construit et un fichier artisanal, c'est 2 jours de travail gagnés à chaque clôture mensuelle.
Ce guide te présente les 10 formules les plus utiles en contrôle de gestion, avec des exemples concrets tirés de la vraie vie du CDG : construction budgétaire avec ventilation par centre de coût et par nature de charge, analyse des écarts budget/réalisé avec seuils d'alerte, calcul de coûts de revient pondérés, et reporting de direction avec des montants qui s'additionnent correctement. Chaque formule est illustrée avec des données réalistes : codes de centres analytiques, natures de charges, montants mensuels et écarts en pourcentage.
Les 10 formules indispensables pour les Contrôleurs de gestion
1. SOMME.SI.ENS - Totaliser par centre de coût, nature et période
SOMME.SI.ENS est la formule que tu utilises 50 fois par jour en contrôle de gestion. Elle totalise les montants par centre de coût + nature de charge + période. Budget réalisé sur le centre 'Production' pour les charges de personnel en mars ? Une seule formule au lieu de filtrer, additionner, noter. C'est la base de tout ton reporting : tu construis ton tableau budget vs réalisé complet en enchaînant des SOMME.SI.ENS avec des critères dynamiques (mois en colonne, centre en ligne). Sans cette formule, chaque clôture mensuelle te prend 2 jours de plus. Astuce : utilise des références de cellule pour les critères et crée un tableau croisé qui se met à jour en changeant le mois dans une cellule.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Code | Centre | Nature | Mois | Montant |
| 2 | CC01 | Production | Personnel | Mars | 45 000 |
| 3 | CC01 | Production | Matières | Mars | 28 000 |
| 4 | CC02 | Commercial | Personnel | Mars | 32 000 |
| 5 | CC02 | Commercial | Déplacements | Mars | 8 500 |
| 6 | CC03 | R&D | Personnel | Mars | 52 000 |
| 7 | CC03 | R&D | Sous-traitance | Mars | 18 000 |
| 8 | CC01 | Production | Énergie | Mars | 12 500 |
| 9 | CC04 | Administratif | Personnel | Mars | 38 000 |
| 10 | CC04 | Administratif | Fournitures | Mars | 3 200 |
=SOMME.SI.ENS(E2:E10;B2:B10;"Production";C2:C10;"Personnel";D2:D10;"Mars")2. SOMMEPROD - Calculs pondérés et conditions complexes
SOMMEPROD est l'arme secrète du contrôleur de gestion. Elle multiplie des plages et additionne les résultats, ce qui permet des calculs pondérés impossibles avec SOMME.SI.ENS : marge pondérée par le volume, coût standard multiplié par les quantités réelles, taux de charge pondéré par les effectifs. Par exemple, pour calculer le coût de production total, tu multiplies chaque quantité par son coût unitaire et SOMMEPROD fait la somme. Sans cette formule, tu crées une colonne intermédiaire (quantité * coût) puis tu additionnes. SOMMEPROD fait les deux opérations en une seule formule. Astuce : utilise des conditions dans SOMMEPROD pour des calculs conditionnels complexes : =SOMMEPROD((centre="Production")*quantité*coût).
| A | B | C | |
|---|---|---|---|
| 1 | Produit | Quantité | Coût unitaire |
| 2 | Produit A | 500 | 42 |
| 3 | Produit B | 300 | 85 |
| 4 | Produit C | 200 | 40 |
| 5 | Produit D | 150 | 120 |
| 6 | Produit E | 800 | 28 |
| 7 | Produit F | 50 | 210 |
| 8 | Produit G | 400 | 35 |
| 9 | Produit H | 250 | 65 |
| 10 | Produit I | 100 | 95 |
=SOMMEPROD(B2:B10;C2:C10)3. INDEX - Extraire des données dans un modèle budgétaire
INDEX combinée avec EQUIV te permet de naviguer dans des tableaux budgétaires complexes, quelle que soit leur structure. Tu cherches le montant budgété pour le centre Production, au mois de mars, pour les charges de personnel ? INDEX/EQUIV le fait en une formule. C'est particulièrement puissant pour les grilles budgétaires avec les mois en colonnes et les centres en lignes : EQUIV localise la bonne ligne et la bonne colonne, INDEX extrait le montant au croisement. Sans INDEX/EQUIV, tu dois connaître le numéro de ligne et de colonne par coeur. Astuce : cette combinaison rend tes tableaux de synthèse dynamiques : tu changes le mois dans une cellule, et tous les montants se mettent à jour.
4. EQUIV - Trouver la position dans les grilles budgétaires
EQUIV localise un centre de coût, un mois ou une nature de charge dans tes grilles budgétaires. Combinée avec INDEX, elle forme le duo indispensable pour construire des tableaux de synthèse qui se mettent à jour quand tu changes de période ou de périmètre. En contrôle de gestion, tes tableaux ont souvent les mois en colonnes (janvier à décembre) et les centres de coût en lignes. EQUIV transforme le nom du mois en numéro de colonne, ce qui rend tes formules de reporting indépendantes de la position des données. Astuce : utilise EQUIV avec le type de correspondance 0 (exact) pour éviter les résultats approximatifs qui peuvent fausser un reporting financier.
5. SI - Alertes sur les écarts budget/réalisé
SI est ta formule d'alerte en contrôle de gestion. Écart défavorable de plus de 10% ? "Alerte". Marge inférieure au seuil de rentabilité ? "À investiguer". Consommation du budget supérieure à 90% au 20 du mois ? "Quasi épuisé". Tu paramètres tes seuils une seule fois et le fichier surveille les dérives pour toi sur l'ensemble des centres de coût. Imbrique plusieurs SI pour créer des niveaux d'alerte : écart < 5% = OK, entre 5% et 10% = à surveiller, > 10% = alerte. Combinée avec la MFC, les lignes en dépassement sautent aux yeux du DAF dès l'ouverture du fichier. Astuce : utilise ABS pour détecter les écarts significatifs dans les deux sens (favorable et défavorable).
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Poste | Budget | Réalisé | Écart % |
| 2 | Personnel | 100 000 | 95 000 | -5% |
| 3 | Matières premières | 50 000 | 62 000 | +24% |
| 4 | Énergie | 15 000 | 16 200 | +8% |
| 5 | Sous-traitance | 30 000 | 28 500 | -5% |
| 6 | Déplacements | 12 000 | 14 800 | +23% |
| 7 | Fournitures | 8 000 | 7 600 | -5% |
| 8 | Maintenance | 20 000 | 22 100 | +10,5% |
| 9 | Formation | 10 000 | 4 500 | -55% |
=SI(ABS((C2-B2)/B2)>0.1;"Écart significatif";"OK")6. RECHERCHEV - Récupérer les données de référence
RECHERCHEV te permet de retrouver un libellé de compte, un taux de répartition ou un prix standard à partir d'un code analytique. Quand tu consolides des données de l'ERP avec des codes centres (CC01, CC02, CC03), RECHERCHEV fait le lien avec la table de référence pour afficher le nom complet, le responsable ou le budget annuel. Par exemple, ton export SAP contient des codes nature de charge (6011, 6061, 6211), et RECHERCHEV va chercher le libellé correspondant dans ton plan comptable. Sans cette formule, tu retapes les libellés à chaque clôture. Astuce : crée un onglet 'Référentiels' avec toutes tes tables de correspondance (centres, natures, produits).
7. ARRONDI - Des montants propres dans les reportings
ARRONDI garantit des montants cohérents dans tes reportings financiers. Les calculs de répartition analytique (clés de répartition à 4 décimales) génèrent des centimes, les pourcentages de marge produisent des décimales infinies, et les prorata temporis créent des montants à 6 chiffres après la virgule. ARRONDI te permet de présenter des chiffres propres qui s'additionnent correctement. Sans ARRONDI, tu risques un écart de 0,01 euro entre ton total et la somme des lignes, ce qui pose un problème en contrôle de gestion. Astuce : arrondis au millier avec =ARRONDI(montant;-3) pour les présentations de direction où le détail au centime n'est pas nécessaire.
8. NB.SI.ENS - Compter les lignes par critères multiples
NB.SI.ENS compte les lignes qui respectent plusieurs conditions simultanément. Combien d'écritures dépassent 10 000 euros sur le centre Production en mars ? Combien de postes de coût sont en dépassement budgétaire de plus de 10% ? Combien de centres ont un taux de consommation supérieur à 90% au 20 du mois ? C'est ta formule de contrôle pour repérer les anomalies et les dérives. En contrôle de gestion, NB.SI.ENS te permet aussi de vérifier la qualité des données : combien de lignes ont un centre de coût vide, un montant négatif, ou une date hors période ? Astuce : =NB.SI.ENS(écart;">10%") te donne le nombre de postes en dépassement significatif en une cellule.
9. SOMME.SI - Totaux rapides par critère unique
SOMME.SI est la version simple de SOMME.SI.ENS quand un seul critère suffit. Total par centre de coût, total par nature de charge, total par mois. Plus rapide à écrire que SOMME.SI.ENS quand tu n'as besoin que d'un seul filtre. Par exemple, le total des charges de personnel tous centres confondus : =SOMME.SI(nature;"Personnel";montant). En contrôle de gestion, SOMME.SI est souvent utilisée pour les sous-totaux par nature dans les tableaux de synthèse simples. Astuce : SOMME.SI est aussi plus rapide en calcul que SOMME.SI.ENS sur les très gros fichiers (100 000+ lignes), un avantage non négligeable en clôture.
10. MOYENNE - Coût moyen, marge moyenne, écart moyen
MOYENNE te donne le coût unitaire moyen, la marge moyenne par produit ou l'écart budgétaire moyen par centre de coût. Par exemple, l'écart moyen tous centres confondus est de +7% : c'est l'indicateur synthétique que le DAF veut voir en comité de direction. Combinée avec MOYENNE.SI, elle te permet de calculer des moyennes par catégorie sans TCD : la marge moyenne des produits de la gamme Premium, ou le coût moyen par unité produite sur les 6 derniers mois. Astuce : compare MOYENNE et MEDIANE pour détecter les valeurs extrêmes qui tirent la moyenne vers le haut ou vers le bas.
Ta fiche mémo est prête
Nous avons résumé les formules et raccourcis essentiels aux Contrôleurs de gestion dans 1 PDF. Imprime-le et garde-le à côté de ton écran !
Télécharger le PDF gratuitLes fonctionnalités Excel clés pour les Contrôleurs de gestion
Tableaux Croisés Dynamiques (TCD)
Les TCD sont ton outil de reporting numéro 1 en contrôle de gestion. Croise les données par centre de coût, nature de charge, période et produit en quelques clics. Ajoute des champs calculés pour les écarts et les pourcentages, des regroupements par trimestre ou par semestre, et des segments pour filtrer interactivement par périmètre. Un seul TCD remplace 50 formules SOMME.SI.ENS et se met à jour en un clic à chaque clôture.
Power Query
Importe et consolide automatiquement les exports de ton ERP (SAP, Sage, Oracle). Nettoie les données, fais les jointures entre tables analytiques et opérationnelles, standardise les formats de date et de montant, et automatise le refresh. Le processus de clôture passe de 3 heures de copier-coller à 5 minutes de refresh. Tu gagnes des heures chaque mois et tu réduis les risques d'erreur humaine.
Scénarios et simulations
Utilise le Gestionnaire de scénarios ou les tables de données pour simuler plusieurs hypothèses budgétaires. Que se passe-t-il si les matières premières augmentent de 15% ? Si l'effectif baisse de 3 personnes ? Si le prix de vente augmente de 5% ? Excel calcule chaque scénario en un clic. Le Solveur te permet aussi d'optimiser une variable (maximiser la marge) sous contraintes (budget limité, capacité de production), un outil puissant pour les études de rentabilité.
Graphiques de variances
Construis des graphiques en cascade (waterfall) pour expliquer visuellement les écarts entre le budget et le réalisé. Le DAF voit immédiatement quels postes ont dérivé et dans quelle proportion. C'est le format préféré des directeurs financiers en comité de direction. Ajoute des graphiques en barres empilées pour comparer budget vs réalisé par centre, et des courbes pour suivre l'évolution mensuelle des charges.
Comparaisons de formules
RECHERCHEV vs RECHERCHEX
RECHERCHEX remplace RECHERCHEV mais n'est pas disponible partout.
INDEX vs RECHERCHEV
INDEX/EQUIV est plus flexible que RECHERCHEV, mais plus complexe à écrire.
SI vs SI.CONDITIONS
SI.CONDITIONS remplace les SI imbriqués par une syntaxe plus lisible.
NB.SI vs NB.SI.ENS
NB.SI.ENS gère plusieurs critères là où NB.SI n'en accepte qu'un seul.
SOMME.SI vs SOMME.SI.ENS
SOMME.SI.ENS permet de sommer avec plusieurs critères simultanément.
SOMME.SI vs NB.SI
SOMME.SI additionne des valeurs selon un critère, NB.SI se contente de les compter.
MOYENNE vs MOYENNE.SI
MOYENNE calcule la moyenne de tout, MOYENNE.SI ne prend que les valeurs qui correspondent à un critère.
EQUIV vs CHERCHE
EQUIV trouve la position d'une valeur dans une plage, CHERCHE trouve la position d'un texte dans une chaine.
EXACT vs SI
EXACT compare deux textes en respectant la casse, SI teste une condition et renvoie un résultat.
Questions fréquentes
Quelles formules Excel sont indispensables en contrôle de gestion ?
Les 5 clés : SOMME.SI.ENS (totaux multi-critères), SOMMEPROD (calculs pondérés), INDEX/EQUIV (navigation dans les modèles), SI (alertes sur les écarts) et ARRONDI (montants propres). Ajoute NB.SI.ENS pour les contrôles de cohérence et tu couvres 90% des besoins quotidiens.
Comment construire un budget dans Excel ?
Crée un modèle avec un onglet par centre de coût (ou un onglet unique avec un code centre). Colonnes : nature de charge, budget N-1, budget N, réalisé M, réalisé cumul, écart. Utilise SOMME.SI.ENS pour les totaux par critère. Ajoute un onglet de synthèse consolidé avec des formules qui pointent vers les onglets détaillés.
Comment analyser les écarts budget/réalisé dans Excel ?
Calcule l'écart absolu (=réalisé - budget) et l'écart relatif (=(réalisé-budget)/budget). Utilise SI pour catégoriser : favorable/défavorable, significatif/non significatif. Ajoute la MFC pour colorer les écarts. Un graphique en cascade (waterfall) explique visuellement les dérives du résultat.
Excel ou outil BI (Power BI, Tableau) en contrôle de gestion ?
Les deux sont complémentaires. Excel est imbattable pour la construction de modèles, les simulations et les analyses ad hoc. Power BI est meilleur pour les dashboards interactifs consultés par beaucoup de monde. En pratique, tu construis ton modèle dans Excel et tu publies les résultats dans Power BI.
Comment automatiser la clôture mensuelle dans Excel ?
Utilise Power Query pour importer automatiquement les exports de l'ERP (balance, écritures analytiques, données de paie). Définis les étapes de transformation une seule fois (nettoyage, jointures, calculs). Chaque mois, tu mets à jour les fichiers source et tu cliques sur 'Actualiser tout'. Les TCD et les formules se recalculent automatiquement. Le reporting mensuel qui prenait 2 jours passe à 2 heures, dont la majeure partie est consacrée à l'analyse, pas à la mise en forme.
Comment calculer un coût de revient dans Excel ?
Crée un modèle avec les composantes du coût : matières premières (SOMMEPROD des quantités par les prix unitaires), main-d'oeuvre directe (heures par taux horaire chargé), charges indirectes (réparties par clés analytiques). Utilise SOMME.SI.ENS pour ventiler les charges par produit et par nature. ARRONDI garantit des montants cohérents. Ajoute un onglet de simulation pour tester l'impact d'une variation de prix ou de volume sur le coût de revient.
Comment présenter un reporting financier clair dans Excel ?
Structure ton reporting en 3 niveaux : synthèse (une page avec les KPI clés et les écarts majeurs), détail par centre de coût (un onglet par centre ou un TCD filtrable), et annexes (données brutes). Utilise la MFC pour colorer les écarts significatifs. Les montants doivent être arrondis au millier pour la synthèse direction. Ajoute un graphique en cascade pour les écarts et un graphique en courbes pour les tendances mensuelles. Le reporting doit raconter une histoire, pas noyer sous les chiffres.
Découvre aussi Excel pour les...
Envie de maîtriser Excel en contrôle de gestion ?
Rejoins Le Dojo Club pour accéder à des formations complètes, des templates budgétaires et une communauté de contrôleurs de gestion qui partagent leurs méthodes.
Essayer pendant 30 jours