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
Standardiser la structure des onglets par site pour rendre 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 les ratios de performance entre sites (marge, charges sur CA).
- 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 de cet exercice. Copie-les ou télécharge le fichier Excel, puis entraîne-toi avant de regarder le corrigé.
| 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 |
| 5 | Salaires | 18 000 | 18 000 | 18 000 | 54 000 |
| 6 | Loyer | 5 500 | 5 500 | 5 500 | 16 500 |
| 7 | Achats matières | 8 200 | 9 100 | 10 500 | 27 800 |
| 8 | Énergie | 2 800 | 2 600 | 2 400 | 7 800 |
| 9 | Marketing local | 1 500 | 2 200 | 1 800 | 5 500 |
| 10 | Maintenance | 900 | 1 200 | 3 500 | 5 600 |
| 11 | Marge brute | 10 100 | 9 900 | 10 300 | 30 300 |
Exercice guidé
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 que la structure d'un onglet diverge : une ligne insérée, un onglet renommé différemment, une colonne décalée. 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. Place-les dans une colonne d'une table de référence (Site_A, Site_B...) et fais pointer toutes tes formules vers cette colonne. Si un site ferme ou change de nom, tu mets à jour un seul endroit.
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.
3 exercices similaires à la consolidation multi-sites
Rapprochement bancaire automatisé
Construire un rapprochement bancaire automatisé qui compare ton relevé bancaire avec ta comptabilité et identifie les écarts en quelques secondes.
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 pour repérer les doublons de pièces, les écritures déséquilibrées et les montants hors seuil avec NB.SI, SOMME.SI et SI.
Voir l'exercice
Le modèle prêt à l'emploi
Découvre notre tableau de comptabilité simple pour tenir tes recettes et tes dépenses sans logiciel
Questions fréquentes
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.
Crée un onglet Taux de change avec le taux mensuel pour chaque devise. Dans chaque onglet site, multiplie les montants par le taux correspondant avant de consolider. Affiche les deux versions (devise locale et euros) dans le tableau de synthèse pour garder la traçabilité.
Toute la boîte à outils Excel
Formules, exercices, modèles, raccourcis, lexique : tout ce qu’il te faut pour progresser, en accès libre.
Tout voir
