Monsieur Excel
Pour tout savoir faire sur Excel !

28 décembre 2015

Serpents et échelles (e)

Mes meilleurs vœux !

Puisque ceci est mon dernier message de l’année, je vous souhaite à tous une excellente année 2016 !

Comme nous venons de vivre Noël, je veux partager avec vous ce joli dessin qui nous vient de Pologne :

Serpents et échelles

Nous répondons aujourd'hui à la seconde question posée dans la série « Serpents et échelles ». Quelle est la probabilité que le premier joueur gagne ?

En fait, en partant du tableau développé dans l’article précédent, il n’y a presque rien à faire : il suffit de remplacer la formule de M1 en entrant :
=1*(E2<=I2).

Avec quelques essais, on constate facilement que la bonne réponse est 53%, soit la réponse (b).


Quand le calcul est-il fini ?

Quand j’ai une table comme cela, je me mets en général en mode de calcul « Automatique sauf les tables ».

Je ne sais pas si vous avez alors le même problème que moi… Pour ma part, quand une table met un certain temps à se calculer, j’ai parfois du mal à voir si le calcul a été fait, pour peu que je n’aie pas mémorisé le résultat qui était affiché avant le lancement du calcul.

Certes, on pourrait mettre une macro événementielle qui, à la fin du calcul de la table, affiche un message du type « Fini ! ». Mais, en règle générale, j’essaye de ne pas utiliser – sauf cas de force majeure – les macros événementielles, car cela induit un certain nombre de problèmes.

La solution que j’utilise ici est tout simplement d’afficher en O4000:P4000 la somme de la colonne M et la moyenne. Je vois alors plus facilement quand ces deux cellules se mettent à jour à la fin du calcul de la table.

La question n°4

Reste à poser la quatrième question afin que vous puissiez vous occuper intelligemment durant ces longues fêtes…

Vous trouvez que la situation est un peu trop favorable pour le joueur n°1. Pour équilibrer les chances des deux joueurs, donc ramener la probabilité de gain de chaque joueur à près de 50%, vous décidez que le joueur n°2 débutera son jeu sur une autre case.

Sur quelle case le joueur n°2 doit-il démarrer pour égaliser les chances ?
  • case n°3
  • case n°6
  • case n°9
  • case n°12



22 décembre 2015

Serpents et échelles (d)

Pour répondre à la seconde question, il nous faut ajouter trois colonnes pour le joueur n°2, en I à K.

La formule en M1 est élémentaire : =max(E2;J2).

Il ne reste plus qu’à créer la table en sélectionannt L1:M4000 et en mettant en D1 comme « cellule d’entrée en colonne ».

Pour ma part, j’obtiens en fait une moyenne de 14, soit une réponse qui est intermédiaire entre les deux premières réponses de la liste du QCM. Cela me surprend car, quand j’ai réalisé ce modèle il y a deux mois, j’avais le souvenir que j’avais trouvé la seconde réponse. Si un lecteur peut me dire où est mon erreur, je corrigerai cet article.


Question n°3

Pour le prochain article, voici la question sur laquelle vous pouvez travailler…

Avec deux joueurs, quelle est la probabilité que le joueur 1 (qui joue en premier) gagne ?
  • 50%
  • 53%
  • 57%
  • 60%



17 décembre 2015

Serpents et échelles (c)

La dernière phrase de l’article précédent, disant qu’il fallait en moyenne 11 coups pour atteindre l’objectif, peut paraître – à bon escient ! – un peu rapide.

La meilleure façon de valider cette réponse est de construire une table afin de pouvoir simuler d’un coup un grand nombre de parties.

C’est ce que nous avons fait dans l’exemple suivant. Nous entrons la formule =E2 en I9. Nous sélectionnons la zone H1:I1000 et nous créons une table avec la cellule D1 comme paramètre de colonne. Nous voyons ci-dessous le résultat de cette table. Nous avons aussi affiché en colonne L des statistiques avec les formules reproduites en colonne M.


Remarque 1 – Nous avons déjà utilisé à plusieurs reprises cette façon extraordinaire de lancer une série d'itérations avec Excel : cela consiste à placer les valeurs de la colonne H – qui sont toutes vides – dans la cellule D1 – qui ne sert à rien ! –. Mais cela force Excel à boucler 1.000 fois sur les calculs.

Remarque 2 – Pour obtenir des résultats plus serrés autour du résultat moyen de 11 lancers de dés, il suffirait de faire une table avec 4.000 lignes au lieu de 1.000.

On sait en effet, en statistiques, que si l’on estime un pourcentage à l’aide d’un échantillon, que l’écart-type est égal à 1/2*racine(n), n étant la taille de l’échantillon. Avec un échantillon 4 fois plus grand, on est donc deux fois plus précis…

Question n°2 – Nous avons bien répondu à la question 1. Vous pouvez maintenant vous attaquer à la question n°2. Quand il y a deux joueurs, combien de coups faut-il en moyenne pour que les deux joueurs aient terminé la partie ?

Réponses possibles :
  • 13 coups
  • 15 coups
  • 17 coups
  • 19 coups
Je vous laisse travailler...


12 décembre 2015

Serpents et échelles (b)

Si l’on possède à la fois un esprit clair et une bonne maîtrise d’Excel, la modélisation de ce jeu ne présente aucune difficulté majeure.

Nous créons une table en B1:C34 indiquant dans quelle case on parvient (en colonne C) quand on atteint une case donnée (en colonne B). Pour faciliter la lecture et la vérification de cette table, je lui ai mis des formats conditionnels affichant les échelles en bleu sur fond jaune et les serpents en italique rouge sur fond cyan.


La formule de F1, reproduite vers le bas, est : =ent(1+6*alea()).

Remarque 1 – On aurait aussi pu utiliser la fonction alea.entre.bornes(), mais – selon mon opinion – cette seconde formule est plus lourde…

La formule de G1 est : =index($C$1:$C$34;F1).
La formule de G2, reproduite vers le bas, est : =sierreur(index($C$1:C$34;F2+G1);34).

La formule de E2, qui indique le nombre coups requis pour atteindre l’objectif, est :
=EQUIV(34;G1:G101;0).

En réalisant une série de simulations, on constate que la moyenne du nombre de coups requis pour atteindre l’objectif est de 11.

Remarque 2 – J'ai mis "E1" en J1 pour indiquer qu'il s'agit du joueur n°1. On s'intéressera plus tard à un second joueur...

07 décembre 2015

Serpents et échelles (a)

Le jeu des « serpents et échelles » est un jeu ancien, une sorte de jeu de l’oie. Vous en trouverez une description détaillée dans Wikipédia à l’adresse https://fr.wikipedia.org/wiki/Serpents_et_%C3%A9chelles.

Avec l’exemple ci-dessous, quand le joueur, en tirant un dé, tombe sur la case 20, il monte aussitôt en 31. Si en revanche il tombe sur la case 33, il dégringole en 19. Le but du jeu est d’atteindre ou de dépasser la case finale en 34. Le gagnant est le premier qui atteint cet objectif.


Cette année, ce jeu était la base d’une des épreuves de la sélection du concours international ModelOff de modélisation financière sur Excel : http://www.modeloff.com/.

Votre mission, jusqu’à mon prochain post sur ce blog, est de construire le modèle Excel et de répondre à la première question : Si vous jouez seul, combien de coups vous faut-il en moyenne pour terminer le jeu ?

Votre réponse correspond à un QCM avec les valeurs suivantes :
  • 7 coups
  • 9 coups
  • 11 coups
  • 13 coups

01 décembre 2015

Mes formations en janvier 2016

Vous trouverez ci-dessous la liste des formations que je propose pour le mois de janvier 2016. Chaque formation est limitée à huit participants.
Les autres séminaires habituels ne sont pas proposés en inter lors de cette session. Toutes les formations proposées sont organisées en association avec la société EuroDécision (www.eurodecision.com). Elles auront lieu à Paris, au 14 avenue de l’Opéra.

La formation « Création de tableau de bord sous Excel » vous permettra, en une seule journée, de totalement maîtriser la création d’un tableau de bord personnalisé, comme vous pouvez le voir dans l’article « Notre premier tableau de bord » du 22 mai 2013.

La formation « Modélisation avec Excel » est mon best-seller intégral : c’est une formation que j’ai animée plus de 100 fois, en inter ou en intra, avec un taux de satisfaction proche de 100%. Avec cette formation, n’importe quel utilisateur d’Excel parvient en deux jours à atteindre le niveau du top 5% des utilisateurs d’Excel.

Avec la formation « Découverte de Visual Basic », vous ne saurez pas tout sur le VBA, ce qui n'est pas possible en si peu de temps. Mais vous en saurez assez pour développer vos propres macros et savoir où trouver ce que vous ne savez pas encore.

Toutes mes formations (cf. catalogue sur ce blog) peuvent être animées en intra dans votre entreprise et – le cas échéant – personnalisées grâce à l’analyse et à l’amélioration des modèles propres à votre entreprise.

Ne loupez pas cette occasion de découvrir tout cela de la bouche même de l’auteur de ce blog, qui partagera avec vous l’expérience qu'il acquise en développant plus de 1.000 modèles dans plus de 100 entreprises en plus de 10 pays.


Quelques-unes de mes références de formation intra : Aéroports de Paris, Aérospatiale, Arianespace, Bouygues, Caisse des dépôts, CASE-Poclain, CCIP, Cegelec, CNES, CNET, EADS, EdF, Elf, Ernst & Young, Euroconsult, Finacor, France Telecom, Gaz de France, GIAT, IFP, Isochem, Lafarge, La Poste, Lilly France, Marsh, RTE, Sanofi, SIRIS, Texas Instruments, Tir Groupé, Total, Wabco, Walt Disney.