Kako pronaći kružne reference u Excelu da biste izbjegli neispravne podatke

Jeste li vidjeli pogrešku kružne reference na Excel listu i pitate se je li radni list pokvaren? Vjeruj mi! Niste prvi koji se suočio s ovom Excel greškom. Stotine i tisuće korisnika Excela svakodnevno se susreću s kružnim referentnim pogreškama. Bez brige! Evo kako pronaći kružne reference u Excelu i riješiti ih.

Budući da ste strastveni korisnik programa Excel, rijetko upisujete iste podatke u mnogo ćelija. Umjesto toga uvozite skup podataka iz izvora i koristite referenciranje ćelija za stvaranje formula. Tako stvarate dinamičku proračunsku tablicu.

Kada netko promijeni vrijednosti ćelija referenci ćelije, radni list se automatski ažurira. To se zove automatizacija analize podataka, a analitičari podataka vole se igrati s referencama ćelija.

Međutim, problem nastaje kada namjerno ili slučajno upišete adresu ćelije u kojoj trenutno izvodite izračun. Ili, kada stvarate mnogo formula na radnom listu, ćelija koja sadrži formulu neizravno se pojavljuje u drugoj formuli.

U takvim i mnogim sličnim situacijama dobivate pogrešku kružne reference u Excelu. Čitajte dalje da biste saznali što je kružna referenca, razlozi za to, zašto je izbjegavati i što je najvažnije kako pronaći kružne reference u Excelu da biste se zauvijek riješili ovih anomalija.

Što je kružna referenca u Excelu?

Excel nailazi na pogrešku kružne reference kada vidi da jedna ili više funkcija radnog lista upućuje na adresu ćelije na kojoj su njihove funkcije stvorene.

Možda vam zvuči zbunjujuće, pa razmislite koliko bi to moglo biti ozbiljno za aplikaciju Excel. Ukratko, nikada ne želite staviti adresu ćelije u formulu gdje je stvarate. Za vizualno pojašnjenje pogledajte sljedeći primjer:

Pokušavate pomnožiti 10 iz ćelije B1 i staviti rezultat u samu ćeliju B1 .

Sada, u gornjem scenariju, ako samo pritisnete OK , Excel će zatvoriti pogrešku i prikazati nulu kao izračunati rezultat. Ovo je samo jednostavan primjer.

Što je kružna referenca u Excelu

Sada zamislite da postoje stotine formula u knjigovodstvenom radnom listu, radnom listu za financijsko planiranje, platnom spisku zaposlenika, radnom listu za plaćanje dobavljača i tako dalje.

Vjerovat ćete neispravnim podacima koje izračunava Excel zbog kružne referentne pogreške, a to će zauzvrat utjecati na vaše poslovne ili profesionalne odluke.

Nadalje, kružno referenciranje može ozbiljno smanjiti brzinu izračuna unutar aplikacije Excel. U teškim slučajevima, sama pogreška može uzrokovati izuzetno spore performanse i na računalu ako se odmah ne riješi.

Međutim, ako radite na naprednim Excel formulama gdje vaš podatkovni model zahtijeva posredničke unose iz kružne reference da biste nastavili s dugom funkcijom, možda ćete je morati upotrijebiti. Zapravo, Excel vam omogućuje korištenje ove metode izračuna kada je to potrebno.

Na primjer, u jedinicama za proizvodnju energije, poput vjetrenjača, morate izračunati recikliranu izlaznu energiju iz dijela ulazne energije. Alternativno, u kemijskom postrojenju, inženjeri koriste izlaznu osovinu kao ulaz za isti ili drugi kotao ili procesnu posudu. Takvi inženjerski izračuni visoke razine doista zahtijevaju kružne reference za iterativne izračune.

Kada vidite pogrešku kružne reference u Excelu?

Jasno govoreći, skočni prozor kružne reference nije poruka o pogrešci. To je način na koji vas Excel obavještava da je naišao na kružnu referencu u jednoj ili više formula i da to može utjecati na njegov algoritam izračuna i izvedbu.

Kako biste izbjegli bilo kakvu štetu uzrokovanu kružnim referencama u Excel formulama, morate znati kada se pojavljuju, a kada ne. U nastavku pronađite primjere:

• U bilo kojem otvorenom radnom listu ili radnoj knjizi stvarate formulu i prvi put upisujete kružnu referencu.
• Vi ili netko uklonio je sve kružne reference iz Excel datoteke i spremio je. Sada ponovno vi ili drugi korisnik unesete kružnu referencu u istu datoteku.
• Zatvorili ste sve otvorene radne knjige. Sada otvorite drugu radnu knjigu i zatim upišite formulu koja sadrži kružno referenciranje.

Kada vidite pogrešku kružne reference u Excelu

• Upravo ste otvorili radnu knjigu koja je prethodno sadržavala jednu ili više kružnih referenci. Vidjet ćete upozorenje.
• Nakon što vam Excel jednom prikaže poruku upozorenja o kružnim referencama i vi kliknete U redu , više neće prikazivati ​​isti skočni prozor u istoj sesiji čak i ako nastavite stvarati više formula s kružnim referencama.

Stoga morate pažljivo potražiti gore navedene slučajeve kako biste uhvatili kružna referentna upozorenja i riješili ih ako ne tražite iterativne izračune.

Vrste kružnih referenci u Excelu

Ako želite naučiti kako pronaći kružne reference u Excelu, morate razumjeti njihove vrste. Zato što pronalaženje i uklanjanje kružnih referenci u Excelu nije automatiziran zadatak. U nastavku pronađite uobičajene vrste ove pogreške:

Primjer upotrebe kružne reference

• Namjeravane kružne reference pojavljuju se kada ih namjerno koristite u svrhe iterativnog izračuna. Na primjer, trebate unijeti vremensku oznaku u drugu ćeliju kada netko unese vrijednost u referenciranu ćeliju pomoću nepostojanih i logičkih funkcija kao što su IF, OR, SUMIF itd.

Nenamjerna kružna referenca u Excelu

• Nenamjerno kružno referenciranje kada pokušate brže raditi na Excel formuli i greškom upućujete na ćeliju koja sadrži formulu. Na primjer, morate primijeniti funkciju SUM iz ćelije A1 u A9 i dohvatiti rezultat u ćeliji A10 . Ali greškom upisujete formulu ovako u ćeliju A10:

=SUM(A2:A10)

• Skrivene kružne reference su one koje ne prikazuju nikakve poruke upozorenja kada stvorite formulu ili otvorite radni list po prvi put. To se događa jer je netko dopustio iterativno izračunavanje na Excel datoteci.

Primjer neizravne kružne reference

• Neizravne kružne reference također su popularne i pojavljuju se kada stvorite novu formulu u bilo kojoj ćeliji, na primjer, ćeliji A1 , a ta nova formula koristi drugu funkciju u drugoj ćeliji, poput ćelije B1 , koja već koristi ćeliju A1 kao referencu .

Sada kada ste razumjeli osnove kružnih referenci u Excelu, saznajmo u nastavku kako pronaći kružne reference u Excelu:

Kako pronaći kružne reference u Excelu: iz statusne trake

Pod uvjetom da je značajka iterativnog izračuna deaktivirana, možete vidjeti sljedeće obavijesti o kružnim referencama na statusnoj traci programa Excel:

• Kružne reference: C3  (ili bilo koja druga referenca ćelije) kada ste na radnom listu koji sadrži kružnu formulu u radnoj knjizi.

Prikaz kružnih referenci u statusnoj traci programa Excel

• Kružne reference vidjet ćete samo ako ste na radnom listu koji ih ne sadrži, ali radna knjiga ima nekoliko ovih kružnih formula.

Kako pronaći kružne reference u Excelu: korištenje provjere pogrešaka

To je najbolji način za provjeru kružnih referenci u radnoj knjizi ili radnom listu. Ovo su koraci koje morate znati:

• Otvorite radni list za koji sumnjate da sadrži kružne formule.
• Pritisnite izbornik Formule na vrpci programa Excel.

Koristite provjeru pogrešaka za Kako pronaći kružne reference u Excelu

• U grupi naredbi Revizija formule kliknite padajući popis Provjera pogrešaka  .
• Zadržite pokazivač iznad opcije Kružne reference da biste vidjeli sve dostupne instance na svim radnim listovima.
• Odaberite svaku instancu i Excel će automatski odrediti te ćelije.

Kako pronaći kružne reference u Excelu: iz praćenja formula

Ako ne smatrate da je metoda provjere pogrešaka dovoljno učinkovita za otkrivanje velikog broja formula s kružnim referencama, možete isprobati ove korake za korištenje metode praćenja formule:

• Odaberite bilo koju referenciranu ćeliju ili ćeliju koja sadrži formulu.
• Idite na karticu Formule na izborniku vrpce programa Excel, a zatim kliknite Praćenje prethodnika .
• Excel će označiti ćelije koje utječu na rezultat formule na trenutnoj ćeliji strelicama.

Koristite praćenje formule za Kako pronaći kružne reference u Excelu

• Također možete kliknuti Trace Dependents da otkrijete ćelije na koje utječe vrijednost odabrane reference ćelije.
• Ako Excel ne pokaže nikakve pogreške nakon što kliknete gornje naredbe, na vašem radnom listu nema kružnih referenci.
• Ponovite korake na svim radnim listovima da provjerite cijelu radnu knjigu.

Ova je metoda osobito važna kada su značajke iterativnog izračuna aktivne na Excel radnom listu.

Kako pronaći kružne reference u Excelu: korištenje Excel opcija

• Kliknite karticu Datoteka na vrpci programa Excel, a zatim otvorite mogućnosti programa Excel na bočnoj traci koja se prikazuje.
• U dijaloškom okviru Mogućnosti programa Excel odaberite izbornik Formule na bočnoj traci, a zatim poništite potvrdni okvir za Omogući iterativni izračun .

Kako deaktivirati iterativni izračun u Excelu

• Pritisnite OK za spremanje promjena.
• Sada će vam radna knjiga ili radni list automatski prikazati upozorenje o kružnim referencama.
• Ako se ne prikaže odmah, spremite radnu knjigu, zatvorite je i ponovno otvorite da biste vidjeli upozorenje.
• Nakon što vidite upozorenje, provjerite statusnu traku programa Excel da biste znali koja je ćelija u kružnoj referenci.

Kako razriješiti kružne reference u Excelu

Siguran način ispravljanja jedne ili više kružnih referentnih pogrešaka u Excelu jest ručno uređivanje svake formule koja ih sadrži. Na taj način možete razriješiti i slučajne i neizravne kružne reference u Excelu.

Kako razriješiti kružne reference u Excelu

Još jedan jednostavan način je ukloniti formulu iz trenutne ćelije i smjestiti je negdje drugdje unutar istog radnog lista.

Kako pronaći kružne reference u Google tablicama (bonus metoda)

Kao i Excel, Google tablice ne dopuštaju nikakve kružne reference u jednoj ili više formula ako niste aktivirali značajku iterativnog izračuna. Stoga će Google tablice odmah prikazati #REF! pogreška na ćeliji koja sadrži kružnu formulu.

Google tablice Kako pronaći kružne reference

Stoga možete jednostavno odabrati sve ćelije koje sadrže #REF! upozorenje za pronalaženje i rješavanje kružnih referenci na Google tablicama.

Excel Circular References: Završne riječi

Dakle, sada znate sve tajne kako pronaći kružne reference u Excelu. Upotrijebite metodu koja vam se sviđa i odbacite sve kružne reference kako biste bili sigurni da vas neće zbuniti nepouzdani podaci iz Excel izvješća ili matematičke nadzorne ploče.

Sljedeće,  najbolji Excel predlošci kalendara za bilo koju godinu  i  najbolji Excel proračunski predlošci .