Eno najpogostejših opozoril o napakah, s katerimi se uporabniki srečujejo v Excelu, je »Krožna referenca«. Na tisoče uporabnikov ima enako težavo in se pojavi, ko se formula neposredno ali posredno sklicuje nazaj na lastno celico, kar povzroči neskončno zanko izračunov.
V celicah B1 in B2 imate na primer dve vrednosti. Ko se formula =B1+B2 vnese v B2, ustvari krožno referenco; formula v B2 se večkrat znova izračuna, ker se je vrednost B2 vsakič, ko izračuna, spremenila.
Večina krožnih sklicevanj je nenamernih napak; Excel vas bo na to opozoril. Vendar pa obstajajo tudi predvidene krožne reference, ki se uporabljajo za iterativne izračune. Nenamerne krožne reference v vašem delovnem listu lahko povzročijo napačen izračun formule.
Zato bomo v tem članku razložili vse, kar morate vedeti o krožnih sklicah, ter kako najti, popraviti, odstraniti in uporabiti krožne reference v Excelu.
Kako najti in ravnati s krožnimi referencami v Excelu
Pri delu z Excelom včasih naletimo na napake krožnih referenc, ki se zgodijo, ko vnesete formulo, ki vključuje celico, v kateri je vaša formula. V bistvu se to zgodi, ko se vaša formula poskuša izračunati sama.
V celici A1:A4 imate na primer stolpec številk in v celici A5 uporabljate funkcijo SUM (=SUM(A1:A5)). Celica A5 se neposredno nanaša na svojo lastno celico, kar je narobe. Tako boste prejeli naslednje opozorilo o krožni referenci:
Ko prejmete zgornje opozorilno sporočilo, lahko kliknete gumb »Pomoč«, če želite izvedeti več o napaki, ali zaprete okno s sporočilom o napaki, tako da kliknete gumb »V redu« ali »X« in kot rezultat dobite »0«.
Včasih lahko krožne referenčne zanke povzročijo, da se vaš izračun zruši ali upočasni delovanje delovnega lista. Krožno sklicevanje lahko vodi tudi do številnih drugih težav, ki ne bodo takoj očitne. Zato se je tem najbolje izogibati.
Neposredne in posredne krožne reference
Okrožne reference lahko razvrstimo v dve vrsti: neposredne krožne reference in posredne krožne reference.
Neposredna referenca
Neposredna krožna referenca je precej preprosta. Opozorilo za neposredno krožno referenco se prikaže, ko se formula neposredno sklicuje nazaj na svojo celico.
V spodnjem primeru se formula v celici A2 neposredno nanaša na njeno lastno celico (A2).
Ko se prikaže opozorilno sporočilo, lahko kliknete »V redu«, vendar bo rezultat le »0«.
Posredno krožno sklicevanje
Do posredne krožne sklice v Excelu pride, ko se vrednost v formuli sklicuje nazaj na lastno celico, vendar ne neposredno. Z drugimi besedami, krožno referenco lahko tvorita dve celici, ki se sklicujeta druga na drugo.
Razložimo s tem preprostim primerom.
Zdaj se vrednost začne z A1, ki ima vrednost 20.
Nato se celica C3 nanaša na celico A1.
Nato se celica A5 nanaša na celico C3.
Zdaj zamenjajte vrednost 20 v celici A1 s formulo, kot je prikazano spodaj. Vsaka druga celica je odvisna od celice A1. Ko uporabite sklic katere koli druge prejšnje celice formule v A1, bo to povzročilo opozorilo o krožni sklici. Ker se formula v A1 nanaša na celico A5, ki se nanaša na C3, celica C3 pa se nanaša nazaj na A1, od tod krožna referenca.
Ko kliknete »V redu«, to povzroči vrednost 0 v celici A1 in Excel ustvari povezano vrstico, ki prikazuje predhodne in odvisne od sledenja, kot je prikazano spodaj. To funkcijo lahko uporabimo za enostavno iskanje in popravljanje/odstranjevanje krožnih sklicevanj.
Kako omogočiti / onemogočiti krožne reference v Excelu
Iterativni izračuni so privzeto izklopljeni (onemogočeni) v Excelu. Iterativni izračuni so ponavljajoči se izračuni, dokler ne izpolnijo določenega pogoja. Ko je onemogočen, Excel prikaže sporočilo o krožni referenci in kot rezultat vrne 0.
Vendar pa so včasih za izračun zanke potrebne krožne reference. Če želite uporabiti krožno sklicevanje, morate omogočiti iterativne izračune v Excelu, kar vam bo omogočilo izvajanje izračunov. Zdaj pa naj vam pokažemo, kako lahko omogočite ali onemogočite iterativne izračune.
V Excel 2010, Excel 2013, Excel 2016, Excel 2019 in Microsoft 365 pojdite na zavihek »Datoteka« v zgornjem levem kotu Excela in nato v levem podoknu kliknite »Možnosti«.
V oknu z možnostmi Excel pojdite na zavihek »Formula« in potrdite polje »Omogoči iterativni izračun« v razdelku »Možnosti izračuna«. Nato kliknite »V redu«, da shranite spremembe.
To bo omogočilo iterativni izračun in tako omogočilo krožno sklicevanje.
Če želite to doseči v prejšnjih različicah Excela, sledite tem korakom:
- V Excelu 2007 kliknite gumb Office > Excelove možnosti > Formule > Območje iteracije.
- V Excelu 2003 in starejših različicah morate iti v Meni > Orodja > Možnosti > zavihek Izračun.
Največje število ponovitev in največje število parametrov sprememb
Ko omogočite iterativne izračune, lahko nadzirate iterativne izračune, tako da določite dve možnosti, ki sta na voljo v razdelku Omogoči iterativni izračun, kot je prikazano na spodnjem posnetku zaslona.
- Največje število ponovitev – Ta številka določa, kolikokrat naj se formula ponovno izračuna, preden vam da končni rezultat. Privzeta vrednost je 100. Če jo spremenite v '50', bo Excel 50-krat ponovil izračune, preden vam bo dal končni rezultat. Ne pozabite, da večje kot je število ponovitev, več virov in časa je potrebnih za izračun.
- Največja sprememba – Določa največjo spremembo med rezultati izračuna. Ta vrednost določa natančnost rezultata. Manjše kot je število, bolj natančen bo rezultat in dlje traja izračun delovnega lista.
Če je možnost iterativnih izračunov omogočena, ne boste prejeli nobenega opozorila, ko bo na vašem delovnem listu krožna sklic. Interaktivni izračun omogočite le, kadar je to nujno potrebno.
Poiščite krožno referenco v Excelu
Recimo, da imate velik nabor podatkov in ste prejeli opozorilo o krožni referenci, še vedno boste morali ugotoviti, kje (v kateri celici) je prišlo do napake, da jo odpravite. Če želite poiskati krožne reference v Excelu, sledite tem korakom:
Uporaba orodja za preverjanje napak
Najprej odprite delovni list, kjer se je zgodila krožna referenca. Pojdite na zavihek »Formula«, kliknite puščico poleg orodja »Preverjanje napak«. Nato samo premaknite kazalec nad možnost »Krožne reference«, Excel vam bo pokazal seznam vseh celic, ki so vključene v krožno sklicevanje, kot je prikazano spodaj.
Kliknite kateri koli naslov celice, ki ga želite na seznamu, in preusmeril vas bo na naslov te celice, da rešite težavo.
Uporaba vrstice stanja
Krožno sklicevanje lahko najdete tudi v vrstici stanja. V Excelovi vrstici stanja vam bo prikazal najnovejši naslov celice s krožnim sklicevanjem, na primer »Krožne reference: B6« (glejte spodnji posnetek zaslona).
Pri ravnanju s krožnimi referencami morate vedeti nekaj stvari:
- Vrstica stanja ne bo prikazala naslova celice krožne reference, ko je omogočena možnost Iterativni izračun, zato jo morate onemogočiti, preden začnete iskati krožne reference v delovnem zvezku.
- Če krožne sklice ni mogoče najti na aktivnem listu, se v vrstici stanja prikažejo samo »Krožne reference« brez naslova celice.
- Poziv za krožno referenco boste prejeli samo enkrat in ko kliknete »V redu«, naslednjič ne bo več prikazan.
- Če ima vaš delovni zvezek krožne reference, vam bo prikazal poziv vsakič, ko ga odprete, dokler ne razrešite krožne sklice ali dokler ne vklopite iterativnega izračuna.
Odstranite krožno sklicevanje v Excelu
Iskanje krožnih referenc je enostavno, popravljanje pa ni tako preprosto. Na žalost v Excelu ni možnosti, ki bi vam omogočila, da odstranite vse krožne reference naenkrat.
Če želite popraviti krožne reference, morate poiskati vsako krožno referenco posebej in jo poskusiti spremeniti, v celoti odstraniti krožno formulo ali jo zamenjati z drugo.
Včasih v preprostih formulah vse, kar morate storiti, je, da ponovno prilagodite parametre formule, tako da se ne nanaša nazaj na sebe. Na primer, spremenite formulo v B6 v =SUM(B1:B5)*A5 (spremenite B6 v B5).
Rezultat izračuna bo vrnil kot "756".
V primerih, ko je Excelovo krožno sklicevanje težko najti, lahko uporabite funkcije Precedens Trace in Trace Dependents, da ga izsledite nazaj do vira in ga rešite enega za drugim. Puščica prikazuje, na katere celice vpliva aktivna celica.
Obstajata dve metodi sledenja, ki vam lahko pomagata izbrisati krožne reference s prikazom razmerij med formulami in celicami.
Za dostop do metod sledenja pojdite na zavihek »Formule« in nato v skupini Revizija formule kliknite »Precedensi sledenja« ali »Odvisni od sledenja«.
Precedens v sledovih
Ko izberete to možnost, sledi celicam, ki vplivajo na vrednost aktivne celice. Nariše modro črto, ki označuje, katere celice vplivajo na trenutno celico. Bližnjična tipka za uporabo precedensov sledenja je Alt + T U T.
V spodnjem primeru modra puščica prikazuje celice, ki vplivajo na vrednost B6, so B1:B6 in A5. Kot lahko vidite spodaj, je celica B6 tudi del formule, zaradi česar je krožna referenca in povzroči, da formula vrne '0' kot rezultat.
To je mogoče enostavno popraviti tako, da B6 zamenjate z B5 v argumentu SUM: =SUM(B1:B5).
Odvisne osebe za sledenje
Funkcija odvisnosti od sledenja sledi celicam, ki so odvisne od izbrane celice. Ta funkcija nariše modro črto, ki označuje, na katere celice vpliva izbrana celica. To pomeni, da prikaže, katere celice vsebujejo formule, ki se sklicujejo na aktivno celico. Bližnjična tipka za uporabo odvisnih je Alt + T U D.
V naslednjem primeru na celico D3 vpliva B4. Njegova vrednost je odvisna od vrednosti B4, ki daje rezultate. Zato, odvisno od sledenja, nariše modro črto od B4 do D3, kar kaže, da je D3 odvisen od B4.
Namerna uporaba krožnih sklicevanj v Excelu
Namerna uporaba krožnih sklicevanj ni priporočljiva, vendar so morda nekateri redki primeri, ko potrebujete krožno referenco, da lahko dobite želeni rezultat.
Naj to razložimo z uporabo primera.
Za začetek omogočite »Iterativno izračun« v delovnem zvezku Excel. Ko omogočite iterativno izračun, lahko začnete uporabljati krožne reference v svojo korist.
Predpostavimo, da kupujete hišo in želite svojemu agentu dati 2 % provizije na skupne stroške hiše. Skupni stroški bodo izračunani v celici B6, odstotek provizije (provizija agenta) pa se izračuna v B4. Provizija se izračuna od skupnih stroškov, skupni strošek pa vključuje provizijo. Ker sta celici B4 in B6 odvisni druga od druge, ustvarita krožno referenco.
Vnesite formulo za izračun skupnih stroškov v celico B6:
=SUM(B1:B4)
Ker skupni strošek vključuje provizijo agenta, smo v zgornjo formulo vključili B4.
Za izračun provizije agenta v višini 2 % vstavite to formulo v B4:
=B6*2%Zdaj je formula v celici B4 odvisna od vrednosti B6 za izračun 2 % celotne provizije, formula v B6 pa je odvisna od B4 za izračun skupnih stroškov (vključno s provizijo agenta), od tod krožna referenca.
Če je iterativni izračun omogočen, vam Excel v rezultatu ne bo dal opozorila ali 0. Namesto tega bo rezultat celic B6 in B4 izračunan, kot je prikazano zgoraj.
Možnost iterativnih izračunov je običajno privzeto onemogočena. Če ga niste vklopili in ko vnesete formulo v B4, ki bo ustvarila krožno referenco. Excel bo izdal opozorilo in ko kliknete »V redu«, se prikaže puščica sledilnika.
to je to. To je bilo vse, kar morate vedeti o krožnih referencah v Excelu.