Monsieur Excel
Pour tout savoir faire sur Excel !

14 février 2018

Simulation de dés (d)

Une macro pour la simulation...

Il est un peu fastidieux de devoir modifier à la main, chaque fois que l’on souhaite relancer une simulation ou revenir en mode normal, le statut de calcul : itératif ou pas, automatique ou non, …

D’autant plus que la commande « Options Excel » n’est pas d’un accès immédiat…

Avec les deux macros représentées ci-dessous, nous pouvons automatiser le processus et, au choix, réinitialiser ou lancer la simulation.

Il suffit à présent d’un clic sur le bouton adéquat pour effectuer l’opération désirée.


09 février 2018

Simulation de dés (c)

Dans l’article précédent, nous expliquions qu’Excel avait la particularité – lors du calcul d’un classeur – de n’effectuer que les calculs qui, selon lui, étaient nécessaires.

Il y a une autre chose fondamentale, concernant le calcul par Excel, qu’il faut savoir. C’est qu’Excel ne peut pas calculer toutes les cellules à la fois, il faut bien qu’il procède par ordre. Et cet ordre est le suivant :
  • Excel calcule ligne par ligne, du haut vers le bas ;
  • dans chaque ligne, Excel calcule de gauche à droite.
Bien entendu, quand les formules l’y obligent, Excel est un peu obligé d’aller voir ailleurs pour les évaluer, mais cela lui complique la tâche.

Ceci explique qu’un modèle où chaque formule ne dépend que de cellules placée au-dessous d’elle et/ou à sa gauche se calcule plus vite qu’un modèle où cette règle n’est pas respectée.

Reprenons notre dernier modèle :


Quand nous débutons le calcul de l’itération n°10 en calculant la ligne n°1, toute la partie du modèle ci-dessus placée en dessous, c’est-à-dire l’ensemble des lignes 2 à 7, se trouve encore en l’état de l’itération n°9. Donc le compteur en B3 est toujours à 9. C’est pour cela que le pourcentage en F2 – et celui en F1, mais on ne le voit pas ici car il est nul – est obtenu en divisant la valeur de la colonne E par 9.

Quand le calcul passe à la ligne n°3, B3 passe à 10 et les pourcentages de F3 à F6 sont tous justes.

Quand le calcul passe à la ligne n°4, B4 passe à 3, mais ce dé est ignoré car le test en E2 pour vérifier si l’on avait obtenu un 3 a déjà été calculé. On perd donc le dixième dé quand celui-ci est un 1, un 2 ou un 3 car, dans ces cas, le dixième test en E1, E2 ou E3 ne peut s’effectuer.

Le remède est donc tout simplement de déplacer le bloc D1:F7 d’au moins 3 lignes vers le bas de façon que, désormais, tous les calculs s’effectuent bien du haut vers le bas, et de gauche à droite.

Voici le résultat d'une nouvelle simulation :


Remarque – La morale de l’histoire est que, même si Excel – avec la fonction alea() et le calcul itératif – vous permet de créer un modèle de simulation stochastique (joli mot pour dire aléatoire), vous devez prendre un certain nombre de précautions pour être sûr que ce modèle obtienne des résultats exacts !

03 février 2018

Simulation de dés (b)

La clef du problème rencontré dans l’article précédent est la façon dont Excel procède lors d’un recalcul.

La formule de B4, celle qui tire le dé, est : =SI(B2=0;"";ENT(6*ALEA()+1)).

Pour éviter de perdre son temps, Excel essaye – lors d’un calcul itératif – de ne calculer que les cellules qui – selon lui – ont besoin d’être recalculées.

Quand il se trouve au début de l’itération n°2, Excel analyse la formule et raisonne ainsi :
  • B2 n’a pas changé depuis l’itération n°1…
  • le reste de la formule ne dépend d’aucune cellule, et donc n’a pas changé non plus
  • en conséquence, il n’y a aucune raison de se fatiguer à recalculer la formule !
Le remède est simple ! Pour que le dé se recalcule à chaque itération, il ne faut pas qu’il dépende de B2 mais plutôt d’une cellule qui – elle – change à chaque itération. Ce qui, heureusement pour nous, est le cas du compteur d’itérations :)

Et voilà la solution ! Changeons maintenant la formule de B4 en =SI(B3=0;"";ENT(6*ALEA()+1)).
Réinitialisons le tableau en mettant le drapeau à 0 et en faisant [F9]. Puis remettons le drapeau à 1 et lançons la simulation. Nous obtenons alors – par exemple – le tableau suivant :


Bon, il y a un net progrès, nous ne sommes plus en train de jouer avec un mafieux et son dé pipé !
Mais il reste quand même trois problèmes :
  • pourquoi les % en F1:F6 ne sont-ils pas tous des multiples de 10 % ?
  • le dernier dé – un 2 – n’a pas été pris en compte !
  • pourquoi n’y a-t-il pas un total de 100% en F7 ?
Nous vous donnerons les réponses à ces questions, et la parade à ces problèmes, dans le prochain article.