Une difficile conversion de données avec Excel – Un exemple de Modeloff 2014 (2 de 3)

Publié le 10 avril 2015
par Francis Paquet M.Sc., EEE

Une difficile conversion de données avec Excel – Un exemple de Modeloff 2014 (2 de 3)

Dans le billet précédent, Une difficile conversion de données avec Excel – Un exemple de Modeloff 2014 (1 de 3), nous avons vu les premières étapes afin de convertir des données présentées sous la forme de chaînes de caractères ayant des particularités les rendant difficiles à analyser sans un exercice de traitement de données. Dans un premier temps, au billet précédent, une certaine uniformisation des données a été faite. Nous en sommes donc maintenant à l’étape d’extraire les données requises dans le format approprié. Ces données sont la date, l’heure et la consommation énergétique.

Vous pouvez télécharger le fichier source ainsi que les questions et réponses de cette partie du test de Modeloff. Au risque de me répéter, le test Modeloff a une durée de 2 heures et 35 minutes étaient allouées à cette question. Inutile de préciser que les réflexes de modélisation doivent venir très rapidement !

 

Extraction de la période horaire (temps)

 

Donc, on extrait d’abord le temps (ou la période horaire) à partir des données. On remarque que ce temps est en fait tout ce qui se trouve à gauche du premier espace.

On utilise la fonction LEFT (GAUCHE en français) afin d’extraire les caractères les plus à gauche et on utilise la fonction FIND (TROUVE en français) afin de trouver la position de ce premier espace.

Modeloff Energie Temps1

On décrémente la position du premier espace de 1 afin de ne prendre que tout ce qui est à gauche de cet espace (donc excluant l’espace).

Par la suite, on doit séparer le AM ou PM du temps résultant. Cela se fait en deux étapes. La fonction RIGHT (DROITE en français) permet de récupérer facilement le AM ou le PM. Et, encore une fois, une fonction LEFT est utilisée afin de récupérer l’heure. On utilise la fonction LEN (NBCAR en français) afin de repérer la position du dernier chiffre, étant donné que AM ou PM n’a que deux caractères. Vous remarquerez que je multiplie ce résultat par 1 afin de forcer Excel à convertir ce chiffre en une valeur numérique.

 

Modeloff Energie Temps2
Modeloff Energie Temps2

Finalement, étant donné que je ne veux pas utiliser le temps en format américain, je veux transformer le temps sur une base 0 à 23 heures. Une imbrication de SI fait un excellent travail. Ici, il convient de mentionner que 12AM est en fait minuit, donc 0 heure, et que 12PM est midi, donc 12 heures.

Modeloff Energie Temps3
Modeloff Energie Temps3

 

Extraction de la consommation

 

La consommation se trouve à droite de la chaîne de caractères. La fonction RIGHT (DROITE en français) peut donc être utilisée afin de faire cette extraction de tout ce qui se trouve à droite du dernier espace. Par contre, trouver la position de ce dernier espace demande un peu de doigté. Par inspection, on voit que cette consommation a toujours, au plus, 5 caractères. L’idée ici, dans un contexte de course contre la montre, n’est pas d’être parfait, mais de répondre correctement à ce qui est demandé. Conséquemment, la fonction résultante est une combinaison de RIGHT, FIND et LEN. Finalement, si, tout comme moi, votre séparateur décimal est la virgule, on doit remplacer le point par une virgule.

 

Modeloff Energie Consommation
Modeloff Energie Consommation
Vous aimeriez être un Ninja Excel et devenir la référence ultime de votre bureau, ou tout le moins ne plus avoir l’impression de stagner ? Développez des solutions innovatrices en explorant des fonctions et fonctionnalités souvent méconnues mais fort utiles avec nos formations pour devenir Ninja d’Excel.

En cherchant le dernier espace à une position commençant à la longueur de la chaîne de caractères moins 6, on s’assure de trouver le DERNIER espace. Le nombre de caractères à prendre est alors la longueur de la chaîne moins la position du dernier espace.
 

Extraction de la date

 

L’extraction de la date n’est pas une mince affaire! Toutefois, par inspection, on voit que le format de cette date est toujours (J)J-MMM-AAAA. Donc, on va s’aider de la présence des tirets afin d’extraire les 3 éléments de la date. La position du premier tiret permet de récupérer la date et le mois. On s’assure que le jour et l’année soient des valeurs numériques. Finalement, la position du mois permet de récupérer l’année. Dans tous les cas, on utilise la fonction MID (STXT en français) afin d’extraire une portion de la chaîne de caractères qui se trouve quelque part au sein de cette chaîne.

Modeloff Energie Date1
Modeloff Energie Date1

La dernière manipulation est de convertir le mois en un chiffre de 1 à 12 avant de reconstituer la date avec la fonction DATE. La conversion du mois se fait avec un VLOOKUP (RECHERCHEV en français) à partir d’une table créée à cette fin.

Modeloff Energie Date2
Modeloff Energie Date2

 

 Conclusion

Bon, après toutes ces manipulations, les données sont prêtes pour l’analyse. Je vous invite à télécharger le fichier d’accompagnement (à la fin de cet article) qui contient tout le travail fait jusqu’à présent sur ce cas. Dans le dernier billet, les réponses aux questions de Modeloff seront présentées.

 

Lire la suite:

Une difficile conversion de données avec Excel – Un exemple de Modeloff 2014 (3 de 3)


 

Formation complémentaire

Pour en savoir plus sur les fonctions d’Excel, qui permettent de manipuler, gérer et transformer des données qui proviennent de diverses sources et différents formats, sans faire intervenir Power Query ou le VBA, suivez la formation Excel – Traitement, manipulation et analyse de données.
 

Voici quelques commentaires d’apprenants ayant suivi cette formation :

Anthony DJOUMBISSI
Écrit il y a 1 an
très intéressant

je me suis vraiment régalé sur des notions que je semblais connaitre mais des astuces avancées m'ont vraiment amené au cœur d'Excel merci une fois de plus

Jean Alexis Placius
Écrit il y a 1 an
Excellente formation

Contenu très intéressant et adapté à mes besoins. Explications claires et concises soutenues par des exemples très pratique.

Isabelle Arbour
Écrit il y a 2 ans
Très pertinente

Très pertinente pour des comptable qui utilise à outrance la manipulation de données dans Excel et qui ne connaissent pas tous les trucs et pièges.

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