Kako najti krožne reference v Excelu, da se izognete napačnim podatkom

Ste opazili napako krožnega sklica na Excelovem listu in se sprašujete, ali je delovni list pokvarjen? Zaupaj mi! Niste prvi, ki se sooča s to Excelovo napako. Na stotine in tisoče uporabnikov Excela se vsak dan srečuje s krožnimi referenčnimi napakami. Ne skrbi! Tukaj je opisano, kako najti krožne reference v Excelu in jih razrešiti.

Ker ste navdušeni uporabnik Excela, redko vnesete iste podatke v veliko celic. Namesto tega uvozite nabor podatkov iz vira in uporabite sklicevanje na celico za ustvarjanje formul. Tako ustvarite dinamično preglednico.

Ko nekdo spremeni vrednosti celic referenc celic, se delovni list samodejno posodobi. To se imenuje avtomatizacija analize podatkov in analitiki podatkov se radi igrajo s sklici na celice.

Težava pa nastopi, ko namerno ali pomotoma vnesete naslov celice, v kateri trenutno izvajate izračun. Ali pa, ko ustvarjate veliko formul na delovnem listu, se celica, ki vsebuje formulo, posredno pojavi v drugi formuli.

V takih in mnogih drugih podobnih situacijah dobite napako krožnega sklicevanja v Excelu. V nadaljevanju preberite, če želite izvedeti, kaj je krožna referenca, razlogi zanjo, zakaj se ji izogniti in kar je najpomembnejše, kako najti krožno referenco v Excelu, da se za vedno znebite teh anomalij.

Kaj je krožna referenca v Excelu?

Excel naleti na napako krožnega sklicevanja, ko vidi, da se ena ali več funkcij delovnega lista sklicuje nazaj na naslov celice, kjer so bile njihove funkcije ustvarjene.

Morda se vam zdi zmedeno, zato pomislite, kako resno bi lahko bilo za aplikacijo Excel. Skratka, nikoli ne želite dati naslova celice v formulo, kjer jo ustvarjate. Za vizualno razjasnitev si oglejte naslednji primer:

Poskušate pomnožiti 10 iz celice B1 in dati rezultat v samo celico B1 .

Zdaj, če v zgornjem scenariju samo pritisnete OK , bo Excel zaprl napako in prikazal nič kot izračunani rezultat. To je samo preprost primer.

Kaj je krožna referenca v Excelu

Zdaj pa si predstavljajte, da obstaja na stotine formul v delovnem listu za knjigovodstvo, delovnem listu za finančno načrtovanje, plačilni listi zaposlenih, delovnem listu za plačilo prodajalca itd.

Zaupali boste napačnim podatkom, ki jih izračuna Excel zaradi krožne referenčne napake, kar bo posledično vplivalo na vaše poslovne ali poklicne odločitve.

Poleg tega lahko krožno referenciranje močno zmanjša hitrost izračunov v aplikaciji Excel. V hudih primerih lahko sama napaka povzroči izjemno počasno delovanje tudi v računalniku, če je ne odpravite takoj.

Če pa delate na naprednih Excelovih formulah, kjer vaš podatkovni model zahteva vmesne vnose iz krožnega sklica za nadaljevanje z dolgo funkcijo, jo boste morda morali uporabiti. Pravzaprav vam Excel omogoča uporabo te metode izračuna, kadar je to potrebno.

Na primer, v enotah za proizvodnjo energije, kot so mlini na veter, morate reciklirano izhodno energijo izračunati iz delčka vhodne energije. Druga možnost je, da v kemični tovarni inženirji uporabijo izhodno steblo kot vhod za isti ali drug kotel ali procesno posodo. Takšni inženirski izračuni na visoki ravni dejansko zahtevajo krožne reference za iterativne izračune.

Kdaj vidite napako krožnega sklica v Excelu?

Jasno je, da pojavno okno s krožno referenco ni sporočilo o napaki. To je način, s katerim vas Excel obvesti, da je v eni ali več formulah naletel na krožno sklicevanje, kar lahko vpliva na njegov algoritem izračuna in delovanje.

Da bi se izognili kakršni koli škodi zaradi krožnih sklicev v Excelovih formulah, morate vedeti, kdaj se pojavijo in kdaj ne. Spodaj poiščite primere:

• V katerem koli odprtem delovnem listu ali delovnem zvezku ustvarite formulo in prvič vnesete krožni sklic.
• Vi ali nekdo je odstranil vse krožne reference iz Excelove datoteke in jo shranil. Zdaj spet vi ali drug uporabnik v isto datoteko vnesete krožni sklic.
• Zaprli ste vse odprte delovne zvezke. Zdaj odprite drug delovni zvezek in nato vnesite formulo, ki vsebuje krožno sklicevanje.

Kdaj vidite napako krožnega sklica v Excelu

• Pravkar ste odprli delovni zvezek, ki je prej vseboval enega ali več krožnih sklicev. Videli boste opozorilo.
• Ko vam Excel enkrat prikaže opozorilno sporočilo o krožnem sklicevanju in kliknete V redu , v isti seji ne bo več prikazal istega pojavnega okna, tudi če še naprej ustvarjate več formul s krožnimi sklicevanjem.

Zato morate skrbno poiskati zgornje primere, da ujamete krožna referenčna opozorila in jih razrešite, če ne iščete ponavljajočih se izračunov.

Vrste krožnih sklicev v Excelu

Če se želite naučiti najti krožne reference v Excelu, morate razumeti njihove vrste. Ker iskanje in odstranjevanje krožnih sklicev v Excelu ni avtomatizirana naloga. Spodaj poiščite pogoste vrste te napake:

Primer uporabe krožnega sklica

• Do načrtovanih krožnih sklicev pride, ko jih namenoma uporabite za namene ponavljajočih se izračunov. Na primer, v drugo celico morate vnesti časovni žig, ko nekdo vnese vrednost v referenčno celico z uporabo nestanovitnih in logičnih funkcij, kot so IF, OR, SUMIF itd.

Nenamerna krožna referenca v Excelu

• Nenamerno krožno sklicevanje, ko poskušate hitreje delati na Excelovi formuli in se pomotoma sklicujete na celico, ki vsebuje formulo. Uporabiti morate na primer funkcijo SUM iz celice A1 v celico A9 in pridobiti rezultat v celici A10 . Toda pomotoma v celico A10 vnesete formulo, kot je ta:

=SUM(A2:A10)

• Skrite krožne reference so tiste, ki ne prikažejo nobenih opozorilnih sporočil, ko ustvarite formulo ali prvič odprete delovni list. To se zgodi, ker je nekdo dovolil iterativni izračun v datoteki Excel.

Primer posrednega krožnega sklicevanja

• Priljubljene so tudi posredne krožne reference in se pojavijo, ko ustvarite novo formulo v kateri koli celici, na primer celici A1 , in ta nova formula bi uporabila drugo funkcijo v drugi celici, kot je celica B1 , ki že uporablja celico A1 kot referenco .

Zdaj, ko ste razumeli osnove krožnih sklicev v Excelu, poglejmo spodaj, kako najti krožne sklice v Excelu:

Kako najti krožne reference v Excelu: iz vrstice stanja

Pod pogojem, da je funkcija iterativnega izračuna deaktivirana, lahko vidite naslednja obvestila o krožnih sklicih v Excelovi vrstici stanja:

• Krožni sklici: C3  (ali kateri koli drug sklic na celico), ko ste na delovnem listu, ki vsebuje krožno formulo v delovnem zvezku.

Prikaz krožnih sklicev v Excelovi vrstici stanja

• Krožne reference boste videli le, če ste na delovnem listu, ki ne vsebuje nobenega, vendar ima delovni zvezek nekaj teh krožnih formul.

Kako najti krožne reference v Excelu: s preverjanjem napak

To je najboljši način za preverjanje krožnih sklicev v delovnem zvezku ali delovnem listu. Tu so koraki, ki jih morate poznati:

• Odprite delovni list, za katerega sumite, da vsebuje krožne formule.
• Kliknite meni Formule na Excelovem traku.

Uporabite preverjanje napak za Kako najti krožne reference v Excelu

• V skupini ukazov za nadzor formule kliknite spustni seznam Preverjanje napak  .
• Premaknite kazalec nad možnost Krožne reference , da si ogledate vse razpoložljive instance na vseh delovnih listih.
• Izberite vsak primerek in Excel bo te celice samodejno določil.

Kako najti krožne reference v Excelu: iz sledenja formul

Če se vam metoda preverjanja napak ne zdi dovolj učinkovita za odkrivanje velikega števila formul s krožnimi sklici, lahko poskusite s temi koraki uporabiti metodo sledenja formulam:

• Izberite katero koli celico s sklicem ali celico, ki vsebuje formulo.
• Pojdite na zavihek Formule v meniju Excelovega traku in kliknite Sledi precedensom .
• Excel bo s puščicami označil celice, ki vplivajo na rezultat formule v trenutni celici.

Uporabite sledenje formule za Kako najti krožne reference v Excelu

• Lahko tudi kliknete Sledi odvisnim , da odkrijete celice, na katere vpliva vrednost izbrane reference celice.
• Če Excel ne prikaže nobenih napak, potem ko kliknete zgornje ukaze, na vašem delovnem listu ni krožnih sklicev.
• Ponovite korake na vseh delovnih listih, da preverite celoten delovni zvezek.

Ta metoda je še posebej pomembna, ko so funkcije iterativnega izračuna aktivne na Excelovem delovnem listu.

Kako najti krožne reference v Excelu: Uporaba Excelovih možnosti

• Kliknite zavihek Datoteka na Excelovem traku in nato odprite Excelove možnosti v stranski vrstici, ki se prikaže.
• V pogovornem oknu Excelove možnosti v stranski vrstici izberite meni Formule in nato počistite potrditveno polje Omogoči iterativni izračun .

Kako deaktivirati iterativni izračun v Excelu

• Kliknite OK , da shranite spremembe.
• Zdaj vam bo delovni zvezek ali delovni list samodejno prikazal opozorilo o krožnem sklicu.
• Če se ne prikaže takoj, shranite delovni zvezek, ga zaprite in znova odprite, da vidite opozorilo.
• Ko vidite opozorilo, preverite Excelovo vrstico stanja , da ugotovite, katera celica je v krožnem sklicu.

Kako razrešiti krožne reference v Excelu

Zanesljiv način za popravljanje ene ali več krožnih referenčnih napak v Excelu je ročno urejanje vsake formule, ki jo vsebuje. Na ta način lahko razrešite nenamerne in posredne krožne sklice v Excelu.

Kako razrešiti krožne reference v Excelu

Drug preprost način je, da formulo odstranite iz trenutne celice in jo postavite nekam drugam znotraj istega delovnega lista.

Kako najti krožne reference v Google Preglednicah (dodatna metoda)

Tako kot Excel tudi Google Preglednice ne dovoljujejo krožnih sklicevanj v eni ali več formulah, če niste aktivirali funkcije iterativnega izračuna. Zato bodo Google Preglednice takoj prikazale #REF! napaka v celici, ki vsebuje krožno formulo.

Google Preglednice Kako najti krožne reference

Zato lahko preprosto izberete vse celice, ki vsebujejo #REF! opozorilo za iskanje in razreševanje krožnih sklicev v Google Preglednicah.

Excelove okrožnice: zadnje besede

Torej, zdaj poznate vse skrivnosti, kako najti krožne reference v Excelu. Uporabite metodo, ki vam je všeč, in opustite vse krožne reference, da zagotovite, da vas ne bodo zmedli nezanesljivi podatki iz Excelovega poročila ali matematične nadzorne plošče.

Sledijo  najboljše Excelove predloge koledarjev za vsako leto  in  najboljše Excelove proračunske predloge .