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

Publié le 29 avril 2019
par Sophie Marchand M.Sc.
Extrait données sources

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

mauvaises méthodes de travail dans Excel

 

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

méthodes de travail dans Excel

 

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

mauvaises méthodes de travail dans Excel

 

Analyse de la méthode de travail dans Excel

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

 

Analyse de la méthode de travail dans Excel : 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 de la méthode de travail dans Excel : 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?.

 

Vous devez consolider des données, les analyser, les interpréter et présenter des conclusions utiles pour des fins de prise de décision ? Apprenez à développer des solutions robustes et automatisées avec nos formations en Tableaux de bord avec Excel.

 

Méthodes de travail à privilégier dans Excel 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-dessous.

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 et de cette bonne méthode de travail dans Excel, 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.


Fichier d’accompagnement VIP à télécharger

Pour télécharger le fichier utilisé dans ce tutoriel, devenez membre VIP du CFO masqué.

 

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:

 

Voici quelques commentaires d’apprenants ayant suivi la formation Excel – Mise à niveau :

CFO-Masque_Formations-en-ligne_FBLa 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é.

3 réflexions sur “Vous ne serez jamais maître d’Excel si vous travaillez comme ça !”

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

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

Laisser un commentaire

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

Retour en haut