L’organisation du travail est parfois organisé de sorte à ce que les données sont éparpillées sur différents tableaux, feuilles voire classeurs Excel.
Or, pour une analyse approfondie, il est parfois nécessaire de centraliser toutes les données. On appelle cela, la consolidation.
Nous verrons les différentes façons de consolider vos données.
Utiliser des fonctions Excel
C’est tout bête mais on peut déjà faire de la consolidation sans s’en rendre compte. Plusieurs fonctions peuvent être utilisées.
Les fonctions de recherche
On l’utilise souvent dans certaines fichiers pour ajouter une information à un tableau. Parmi elles, il y a :
Elle est surtout utilisée pour ajouter une nouvelle information (colonne) principalement de format informative (texte, date, nombre…)
La fonction SOMME
Je vous le dis tout de suite, ce n’est pas la plus pratique à mettre en action.
De plus, elle n’a qu’un seul but : additionner des valeurs numériques. Par exemple, elle servira à additionner les ventes qui sont dans des tableaux séparés.
Il faut d’abord ressaisir les en-têtes de ligne et colonne. Ensuite, pour les cellules contenant les ventes, saisir la formule suivante :
Si les cellules dans les différentes feuilles n’ont pas les mêmes références :
=SOMME(NomFeuille1!PlageCellules1;NomFeuille2!PlageCellules2;...;NomFeuilleN!PlageCellulesN)
=SOMME(ventesT1!A2;ventesT2!B5;ventesT3!B1;ventesT4!C3)
Si les cellules dans les différentes feuilles ont les mêmes références :
=SOMME(NomFeuille1:NomFeuilleN!PlageCellules)
=SOMME(ventesT1:ventesT4!D2:F2)
Consolider
Sur Excel, il existe une fonctionnalité dédiée à la centralisation des données : consolider.
Préparation
Il y a certaines règles afin que la consolidation se passe sans problème :
Chaque plage de données est au format liste : chaque type d’information représente une colonne, les occurrences représentent les lignes.
Chaque colonne doit avoir une étiquette (en-tête) dans la première ligne.
Il ne doit y avoir aucune ligne ou colonne vide.
Chaque plage a la même disposition.
Mise en pratique
En premier lieu, il vous faut un espace vierge pour accueillir la consolidation. Je vous conseille de créer une nouvelle feuille à cet effet.
- Dans le ruban Excel, allez sur l’onglet Données puis Consolider dans le groupe Outil de données
2. Dans la zone Fonction, sélectionnez la fonction de synthèse que vous souhaitez qu’Excel utilise pour consolider les données. Par défaut, la fonction est SOMME. Vous pouvez laisser tel quel.
3. Ensuite, dans la zone Référence , sélectionnez la plage de cellules du 1er tableau que vous voulez consolider. N’oubliez pas d’incorporer les en-têtes. Cliquez sur Ajouter.
4. Répétez l’opérations pour ajouter toutes les plages de données prévues pour la consolidation.
5.Si vous souhaitez copier les en-têtes des sources de données dans le tableau de consolidation, cochez les 2 cases de la rubrique Étiquettes dans
6.Si vous souhaitez qu’Excel mette automatiquement à jour votre table de consolidation lorsque les données sources changent, cochez simplement la case Lier aux données source .
7. Lorsque tout est prêt, cliquez sur OK
Vous voyez que le tableau de consolidation est composé de lignes groupées.
Pour afficher/masquer les détails de chaque tableau source, il suffit de cliquer sur les plus à gauche de la feuille.
Tableau croisé dynamique
Une autre manière de consolider plusieurs sources de données est de créer un tableau croisé dynamique (TCD).
Préparation
Il y a certaines règles à respecter :
- Toutes les sources doivent être dans le même classeur Excel.
- Les sources doivent comporter au moins un champ en commun (puisque c’est le but d’un tableau croisé)
- Chaque source doit être sous forme de tableau structuré..
Mise en pratique
Dans le ruban Excel, rendez-vous sur l’onglet Insertion puis cliquez sur Tableau croisé dynamique.
Dans le 1er champ, saisissez le nom d’un des tableaux structurés.
Cochez la case Ajouter ces données au modèle de données puis cliquez sur OK.
- Dans le panneau latéral, sélectionnez les champ à afficher (ça a peu d’importance). Personnellement, j’ai tout sélectionné
- Dans ce même panneau, cliquez sur l’onglet Tous. Vous verrez les différents tableaux enregistrés sur ce fichier. Sélectionner un champ de la 2ème source de données à consolider.
- Un message sous fond jaune apparaît : Excel explique qu’une relation est nécessaire. Cliquez sur Créer.
Sur la 1ère ligne, sélectionnez la 1ère source de données et le champ en commun.
Sur la 2nde ligne, sélectionner la 2e source de données ainsi que le champ en commun.
Validez en cliquant sur OK.
Et nous voici avec un tableau croisé dynamique contenant des données de différentes sources :
- Tableau (source) Ventes : Pays, Boutique, Internet, Total
- Tableau (source) Pays : Pays, CodePays.
