Contact: 514-605-7112 / info@lecfomasque.com

Excel: Consolidez vos données (1 de 3)

Publié le : 17 octobre 2012

Il y a quelques temps, j’avais publié sur ce blogue un billet intitulé Excel : La fonction Indirect pour créer un sommaire exécutif en un clic de souris et un autre billet intitulé Excel: Additionner plusieurs onglets en un clic de souris  Suite à la diffusion de ces deux articles, Marc Maisonneuve, un lecteur, m’a demandé pourquoi je n’utilisais pas simplement la fonctionnalité « Consolidation » (fonctionnalité que je n’avais jamais utilisée auparavant!). Cette série de 3 billets servira donc à démontrer comment utiliser cette fonctionnalité (grâce à l’apport de Marc Maisonneuve), dans quels contextes la fonctionnalité Consolidation peut être intéressante et dans quels contextes, elle l’est beaucoup moins.

.

LA FONCTIONNALITÉ CONSOLIDATION DANS EXCEL

.

La consolidation consiste à créer un regroupement de données pour en faire une synthèse. On peut utiliser la consolidation par position ou par catégorie.

.

  • Par position : 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. Il est à noter que les différents tableaux peuvent se retrouver dans le même fichier ou dans des fichiers séparés.

.

  • Par catégorie : Cette consolidation peut être utilisée lorsque les données ne sont pas placées au même endroit (colonne et ligne) ou lorsque les étiquettes (de lignes et de colonnes) ne sont pas placées au même endroit.

.

  • Il est aussi possible aussi d’utiliser les tableaux croisés dynamiques pour faire cette consolidation.

.

CONSOLIDER PAR POSITION

.

Avant de voir les étapes à suivre pour procéder à la consolidation par position, voici quelques points importants.

.

.

  • Remarquez que sur les feuilles Magasin 1 à 3, les tableaux se retrouvent dans les mêmes cellules sur chacune des feuilles. Aussi, les étiquettes se retrouvent aux mêmes endroits dans chacune des feuilles.

.

Magasin 1

.

Magasin 2


.

Magasin 3

.

  • Dans la feuille Sommaire, on a recréé le même tableau. C’est dans cette feuille que l’on procédera à la consolidation.

.

Sommaire

.

Voici les étapes à suivre pour créer votre consolidation par position :

.

  • Allez dans la feuille Magasin 1 et sélectionnez les données monétaires (cellule B4 à B10)

.

  • Ensuite, dans la zone Nom, en haut à gauche, écrivez mag_1 (pour magasin 1) et appuyez sur la touche Enter de votre clavier. (si vous le souhaitez, vous pouvez revoir comme 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, pour la feuille magasin 2, vous nommerez la plage mag_2, et ainsi de suite.

.

  • Allez dans la feuille Sommaire.

.

  • Sélectionnez la cellule B4.

.

  • Choisissez l’onglet Données (Data), groupe Outils de données (Data tools) et la commande Consolider (Consolidate).

.

.

Dans la fenêtre suivante:

.

.

  • Dans le champ Fonction (Function) : Choisissez la fonction désirée.

.

  • Dans la zone Référence (Reference): Écrivez mag_1 et cliquez sur le bouton Ajouter (Add). Ensuite, écrivez mag_2 et cliquez sur Ajouter (Add) et ainsi de suite jusqu’à tant que toutes les zones à consolider soient inscrites.

.

  • Cochez Lier aux données sources (create Links to source data).

.

.

  • Cliquez sur le bouton OK. Le résultat devrait alors être le suivant.

.

.

En plus de créer le sommaire, Excel créera un plan (groupé par défaut). Si l’on clique sur les boutons + à gauche, Excel indiquera la valeur qui est inscrite dans chacun des magasins. Comme ceci:

.

.

Malheureusement, Excel n’écrit pas dans la colonne A le nom de la feuille. Si les feuilles avaient été sur différents fichiers, alors là, ils les auraient inscrits.

.

MES PREMIÈRES IMPRESSIONS

.

Ceci est un premier billet d’une série de 3. Je tâcherai de faire mes commentaires au fur et à mesure. Mes premières impressions sont bonnes. 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. Le tout n’est pas automatisé.

.

Pour les 2 raisons mentionnées ci-haut, je n’utiliserais la fonctionnalité que dans des cas où la structure du fichier ne serait pas appelée à changer et où le nombre de feuilles dans le fichier ne serait pas trop élevé.

.

EXCEL: CONSOLIDEZ VOS DONNÉES (2 de 3)

EXCEL: CONSOLIDEZ VOS DONNÉES (3 de 3)

.

Remerciements

Merci à Marc Maisonneuve de nous avoir présenté la fonctionnalité Consolidation dans Excel. Ne manquez pas la suite!

.


NOTRE CATALOGUE DE FORMATIONS


Catalogue de formations

Le CFO masqué vous offre 13 formations, réparties dans 4 catégories: Tableaux de bord, Modélisation financière, Finance corporative et Analyse et modélisation de données. Ces formations sont offertes en classe, en entreprise et en ligne. Nos formateurs sont des experts dans leur domaine et sont accrédités par Emploi-Québec et vous remettent un certificat, à la fin de chaque formation, que vous pouvez notamment utiliser pour faire reconnaître des heures de formation continue auprès de votre ordre professionnel.

Pour info: 514-605-7112 ou info@lecfomasque.com

Téléchargez notre catalogue

Sophie Marchand, M.Sc., CPA, CGA, MVP

Instigatrice du CFO masqué, Sophie Marchand est détentrice d’une M.Sc. en finance corporative, d’un titre comptable CPA, CGA et d’un titre MVP (Most valuable professional) Excel de Microsoft, et cumule plus de 12 années d’expérience dans le milieu des affaires. Elle se spécialise particulièrement en modélisation financière et en intelligence d’affaires. À ce titre, elle développe des modèles financiers rigoureux, des tableaux de bord sophistiqués et des outils de gestion performants. Elle offre ses services en tant que consultante, formatrice et conférencière.

Écrire un commentaire


  • *