Jedno od najčešćih upozorenja o pogreškama s kojima se korisnici susreću u Excelu je "Kružna referenca". Tisuće korisnika imaju isti problem, a javlja se kada se formula izravno ili neizravno poziva na vlastitu ćeliju, uzrokujući beskonačnu petlju izračuna.
Na primjer, imate dvije vrijednosti u ćelijama B1 i B2. Kada se formula =B1+B2 unese u B2, stvara se kružna referenca; formula u B2 se opetovano preračunava jer se svaki put kada izračunava vrijednost B2 promijenila.
Većina kružnih referenci su nenamjerne pogreške; Excel će vas upozoriti na njih. Međutim, postoje i predviđene kružne reference koje se koriste za iterativne izračune. Nenamjerne kružne reference u vašem radnom listu mogu uzrokovati netočan izračun vaše formule.
Stoga ćemo u ovom članku objasniti sve što trebate znati o kružnim referencama, kao i kako pronaći, popraviti, ukloniti i koristiti kružne reference u Excelu.
Kako pronaći i rukovati kružnim referencama u Excelu
Kada radimo s Excelom, ponekad nailazimo na pogreške kružne reference koje se događaju kada unesete formulu koja uključuje ćeliju u kojoj se nalazi vaša formula. U osnovi, to se događa kada vaša formula pokušava sama izračunati.
Na primjer, imate stupac brojeva u ćeliji A1:A4 i koristite funkciju SUM (=SUM(A1:A5)) u ćeliji A5. Ćelija A5 izravno se odnosi na vlastitu ćeliju, što je pogrešno. Stoga ćete dobiti sljedeće kružno upozorenje:
Nakon što dobijete gornju poruku upozorenja, možete kliknuti gumb "Pomoć" da biste saznali više o pogrešci ili zatvorite prozor s porukom o pogrešci tako da kliknete gumb "U redu" ili "X" i dobijete "0" kao rezultat.
Ponekad kružne referentne petlje mogu uzrokovati pad vašeg izračuna ili usporiti izvedbu vašeg radnog lista. Kružna referenca također može dovesti do brojnih drugih problema, koji neće biti vidljivi odmah. Stoga ih je najbolje izbjegavati.
Izravne i neizravne kružne reference
Kružne reference mogu se kategorizirati u dvije vrste: izravne kružne reference i neizravne kružne reference.
Izravna referenca
Izravna kružna referenca prilično je jednostavna. Izravna kružna referenca s upozorenjem pojavljuje se kada se formula izravno upućuje na svoju ćeliju.
U donjem primjeru, formula u ćeliji A2 izravno se odnosi na vlastitu ćeliju (A2).
Kada se pojavi poruka upozorenja, možete kliknuti na 'U redu', ali će rezultirati samo s '0'.
Neizravna kružna referenca
Neizravna kružna referenca u Excelu događa se kada se vrijednost u formuli odnosi natrag na vlastitu ćeliju, ali ne izravno. Drugim riječima, kružnu referencu mogu oblikovati dvije ćelije koje se međusobno upućuju.
Objasnimo ovim jednostavnim primjerom.
Sada vrijednost počinje od A1 koji ima vrijednost 20.
Zatim, ćelija C3 odnosi se na ćeliju A1.
Zatim se ćelija A5 odnosi na ćeliju C3.
Sada zamijenite vrijednost 20 u ćeliji A1 formulom kao što je prikazano u nastavku. Svaka druga stanica ovisi o stanici A1. Kada koristite referencu bilo koje druge prethodne ćelije formule u A1, to će uzrokovati kružno upozorenje o referenci. Jer, formula u A1 se odnosi na ćeliju A5, koja se odnosi na C3, a ćelija C3 se odnosi na A1, dakle kružna referenca.
Kada kliknete 'U redu', to rezultira vrijednošću od 0 u ćeliji A1 i Excel stvara povezanu liniju koja prikazuje prethodnike i zavisne tragove kao što je prikazano u nastavku. Ovu značajku možemo koristiti za jednostavno pronalaženje i popravljanje/uklanjanje kružnih referenci.
Kako omogućiti/onemogućiti kružne reference u Excelu
Prema zadanim postavkama, iterativni izračuni su isključeni (onemogućeni) u Excelu. Iterativni izračuni su izračuni koji se ponavljaju sve dok ne ispuni određeni uvjet. Kada je onemogućen, Excel prikazuje poruku kružne reference i vraća 0 kao rezultat.
Međutim, ponekad su potrebne kružne reference za izračunavanje petlje. Da biste koristili kružnu referencu, morate omogućiti iterativne izračune u svom Excelu i to će vam omogućiti da izvršite svoje izračune. Sada ćemo vam pokazati kako možete omogućiti ili onemogućiti iterativne izračune.
U Excel 2010, Excel 2013, Excel 2016, Excel 2019 i Microsoft 365 idite na karticu "Datoteka" u gornjem lijevom kutu Excela, a zatim kliknite "Opcije" u lijevom oknu.
U prozoru Mogućnosti programa Excel idite na karticu "Formula" i označite potvrdni okvir "Omogući iterativni izračun" u odjeljku "Opcije izračuna". Zatim kliknite "U redu" da biste spremili promjene.
To će omogućiti iterativni izračun i time omogućiti kružnu referencu.
Da biste to postigli u prethodnim verzijama Excela, slijedite ove korake:
- U programu Excel 2007 kliknite na gumb Office > Excel opcije > Formule > Područje iteracije.
- U programu Excel 2003 i starijim verzijama trebate ići na Izbornik > Alati > Opcije > kartica Izračun.
Maksimalne iteracije i maksimalni parametri promjene
Nakon što omogućite iterativne izračune, možete kontrolirati iterativne izračune tako da navedete dvije dostupne opcije u odjeljku Omogući iterativni izračun kao što je prikazano na snimci zaslona u nastavku.
- Maksimalni broj iteracija – Ovaj broj određuje koliko puta se formula treba ponovno izračunati prije nego što dobijete konačni rezultat. Zadana vrijednost je 100. Ako je promijenite u '50', Excel će ponoviti izračune 50 puta prije nego što vam da konačni rezultat. Zapamtite da što je veći broj iteracija, to je više resursa i vremena potrebno za izračun.
- Maksimalna promjena – Određuje maksimalnu promjenu između rezultata izračuna. Ova vrijednost određuje točnost rezultata. Što je broj manji, to bi rezultat bio točniji i duže je potrebno za izračunavanje radnog lista.
Ako je omogućena opcija iterativnih izračuna, nećete dobiti nikakvo upozorenje kad god postoji kružna referenca u vašem radnom listu. Omogućite interaktivni izračun samo kada je to apsolutno neophodno.
Pronađite kružnu referencu u Excelu
Pretpostavimo da imate veliki skup podataka i dobili ste upozorenje o kružnoj referenci, i dalje ćete morati otkriti gdje (u kojoj ćeliji) se pogreška dogodila kako biste je popravili. Da biste pronašli kružne reference u Excelu, slijedite ove korake:
Korištenje alata za provjeru grešaka
Prvo otvorite radni list na kojem se dogodila kružna referenca. Idite na karticu "Formula", kliknite na strelicu pored alata "Provjera pogrešaka". Zatim samo zadržite pokazivač iznad opcije "Kružne reference", Excel će vam pokazati popis svih ćelija koje su uključene u kružnu referencu kao što je prikazano u nastavku.
Kliknite na koju god adresu ćelije želite na popisu i odvest će vas do te adrese ćelije da riješite problem.
Korištenje statusne trake
Također možete pronaći kružnu referencu na statusnoj traci. Na Excelovoj statusnoj traci prikazat će vam najnoviju adresu ćelije s kružnom referencom, kao što je "Kružne reference: B6" (pogledajte donju snimku zaslona).
Postoje određene stvari koje biste trebali znati kada rukujete kružnim referencama:
- Statusna traka neće prikazati adresu ćelije kružne reference kada je omogućena opcija Iterativno izračunavanje, pa je morate onemogućiti prije nego što počnete tražiti kružne reference u radnoj knjizi.
- U slučaju da kružna referenca nije pronađena u aktivnom listu, statusna traka prikazuje samo 'Kružne reference' bez adrese ćelije.
- Primit ćete samo jednom kružnu referencu i nakon što kliknete "U redu", neće se ponovno prikazati sljedeći put.
- Ako vaša radna knjiga ima kružne reference, pokazat će vam upit svaki put kada je otvorite dok ne riješite kružnu referencu ili dok ne uključite iterativni izračun.
Uklonite kružnu referencu u Excelu
Pronalaženje kružnih referenci je jednostavno, ali popravljanje nije tako jednostavno. Nažalost, u Excelu ne postoji opcija koja će vam omogućiti da uklonite sve kružne reference odjednom.
Da biste popravili kružne reference, morate pronaći svaku kružnu referencu pojedinačno i pokušati je izmijeniti, potpuno ukloniti kružnu formulu ili je zamijeniti drugom.
Ponekad, u jednostavnim formulama, sve što trebate učiniti je ponovno podesiti parametre formule tako da se ne odnosi na sebe. Na primjer, promijenite formulu u B6 u =SUM(B1:B5)*A5 (mijenjajući B6 u B5).
Vratit će rezultat izračuna kao "756".
U slučajevima kada je teško pronaći kružnu referencu programa Excel, možete koristiti značajke Trace Precedents i Trace Dependents da biste je pratili do izvora i riješili je jedan po jedan. Strelica pokazuje na koje stanice utječe aktivna stanica.
Postoje dvije metode praćenja koje vam mogu pomoći da izbrišete kružne reference pokazujući odnose između formula i ćelija.
Da biste pristupili metodama praćenja, idite na karticu "Formule", a zatim kliknite "Precedenti praćenja" ili "Ovisni o tragovima" u grupi Revizija formule.
Presedani u tragovima
Kada odaberete ovu opciju, ona prati ćelije koje utječu na vrijednost aktivne ćelije. Povlači plavu liniju koja označava koje ćelije utječu na trenutnu ćeliju. Tipka prečaca za korištenje presedana praćenja je Alt + T U T.
U donjem primjeru, plava strelica pokazuje ćelije koje utječu na vrijednost B6 su B1:B6 i A5. Kao što možete vidjeti u nastavku, ćelija B6 također je dio formule, što je čini kružnom referencom i uzrokuje da formula vraća '0' kao rezultat.
To se lako može popraviti zamjenom B6 s B5 u argumentu SUM: =SUM(B1:B5).
Ovisnici o tragovima
Značajka ovisnih o tragovima prati ćelije koje ovise o odabranoj ćeliji. Ova značajka crta plavu liniju koja označava na koje ćelije utječe odabrana ćelija. To jest, prikazuje koje ćelije sadrže formule koje upućuju na aktivnu ćeliju. Tipka prečaca za korištenje ovisnih je Alt + T U D.
U sljedećem primjeru na ćeliju D3 utječe B4. Njegova vrijednost ovisi o B4 za postizanje rezultata. Stoga, trag ovisan povlači plavu liniju od B4 do D3, što ukazuje da je D3 ovisan o B4.
Namjerno korištenje kružnih referenci u Excelu
Namjerno korištenje kružnih referenci se ne preporučuje, ali mogu postojati rijetki slučajevi u kojima vam je potrebna kružna referenca kako biste dobili željeni rezultat.
Objasnimo to na primjeru.
Za početak omogućite "Iterativno izračunavanje" u radnoj knjizi programa Excel. Nakon što omogućite iterativno izračunavanje, možete početi koristiti kružne reference u svoju korist.
Pretpostavimo da kupujete kuću i svom agentu želite dati 2% provizije na ukupnu cijenu kuće. Ukupni trošak izračunat će se u ćeliji B6, a postotak provizije (agentska naknada) izračunat će se u ćeliji B4. Provizija se obračunava od ukupnog troška, a ukupni trošak uključuje proviziju. Budući da ćelije B4 i B6 ovise jedna o drugoj, to stvara kružnu referencu.
Unesite formulu za izračun ukupnog troška u ćeliju B6:
=SUM(B1:B4)
Budući da ukupni trošak uključuje agentsku naknadu, uključili smo B4 u gornju formulu.
Da biste izračunali agentsku naknadu od 2%, umetnite ovu formulu u B4:
=B6*2%Sada formula u ćeliji B4 ovisi o vrijednosti B6 za izračun 2% ukupne naknade, a formula u ćeliji B6 ovisi o B4 za izračun ukupnog troška (uključujući naknadu agenta), dakle kružna referenca.
Ako je iterativni izračun omogućen, Excel vam neće dati upozorenje ili 0 u rezultatu. Umjesto toga, rezultat ćelija B6 i B4 će se izračunati kao što je prikazano gore.
Opcija iterativnih izračuna obično je onemogućena prema zadanim postavkama. Ako ga niste uključili i kada unesete formulu u B4 koja će stvoriti kružnu referencu. Excel će izdati upozorenje i kada kliknete "U redu", prikazat će se strelica za praćenje.
To je to. Ovo je bilo sve što trebate znati o kružnim referencama u Excelu.