Simulation de dés (a)

Nous nous attaquons aujourd’hui, forts de notre nouvelle expérience en gestion du calcul itératif – cf. les trois derniers articles – dans la simulation de 10 lancements d’un dé. Nous avons construit à cet effet le modèle suivant :

Voici les formules de ce modèle :

B3 : =SI(B2=0;0;B3+1)

B4 : =SI(B2=0;"";ENT(6*ALEA()+1))

E1 (recopiée jusqu’en E6) : =SI($B$2=0;0;E1+SI($B$4=D1;1;0))

F1 (recopiée jusqu’en F6) : =E1/$B$3

E7 (recopiée en F7) : =SOMME(E1:E6)

Nous sommes pour le présent en calcul automatique, comme c’est le cas par défaut dans Excel.

Si tout se passe bien, nous pourrons ensuite nous mettre en calcul manuel, avec un maximum de 10 itérations, entrer 1 en B2, puis faire [F9] pour lancer la simulation des 10 dés.

Eh bien, si vous le faites, vous allez avoir la surprise de votre vie !

 

Voici donc un exemple de résultat obtenu après une telle simulation :

 Manifestement, nous devons être en train de jouer avec un truand qui a un dé pipé !

Dans le prochain article, nous découvrirons d’où vient le problème, puis comment le résoudre…

Calcul du nombre d’itérations

Pour en terminer avec mes messages du 10 et du 15 janvier, nous allons voir aujourd’hui comment calculer le nombre d’itérations requis dans notre exemple de calcul de marge pour obtenir le résultat final au centime près. Pour cela, nous devons utiliser les réglages que vous pouvez voir dans l’encart de la copie d’écran ci-dessous.

Notez que nous avons demandé un calcul sur ordre avec 100 itérations au maximum (ce sera plus que nécessaire pour obtenir la précision demandée) et un écart de 0,01 qui correspond au centime requis comme précision.

Il y a encore deux problèmes à résoudre :

• comment faire pour réinitialiser les valeurs après un calcul de marge ?

• comment obtenir une précision de 0,01 si le compteur d’itérations est incrémenté de 1 à chaque itération ?

Réinitialisation de la simulation

Pour réinitialiser les valeurs après un calcul itératif, la solution la plus efficace est de créer un drapeau que l’on met à 0 pour réinitialiser puis à 1 quand on souhaite que le calcul itératif s’effectue. Nous avons donc modifié en conséquence les formules de B4 et B5, comme vous pouvez le voir en C4:C5 dans la copie d’écran ci-après.

Gestion du compteur d’itérations

Pour que le compteur d’itérations ne casse pas la convergence en ajoutant 1 à chaque itération – auquel cas l’écart maximal de 0,01 ne serait jamais obtenu – l’astuce consiste simplement à compter les itérations en millièmes, donc en ajoutant à chaque itération un incrément inférieur à 0,01 ! C’est donc la formule que nous avons entrée en F2.

Convergence vers la solution finale

Il ne nous reste plus qu’à saisir 1 dans le drapeau en E2 et à lancer le calcul par [F9]. Le résultat apparaît instantanément, avec 142,85 € en B4 et 0,009 en F2. Il a donc suffi de 9 itérations pour converger vers la solution !

Réglage de la référence circulaire

Cet article fait la suite de notre première présentation des références circulaires, le 10 janvier dernier.

Quand on active, en cochant « Activer le calcul itératif » dans la commande Fichier – Options – Formules, les références circulaires, on a accès à deux réglages, le nombre maximal d’itérations et l’écart maximal.

Le nombre maximal d’itérations est le nombre d’itérations qui vont être effectuées lors d’un calcul de la feuille. Fixons-le à « 1 » pour l’instant. Cette commande n’a donc de sens que si l’on a sélectionné « Manuel » dans le bloc « Calcul du classeur », comme dans l’encart dela copie d’écran ci-dessous :

Effaçons à présent la formule de la cellule B4, lançons un calcul avec [F9] pour que la cellule B3 retrouve sa valeur de 0, puis ré-entrons la formule de sommation en B4.

Le prix de vente est maintenant de 100 euros puisque seule la cellule B4 a pu être calculée, au moment où sa formule a été validée :

Lançons un calcul avec [F9] et nous constatons que B3 prend à présent une valeur de 30,00 € et B4 une valeur de 130,00 €.

Lançons un nouveau calcul avec [F9], et nous obtenons 39,00 € en B3 et 139,00 € en B4.

Lançons encore un calcul avec [F9], et nous obtenons 41,70 € en B3 et 141,70 € en B4.

Avec six calculs de plus, le résultat final avec 42,86 € en B3 et 142,86 € en B4 est enfin obtenu... Mais on était déjà à la bonne réponse à 1 centime près avec le quatrième calcul.

Quand on ne limite pas, comme nous l'avons fait ici, à 1 le nombre d'itérations, Excel arrête le calcul dès que soit le nombre maximal d’itérations a été atteint, soit l’écart maximal entre le calcul de n° (n-1) et le énième calcul – pour les valeurs de toutes les cellules du classeur – est inférieur à l’écart maximal défini.

Création d’une référence circulaire

Nous allons aujourd’hui nous attaquer à un problème qui vous a sûrement intrigué un jour ou l’autre : l’apparition d’une référence circulaire !

Réalisons un petit modèle dans lequel nous entrons un prix d’achat en B2, nous déclarons en B4 que nous allons faire une marge de 30% du prix de vente et, en B5, nous demandons la somme grâce à l’outil de sommation.

Et patatras ! Excel nous annonce qu’il ne peut pas calculer la formule car il y a une référence circulaire :

Remarque – Le message d’Excel est inexact et, de plus, moins clair qu’il ne l’était dans les versions antérieures du logiciel : en effet, Excel ne semble plus envisager que vous ayez pu construire cette référence circulaire volontairement !

Il est vrai que, bien souvent, les références circulaires sont tout à fait involontaires. Il s’agit en général d’erreurs de conception qu’Excel signale avec raison.

Mais il se peut cependant – c’est le cas ici – qu’il ne s’agisse pas d’une erreur mais que nous souhaitions effectivement calculer un résultat par convergence. C’est par exemple indispensable si vous construisez un modèle de calcul d’ISF (Impôt Sur la Fortune) : en effet, dans ce cas, on peut déduire, pour calculer la base imposable, le montant de l’ISF à payer qui dépend de cette même base imposable ! Encore une invention d’énarque...

Pour valider le calcul en références circulaires, il vous suffit d’utiliser la commande Fichier – Options – Formules, et de cocher « Activer le calcul itératif », comme nous le voyons dans la copie d’écran ci-dessous.

Le résultat final de 142,86 € apparaît alors aussitôt dans la cellule B4 !

Et voilà, ce n’est pas plus compliqué que cela...

Nous verrons dans le prochain article comment tirer profit des deux réglages suivants, le nombre maximal d’itérations et l’écart maximal.