Pri delu s Excelovimi formulami boste morda kdaj videli naslednji opozorilni poziv.
Ta poziv vam pove, da je na vašem delovnem listu krožna referenca, kar lahko vodi do napačnega izračuna po formulah. Prav tako vas prosi, da obravnavate to krožno referenčno vprašanje in ga razvrstite.
V tej vadnici bom zajel vse, kar morate vedeti o krožnih referencah, in tudi kako najti in odstraniti krožne reference v Excelu.
Pa začnimo!
Kaj je krožna referenca v Excelu?
Preprosto povedano, krožna referenca se zgodi, ko imate na koncu formulo v celici - ki sama uporablja celico (v katero je vnesena) za izračun.
Naj to poskusim razložiti s preprostim primerom.
Recimo, da imate nabor podatkov v celici A1: A5 in uporabite naslednjo formulo v celici A6:
= SUM (A1: A6)
To vas bo opozorilo na krožno referenco.
To je zato, ker želite sešteti vrednosti v celici A1: A6, rezultat pa mora biti v celici A6.
To ustvari zanko, saj Excel samo dodaja novo vrednost v celico A6, ki se nenehno spreminja (torej krožna referenčna zanka).
Kako najti krožne reference v Excelu?
Čeprav je opozorilo o krožnem sklicu dovolj prijazno, da vam pove, da obstaja na vašem delovnem listu, vam ne pove, kje se to dogaja in katere sklice na celice to povzročajo.
Če torej poskušate najti in obravnavati krožne reference na delovnem listu, morate poznati način, kako jih nekako najti.
Spodaj so navedeni koraki za iskanje krožne reference v Excelu:
- Aktivirajte delovni list s krožno referenco
- Kliknite zavihek Formule
- V skupini Urejanje formul kliknite spustno ikono Preverjanje napak (majhna puščica navzdol na desni)
- Premaknite kazalec na možnost Krožne reference. Prikazal vam bo celico, ki ima krožni sklic na delovnem listu
- Kliknite na naslov celice (ki je prikazan) in popeljal vas bo do te celice na delovnem listu.
Ko odpravite težavo, lahko znova sledite istim korakom zgoraj in prikazalo se bo več referenc celic, ki imajo krožno referenco. Če ni nobene, ne boste videli sklica na celico,
Drug hiter in enostaven način za iskanje krožne reference je ogled vrstice stanja. Na levem delu bo prikazano besedilo Circular Reference skupaj z naslovom celice.
Pri delu s krožnimi referencami morate vedeti nekaj stvari:
- Če je omogočen iterativni izračun (obravnavan kasneje v tej vadnici), vrstica stanja ne bo prikazala naslova krožne referenčne celice
- Če krožna referenca ni na aktivnem listu (ampak na drugih listih v istem delovnem zvezku), bo prikazana le krožna referenca in ne naslov celice
- Če enkrat dobite opozorilo za krožno referenco in ga zavrnete, naslednjič poziv ne bo več prikazan.
- Če odprete delovni zvezek s krožno referenco, vam bo pokazal poziv takoj, ko se delovni zvezek odpre.
Kako odstraniti krožno referenco v Excelu?
Ko ugotovite, da so na vašem listu krožne reference, je čas, da jih odstranite (razen če želite, da so tam z razlogom).
Na žalost ni tako preprosto kot pritisniti tipko za brisanje. Ker so te odvisne od formul in je vsaka formula drugačna, morate to analizirati za vsak primer posebej.
Če gre le za to, da referenca celice povzroči napako, jo lahko preprosto popravite tako, da referenco prilagodite.
Toda včasih ni tako preprosto.
Krožno sklicevanje lahko povzroči tudi več celic, ki se med seboj napajajo na številnih ravneh.
Naj vam pokažem primer.
Spodaj je krožna referenca v celici C6, vendar to ni le preprost primer samoreferenciranja. Je na več ravneh, kjer se celice, ki jih uporablja pri izračunih, med seboj sklicujejo.
- Formule v celici A6 so = SUM (A1: A5)+C6
- Formula je celica C1 je = A6*0,1
- Formula v celici C6 je = A6+C1
V zgornjem primeru je rezultat v celici C6 odvisen od vrednosti v celici A6 in C1, ki pa so odvisne od celice C6 (s tem povzroči krožno referenčno napako)
In spet sem izbral zelo preprost primer samo za demo namene. V resnici bi bilo to težko določiti in morda daleč na istem delovnem listu ali celo razpršeno po več delovnih listih.
V takem primeru obstaja en način za identifikacijo celic, ki povzročajo krožno referenco, in njihovo zdravljenje.
To je z uporabo možnosti sledenja predhodnikov.
Spodaj so navedeni koraki za uporabo predhodnikov sledenja za iskanje celic, ki se hranijo s celico s krožno referenco:
- Izberite celico s krožno referenco
- Kliknite zavihek Formule
- Kliknite Trace Precedents
Zgornji koraki bi pokazali modre puščice, ki vam povedo, katere celice se vnesejo v formulo v izbrani celici. Na ta način lahko pregledate formule in celice ter se znebite krožne reference.
Če delate s kompleksnimi finančnimi modeli, je možno, da ti precedensi segajo tudi na več ravneh.
To dobro deluje, če imate vse formule, ki se nanašajo na celice na istem delovnem listu. Če je na več delovnih listih, ta metoda ni učinkovita.
Kako omogočiti/onemogočiti ponavljajoče se izračune v Excelu
Ko imate v celici krožno referenco, najprej dobite opozorilni poziv, kot je prikazano spodaj, in če zaprete to pogovorno okno, vam bo v celici dalo 0.
Ko je krožna referenca, je to neskončna zanka in Excel je noče ujeti. Tako vrne 0.
Toda v nekaterih primerih boste morda želeli, da bo krožna referenca aktivna, in naredite nekaj ponovitev. V takem primeru se namesto neskončne zanke lahko odločite, kolikokrat naj se zanka zažene.
Temu se reče iterativni izračun v Excelu.
Spodaj so navedeni koraki za omogočanje in konfiguriranje iterativnih izračunov v Excelu:
- Kliknite zavihek Datoteka
- Kliknite Možnosti. S tem se odpre pogovorno okno Možnosti programa Excel
- V levem podoknu izberite formulo
- V razdelku Možnosti izračuna potrdite polje »Omogoči iterativni izračun«. Tu lahko določite največje ponovitve in največjo vrednost spremembe
To je to! Zgornji koraki bi omogočili iterativni izračun v Excelu.
Naj na hitro razložim tudi dve možnosti pri ponovnem izračunu:
- Največje ponovitve: To je največje število izračunov, ki jih Excel želi izračunati, preden vam poda končni rezultat. Če torej to določite kot 100, bo Excel zanko izvedel 100 -krat, preden vam bo dal končni rezultat.
- Največja sprememba: To je največja sprememba, ki bi se, če ne bi bila dosežena med ponovitvami, ustavila. Privzeto je vrednost 0,001. Nižja kot je ta vrednost, bolj natančen bo rezultat.
Ne pozabite, da večkrat, ko se ponovitve izvajajo, več časa in sredstev potrebuje Excel za to. Če imate največje število ponovitev, lahko pride do upočasnitve ali sesutja Excela.
Opomba: Ko so ponovljeni izračuni omogočeni, vam Excel ne bo prikazal opozorila o krožnem sklicu in ga bo zdaj prikazal tudi v vrstici stanja.Namerno uporaba krožnih referenc
V večini primerov bi bila prisotnost krožnih referenc na vašem delovnem listu napaka. In zato vam Excel prikaže poziv, ki pravi - "Poskusite odstraniti ali spremeniti te reference ali premakniti formule v različne celice."
Morda pa obstajajo nekateri posebni primeri, ko potrebujete krožno referenco, da dobite želeni rezultat.
En tak poseben primer, o katerem sem že pisal, je bil, da je časovni žig v celici v celici v Excelu.
Recimo, da želite ustvariti formulo, tako da bo vsak vnos v celico v stolpcu A časovni žig prikazan v stolpcu B (kot je prikazano spodaj):
Čeprav lahko preprosto vstavite časovni žig po spodnji formuli:
= IF (A2 "", IF (B2 "", B2, NOW ()), "")
Težava z zgornjo formulo je, da bi posodobila vse časovne žige takoj, ko se na delovnem listu kaj spremeni ali če se delovni list znova odpre (ker je formula NOW nestanovitna)
Če želite odpraviti to težavo, lahko uporabite krožno referenčno metodo. Uporabite isto formulo, vendar omogočite iterativni izračun.
Obstajajo tudi drugi primeri, ko je zaželena možnost uporabe krožne reference (en primer lahko najdete tukaj).
Opomba: Čeprav lahko obstajajo primeri, ko lahko uporabite krožno referenco, se mi zdi najbolje, da se ji izognemo. Krožne reference lahko vplivajo tudi na uspešnost vašega delovnega zvezka in ga upočasnijo. V redkih primerih, ko ga potrebujete, vedno raje uporabljam kode VBA za opravljanje dela.Upam, da vam je bila ta vadnica koristna!
Druge vaje za Excel, ki vam bodo morda koristne:
- #REF! Napaka v Excelu; Kako odpraviti referenčno napako!
- Ravnanje z napakami Excel VBA
- Uporabite IFERROR z VLOOKUP -om, da se znebite #N/A napak
- Kako se sklicevati na drug list ali delovni zvezek v Excelu (s primeri)
- Absolutne, relativne in mešane sklice na celice v Excelu
