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.
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.
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.
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.
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.
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.
Conclusion
Bon, après toutes ces manipultions, les données sont prêtes pour l’analyse. Je vous invite à télécharger le fichier 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.
NOTRE OFFRE DE FORMATIONS
Le CFO masqué vous offre 13 formations, réparties dans 4 catégories: Tableaux de bord, Modélisation financière, Finance corporative et Analyse et modélisation de données. Ces formations sont offertes en classe, en entreprise et en ligne. Nos formateurs sont des experts dans leur domaine et 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.
Consulter la liste des formations offertes
Pour info: 514-605-7112 ou info@lecfomasque.com