COMMENT REPERER ET CORRIGER (OU PAS !) LES REFERENCES CIRCULAIRES DANS EXCEL ?

Guide « 108 astuces pour grands débutants sur Excel ! » Offert
Booster votre productivité en téléchargeant gratuitement votre ebook PDF des meilleures astuces pour les débutants et les pros d'Excel
Excel formation - 00 Astuces - 100
 

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 :

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.
 

 

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 :

Excel formation - identifier et corriger les références circulaires - 01

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 formation - identifier et corriger les références circulaires - 02

Excel ne pourra pas valider la saisie de la formule, et va directement nous afficher un message d’erreur :

Excel formation - identifier et corriger les références circulaires - 03

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.

Excel formation - identifier et corriger les références circulaires - 04

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) :

Excel formation - identifier et corriger les références circulaires - 05

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.

Excel formation - identifier et corriger les références circulaires - 06

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 :

Excel formation - identifier et corriger les références circulaires - 07

 

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 » :

Excel formation - identifier et corriger les références circulaires - 08

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é :

 

Excel formation - identifier et corriger les références circulaires - 09

Ensuite, nous ressaisissons la formule d’origine :

Excel formation - identifier et corriger les références circulaires - 10

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) :

Excel formation - identifier et corriger les références circulaires - 11

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 !

Excel formation - identifier et corriger les références circulaires - 12

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 :

Excel formation - identifier et corriger les références circulaires - 13

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 !

 

 



Articles qui pourraient vous intéresser

Comment connecter Excel sur Internet, sans VBA ?
Comment traduire automatiquement des fonctions Excel dans leur version originale ?
Comment analyser les résultats d’un sondage ou questionnaire avec Excel ?
Comment utiliser la fonction SOMME.SI pour effectuer des recherches sur des textes sur Excel ?
Comment calculer et étudier des écarts budgétaires avec Excel ?
Comment verrouiller et protéger un objet (graphique, image, zone de texte…) sur Excel ?
Comment formater des dates correctement dans Excel ?
Comment protéger le formatage des cellules tout en autorisant la saisie de données dans Excel ?
Comment transformer une photo en tableau Excel ?
Comment créer un publipostage automatique avec Excel ? (sans Word !)
Comment créer un graphique Gaufre sur Excel ?
Comment créer un graphique en cascade dans Excel ?

Contact - Plan du site - À propos - Contact

© Excelformation.fr 2018-2024 - Excel, simplement

Haut de la page

Excelformation.fr propose de nombreuses formations pratiques et gratuites sur Microsoft Excel : des cours pour apprendre à structurer et analyser les données, des tutos sur les fonctions avancées, les formules et les formules avancées d'Excel, les formules matricielles, les mise-en-formes conditionnelles, les graphiques Excel (xls, xlsx, xlsm,…). Des tutoriels pour apprendre à automatiser les tâches répétitives (via des macro-commandes en VBA) de vos classeurs Excel.