Le présent article fait suite à l’article : EXCEL: CONSOLIDEZ VOS DONNÉES (1 de 3). Dans ce premier article, nous avions vu comment consolider des données par position. Dans le présent article, nous verrons plutôt comment procéder à la consolidation des données par catégorie.
CONSOLIDER PAR CATÉGORIE
Avant de voir les étapes à suivre pour consolider par catégorie, voici quelques points importants:
- Ouvrez le fichier Consolidation_par_catégorie.
- Remarquez que sur les feuilles Bureau 1 à 3, les tableaux ne se retrouvent pas dans les mêmes cellules pour chacune des feuilles. Aussi, les étiquettes ne se retrouvent pas aux mêmes endroits dans chacune des feuilles.
Bureau 1
Bureau 2
Bureau 3
- Dans la feuille sommaire, remarquez qu’il n’y a rien d’inscrit et c’est correct ainsi. C’est dans cette feuille que l’on créera la consolidation.
Voici les étapes à suivre pour créer votre consolidation par catégorie :
- Allez dans la feuille Bureau 1 et sélectionnez les données du tableau (cellule A3 à E8).
- Ensuite, dans la zone Nom, en haut à gauche, écrivez bur_1 (pour Bureau 1) et appuyez sur la touche Enter de votre clavier (si vous le souhaitez, vous pouvez revoir comment nommer des plages de cellules dans Excel en relisant l’article Astuce Excel: Nommer une plage de cellules).
- Refaites les étapes 1 et 2 pour chacune des feuilles à consolider. Bien sûr, vous nommerez la feuille Bureau 2, bur_2, et ainsi de suite.
- Allez ensuite dans la feuille Sommaire
- Sélectionnez la cellule A1.
- Choisissez l’onglet Données, groupe Outils de données et la commande Consolider.
- Dans le champ fonction : Choisissez la fonction désirée.
- Dans la zone référence : Écrivez bur_1 et cliquez sur le bouton Ajouter. Ensuite, écrivez bur_2 et cliquez sur ajouter et ainsi de suite jusqu’à ce que toutes les zones à consolider soient inscrites.
- Comme les différents tableaux ne présentent pas les mêmes données aux mêmes endroits et dans la même position (colonne et ligne) il faut absolument que vous cochiez “Étiquette” (Use labels in) dans : Ligne du haut et colonne de gauche. Ceci aura pour effet de demander à Excel de trouver lui-même les étiquettes pour additionner les bons chiffres.
- Cochez aussi Lier aux données sources. La feuille sommaire devrait ressembler à ceci.
Remarquez qu’Excel laisse une colonne vide. Si vous cliquez sur le symbole +, vous remarquerez qu’Excel a écrit le nom du fichier.
Ne manquez pas la suite!
EXCEL: CONSOLIDEZ VOS DONNÉES (3 de 3)
MES PREMIÈRES IMPRESSIONS
Encore une fois, je trouve cette fonctionnalité de consolidation par catégorie intéressante et performante mais un peu longue à mettre en place. Je crois qu’elle peut être utile dans le cas de modèles “mal structurés”. Toutefois, je recommande plutôt de veiller à construire des modèles selon les meilleures pratiques d’affaires, ce qui, dans tous les cas, permettra aux utilisateurs de vos modèles de mieux comprendre le flot des données du modèle et évitera les risques d’erreurs. De plus, je n’aime pas le fait que la fonctionnalité ne tienne pas compte des éventuels changements qui pourraient être apportés au modèle (ajout de lignes, de colonnes, de tableaux, …). La fonction Index/Match, dans ce cas, sera plus utile, plus rapide à mettre en place et plus facile à mettre à jour.
Remerciements
Merci à Marc Maisonneuve de nous avoir présenté la fonctionnalité Consolidation dans Excel. Merci aussi à Martin Le Comte d’avoir aussi participé à évaluer ces 2 fonctionnalités avec moi.
La mission du CFO masqué est de développer les compétences techniques des analystes et des contrôleurs de gestion en informatique décisionnelle avec Excel et Power BI et favoriser l’atteinte de leur plein potentiel, en stimulant leur autonomie, leur curiosité, leur raisonnement logique, leur esprit critique et leur créativité.
Ping : EXCEL: CONSOLIDEZ VOS DONNÉES (2 de 3) | Bienvenue à BM Formation
Bonjour,
bravo pour vos articles.
Cela permet rapidement de comprendre les notions d’Excel.
Juste petite remarque, vous écrivez :
“Voici les étapes à suivre pour créer votre consolidation par position :”
je pense qu’il faut remplacer “position” par “catégorie”, une erreur de copier-coller en reprenant la phrase de l’article précédent sûrement 🙂
Bonne journée
Merci pour vous m’aiderez trop je vous remerciez 😉
Bonjour
Je voudrais apporter quelques remarques à cette série d’articles sur la “Consolidation”.
Même s’il arrive que l’on trouve des tableaux qui sont présentés à des endroits différents dans des feuilles différentes (comme expliqué dans cet article), il existe un intérêt plus important à la fonctionnalité “Consolidation”.
En effet, les tableaux peuvent démarrer au même endroit dans chaque feuille, mais ne pas avoir le même nombre de lignes ou de colonnes chacun (le bureau 3 peut ne pas vendre de serveur, mais vendre des câbles, les bureaux 1 et 2 peuvent vendre des souris en plus, le bureau 2 peut aussi vendre des souris et du papier, le bureau 3 peut n’avoir ouvert que lors du trimestre 2, …). Et là, la fonctionnalité “Consolidation” va trouver son intérêt car elle va pouvoir faire la somme par Catégorie, même si elles ne sont pas présentes partout.
J’ai plusieurs fois rencontré des personnes qui ont pleuré (oui, réellement) de joie en découvrant cette fonctionnalité car cela leur soulageait plusieurs journées de travail.
D’un côté pratique, il n’est pas nécessaire de nommer chacun des tableaux en amont, car cela ajoute effectivement de la lourdeur dans le processus. De plus, après avoir validé la première référence, quand on veut ajouter la suivante, Excel propose par défaut la même plage dans la nouvelle feuille (et cela correspond souvent). Cela permet d’aller très vite pour les sélections de référence.
Enfin, si les données changent dans les tableaux en amont, il suffit de retourner dans la feuille de résultat puis de recliquer sur le bouton “Consolidation”, Excel va reproposer directement les références précédentes, il ne reste plus qu’à valider. C’est quasiment aussi rapide que l’actualisation dans les TCD 😉
Pour conclure, je trouve cette option plus intéressante que de bâtir des formules à la main. Néanmoins, il vaut quand même mieux avoir les données dans un seul tableau de données au départ et de créer un TCD. C’est qui est le plus rapide et le plus fiable.
En espérant que cela puisse servir à quelqu’un
Pierre
Bonjour,
Merci pour votre commentaire. Cet article a été publié en 2012 et il s’agit d’un article partagé par un de nos lecteurs. De notre côté, nous n’utilisons pas cette fonctionnalité, surtout plus en 2019 où Power Query peut faire ces choses beaucoup plus simplement et avec beaucoup plus de performance.
Nous recommandons d’ailleurs à tous nos lecteurs d’utiliser plutôt Power Query pour ce genre de tâches.
Au plaisir,
Sophie