Monsieur Excel
Pour tout savoir faire sur Excel !

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.