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é.
Utilise INDIRECT pour construire des références dynamiques vers les onglets de chaque site. Le nom de l'onglet est dans une cellule, et INDIRECT le transforme en référence valide. Tu changes le nom du site dans la cellule et la formule pointe automatiquement vers le bon onglet.
=INDIRECT(A1&"!B2")Résultat attendu : La valeur de la cellule B2 de l'onglet dont le nom est dans A1 (ex: 45 000 si A1 contient "Site_A").
Attention
- INDIRECT ne fonctionne pas avec des fichiers fermés. Tous les fichiers sources doivent être ouverts. Pour consolider des fichiers externes, utilise Power Query.
- Si le nom de l'onglet contient un espace, entoure-le d'apostrophes : =INDIRECT("'"&A1&"'!B2").
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.
Questions fréquentes
Utilise INDIRECT pour créer des références dynamiques : =INDIRECT("Site_A!B2") récupère la cellule B2 de l'onglet Site_A. Additionne les valeurs de chaque site dans l'onglet de consolidation. Pour que cette approche fonctionne, tous les onglets doivent avoir exactement la même structure.
INDIRECT(A1&"!B2") transforme le texte "Site_A!B2" en vraie référence Excel. Si A1 contient le nom de l'onglet, la formule lit automatiquement la cellule B2 de cet onglet. Change A1 et la formule pointe vers un autre site sans réécrire quoi que ce soit.
Les références 3D (=SOMME(Site_A:Site_C!B2)) sont plus concises mais peu flexibles : tu ne peux pas exclure un site ni insérer un onglet entre les deux extrêmes sans casser la formule. 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 ajoute son nom dans la liste de référence. Si tes formules INDIRECT pointent vers cette liste, elles s'adaptent automatiquement sans toucher à la consolidation.
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é.
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
Envie de t'entraîner plus ?
Des dizaines de cas pratiques Excel corrigés pour progresser pour de vrai.
Voir tous les exercices