Com trobar i eliminar referències circulars a Excel

Com trobar i eliminar referències circulars a Excel

Les referències circulars poden ser tedioses rastrejar-les i eliminar-les pel vostre compte. Apreneu a utilitzar les eines d'Excel per eliminar-les.





Què són les referències circulars a Excel?

Una referència circular és un terme utilitzat per a una fórmula que visita una cel·la més d’una vegada a la seva línia de càlculs. A causa d'això, els càlculs trigaran molt a restringir els números i és molt probable que els càlculs donin una resposta equivocada la major part del temps.



Tot i això, les referències circulars no sempre són perjudicials. En alguns casos, són força útils. Malgrat això, les referències circulars representen un risc de descontrol i contribueixen a problemes que no són immediatament evidents.





Si teniu una referència circular al full de càlcul però mai no teniu intenció de fer-ne cap, hauríeu d’eliminar-la.

Comprensió de referències circulars

Per eliminar la fórmula, primer l’heu d’entendre. Hi ha dues maneres en què una referència pot ser circular: directa i indirectament.



Referències circulars directes

Una referència circular directa passa quan una cèl·lula es refereix directament a ella mateixa. Fem una referència circular directa:

  1. A les cel·les A1 , A2, i A3 , introduïu els números 100, 200 i 300.
  2. Selecciona una cel·la A4 i, a la barra de fórmules, introduïu la fórmula següent: =SUM(A1+A2+A3+A4) Aquesta és una funció SUM simple que sumarà les cel·les A1, A2, A3 i A4.
  3. Un cop hàgiu escrit la fórmula, premeu Entra . Apareixerà un missatge d'error.
  4. Al missatge d'error que apareix, Excel us avisa que hi ha una referència circular en aquest full de càlcul. Feu clic a D'acord .

Aquesta fórmula crea una referència circular perquè A4 vol sumar un conjunt de valors amb el valor de A4. Tanmateix, quin valor té l'A4? Mai no ho sabreu ni tampoc Excel.



Com a resultat d'aquesta referència circular, Excel retornarà 0 per a la fórmula. Potser no sabeu la resposta a aquesta fórmula, però no és 0.

Relacionat: Funcions de cerca d'Excel per cercar fulls de càlcul de manera eficient



Referències circulars indirectes

Una referència circular indirecta és quan una cèl·lula es refereix a si mateixa a través d’altres cèl·lules.

  1. A la cel·la A1 , introduïu la fórmula següent: =D1 Això establirà el valor de A1 en el de D1. Sigui el que sigui.
  2. Introduïu els números 100 i 200 en cel·les B1 i C1 , respectivament.
  3. Selecciona una cel·la D1 i, a la barra de fórmules, introduïu la fórmula següent: =SUM(A1+B1 + C1) Aquesta és una altra funció SUM que sumarà els valors de les cel·les A1, B1 i C1 i retornarà el resultat a D1.
  4. Premeu Entra . Apareixerà un missatge d'error.
  5. Al missatge d'error, feu clic a D'acord .

Com abans, Excel retorna 0. Tanmateix, a diferència de l'exemple anterior, la cel·la D4 no fa cap referència directa a ella mateixa. En lloc d'això, suma A1 amb un conjunt de valors. Què és A1? És D4. Llavors, què és D4? És A1 amb un conjunt de valors.

S’ha format una referència circular.

com gravar una trucada al vostre iPhone

Relacionat: Com copiar fórmules a Microsoft Excel

Cerqueu i elimineu referències circulars a Excel

En escenaris més complexos, on nombroses cel·les es refereixen entre si, no és tan senzill trobar referències circulars com mirar la fórmula sola. Excel té eines per ajudar-vos a trobar referències circulars.

Abans d’arribar a trobar referències circulars, afegim-ne algunes al full de càlcul:

  1. A les cel·les A1 , A2 , i A3 , introduïu 1, 2 i 3.
  2. Selecciona una cel·la A4 .
  3. A la barra de fórmules, introduïu la fórmula següent i premeu Entra : =SUM(A1+A2+A3+A4) Com en el primer exemple, aquesta també és una referència circular directa on A4 es refereix a si mateix.
  4. Al missatge d'error, feu clic a D'acord .

Excel retorna 0 perquè la fórmula en A4 és una referència circular directa. En creem també una de indirecta:

  1. A la cel·la C1 , escriviu 20 .
  2. Introduïu la fórmula següent per a la cel·la E4 : =G1+3
  3. Introduïu la fórmula següent per a la cel·la G1 : =C1+2
  4. Per últim, seleccioneu la cel·la C1 a la barra de fórmules i substituïu 20 amb la línia següent: =E4+1

Com era d’esperar, Excel retorna 0 perquè aquestes cel·les es refereixen entre elles en cercle. No obstant això, com que no es refereixen directament a si mateixos, són referències circulars indirectes.

Relacionat: Com es compten els valors únics a Excel

Explorem com podeu utilitzar les eines d'Excel per trobar referències circulars al full de càlcul. Tingueu en compte que el full de càlcul conté quatre referències circulars, concretament A4 (directa), C1, E4 i G1 (indirecta).

  1. Al menú de la cinta, aneu a Fórmula fitxa.
  2. A la Auditoria de fórmules , feu clic al botó de fletxa que hi ha al costat Error de comprovació .
  3. Al menú, passeu el cursor per sobre Referències circulars . S’obrirà una llista de referències circulars.
  4. Feu clic a la cel·la a la llista. Això us portarà a la cel·la amb referència circular.
  5. Notareu que Excel només mostra una de les quatre referències. Això es deu al fet que Excel gestiona aquestes cadenes d'una en una.
  6. Quan hàgiu corregit la primera referència circular, podeu passar a la següent.
  7. Al següent pas, fixeu la referència circular .
  8. Tornar a Error de comprovació i feu clic a Referències circulars .
  9. A la llista, seleccioneu la següent referència circular i procediu a solucionar-la.

Seguiment de les relacions cel·lulars

Fer un seguiment de les relacions cel·lulars pot esdevenir un desafiament en els fulls de càlcul on massa cèl·lules es refereixen entre si. Allà és on està Excel Auditoria de fórmules la funció pot ser útil. Us pot ajudar a obtenir una millor visualització de les vostres fórmules.

  1. Seleccioneu una cel·la que contingui una fórmula.
  2. Des de la cinta, aneu a Fórmula fitxa.
  3. A la Auditoria de fórmules secció, feu clic a Traça dels precedents .
  4. Totes les cel·les que afecten els valors de la cel·la seleccionada es connectaran entre elles amb fletxes blaves.
  5. A continuació, feu clic a la mateixa secció Traceu dependents . (Aquest mètode dibuixarà fletxes blaves de la cel·la seleccionada a cadascuna de les cel·les que afecta.)
  6. Finalment, per veure les fórmules en lloc dels valors de totes les cel·les, feu clic a Mostra les fórmules .

Trenca el cercle

Excel utilitza referències circulars quan intenta calcular el resultat d’una cel·la que s’ha visitat més d’una vegada durant la ronda de càlcul. Per tant, no us ajudarà en tot moment.

Tot i això, molts models financers contenen referències circulars. Però s’han d’evitar sempre que sigui possible. Ara que ja sabeu com tenir cura de les referències circulars, pot ser que sigui el moment adequat per aprendre funcions Excel més avançades.

Compartir Compartir Tweet Correu electrònic Com s'utilitzen les instruccions IF a Microsoft Excel

Tant si sou un expert experimentat com si inicieu un full de càlcul, voldreu consultar aquesta guia de declaracions IF a Excel.

Llegiu a continuació Temes relacionats
  • Productivitat
  • Full de càlcul
  • Microsoft Excel
  • Microsoft Office 365
  • Consells de Microsoft Office
Sobre l'autor Amir M. Intel·ligència(39 articles publicats)

Amir és un estudiant de farmàcia amb passió per la tecnologia i els jocs. Li agrada tocar música, conduir cotxes i escriure paraules.

Més d'Amir M. Bohlooli

Subscriu-te al nostre butlletí

Uniu-vos al nostre butlletí per obtenir consells tècnics, ressenyes, llibres electrònics gratuïts i ofertes exclusives.

Feu clic aquí per subscriure-us