Comment Excel recalcule-t-il ?

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 deux problèmes :

- pourquoi les % en F1:F6 ne sont-ils pas tout des multiples de 10 % ?

- le dernier dé – un 2 – n’a pas été pris en compte !

Nous vous donnerons les réponses à ces questions, et la parade à ces problèmes, dans le prochain article.