Unul dintre cele mai frecvente avertismente de eroare pe care le întâlnesc utilizatorii în Excel este „Referința circulară”. Mii de utilizatori au aceeași problemă și apare atunci când o formulă se referă direct sau indirect la propria sa celulă, provocând o buclă nesfârșită de calcule.
De exemplu, aveți două valori în celulele B1 și B2. Când formula =B1+B2 este introdusă în B2, se creează o referință circulară; formula din B2 se recalculează în mod repetat, deoarece de fiecare dată când calculează, valoarea B2 s-a schimbat.
Majoritatea referințelor circulare sunt greșeli neintenționate; Excel vă va avertiza despre acestea. Cu toate acestea, există și referințe circulare destinate, care sunt folosite pentru a face calcule iterative. Referințele circulare neintenționate din foaia de lucru ar putea face ca formula să calculeze incorect.
Prin urmare, în acest articol, vom explica tot ce trebuie să știți despre referințele circulare și cum să găsiți, reparați, eliminați și utilizați referințe circulare în Excel.
Cum să găsiți și să gestionați referința circulară în Excel
Când lucrăm cu Excel, întâlnim uneori erori de referință circulare care se întâmplă atunci când introduceți o formulă care include celula în care se află formula dvs. Practic, se întâmplă atunci când formula ta încearcă să se calculeze singură.
De exemplu, aveți o coloană de numere în celula A1:A4 și utilizați funcția SUM (=SUM(A1:A5)) în celula A5. Celula A5 se referă direct la propria sa celulă, ceea ce este greșit. Prin urmare, veți primi următorul avertisment circular de referință:
Odată ce ați primit mesajul de avertizare de mai sus, puteți face clic pe butonul „Ajutor” pentru a afla mai multe despre eroare sau puteți închide fereastra cu mesajul de eroare făcând clic pe butonul „OK” sau „X” și obțineți „0” ca rezultat.
Uneori, buclele de referință circulare pot cauza blocarea calculului sau încetinirea performanței foii de lucru. Referința circulară poate duce, de asemenea, la o serie de alte probleme, care nu vor fi evidente imediat. Deci, cel mai bine este să evitați acestea.
Referințe circulare directe și indirecte
Referințele circulare pot fi clasificate în două tipuri: Referințe circulare directe și Referințe circulare indirecte.
Referință directă
O referință circulară directă este destul de simplă. Mesajul de avertizare privind referința circulară directă apare atunci când formula se referă direct la propria sa celulă.
În exemplul de mai jos, formula din celula A2 se referă direct la propria sa celulă (A2).
Odată ce apare mesajul de avertizare, puteți face clic pe „OK”, dar va avea ca rezultat doar „0”.
Referință circulară indirectă
O referință circulară indirectă în Excel apare atunci când o valoare dintr-o formulă se referă la propria sa celulă, dar nu direct. Cu alte cuvinte, referința circulară poate fi formată din două celule care se referă una la alta.
Să explicăm cu acest exemplu simplu.
Acum valoarea începe de la A1 care are valoarea 20.
Apoi, celula C3 se referă la celula A1.
Apoi, celula A5 se referă la celula C3.
Acum înlocuiți valoarea 20 din celula A1 cu formula așa cum se arată mai jos. Orice altă celulă este dependentă de celula A1. Când utilizați o referință a oricărei alte celule anterioare de formulă din A1, va provoca o avertizare de referință circulară. Deoarece, formula din A1 se referă la celula A5, care se referă la C3, iar celula C3 se referă la A1, de unde referința circulară.
Când faceți clic pe „OK”, rezultă o valoare de 0 în celula A1 și Excel creează o linie legată care arată Precedentele de urmărire și Dependenții de urmărire, așa cum se arată mai jos. Putem folosi această funcție pentru a găsi și a remedia/elimina cu ușurință referințele circulare.
Cum să activați/dezactivați referințele circulare în Excel
În mod implicit, calculele iterative sunt dezactivate (dezactivate) în Excel. Calculele iterative sunt calcule repetitive până când îndeplinește o anumită condiție. Când este dezactivat, Excel afișează un mesaj de referință circulară și returnează un 0 ca rezultat.
Cu toate acestea, uneori sunt necesare referințe circulare pentru a calcula o buclă. Pentru a utiliza referința circulară, trebuie să activați calculele iterative în Excel și vă va permite să efectuați calculele. Acum, permiteți-ne să vă arătăm cum puteți activa sau dezactiva calculele iterative.
În Excel 2010, Excel 2013, Excel 2016, Excel 2019 și Microsoft 365, accesați fila „Fișier” din colțul din stânga sus al Excel, apoi faceți clic pe „Opțiuni” în panoul din stânga.
În fereastra Opțiuni Excel, accesați fila „Formulă” și bifați caseta de selectare „Activați calculul iterativ” din secțiunea „Opțiuni de calcul”. Apoi faceți clic pe „OK” pentru a salva modificările.
Acest lucru va permite calculul iterativ și, astfel, va permite referința circulară.
Pentru a realiza acest lucru în versiunile anterioare de Excel, urmați acești pași:
- În Excel 2007, faceți clic pe butonul Office > Opțiuni Excel > Formule > Zona de iterație.
- În Excel 2003 și versiunile anterioare, trebuie să accesați Meniu > Instrumente > Opțiuni > fila Calcul.
Iterații maxime și parametrii de modificare maximă
Odată ce activați calculele iterative, puteți controla calculele iterative, specificând două opțiuni disponibile în secțiunea Activează calculul iterativ, așa cum se arată în captura de ecran de mai jos.
- Iterații maxime – Acest număr specifică de câte ori trebuie recalculată formula înainte de a vă oferi rezultatul final. Valoarea implicită este 100. Dacă o modificați la „50”, Excel va repeta calculele de 50 de ori înainte de a vă oferi rezultatul final. Amintiți-vă că cu cât este mai mare numărul de iterații, cu atât este nevoie de mai multe resurse și timp pentru a calcula.
- Schimbare maximă – Determină variația maximă între rezultatele calculului. Această valoare determină acuratețea rezultatului. Cu cât numărul este mai mic, cu atât rezultatul va fi mai precis și cu atât este nevoie de mai mult pentru a calcula foaia de lucru.
Dacă opțiunea de calcule iterative este activată, nu veți primi niciun avertisment ori de câte ori există o referință circulară în foaia de lucru. Activați calculul interactiv numai atunci când este absolut necesar.
Găsiți referință circulară în Excel
Să presupunem că aveți un set de date mare și ați primit avertismentul de referință circulară, va trebui totuși să aflați unde (în ce celulă) a apărut eroarea pentru a o remedia. Pentru a găsi referințe circulare în Excel, urmați acești pași:
Utilizarea instrumentului de verificare a erorilor
Mai întâi, deschideți foaia de lucru în care s-a întâmplat referința circulară. Accesați fila „Formulă”, faceți clic pe săgeata de lângă instrumentul „Verificarea erorilor”. Apoi treceți cursorul peste opțiunea „Referințe circulare”, Excel vă va afișa lista tuturor celulelor care sunt implicate în referința circulară, așa cum se arată mai jos.
Faceți clic pe adresa de celulă dorită din listă și vă va duce la acea adresă de celulă pentru a rezolva problema.
Utilizarea barei de stare
De asemenea, puteți găsi referința circulară în bara de stare. Pe bara de stare a Excel, vă va afișa cea mai recentă adresă de celulă cu o referință circulară, cum ar fi „Referințe circulare: B6” (vezi captura de ecran de mai jos).
Există anumite lucruri pe care ar trebui să le cunoașteți când manipulați referința circulară:
- Bara de stare nu va afișa adresa celulei de referință circulară atunci când opțiunea Calcul iterativ este activată, așa că trebuie să o dezactivați înainte de a începe să căutați registrul de lucru pentru referințe circulare.
- În cazul în care referința circulară nu este găsită în foaia activă, bara de stare afișează doar „Referințe circulare” fără adresă de celulă.
- Veți primi o singură dată o solicitare circulară de referință și după ce faceți clic pe „OK”, nu va mai afișa solicitarea data viitoare.
- Dacă registrul de lucru are referințe circulare, acesta vă va afișa promptul de fiecare dată când îl deschideți până când rezolvați referința circulară sau până când activați calculul iterativ.
Eliminați o referință circulară în Excel
Găsirea referințelor circulare este ușoară, dar repararea acestora nu este atât de simplă. Din păcate, nu există nicio opțiune în Excel care să vă permită să eliminați toate referințele circulare simultan.
Pentru a fixa referințe circulare, trebuie să găsiți fiecare referință circulară individual și să încercați să o modificați, să eliminați cu totul formula circulară sau să o înlocuiți cu alta.
Uneori, în formule simple, tot ce trebuie să faceți este să reajustați parametrii formulei astfel încât să nu se refere la sine. De exemplu, modificați formula din B6 în =SUM(B1:B5)*A5 (schimbarea B6 în B5).
Va returna rezultatul calculului ca „756”.
În cazurile în care o referință circulară Excel este greu de găsit, puteți utiliza funcțiile Urmărire precedente și Urmărire dependenți pentru a o urmări înapoi până la sursă și a o rezolva unul câte unul. Săgeata arată care celule sunt afectate de celula activă.
Există două metode de urmărire care vă pot ajuta să ștergeți referințele circulare arătând relațiile dintre formule și celule.
Pentru a accesa metodele de urmărire, accesați fila „Formule”, apoi faceți clic fie pe „Urmărire precedente”, fie „Urmări dependente” în grupul Audit formule.
Urme precedente
Când selectați această opțiune, urmărește celulele care afectează valoarea celulei active. Desenează o linie albastră care indică celulele care afectează celula curentă. Tasta de comandă rapidă pentru a utiliza precedentele de urmărire este Alt + T U T.
În exemplul de mai jos, săgeata albastră arată celulele care afectează valoarea B6 sunt B1:B6 și A5. După cum puteți vedea mai jos, celula B6 este, de asemenea, parte a formulei, ceea ce o face o referință circulară și face ca formula să returneze „0” ca rezultat.
Acest lucru poate fi rezolvat cu ușurință prin înlocuirea B6 cu B5 în argumentul SUM: =SUM(B1:B5).
Dependenți de urmărire
Funcția de urmărire dependentă urmărește celulele care sunt dependente de celula selectată. Această caracteristică desenează o linie albastră indicând care celule sunt afectate de celula selectată. Adică, afișează care celule conțin formule care fac referire la celula activă. Tasta de comandă rapidă pentru a utiliza persoanele dependente este Alt + T U D.
În exemplul următor, celula D3 este afectată de B4. Este dependent de B4 pentru valoarea sa pentru a produce rezultate. Prin urmare, dependenta de urme trasează o linie albastră de la B4 la D3, indicând faptul că D3 este dependent de B4.
Folosind în mod deliberat referințe circulare în Excel
Utilizarea referințelor circulare nu este recomandată, dar ar putea exista unele cazuri rare în care aveți nevoie de o referință circulară, astfel încât să puteți obține rezultatul dorit.
Să explicăm asta folosind un exemplu.
Pentru început, activați „Calcul iterativ” în registrul de lucru Excel. După ce ați activat Calculul iterativ, puteți începe să utilizați referințe circulare în avantajul dvs.
Să presupunem că cumpărați o casă și doriți să oferiți agentului dvs. un comision de 2% din costul total al casei. Costul total va fi calculat în celula B6, iar procentul comisionului (taxa de agent) este calculat în B4. Comisionul se calculează din costul total, iar costul total include comisionul. Deoarece celulele B4 și B6 depind unele de altele, se creează o referință circulară.
Introduceți formula pentru a calcula costul total în celula B6:
=SUMA(B1:B4)
Deoarece costul total include taxa de agent, am inclus B4 în formula de mai sus.
Pentru a calcula comisionul de agent de 2%, introduceți această formulă în B4:
=B6*2%Acum, formula din celula B4 depinde de valoarea lui B6 pentru a calcula 2% din comisionul total și formula din B6 depinde de B4 pentru a calcula costul total (inclusiv comisionul de agent), de unde și referința circulară.
Dacă calculul iterativ este activat, Excel nu vă va da un avertisment sau un 0 în rezultat. În schimb, rezultatul celulelor B6 și B4 va fi calculat așa cum se arată mai sus.
Opțiunea de calcule iterative este de obicei dezactivată implicit. Dacă nu l-ați pornit și când introduceți formula în B4, care va crea o referință circulară. Excel va emite avertismentul și când faceți clic pe „OK”, va fi afișată săgeata trasoare.
Asta e. Acesta a fost tot ce trebuie să știți despre referințele circulare în Excel.