Com trobar referències circulars a Excel per evitar dades defectuoses

Heu vist l'error de referència circular en un full d'Excel i us heu preguntat si el full de treball està trencat? Confia en mi! No sou el primer que s'enfronta a aquest error d'Excel. Centenars i milers d'usuaris d'Excel experimenten errors de referència circular cada dia. No et preocupis! A continuació, es mostra com trobar referències circulars a Excel i resoldre-les.

En ser un àvid usuari d'Excel, poques vegades escriviu les mateixes dades a moltes cel·les. En lloc d'això, importeu un conjunt de dades de la font i utilitzeu la referència de cel·les per crear fórmules. Així, creeu un full de càlcul dinàmic.

Quan algú canvia els valors de les cel·les de les referències de cel·les, el full de treball s'actualitza automàticament. Això s'anomena automatització de l'anàlisi de dades i als analistes de dades els encanta jugar amb les referències cel·lulars.

Tanmateix, el problema arriba quan escriviu de manera intencionada o accidental l'adreça de la cel·la on esteu fent un càlcul. O bé, quan creeu moltes fórmules al full de treball, una cel·la que conté una fórmula apareix indirectament en una altra fórmula.

En aquestes i moltes altres situacions similars, obteniu l'error de referència circular a Excel. Continueu llegint per saber què és la referència circular, els seus motius, per què evitar-la i, el més important, com trobar referències circulars a Excel per desfer-se definitivament d'aquestes anomalies.

Què és una referència circular a Excel?

Excel troba un error de referència circular quan veu que una o moltes funcions del full de treball fan referència a l'adreça de la cel·la on es van crear les seves funcions.

Pot semblar confús per a vostè, així que penseu en el greu que podria ser per a l'aplicació Excel. En resum, mai no voleu posar l'adreça de la cel·la en una fórmula on la creeu. Mireu l'exemple següent per obtenir un aclariment visual:

Esteu intentant multiplicar 10 de la cel·la B1 i posar el resultat a la mateixa cel·la B1 .

Ara, en l'escenari anterior, si només premeu D'acord , Excel tancarà l'error i mostrarà zero com a resultat calculat. Aquest és només un exemple senzill.

Què és la referència circular a Excel

Ara, imagineu-vos que hi ha centenars de fórmules en un full de treball de comptabilitat, un full de planificació financera, una nòmina d'empleats, un full de càlcul de pagament al proveïdor, etc.

Confiaràs en les dades defectuoses calculades per Excel a causa de l'error de referència circular i això, al seu torn, afectarà la teva presa de decisions empresarial o professional.

A més, la referència circular pot reduir molt la velocitat dels càlculs a l'aplicació Excel. En casos greus, el mateix error també pot provocar un rendiment extremadament lent a l'ordinador si no es tracta immediatament.

Tanmateix, si esteu treballant en fórmules avançades d'Excel on el vostre model de dades requereix entrades intermediàries de la referència circular per continuar amb una funció llarga, és possible que hàgiu d'utilitzar-la. De fet, Excel us permet utilitzar aquest mètode de càlcul quan sigui necessari.

Per exemple, a les unitats de producció d'energia, com els molins de vent, cal calcular l'energia de sortida reciclada a partir d'una fracció de l'energia d'entrada. Alternativament, en una planta química, els enginyers utilitzen la tija de sortida com a entrada per a la mateixa caldera o recipient de procés o una altra. Aquests càlculs d'enginyeria d'alt nivell requereixen de fet referències circulars per a càlculs iteratius.

Quan veieu l'error de referència circular a Excel?

Parlant clarament, la finestra emergent de referència circular no és un missatge d'error. És una manera perquè Excel us informi que ha trobat una referència circular en una o moltes fórmules i que pot influir en el seu algorisme de càlcul i el seu rendiment.

Per evitar els danys causats per les referències circulars a les fórmules d'Excel, cal saber quan apareixen i quan no. Trobeu a continuació els casos:

• En qualsevol full de treball o llibre de treball obert, creeu una fórmula i introduïu la referència circular per primera vegada.
• Vostè o algú ha eliminat totes les referències circulars del fitxer Excel i l'han desat. Ara, de nou, vostè o un altre usuari introduïu una referència circular al mateix fitxer.
• Has tancat tots els llibres de treball oberts. Ara, obriu un quadern de treball diferent i, a continuació, escriviu una fórmula que contingui una referència circular.

Quan veieu l'error de referència circular a Excel

• Acabeu d'obrir un llibre de treball que anteriorment contenia una o moltes referències circulars. Veureu l'avís.
• Un cop Excel us hagi mostrat el missatge d'advertència de referència circular una vegada i hàgiu fet clic a D'acord , ja no es mostrarà la mateixa finestra emergent a la mateixa sessió, encara que continueu creant més fórmules de referència circular.

Per tant, heu de buscar acuradament les instàncies anteriors per detectar advertències de referència circular i resoldre'ls si no busqueu càlculs iteratius.

Tipus de referències circulars en Excel

Si voleu aprendre a trobar referències circulars a Excel, heu d'entendre els seus tipus. Perquè trobar i eliminar referències circulars a Excel no és una tasca automatitzada. Trobeu a continuació els tipus habituals d'aquest error:

Un exemple d'ús de referència circular

• Les referències circulars previstes es produeixen quan les feu servir intencionadament amb finalitats de càlcul iteratiu. Per exemple, heu d'introduir un segell de temps en una altra cel·la quan algú introdueix un valor en una cel·la a la qual es fa referència mitjançant funcions volàtils i lògiques com SI, OR, SUMIF, etc.

Referència circular no desitjada a Excel

• Referències circulars no desitjades quan intenteu treballar en una fórmula d'Excel més ràpidament i feu referència a la cel·la que conté la fórmula per error. Per exemple, cal aplicar la funció SUMA de la cel·la A1 a A9 i obtenir el resultat a la cel·la A10 . Però per error, escriviu la fórmula com aquesta a la cel·la A10:

=SUMA(A2:A10)

• Les referències circulars ocultes són aquelles que no mostren cap missatge d'advertència quan creeu una fórmula o obriu el full de treball per primera vegada. Passa perquè algú va permetre el càlcul iteratiu al fitxer Excel.

Un exemple de referència circular indirecta

• Les referències circulars indirectes també són populars i es produeixen quan creeu una fórmula nova a qualsevol cel·la, per exemple, la cel·la A1 , i aquesta fórmula nova utilitzaria una altra funció en una altra cel·la, com la cel·la B1 , que ja utilitza la cel·la A1 com a referència. .

Ara que heu entès els conceptes bàsics de les referències circulars a Excel, a continuació esbrinem com trobar referències circulars a Excel:

Com trobar referències circulars a Excel: des de la barra d'estat

Sempre que la funció de càlcul iteratiu estigui desactivada, podeu veure les notificacions següents sobre referències circulars a la barra d'estat d'Excel:

• Referències circulars: C3  (o qualsevol altra referència de cel·la) quan esteu en un full de treball que conté una fórmula circular al llibre de treball.

Mostrant referències circulars a la barra d'estat d'Excel

• Només veureu Referències circulars si esteu en un full de treball que no en conté cap, però el llibre de treball conté algunes d'aquestes fórmules circulars.

Com trobar referències circulars a Excel: utilitzant la comprovació d'errors

És la millor manera de comprovar si hi ha referències circulars en un llibre o full de treball. Aquests són els passos que has de saber:

• Obriu un full de treball que sospiteu que conté fórmules circulars.
• Feu clic al menú Fórmules a la cinta d'Excel.

Utilitzeu la comprovació d'errors per a Com trobar referències circulars a Excel

• Al grup d'ordres Auditoria de fórmules , feu clic a la llista desplegable Comprovació d'errors  .
• Passeu el cursor per sobre de l' opció Referències circulars per veure totes les instàncies disponibles a tots els fulls de treball.
• Seleccioneu cada instància i Excel identificarà aquestes cel·les automàticament.

Com trobar referències circulars a Excel: des del traçat de fórmules

Si no trobeu que el mètode de comprovació d'errors no és prou eficient per detectar un gran nombre de fórmules amb referències circulars, podeu provar aquests passos per utilitzar el mètode de traça de fórmules:

• Seleccioneu qualsevol cel·la de referència o la cel·la que conté la fórmula.
• Aneu a la pestanya Fórmules del menú de la cinta d'Excel i feu clic a Traça precedents .
• Excel ressaltarà les cel·les que afecten el resultat de la fórmula a la cel·la actual amb fletxes.

Utilitzeu el seguiment de fórmules per a Com trobar referències circulars a Excel

• També podeu fer clic a Traça dependents per descobrir les cel·les afectades pel valor de la referència de cel·la seleccionada.
• Si Excel no mostra cap error després de fer clic a les ordres anteriors, no hi ha referències circulars al vostre full de treball.
• Repetiu els passos de tots els fulls de treball per comprovar tot el llibre de treball.

Aquest mètode és especialment important quan les funcions de càlcul iteratiu estan actives al full de treball d'Excel.

Com trobar referències circulars a Excel: utilitzant les opcions d'Excel

• Feu clic a la pestanya Fitxer a la cinta d'Excel i, a continuació, obriu Opcions d'Excel des de la barra lateral que apareix.
• Al quadre de diàleg Opcions d'Excel , seleccioneu el menú Fórmules de la barra lateral i, a continuació, desmarqueu la casella de selecció Habilita el càlcul iteratiu .

Com desactivar el càlcul iteratiu a Excel

• Feu clic a D'acord per desar els canvis.
• Ara, el llibre o full de treball us mostrarà automàticament l'advertència de referència circular.
• Si no es mostra a l'instant, deseu el llibre de treball, tanqueu-lo i torneu-lo a obrir per veure l'avís.
• Un cop vegeu l'avís, comproveu la barra d'estat d'Excel per saber quina cel·la es troba a la referència circular.

Com resoldre referències circulars a Excel

La manera segura de corregir un o molts errors de referència circular a Excel és editant manualment cada fórmula que la conté. D'aquesta manera, podeu resoldre tant les referències circulars accidentals com indirectes a Excel.

Com resoldre referències circulars a Excel

Una altra manera senzilla és eliminar la fórmula de la cel·la actual i col·locar-la en un altre lloc dins del mateix full de treball.

Com trobar referències circulars a Google Sheets (mètode de bonificació)

Igual que Excel, Google Sheets no permet cap referència circular en una o més fórmules si no heu activat la funció de càlcul iteratiu. Per tant, Google Sheets mostrarà immediatament el #REF! error a la cel·la que conté una fórmula circular.

Fulls de Google Com trobar referències circulars

Per tant, només podeu seleccionar totes les cel·les que contenen el #REF! advertència per trobar i resoldre referències circulars a Fulls de càlcul de Google.

Referències circulars d'Excel: Paraules finals

Per tant, ara coneixeu tots els secrets de com trobar referències circulars a Excel. Utilitzeu el mètode que us agradi i elimineu totes les referències circulars per assegurar-vos que no us confongueu amb dades poc fiables d'un informe d'Excel o d'un tauler matemàtic.

A continuació, les  millors plantilles de calendari d'Excel per a qualsevol any  i les  millors plantilles de pressupost d'Excel .