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

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. Să nu-ți faci 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, 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.

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ă vă înmulțiți 10 din celulă B1 și puneți rezultatul în celulă B1 în sine.

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

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.

Î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ă.

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

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.

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

• 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 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 SUMĂ funcția din celulă A1 la A9 și aduceți rezultatul în celulă 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.

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

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.

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

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.
• Apasă pe Formule meniul de pe panglica Excel.

• În Auditul formulei grup de comenzi, faceți clic pe Verificarea erorilorlista verticală.
• Treceți cursorul peste Referințe circulare opțiunea de a vedea toate instanțele disponibile în toate foile de lucru.
• Selectați fiecare instanță și Excel va identifica automat acele celule.

Dacă nu considerați că metoda de verificare a erorilor 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.
• Du-te la Formule din meniul panglică Excel și apoi faceți clic Urme precedente.
• Excel va evidenția celulele care afectează rezultatul formulei pe celula curentă cu săgeți.

• De asemenea, puteți face clic Dependenți de urmărire 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.

• Apasă pe Fişier pe panglica Excel și apoi deschideți Opțiuni Excel din bara laterală care apare.
• Pe Opțiuni Excel caseta de dialog, selectați Formule meniu din bara laterală și apoi debifați caseta de selectare pentru Activați calculul iterativ.

• Clic Bine 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ă.

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.

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.

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ă.

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.

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ă încurcă datele nesigure dintr-un raport Excel sau un tablou de bord matematic.

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