Réglage de la référence circulaire

Cet article fait la suite de notre article « Création d’une référence circulaire » présenté le 14 février.

Pour avoir accès aux réglages du calcul itératif, il nous a fallu utiliser la commande « Options » d’Excel, puis l’onglet « Formules » :

Dès que nous avons coché l’option « Activer le calcul itératif », Excel a fait très rapidement quelques itérations et s’est aussitôt arrêté sur la solution finale de 142,86 €.

Quand on active le calcul itératif, il faut immédiatement activer l’option de calcul manuel, car il est alors important de contrôler le moment où l’on lance le calcul itératif. Celui-ci appliquera par défaut les deux réglages proposés, à savoir le nombre maximal d’itérations à 100 et l’écart maximal à 0,001.

Cela signifie qu’Excel va réaliser au maximum 100 itérations mais qu’il peut aussi s’arrêter bien avant, en fait dès que – d’une itération à l’itération suivante – aucune cellule du classeur actif n’aura vu sa valeur modifiée de plus que 0,001 (l’écart maximal) en valeur absolue.

Comment mesurer la vitesse de convergence ?

Il n’est pas facile, avec notre petit exemple en six cellules, de savoir combien d’itérations Excel a réellement opérées avant de s’arrêter sur le prix de vente définitif.

Pour le découvrir, il nous suffit de régler à 1 le nombre maximal d’itérations, d’effacer à présent la formule de la cellule B4, de lancer un calcul avec [F9] pour que la cellule B3 retrouve sa valeur initiale de 0, puis de ré-entrer la formule de sommation en B4.

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

Utilisons la touche [F9] pour lancer le calcul à chaque itération : B4 prend alors successivement les valeurs 130 – 139 – 141,70 – 142,51 – 142,75 – 142,83 – 142,85 – 142,85 – 142,86.

Il a donc fallu moins de 10 itérations pour converger vers la solution définitive.