Monsieur Excel
Pour tout savoir faire sur Excel !

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…