De plus en plus, sur le forum et sur le blogue du CFO masqué, je remarque des questions d’usagers qui ne devraient pas être posées. Détrompez-vous! Il ne s’agit pas de questions indécentes ou rien qui ne soit inacceptables à partager. Elles ne devraient pas être posées, à mon avis, simplement parce que l’usager n’aurait pas dû se retrouver dans cette situation au départ. L’usager n’a simplement pas suivi les meilleures pratiques pour effectuer son analyse de données dans Excel.
Errer dans son analyse de données dans Excel
Excel permet certes aux usagers une grande liberté mais malheureusement, plusieurs s’en servent pour creuser leur propre tombe, sans jamais vraiment s’en rendre compte. Alors, il ont passé à travers une longue démarche qui les a menés de A à B, jusqu’à Y, où là, il ne trouvent toujours pas la solution à leur problème et ils ont nettement l’impression que si on leur disait comment compléter l’étape Z, ils pourraient enfin régler leur problématique. Le problème c’est que depuis le point A, le point B aurait dû être tout autre et la solution aurait dû prendre une autre trajectoire et apparaître au point C. Ça signifie que du point D au point Y, l’usager a perdu royalement son temps, pour finalement se retrouver dans un contexte où la résolution est loin d’être simple. C’est la raison pour laquelle ça me laisse plutôt songeuse quand j’entends des professionnels dire qu’ils n’ont “pas le temps” de suivre des formations. Ironiquement, je comprends! Si vous êtes constamment pris dans de tels labyrinthes, ça doit certainement gober tout votre temps. Les formations sont justement là pour vous montrer les bonnes pratiques et vous éviter d’être prisonniers des rabbits holes que vous creuser vous-mêmes parfois. Je ne saurais trop vous recommander de prendre vos distances de vos méthodes actuelles pour aller chercher les meilleures pratiques auprès des experts. Et ça tombe bien, Le CFO masqué se concentre exclusivement à former les professionnels en Excel et Power BI.
Cet article porte plus précisément sur une question qui a été posée sur notre forum et qui aurait très bien pu porter le titre Pourquoi personne n’ose répondre à vos questions sur les forums… L’idée, vous l’aurez compris, n’est certainement pas de ridiculiser qui que ce soit. Bien au contraire. Je réponds ici de façon tout à fait bénévole à la question posée sur le forum et je le fais avec soin. Mon désir est donc d’aider cet usager et comme je sais que cet usager est loin d’être unique, c’est aussi une main tendue vers tous les autres, qui se reconnaîtront dans cet article. Comme dans plusieurs autres sphères de la vie, la première étape ici est d’admettre que vous avez besoin d’aide.
Il faut aussi réaliser que pour avancer, dans Excel, il vous faudra éventuellement développer vos compétences non seulement techniques mais aussi conceptuelles et cela se fera notamment en pratiquant et en s’inspirant des experts.
Données de départ de l’analyse de données dans Excel
Le fichier fourni par l’usager du forum présente une page sommaire avec les données ci-dessous. En gros, l’usager tente de calculer le nombre de courriels reçus (colonne D) et le nombre de courriels envoyés (colonne F), par mois, pour les adresses spécifiées dans la colonne B. En ce moment, ces formules vont chercher l’information dans deux onglets spécifiques mais l’usager aimerait pouvoir faire varier le nom de ces onglets, dans la colonne B, pour ajuster les calculs en conséquence.
Le fichier possède 4 onglets, deux onglets pour les réceptions, qui se terminent par R et deux onglets pour les envois, qui se terminent par E. Mais l’idée serait aussi de pouvoir ajouter d’autres onglets de la sorte aisément.
Formule de départ (spaghetti)
Il faut admettre que cette formule n’est pas compliquée comme telle mais pour un usager qui n’est pas payé pour la comprendre, c’est quand même le genre de formule repoussante ;).
Vous avez des sources de données volumineuses à rassembler et analyser dans Excel ? Apprenez à automatiser tout le processus d’importation, de transformation et de modélisation des données avec nos formations sur Power Query et Power Pivot dans Excel. |
|---|
Autopsie d’un spaghetti ou d’une analyse de données erronée dans Excel
Pour y voir un peu plus clair, j’ai simplement brisé la formule, pour mieux la comprendre (à l’aide des touches ALT+ENTER). Ce qui m’a d’abord sauté aux yeux, c’est qu’il ne s’agit pas d’une formule unique pour toute la colonne des réceptions de courriels ni une formule unique pour toute la colonne des envois de courriels. Il s’agit plutôt de formules différentes, dans chacune des cellules. Ceci est une infraction majeure au code des bonnes pratiques Excel. Plus précisément, on voit que les références aux adresses courriels (B5 et B6) n’utilisent pas les signes de dollars, donc, impossible de copier/coller une telle formule pour réutiliser ces cellules de référence. Ensuite, on remarque que les intervalles de dates sont inscrits à la main au lieu de référer aux mois inscrits dans les cellules. Je comprends que dans la formule, on inscrit une valeur de type datetime (ce qui correspond au format des données fournies dans les onglets) et que dans le sommaire, on inscrit les mois en lettres, mais il y aurait moyen d’ajouter deux colonnes avec les dates des intervalles dans le sommaire et y faire référence dans les formules. Cela permettrait de créer une seule formule pour toute la colonne des envois et une seule formule pour toute la colonne des réceptions.
Pour mieux comprendre à quoi réfèrent toutes ces plages dans les fonctions NB.SI utilisées par l’usager, je suis allée dans les onglets et c’est là que j’ai découvert que les données étaient présentées sous forme de tableaux. Et quand c’est le cas, je ne vois aucune bonne raison de ne pas en profiter.
J’ai donc remplacé les références de plages à des références cellulaires par des références à des noms de tableaux et de colonnes. Ça m’a enfin permis d’y voir plus clair:
On comprend mieux ici que l’usager compte le nombre de “transactions” pour une première adresse courriel (le premier nb.si de la formule), pour une période de temps déterminée, en excluant les éléments supprimés et les couriers indésirables. On voit qu’elle refait la même chose pour la deuxième adresse courriel (le deuxième nb.si de la formule). On le fait une fois avec les courriels reçus et une fois avec les courriels envoyés.
Pour en savoir un peu plus sur les données mises sous forme de tableau Ce qu’il faut savoir sur les formules appliquées à des données sous forme de tableau.
Utiliser une solution appropriée pour effectuer cette analyse de données dans Excel
Pendant longtemps, les usagers d’Excel ont été privés d’engins de transformation et de modélisation de données comme Power Query et Power Pivot, ce qui les a forcés à devenir très créatifs et très aventuriers en termes de formules Excel. Depuis 2012, on a des solutions beaucoup plus robustes à portée de main. Avec Power Query et Power Pivot, les usagers n’ont plus à risquer des formules abracadabrantes. C’est pourquoi dans notre exemple ici, je ne recommanderai pas à l’usager du forum une formule, tel que recherché. Ce serait encourager cet usager à maintenir ses mauvaises habitudes qui, soyons francs, ne sont pas seulement sujettes à des risques d’erreurs considérables mais doivent également manger une quantité de temps incroyable.
C’est pourquoi, je propose une solution avec Power Query, qui existe depuis maintenant 7 ans et qui est encore inconnue de la grande majorité des usagers Excel. Si vous vous êtes le moindrement reconnu dans cet article, jusqu’ici, et que vous n’avez pas encore touché à Power Query (ou si peu), il faut vite y remédier. Power Query est la solution qui va faire TOUTE la différence dans votre usage d’Excel. Je vous invite d’ailleurs à suivre notre formation Excel – Introduction à Power Query et au langage M.
Démarche de résolution dans Power Query
La première chose à faire est d’importer l’ensemble des tables comprises dans les différents onglets, dans Power Query. Pour ce faire, il faut aller dans le menu Données et cliquer sur le bouton À partir d’un tableau. Une fois que vous avez importé toutes les tables dans Power Query, il faudrait ajouter une colonne personnalisée avec le nom de l’onglet, tel que présenté ci-dessous:
Si vous utilisez déjà Power Query, vous savez sans doute qu’avec un peu de langage M, il serait possible de récupérer tous les onglets du fichier ainsi que les noms des onglets automatiquement. Cette méthode est d’ailleurs enseignée dans notre formation. Mais pour la résolution du problème actuel, nous allons nous concentrer sur des étapes très simples, sans usage du langage M.
La deuxième étape serait d’ajouter les tables bout à bout à l’aide de l’option Ajouter comme une nouvelle requête. Ceci va permettre de concevoir une seule grosse table avec toutes les données du fichier. Notez qu’il serait aussi possible de créer une table conso pour les envois et une autre pour les réceptions de courriels.
Dans cette table conso, il pourra s’avérer utile d’ajouter des colonnes de dates, pour déterminer, par exemple, les dates et les mois des courriels envoyés et reçus. On s’assurera également ici d’apposer un filtre sur la colonne Folder Path pour ne pas considérer les Éléments supprimés et indésirables.
Pour ce faire, il faut simplement utiliser l’option Date du menu Ajouter une colonne.
Lorsque la table conso sera terminée, on pourra s’y référer (bouton de droit référence) pour créer d’abord une table pour les réceptions de courriels et ensuite on répétera la procédure pour les envois de courriels. Dans la nouvelle table pour les réceptions de courriels, on utilisera la fonctionnalité Regrouper par afin de compter le nombre de lignes par adresse, onglet et mois.
Ceci nous permettra d’obtenir une table comme la suivante:
On pourra filtrer la colonne d’adresses courriel pour retirer les null, trier par numéro de mois et ensuite supprimer la colonne de numéro de mois. Ceci nous permettra d’obtenir le résultat suivant:
On remarque donc que ça balance avec les données que l’usager avait calculées pour l’onglet DS-R, soit un total de 3, pour le mois d’août pour les deux courriels spécifiés et 4, pour le mois d’août, pour l’onglet SR-R.
Et on pourra répéter la même opération avec les envois de courriels.
On remarque donc que ça balance avec les données que l’usager avait calculées pour l’onglet DS-E, 1 en juillet et 3 en août et pour l’onglet SR-E, 3 en août et 1 en septembre.
Ici, notons que les données de départ ont été importées dans des onglets Excel mais qu’il serait évidemment possible et plus logique de les importer directement dans Power Query. Notons également qu’il aurait été possible de travailler avec la table conso en entier et laisser les usagers créer leurs tableaux croisés dynamiques à partir de cette table conso. Une fois que l’on crée une procédure avec Power Query, on n’a plus à se soucier de formules et des mises à jour. On appuie seulement sur Actualiser pour mettre à jour nos analyses et c’est là, toute la beauté. Quel économie de temps!
Fichier d’accompagnement VIP à télécharger
Pour télécharger le fichier utilisé dans ce tutoriel, devenez membre VIP du CFO masqué.
Formation complémentaire
Pour explorer les principales fonctions et fonctionnalités de Power Query, suivez la formation Excel – Introduction à Power Query et au langage M qui vous permettra d’importer, de transformer et de fusionner des données de diverses sources, afin de pouvoir les analyser efficacement.
Voici quelques commentaires d’apprenants ayant suivi cette formation :
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é.
