Comment consolider des données identiques dans un fichier Excel

La consolidation de données permet à une entreprise de regrouper et combiner toutes les données, qui auraient été saisies depuis plusieurs endroits ou à différents moments afin d'offrir une vue synthétisée. La consolidation consiste à créer un regroupement de données pour en faire une synthèse.

Pourquoi consolider les données

La consolidation des données est un processus qui permet de synthétiser des informations provenant de différentes feuilles de calcul en une seule feuille. Cela facilite la mise à jour et l'analyse des données. Pour résumer et signaler les résultats à partir de feuilles de calcul distinctes, vous pouvez consolider les données de chaque feuille dans une feuille de calcul maître.

Conditions préalables et types de consolidation

Vérifiez que chaque plage de données est au format liste. Chaque colonne doit avoir une étiquette (en-tête) dans la première ligne et contenir des données similaires. Placez chaque plage dans une feuille de calcul distincte, mais n’entrez rien dans la feuille de calcul maître où vous envisagez de consolider les données.

Consolidation par position : les données dans les zones source ont le même ordre et utilisent les mêmes étiquettes. Cette consolidation peut être utilisée lorsque toutes les données sont placées au même endroit (colonnes et lignes) et que les étiquettes se retrouvent aussi au même endroit dans chacun des tableaux à consolider.
Consolidation par catégorie : lorsque les données des zones source ne sont pas organisées dans le même ordre, mais utilisent les mêmes étiquettes. La consolidation de données par catégorie revient en quelque sorte à créer un tableau croisé dynamique.

Méthode native d'Excel : l'outil Consolider

Accédez à l'onglet 'Données' et cliquez sur 'Consolider'. Dans la boîte de dialogue Consolider, dans la zone Fonction, cliquez sur la fonction récapitulative que vous souhaitez qu’Excel utilise pour consolider les données (Somme, Moyenne, Max, Min, etc.).

Dans la zone Référence, sélectionnez la plage de cellules du premier tableau, en incluant la ligne de titres, puis cliquez sur Ajouter. Faites de même pour les autres tableaux. Cochez les cases “Ligne du haut” et “Colonne de gauche”, si les données source ont des étiquettes à ces emplacements.

Pour que les données de la feuille de synthèse se mettent à jour automatiquement lorsque les valeurs des feuilles sources changent, cochez la case 'Lier aux données sources' avant de valider. Cliquez sur OK pour qu’Excel génère la consolidation pour vous.

Exemples d'utilisation de l'outil Consolider

La feuille Synthèse des ventes s'active par défaut. Elle propose un tableau dans lequel sont référencés, dans la colonne de gauche, les noms des vendeurs de l'entreprise. La colonne Total quant à elle, est vide pour l'instant. Vous affichez ainsi le tableau synthétisant les chiffres réalisés par chacun des vendeurs, au cours du premier trimestre.

Si vous affichez tour à tour, les feuilles Ventes T2, Ventes T3 et Ventes T4, vous constaterez qu'il s'agit de la synthèse des ventes, pour ces mêmes vendeurs, pour chaque trimestre. Les tableaux ont donc tous la même structure. Ils possèdent le même nombre de lignes, correspondant aux noms des vendeurs ainsi que le même nombre de colonnes, dont la colonne Total.

Pour faciliter l'interprétation des résultats, la feuille Synthèse des ventes propose donc, de consolider la somme des chiffres réalisés par chacun des vendeurs, au cours des 4 trimestres. Seule la cellule du total de la feuille Ventes T1 pour le vendeur Hamalibou a été désignée. C'est d'ailleurs ce qu'indique le contenu de la fonction Somme : 'Ventes T1:Ventes T4'!F6. Les deux points (:) permettent habituellement de désigner une plage de cellules. Cette fois, ils désignent une plage de feuilles.

En plus de créer le sommaire, Excel créera un plan (groupé par défaut). Vous notez de même la présence de symboles +, dans la marge, à gauche des étiquettes de lignes de la feuille, ainsi que les boutons 1 et 2, en haut à gauche des étiquettes de colonnes. En même temps qu'Excel consolide les données sources pour livrer le tableau de synthèse des éléments recoupés, il construit un plan, qui permet d'avoir la trace sur les valeurs d'origine.

Consolider des données avec structures différentes

Les données numériques à consolider ne présentent pas forcément la même structure. Dans l'exemple suivant, nous souhaitons synthétiser les ventes réalisées par plusieurs magasins d'une entreprise, dans la feuille Consolidation CA. Certains produits sont vendus par chacun des magasins, d'autres en revanche, sont spécifiques selon la région. La consolidation doit néanmoins les intégrer dans la feuille de synthèse.

Contrairement à la méthode précédente, cette feuille est vide. C'est fort logiquement, la fonction de consolidation d'Excel qui va reconstruire le tableau final, en même temps qu'elle regroupe les données et les consolide. Cette méthode permet de consolider les sources en reconstruisant toutes les données synthétisées, y compris celles qui ne sont pas communes.

Vous notez néanmoins que pour tous les produits communs, les chiffres des ventes ont bien été sommés et que tous les autres ont été intégrés. Il est désormais beaucoup plus simple, pour la direction, d'analyser les chiffres, dans leur globalité. Vous déployez ainsi l'affichage et accédez au détail des sources qui ont permis de construire le résultat consolidé des ventes, pour chaque produit.

Option alternative : utiliser un Tableau Croisé Dynamique multisource

Une autre façon de consolider plusieurs sources de données, est d’utiliser un tableau croisé dynamique (TCD). L’avantage, comparé à l’outil “Consolider”, est qu’ici vous allez pouvoir mélanger des données complémentaires.

Tout d’abord, vous devez transformer vos données brutes en tableau de données Excel (Ctrl+T). Dans l’onglet Insertion, cliquez sur Tableau croisé dynamique, écrivez le nom de la table dans la source et cochez la case “Ajouter ces données au modèle de données”. Cocher la dernière case permet d‘ajouter ensuite d‘autres données.

Lire aussi: Fiche INSEE : le guide

Quand vous cochez un champ d’un deuxième tableau afin de l’ajouter dans votre TCD, Excel vous demande de confirmer les relations entre les tableaux. Ici, c’est à vous d’indiquer à Excel quels champs sont communs aux 2 tableaux. Et voilà, vous obtenez un TCD combinant des données de plusieurs tableaux source !

Consolidation dynamique via liens, noms et fonctions

Entrez une formule avec des références de cellules à d’autres feuilles de calcul, une pour chaque feuille. Pour entrer une référence de cellule, telle que Sales!B4 : dans une formule sans taper, tapez la formule jusqu’au point où vous avez besoin de la référence, puis cliquez sur l’onglet de la feuille de calcul, puis sur la cellule. Excel complète le nom de la feuille et l’adresse de la cellule pour vous.

Entrez une formule contenant une référence 3D utilisant une référence à une plage de noms de feuille de calcul. Par exemple, pour faire la somme des mêmes cellules sur plusieurs onglets : 'Ventes T1:Ventes T4'!F6. REMARQUE : dans de tels cas, les formules peuvent être sujettes aux erreurs, car il est très facile de sélectionner accidentellement la mauvaise cellule.

Utilisez des plages nommées pour une consolidation dynamique. Créez des plages nommées pour chaque source de données (Formules > Définir un nom). Cela permet de construire des formules de consolidation flexibles et maintenables. Quand une source change de taille, vos formules s'adaptent automatiquement sans intervention manuelle.

Consolider avec INDIRECT pour éviter les mises à jour manuelles : utilisez INDIRECT pour référencer dynamiquement plusieurs feuilles sans créer de formules complexes, par exemple =SOMME(INDIRECT("'"&A1&"'!B:B")) où A1 contient le nom de la feuille (ex: "Janvier").

Approche basée sur les tables et formules avancées

Après transformation de votre fichier brut en table de données, vous obtenez plusieurs tableaux identiques sur des périodes différentes. Sélectionnez les plages à consolider et créez une nouvelle feuille de calcul : c’est là que va être créée la consolidation.

Pour consolider des plages qui ne sont pas exactement aux mêmes emplacements, vous devez soit maintenir les tableaux aux mêmes emplacements, soit les sélectionner manuellement dans la boîte de dialogue Consolidation. En cochant 'Lier aux données sources', vous obtenez une vue détaillée et interconnectée des données consolidées.

Technique	Quand l'utiliser	Avantages
Outil Consolider	Plages structurées identiques ou étiquettes communes	Regroupe rapidement, crée un plan et lie aux sources
Tableau Croisé Dynamique	Données complémentaires, analyses flexibles	Réorganisation facile, relations entre tables
Formules (SOMME, SOMME.SI, INDIRECT)	Consolidation sur mesure, automatisation	Contrôle fin, dynamiques via plages nommées

Bonnes pratiques et contrôles qualité

Transformez chaque plage de données en tableau structuré pour maintenir des références dynamiques.
Utilisez SUMIFS pour agréger avec plusieurs critères (région ET période) afin d'éviter les erreurs de double-comptage.
Ajoutez des formules de vérification pour détecter les anomalies : vérifiez que les totaux consolidés correspondent aux totaux des données brutes.
Utilisez la mise en forme conditionnelle pour mettre en évidence les écarts et anomalies.
Si vous consolidez fréquemment des données, créez des feuilles de calcul à partir d’un modèle qui utilise une disposition cohérente.

Exemples de formules utiles

=SUMIFS(Données_Brutes!F:F, Données_Brutes!C:C, A2, Données_Brutes!B:B, ">="&DATE(2024,1,1), Données_Brutes!B:B, "<"&DATE(2024,2,1)) - somme avec plusieurs critères.
=INDEX(Données_Brutes!D:D, MATCH(1, (Données_Brutes!C:C=A2)*(Données_Brutes!F:F=MAX(SI(Données_Brutes!C:C=A2, Données_Brutes!F:F))), 0)) - combinaison INDEX-MATCH pour récupérer un détail.
=SOMME(INDIRECT("'"&A1&"'!B:B")) - consolidation dynamique via INDIRECT.

Regrouper et consolider des données Excel

Astuces de pro et limites

La fonctionnalité est intéressante. Par contre, le temps de mise en place, dans le cas d’un dossier plus volumineux, est excessivement long, comparativement à la fonction INDIRECT ou à la fonction de somme transversale. De plus, si on ajoute simplement un nouveau magasin dans le fichier, on doit recommencer la manipulation avec l'outil Consolider, sauf si l'on a anticipé via des plages nommées ou des TCD liés au modèle de données.

Utilisez SUMPRODUCT pour consolider avec filtres multiples sans créer de tableaux intermédiaires : =SOMMEPROD((Region=A1)*(Produit=B1)*(Montant)).

Automatisation et reporting

Configurez les connexions de données pour que votre consolidation se mette à jour automatiquement. Utilisez Données > Actualiser tout ou programmez une actualisation programmée. Pour Excel 365, activez les connexions cloud pour que les données se synchronisent sans action manuelle.

Finalisez votre process par une feuille 'Dashboard' avec des graphiques et KPIs clés (total général, meilleure région, croissance %). Liez les graphiques aux plages consolidées pour une mise à jour instantanée.

Exemples concrets d'applications

Consolidation des ventes multi-canaux : fusionner des données de ventes provenant de plusieurs sources pour obtenir une vision globale mensuelle et un tableau consolidé affichant totaux, CA par canal et parts de marché.

Agrégation des KPIs RH multi-départements : consolider indicateurs de performance de plusieurs départements pour un tableau de bord trimestriel de la direction.

Fusion des données financières multi-filiales : consolider les résultats trimestriels de filiales pour la consolidation comptable groupe et l'analyse de performance.

Récapitulatif rapide des étapes pour consolider

Préparez et formatez chaque plage en tableau (Ctrl+T).
Créez une feuille de synthèse vide.
Onglet Données → Consolider : choisissez la fonction (Somme par défaut).
Ajoutez chaque plage de référence et cochez lignes/colonnes si nécessaire.
Cochez 'Lier aux données sources' pour mises à jour automatiques.
Validez, puis appliquez mise en forme et contrôles qualité.

balises: