Monsieur Excel
Pour tout savoir faire sur Excel !

28 juillet 2017

Passagers d’un avion (d)

Le modèle tel que je l’ai décrit dans les artices précédents permet de simuler le remplissage d’un avion. Il faut pour cela, en passant par Fichier – Options – Formules, se mettre en cacul manuel avec itérations autorisées, 1 itération. Puis entrer 1 en A1 et lancer le calcul avec [F9].

Puis on entre 2 en A1 et on relance le calcul. Puis 3 et on relance le calcul. Et ainsi de suite jusqu’à ce qu’il n’y ait pas de « pbm » dans la colonne qui vient de se calculer. On voit alors en A23 si l’on a perdu ou gagné.

Cette approche pas à pas est parfaite pour bien saisir la logique de l’affectation des places. Mais, comme elle est fastidieuse, j’ai écrit une macro qui fait tout le travail à votre place. Sauf que, pour en simplifier le code, je ne vérifie pas si l’avion est déjà rempli : je fais 15 itérations quoi qu’il arrive.

Sub One_shot()
For I = 1 To 15
    Range("A1") = I
    Calculate
Next I
End Sub

Remarque – Nous aurions pu accélérer le traitement en arrêtant la macro dès qu’il n’y a plus de « pbm »  dans la colonne active. Ou aussi en mettant des formules plus compliquées encore en F2, G2 et G23 pour s’arrêter dès qu’un avion est rempli.

Mais notre modèle est déjà bien assez compliqué comme cela…

L’écran ci-dessous montre un remplissage complet de l’avion qui aboutit à « Gagné » : le client n°7 qui avait réservé la place n°1 est dérouté sur la place n°16. Du coup, le client n°14 prend la place n°5, le client n°16 prend la place n°11, le client n°18 prend la place n°3 et, comme celle-ci était celle prévue au départ pour le premier client, tout se termine bien pour les deux derniers passagers.




rédigé par Hervé Thiriez | 0 commentaire(s)

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


rédigé par Hervé Thiriez | 0 commentaire(s)

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.

rédigé par Hervé Thiriez | 0 commentaire(s)

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.

rédigé par Hervé Thiriez | 1 commentaire(s)

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…

rédigé par Hervé Thiriez | 0 commentaire(s)