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

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.

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

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.

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

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:

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

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.

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

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.

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

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.

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

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.

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

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.

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

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 trobar referències circulars a Excel per evitar dades defectuoses

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 trobar referències circulars a Excel per evitar dades defectuoses

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.

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

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 .


MacOS: torna a mostrar la finestra fora de la pantalla

MacOS: torna a mostrar la finestra fora de la pantalla

Una llista de possibles solucions per a un problema en què potser heu perdut una finestra d'aplicació fora de la pantalla a macOS.

Com crear una llista de distribució a Outlook: els 3 millors mètodes

Com crear una llista de distribució a Outlook: els 3 millors mètodes

Vols enviar un correu electrònic a molts destinataris? No saps com crear una llista de distribució a Outlook? Aquí teniu com fer-ho sense esforç!

Com fer una llista desplegable a Excel: 2 millors mètodes el 2023

Com fer una llista desplegable a Excel: 2 millors mètodes el 2023

Si voleu que les tasques d'entrada de dades siguin impecables i ràpides, heu d'aprendre a fer una llista desplegable a Excel.

Solucionat: aquest fitxer no és compatible amb QuickTime Player

Solucionat: aquest fitxer no és compatible amb QuickTime Player

Si veieu que aquest fitxer no és compatible amb QuickTime Player, llegiu aquest article ara. Ofereix llum a algunes de les millors solucions.

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

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

Si les referències circulars al full de treball d'Excel són preocupants, aquí apreneu a trobar referències circulars a Excel i eliminar-les.

33 millors plantilles de pressupost dExcel per a ús personal i professional el 2023

33 millors plantilles de pressupost dExcel per a ús personal i professional el 2023

Busqueu plantilles de pressupost d'Excel gratuïtes o de pagament? Llegiu aquest article per explorar algunes de les millors plantilles de pressupost per a Excel en línia i fora de línia.

Com arreglar les tecles de fletxa que no funcionen a Excel: 6 mètodes de tir segur

Com arreglar les tecles de fletxa que no funcionen a Excel: 6 mètodes de tir segur

Esteu davant les tecles de fletxa que no funcionen al problema d'Excel? Llegiu aquest article ara i descobriu com podeu solucionar-ho en menys de 5 minuts!

Com reiniciar el controlador de gràfics: 9 mètodes principals que heu de conèixer

Com reiniciar el controlador de gràfics: 9 mètodes principals que heu de conèixer

Saber com reiniciar el controlador de gràfics a Windows 10, 11 i Mac per solucionar els problemes. Proveu aquests millors mètodes per restablir el controlador de gràfics.

Com imprimir etiquetes des dExcel mitjançant la combinació de correspondència de MS Word

Com imprimir etiquetes des dExcel mitjançant la combinació de correspondència de MS Word

Si us pregunteu com imprimir etiquetes des d'Excel, esteu al lloc correcte! Llegiu per saber com imprimir etiquetes a Excel mitjançant Combinació de correspondència.

Com solucionar lerror dels vídeos que no es reprodueixen al Mac: una guia pas a pas

Com solucionar lerror dels vídeos que no es reprodueixen al Mac: una guia pas a pas

Si alguns o tots els vídeos descarregats o editats no es reprodueixen al Mac, llegiu aquesta guia definitiva per obtenir les millors i fàcils idees de resolució de problemes.