Monsieur Excel
Pour tout savoir faire sur Excel !

16 août 2017

Et la touche « Windows » !

La touche Windows, dont quelques exemples d’icônes sont reproduits ci-dessous, vous donne un accès direct à une flopée de raccourcis. Ces raccourcis sont certes utilisables pour les autres applications Windows, mais ils sont quand même bien utiles quand on travaille avec Excel.


C’est probablement dans Excel la source de raccourcis la plus méconnue. Mais il est vrai que la plupart de ces raccourcis ne concernent pas directement Excel.

Win + D (Desktop) : réduit toutes les applications ouvertes.

Win + E (Explorer) : ouvre l’explorateur de fichiers

Dans de nombreuses situations, c’est la façon la plus rapide d’accéder dans Excel à l’ouverture de fichiers sur lesquels on a travaillé récemment. On voit d’un coup d’œil la liste des derniers fichiers ouverts, toutes applications confondues, avec leur chemin complet.

Win + L (Lock) : verrouillage immédiat de l’écran.

Win + M (Minimize) : réduit toutes les applications ouvertes (comme Win+D). Je ne vois pas bien l’intérêt de ce double emploi…

Win + R (Run) : il faut entrer le nom d’un programme, dossier, document ou ressource Internet que l’on demande à Windows d’ouvrir.

Win + P (Project) : pour modifier le lieu de projection de l’écran.


Win + n° : ouvre la tâche placée dans cette position dans la barre des tâches en bas de votre écran. Ainsi, si l’icône de Powerpoint est la troisième à droite de l’icône de Windows dans la barre des tâches, Win + 3 ouvre Powerpoint. Cela joue en quelque sorte le rôle d’un [Alt]-[Tab] intelligent.

Win + flèche de déplacement : redimensionne et/ou déplace la fenêtre Excel.

Win + PgUp/PgDn : déplace la zone affichée dans Excel d’une page vers le haut/vers le bas.

10 août 2017

Commentaires sur l’énigme

L’énigme que nous avons étudiée dans les articles précédents avait été proposée par M. Laurent Poli à la société Eurodécision qui publie plusieurs fois par an des énigmes sur son blog. Lui-même a d’ailleurs trouvé une autre solution presque entièrement par VBA, et qui aboutit au même résultat. Sa solution est donc plus compacte que celle que je vous ai proposée, mais ne permet pas de suivre aussi facilement la façon dont les sièges de l’avion se remplissent.

D’autres personnes ont trouvé la solution par récurrence. Il est clair que, dans le cas de 2 passagers, il y a une chance sur deux que le second passager puisse occuper la place qui lui était réservée.On vérifie aisément que c’est encore vrai avec 3 passagers. Reste alors à construire le raisonnement par récurrence qui prouve qu’il en sera de même avec n+1 passagers si c’est vrai avec n passagers…

Quel est l’avantage de passer par un modèle de simulation tel que celui que je vous ai proposé, par rapport par exemple à une solution analytique ? En fait, il y en a deux.

Le premier est que mon modèle de simulation permet de suivre pas à pas, de façon visuelle, le remplissage de l’avion et de vérifier à tout moment le bon fonctionnement de l’algorithme. C’est donc à la fois un outil pédagogique et un contrôle de sécurité permanent.

Le second est que, s’agissant d’une simulation, on peut intégrer sans limite des règles ou conditions supplémentaires, ce qui ne serait pas possible avec une approche analytique. On peut par exemple décider que toute personne ne pouvant s’asseoir à sa place réservée s’assiera sur la place vide la plus proche de celle-ci. Ou, dans une autre simulation, sur une place de même nature (fenêtre, couloir).

Si vous voulez en lire plus sur cette énigme, voici quelques références qui m’ont été proposées par Laurent Poli :

Ou alors, aussi par simulation mais sans Excel, avec l’aide du logiciel gratuit d’analyse statistique « R » :

Si vous désirez recevoir à titre gracieux mon fichier Excel, envoyez-moi un mail à cet effet à :

04 août 2017

Passagers d’un avion (e)

Maintenant que nous avons un modèle qui simule parfaitement le remplissage d’un avion, il ne reste plus qu’à simuler un grand nombre de remplissages pour voir quelle est – en moyenne – la probabilité pour que le 100ème passager puisse occuper la place qui lui était réservée.

Voici à quoi ressemble le modèle final. La macro présentée dans l’article précédent se lance avec le bouton « 1 scénario ». La macro de simulation complète se lance avec le bouton « Simulation ». Elle fait 10 boucles de 100 itérations dont les résultats sont affichés en Y2:Y101, puis stocke le résultat en colonne Z. Enfin, Z13 affiche la moyenne ainsi obtenue sur 1.000 remplissages de l’avion.


Voici le code de la macro :

Sub Simulation()
'
' Auteur : Hervé Thiriez
'
Dim I As Integer
Dim K As Integer
Dim L As Integer

Range("V2:V11").ClearContents
For L = 1 To 10
    Range("Y2:Y101").ClearContents
    Application.ScreenUpdating = False
    For K = 1 To 100
        Range("A1") = 1
        Calculate
        For I = 2 To 19
            Range("A1") = I
            Calculate
        Next I
        Cells(K + 1, 25) = Range("Rés")
    Next K
    Cells(L + 1, 26) = Range("Pct").Value
    Application.StatusBar = "Fin du traitement n°" & L
    Application.ScreenUpdating = True
Next L

Application.ScreenUpdating = True
Application.StatusBar = ""
Calculate
End Sub

On obtient donc le résultat moyen de 50%. Notez qu’il est important de reproduire les colonnes F:G jusqu’à V:W. En effet, au début, je m’étais arrêté 4 colonnes plus tôt et j’obtenais alors un résultat moyen de plus de 60% !

Et voilà ! Nous avons trouvé la solution de l'énigme :)

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 FichierOptionsFormules, 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.




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.