Consolidation multi-sitesCas pratique Excel pas a 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 a un reporting unifié.
Les données de depart
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 |
Solution pas a 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")Resultat 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")Resultat 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 a jour ces infos dans chaque onglet.
=RECHERCHEV(A1;RefSites!$A:$D;2;FAUX)Resultat 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")Resultat attendu : Le ratio résultat/CA du site (ex: 44 500 / 145 000 = 30,7% de marge pour Site_A).
Formules utilisées dans ce cas pratique
Questions fréquentes
Comment ajouter un nouveau site a la consolidation ?
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.
Que faire si les sites n'ont pas les mêmes catégories de charges ?
Impose un plan comptable commun. Si un site a une charge spécifique, crée la ligne dans tous les onglets (avec un montant a zéro pour les sites qui ne l'utilisent pas). La structure doit être identique partout.
INDIRECT ou 3D references pour consolider ?
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.
Comment gérer les devises différentes entre sites ?
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).
Power Query est-il meilleur qu'INDIRECT pour la consolidation ?
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
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.
Budget prévisionnel annuel
Construis un budget prévisionnel annuel qui ventile les recettes et dépenses mois par mois, calcule les écarts et te donne une vision claire de ta trajectoire financière.
Comparaisons liees
Envie de pratiquer plus ?
Rejoins Le Dojo Club pour accéder a des dizaines de cas pratiques corrigés, des formations complètes et une communauté de pros qui s'entraident.
Essayer pendant 30 jours