Les referències circulars poden ser bastant complicades, per això és important saber com trobar-les. Excel té un mecanisme integrat que pot detectar referències circulars i impedir que els càlculs es desenvolupin en un bucle sense fi. Només heu de saber com habilitar i utilitzar aquesta funció al vostre avantatge.
En aquest article, us mostrarem com trobar referències circulars a Excel. A més, explicarem què són exactament les referències circulars en primer lloc i per què les hauríeu d'evitar.
Com trobar una referència circular a Microsoft Excel
Quan intenteu introduir una fórmula al vostre quadern de treball d'Excel i trobeu algun tipus de problema, és possible que estigueu tractant amb una referència circular. Això passa quan la fórmula intenta utilitzar el seu propi valor per fer càlculs. En aquest punt, Excel us enviarà un missatge d'advertència:
"Hi ha una o més referències circulars on una fórmula es refereix a la seva pròpia cel·la directament o indirectament. Això podria fer que calculin incorrectament".
Com que un bucle interminable pot continuar per sempre o sortir abans d'arribar a la resposta correcta, el millor és mantenir-se allunyat de les referències circulars a Excel. No només això, sinó que les referències circulars també poden alentir en gran mesura tot el procés de càlcul dels vostres llibres de treball. Tanmateix, en la majoria dels casos, el problema més gran amb les referències circulars és identificar-les.
Hi ha tres tipus de referències circulars: no intencionades, intencionades i ocultes. La majoria de les referències circulars no són intencionades, ja que caldria que algú sàpiga utilitzar Excel per crear una referència circular deliberada. Finalment, tenim referències circulars amagades. Tot i que les referències circulars accidentals són fàcils de trobar, Excel no sempre pot detectar referències circulars amagades, de manera que haureu d'utilitzar altres mètodes.
Quan aparegui el missatge d'advertència, podeu fer clic al botó "D'acord" o "Ajuda". Aquest últim només us donarà més informació sobre les referències circulars sense assenyalar on es troben al vostre quadern de treball. D'altra banda, si trieu "D'acord" o si només desactiveu el missatge, trobareu l'últim valor calculat o zero a la vostra darrera cel·la. També és important tenir en compte que aquesta notificació no apareixerà sempre. Per exemple, si continueu creant més referències circulars, intencionadament o no, Excel deixarà d'avisar-vos sobre aquest tema.
Molt poques vegades, una fórmula que conté una referència circular es pot completar abans que el mecanisme d'autocàlcul estigui en moviment. En aquest cas, només es mostrarà l'últim valor correcte com a resultat. En altres paraules, una referència circular pot fer que el sistema no respongui. Per això identificar-lo és el pas més important.
Per solucionar un error de referència circular a Excel, primer heu de trobar-lo. Seguiu els passos següents per saber com es fa:
- Desactiveu el missatge d'advertència que mostra Excel.
- Aneu a la pestanya "Fórmules" al menú superior.
- Aneu a la pestanya "Comprovació d'errors" i feu-hi clic.
- Feu clic a "Referències circulars" al menú desplegable. Aquí és on es revelaran les referències circulars.
- Feu clic al valor de la llista emergent i se us dirigirà directament a aquesta referència circular.
Quan feu clic a la cel·la que conté la referència circular, també es mostrarà a la vostra barra d'adreces a la part inferior del full.
Si necessiteu més ajuda amb la referència circular, hi ha dues eines que us poden ajudar: rastrejar precedents i rastrejar dependents. La primera eina, traça precedents, mostra línies blaves al llibre d'Excel que us mostren quines cel·les afecten la cel·la a la qual heu fet clic. Els dependents de traça, en canvi, fan el contrari. Tracen línies per mostrar-vos quines cel·les es veuen afectades per la cel·la a la qual heu fet clic. Aquestes dues funcions us ajuden a trobar referències circulars que Excel no pot detectar. Tingueu en compte que aquests traçadors no us mostren exactament on és la referència circular, només una pista per ajudar-vos a trobar-la més ràpidament.
Si voleu habilitar el rastreig de precedents i de rastreig de dependents, seguiu els passos següents:
- Aneu a la pestanya "Fórmules" a la part superior del vostre full de càlcul.
- Aneu a la categoria "Auditoria de fórmules".
- Seleccioneu "Trace Precedents" o "Trace Dependents".
Només podeu seleccionar-ne un a la vegada. Una manera més ràpida de fer-ho és utilitzar aquestes dreceres: "Alt + T U T" per a precedents de traça, o "Alt + T U D" per a dependents de traça.
Alguns usuaris d'Excel creen referències circulars a propòsit per fer càlculs iteratius. Però, en general, no és una bona idea incorporar referències circulars als vostres fulls.
PMF addicionals
Com eliminar una referència circular
Quan finalment trobeu la referència circular que està causant tots els problemes al vostre llibre de treball d'Excel, voldreu eliminar-la. Tot i que no hi ha manera de solucionar-ho automàticament, podeu esbrinar quina part de la fórmula s'ha d'eliminar o substituir. Sabreu que heu resolt el problema quan feu clic a la cel·la i no hi ha cap etiqueta de "Referència circular" a la barra d'adreces.
Les referències circulars només es poden crear al vostre full d'Excel si la funció de càlcul iteratiu està activada. Aquesta funció està desactivada de manera predeterminada, de manera que normalment no hi ha res amb què es pot arraconar. Tanmateix, si voleu comprovar si la funció de càlcul iteratiu s'ha habilitat d'alguna manera, això és el que heu de fer:
1. Aneu a la pestanya "Fitxer" a l'extrem superior esquerre de la pantalla.
2. Continueu a la secció "Opcions" a la part inferior del menú.
3. Trieu "Fórmules" a la barra lateral esquerra de la finestra emergent.
4. A "Opcions de càlcul", aneu al quadre "Activa el càlcul iteratiu" per veure si està marcat.
5. Desmarqueu-lo per desactivar les iteracions.
6. Feu clic al botó "D'acord".
Aquest mètode es pot aplicar a Excel 2010, Excel 2013, Excel 2016 i Excel 2019. Si teniu Excel 2007, trobareu la funció de càlcul iteratiu quan feu clic al botó Office i aneu a "Opcions d'Excel". La secció "Àrea d'iteració" estarà a la pestanya "Fórmules". Si teniu Excel 2003 o anterior, heu d'anar a "Menú" i després a la pestanya "Eines". La "pestanya de càlcul" estarà a la secció "Opcions".
Localitzeu totes les referències circulars al vostre quadern d'Excel
En la majoria dels casos, les referències circulars es creen per casualitat, però poden ser bastant problemàtiques. No només emboliquen tota la fórmula, sinó que també poden alentir tot el procés de càlcul. Per això és important trobar-los i substituir-los el més aviat possible. Afortunadament, Excel us informarà en el moment que se'n detecti alguna. També podeu utilitzar traces precedents i traces dependents per ajudar-vos a descobrir la relació entre la referència circular i les altres cel·les.
Heu provat mai de trobar una referència circular al vostre full d'Excel abans? Com ho vas trobar? Feu-nos-ho saber a la secció de comentaris a continuació.