Cum să găsiți referințe circulare în Excel pentru a evita datele defecte

Ați văzut eroarea de referință circulară pe o foaie Excel și vă întrebați dacă foaia de lucru este ruptă? Aveţi încredere în mine! Nu ești primul care se confruntă cu această eroare Excel. Sute și mii de utilizatori Excel se confruntă cu erori circulare de referință în fiecare zi. Nu vă faceți griji! Iată cum să găsiți referințe circulare în Excel și să le rezolvați.

Fiind un utilizator pasionat de Excel, rareori tastați aceleași date în multe celule. În schimb, importați un set de date din sursă și utilizați referirea la celule pentru a crea formule. Astfel, creați o foaie de calcul dinamică.

Când cineva modifică valorile celulelor referințelor de celule, foaia de lucru se actualizează automat. Aceasta se numește automatizare a analizei datelor și analiștilor de date le place să se joace cu referințele de celule.

Cu toate acestea, problema apare atunci când introduceți în mod intenționat sau accidental adresa celulei la care efectuați în prezent un calcul. Sau, atunci când creați multe formule în foaia de lucru, o celulă care conține o formulă apare indirect într-o altă formulă.

În astfel de situații și în multe alte situații similare, obțineți eroarea de referință circulară în Excel. Citiți mai departe pentru a afla ce este referința circulară, motivele sale, de ce să o evitați și, cel mai important, cum să găsiți referințe circulare în Excel pentru a scăpa definitiv de aceste anomalii.

Ce este o referință circulară în Excel?

Excel întâmpină o eroare de referință circulară când vede că una sau mai multe funcții ale foii de lucru se referă la adresa celulei la care au fost create funcțiile lor.

Poate părea confuz pentru dvs., așa că gândiți-vă cât de grav ar putea fi pentru aplicația Excel. Pe scurt, nu doriți niciodată să puneți adresa celulei într-o formulă în care o creați. Priviți următorul exemplu pentru o clarificare vizuală:

Încercați să înmulțiți 10 din celula B1 și să puneți rezultatul în celula B1 însăși.

Acum, în scenariul de mai sus, dacă apăsați doar pe OK , Excel va închide eroarea și va afișa zero ca rezultat calculat. Acesta este doar un exemplu simplu.

Ce este referința circulară în Excel

Acum, imaginați-vă că există sute de formule într-o foaie de lucru de contabilitate, o foaie de lucru de planificare financiară, statul de plată a angajaților, o foaie de lucru pentru plăți la furnizor și așa mai departe.

Veți avea încredere în datele defecte calculate de Excel din cauza erorii de referință circulară și asta, la rândul său, va afecta afacerea dvs. sau luarea deciziilor profesionale.

În plus, referința circulară poate reduce drastic viteza calculelor în aplicația Excel. În cazuri grave, eroarea poate cauza performanțe extrem de lente și în PC, dacă nu este rezolvată imediat.

Cu toate acestea, dacă lucrați la formule Excel avansate în care modelul dvs. de date necesită intrări intermediare din referința circulară pentru a continua cu o funcție lungă, poate fi necesar să o utilizați. De fapt, Excel vă permite să utilizați această metodă de calcul atunci când este necesar.

De exemplu, în unitățile de producere a energiei, cum ar fi morile de vânt, trebuie să calculați energia de ieșire reciclată dintr-o fracțiune din energia de intrare. Alternativ, într-o fabrică chimică, inginerii folosesc tija de ieșire ca intrare pentru același cazan sau vas de proces. Astfel de calcule de inginerie de nivel înalt necesită într-adevăr referințe circulare pentru calcule iterative.

Când vedeți eroarea de referință circulară în Excel?

În mod clar, fereastra pop-up de referință circulară nu este un mesaj de eroare. Este o modalitate prin care Excel vă informează că a întâlnit referințe circulare în una sau mai multe formule și care poate influența algoritmul și performanța de calcul.

Pentru a evita orice daune cauzate de referințele circulare în formulele Excel, trebuie să știți când apar și când nu. Găsiți mai jos cazurile:

• În orice foaie de lucru sau registru de lucru deschis, creați o formulă și introduceți referința circulară pentru prima dată.
• Dumneavoastră sau cineva ați eliminat toate referințele circulare din fișierul Excel și le-ați salvat. Acum, tu sau un alt utilizator introduceți o referință circulară în același fișier.
• Ați închis toate registrele de lucru deschise. Acum, deschideți un alt registru de lucru și apoi introduceți o formulă care conține o referință circulară.

Când vedeți eroarea de referință circulară în Excel

• Tocmai ați deschis un registru de lucru care conținea anterior una sau mai multe referințe circulare. Vei vedea avertismentul.
• Odată ce Excel ți-a arătat mesajul de avertizare referință circulară o dată și ai făcut clic pe OK , nu va mai afișa aceeași fereastră pop-up în aceeași sesiune, chiar dacă vei continua să creezi mai multe formule circulare de referință.

Astfel, trebuie să căutați cu atenție cazurile de mai sus pentru a captura avertismente de referință circulară și pentru a le rezolva dacă nu căutați calcule iterative.

Tipuri de referințe circulare în Excel

Dacă doriți să aflați cum să găsiți referințe circulare în Excel, trebuie să înțelegeți tipurile acestora. Pentru că găsirea și eliminarea referințelor circulare în Excel nu este o sarcină automată. Găsiți mai jos tipurile comune ale acestei erori:

Un exemplu de utilizare a referințelor circulare

• Referințele circulare intenționate apar atunci când le utilizați în mod intenționat în scopuri de calcul iterativ. De exemplu, trebuie să introduceți un marcaj de timp pe o altă celulă atunci când cineva introduce o valoare într-o celulă la care face referire folosind funcții volatile și logice, cum ar fi IF, SAU, SUMIF etc.

Referințe circulare neintenționate în Excel

• Referințe circulare neintenționate atunci când încercați să lucrați mai rapid la o formulă Excel și vă referiți la celula care conține formula din greșeală. De exemplu, trebuie să aplicați funcția SUM din celula A1 la A9 și să obțineți rezultatul în celula A10 . Dar, din greșeală, tastați formula astfel pe celula A10:

=SUMA(A2:A10)

• Referințele circulare ascunse sunt cele care nu afișează niciun mesaj de avertizare atunci când creați o formulă sau deschideți foaia de lucru pentru prima dată. Se întâmplă pentru că cineva a permis calculul iterativ pe fișierul Excel.

Un exemplu de referință circulară indirectă

• Referințele circulare indirecte sunt, de asemenea, populare și apar atunci când creați o formulă nouă în orice celulă, de exemplu, celula A1 , iar această nouă formulă ar folosi o altă funcție într-o altă celulă, cum ar fi celula B1 , care deja folosește celula A1 ca referință. .

Acum că ați înțeles elementele de bază ale referințelor circulare în Excel, să aflăm mai jos cum să găsiți referințe circulare în Excel:

Cum să găsiți referințe circulare în Excel: Din bara de stare

Cu condiția ca funcția de calcul iterativ să fie dezactivată, puteți vedea următoarele notificări despre referințele circulare în bara de stare Excel:

• Referințe circulare: C3  (sau orice altă referință de celulă) atunci când vă aflați pe o foaie de lucru care conține formula circulară în registrul de lucru.

Se afișează referințe circulare în bara de stare Excel

• Veți vedea referințe circulare doar dacă vă aflați pe o foaie de lucru care nu conține niciuna, dar registrul de lucru are câteva dintre aceste formule circulare.

Cum să găsiți referințe circulare în Excel: utilizând verificarea erorilor

Este cel mai bun mod de a verifica referințele circulare într-un registru de lucru sau o foaie de lucru. Iată pașii pe care trebuie să îi cunoașteți:

• Deschideți o foaie de lucru despre care bănuiți că conține formule circulare.
• Faceți clic pe meniul Formule din panglica Excel.

Utilizați verificarea erorilor pentru Cum să găsiți referințe circulare în Excel

• În grupul de comenzi Auditare formule , faceți clic pe lista derulantă Verificare erori  .
• Treceți cursorul peste opțiunea Referințe circulare pentru a vedea toate instanțele disponibile în toate foile de lucru.
• Selectați fiecare instanță și Excel va identifica automat acele celule.

Cum să găsiți referințe circulare în Excel: Din Formula Tracing

Dacă nu considerați că metoda de verificare a erorilor nu este suficient de eficientă pentru a detecta un număr mare de formule cu referințe circulare, puteți încerca acești pași pentru a utiliza metoda de urmărire a formulei:

• Selectați orice celulă la care se face referire sau celula care conține formula.
• Accesați fila Formule din meniul panglică Excel și apoi faceți clic pe Urmărire precedente .
• Excel va evidenția celulele care afectează rezultatul formulei pe celula curentă cu săgeți.

Utilizați trasarea formulei pentru Cum să găsiți referințe circulare în Excel

• De asemenea, puteți face clic pe Urmăriți dependenți pentru a descoperi celulele afectate de valoarea referinței celulei selectate.
• Dacă Excel nu afișează erori după ce faceți clic pe comenzile de mai sus, nu există referințe circulare în foaia de lucru.
• Repetați pașii de pe toate foile de lucru pentru a verifica întregul registru de lucru.

Această metodă este deosebit de importantă atunci când funcțiile de calcul iterativ sunt active pe foaia de lucru Excel.

Cum să găsiți referințe circulare în Excel: Folosind opțiunile Excel

• Faceți clic pe fila Fișier din panglica Excel și apoi deschideți Opțiuni Excel din bara laterală care apare.
• În caseta de dialog Opțiuni Excel , selectați meniul Formule din bara laterală și apoi debifați caseta de selectare pentru Activare calcul iterativ .

Cum să dezactivați calculul iterativ în Excel

• Faceți clic pe OK pentru a salva modificările.
• Acum, registrul de lucru sau foaia de lucru vă vor afișa automat avertismentul de referință circulară.
• Dacă nu apare instantaneu, salvați registrul de lucru, închideți-l și redeschideți-l pentru a vedea avertismentul.
• Odată ce vedeți avertismentul, verificați bara de stare Excel pentru a ști care celulă se află în referința circulară.

Cum se rezolvă referințele circulare în Excel

Modul sigur de a corecta una sau mai multe erori circulare de referință pe Excel este prin editarea manuală a fiecărei formule care o conține. În acest fel, puteți rezolva atât referințele circulare accidentale, cât și indirecte în Excel.

Cum se rezolvă referințele circulare în Excel

O altă modalitate ușoară este să eliminați formula din celula curentă și să o plasați în altă parte în aceeași foaie de lucru.

Cum să găsiți referințe circulare în Foi de calcul Google (metoda bonus)

La fel ca Excel, Google Sheets nu permite referințe circulare în una sau mai multe formule dacă nu ați activat funcția de calcul iterativ. Prin urmare, Foi de calcul Google va afișa imediat #REF! eroare pe celula care conține o formulă circulară.

Foi de calcul Google Cum să găsiți referințe circulare

Prin urmare, puteți selecta pur și simplu toate celulele care conțin #REF! avertisment pentru a găsi și rezolva referințe circulare pe Foi de calcul Google.

Referințe circulare Excel: cuvinte finale

Deci, acum cunoașteți toate secretele pentru cum să găsiți referințe circulare în Excel. Utilizați metoda care vă place și renunțați la toate referințele circulare pentru a vă asigura că nu vă încurca datele nesigure dintr-un raport Excel sau un tablou de bord matematic.

În continuare, cele  mai bune șabloane de calendar Excel pentru orice an  și cele  mai bune șabloane de buget Excel .