Excel: Automatiser le nettoyage des données

Publié le 05 octobre 2015
par Sophie Marchand M.Sc., CPA, CGA, MVP
Substitue et Supprespace

Excel: Automatiser le nettoyage des données

Bientôt, aura lieu notre formation Excel – Traitement, manipulation et analyse de données. Cette formation a pour objectif de présenter une série de fonctions et fonctionnalités qui permettent de “nettoyer” les données dans Excel, sans l’usage de Power Query ou de l’option “New Query” (Nouvelle requête) d’Excel 2016, en vue de pouvoir les analyser efficacement par la suite. Elle s’adresse donc plus particulièrement à des gens qui ont à travailler avec des données importées de systèmes non conviviaux et qui n’ont pas accès à Power Query ou à Excel 2016. L’article ci-bas vous donne un aperçu de ce que vous pourriez apprendre dans cette formation.

 

Fonction TRIM (ou SUPPRESPACE en français)

Dans l’exemple ci-bas, un analyste a importé des données d’un système maison et se retrouve initialement avec 3 colonnes de données, i.e. les colonnes C, D et E. Il souhaite ensuite faire la somme des montants de la colonne E par groupe (note: les groupes se trouvent dans la colonne D). Dans la cellule K8, il utilise la fonction SUMIF (ou SOMME.SI en français) pour faire la somme des montants appartenant au groupe A. Mais surprise! Il obtient 0$ comme résultat. Il jette alors un coup d’oeil à sa formule et pourtant… elle semble bonne. Mais qu’est-ce qui se passe?

Substitue et Supprespace

 

En jetant un oeil plus attentif aux données de la colonne D, notre analyste s’aperçoit qu’il y a un espace avant chaque nom de groupe. Cet espace est créé par le système maison, qui exporte les données dans Excel, sous ce format. Notre analyste comprend alors que c’est cet espace qui provoque une somme de 0$ pour chacun de ses groupes. Il doit donc supprimer cet espace.

Substitue et Supprespace

 

Mais il y a un tas de données à transformer dans son fichier (laissez aller votre imagination…). Il ne peut quand même pas faire les transformations manuellement. Il pense alors à utiliser la fonctionnalité FIND and REPLACE (ou CHERCHE et REMPLACE en français) mais il ne voudrait pas avoir à refaire cette manipulation à chaque fois qu’il doit analyser ces données… Il est d’avis qu’il serait plus intéressant d’utiliser une formule, puisqu’il pourra ensuite utiliser l’option FORMAT AS TABLE (ou METTRE SOUS FORME DE TABLEAU en français) pour automatiser ses calculs (ceci sera vu plus loin dans cet article). Il choisit donc la fonction TRIM (ou SUPPRESPACE en français), pour créer une nouvelle colonne de groupes, dans la colonne F.

Substitue et Supprespace

 

Finalement, notre analyste s’assure que ses calculs réfèrent à cette nouvelle colonne. Et voilà, le résultat semble concluant.

Substitue et Supprespace

 

Fonction SUBSTITUTE (ou SUBSTITUE en français)

Notre analyste aimerait bien ajouter le nom du gestionnaire à sa base de données puisqu’ensuite,il voudra faire d’autres calculs relativement aux gestionnaires (encore une fois, laissez aller votre imagination… un gestionnaire pourrait gérer plus d’un groupe, ce qui n’est pas le cas dans notre court exemple ci-bas). Il importe donc une table d’un autre système, qui lui indique simplement le nom du gestionnaire responsable de chaque groupe. On retrouve cette importation dans les colonnes M et N ci-bas. Par contre, lorsque notre analyste utilise une fonction VLOOKUP (ou RECHERCHEV en français), il obtient des #N/A…

Substitue et Supprespace

 

Il examine donc plus attentivement les données qu’il vient d’importer dans les colonnes M et N et il s’aperçoit que le nom des groupes ne comprend pas d’espace entre “groupe” et la lettre désignant le groupe. Il décide donc d’ajouter une nouvelle colonne dans son tableau initial et il utilise la fonction SUBSTITUTE (ou SUBSTITUE en français) pour supprimer les espaces situés à l’intérieur des noms de groupes. À noter que la fonction TRIM (ou SUPPRESPACE en français) n’élimine que les espaces placés devant ou après l’expression et laisse les espaces placés à l’intérieur intacts. Voilà pourquoi notre analyste a plutôt arrêté son choix sur la fonction SUBSTITUTE (ou SUBSTITUE en français) cette fois-ci.Substitue et Supprespace

 

Cette nouvelle colonne lui permet de corriger ses formules VLOOKUP (ou RECHERCHEV en français).

Substitue et Supprespace

 

Mettre sous forme de tableau

Les deux étapes ci-haut mentionnées ne sont pas les seules interventions que souhaite faire notre analyste. Et celui-ci tient à structurer son fichier Excel pour que ses calculs soient mis à jour chaque au fil de l’évolution de sa base de données d’origine. Il décide donc de mettre ses données sous forme de tableau. Ainsi, du moment qu’il ajoute une nouvelle ligne de données (ou plusieurs lignes de données), les formules de ses nouvelles colonnes sont réptées.

Substitue et Supprespace

 

Ceci permet à notre analyste de mettre son tableau à jour tout en s’assurant que ses calculs fourniront toujours les bons résultats.

Substitue et Supprespace

 

Notions de base sur les fonctions Texte

Vous trouverez quelques informations de base sur les fonctions de texte Excel dans l’article Astuce Excel: Extraire des chaînes de texte.

 

Formation Excel – Analyse et modélisation de données

Pour des notions avancées, notamment sur les fonctions de texte et les fonctions de dates, inscrivez-vous à notre formation Excel – Traitement, manipulation et analyse de données .

 

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