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 des données dans Excel 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 !
Extraire des données dans Excel : 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.
Extraire des données dans Excel : 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.
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.
Extraire des données dans Excel : 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.
Extraire des données dans Excel : 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 :
La 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é.
