Pierre Fauconnier

Salut.

La meilleure organisation des données pour permettre l'analyse avec Excel est la table de données ou le tableau structuré (Si cela ne vous parle pas trop, voici mon tuto pour prendre en main cet outil indispensable d'Excel). Cet agencement de données permet d'utiliser tous les outils d'analyse d'Excel:

• Tableaux croisés dynamiques (TCD);
• Graphiques croisés dynamiques;
• Fonctions d'ensemble (SOMME.SI.ENS, MOYENNE.SI.ENS,NB.SI.ENS, mais aussi, en XL365, MAX.SI.ENS, MIN.SI.ENS);
• Formules matricielles, parmi lesquelles SOMMEPROD.

Encore faut-il que les données, même sous tableau structuré ou table de données, soient correctement agencées, et ce n'est pas toujours le cas, notamment lorsque l'on reçoit un xls(x) généré par un ERP ou un CRM. En effet, les données sont parfois prémachées et préanalysées, rendant leur exploitation délicate, notamment par TCD.


Voici un tableau tel qu'on pourrait le recevoir, avec la mission de comparer les années entre elles.



En l'état, c'est mission impossible, sauf à créer des formules fragiles et liées à la structure même du tableau. On comprend aisément que l'ajout de la colonne 2021 va nous obliger à modifier nos formules.



En fait, les années devraient faire partie des données du tableau au sein d'une colonne Année. Il serait donc bien plus intéressant de pouvoir travailler sur le tableau suivant:



On remarque ici que les années ont été considérées comme une donnée d'analyse. Nous avons donc un tableau avec moins de colonnes, et plus de lignes, puisque chaque ligne a été répétée par colonne d'année à traiter. D'un tableau de 5 lignes et 4 colonnes d'années, nous sommes passés à un tableau de 20 lignes et une colonne d'année. Cette organisation va permettre une analyse rapide, notamment par TCD.


Comment passe-t-on du premier tableau au second? Un traitement manuel serait beaucoup trop long, car il faudrait transposer les lignes une à une, ce qui sera déjà laborieux pour notre tableau de 5 lignes, mais qui sera inimaginable pour un tableau de plusieurs milliers de lignes.

Power Query, disponible depuis 2010 en Addin et intégré à Excel depuis 2016, permet de réaliser cela en quelques clics, les actualisations de la requête Power Query permettant d'intégrer automatiquement les nouvelles colonnes d'année...

Comment pratiquer cette transformation avec Power Query?

1. Intégration du tableau dans Power Query

Sélectionner une cellule du tableau de départ et ouvrir Power Query en y intégrant nos données.





2. Intégration du tableau dans Power Query

Power Query s'ouvre et nous présente notre tableau ainsi que les étapes déjà réalisées lors de l'importation, à savoir:

• l'acquisition de la source;
• la modification des types de données lorsque Power Query a pu les identifier (sur base des premières lignes de la table).

Power Query travaille avec trois volets:

• à gauche, les requêtes (tableaux source, copies et références);
• à droite, les étapes appliquées sur la requête sélectionnée;
• au milieu, la situation des données en fonction de l'étape sélectionnée, les données étant présentées après la réalisation de l'étape.

3. Dépivotage des colonnes

Transformer le tableau 1 en tableau 2 revient à dépivoter les colonnes d'année. Pour cela, nous allons les sélectionner puis, par clic droit ou via le ruban, dépivoter les colonnes^(*).



Le résultat du dépivotage correspond à nos attentes. Les colonnes d'années ont été transformées en données. Power query a répété les colonnes fixes et intégré les données dans des nouvelles lignes.



On remarquera deux choses:

• Power Query a nommé automatiquement les colonnes en Attribut et Valeur;
• Power Query a en fait mémorisé qu'il devait dépivoter les autres colonnes que les fixes (Table.UnpivotOtherColumns), ce qui est intéressant car nous comprenons ainsi que les années ne sont pas énumérées lors du dépivotage, mais que ce sont les colonnes fixes qui le sont. Cela permettra de dépivoter les nouvelles années ajoutées à la droite du tableau source par simple actualisation de la requête (clic droit ou ruban).

Vous pouvez

• Renommer les colonnes à cette étape en modifiant les noms (solution qui a ma préférence);
• ajouter une étape pour les renommer.

4. Restituer les données dans Excel^(**)

On restituera les données dans Excel via Accueil > Fermer et charger pour récupérer le tableau correctement formé et prêt pour l'analyse.





Lorsque, par la suite, nous récupérerons un nouveau tableau avec une année supplémentaire, il suffira d'actualiser la requête dans Excel pour que la nouvelle colonne soit dépivotée elle aussi (souvenez-vous, le dépivotage concerne, par défaut, les autres colonnes que les fixes!)






(*) Les commandes "Dépivoter les colonnes" et "dépivoter les autres colonnes" aboutissent à la même étape: Table.UnpivotOtherColumns. Ce sont donc les colonnes fixes qui sont énumérées. Cela est intéressant, comme dit plus haut, car les nouvelles années seront prises en compte. C'est la sélection des colonnes de départ qui oriente votre choix:

• vous sélectionnez les colonnes fixes (prénom et nom) et vous dépivoter les autres colonnes;
• vous sélectionnez les colonnes d'années (2017 à 2020) et vous dépivotez les colonnes.

Une troisième option (Dépivoter uniquement les colonnes sélectionnées) permet d'énumérer les colonnes à dépivoter, mais l'ajout de colonnes ne sera alors pas pris en compte lors du dépivotage.




(**) Il est possible de ne pas ramener le tableau dans Excel, par exemple s'il ne sert qu'à créer un TCD. On peut alors appuyer directement le TCD sur la requête Power Query, grâce par exemple au panneau de droite (Requêtes et connexions) (Clic droit sur la requête souhaitée, Charger dans et choisir tableau croisé dynamique)

• Ce billet vous parle-t-il?
• Trouvez-vous ici une solution à un problème auquel vous êtes confronté régulièrement?
• Connaissez-vous Power Query?
• Souhaitez-vous d'autres billets ou des tutos sur ce complément d'Excel qui, à mes yeux, devient incontournable?