Un dels avisos d'error més habituals que els usuaris troben a Excel és la "Referència circular". Milers d'usuaris tenen el mateix problema, i es produeix quan una fórmula fa referència a la seva pròpia cel·la directament o indirectament, provocant un bucle interminable de càlculs.
Per exemple, teniu dos valors a les cel·les B1 i B2. Quan s'introdueix la fórmula =B1+B2 a B2, es crea una referència circular; la fórmula de B2 es recalcula repetidament perquè cada vegada que calcula, el valor de B2 ha canviat.
La majoria de referències circulars són errors no desitjats; Excel us avisarà sobre aquests. Tanmateix, també hi ha referències circulars destinades, que s'utilitzen per fer càlculs iteratius. Les referències circulars no desitjades al vostre full de treball poden fer que la fórmula es calculi incorrectament.
Per tant, en aquest article, explicarem tot el que necessiteu saber sobre les referències circulars i com trobar, arreglar, eliminar i utilitzar referències circulars a Excel.
Com trobar i gestionar la referència circular a Excel
Quan treballem amb Excel, de vegades ens trobem amb errors de referència circular que es produeixen quan introduïu una fórmula que inclou la cel·la on resideix la fórmula. Bàsicament, passa quan la teva fórmula intenta calcular-se per si mateixa.
Per exemple, teniu una columna de números a la cel·la A1:A4 i esteu utilitzant la funció SUMA (=SUMA(A1:A5)) a la cel·la A5. La cel·la A5 es refereix directament a la seva pròpia cèl·lula, que és incorrecta. Per tant, obtindreu el següent avís de referència circular:
Un cop hàgiu rebut el missatge d'advertència anterior, podeu fer clic al botó "Ajuda" per obtenir més informació sobre l'error o tancar la finestra del missatge d'error fent clic al botó "D'acord" o "X" i obtenir "0" com a resultat.
De vegades, els bucles de referència circulars poden provocar que el vostre càlcul es bloquegi o que alentiu el rendiment del vostre full de treball. La referència circular també pot donar lloc a una sèrie d'altres problemes, que no seran evidents immediatament. Per tant, el millor és evitar-los.
Referències circulars directes i indirectes
Les referències circulars es poden classificar en dos tipus: referències circulars directes i referències circulars indirectes.
Referència directa
Una referència circular directa és bastant senzilla. El missatge d'advertència de referència circular directa apareix quan la fórmula es refereix directament a la seva pròpia cel·la.
A l'exemple següent, la fórmula de la cel·la A2 es refereix directament a la seva pròpia cel·la (A2).
Un cop aparegui el missatge d'advertència, podeu fer clic a "D'acord", però només donarà lloc a "0".
Referència circular indirecta
Una referència circular indirecta a Excel es produeix quan un valor d'una fórmula fa referència a la seva pròpia cel·la, però no directament. En altres paraules, la referència circular pot estar formada per dues cel·les que es refereixen entre si.
Anem a explicar-ho amb aquest exemple senzill.
Ara el valor comença per A1 que té el valor 20.
A continuació, la cel·la C3 fa referència a la cel·la A1.
Aleshores, la cel·la A5 fa referència a la cel·la C3.
Ara substituïu el valor 20 a la cel·la A1 amb la fórmula que es mostra a continuació. Totes les altres cèl·lules depenen de la cèl·lula A1. Quan utilitzeu una referència de qualsevol altra cel·la de fórmula anterior a A1, provocarà un avís de referència circular. Perquè, la fórmula d'A1 fa referència a la cel·la A5, que fa referència a C3, i la cel·la C3 fa referència a A1, d'aquí la referència circular.
Quan feu clic a "D'acord", dóna com a resultat un valor de 0 a la cel·la A1 i Excel crea una línia enllaçada que mostra els precedents de traça i els dependents de traça tal com es mostra a continuació. Podem utilitzar aquesta funció per trobar i corregir/eliminar fàcilment referències circulars.
Com habilitar/desactivar les referències circulars a Excel
Per defecte, els càlculs iteratius estan desactivats (desactivats) a Excel. Els càlculs iteratius són càlculs repetitius fins que compleixen una condició específica. Quan està desactivat, Excel mostra un missatge de referència circular i retorna un 0 com a resultat.
Tanmateix, de vegades es necessiten referències circulars per calcular un bucle. Per utilitzar la referència circular, heu d'habilitar els càlculs iteratius al vostre Excel i us permetrà realitzar els vostres càlculs. Ara, us mostrem com podeu activar o desactivar els càlculs iteratius.
A Excel 2010, Excel 2013, Excel 2016, Excel 2019 i Microsoft 365, aneu a la pestanya "Fitxer" a la cantonada superior esquerra d'Excel i feu clic a "Opcions" al panell esquerre.
A la finestra Opcions d'Excel, aneu a la pestanya "Fórmula" i marqueu la casella de selecció "Activa el càlcul iteratiu" a la secció "Opcions de càlcul". A continuació, feu clic a "D'acord" per desar els canvis.
Això permetrà el càlcul iteratiu i, per tant, permetrà la referència circular.
Per aconseguir-ho en versions anteriors d'Excel, seguiu aquests passos:
- A Excel 2007, feu clic al botó Office > Opcions d'Excel > Fórmules > Àrea d'iteració.
- A Excel 2003 i versions anteriors, heu d'anar a Menú > Eines > Opcions > pestanya Càlcul.
Màxim d'iteracions i paràmetres de canvi màxim
Un cop hàgiu activat els càlculs iteratius, podeu controlar els càlculs iteratius, especificant dues opcions disponibles a la secció Habilita el càlcul iteratiu, tal com es mostra a la captura de pantalla següent.
- Màxim d'iteracions – Aquest número especifica quantes vegades s'ha de tornar a calcular la fórmula abans de donar-te el resultat final. El valor predeterminat és 100. Si el canvieu a "50", Excel repetirà els càlculs 50 vegades abans de donar-vos el resultat final. Recordeu que com més gran sigui el nombre d'iteracions, més recursos i més temps es necessita per calcular.
- Canvi màxim – Determina el canvi màxim entre els resultats del càlcul. Aquest valor determina la precisió del resultat. Com més petit sigui el nombre, més precís seria el resultat i més temps es trigarà a calcular el full de treball.
Si l'opció de càlculs iteratius està activada, no rebreu cap avís sempre que hi hagi una referència circular al vostre full de treball. Activeu el càlcul interactiu només quan sigui absolutament necessari.
Trobeu la referència circular a Excel
Suposem que teniu un conjunt de dades gran i teniu l'avís de referència circular, encara haureu d'esbrinar on (en quina cel·la) s'ha produït l'error per solucionar-lo. Per trobar referències circulars a Excel, seguiu aquests passos:
Utilitzant l'eina de comprovació d'errors
Primer, obriu el full de treball on s'ha produït la referència circular. Aneu a la pestanya "Fórmula", feu clic a la fletxa que hi ha al costat de l'eina "Comprovació d'errors". A continuació, només cal que passeu el cursor per sobre de l'opció "Referències circulars", Excel us mostrarà la llista de totes les cel·les implicades en la referència circular, tal com es mostra a continuació.
Feu clic a l'adreça de la cel·la que vulgueu a la llista i us portarà a aquesta adreça de cel·la per resoldre el problema.
Ús de la barra d'estat
També podeu trobar la referència circular a la barra d'estat. A la barra d'estat d'Excel, us mostrarà l'última adreça de cel·la amb una referència circular, com ara "Referències circulars: B6" (vegeu la captura de pantalla a continuació).
Hi ha certes coses que hauríeu de saber quan manipuleu la referència circular:
- La barra d'estat no mostrarà l'adreça de la cel·la de referència circular quan l'opció de càlcul iteratiu estigui habilitada, de manera que cal que la desactiveu abans de començar a buscar referències circulars al llibre de treball.
- En cas que no es trobi una referència circular al full actiu, la barra d'estat només mostra "Referències circulars" sense cap adreça de cel·la.
- Només rebràs una sol·licitud de referència circular una vegada i després de fer clic a "D'acord", no tornarà a mostrar la sol·licitud la propera vegada.
- Si el vostre quadern de treball té referències circulars, us mostrarà la sol·licitud cada vegada que l'obriu fins que resolgueu la referència circular o fins que activeu el càlcul iteratiu.
Eliminar una referència circular a Excel
Trobar referències circulars és fàcil, però arreglar-ho no és tan senzill. Malauradament, no hi ha cap opció a Excel que us permeti eliminar totes les referències circulars alhora.
Per arreglar les referències circulars, heu de trobar cada referència circular individualment i intentar modificar-la, eliminar la fórmula circular per complet o substituir-la per una altra.
De vegades, en fórmules senzilles, tot el que cal fer és reajustar els paràmetres de la fórmula perquè no es refereixi a ella mateixa. Per exemple, canvieu la fórmula de B6 a =SUMA(B1:B5)*A5 (canviant B6 a B5).
Tornarà el resultat del càlcul com a "756".
En els casos en què sigui difícil de trobar una referència circular d'Excel, podeu utilitzar les funcions Traça precedents i Traça dependents per rastrejar-la fins a la font i resoldre-la una per una. La fletxa mostra quines cel·les es veuen afectades per la cel·la activa.
Hi ha dos mètodes de traça que us poden ajudar a suprimir referències circulars mostrant les relacions entre fórmules i cel·les.
Per accedir als mètodes de traça, aneu a la pestanya "Fórmules" i feu clic a "Trace Precedents" o "Trace Dependents" al grup d'auditoria de fórmules.
Traça de precedents
Quan seleccioneu aquesta opció, fa un seguiment de les cel·les que afecten el valor de la cel·la activa. Dibuixa una línia blava que indica quines cel·les afecten la cel·la actual. La tecla de drecera per utilitzar els precedents de traça és Alt + T U T.
A l'exemple següent, la fletxa blava mostra que les cel·les que afecten el valor B6 són B1:B6 i A5. Com podeu veure a continuació, la cel·la B6 també forma part de la fórmula, cosa que la converteix en una referència circular i fa que la fórmula torni "0" com a resultat.
Això es pot solucionar fàcilment substituint B6 per B5 a l'argument de SUM: =SUM(B1:B5).
Traça dependents
La funció de traça dependents rastreja les cel·les que depenen de la cel·la seleccionada. Aquesta característica dibuixa una línia blava que indica quines cel·les es veuen afectades per la cel·la seleccionada. És a dir, mostra quines cel·les contenen fórmules que fan referència a la cel·la activa. La tecla de drecera per utilitzar els dependents és Alt + T U D.
A l'exemple següent, la cel·la D3 està afectada per B4. Depèn de B4 pel seu valor per produir resultats. Per tant, el dependent de traça dibuixa una línia blava de B4 a D3, que indica que D3 depèn de B4.
Ús deliberada de referències circulars a Excel
No es recomana utilitzar referències circulars deliberadament, però hi pot haver alguns casos rars en què necessiteu una referència circular perquè pugueu obtenir la sortida que voleu.
Expliquem-ho fent servir un exemple.
Per començar, activeu "Càlcul iteratiu" al vostre llibre de treball d'Excel. Un cop hàgiu habilitat el càlcul iteratiu, podeu començar a utilitzar referències circulars al vostre avantatge.
Suposem que esteu comprant una casa i voleu donar una comissió del 2% sobre el cost total de la casa al vostre agent. El cost total es calcularà a la cel·la B6 i el percentatge de comissió (comissió d'agent) es calcula a B4. La comissió es calcula a partir del cost total i el cost total inclou la comissió. Com que les cel·les B4 i B6 depenen entre si, es crea una referència circular.
Introduïu la fórmula per calcular el cost total a la cel·la B6:
=SUMA(B1:B4)
Com que el cost total inclou la tarifa de l'agent, hem inclòs B4 a la fórmula anterior.
Per calcular la Comissió d'Agent del 2%, inseriu aquesta fórmula a B4:
=B6*2%Ara, la fórmula de la cel·la B4 depèn del valor de B6 per calcular el 2% de la tarifa total i la fórmula de B6 depèn de B4 per calcular el cost total (inclosa la tarifa d'agent), d'aquí la referència circular.
Si el càlcul iteratiu està habilitat, Excel no us donarà cap avís ni un 0 al resultat. En canvi, el resultat de les cel·les B6 i B4 es calcularà tal com es mostra a dalt.
L'opció de càlculs iteratius normalment està desactivada per defecte. Si no l'heu activat i quan introduïu la fórmula a B4 que crearà una referència circular. Excel emetrà l'avís i quan feu clic a "D'acord", es mostrarà la fletxa traçadora.
Això és. Això era tot el que necessiteu saber sobre les referències circulars a Excel.