Contact: 514-605-7112 / info@lecfomasque.com

Vous ne serez jamais maître d’Excel si vous travaillez comme ça!

Publié le : 29 avril 2019

Je me suis inspirée d’une question posée sur le forum pour rédiger l’article suivant. Mais attention! Je ne porte absolument aucun jugement sur l’individu qui a posé cette question et vous ne devriez pas non plus puisque je reçois des dizaines de questions semblables à chaque semaine. Il est donc loin d’être le seul dans sa situation. Le problème, c’est que je pourrais bêtement répondre à la question posée mais celle-ci n’est pas la bonne… Comment puis-je le savoir me direz-vous? Et bien parfois, les usagers d’Excel s’enfoncent dans une direction et aboutissent là où ils n’auraient jamais dû aboutir et se placent donc dans des impasses pouvant (et devant) être évitées. Cet article vise donc à vous présenter une mauvaise méthode de travail dans Excel, à vous expliquer en quoi cette méthode n’est pas adéquate et à vous suggérer la meilleure approche dans une telle situation, courante, rappelons-le.

 

Données soumises par l’usager sur le forum

L’usager utilise d’abord le premier tableau ci-dessous comme source de données afin d’alimenter les deux tableaux suivants.

 

Tableau présentant les données sources de l’usager

Extrait données sources

 

Tableau comprenant les premiers résultats recherchés (fonctionne bien)

Premier tableau

 

Tableau comprenant les seconds résultats recherchés (l’usager ne sait pas quelle formule utiliser)

Deuxième tableau

 

Analyse de la situation

Dans les prochains paragraphes, nous allons réviser la méthode de travail de l’usager, expliquer les problématiques et proposer des pistes de solutions à différents niveaux.

 

Analyse du premier tableau de résultats

Dans le premier tableau de résultats, l’usager a utilisé une fonction SOMMEPROD afin de récupérer les valeurs recherchées. La formule fonctionne et rapporte les bons résultats. Par contre, l’usager a utilisé une formule différente dans chaque colonne de Htva. Par exemple, dans la cellule L5, il a inscrit: =SOMMEPROD((ANNEE($E$6:$E$1001)=2012)*(MOIS($E$6:$E$1001)=1)*($G$6:$G$1001)). Ceci fait en sorte que la formule ne peut pas être copiée sur les autres lignes ni sur les autres colonnes. Cela signifie aussi que l’usager a dû manuellement changer les =2012 et les =1 à chacune des cellules de son tableau, ce qui est tout à fait à l’encontre des meilleures pratiques d’affaires. Ceci aurait pu être évité en utilisant une fonction plus performante et dynamique, i.e. en faisant référence aux années et aux mois dans les entêtes de lignes et de colonnes.

De même, l’usager a étonnamment recalculé les montants avec taxes dans son tableau alors qu’ils étaient déjà calculés dans le tableau de données source. Par exemple, dans la cellule M5, il utilise la fonction =SOMME(L5*21%)+L5. Si l’usager avait utilisé les entêtes de lignes et de colonnes, il aurait pu utiliser un seule formule pour l’ensemble de son tableau, autant pour les montants avant et après taxes.

Finalement, quand l’usager mettra son tableau à jour l’an prochain, il devra ajouter de nouvelles colonnes avec elles aussi de nouvelles formules. Son tableau ne sera pas mis à jour automatiquement. Notez aussi que l’usager fait référence à des cellules jusqu’à la ligne 1001 dans ses formules alors que ses données sources arrêtent à la ligne 105. Il prévoit donc l’ajout de nouvelles données au fil du temps (très bien), mais que ce passera-t-il quand les nouvelles données dépasseront la ligne 1001? Il ne faudrait jamais sélectionner plus de cellules que nécessaire dans une formule. À ce sujet, vous pouvez lire l’article suivant: Excel : Évitez de faire référence à des plages de données plus grandes que nécessaire.

 

Analyse du deuxième tableau de résultats

Dans le deuxième tableau, l’usager tente de réutiliser la même fonction mais pour présenter du texte. De toute évidence, l’usager ne tient pas compte du fait qu’il utilise une fonction de matrices qui ne prend en compte que des chiffres et non du texte. Il est donc embêté et avec raison.

C’est ici que mon réflexe de formatrice entre en jeu. J’aurais pu fournir à l’usager une formule lui permettant d’obtenir les résultats recherchés. Cette formule, encore une fois, aurait été très manuelle au niveau de la mise à jour, ce qui est tout à fait à proscrire au niveau d’Excel. Je préfère donc aider l’usager à corriger une lacune importante dans la méthode de travail. Les plus avancés d’entre vous l’auront tout de suite identifiée. Les données sources ne sont pas réellement des données sources… Retournez jeter un coup d’oeil au premier tableau ci-dessus… Il est gorgé de couleurs, de colonnes vides, de lignes vides, de colonnes fusionnées, etc. Il s’agit de données mises en forme, que l’on privilégie pour la présentation et non pour les calculs.

Nombreux d’ailleurs sont les usagers d’Excel qui utilisent des données de présentation en guise de données sources. Vous ne devriez jamais faire ça. Sinon, vous serez assurément embêté à un moment ou un autre avec une formule ET vos formules ne seront jamais mises à jour automatiquement. Vous serez confronté à un bon lot de travail manuel, avec tous les risques d’erreurs de calculs qui l’accompagne. Dépendamment de l’emplacement de vos données d’origine, vous devriez donc toujours travailler à partir de données mises sous forme de tableau dans Excel, de connexion à une base de données structurée ou de données transformées avec Power Query, directement dans Excel.

 

Analyse du tableau de données sources

À la base, les données sources ne sont JAMAIS présentées comme dans le premier tableau ci-dessus. Et là, je vous entends dire que ce n’est pas vrai, que vous recevez fréquemment des données sous ce format ou que vous importez souvent des données d’un certain système sous ce format. Dites-vous bien que si vous extrayez des données d’un système et que ces données vous sont présentées sous ce format, ce ne sont pas les données sources! Vous êtes plutôt en face d’un rapport, créé à partir des données source, par le système. Cette nuance est très importante. Trop souvent, j’ai discuté avec des clients qui ne faisaient pas la différence alors que travailler à partir de données de rapports (ou données de présentation) sans les transformer, vous mènera toujours vers des problématiques pouvant être évitées.

Que faire alors si les données que vous extrayez de votre système vous sont fournies sous forme de rapport? Et bien vous n’avez qu’à utiliser une solution ETL (Extracting, Transforming, Loading) comme Power Query. Si vous ne savez pas encore ce qu’est Power Query, il est grand temps pour vous de remédier à la situation! Power Query peut être ajouté gratuitement à toutes les versions d’Excel 2010 et 2013 et fait partie intégrante d’Excel 2016 et plus. Alors plus d’excuses!

Avec Power Query, l’avantage est que tout sera éventuellement dynamique. Vous allez écrire une requête de transformation de données (une recette), qui sera appliquée à chaque mise à jour pour tenir compte des nouvelles données. Vous éviterez le travail manuel et les risques d’erreurs. Et comme vous présenterez vos analyses sous forme de tableaux croisés dynamiques, vous n’aurez plus à vous soucier de formules complexes. Si vous n’aimez pas le look des tableaux croisés dynamiques, je vous invite à jeter un coup d’oeil à l’article suivant, qui vous explique qu’on peut travailler avec un tableau croisé dynamique, tout en lui donnant l’apparence d’un rapport Excel: Et si personne ne remarquait que votre rapport est en fait un tableau croisé dynamique?.

 

Procédure avec Power Query

Voici maintenant comment nous pourrions utiliser Power Query à notre avantage afin de transformer et analyser les données fournies par l’usager.

 

Pour le moment, nous allons supposer que ces données proviennent d’un rapport que l’usager a extrait d’un système et importé dans Excel. Il faudrait donc d’abord importer les données dans Power Query. Pour cela, nous allons dans le menu Données et choisissons À partir d’un tableau.

Power Query - Données à partir d'un tableau

 

Ceci nous permet d’importer les données dans Power Query, tel que présenté ci-desous.

Données importées dans Power Query

 

Quelques transformations de base seront nécessaires. Par exemple ici, nous allons Utiliser la première ligne pour les en-têtes de colonnes.

Utiliser la première ligne comme entêtes de colonnes

 

Nous allons ensuite supprimer les colonnes vides et Supprimer les premières lignes du haut, plus précisément les deux premières, puisqu’elles sont vides également.

Supprimer les lignes du haut

 

Dans la colonne Marque, les données sont présentées avec des espaces à la fin et au début. Nous allons retirer ces espaces en sélectionnant la colonne et en allant dans le menu Transformer/Format/Supprimer les espaces.

Supprimer les espaces

 

Nous pouvons ensuite ajouter la colonne conditionnelle suivante…

Ajouter une colonne conditionnelle

 

et remplir ver le bas les cellules des colonnes Marque et ref 2016-2019.

Remplissage vers le bas

 

Nous allons aussi retirer toutes les lignes avec des dates de commande null.

Lignes filtrées

 

Finalement, nous allons renommer nos colonnes et modifier les types de données. Nous obtiendrons donc une table de données structuré, tel qu’illustré ci-dessous.

Résultat données transformées

 

Présentation des résultats

À partir de ces données structurées, nous pourrons, refaire le premier tableau de résultats à l’aide d’un tableau croisé dynamique connecté sur la requête Power Query avec l’avantage que celle-ci se mettra à jour automatiquement à l’ajout de données (à noter que j’ai caché les colonnes avec des 0 dans le tableau croisé dynamique de l’image ci-dessous).

Tableau croisé dynamique

 

Le deuxième tableau de résultats pourrait être reproduit de façon plus dynamique et plus conviviale pour l’usager, toujours par le biais d’un tableau croisé dynamique connecté à la même source de données transformée dans Power Query.

Tableau croisé dynamique

 

À noter que je n’ai pas travaillé la présentation ci-dessus mais que vous pouvez présenter des tableaux plus qu’intéressants dans Excel, en travaillant la mise en forme, en plus d’être dynamiques. Et si les visualisations ne sont pas à la hauteur de vos attentes, vous pourriez aussi importer votre fichier Excel (avec Power Query) dans Power BI et utiliser les visualisations de Power BI. À ce sujet, rappelez-vous que Microsoft a annoncé dans le passé que les visualisations de Power BI allaient éventuellement être intégrées dans Excel. À ce sujet, vous pouvez relire l’article suivant: Vous pourrez bientôt utiliser les visualisations de Power BI dans Excel.

 

Si l’usager tenait mordicus à la structure de son deuxième tableau Excel, dans ce cas, la meilleure approche serait probablement d’enregistrer la requête Power Query dans Power Pivot (toujours dans Excel) et d’utiliser des formules cube pour extraire les informations de la base de données. Mais parfois, ça vaut la peine de sortir des cadres de présentation conventionnels et statiques et s’ouvrir aux formes de présentation dynamique des données.

 

Meilleures pratiques d’affaires dans Excel

Apprendre à travailler selon les meilleures pratiques d’affaires dans Excel peut vous faire économiser un temps fou, en plus d’enrayer vos risques d’erreurs. On croit parfois à tort qu’en cherchant sur internet, on va trouver les réponses à nos questions mais on sous-estime largement le temps investi pour chercher les réponses à nos questions et les adapter à notre situation. Par surcroît, en travaillant de cette façon, on ne fait que boucher des trous. Il est impossible d’apprendre les meilleures méthodes d’élaboration d’un fichier Excel en allant piger des trucs à gauche et à droite. Pour cela, il faut passer à travers une formation structurée, qui enseigne les concepts de base. À ce sujet, je vous recommande les 3 formations suivantes:

 


NOTRE OFFRE DE FORMATIONS


Microsoft Most Valuable Professional

Le CFO masqué vous offre un vaste choix de formations Excel et Power BI, réparties dans 8 catégories: Excel – Tableaux de bord, Excel – Modélisation financière, Excel – VBA, Excel – Power Tools, Excel – Ninja, Power BI, Finance corporative et Compléments avancés. Ces formations sont offertes en classe, en entreprise et en ligne. Nos formateurs sont des experts dans leur domaine et ils sont accrédités par Emploi-Québec et vous remettent un certificat, à la fin de chaque formation, que vous pouvez notamment utiliser pour faire reconnaître des heures de formation continue auprès de votre ordre professionnel.

 

Découvrez quelles formations vous conviennent

Pour info: 514-605-7112 ou info@lecfomasque.com

 

Sophie Marchand

Sophie Marchand, M.Sc., CPA, CGA, MVP, est détentrice d’une M.Sc. en finance corporative et d’un titre comptable CPA, CGA. Elle est également MVP Office Apps and Services (Excel) et MVP Data Platform (Power BI). Le titre de MVP est un titre honorifique remis par Microsoft et signifie « Most Valuable Professional ». Il est attribué à des experts de solutions Microsoft qui se distinguent non seulement par leurs compétences mais également par leur implication dans la communauté. Seuls 4 professionnels possèdent cette double-qualification dans le monde. Mme Marchand cumule de nombreuses années d’expérience dans le milieu des affaires et se spécialise en modélisation financière et en intelligence d’affaires avec Excel et Power BI. Après avoir longtemps offert ses services comme consultante, elle se consacre désormais à la formation, sous toutes ses formes (formations en classe, formations en ligne, formations en entreprise, webinaires, animation de groupe d'usagers, rédaction d'articles de blogue, animation de forum en ligne et plus encore). Vous pouvez d'ailleurs la retrouver à la tête du groupe d'usagers Montreal Modern Excel and Power BI.

  • réponse Pierre-Olivier Biard ,

    Hey oui une belle erreur que je vois trop souvent lorsqu’on me demande de faire un rapport a partir d’un fichier Excel avec mise en forme, je dois alors faire la meme formation!

    Très bon article Sophie!

    • réponse Jean Ronald ,

      Très bien expliqué .

      • réponse Cyril Aubert ,

        Je me suis, hélas, immédiatement reconnu dans cet exemple. Comme la personne dont vous citez l’exemple, je pars de mauvaises bases et perd un temps dingue à corriger à la main mes formules (1 heure encore hier), mais la difficulté sur des outis comme Power Query, Power Pivot et même les tableaux croisés dynamiques et que si on ne pratique pas tous les jours, on oublie. Je passe mes jours fériés à parcourir votre site, mais ce n’est pas suffisant.

        Répondre à Jean Ronald Annuler