Monsieur Excel
Pour tout savoir faire sur Excel !

22 juillet 2017

Passagers d’un avion (c)

Aujourd’hui, nous nous attaquons aux deux formules principales qui sont le cœur de ce modèle, celles des cellules F2 et G2.

Formule de F2

En F2, nous voulons tirer un  nombre au hasard qui sera lié au siège référencé en colonne D si ce siège n’a pas encore été attribué dans la colonne précédente. S’il a déjà été attribué, nous affichons un « 0 ».

F2 : =si($A$1=colonne()-4;si(estnum(equiv($D2;E$1:decaler(E$1;E$23-1;0);0));0;alea());F2)

Avec =si($A$1=colonne()-4;…;F2), on dit tout simplement que si le drapeau en A1 n’a pas la valeur associée au calcul de cette colonne, la valeur de la cellule reste égale à ce qu’ele était auparavant.

Si estnum(equiv($D2;E$1:decaler(E$1;E$23-1;0);0)) est vrai, cela signifie que le siège en colonne D a été attribué, auquel cas on met un « 0 » dans la cellule. En revanche, s’il ne l’a pas encore été, on calcule un aléa entre 0 et 1-e.

Formule de G2

Si $A$1=colonne()-4 est faux, ce n’est pas le moment de calculer la colonne, on reprend la valeur antérieure de la cellule.

Sinon, en G2, on attribue le siège de la colonne E si ligne(), donc si l’on n’a pas encore atteint le « Pbm ». Si l’on se trouve dans la ligne du problème, on attribue le siège correspondant dans la colonne D à la position du plus grand aléa de la colonne F.

G2 : =si($A$1=colonne()-4;si(ligne()

Formats conditionnels

Vous pouvez voir dans la copie d’écran les formules de format conditionnel que nous avons utilisées pour mettre les cellules à problème en fond jaune et – en fond beu ciel – les paires regroupant le siège sélectionné et le nombre aléatoire ayant permis de l’obtenir.

Dans la copie d’écran, on constate que le siège de remplacement en G10 a été obtenu grâce aux cellules à fond bleu E11 et F11.

La condition du premier format conditionnel est : =$F2=max($F$2:$F$21).


17 juillet 2017

Passagers d’un avion (b)

Un souci principal dans la création de ce modèle est que – pour faciliter le contrôle de ce qui s’y passe – les nombres aléatoires tirés à un moment donné demeurent et ne soient pas écrasés par un retirage ultérieur. J’ai donc, via la commande Outils Options, réglé Excel en mode Calcul manuel, Itératif avec 1 itération.

En A1, j’ai placé un compteur qui sera incrémenté de 1 à 15 pour calculer tour à tour la colonne C, puis les colonnes F à O. Cela me permet de m’assurer que les aléas calculés en colonne C grâce à la formule =si($A$1=1;alea();C2) en C2 ne soient pas recalculés quand ce compteur sera plus grand que 1.

Voici une copie d’écran avec un nouveau tirage du remplissage de l’avion :


En D2, la formule =rang(C2;C:C) calcule le rang du nombre aléatoire. Comme celle de C2, elle est reproduite vers le bas.

La formule de E3, reproduite elle aussi vers le bas, est : =si(estna(equiv(D3;E$1:E2;0));D3;"pbm"). Elle souligne qu’il y a un problème dès lors que le siègé réservé par le client est déjà occupé.

En E23, recopiée vers la droite une colonne sur 2, la formule =sierreur(equiv("pbm";E1:E21;0);"Fin") permet d’afficher « Fin » quand la simulation est terminée.

Enfin, en A23, la formule =si(index(21:21;equiv("Fin";23:23;0))=D21;"Gagné";
"Perdu") permet de savoir si l’on a gagné ou non.

Il ne vous reste plus qu’à imaginer quelles sont les formules en G2:H2 que nous allons pouvoir recopier vers le bas, puis par bloc de deux colonnes vers la droite, pour que tout fonctionne parfaitement. Je vous donnerai ma solution dans mon prochain article.

11 juillet 2017

Passagers d’un avion (a)

Le modèle complet dont je vous ai parlé dans mon dernier article possède un inconvénient : comme il faut 100 lignes si l’on consacre une ligne à chaque passager, vous ne pouvez pas avoir une vue d’ensemble de ce qui se passe sur un écran où l’on peut seulement voir d’un coup une vingtaine de lignes en taille normale.

J’ai donc décidé de traiter dans les articles qui viennent le cas d’un avion de seulement 20 places, sachant qu’il sera aisé ensuite d’étendre le modèle à 100 places.

J’ai aussi fait l’hypothèse que le premier passager, quelle que soit la place qui lui est attribuée, s’installe toujours sur le siège n°1. Avec 20 places dans l’avion, on est donc sûr que le dernier passager à s’installer pourra se mettre sur son siège réservé dans plus de 5% des cas, puisqu’il y a déjà une chance sur 20 que le premier passager s’installe sur le siège qui lui était réservé au départ.

Un autre souci que j’avais était que l’on puisse vérifier chaque étape de l’attribution des sièges afin de pouvoir contrôler à tout moment le bon fonctionnement du modèle.

Voici donc le modèle que nous allons construire :


On réserve les places en colonne D en fonction des aléas en colonne C.

Quand on tombe sur un siège occupé (D7), on attribue (G7) le siège correspondant au plus gros nombre aléatoire tiré en colonne F, soit le siège 8 (cf. E9-F9).

Et ainsi de suite… Le modèle affiche « Pbm » dès qu’il y a un conflit sur un siège.

Dans cet exemple, le passager n°1 a « volé » le siège n°1.
Le passager n°6, du coup, a occupé le siège n°8.
Le passager n°8, du coup, a occupé le siège n°20.
Le passager n°16, du coup, a occupé le siège n°4.
Et, comme c’est le siège qui était au départ destiné au passager n°1, tout est rentré dans l’ordre, ce qui a abouti à « Gagné » en A23.

Je commencerai à détailler la conception de ce modèle dans le prochain article.

06 juillet 2017

Enigme : passagers d’un avion

Voici une énigme sur laquelle je suis tombé il y a quelque temps…

Dans un avion de 100 places plein, sans no-show ni overbooking, chaque passager a une place réservée. Mais le premier passager à monter s'installe où bon lui semble, au hasard. Les autres passagers essayent de s'asseoir à leur place réservée mais, si celle-ci est déjà prise, ils prennent au hasard une place libre (ils sont trop timides pour vider l’intrus).

Quelle est la probabilité que le dernier passager puisse s'asseoir à la place figurant sur son billet ?

J’ai trouvé une solution à ce problème, que je vais commencer à expliquer à partir du prochain article. Si par hasard vous touvez une réponse, n’hésitez pas à me l’envoyer à thiriez@hec.fr.

Après avoir publié ma solution, je publierai la meilleure réponse que j’aurai reçue si elle est meilleure que la mienne. Votre réponse peut être analytique (par des équations) ou – à l’instar de celle que je vous proposerai – obtenue à l’aide d’Excel.

Cet exercice sera l’occasion pour nous d’explorer des fonctionnalités spéciales d’Excel liées au calcul itératif…

30 juin 2017

Informations sur Windows

Vous avez parfois besoin de savoir exactement quelle est la version de Windows installée sur votre ordinateur. Cela peut par exemple être indispensable quand vous êtes victime d’un plantage – ou même d’un simple dysfonctionnement – et que le dépanneur vous demande ce renseignement.

Mon collègue MVP et ami Jon Peltier a trouvé pour cela un petit bijou sur Internet :


Comme vous le voyez dans la copie d’écran ci-dessus, il vous suffit de taper :
? GetWindowsInfo
dans la fenêtre Exécution du VBA pour obtenir la réponse.

Jon est, à l’instar de tous les MVPs Excel, un expert dans ce domaine. Mais il possède aussi une compétence toute particulière dans le domaine des graphes, dans lequel il a créé des add-ins particulièrement intéressants.

Pour en savoir plus : http://peltiertech.com/

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.