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

Publié le 16 avril 2015
par Francis Paquet M.Sc., EEE
Modeloff Energie Tableau Resultats1

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 avec 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 avec 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, 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

 

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

 

Dans un premier temps, 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 à:

Modeloff Energie Tableau Resultats1
Modeloff Energie Tableau Resultats1

 

La réponse aux questions

 

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

 

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 :

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

 

2 réflexions sur “Une difficile conversion de données avec Excel – Un exemple de Modeloff 2014 (3 de 3)”

  1. Laurent Lamarche

    Mon Cher Watson,
    Merci à la fois pour les commentaires et surtout pour le fichier.
    Bravo pour la rapidité de l’exécution
    Cordialement
    Laurent

  2. Ping : Ms-office | Pearltrees

Laisser un commentaire

Votre adresse de messagerie ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Scroll to Top