Kako najti krožne reference v Excelu na 4 načine

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! Evo, 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 vtipkate naslov celice, kjer 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.

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 vstavite rezultat v celico B1 sama.

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

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.

Jasno je, da pojavno okno s krožnim sklicem ni sporočilo o napaki. To je način, da vas Excel obvesti, da je v eni ali več formulah naletel na krožno referenco, 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.

• Pravkar ste odprli delovni zvezek, ki je prej vseboval enega ali več krožnih sklicev. Videli boste opozorilo.
• Ko vam je Excel enkrat prikazal opozorilno sporočilo o krožni referenci in ste kliknili v redu, v isti seji ne bo več prikazal istega pojavnega okna, tudi če boste še naprej ustvarjali več krožnih referenčnih formul.

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.

Č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:

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

• 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 SUM deluje iz celice A1 do A9 in pridobi 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.

• Priljubljeni so tudi posredni krožni sklici 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 referenca.

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

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

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

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

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 na Formule meni na Excelovem traku.

• V Revizija formule skupino ukazov kliknite Preverjanje napakspustni seznam.
• Premaknite kazalec nad Okrožne reference možnost za ogled vseh razpoložljivih primerkov na vseh delovnih listih.
• Izberite vsak primerek in Excel bo te celice samodejno določil.

Č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.
• Pojdi na Formule zavihek v meniju traku programa Excel in nato kliknite Trace Precedents.
• Excel bo s puščicami označil celice, ki vplivajo na rezultat formule v trenutni celici.

• Lahko tudi kliknete Izsledi odvisnike za odkrivanje celic, na katere vpliva vrednost sklica izbrane 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.

• Kliknite na mapa na Excelovem traku in nato odprite Excelove možnosti iz stranske vrstice, ki se prikaže.
• Na Excelove možnosti pogovornem oknu izberite Formule v stranski vrstici in nato počistite potrditveno polje za Omogoči iterativni izračun.

• Kliknite v redu 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 Excelova vrstica stanja vedeti, katera celica je v krožnem sklicu.

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.

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

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.

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

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.

Naslednji, najboljše predloge koledarjev Excel za vsako leto in najboljše proračunske predloge Excel.