Cum să găsiți referințe circulare în Excel

Când lucrați cu Microsoft Excel , puteți întâlni ocazional ceea ce este cunoscut sub numele de referință circulară. Acesta este momentul în care o formulă dintr-o celulă se referă la ea însăși, provocând o buclă nesfârșită în procesul de calcul.

Pentru a găsi referințe circulare în Excel, utilizați instrumentul „Verificarea erorilor” din fila „Formule” pentru a identifica celulele cu o dependență circulară.

Acest articol vă arată cum să găsiți referințe circulare în Excel și să le remediați. De asemenea, veți învăța cum să le preveniți atunci când lucrați cu foi de lucru mari sau complexe.

Cuprins

• Ce sunt referințele circulare
• Fereastra de mesaje de eroare de referință circulară
• Cum să găsiți referințe circulare în Excel
• Cum să găsiți referințe circulare cu bara de stare Excel
• Cum să găsiți referințe circulare ascunse în Excel
  • Cum să utilizați instrumentul dependent de urmărire
  • Instrumentul de urmărire a precedentelor
• 2 moduri cum să eliminați referințele circulare în Excel
  • 2. Folosiți formule alternative
• 5 sfaturi pentru prevenirea referințelor circulare
  • 1. Aveți grijă când selectați intervalele
  • 2. Planifică-ți cu atenție formulele
  • 3. Folosiți formule alternative
  • 4. Împărțiți formulele complexe în părți mai mici
  • 5. Remediați problemele pe măsură ce mergeți
• Cum să activați calculul iterativ și de ce să vă deranjați?

Ce sunt referințele circulare

Referințele circulare apar în Excel atunci când calculul unei formule într-o celulă depinde de propria valoare a celulei. Acest lucru creează o buclă și duce la rezultate incorecte sau imprevizibile.

Acest lucru este mai ușor de înțeles cu un exemplu simplu.

Să presupunem că aveți două numere în celulele A1 și B1. Doriți să utilizați o formulă în celula A3 care adună cele două numere. Formula din celula A3 ar trebui să arate astfel:

=A1 + B1

Cu toate acestea, dacă greșiți și includeți C1 în calcule, aveți acum o referință circulară:

=A1 + B1 + C1

Aceasta poate fi o problemă evidentă de evitat atunci când aveți de-a face cu două celule. Cu toate acestea, atunci când lucrați cu game mari de celule, greșelile devin mai probabile.

Din fericire, Excel este conceput pentru a gestiona astfel de situații prin afișarea unui mesaj de avertizare atunci când este detectată o referință circulară.

Cu toate acestea, în unele cazuri, este posibil să întâlniți o referință circulară fără a primi nicio alertă. Prin urmare, este important să știți cum să găsiți și să rezolvați manual aceste probleme.

Fereastra de mesaje de eroare de referință circulară

În mod implicit, Excel monitorizează referințele circulare pe măsură ce introduceți formule în celule. Când apăsați enter într-o celulă cu o problemă, o fereastră pop-up va afișa mesajul de eroare de referință circulară.

Acesta este textul explicativ:

Există una sau mai multe referințe circulare în care o formulă se referă la propria sa celulă fie direct, fie indirect. Acest lucru i-ar putea face să calculeze incorect. Încercați să eliminați sau să modificați aceste referințe sau să mutați formulele în celule diferite.

Când vedeți această casetă de dialog, ar trebui să faceți clic pe „OK” și să utilizați metodele din acest articol pentru a remedia problema.

Cu toate acestea, nu trebuie să remediați imediat eroarea. Puteți continua să lucrați cu foaia de calcul cu intenția de a rezolva problema mai târziu.

În acest caz, trebuie să știți cum să găsiți referințe circulare în Excel după ce ați respins caseta de avertizare.

Cum să găsiți referințe circulare în Excel

Excel are un instrument de verificare a erorilor încorporat care găsește mai multe tipuri de erori, inclusiv referințe circulare. Accesați instrumentul din fila Formule.

Această imagine arată locația din grupul Formula Auditing.

Acum știți cum să găsiți instrumentul, iată pașii detaliați pentru a-l folosi:

1. Accesați fila Formule.

1. Faceți clic pe meniul vertical Verificare erori din grupul Audit formule.

2. Selectați „Referințe circulare” din lista verticală.

3. Faceți clic pe link pentru a trece cursorul la prima celulă cu o problemă.

Imaginea de mai jos arată că instrumentul a găsit o eroare circulară în celula G1. Celula afișată este un hyperlink.

Când faceți clic pe link, cursorul se deplasează la celulă. Acest lucru este foarte util cu un volum mare de date.

După ce ați rezolvat prima referință circulară, puteți reveni la instrumentul de verificare a erorilor pentru a găsi următoarea pe foaie.

Utilizați pașii din secțiunile ulterioare ale acestui articol pentru a remedia problema și utilizați instrumentul pentru a trece la următoarea problemă.

Dacă nu mai există probleme circulare, elementul de meniu „Referințe circulare” din meniul derulant va fi inactiv. Nu mai ai de lucru.

Cum să găsiți referințe circulare cu bara de stare Excel

O altă modalitate de a identifica o referință circulară este să te uiți la bara de stare din partea de jos a foii de lucru Excel.

Când există una sau mai multe probleme în foaia de lucru, bara de stare afișează locația referinței unei celule cu probleme.

De exemplu, dacă există o referință circulară în celula C1 și nu există alte probleme cu foaia de calcul, bara de stare va citi: „Referințe circulare: C1”.

Această imagine vă arată unde să vedeți afișajul.

Cum să găsiți referințe circulare ascunse în Excel

Referințele circulare ascunse în Excel sunt referințe circulare care nu sunt detectate imediat de caracteristicile de eroare încorporate. Cu alte cuvinte, Excel nu afișează avertismentul standard.

Ele se întâmplă atunci când o celulă face referire la o altă celulă indirect, printr-o serie de celule intermediare, și în cele din urmă se referă înapoi la celula originală.

Excel are două instrumente care vă ajută să urmăriți relațiile și să investigați aceste probleme:

1. Instrumentul de urmărire a dependenților

2. Instrumentul de urmărire a precedentelor

Cum să utilizați instrumentul dependent de urmărire

Utilizatorii Excel experimentați profită de instrumentul Trace Dependents pentru a investiga aceste probleme și pentru a remedia referințe circulare. Alegând una sau mai multe celule și folosind acest instrument, Excel vă va afișa celulele dependente.

Instrumentul este disponibil în secțiunea Audit formule din fila Formule.

Pentru a utiliza instrumentul, urmați acești pași:

1. Faceți clic pe celula sau celulele pentru care doriți să urmăriți persoanele dependente.

2. Accesați fila „Formule” din panglica Excel.

3. Faceți clic pe butonul „Trace Dependents” din grupul „Formula Auditing”.

4. Examinați săgeata de urmărire sau săgețile care arată dependențele.

Excel arată săgeți de la valoarea celulei active la orice celule care depind de aceasta. Săgeata albastră din imaginea de mai jos arată că celula D6 are o dependență de celula B2.

Instrumentul de urmărire a precedentelor

Precedentele sunt opusul dependenților. Instrumentul Urmărire precedente vă permite să alegeți o celulă cu o formulă și să vedeți toate celulele de care depinde, adică precedentele.

Instrumentul este disponibil în secțiunea Audit formule din fila Formule.

Pentru a utiliza instrumentul, urmați acești pași:

1. Faceți clic pe celula care conține formula pe care doriți să o investigați.

2. Accesați fila Formulă din panglica Excel.

3. Faceți clic pe butonul „Urmărire precedente” din grupul „Auditare formule”.

4. Examinați săgețile de urmărire pe care Excel le afișează.

Săgețile albastre din imaginea de mai jos vă arată că celula D6 depinde de intervalul de celule A2:B5.

Folosirea ambelor instrumente vă ajută să găsiți referințe circulare Excel care nu sunt imediat evidente. Următorul pas este să remediați problema în celula selectată. Apoi puteți continua să căutați aceeași problemă în alte celule.

2 moduri cum să eliminați referințele circulare în Excel

Există două metode principale pentru a rezolva referințele circulare:

• ruperea referinței circulare

• folosind formule alternative

Să explicăm ambele moduri.

1. Ruperea Referinței Circulare

Ruperea referinței circulare în Excel este o modalitate eficientă de a rezolva problema. Acest lucru necesită să identificați celulele implicate în referința circulară și să le modificați formulele în consecință.

Pentru a face acest lucru, urmați pașii descriși mai devreme pentru a găsi adresa celulei.

Apoi ar trebui să examinați formula din Bara de formule. Căutați o referință la celula în sine.

În primul nostru exemplu de referință circulară, formula de adăugare din celula C1 a inclus o referință la celula în sine:

=A1 + B1 + C1

Acest lucru poate fi rezolvat prin eliminarea adăugării finale. Odată rezolvată această problemă, treceți să găsiți alte referințe circulare și să le abordați.

2. Folosiți formule alternative

Unele dintre cele mai frecvente cauze ale referințelor circulare sunt atunci când se însumează sau se face media celulelor. Greșeala este să includeți celula rezultate în formulă.

În exemplul nostru anterior, formula a folosit operatori de adăugare pentru a calcula suma unui interval de celule. Metoda alternativă este utilizarea funcției SUM Excel.

Pentru a remedia exemplul, înlocuiți formula cu:

=SUMA(A1:B1)

În mod similar, puteți medie un interval cu o formulă precum =(A1+B1)/2. Cu toate acestea, funcția AVERAGE este mai puțin probabil să ducă la o greșeală.

=MEDIE(A1:B1)

5 sfaturi pentru prevenirea referințelor circulare

Pentru a preveni referințele circulare în Microsoft Excel, este esențial să înțelegeți clar modul în care formulele interacționează între ele în foaia Excel.

Prin identificarea relațiilor dintre celule și conștientizarea modului în care aceste conexiuni funcționează împreună, puteți evita crearea de formule circulare.

Iată cele mai bune cinci sfaturi ale noastre pentru a vă ajuta să preveniți referințele circulare.

1. Aveți grijă când selectați intervalele

Din experiența mea, una dintre cele mai comune cauze ale referințelor circulare este atunci când utilizatorii selectează rapid intervale pentru a le include în formulele lor.

Când glisați vertical și/sau orizontal pentru a grupa celule, este ușor să selectați încă o celulă care este adiacentă intervalului corect. Dacă acea celulă este și celula rezultate, ați creat din greșeală o referință circulară.

Această greșeală se face de obicei atunci când se lucrează cu seturi mari de date, cum ar fi rularea testelor chi-pătrat. Soluția este să fii atent la alegerea gamei.

2. Planifică-ți cu atenție formulele

Înainte de a introduce formule, gândiți-vă la logica din spatele calculelor dvs. și la modul în care acestea ar trebui să curgă prin celule.

Puteți mapa calculele în avans folosind instrumente de diagramă sau o tablă albă.

Acest lucru vă poate ajuta să identificați eventualele bucle care ar putea duce la referințe circulare.

3. Folosiți formule alternative

Anumite funcții, cum ar fi INDIRECT sau OFFSET , pot crea din neatenție referințe circulare atunci când sunt utilizate necorespunzător.

Luați în considerare utilizarea de funcții alternative care oferă funcționalități similare fără riscul referințelor circulare.

4. Împărțiți formulele complexe în părți mai mici

În loc să creați o singură formulă complexă cu mai multe funcții imbricate, luați în considerare împărțirea calculelor în părți mai mici și mai ușor de gestionat.

Acest lucru nu numai că face ca registrul de lucru să fie mai ușor de înțeles și de întreținut, dar și reduce șansele de a introduce o referință circulară.

5. Remediați problemele pe măsură ce mergeți

Când lucrați cu registre de lucru mari sau complicate, este, de asemenea, esențial să monitorizați referințele circulare. În loc să ignorați problemele până când un set mare de date și formule a fost asamblat, este mai bine să remediați fiecare eroare pe măsură ce apare.

Puteți alege să ignorați un mesaj de avertizare de referință circulară pentru o scurtă perioadă. Cu toate acestea, ar trebui să utilizați funcția de verificare a erorilor pentru a ajunge din urmă cu referințele circulare existente înainte de prea mult timp.

Acest lucru le face mai ușor de remediat și de a preveni efectele lor asupra registrului de lucru.

Cum să activați calculul iterativ și de ce să vă deranjați?

De obicei, doriți să evitați referințele circulare în foaia de lucru. Cu toate acestea, există câteva scenarii în care acestea sunt necesare.

De exemplu, unele calcule de inginerie sau științifice trebuie să utilizeze rezultatul unui calcul anterior. Aceasta este cunoscută ca o soluție iterativă.

Fiecare buclă rafinează rezultatele unui calcul până când este atins un anumit prag sau țintă.

În aceste cazuri, este esențial să activați calculele iterative în setările Excel pentru a permite referințele circulare dorite. Acest lucru permite software-ului să repete calculele de un număr specificat de ori, convergând către o soluție.

Urmați acești pași pentru a face o referință circulară în fișierul dvs. Excel:

1. Faceți clic pe fila Fișier.

2. Selectati optiunile.

3. Alegeți categoria Formule.

4. În secțiunea „Opțiuni de calcul”, bifați caseta pentru „Activați calculul iterativ”.

5. Schimbați numărul din caseta de iterații maxime sau lăsați ca implicit 100.

6. Utilizați caseta de modificare maximă pentru a reduce iterațiile conform cerințelor dvs.