Excel: Autopsie d’un spaghetti ou l’art de transformer un spaghetti 🍝 en caviar

Publié le 27 novembre 2019
par Sophie Marchand M.Sc., CPA, CGA, MVP
Formules de tableaux

Excel: Autopsie d’un spaghetti ou l’art de transformer un spaghetti 🍝 en caviar

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. 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

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.

Spaghetti entier

 

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.

Onglets spaghetti

 

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 ;).

Spaghetti original
 
 

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

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.

 

Formule indentée

 

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.

Onglet du spaghetti

 

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:

Formules de tableaux

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

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:

Ajout colonne nom onglet

 

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.

Append 4 tables

 

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.

Colonnes ajoutées

 

Pour ce faire, il faut simplement utiliser l’option Date du menu Ajouter une colonne.

Ajout colonne dates

 

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.

Regrouper par emails reçus

 

Ceci nous permettra d’obtenir une table comme la suivante:

Résultat interméadiaire emails reçus

 

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:

Emails reçus

 

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.

Emails envoyés

 

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 :

Amélie Tremblay
Écrit il y a 2 mois
Une des rares formations où nous quittons avec une boîte à outils.

Très bonne formation applicable à ma réalité. Une des rares formations où nous quittons avec une boîte à outils.

CĂ©cile BERNARD
Écrit il y a 3 mois
Je ne regrette absolument pas mon choix !

Excellent !! Je suivais déjà Sophie via Twitter ou directement sur le site du CFO masqué (forum et astuce) et quand il a fallu déterminer quel organisme de formation prendre, j'ai naturellement pensé au CFO Masqué. Je ne regrette absolument pas mon choix !

Stephanie Lambert
Écrit il y a 2 ans
Enfin du contenu que je ne connaissais presque pas

J'ai enfin pu avoir l'expérience d'un nouvel apprenant et comme je m'y attendais, elle fut très positive. Les notions sont bien expliquées et illustrées avec des exemples concrets. Il est très utile d'avoir les mêmes tables de données pour pouvoir reproduire les exemples. Les documents pdf fournis sont clairs et bien faits.

Daniel Harvey
Écrit il y a 2 ans
J'ai adoré.

Comme introduction a l'outil, pour avoir un aperçu de ce qu'il est possible de faire, je pense que la formation est vraiment géniale.

CFO-Masque_Formations-en-ligne_FB Le CFO masquĂ© offre un vaste choix de formations en informatique dĂ©cisionnelle avec Excel et Power BI, via un portail en ligne et Ă  distance en temps rĂ©el, selon un calendrier. Si vous dĂ©sirez organiser des formations privĂ©es, faites nous simplement parvenir un courriel Ă  info@lecfomasque.com . Des certificats convenant aux normes de formation continue des divers ordres professionnels du QuĂ©bec sont offerts pour l'ensemble des formations.  

Découvrez quelles formations vous conviennent

 

Laisser un commentaire

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

Scroll to Top