Tijekom rada s Excelovim formulama možda ćete ponekad vidjeti sljedeći upit upozorenja.
Ovaj upit govori vam da na vašem radnom listu postoji kružna referenca i to može dovesti do pogrešnog izračuna prema formulama. Također vas traži da riješite ovo pitanje kružne reference i razvrstite ga.
U ovom ću vodiču pokriti sve što trebate znati o kružnoj referenci, a također kako pronaći i ukloniti kružne reference u Excelu.
Pa krenimo!
Što je kružna referenca u Excelu?
Jednostavnim riječima, kružna referenca događa se kada na kraju imate formulu u ćeliji - koja sama po sebi koristi ćeliju (u koju je unesena) za izračun.
Dopustite mi da to pokušam objasniti jednostavnim primjerom.
Pretpostavimo da imate skup podataka u ćeliji A1: A5 i koristite sljedeću formulu u ćeliji A6:
= ZBIR (A1: A6)
Ovo će vas upozoriti na kružnu referencu.
To je zato što želite zbrojiti vrijednosti u ćeliji A1: A6, a rezultat bi trebao biti u ćeliji A6.
Ovo stvara petlju jer Excel samo nastavlja dodavati novu vrijednost u ćeliju A6, koja se stalno mijenja (dakle, kružna referentna petlja).
Kako pronaći kružne reference u Excelu?
Iako je upozorenje o kružnoj referenci dovoljno ljubazno da vam kaže da postoji na vašem radnom listu, ne govori vam gdje se to događa i koje reference ćelija to uzrokuju.
Dakle, ako pokušavate pronaći i rukovati kružnim referencama na radnom listu, morate znati način da ih nekako pronađete.
U nastavku su navedeni koraci za pronalaženje kružne reference u Excelu:
- Aktivirajte radni list koji ima kružnu referencu
- Kliknite karticu Formule
- U grupi Uređivanje formula kliknite padajuću ikonu Provjera pogrešaka (mala strelica usmjerena prema dolje desno)
- Zadržite pokazivač iznad opcije Kružne reference. Pokazat će vam se ćelija koja ima kružnu referencu na radnom listu
- Kliknite na adresu ćelije (koja je prikazana) i ona će vas odvesti do te ćelije na radnom listu.
Nakon što riješite problem, možete ponovno slijediti gore navedene korake i prikazat će se više referenci ćelija koje imaju kružnu referencu. Ako nema, nećete vidjeti referencu ćelije,
Još jedan brz i jednostavan način za pronalaženje kružne reference je pregledavanje statusne trake. Na njegovu lijevom dijelu prikazat će vam se tekst Circular Reference zajedno s adresom ćelije.
Prilikom rada s kružnim referencama morate znati nekoliko stvari:
- U slučaju da je omogućen iterativni izračun (obrađen kasnije u ovom vodiču), statusna traka neće prikazivati adresu kružne referentne ćelije
- U slučaju da kružna referenca nije u aktivnom listu (već na drugim listovima u istoj radnoj knjizi), prikazat će se samo kružna referenca, a ne i adresa ćelije
- U slučaju da jednom dobijete upit za upozorenje kružne reference i odbacite ga, sljedeći put neće se ponovno prikazati.
- Ako otvorite radnu knjigu s kružnom referencom, prikazat će vam se upit čim se radna knjiga otvori.
Kako ukloniti kružnu referencu u Excelu?
Nakon što utvrdite da na vašem listu postoje kružne reference, vrijeme je da ih uklonite (osim ako ne želite da postoje s razlogom).
Nažalost, nije tako jednostavno kao pritisnuti tipku za brisanje. Budući da one ovise o formulama i svaka je formula drugačija, morate to analizirati od slučaja do slučaja.
U slučaju da je riječ samo o tome da referenca ćelije greškom uzrokuje problem, jednostavno je možete ispraviti podešavanjem reference.
Ali ponekad, to nije tako jednostavno.
Kružna referenca također se može uzrokovati na temelju više stanica koje se hrane jedna na drugu na mnogim razinama.
Dopustite mi da vam pokažem primjer.
Ispod se nalazi kružna referenca u ćeliji C6, ali to nije samo jednostavan slučaj samoreferencije. To je više razina gdje se ćelije koje koristi u izračunima međusobno referenciraju.
- Formule u ćeliji A6 su = SUM (A1: A5)+C6
- Formula je ćelija C1 je = A6*0,1
- Formula u ćeliji C6 je = A6+C1
U gornjem primjeru rezultat u ćeliji C6 ovisi o vrijednostima u ćeliji A6 i C1, koje pak ovise o ćeliji C6 (uzrokujući tako pogrešku kružne reference)
I opet, odabrao sam zaista jednostavan primjer samo u demo svrhe. U stvarnosti, to bi moglo biti prilično teško shvatiti, a možda i daleko na istom radnom listu ili čak raštrkano po više radnih listova.
U tom slučaju postoji jedan način da se identificiraju stanice koje uzrokuju kružnu referencu, a zatim ih tretira.
To je pomoću opcije Trace Precedents.
U nastavku su navedeni koraci za korištenje presedana praćenja za pronalaženje stanica koje se hrane stanicom koja ima kružnu referencu:
- Odaberite ćeliju koja ima kružnu referencu
- Kliknite karticu Formule
- Kliknite na Trace Precedents
Gore navedeni koraci pokazat će vam plave strelice koje će vam reći koje se stanice unose u formulu u odabranoj ćeliji. Na taj način možete pregledati formule i ćelije i riješiti se kružne reference.
U slučaju da radite sa složenim financijskim modelima, moguće je da ti presedani također idu duboko u više razina.
Ovo dobro funkcionira ako imate sve formule koje se odnose na ćelije na istom radnom listu. Ako se nalazi na više radnih listova, ova metoda nije učinkovita.
Kako omogućiti/onemogućiti ponavljajuće izračune u Excelu
Kad imate kružnu referencu u ćeliji, prvo ćete dobiti upozorenje kao što je prikazano u nastavku, a ako zatvorite ovaj dijaloški okvir, dobit ćete 0 kao rezultat u ćeliji.
To je zato što kada postoji kružna referenca, to je beskrajna petlja i Excel se ne želi u nju uhvatiti. Dakle, vraća 0.
No u nekim ćete slučajevima možda htjeti da kružna referenca bude aktivna i napraviti nekoliko ponavljanja. U tom slučaju, umjesto beskonačne petlje, možete odlučiti koliko će puta petlja biti pokrenuta.
Ovo se zove iterativni izračun u Excelu.
U nastavku su navedeni koraci za omogućavanje i konfiguriranje iterativnih izračuna u Excelu:
- Kliknite karticu Datoteka
- Pritisnite Opcije. Ovo će otvoriti dijaloški okvir Excel mogućnosti
- Odaberite Formula u lijevom oknu
- U odjeljku Opcije izračuna potvrdite okvir ‘Omogući iterativni izračun’. Ovdje možete odrediti maksimalne iteracije i maksimalnu vrijednost promjene
To je to! Gore navedeni koraci omogućili bi iterativni izračun u Excelu.
Dopustite mi da brzo objasnim dvije opcije u iterativnom izračunu:
- Maksimalne iteracije: Ovo je maksimalni broj puta koliko želite da Excel izračuna prije nego što vam da konačni rezultat. Dakle, ako navedete ovo kao 100, Excel će pokrenuti petlju 100 puta prije nego što vam da konačni rezultat.
- Maksimalna promjena: Ovo je maksimalna promjena, koja bi se, ako se ne postigne između ponavljanja, izračunavanje zaustavilo. Prema zadanim postavkama vrijednost je .001. Što je ova vrijednost niža, rezultat će biti točniji.
Imajte na umu da što više puta ponavljaju iteracije, Excel -u je potrebno više vremena i resursa za to. U slučaju da zadržite maksimalne iteracije visokim, to može dovesti do usporavanja ili rušenja vašeg programa Excel.
Napomena: Kad su omogućeni iterativni izračuni, Excel vam neće prikazati upozorenje kružne reference, već će ga sada prikazati i na statusnoj traci.Namjerno se koristi kružnim referencama
U većini slučajeva prisutnost kružne reference na vašem radnom listu bila bi pogreška. I zato vam Excel prikazuje upit koji kaže - "Pokušajte ukloniti ili promijeniti ove reference ili premjestiti formule u različite ćelije."
No mogli bi postojati neki specifični slučajevi u kojima vam je potrebna kružna referenca kako biste mogli postići željeni rezultat.
Jedan takav specifičan slučaj o kojem sam već pisao dobivanje vremenske oznake u ćeliji u ćeliji u Excelu.
Na primjer, pretpostavimo da želite stvoriti formulu tako da se svaki unos u ćeliju u stupcu A, vremenska oznaka prikazuje u stupcu B (kao što je prikazano u nastavku):
Iako možete jednostavno umetnuti vremensku oznaku pomoću formule u nastavku:
= IF (A2 "", IF (B2 "", B2, NOW ()), "")
Problem s gornjom formulom je taj što će ažurirati sve vremenske oznake čim se izvrši bilo kakva promjena na radnom listu ili ako se radni list ponovno otvori (budući da je formula SADA nestabilna)
Da biste zaobišli ovaj problem, možete upotrijebiti kružnu referentnu metodu. Koristite istu formulu, ali omogućite iterativni izračun.
Postoje i neki drugi slučajevi u kojima je poželjna mogućnost korištenja kružne reference (jedan primjer možete pronaći ovdje).
Napomena: Iako mogu postojati neki slučajevi u kojima možete koristiti kružnu referencu, smatram da je najbolje izbjegavati je koristiti. Kružne reference također mogu utjecati na rad vaše radne knjige i usporiti je. U rijetkim slučajevima kad vam zatreba, uvijek radije koristim VBA kodove za obavljanje posla.Nadam se da vam je ovaj vodič bio koristan!
Ostali vodiči programa Excel koji bi vam mogli biti korisni:
- #REF! Pogreška u Excelu; Kako ispraviti grešku reference!
- Upravljanje pogreškama programa Excel VBA
- Upotrijebite IFERROR s VLOOKUP -om da biste se riješili #N/A grešaka
- Kako uputiti na drugi list ili radnu knjigu u Excelu (s primjerima)
- Apsolutne, relativne i mješovite ćelijske reference u Excelu
