Excel : budget prévisionnel vs réalisé par mois
Créez dans Excel un suivi de budget mensuel comparant prévisionnel et réalisé avec SOMME.SI.ENS, RECHERCHEX et alertes d’écart visuelles.
- Difficulté
- Intermédiaire
Ce tutoriel montre comment construire dans Excel un suivi de budget qui compare, mois par mois, le prévisionnel et le réalisé. Vous allez créer une base d’écritures, une table de budget, puis une synthèse lisible avec des formules robustes.
L’objectif est simple : savoir rapidement où vous êtes conforme au budget, où vous dépassez, et où vous sous-consommez. Le tout repose sur des fonctions courantes d’Excel et sur une mise en forme conditionnelle facile à maintenir.
Prérequis
Préparez un fichier Excel avec au moins une feuille vide pour les écritures, une feuille pour le budget et une feuille pour la synthèse. Il est préférable de connaître les bases des tableaux Excel et la recopie de formules.
Les fonctions utilisées ici sont SOMME.SI.ENS et RECHERCHEX. Si votre version d’Excel ne propose pas RECHERCHEX, il faudra adapter avec une autre méthode de recherche.
Matériel nécessaire
- Un fichier Excel avec trois feuilles
- Une liste de catégories budgétaires
- Des écritures datées avec montants réalisés
Étapes
Créer la table des écritures
Dans une première feuille, saisissez vos dépenses et revenus réalisés sous forme de colonnes claires : Date, Mois, Catégorie et Montant. Convertissez cette plage en tableau Excel avec Insertion > Tableau pour faciliter les formules et les ajouts futurs.
Dans la colonne Mois, stockez une valeur mensuelle cohérente issue de la date, par exemple le premier jour du mois correspondant. Garder un vrai format de date mensuelle évite les comparaisons fragiles entre textes.
Saisir le budget prévisionnel par catégorie et par mois
Dans une deuxième feuille, créez une grille de budget avec une ligne par catégorie et une colonne par mois. Placez la catégorie dans la première colonne, puis les mois sur la première ligne, toujours avec de vraies dates mensuelles.
Convertissez aussi cette plage en tableau si votre structure s’y prête, ou gardez une plage propre et compacte. L’essentiel est d’avoir une correspondance claire entre une catégorie et chaque montant prévisionnel mensuel.
Monter la grille de synthèse mensuelle
Dans une troisième feuille, créez votre tableau de pilotage. Placez en colonnes : Mois, Catégorie, Prévisionnel, Réalisé, Écart et éventuellement % d’écart.
Renseignez les mois ligne par ligne, puis répétez les catégories pour chaque mois que vous souhaitez suivre. Cette structure longue est très pratique pour filtrer, trier et appliquer des règles d’alerte.
Rapatrier le prévisionnel avec RECHERCHEX
Dans la colonne Prévisionnel de la synthèse, utilisez RECHERCHEX pour retrouver le bon montant dans votre table de budget. La logique consiste à retrouver la ligne de la catégorie, puis à récupérer la valeur dans la colonne du mois visé.
Une approche simple consiste à préparer dans la feuille budget une table structurée de type Catégorie | Mois | Prévisionnel. Vous pouvez alors rechercher une clé composée, par exemple catégorie + mois, afin de renvoyer le montant prévu dans la synthèse.
La formule dépend de votre organisation exacte, mais le principe reste le même : faire correspondre la catégorie et le mois de la ligne courante avec la source budgétaire. Testez la formule sur quelques lignes avant de la recopier partout.
Calculer le réalisé avec SOMME.SI.ENS
Dans la colonne Réalisé, utilisez SOMME.SI.ENS pour additionner les montants de la table d’écritures selon deux critères : le mois et la catégorie. Cette formule est adaptée dès que plusieurs écritures doivent être regroupées dans une seule ligne de synthèse.
Le schéma est : somme des montants de tEcritures quand la colonne Mois est égale au mois de la ligne et que la colonne Catégorie est égale à la catégorie de la ligne. Recopiez ensuite la formule sur toutes les lignes de la synthèse.
Si vous avez des revenus et des dépenses dans la même base, gardez une convention de signe stable. Votre lecture des écarts sera plus claire si tous les montants comparés suivent la même logique.
Calculer l’écart et le pourcentage d’écart
Dans la colonne Écart, soustrayez le prévisionnel du réalisé. Vous obtenez immédiatement la différence brute entre ce qui était prévu et ce qui s’est réellement passé.
Ajoutez ensuite une colonne % d’écart si vous voulez relativiser la différence. Divisez l’écart par le prévisionnel, puis appliquez un format pourcentage.
Gérez le cas d’un prévisionnel nul avec un test simple pour éviter une division par zéro. Mieux vaut afficher une cellule vide qu’une erreur difficile à interpréter.
Appliquer les alertes en mise en forme conditionnelle
Sélectionnez la colonne Écart, puis créez des règles de mise en forme conditionnelle. Par exemple, colorez en rouge les écarts supérieurs à 0 si un dépassement signifie une dépense plus forte que prévu, et en vert les écarts inférieurs ou égaux à 0.
Vous pouvez aussi appliquer une règle sur le % d’écart pour faire ressortir les dépassements les plus marqués. Une deuxième couche d’alerte rend le tableau plus utile quand les montants absolus varient beaucoup selon les catégories.
Testez vos règles sur quelques lignes avant de les étendre à toute la plage. Vérifiez surtout que la formule ou le seuil s’applique correctement à chaque ligne de votre tableau.
Filtrer les écarts prioritaires
Activez les filtres sur la synthèse pour afficher uniquement les écarts positifs, les écarts supérieurs à un seuil, ou un mois précis. En quelques clics, vous obtenez une vue de contrôle orientée action.
Vous pouvez aussi trier la colonne % d’écart du plus fort au plus faible. Cela aide à traiter d’abord les lignes les plus sensibles, au lieu de parcourir tout le tableau visuellement.
Contrôler les résultats avant diffusion
Comparez quelques lignes de la synthèse avec les données sources pour valider vos formules. Vérifiez au minimum un mois, une catégorie avec plusieurs écritures et une catégorie sans écriture.
Contrôlez aussi la cohérence des totaux mensuels. Si un montant paraît anormal, remontez à la base d’écritures avant de corriger la formule au hasard.
Une fois la validation faite, figez la ligne d’en-tête et ajustez les formats de date, de devise et de pourcentage. Votre tableau sera plus lisible pour vous comme pour un collègue.
Erreurs fréquentes & dépannage
La première erreur fréquente consiste à utiliser des mois affichés pareil mais stockés différemment, par exemple texte d’un côté et date de l’autre. RECHERCHEX et SOMME.SI.ENS dépendent de correspondances exactes ou cohérentes.
Autre piège courant : additionner sur une base dont les catégories ne sont pas homogènes. Une différence minime de libellé suffit à exclure des écritures du réalisé.
Enfin, beaucoup de tableaux deviennent fragiles parce que la source n’est pas convertie en tableau Excel. Les nouvelles lignes risquent alors de ne pas entrer dans les calculs ni dans les alertes.
Astuces & pour aller plus loin
Si votre budget initial est aujourd’hui en grille croisée, envisagez de le dupliquer dans un format plus “long” avec une ligne par couple Catégorie + Mois. Ce format simplifie souvent les recherches et les contrôles.
Gardez une feuille dédiée aux listes de référence, notamment les catégories. Vous limiterez les variations de saisie qui faussent les regroupements.
Ajoutez enfin une petite zone de contrôle avec quelques indicateurs simples, comme le total prévisionnel du mois et le total réalisé du mois. Ces repères permettent de détecter rapidement une anomalie de structure.
Questions fréquentes
Pourquoi utiliser SOMME.SI.ENS au lieu d’une somme simple ?
Parce que le réalisé doit regrouper plusieurs écritures en fonction de plusieurs critères, ici au minimum le mois et la catégorie. Une somme simple ne permet pas ce filtrage.
À quoi sert RECHERCHEX dans ce suivi de budget ?
RECHERCHEX sert à rapatrier le montant prévisionnel correspondant à une catégorie et à un mois donnés. Il évite de ressaisir le budget dans la feuille de synthèse.
Puis-je me contenter d’un écart en valeur sans pourcentage ?
Oui, si votre lecture repose surtout sur les montants absolus. Le pourcentage devient utile quand vous comparez des catégories de tailles très différentes.
Comment éviter les erreurs quand j’ajoute de nouvelles écritures ?
Travaillez avec un tableau Excel pour la base d’écritures et conservez les mêmes colonnes de référence. Les formules de synthèse s’adapteront plus facilement aux nouvelles lignes.
Conclusion
Vous disposez maintenant d’un suivi de budget mensuel qui compare prévisionnel et réalisé dans Excel avec SOMME.SI.ENS, RECHERCHEX et des alertes visuelles. La méthode reste simple : une base propre, une source budgétaire cohérente et une synthèse claire.
Une fois le modèle en place, l’entretien devient léger. Il suffit surtout d’ajouter les écritures, de vérifier les correspondances, puis de lire les écarts pour agir plus vite.