Consolidation multi-sitesCas pratique Excel pas à pas
Le contexte
Consolide automatiquement les données de plusieurs sites (magasins, agences, filiales) dans un tableau de synthèse. Tu passes de 5 fichiers séparés à un reporting unifié.
Les données de départ
Crée un onglet par site avec exactement la même structure : mêmes colonnes, mêmes lignes, même ordre. Nomme les onglets avec le code du site (Site_A, Site_B, Site_C...). La standardisation est la clé : si les structures divergent, la consolidation casse.
| 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 |
Solution pas à pas
2Créer la formule de consolidation dynamique
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").
3Totaliser par poste et par site
Utilise SOMME.SI.ENS dans l'onglet de consolidation pour totaliser un poste donné sur tous les sites. La formule additionne les valeurs de chaque onglet site. Tu obtiens le CA total, les charges totales et le résultat consolidé.
=INDIRECT("Site_A!B2")+INDIRECT("Site_B!B2")+INDIRECT("Site_C!B2")Résultat attendu : Le CA consolidé de janvier (ex: 45 000 + 38 000 + 52 000 = 135 000 euros).
4Récupérer les infos site
Utilise RECHERCHEV pour aller chercher les infos de chaque site (nom du responsable, adresse, date d'ouverture) dans une table de référence. Cela évite de mettre à jour ces infos dans chaque onglet.
=RECHERCHEV(A1;RefSites!$A:$D;2;FAUX)Résultat attendu : Le nom du responsable du site (ex: "Dupont" pour Site_A).
5Créer le comparatif inter-sites
Calcule les ratios par site (marge, CA/effectif, charges/CA) et classe-les. Le site avec le meilleur ratio de marge est ton modèle, celui avec le pire est ta priorité d'amélioration. Affiche le tout dans un tableau comparatif avec des sparklines.
=INDIRECT(A2&"!E3")/INDIRECT(A2&"!E1")Résultat attendu : Le ratio résultat/CA du site (ex: 44 500 / 145 000 = 30,7% de marge pour Site_A).
Questions fréquentes
Duplique un onglet existant (pour garder la même structure), renomme-le avec le code du nouveau site et ajoute-le dans la liste des sites de l'onglet de consolidation. Les formules INDIRECT s'adaptent automatiquement.
Impose un plan comptable commun. Si un site a une charge spécifique, crée la ligne dans tous les onglets (avec un montant à zéro pour les sites qui ne l'utilisent pas). La structure doit être identique partout.
Les références 3D (=SOMME(Site_A:Site_C!B2)) sont plus simples mais moins flexibles : tu ne peux pas exclure un site facilement. INDIRECT est plus verbeux mais tu contrôles exactement quels sites sont inclus.
Ajoute un onglet 'Taux de change' avec le taux du mois pour chaque devise. Multiplie les montants par le taux avant de consolider. Affiche les montants en devise locale ET en devise de consolidation (euros).
Oui, pour les gros volumes et les fichiers externes. Power Query gère les fichiers fermés, les formats différents et les transformations complexes. INDIRECT reste pratique pour une consolidation simple de 3-5 onglets dans le même fichier.
Cas pratiques similaires
Rapprochement bancaire automatisé
Construis un rapprochement bancaire automatisé qui compare ton relevé bancaire avec ta comptabilité et identifie les écarts en quelques secondes.
Suivi de trésorerie pour association
Mets en place un suivi de trésorerie simple et efficace pour ton association, avec prévisionnel et alertes automatiques.
Reporting financier mensuel
Automatise ton reporting financier mensuel avec des formules qui consolident les données, calculent les écarts budget vs réalisé et mettent en forme le tout pour la direction.
Comparaisons liées
Envie de pratiquer plus ?
Rejoins Le Dojo Club pour accéder à des dizaines de cas pratiques corrigés, des formations complètes et une communauté de pros qui s'entraident.
Essayer pendant 30 jours