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.


28 janvier 2018

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…

21 janvier 2018

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 !

15 janvier 2018

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.

10 janvier 2018

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.