COMMENT REPERER ET CORRIGER (OU PAS !) LES REFERENCES CIRCULAIRES DANS EXCEL ?
Dans ce tutoriel, je vais vous faire découvrir la notion de référence circulaire dans Excel. Nous verrons notamment comment les identifier et comment les corriger. Et pour finir, restez bien jusqu’à la fin de la vidéo, car je vais vous montrer que parfois nous pouvons créer volontairement des références circulaires et demander à Excel de présenter le résultat correspondant.
Téléchargement
Vous pouvez télécharger le fichier d'exemple de cet article en cliquant sur le lien suivant :
Tutoriel Vidéo
1. Une référence circulaire c’est quoi ?
La base de la construction des formules dans Excel consiste à utiliser des références, c’est-à-dire appeler d’autres cellules pour récupérer leur valeur afin d’effectuer un traitement, et de retourner un résultat.
C’est ce qui permet de faire en sorte qu’Excel soit bien puissant qu’une simple calculatrice.
En revanche pour que les références puissent être correctement utilisées par Excel, il ne faut pas que le résultat calculé dépende directement ou non de son propre résultat !
Dans ce cas-là, Excel va générer une erreur dite de référence circulaire !
Pour bien comprendre de quoi il s’agit, nous disposons de l’exemple suivant :
Ce petit tableau permet de déterminer la marge brute dégagée par un produit vendu.
Comme nous pouvons le voir, cette marge brute est égale au prix de vente HT duquel nous retranchons tout d’abord le prix d’achat HT de ce produit, mais également la commission reversée au vendeur.
La difficulté ici, c’est que le montant de cette commission dépend directement du montant de la marge brute dégagée (qui pour rappel dépend de la commission versée !!).
Aussi, lorsque nous insérons la formule qui permet de calculer cette commission comme ci-dessous :
Excel ne pourra pas valider la saisie de la formule, et va directement nous afficher un message d’erreur :
Ce message nous informe de la présence d’une référence circulaire, qui empêche de valider la formule.
Bien souvent les références circulaires sont involontaires, ou dues à une méconnaissance du fonctionnement d’Excel.
En effet, l’exemple que nous venons de voir ici est très simpliste, et une fois expliqué nous pouvons simplement comprendre pourquoi Excel bloque à cet endroit (quoique, nous verrons un peu plus tard que nous pouvons quand même demander à Excel d’effectuer l’opération ! Suspense ;))
Mais nous pouvons également imaginer des modèles beaucoup plus complexes, dans lesquels la référence circulaire est indirecte ! Dans ce cas il sera alors nécessaire de remonter le fil des formules afin d’identifier la source de la référence circulaire.
Enfin, sachez qu’en plus de présenter des résultats incorrects, les références circulaire vont demander d’utiliser d’importantes ressources ce qui peut entraîner des ralentissements dans l’utilisation du classeur.
Il est donc important de corriger les références circulaires éventuelles, dès lors que celles-ci ne sont volontaires.
2. Identifier et corriger une référence circulaire
Heureusement, il est très simple d’identifier la présence d’une référence circulaire.
En effet dès qu’Excel identifie une formule qui pose problème sur une feuille de calcul, celui-ci retourne directement les coordonnées de la cellule correspondante dans la barre d’état (en bas à gauche) :
Nous pouvons donc suivre cette référence et la corriger au besoin !
Une seconde solution pour identifier rapidement les références circulaires consiste à se rendre dans le menu Formules > dans le groupe Vérification des formules > Afficher les formules > Références circulaires afin de retrouver une liste des références circulaires.
Cliquer sur l’une des références identifiées permet d’accéder directement à la cellule correspondante.
Pour corriger la référence circulaire, nous pouvons par exemple utiliser une autre formule qui permet d’atteindre le même résultat :
3. Utiliser volontairement des références circulaires
Maintenant, imaginons que nous ne souhaitions pas modifier la formule, c’est-à-dire conserver la référence circulaire, et forcer Excel à réaliser l’opération.
Impossible ?
Et non, un simple réglage permet de réaliser cette tâche.
Il s’agit ici d’activer les calculs itératifs dans Excel.
Un calcul itératif consiste à répéter un même calcul, jusqu’à ce qu’une condition soit remplie.
En d’autres termes, Excel va réaliser un premier calcul et va regarder si le résultat présenté répond aux conditions saisies dans les différentes formules !
Pour activer les calculs itératifs, rendons-nous dans le menu Fichier > Options, puis nous choisissons la catégorie Formules afin d’activer l’option « Activer le calcul itératif » :
En dessous, nous retrouvons deux champs :
- Nb maximal d’itérations : il s’agit du nombre de recalcul maximums que nous souhaitons qu’Excel exécute pour obtenir le bon résultat. Il est important de limiter le nombre de recalculs pour ne pas qu’Excel soit bloqué dans des calculs trop nombreux, voir indéfinis,
- Ecart maximal : permet de définir la marge d’erreur maximale à partir de laquelle Excel va considérer que le calcul est suffisamment précis et va interrompre les recalculs
p>Pour l’exemple et pour bien comprendre les différentes opérations réalisées, nous allons définir le nombre d’itérations à une seul, et l’écart maximal à l’unité :
Ensuite, nous ressaisissons la formule d’origine :
A ce moment-là, comme vous pouvez le constater le résultat présenté par Excel n’est pas correct.
Il nous suffit alors de relancer les calculs de la feuille pour passer à l’itération suivante (en appuyant sur la touche [F9] ou le bouton Calculer maintenant du menu Formules) :
Le calcul change alors mais n’est toujours pas correct !
Nous pouvons alors appuyer plusieurs fois sur le bouton jusqu’à obtenir le bon résultat !
Pour éviter de devoir relancer plusieurs fois les calculs, il suffit d’augmenter le nombre d’itérations dans les options de calculs de cette manière Excel enchaînera à chaque fois le nombre de calculs que nous allons lui demander :
Ici, nous remettons ce nombre à sa valeur d’origine, c’est à dire 100 recalculs.
Alors évidemment, attention, si les calculs itératifs ne sont pas activés par défaut, c’est parce qu’ils demandent beaucoup de calculs et consomme donc de nombreuses ressources.
A utiliser avec précaution, uniquement lorsqu’aucune autre solution n’est possible !