Consolidation multi-sites sur Excel
Un exercice corrigé pour consolider plusieurs sites dans Excel avec INDIRECT, unifier CA et résultat sans copier-coller, avec le fichier Excel à télécharger.
Consolider plusieurs sites, c'est rassembler le CA, les charges et le résultat de chaque magasin ou agence dans un reporting unique. Fait au copier-coller, c'est long, et une seule cellule mal collée fausse tout le consolidé. Dans cet exercice, on va voir ensemble comment laisser Excel aller chercher les chiffres dans chaque onglet tout seul.
L'objectif est de bâtir une consolidation qui se met à jour quand les sites livrent leurs chiffres, et de comparer leurs performances une fois les données réunies au même endroit. C'est exactement le travail d'un contrôleur de gestion ou d'un DAF qui veut un reporting instantané sans outil BI externe !
Ce que tu vas construire
- Comprendre pourquoi des onglets de sites à la structure identique rendent la consolidation possible.
- Utiliser INDIRECT pour créer des références dynamiques vers les onglets de chaque site.
- Consolider les postes clés (CA, charges, résultat) sur tous les sites en une formule.
- Comparer la marge de chaque site entre eux et avec celle du groupe.
- Comprendre les limites d'INDIRECT et quand basculer vers Power Query.
À connaître avant de commencer
- Comprendre les références inter-onglets dans Excel (ex: =Site_A!B2).
- Être à l'aise avec les formules imbriquées sur plusieurs onglets de sites.
Voici les données de départ, réparties sur 5 onglets comme dans le fichier (clique sur un onglet sous le tableau pour changer de feuille). Copie-les ou , puis entraîne-toi avant de regarder le corrigé.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Site | CA | Charges | Résultat | Responsable | Marge |
| 2 | Site_A | |||||
| 3 | Site_B | |||||
| 4 | Site_C | |||||
| 5 | Groupe |
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Poste | Jan | Fév | Mar | Total |
| 2 | CA | 45 000 | 48 000 | 52 000 | 145 000 |
| 3 | Charges | 32 000 | 33 500 | 35 000 | 100 500 |
| 4 | Résultat | 13 000 | 14 500 | 17 000 | 44 500 |
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Poste | Jan | Fév | Mar | Total |
| 2 | CA | 38 000 | 40 000 | 42 000 | 120 000 |
| 3 | Charges | 29 000 | 30 500 | 31 500 | 91 000 |
| 4 | Résultat | 9 000 | 9 500 | 10 500 | 29 000 |
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Poste | Jan | Fév | Mar | Total |
| 2 | CA | 52 000 | 50 000 | 55 000 | 157 000 |
| 3 | Charges | 43 000 | 42 000 | 45 500 | 130 500 |
| 4 | Résultat | 9 000 | 8 000 | 9 500 | 26 500 |
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Site | Responsable | Ville | Ouverture |
| 2 | Site_C | Leroy | Lille | 02/05/2017 |
| 3 | Site_A | Dupont | Lyon | 04/03/2019 |
| 4 | Site_B | Martin | Nantes | 16/09/2021 |
Résoudre l'exercice étape par étape
Coche chaque étape au fur et à mesure. Tente-la dans ton fichier, puis déplie le corrigé.
Astuces pour aller plus loin
La standardisation est la seule contrainte qui compte
INDIRECT casse dès qu'un onglet diverge, par exemple un onglet renommé, un poste libellé autrement ou une colonne Total déplacée. Chercher le poste par son nom avec SOMME.SI.ENS absorbe une ligne insérée, pas le reste. Impose un modèle verrouillé (protection de feuille) à tous les sites avant de construire la consolidation. Une heure de cadrage au départ économise des semaines de corrections.
Protège les noms d'onglets dans une table de référence
Ne dissémine pas les noms de sites en dur dans 50 formules INDIRECT. Écris-les une seule fois dans une colonne, comme la colonne A de l'onglet Consolidation (Site_A, Site_B...), et fais pointer toutes tes formules vers cette colonne. Si un site ferme ou change de nom, tu mets à jour cette liste et l'onglet, jamais les formules.
Passe à Power Query pour les fichiers séparés
INDIRECT ne fonctionne que sur des fichiers ouverts. Dès que les sites envoient des fichiers Excel séparés, Power Query est plus adapté : il lit les fichiers fermés, s'actualise en un clic et gère les transformations (nettoyage de colonnes, types de données) sans formule complexe.
S'entraîner sur 3 exercices similaires à la consolidation multi-sites
État de rapprochement bancaire automatisé
Pointer ton relevé contre la comptabilité avec RECHERCHEV, puis dresser l'état de rapprochement bancaire en deux colonnes jusqu'à l'égalité des soldes rectifiés.
Voir l'exercice
Matrice de décision multicritère
Construis une matrice de décision pondérée pour comparer plusieurs options sur des critères chiffrés et sortir une recommandation objective.
Voir l'exercice
Détecter les anomalies d'un journal comptable
Passe au crible un extrait de journal comptable en partie double pour repérer les écritures saisies deux fois, les pièces déséquilibrées et les montants hors seuil avec NB.SI.ENS, SOMME.SI et SI.
Voir l'exercice
Tu as peut-être encore une question
Écris les noms des onglets de sites dans une colonne de l'onglet de consolidation, puis utilise INDIRECT pour transformer chaque nom en référence. Par exemple, =INDIRECT(A2&"!E2") lit la cellule E2 de l'onglet dont le nom est en A2. Recopiée sur la liste, la formule ramène le chiffre de chaque site, et SOMMEPROD combiné à SOMME.SI.ENS en fait le total du groupe. Pour que cette approche fonctionne, tous les onglets doivent avoir exactement la même structure.
Les références 3D (=SOMME(Site_A:Site_C!B2)) sont plus concises mais peu flexibles. Tu ne peux pas exclure un site placé entre les deux extrêmes, et tout onglet glissé entre eux entre dans la somme sans prévenir. INDIRECT est plus verbeux mais tu contrôles exactement quels sites sont inclus dans la consolidation.
Duplique un onglet existant pour garder la structure identique, renomme-le avec le code du nouveau site, puis insère son nom dans la liste des sites, entre deux sites existants pour que la plage citée par le total du groupe s'étende d'elle-même. Ajoute aussi sa ligne dans l'onglet RefSites, sans quoi sa colonne Responsable affiche #N/A. Il ne reste qu'à recopier les formules de la ligne du dessus sur la nouvelle ligne, sans rien réécrire.
Dès que les sites travaillent dans des fichiers séparés (et non dans des onglets du même classeur), Power Query est plus adapté : il lit les fichiers fermés, s'actualise en un clic depuis le ruban Données et gère les différences de structure entre fichiers. INDIRECT est limité aux classeurs ouverts.
L'outil Consolider d'Excel, dans l'onglet Données, additionne plusieurs plages de même forme en quelques clics, il suffit de choisir la fonction Somme, d'ajouter la plage de chaque onglet de site, de cocher « Ligne du haut » et « Colonne de gauche » pour qu'il s'aligne sur les libellés, et de valider. Le résultat est figé, il faut relancer l'outil à chaque mise à jour, sauf à cocher « Lier aux données source », qui crée des liaisons mais fige la liste des sites. C'est le bon outil pour une consolidation ponctuelle, et quand le reporting se refait chaque mois avec des sites qui changent, les formules INDIRECT de cet exercice restent vivantes.
Pour aller plus loin
Tout ce dont tu as besoin pour continuer à t’entraîner se trouve ici.
Récupère notre modèle de comptabilité simple
Télécharger le fichier
Ton cours Excel gratuit
3 chapitres pour apprendre les bases et découvrir l'automatisation.
Voir le cours
Télécharge notre cours Excel complet en PDF
Télécharger
Le quiz qui révèle ton vrai niveau
Faire le test
Le dictionnaire des formules Excel
Chercher une formule