Si votre quotidien consiste à jongler avec plusieurs feuilles de calcul contenant différents types de données, vous cherchez sans doute des moyens rapides et efficaces de rassembler des données issues de plusieurs sources. Par exemple, si vous travaillez avec des données financières, établir un budget annuel implique de combiner les comptes de plusieurs services selon une structure prédéfinie. Heureusement, Excel propose la fonctionnalité Consolider, qui vous aide à le faire sans aucun travail manuel, et donc avec moins de temps et d'efforts de votre part.
Dans cet article, vous trouverez une explication complète de ce qu'est la consolidation de données dans Excel, ainsi qu'un guide étape par étape pour consolider dans Excel les données de plusieurs feuilles de calcul en une seule. Sinon, les automatisations IA de Sheetgo peuvent consolider pour vous des données issues de plusieurs sources, notamment Excel et Google Sheets.
Qu'est-ce que la consolidation de données dans Excel ?
La fonctionnalité Consolider d'Excel vous permet de rassembler les données de différentes feuilles de calcul et de les fusionner en une seule. Cette feuille peut ensuite servir de feuille maîtresse, le même système qu'utilisent la plupart des plateformes d'automatisation. La feuille maîtresse est liée aux feuilles sources : dès qu'une feuille source change, la feuille consolidée est mise à jour.
Ce type d'outil de consolidation est très utilisé dans la finance, en particulier par les responsables de la planification et de l'analyse. Comme toutes les données sont structurées dans un seul tableau, n'importe qui dans votre organisation peut mener beaucoup plus facilement l'analyse dont il a besoin.
Pour une explication générale du concept en dehors d'Excel, découvrez ce qu'est la consolidation de données.
Comment consolider des données dans Excel ?
La fonctionnalité Consolider permet de combiner plusieurs colonnes Excel en une seule mais aussi de consolider des lignes au sein d'une même feuille Excel. Vous pouvez également consolider les données de plusieurs feuilles en une seule.
Consolider les données de plusieurs feuilles dans une seule feuille
Imaginons que vous ou votre responsable deviez analyser les dépenses annuelles. Il faut alors rassembler les coûts des différents services : RH, Marketing, Ventes, etc. Chaque service enregistre ses données dans une feuille différente. Compiler toutes ces données pour une analyse globale prendrait trop de temps, d'où l'intérêt d'une fonctionnalité comme Consolider dans Excel. Voici comment consolider dans Excel les données de plusieurs feuilles dans une seule feuille.
1. Créez une feuille à part et renommez-la "Récapitulatif" ou avec un nom similaire. C'est là qu'Excel créera le tableau récapitulatif.
2. Cliquez sur une cellule vide, de préférence en haut à gauche, pour indiquer où commence votre tableau récapitulatif.
3. Allez dans Données > Consolider.
4. Cliquez sur le menu déroulant "Fonction", puis sur "Somme", pour additionner les valeurs de toutes les feuilles.
5. Dans "Référence", cliquez sur le bouton fléché vers le haut pour sélectionner les données sources à consolider.
6. La fenêtre "Consolider - Référence" s'affiche. Comme les données sources se trouvent dans le même classeur, il vous suffit de naviguer entre les feuilles et de sélectionner la plage en faisant glisser le curseur.
7. Comme ci-dessous, Excel saisit automatiquement la plage de vos données sources. Il affiche le nom de la feuille suivi d'un point d'exclamation, puis les lignes et les colonnes sélectionnées. Une fois la sélection faite, cliquez sur le bouton fléché pour revenir à la fenêtre principale "Consolider".
8. Cliquez sur "Ajouter" pour l'inclure dans la liste "Toutes les références".
9. Répétez les étapes 5, 6, 7 et 8 pour les autres feuilles. Une fois terminé, vous devriez voir la liste complète des références, comme ci-dessous.
10. Cochez les cases "Ligne du haut" et "Colonne de gauche" pour inclure les étiquettes de la première ligne et de la colonne la plus à gauche. Avec ces cases cochées, Excel associe les valeurs d'après leurs étiquettes, et non d'après leur position dans chaque plage.
11. Cochez l'option "Lier aux données sources" pour que le tableau récapitulatif se mette à jour chaque fois qu'un autre utilisateur modifie la feuille source. Sinon, vous devrez le faire manuellement. Une fois terminé, cliquez sur "OK".
12. Cliquez sur les boutons "+" à gauche pour afficher les données d'origine depuis la nouvelle feuille maîtresse.
13. Si vous voulez afficher ces informations d'origine pour toutes les lignes, cliquez sur le bouton portant le numéro "2", juste au-dessus du premier bouton "+".
Les données de la colonne C correspondent au nom du classeur qui contient vos données. Si vous préférez la masquer, faites simplement un clic droit dessus et sélectionnez "Masquer". Cela indique simplement que ces cellules font partie d'un groupe actuellement réduit.
Par ailleurs, Sheetgo offre une transparence et un contrôle complets sur vos classeurs Excel. Pendant que vous vous concentrez sur la saisie des données, les automatisations IA de Sheetgo les consolident dans votre fichier principal. Vous gagnez ainsi le temps de créer de nouvelles feuilles ou de nouveaux classeurs pour les tableaux récapitulatifs. Consultez notre article sur comment fusionner des fichiers Excel avec Sheetgo.
Comment consolider les données de plusieurs classeurs Excel
Les étapes ci-dessus partent du principe que chaque service conserve sa feuille dans le même classeur. Si chaque équipe envoie son propre fichier, vous pouvez quand même utiliser l'outil Consolider. Seule la façon de choisir chaque référence change.
1. Ouvrez le classeur récapitulatif et tous les classeurs sources.
2. Dans le classeur récapitulatif, cliquez sur la cellule où le tableau doit commencer et allez dans Données > Consolider.
3. Cliquez dans la zone Référence, passez à la fenêtre d'un classeur source et sélectionnez la plage. Excel écrit le nom du fichier entre crochets avant le nom de la feuille. Si un fichier est fermé, cliquez sur Parcourir et sélectionnez-le. Excel saisit son chemin, et vous ajoutez ensuite la feuille et la plage.
4. Cliquez sur Ajouter, puis répétez l'étape 3 pour chaque classeur.
5. Cochez Ligne du haut et Colonne de gauche si les fichiers présentent leurs étiquettes dans un ordre différent. Cochez Lier aux données sources, puis cliquez sur OK.
Avec les liaisons activées, Excel actualise le récapitulatif à partir des fichiers sources lorsque vous l'ouvrez et autorisez la mise à jour. Laissez les fichiers sources à leur place et ne les renommez pas, sinon les liaisons seront rompues.
Quand de nouveaux fichiers arrivent chaque mois, les ajouter un par un devient fastidieux. Vous pouvez consolider des fichiers Excel automatiquement pour que les nouvelles données arrivent dans le récapitulatif sans refaire ces étapes.
Comment consolider les données de plusieurs lignes dans Excel
Les deux cases d'étiquettes déterminent la façon dont Excel associe vos données. Si les deux sont décochées, Excel consolide par position : il combine ce qui se trouve dans la même cellule de chaque plage, donc toutes les plages doivent avoir la même disposition. Si Ligne du haut ou Colonne de gauche est cochée, il consolide par catégorie. Il associe les valeurs d'après leurs étiquettes, si bien que les plages peuvent lister les éléments dans n'importe quel ordre et être de tailles différentes.
La consolidation par catégorie fonctionne aussi au sein d'une seule feuille. Imaginons que vous ayez un journal des ventes où chaque commercial apparaît sur plusieurs lignes, une par affaire, et que vous vouliez une ligne par commercial avec le total de ses affaires.
1. Cliquez sur une cellule vide à l'écart des données sources.
2. Allez dans Données > Consolider et choisissez Somme dans le menu Fonction.
3. Dans Référence, sélectionnez toute la liste, en commençant par la colonne qui contient les noms des commerciaux et en incluant la ligne d'en-tête. Cliquez sur Ajouter.
4. Cochez Ligne du haut et Colonne de gauche, puis cliquez sur OK.
Excel renvoie une ligne par nom avec les valeurs additionnées. La première colonne de la plage contient les étiquettes : ce doit donc être la colonne selon laquelle vous voulez regrouper. Consolider ne fonctionne qu'avec des nombres, et les autres colonnes de texte restent vides. L'option Lier aux données sources n'est pas disponible ici, car Excel ne peut pas lier un résultat à une source située sur la même feuille.
Comment consolider les données de plusieurs colonnes dans Excel
Ligne du haut fait pour les colonnes ce que Colonne de gauche fait pour les lignes. Imaginons que la feuille Nord comporte des colonnes pour janvier, février et mars, et la feuille Sud pour janvier, mars et avril. Ajoutez les deux plages, cochez Ligne du haut et Colonne de gauche, et Excel crée un seul tableau avec une colonne par mois. Les valeurs sous un même en-tête sont additionnées. Avril n'apparaît que dans Sud, et obtient tout de même sa propre colonne.
Si vous laissez Ligne du haut décochée, Excel combine les colonnes par position. La deuxième colonne de Nord serait additionnée à la deuxième colonne de Sud, même si l'une contient février et l'autre mars. Ne vous passez des étiquettes que si toutes les plages ont les mêmes en-têtes dans le même ordre.
Exemples de consolidation de données dans Excel
Le menu Fonction de la boîte de dialogue Consolider détermine ce qu'Excel fait des valeurs correspondantes. Quelques usages courants :
- Un budget annuel, où chaque service tient ses coûts dans sa propre feuille et où Somme les additionne par poste de dépense, comme dans la démonstration ci-dessus.
- Des ventes régionales, où chaque région vend des produits différents. Somme avec Ligne du haut et Colonne de gauche donne une ligne par produit avec son total sur l'ensemble des régions.
- Des devis fournisseurs, une feuille par fournisseur, où Min renvoie le prix le plus bas proposé pour chaque article.
- Des résultats d'enquête, une feuille par bureau, où Moyenne donne le score moyen de chaque question.
- Des journaux de commandes, où Nombre renvoie le nombre d'entrées numériques de chaque client dans l'ensemble des plages.
Une fois les données réunies dans un seul tableau, un tableau croisé dynamique dans Excel vous permet de les regrouper et de les filtrer davantage.
Conclusion
La consolidation de données est généralement l'une des tâches les plus courantes des analystes de données. En effet, les données s'interprètent mieux lorsqu'on les voit dans leur contexte. Cependant, comme chaque jeu de données suit probablement sa propre structure ou catégorisation, il ne suffit pas de copier toutes les données dans une seule feuille.
Vous savez maintenant comment fonctionne la fonctionnalité Consolider d'Excel pour des données situées dans le même classeur ou dans des classeurs différents. Cependant, comme vos données sont très probablement réparties dans plusieurs classeurs, les automatisations IA de Sheetgo sont une solution pratique pour consolider les données de différents fichiers dans une seule feuille. Le meilleur, c'est que Sheetgo fait tout le travail à votre place.
Si d'autres méthodes pour fusionner et combiner les données de vos feuilles de calcul vous intéressent, ces articles vous seront utiles :
English 
Español 
Português do Brasil 
Français