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

Publié le 17 octobre 2012
par Sophie Marchand M.Sc.

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. On débute ici avec la consolidation par position dans Excel.

 

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 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, 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!

 

CFO-Masque_Formations-en-ligne_FBLa 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é.

1 réflexion sur “Excel: Consolidez vos données (1 de 3)”

  1. Ping : Coursinfo (zivava) | Pearltrees

Laisser un commentaire

Votre adresse e-mail ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Retour en haut