Monsieur Excel
Pour tout savoir faire sur Excel !

21 juin 2017

Réaliser un modèle complexe

Le modèle de simulation routière décrit durant les trois derniers articles était un modèle particulièrement délicat à concevoir. Pour simplifier, on peut dire qu’il y a deux façons de développer un modèle complexe : top-down et bottom-up.

L’approche top-down est l’approche traditionnelle, celle qui est utilisée dans les cours de programmation habituels et dans la plupart des sociétés de conseil. On décompose la tâche en sous-tâches, par exemple avec un organigramme, puis en sous-sous-tâches, et on les donne à développer à des programmeurs.

L’inconvénient est que cela fait souvent intervenir au moins trois personnes : un analyste astucieux pour analyser le travail à faire, un chef-programmeur pour découper tout cela en étapes, et un développeur (ou plusieurs) pour écrire le code. Le problème alors est que, si l’on se trompe quelque part, on risque de ne le constater qu’à la fin, ce qui coûte très cher ! Autre inconvénient, on ne peut pas montrer au client ce que cela donnera tant que ce n’est pas fini. On se prive donc d’une interaction efficace avec lui.

L’approche bottom-up est celle que j’utilise depuis plus de 50 ans. Je programme au début le noyau du code à réaliser, en simplifiant le cahier des charges. Quand cela fonctionne bien, je rajoute des fonctionnalités, puis d’autres fonctionnalités encore…

L’avantage est qu’avec un seul intervenant (je fais l’analyse, la programmation et la mise en place), on va bien plus vite, et que l’on ne récupère pas des erreurs liées à des problèmes de communication entre les intervenants. L’inconvénient est que, si la personne qui fait tout cela n’est pas astucieuse et n’a pas une vision globale des possibles extensions, elle construit une « usine à gaz ».

C’est pour cela que cette approche n’est pas pratiquée autant qu’elle pourrait l’être. Ce qui est amusant, c’est que la mode aujourd’hui est de parler de méthodes agiles qui, de fait, reviennent grosso modo à cela. Et c’est cette approche que j’ai pratiquée durant la totalité de ma carrière… Ce qui m’a permis de faire des réalisations toujours entre 3 fois et 10 fois plus rapides (en temps de développement) et 3 et 10 fois moins chères que celles de tous mes concurrents.

L’approche utilisée pour ce modèle

Comment donc ai-je fait pour développer ce modèle en mode bottom-up ?

J’ai commencé par élaborer un modèle simple pour simuler le trafic, en prenant les fichiers ne contenant que des jours de semaine normaux.

Quand les estimations de trafic de mon modèle ont fini par bien coller avec les observations sur ces fichiers, je me suis mis à analyser des fichiers de début de semaine et de fin de semaine. J’ai alors ajouté de nouvelles règles et modifié mes formules pour que, pour ces jours particuliers, le modèle donne aussi de bons résultats.

Enfin, j’ai analysé les fichiers des jours fériés, de départ en vacances et de retour de vacances, jusqu’à ce que eux aussi donnent avec mon modèle des résultats réalistes.

Validtion et amortissement du modèle

Comment AdP (Aéroports de Paris) a-t-il procédé pour valider ce modèle ?

Nous avons convenu ensemble qu’AdP ne me transmettrait que la moitié des fichiers Excel. Ils gardaient ainsi 50% des fichiers de jours normaux, de week-ends, de départs en vacances, de retour de vacances,…

Ils ont utilisé ces fichiers, que je ne connaissais pas, pour vérifier les prédictions de mon modèle, et voir si l’écart entre mes prédictions et la réalité observée était raisonnable ou non. Ils ont ainsi confirmé la validité de mon modèle.

Ce modèle a coûté à AdP plus de 60 K€. Le modèle a été amorti en moins de 6 mois. Il était envisagé en effet de modifier une intersection, en remplaçant un rond-point par un feu rouge. Le modèle a prouvé que,si l’on faisait cela, on aurait certes résolu le problème immédiat de ralentissement du trafic au rond-point, mais que le problème aurait alors été reporté que de quelques centaines de mètres, dans la création d’un nouvel embouteillage…

15 juin 2017

Modèle de simulation routière (c)

Nous continuons le modèle de simulation routière démarré le 27 mai dernier. Je vous prie de m'excuser pour la plus faible fréquence de mes derniers articles, je suis en effet assez sollicité en ce moment par mes interventions de conseil et par mon implication dans la start-up Alzohis. J'étais d'ailleurs ce matin au salon VivaTech, sur le stand de Sanofi qui a mis gracieusement un mini-stand à notre disposition. Vous pourrez m'y voir demain matin !

Accélération du calcul

Un des problèmes que j’ai rencontrés avec ce modèle était la lenteur de la simulation. J’ai réalisé que c’était dû en partie à un onglet dans lequel des calculs lourds étaient effectués. Il y avait en effet dans cet onglet plus de mille formules du type :
=moyenne(decaler(Simul!$A$1;$A8+3;F$2-1):decaler(Simul!$A$1;$A8+3;G$2-2))

Or, cet onglet ne servait qu’à effectuer des analyses, mais il n’alimentait aucun autre onglet. J’ai donc créé les deux macros suivantes afin d’effacer la quasi-totalité des formules dès que l’on quittait l’onglet et de les restaurer dès que l’onglet était réactivé :

Private Sub Worksheet_Deactivate()
    Sheets("Synthèse").Range("Effacer").ClearContents
End Sub

Private Sub Worksheet_Activate()
    Application.ScreenUpdating = False
    Range("C1:I4").FillRight
    Range("A3:I4").Copy
    Range("A5:A166").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Range("A1").Select
End Sub

Calcul de trajets

En utilisant l’add-in Crystal Ball, j’ai eu la possibilité de faire des simulations pour un trajet entre deux points donnés dans ce réseau routier et d’obtenir comme résultat la distribution des temps de trajet entre les deux points, selon l’heure de départ de ce trajet.

Pour ceux qui veulent en savoir pus sur la simulation probabiliste et sur Crystal Ball, je ne peux que vous conseiller de lire « La modélisation du risque », livre que j’ai publié chez Economica.

Rentabilité de l’application

Ce modèle de simulation routière, bien qu’il ait coûté 50.000 €, a été amorti en moins de 6 mois. En effet, AdP envisageait d’effectuer une modification à une intersection afin d'améliorer le trafic. Le modèle a montré que cette modification, bien qu’elle résolve le problème en fluidifiant le trafic au point concerné, ne résolvait aucun problème de fond car le ralentissement du trafic était alors simplement reporté de plusieurs centaines de mètres.

07 juin 2017

Modèle de simulation routière (b)

A partir de la description du réseau routier décrite dans la base représentée par la copie d’écran à la fin de l’article précédent, j’ai construit un modèle Excel dans lequel chacun des segments décrits dans cette copie d’écran est représenté par une icône.

La macro gérant la simulation, à chaque top d’horloge, remplit chacun des segments d’une couleur : vert lorsque le traffic est fluide, orange quand la circulation est ralentie, et rouge quand c'est embouteillé.


Les références circulaires

Dans ce modèle, si l’on ne fait pas attention, on se retrouve avec de nombreuses références circulaires. Ainsi, quand on se trouve dans un rond-point, on est à la fois en mouvement vers l’avant (si tout va bien !) et freiné par les voitures devant soi, qui elles-mêmes – si le rond-point est chargé – sont freinées par les voitures « devant » elles, dont nous faisons aussi partie…

C’est pourquoi vous constaterez, si vous observez bien la seconde ligne de segments en partant du haut du schéma, que tous les segments de cette ligne sont dédoublés. J’ai fait cela pour « casser » les références circulaires. A chaque top d’horloge, la macro bascule ce qui se trouve dans le premier élément de chaque binôme pour le verser dans le second élément du binôme. Il n’y a alors plus de références circulaires…

La structure du modèle

Comme vous pouvez le voir dans la copie d’écran ci-dessous, la durée du « top » est de 30 secondes. Pour chaque segment, on dispose d’un certain nombre de lignes dans lesquelles on calcule les résultats saillants. Je vous fais grâce des formules et, pour des raisons de confidentialité, je ne peux pas vous montrer la macro qui gère la simulation.


27 mai 2017

Modèle de simulation routière (a)

Le 26 avril dernier, dans l’article « Le pouvoir immense d’Excel... » je vous ai parlé de divers types de modèles que j’ai eu l’occasion de développer en tant que consultant. Aujourd’hui, je vous présente l’un de ces modèles, un modèle de simulatin routière, qui m’a été commandé par les Aéroport de Paris pour Roissy.

Le dessin ci-dessous illustre ce qu’il était prévu de modéliser : il s’agissait d’une partie des routes de la zone aéroportuaire.


Comme l’indique le schéma ci-dessus, il s’agissait d’une section comportant trois rond-points, dans laquelle des embouteillages étaient régulièrement observés. Il s’agissait d’évaluer si cela serait utile d’effectuer des modifications, par exemple en remplacer un ou plusieurs par des feux, ou des stops… Ou convertir des feux existants en autre chose…

Les ronds bleus indiquent les endroits où l’on a placé des compteurs qui, minute par minute, comptaient le nombre de véhicules et la vitesse moyenne. Il y avait un fichier par jour, chaque fichier faisant donc 60*24 lignes. On avait ainsi des fichiers pour jour de semaine, début de semaine, fin de semaine, jour férié, départ de vacances, retour de vacances,…

Il fallait mettre au point un modèle qui, en utilisant les informations sur les points de contrôle représentant les entrées dans le système (les endroits d’où arrivaient les voitures), simule l’évolution du trafic de façon à être le plus proche possible de la réalité observée aux points de sortie ou à divers points intermédiaires.

La description des différents segments est fournie ci-dessous. Pour chaque segment, on disposait de ses caractéristiques. De même, les feux éventuels étaient aussi décrits dans l’onglet Base.


Je vous laisse réfléchir sur la façon dont vous vous y prendriez pour attaquer un tel problème. Ceci dit, n’espérez pas que je vous donne ma solution dans les articles suivants. Je ne peux pas vous la donner pour des raisons de confidentialité, mais je pourrai quand même vous éclairer en décrivant certains problèmes que j’ai rencontrés et en vous disant comment j’ai résolu ces problèmes.

21 mai 2017

Localiser un nom élastique

Quand on a défini un nom élastique, on peut avoir envie de le sélectionner pour vérifier qu’il a été défini sans erreur. Hélas, quand on déroule la « Zone Nom », à gauche de a barre de formule, on découvre avec dépit que les noms élastiques ne sont pas complètement reconnus comme des noms par Excel et n’appartiennent donc pas à la liste.

Heureusement, Excel possède une commande géniale pour atteindre directement une zone nommée : [Ctrl]-t (pensez à Atteindre). Certes, quand vous l’activez, les noms dynamiques ne sont pas dans la liste des noms proposés, mais vous pouvez alors taper le nom concerné et cliquer dans le bouton « OK ».

Ceci dit, dans certaines circonstances, Excel reconnaît les noms élastiques comme des noms. Par exemple, quand vous créez une formule, la commande [F3] vous permet de coller un nom dans la formule. Eh bien, dans la liste des noms qui apparaît alors, les noms élastiques sont présents. Allez comprendre...

Pour ma part, je suis un fervent utilisateur de la commande « Atteindre ». Outre la possibilité que nous venons d’indiquer, cette commande vous donne accès, via son bouton « Cellules » – parmi la sélection de cellules en cours – aux cellules qui possèdent telle ou telle caractéristique :


13 mai 2017

Un bug dans les graphes

Il y a depuis Excel 2007, je crois – sinon, c’est depuis Excel 2010 – un bug dans les graphes d’Excel dont j’ai déjà d’ailleurs eu l’occasion de parler dans le passé dans ce blog. C’est qu’il n’est plus possible d’entrer directement dans la formule serie() un nom de série débutant par « L » ou « C », si celui-ci est un nom élastique (on dit aussi « dynamique »).

Supposons que vous ayez créé un second nom élastique, Ces_Y, avec exactement la même définition que dans l’article précédent. De bonne foi, vous sélectionnez la série, vous cliquez dans la barre de formule et vous remplacez le « M » de Mes_Y par « C ». Rien ne se passe, pas même un message d’erreur !

Vous cliquez alors dans « Filtres du graphique », la dernière icône à droite, vous activez la commande « Sélectionnez les données », vous cliquez sur le bouton « Modifier » et vous remplacez le « M » de Mes_Y par « C ». Et là, tout baigne, il n’y a aucun problème !

J’ai découvert ce problème chez un client auprès duquel j’avais ainsi rendu dynamique toute une série de graphes. Eh bien, ce problème survient chaque fois que le nom élastique débute par « C » ou « L » : Excel fait alors une confusion gigantesque avec Colonne et Ligne. La preuve, c’est que dans un Excel en anglais, c’est le « C » et le « R » qui sont touchés.

J’ai signalé ce bug à Microsoft depuis plusieurs années déjà, mais il n’est évidemment pas dans la liste des priorités…

08 mai 2017

Création d’un graphe élastique

Quand on crée un graphe dans Excel, on a parfois besoin que ce graphe soit « élastique », c’est-à-dire qu’il s’adapte automatiquement à l’apparition de nouvelles données. Prenons un exemple simple, nous avons saisi la formule =alea() en A1, nous l’avons recopiée vers le bas jusqu'en A10, et nous avons enfin tracé un graphe.


En ce qui nous concerne, le graphe a été défini par les valeurs de A1:A10, ce qui entraîne deux conséquences…

Si nous tirons A10 jusqu’en A13, le graphe reste bloqué sur A1:A10 et n’inclut pas les trois nouvelles valeurs.

Pire encore, si nous effaçons A8:A10 pour ne garder que les 7 premières valeurs, le graphe continue à afficher l’axe des X avec des valeurs de 1 à 7, laissant supposer que les trois dernières valeurs sont à 0.

Remarque – Certes, un observateur perspicace pourra dire que, si ces trois valeurs étaient à 0, cela serait marqué par un segment bleu sur l’axe des X. Mais, vous le savez comme moi, les gens perspicaces sont minoritaires…

Quand vous cliquez sur la série, la barre de formule affiche : =SERIE(;;Graphe!$A$1:$A$10;1).

En utilisant la technique présentée dans le dernier article, définissez le nom élastique Mes_Y par :
=Graphe!$A$1:decaler(Graphe!$A$1;nbval(Graphe!$A:$A)-1;0)

Allez alors dans la barre de formule et remplacez $A$1:$A$10  par mes_y.

Vous constatez alors que le graphe s’adapte immédiatement aux 7 valeurs restantes.

Resélectionnez le graphe et consultez la barre de formule. Vous pouvez voir que le nom de l’onglet a été remplacé par celui du classeur. Pourquoi ? Tout simplement parce qu’un nom est une propriété du classeur alors qu’une adresse (A1:A10) est une propriété de l’onglet !

Et voilà ! Vous avez à présent un graphe élastique.
Tirez A7 vers le bas et les nouvelles valeurs sont aussitôt intégrées dans le graphe.