Comment gérer les références circulaires dans Excel

Dans Excel, vous tapez généralement des formules pour obtenir des résultats et il est assez courant de référencer d’autres cellules dans le processus. Cependant, que se passe-t-il si vous faites référence à la cellule elle-même, que ce soit directement ou indirectement ? Cela signifie une boucle infinie de calculs ! Heureusement, Excel a des solutions pour gérer ce genre de situations.

La référence circulaire signifie que votre formule essaie de calculer la cellule d’origine. Généralement, cela est considéré comme une erreur. Cependant, il y a des moments où cette erreur peut en fait être utile et vous pourriez vouloir créer une référence circulaire à dessein. Examinons ce phénomène de plus près à l’aide de quelques exemples. Vous pouvez télécharger l’exemple de classeur ici.

Référence directe

Si vous vous rendez dans la cellule C3, par exemple, et que vous tapez ensuite « =C3 », vous embrouilleriez un peu Excel car cette est cellule pointe maintenant vers elle-même, créant ainsi une référence circulaire. Faire la même chose à l’intérieur d’une formule aura également le même effet (c’est-à-dire =IF(C3,…,…) ou =C3+10).

Un autre exemple courant consiste à inclure la cellule d’origine lors de la sélection d’une plage à partir de cellules adjacentes. Par exemple, inclure la cellule C4 dans la formule de somme =SUM(C2:C4) transformera celle-ci en une référence circulaire.

Référence indirecte

Une cellule qui cible une cellule qui pointe vers la cellule d’origine créera une référence indirecte. En d’autres termes, une référence circulaire peut être établie par deux cellules se ciblant mutuellement. Par exemple, saisir « =D3 » dans la cellule B3, alors que la cellule D3 contient une référence de la cellule B3 (« =B3 » dans ce cas).

Pourquoi est-ce un problème ?

Excel avertit l’utilisateur lorsqu’il y a une occurrence de référence circulaire, car une référence circulaire signifie essentiellement des boucles sans fin. Demander à Excel de répéter le même calcul encore et encore pendant un nombre infini de fois n’a pas trop de sens. Pour éviter de geler votre ordinateur, Excel ignore simplement les références circulaires.

Regardons ce message d’avertissement. Vous obtiendrez le message suivant après avoir tapé une formule à référence circulaire,

Notez qu’Excel ne donne pas la même erreur pour les références de formule circulaire supplémentaires. Sur la barre d’état d’Excel, la dernière cellule avec une référence circulaire sera affichée. Cela signifie qu’il pourrait y avoir plus d’une telle référence sur cet onglet. Voici à quoi cela ressemble,

Excel conserve également une liste de toutes les références circulaires, accessible depuis l’option Vérification d’erreur de l’onglet Formule,

Comment s’en débarrasser

Malheureusement, il n’existe pas de moyen simple de supprimer ou de corriger les références circulaires indésirables. Vous allez devoir utiliser l’un des moyens mentionnés précédemment et les résoudre un par un. Pour savoir ce qui est à l’origine du problème, vous pouvez utiliser l’option d’audit de formule sous l’onglet Formule. Consultez notre guide pour savoir comment faire, https://www.spreadsheetweb.com/identifying-analyzing-spreadsheets-formula-auditing/

Est-ce qu’ils peuvent être utiles ?

Oui ! Bien que, seulement dans certaines circonstances. Vous pouvez intentionnellement vouloir calculer un bloc de formule circulaire plus de quelques fois. Excel permet cette approche de calcul « itératif » en utilisant des limites.

Pour activer et utiliser les références circulaires, vous devez d’abord les activer en cochant l’option Activer les calculs itératifs dans le menu Fichier.

Voir Fichier > Options > Formules > Options de calcul dans Excel 2016, Excel 2013 et Excel 2010.

Dans Excel 2007, vous devez aller sur le bouton Office > Options Excel > Formules > Zone d’itération

Dans Excel 2003 et antérieur, vous devez aller dansMenu > Outils > Options > Calcul

L’activation des calculs itératifs fera apparaître deux entrées supplémentaires dans le même menu,

Itérations maximales détermine combien de fois Excel doit recalculer le classeur
Changement maximal détermine la différence maximale entre les valeurs des formules itératives. Notez qu’un nombre plus petit signifie des résultats précis.

Les calculs itératifs s’arrêtent lorsqu’une des conditions définies ci-dessus sont réunies.

Cas d’utilisation

Calcul de la valeur future d’un investissement

Supposons que nous avons 10 000 $ voulons investir cet argent dans un compte CD. L’intérêt mensuel de cette option est de 1,25 %. Téléchargez l’exemple de classeur pour ce cas d’utilisation ici. Pour calculer la valeur totale à la fin du 21ème mois, nous allons calculer chaque mois et ajouter l’intérêt au mois précédent.

D’abord, saisissez l’argent de départ, les intérêts et la fonction de valeur totale de base,

=valeur * ( 1 + taux d’intérêt)

Puis, sélectionnez la cellule avec la valeur initiale de l’argent et ajoutez la référence de la fonction de valeur totale.

Ce déplacement provoquera un avertissement de référence circulaire si les calculs itératifs ne sont pas activés. Si vous ne l’avez pas encore fait, activez cette option et définissez les itérations maximales à 20 pour trouver l’intérêt au 21e mois.

Appuyez sur OK.

Horloge automatique

Les références circulaires peuvent également être utilisées pour ajouter un horodatage dans les cellules. Téléchargez l’exemple de classeur pour ce cas d’utilisation ici. Supposons que nous voulions ajouter des horodatages aux commandes dans le tableau ci-dessous,

Nous pouvons utiliser des références circulaires pour ajouter un horodatage lorsqu’une nouvelle information de commande est saisie.

Commencez par ajouter une nouvelle colonne dans le tableau pour y placer les horodatages. Tapez la formule, =IF(A2<> » »,IF(I2<> »,I2,NOW()), » »)

Cette formule va vérifier si le numéro de commande (cellule A2) a été saisi. S’il y a une valeur saisie et que la cellule d’horodatage est vide, alors la formule renverra la fonction NOW().

Notez que si vous appuyez sur le bouton Enter, vous obtiendrez un avertissement de référence circulaire si les calculs itératifs ne sont pas activés. Cette fois, les nombres d’itérations maximales ou de changements maximaux ne signifient pas grand-chose car nous n’avons besoin que d’une seule itération, vous pouvez donc laisser ces deux entrées dans leurs valeurs par défaut.

Maintenant, chaque fois que nous entrons une nouvelle commande et créons une nouvelle ligne, un horodatage sera automatiquement imprimé sur la colonne Timestamp.