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

Nous avons vu précédemment dans Une difficile conversion de données dans Excel – Un exemple de Modeloff 2014 (1 de 3) comment normaliser une chaîne de caractères afin de faciliter l’extraction des données. Par la suite, dans Une difficile conversion de données dans Excel – Un exemple de Modeloff 2014 (2 de 3), les données ont été extraites.

D’emblée, je veux rappeler que cet exercice est un exemple réel de la première ronde de tests de Modeloff 2014, un concours international en modélisation financière. Et le concours suggérait de ne consacrer que 35 minutes à cette section, soit 35 minutes pour lire et comprendre la question, pour faire les conversions, les extractions et répondre aux 7 questions (la partie analyse que nous couvrons aujourd’hui). Donc, dans une situation de tests, il faut avoir de très bons réflexes de modélisation, rapidement.

Il est également bon de mentionner que de telles conversions de données dans Excel, extractions et analyses sont monnaie courante dans la vie du modélisateur financier. En effet, il arrive régulièrement que des données nous soient fournies dans des formats des plus étranges…

Les questions à se poser en conversion de données dans Excel

L’analyse requise dans cette section dépend évidemment des questions auxquelles nous devons répondre, Dans le cadre des présents billets, nous avons un document PDF qui nous donne ces 7 questions de Modeloff. Toutefois, dans le déroulement réel du test, nous ne voyons qu’une question à la fois. Il faut donc défiler toutes les questions et prendre des notes afin de s’assurer de bien structurer nos données et se préparer adéquatement.

Les questions 1 à 3 touchent des consommations moyennes sur des périodes d’une heure. Nous sommes chanceux car toutes les données ne touchent que des périodes d’une heure, donc nul besoin de champs calculés!

La question 4 nous demande la consommation la plus grande sur une période de 4 heures consécutives. Par inspection, on voit bien que les données fournies sont dans un ordre quelconque. Il va donc falloir faire un tri afin de répondre à cette question.

Finalement, les questions 5 à 7 touchent au coût de la consommation énergétique. Il va donc falloir lier les données extraites au tarifs fournis.

La préparation des données

Avant de pouvoir effectuer une conversion de données dans Excel, il faut s’assurer d’utiliser des données dans un format propice. Ici, j’ai décidé de reporter mes 3 données de base: le temps de 0 à 23 heures, les dates dans un format approprié et les consommations dans un tableau que j’ai formaté sous forme de table. J’ai tout mis en valeur et j’ai trié par ordre croissant de date et, ensuite, par ordre croissant de l’heure. Le tout afin d’être prêt pour la question 4. Mon tableau de données, à cette étape-ci, ressemble donc à:

La réponse aux questions de conversion de données dans Excel

Des tableaux croisés dynamiques (TCD) sont créés afin de répondre aux 7 questions. Dans la vraie vie, un seul TCD a été fait et ajusté au fur et à mesure afin de répondre aux questions. On va également constater que notre table de données va prendre de l’expansion puisque nous allons ajouter des colonnes afin de répondre à certaines questions. On voit le bénéfice d’utiliser une table au lieu d’une plage de données, car le TCD va s’adapter à notre expansion de la table, alors que cela est plus difficile si le TCD est lié à une simple plage de données.

Mon fichier final est disponible en téléchargement ci-bas.

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.

Question 1 : Consommation moyenne sur toute la période

Cette première question est facile à répondre avec un TCD où seulement la consommation est montré dans les mesures et où la moyenne est choisie.

Question 2 : Consommation moyenne en février

Cette question demande de placer les dates en paramètre de lignes et la consommation comme mesure. On utilise encore la moyenne. Afin d’avoir les dates en mois, on utilise la fonctionnalité de grouper des dates et de les présenter en mois. On n’a plus ensuite qu’à regarder le résultat pour le mois de février. Cette fonctionnalité a déjà été présenté dans le billet Excel: Maîtriser les tableaux croisés dynamiques (1 de 5).

Question 3 : Le jour de la semaine où la consommation moyenne est la plus élevée

Afin de répondre à cette question, on va jouter une nouvelle colonne à nos résultats afin de récupérer le jour de la semaine. La fonction WEEKDAY (JOURSEM en français) nous fournit le jour de la semaine de 1 à 7, avec 1 = dimanche, 2=lundi et ainsi de suite. On utilise donc le jour de la semaine comme paramètre de lignes et la consommation comme mesure, en utilisant la moyenne. Et on va trouver que dimanche est la journée de plus grande consommation moyenne.

Question 4 : La consommation la plus élevée sur une période consécutive de 4 heures

Encore une fois, afin de bien répondre à cette question, j’ai choisi d’ajouter une colonne à mes données. Cette nouvelle colonne est en fait un SUM ( OFFSET()) ou SOMME ( DECALER ()) en français. On choisit en référence du OFFSET la consommation sur la ligne où nous sommes et nous faisons la somme sur une plage de 4 lignes x 1 colonne. Par la suite, on n’a plus qu’à reporter cette colonne comme mesure dans le TCD et de demander le MAX.

Questions 5 à 7 : Le coût de la consommation selon les différents tarifs

Pour les questions 5 à 7, il faut multiplier la consommation par le bon tarif. J’ai donc ajouté 3 colonnes dans mes résultats afin de calculer le coût de la consommation en fonction de chacun des tarifs. Pour le tarif annuel, on multiple simplement la consommation par le tarif unique. Pour le tarif mensuel, je fais un VLOOKUP (RECHERCHEV en français) dans la table fournie. J’ai ajouté le numéro du mois à cette table, ce qui me permet de passer le numéro du  mois en paramètre du VLOOKUP à l’aide de la fonction MONTH (MOIS en français) appliquée sur la date. Finalement, en ajoutant la plage horaire de 0 à 23 heures à la table des tarifs quotidiens, il devient facile de faire un VLOOKUP du tarif quotidien à partir des résultats.

Ces 3 nouveaux champs sont passés comme mesures dans le TCD et la somme est demandée. Le résultat est alors le coût annuel de la consommation énergétique pour les 3 tarifs. On conclut que le tarif quotidien est le plus avantageux.

Conclusion sur la conversion de données dans Excel

En 3 billets de blogue, nous avons couvert 35 minutes de la vie d’un test de Modeloff… Cela devrait vous laisser pensif (ou admiratif, c’est selon !). Évidemment, dans la vraie vie, le sentiment d’urgence n’est pas toujours présent, ce qui permet de trouver des solutions plus permanentes à ce genre de situations où la conversion et la transformation de données sont nécessaires. Et nous sommes des experts en cette matière.

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