Vous ne devinerez jamais comment j’ai boosté mes analyses Excel en 5 minutes !
Dans ce tutoriel, je vais vous montrer comment transformer un tableau de ventes mensuelles en un format exploitable et dynamique, et ensuite exploiter ces données avec des formules Excel et quelques astuces pratiques. Nous allons partir d’un tableau initial difficile à analyser, le transformer avec Power Query en utilisant la fonctionnalité d’dépivotage, puis réaliser des analyses avancées à l’aide de formules et même d’une macro simple.
Téléchargement
Vous pouvez télécharger le fichier d'exemple de cet article en cliquant sur le lien suivant :
Tutoriel Vidéo
1. Présentation
Pour illustrer ce tutoriel, nous allons pouvoir utiliser le tableau suivant dans lequel sont répertoriées les ventes mensuelles de 20 produits différent sur l’ensemble des 12 mois de l’année.
Chaque ligne représente un produit et chaque colonne « Ventes [Mois] » indique le chiffre d’affaires réalisé durant le mois correspondant.
Ce format tableau est dit « large » car il comporte une colonne par mois, ce qui complique l’analyse transversale sur l’année entière.
Notre objectif est donc de transformer ce tableau en un format « allongé » où chaque ligne correspondra à un seul enregistrement avec trois colonnes : « Produit », « Mois » et « Ventes ».
Cette transformation facilitera grandement la création de graphiques, l’utilisation de tableaux croisés dynamiques et le calcul de statistiques comme la somme des ventes par produit ou par mois.
Avant de poursuivre, nous nous assurons que notre tableau est bien converti en tableau Excel. Pour cela, nous sélectionnons toutes nos données puis nous appuyons sur [Ctrl]+[L]. Une fenêtre s’ouvre, nous vérifions que la case « Mon tableau comporte des en-têtes » est cochée et nous validons en cliquant sur « Ok ».
Cette étape nous permettra d’importer le tableau facilement dans Power Query.
2. Transformation des données avec Power Query
Cette transformation, souvent appelée « dépivotage », va nous permettre de convertir les 12 colonnes des mois en deux colonnes distinctes.
Nous commençons par nous rendre dans l’onglet « Données » et nous cliquons sur le bouton « À partir d’un tableau/plage ».
La fenêtre de Power Query s’ouvre et nous affiche notre tableau avec ses en-têtes. Nous vérifions que les colonnes sont correctement reconnues : « Produit » et « Ventes [Mois] ».
La première étape consiste à conserver la colonne « Produit » telle quelle, car elle identifie nos enregistrements.
Nous sélectionnons l’en-tête « Produit ».
Ensuite, nous nous rendons dans l’onglet « Transformer » et nous choisissons l’option « Dépivoter des autres colonnes ».
Cette opération va convertir toutes les colonnes restantes (celles des mois) en deux colonnes nommées par défaut « Attribut » et « Valeur ».
La colonne « Attribut » contiendra les noms des mois, et la colonne « Valeur » affichera les chiffres de ventes correspondants.
Une fois le dépivotage effectué, nous renommerons la colonne « Attribut » en « Mois » en cliquant dessus puis en choisissant « Renommer » et en tapant « Mois ».
De même, nous renommons la colonne « Valeur » en « Ventes ».
Il est important de vérifier que la colonne « Ventes » est bien définie comme un type numérique pour que nos calculs ultérieurs soient corrects.
Pour cela, nous cliquons sur l’icône à côté du nom de la colonne et nous sélectionnons « Nombre entier » ou « Nombre décimal » selon le besoin.
Nous pouvons également appliquer un formatage pour rendre nos données plus lisibles, par exemple en ajoutant un séparateur de milliers.
Une fois satisfait de la transformation, nous cliquons sur « Fermer & Charger » pour importer notre tableau transformé dans une nouvelle feuille Excel.
Nous obtenons ainsi un tableau « allongé » avec trois colonnes : « Produit », « Mois » et « Ventes ».
Ce format nous permettra désormais d’effectuer des analyses plus dynamiques et plus flexibles.
Cette flexibilité fait de Power Query un outil incontournable pour la transformation de données dans Excel.
3. Exploitation des données transformées avec Excel
À présent, nous allons exploiter notre tableau transformé en utilisant des formules Excel et une macro simple pour automatiser certaines tâches. Grâce au format « allongé », nous pouvons facilement calculer des totaux et créer des tableaux croisés dynamiques.
Nous commençons par créer un récapitulatif des ventes par produit. Par exemple, si nous souhaitons obtenir le total des ventes pour le produit « Chaussures », nous nous plaçons dans une nouvelle cellule et nous écrivons la formule suivante :
=SOMME.SI.ENS(C:C;A:A;"Chaussures")
Ici, la plage C:C correspond à la colonne « Ventes », la plage A:A correspond à la colonne « Produit » et le critère « Chaussures » nous permet de sommer uniquement les ventes associées à ce produit. Nous pouvons copier cette formule pour tous les produits de notre récapitulatif, ce qui nous permet d’avoir une vision claire de la performance de chaque article.
Nous pouvons également calculer le total des ventes par mois. Par exemple, pour totaliser les ventes du mois de janvier, nous écrivons :
=SOMME.SI.ENS(C:C;B:B;"Ventes Janvier")
Dans cette formule, la plage B:B correspond à la colonne « Mois ». Il est essentiel que les intitulés de la colonne « Mois » correspondent exactement aux critères utilisés dans nos formules. Nous pouvons ainsi créer un tableau de bord qui nous affiche rapidement les ventes mensuelles ou annuelles.
Pour une analyse encore plus dynamique, nous pouvons insérer un tableau croisé dynamique.
Nous sélectionnons notre tableau transformé, puis nous nous rendons dans l’onglet « Insertion » et nous cliquons sur « Tableau croisé dynamique ».
Dans la fenêtre qui s’ouvre, nous choisissons de placer le tableau sur une nouvelle feuille.
Une fois le tableau croisé dynamique créé, nous glissons la colonne « Produit » dans la zone des lignes et la colonne « Mois » dans la zone des colonnes, tandis que la colonne « Ventes » se place dans la zone des valeurs.
Ce tableau vous permettra de visualiser instantanément les ventes par produit et par mois, et de filtrer selon vos besoins.