Cum să găsiți referințe circulare în Excel (rapid și ușor)

În timp ce lucrați cu formule Excel, este posibil să vedeți cândva următorul mesaj de avertizare.

Această solicitare vă spune că există o referință circulară în foaia dvs. de lucru și acest lucru poate duce sau un calcul incorect prin formule. De asemenea, vă solicită să abordați această problemă de referință circulară și să o sortați.

În acest tutorial, voi acoperi tot ce trebuie să știți despre referința circulară, precum și cum să găsiți și să eliminați referințele circulare în Excel.

Asadar, haideti sa începem!

Ce este Referința circulară în Excel?

În cuvinte simple, o referință circulară se întâmplă atunci când ajungeți să aveți o formulă într-o celulă - care utilizează în sine celula (în care a fost introdusă) pentru calcul.

Permiteți-mi să încerc să explic acest lucru printr-un exemplu simplu.

Să presupunem că aveți setul de date în celula A1: A5 și că utilizați formula de mai jos în celula A6:

= SUMA (A1: A6)

Acest lucru vă va oferi un avertisment de referință circular.

Acest lucru se datorează faptului că doriți să însumați valorile din celula A1: A6, iar rezultatul ar trebui să fie în celula A6.

Aceasta creează o buclă deoarece Excel continuă să adauge noua valoare în celula A6, care continuă să se schimbe (deci, o buclă de referință circulară).

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

În timp ce solicitarea de avertizare de referință circulară este destul de amabilă să vă spună că există în foaia dvs. de lucru, nu vă spune unde se întâmplă și ce referințe de celule o cauzează.

Deci, dacă încercați să găsiți și să gestionați referințe circulare în foaia de lucru, trebuie să cunoașteți o modalitate de a le găsi cumva.

Mai jos sunt pașii pentru a găsi o referință circulară în Excel:

1. Activați foaia de lucru care are referința circulară
2. Faceți clic pe fila Formule
3. În grupul Editare formulă, faceți clic pe pictograma derulantă Verificare erori (săgeată îndreptată în jos în dreapta)
4. Plasați cursorul peste opțiunea Referințe circulare. Vă va arăta celula care are o referință circulară în foaia de lucru
5. Faceți clic pe adresa celulei (care este afișată) și vă va duce la acea celulă din foaia de lucru.

După ce ați abordat problema, puteți urma din nou aceiași pași de mai sus și va afișa mai multe referințe de celule care au referința circulară. Dacă nu există, nu veți vedea nicio referință de celulă,

O altă modalitate rapidă și ușoară de a găsi referința circulară este căutând bara de stare. În partea stângă a acestuia, vă va arăta textul Referință circulară împreună cu adresa celulei.

Există câteva lucruri pe care trebuie să le cunoașteți atunci când lucrați cu referințe circulare:

1. În cazul în care calculul iterativ este activat (acoperit mai târziu în acest tutorial), bara de stare nu va afișa adresa circulară a celulei de referință
2. În cazul în care referința circulară nu este în foaia activă (ci în alte foi din același registru de lucru), va afișa doar referința circulară și nu adresa celulei
3. În cazul în care primiți o solicitare de avertizare de referință circulară o dată și o respingeți, aceasta nu va mai afișa solicitarea din nou data viitoare.
4. Dacă deschideți un registru de lucru care are referința circulară, acesta vă va arăta promptul imediat ce se deschide registrul de lucru.

Cum se elimină o referință circulară în Excel?

După ce ați identificat că există referințe circulare în foaia dvs., este timpul să le eliminați (cu excepția cazului în care doriți să fie acolo dintr-un motiv).

Din păcate, nu este la fel de simplu ca apăsarea tastei de ștergere. Deoarece acestea sunt dependente de formule și fiecare formulă este diferită, trebuie să analizați acest lucru de la caz la caz.

În cazul în care este doar o chestiune ca referința la celulă să cauzeze problema din greșeală, puteți corecta pur și simplu ajustând referința.

Dar uneori, nu este atât de simplu.

Referința circulară poate fi cauzată, de asemenea, pe baza mai multor celule care se alimentează reciproc la mai multe niveluri.

Permiteți-mi să vă arăt un exemplu.

Mai jos este o referință circulară în celula C6, dar nu este doar un simplu caz de auto-referință. Este pe mai multe niveluri în care celulele pe care le folosește în calcule se referă și ele reciproc.

• Formulele din celula A6 este = SUM (A1: A5) + C6
• Formula este celula C1 este = A6 * 0,1
• Formula din celula C6 este = A6 + C1

În exemplul de mai sus, rezultatul în celula C6 este dependent de valorile din celula A6 și C1, care la rândul lor sunt dependente de celula C6 (provocând astfel eroarea de referință circulară)

Și din nou, am ales un exemplu foarte simplu doar în scopuri demonstrative. În realitate, acestea ar putea fi destul de dificil de identificat și poate departe în aceeași foaie de lucru sau chiar împrăștiate pe mai multe foi de lucru.

Într-un astfel de caz, există o modalitate de a identifica celulele care cauzează referință circulară și apoi de a le trata.

Este folosind opțiunea Trace Precedents.

Mai jos sunt pașii pentru a utiliza urmele precedente pentru a găsi celule care se alimentează celulei care are referința circulară:

1. Selectați celula care are referința circulară
2. Faceți clic pe fila Formule
3. Faceți clic pe Trace Precedents

Pașii de mai sus vă vor arăta săgeți albastre care vă vor spune ce celule alimentează în formula din celula selectată. În acest fel, puteți inspecta formulele și celulele și puteți scăpa de referința circulară.

În cazul în care lucrați cu modele financiare complexe, poate fi posibil ca aceste precedente să adâncească, de asemenea, la mai multe niveluri.

Acest lucru funcționează bine dacă aveți toate formulele referitoare la celule în aceeași foaie de lucru. Dacă se află în mai multe foi de lucru, această metodă nu este eficientă.

Cum se activează / dezactivează calculele iterative în Excel

Când aveți o referință circulară într-o celulă, primiți mai întâi promptul de avertizare așa cum se arată mai jos și, dacă închideți această casetă de dialog, vă va da 0 ca rezultat în celulă.

Acest lucru se datorează faptului că atunci când există o referință circulară, este o buclă nesfârșită și Excel nu vrea să se prindă de ea. Deci, returnează un 0.

Dar, în unele cazuri, este posibil să doriți ca referința circulară să fie activă și să faceți câteva iterații. Într-un astfel de caz, în loc de o buclă infinită, puteți decide de câte ori ar trebui rulată bucla.

Aceasta se numește calcul iterativ în Excel.

Mai jos sunt pașii pentru activarea și configurarea calculelor iterative în Excel:

1. Faceți clic pe fila Fișier
2. Faceți clic pe Opțiuni. Aceasta va deschide caseta de dialog Opțiuni Excel
3. Selectați Formula în panoul din stânga
4. În secțiunea Opțiuni de calcul, bifați caseta „Activați calculul iterativ”. Aici puteți specifica iterațiile maxime și valoarea maximă a modificării

Asta e! Pașii de mai sus ar permite calcularea iterativă în Excel.

De asemenea, permiteți-mi să explic rapid cele două opțiuni din calculul iterativ:

• Iterații maxime: Acesta este numărul maxim de ori pe care doriți să îl calculați Excel înainte de a vă oferi rezultatul final. Deci, dacă specificați acest lucru ca 100, Excel va rula bucla de 100 de ori înainte de a vă oferi rezultatul final.
• Schimbare maximă: Aceasta este modificarea maximă, care, dacă nu este realizată între iterații, calculul ar fi oprit. În mod implicit, valoarea este .001. Cu cât această valoare este mai mică, cu atât rezultatul ar fi mai precis.

Amintiți-vă că de câte ori se execută iterațiile, cu atât mai mult timp și resurse sunt necesare pentru ca Excel să o facă. În cazul în care mențineți iterațiile maxime ridicate, aceasta poate duce la încetinirea sau prăbușirea Excel-ului dvs.

Notă: Când sunt activate calculele iterative, Excel nu vă va afișa promptul de avertizare de referință circulară și îl va afișa și acum în bara de stare.

Utilizarea deliberată a referințelor circulare

În majoritatea cazurilor, prezența referinței circulare în foaia dvs. de lucru ar fi o eroare. Iată de ce Excel vă arată un prompt care spune - „Încercați să eliminați sau să modificați aceste referințe sau să mutați formulele în celule diferite”.

Dar ar putea exista unele cazuri specifice în care aveți nevoie de o referință circulară, astfel încât să puteți obține rezultatul dorit.

Un astfel de caz specific l-am scris deja despre obținerea ștampilei de timp într-o celulă dintr-o celulă din Excel.

De exemplu, să presupunem că doriți să creați o formulă, astfel încât fiecare intrare să fie făcută într-o celulă din coloana A, marcajul de timp să apară în coloana B (așa cum se arată mai jos):

În timp ce puteți insera cu ușurință, introduceți o marcă de timp folosind formula de mai jos:

= IF (A2 "", IF (B2 "", B2, ACUM ()), "")

Problema cu formula de mai sus este că ar actualiza toate marcajele de timp de îndată ce orice modificare este făcută în foaia de lucru sau dacă foaia de lucru este redeschisă (deoarece formula ACUM este volatilă)

Pentru a rezolva această problemă, puteți utiliza o metodă de referință circulară. Utilizați aceeași formulă, dar activați calculul iterativ.

Există și alte cazuri în care se dorește posibilitatea de a utiliza referințe circulare (puteți găsi un exemplu aici).

Notă: Deși pot exista unele cazuri în care puteți utiliza referințe circulare, consider că este mai bine să evit să o utilizați. Referințele circulare pot afecta, de asemenea, performanța registrului de lucru și o pot face mai lentă. În cazuri rare în care aveți nevoie de el, prefer întotdeauna să folosiți coduri VBA pentru a face treaba.

Sper că ți s-a părut util acest tutorial!

Alte tutoriale Excel pe care le puteți găsi utile:

• #REF! Eroare în Excel; Cum se remediază eroarea de referință!
• Excel VBA Error Handling
• Utilizați IFERROR cu VLOOKUP pentru a scăpa de erori # N / A
• Cum să faceți referire la o altă foaie sau registru de lucru în Excel (cu exemple)
• Referințe de celule absolute, relative și mixte în Excel