Ako nájsť kruhové odkazy v Exceli 4 spôsobmi

Videli ste chybu kruhového odkazu na hárku programu Excel a zaujímalo vás, či je pracovný hárok poškodený? Dôveruj mi! Nie ste prvý, kto čelí tejto chybe programu Excel. Stovky a tisíce používateľov Excelu denne zažívajú kruhové chyby odkazov. Nebojte sa! Tu je návod, ako nájsť kruhové odkazy v Exceli a vyriešiť ich.

Ako zanietený používateľ Excelu málokedy zadávate rovnaké údaje do mnohých buniek. Namiesto toho importujete množinu údajov zo zdroja a pomocou odkazovania na bunky vytvoríte vzorce. Takto vytvoríte dynamickú tabuľku.

Keď niekto zmení hodnoty buniek odkazov na bunky, hárok sa automaticky aktualizuje. Toto sa nazýva automatizácia analýzy údajov a analytici údajov sa radi hrajú s odkazmi na bunky.

Problém však nastáva, keď úmyselne alebo náhodne zadáte adresu bunky, kde práve vykonávate výpočet. Alebo keď vytvárate veľa vzorcov v hárku, bunka obsahujúca vzorec sa nepriamo zobrazí v inom vzorci.

V takýchto a mnohých ďalších podobných situáciách sa v Exceli zobrazí chyba kruhového odkazu. Čítajte ďalej a dozviete sa, čo je to kruhový odkaz, jeho dôvody, prečo sa mu vyhnúť a čo je najdôležitejšie, ako nájsť kruhové odkazy v Exceli, aby ste sa týchto anomálií nadobro zbavili.

Excel narazí na chybu kruhového odkazu, keď zistí, že jedna alebo viacero funkcií hárka odkazuje späť na adresu bunky, kde boli vytvorené ich funkcie.

Môže to pre vás znieť mätúce, takže premýšľajte o tom, aké závažné to môže byť pre aplikáciu Excel. Stručne povedané, nikdy nechcete vložiť adresu bunky do vzorca, kde ju vytvárate. Pozrite si nasledujúci príklad pre vizuálne objasnenie:

Pokúšate sa množiť 10 z bunky B1 a výsledok vložte do bunky B1 sám.

Teraz, vo vyššie uvedenom scenári, stačí stlačiť OK, Excel zatvorí chybu a zobrazí nulu ako vypočítaný výsledok. Toto je len jasný príklad.

Teraz si predstavte, že v hárku účtovníctva, hárku finančného plánovania, mzdovej agendy zamestnancov, hárku platieb dodávateľa a tak ďalej sú stovky vzorcov.

Budete dôverovať chybným údajom vypočítaným programom Excel v dôsledku kruhovej referenčnej chyby, čo zase ovplyvní vaše obchodné alebo profesionálne rozhodovanie.

Okrem toho môže kruhové odkazovanie výrazne znížiť rýchlosť výpočtov v aplikácii Excel. V závažných prípadoch môže samotná chyba spôsobiť extrémne pomalý výkon aj v počítači, ak sa s ňou okamžite nezaoberá.

Ak však pracujete na pokročilých vzorcoch Excelu, kde váš dátový model vyžaduje prechodné vstupy z kruhového odkazu, aby ste mohli pokračovať v dlhej funkcii, možno ho budete musieť použiť. V skutočnosti vám Excel umožňuje použiť túto metódu výpočtu v prípade potreby.

Napríklad v jednotkách na výrobu energie, ako sú veterné mlyny, musíte vypočítať recyklovanú výstupnú energiu zo zlomku vstupnej energie. Alternatívne v chemickom závode inžinieri používajú výstupnú stopku ako vstup pre rovnaký alebo iný kotol alebo procesnú nádobu. Takéto technické výpočty na vysokej úrovni skutočne vyžadujú kruhové referencie pre iteračné výpočty.

Je jasné, že vyskakovacie okno s kruhovou referenciou nie je chybové hlásenie. Je to spôsob, ako vás Excel informuje, že v jednom alebo viacerých vzorcoch narazil na kruhový odkaz, čo môže ovplyvniť jeho algoritmus výpočtu a výkon.

Aby ste sa vyhli akýmkoľvek škodám spôsobeným kruhovými odkazmi vo vzorcoch programu Excel, musíte vedieť, kedy sa zobrazujú a kedy nie. Nižšie nájdete príklady:

• V ľubovoľnom otvorenom hárku alebo zošite vytvoríte vzorec a prvýkrát zadáte kruhový odkaz.
• Vy alebo niekto odstránil všetky kruhové odkazy zo súboru Excel a uložil ho. Teraz znova vy alebo iný používateľ zadajte kruhový odkaz do toho istého súboru.
• Zatvorili ste všetky otvorené zošity. Teraz otvorte iný zošit a potom zadajte vzorec obsahujúci kruhové odkazovanie.

• Práve ste otvorili zošit, ktorý predtým obsahoval jeden alebo viacero kruhových odkazov. Uvidíte upozornenie.
• Keď vám Excel raz zobrazí varovnú správu o kruhovom odkaze a vy ste klikli OK, už sa nebude zobrazovať to isté kontextové okno v tej istej relácii, aj keď budete vytvárať viac kruhových odkazovaných vzorcov.

Preto musíte pozorne hľadať vyššie uvedené prípady, aby ste zachytili kruhové referenčné varovania a vyriešili ich, ak nehľadáte iteratívne výpočty.

Ak sa chcete naučiť, ako nájsť kruhové odkazy v Exceli, musíte pochopiť ich typy. Pretože nájdenie a odstránenie kruhových odkazov v Exceli nie je automatizovaná úloha. Nižšie nájdete bežné typy tejto chyby:

• Zamýšľané kruhové odkazy sa vyskytujú, keď ich zámerne použijete na účely iteračných výpočtov. Napríklad, musíte vložiť časovú pečiatku do inej bunky, keď niekto zadá hodnotu do odkazovanej bunky pomocou nestálych a logických funkcií ako IF, OR, SUMIF atď.

• Neúmyselné kruhové odkazovanie, keď sa pokúšate pracovať na vzorci programu Excel rýchlejšie a omylom odkazujete na bunku obsahujúcu vzorec. Napríklad musíte použiť SUM funkcia z bunky A1 do A9 a získajte výsledok v bunke A10. Ale omylom zadáte vzorec takto do bunky A10:

=SUM(A2:A10)

• Skryté kruhové odkazy sú tie, pri ktorých sa pri vytváraní vzorca alebo prvom otvorení pracovného hárka nezobrazujú žiadne varovné správy. Stáva sa to preto, že niekto povolil iteračný výpočet v súbore Excel.

• Nepriame kruhové odkazy sú tiež populárne a vyskytujú sa, keď vytvoríte nový vzorec v ľubovoľnej bunke, napríklad bunke A1a tento nový vzorec by používal inú funkciu v inej bunke, napríklad bunke B1, ktorý už používa bunku A1 ako referenciu.

Teraz, keď ste pochopili základy kruhových odkazov v Exceli, poďme zistiť, ako nájsť kruhové odkazy v Exceli nižšie:

Ak je funkcia iteračného výpočtu deaktivovaná, na stavovom riadku Excelu sa môžu zobraziť nasledujúce upozornenia o kruhových odkazoch:

• Kruhové odkazy: C3 (alebo akýkoľvek iný odkaz na bunku), keď ste na pracovnom hárku obsahujúcom kruhový vzorec v zošite.

• Len uvidíte Kruhové odkazy ak ste na pracovnom hárku, ktorý neobsahuje žiadne, ale zošit obsahuje niekoľko z týchto kruhových vzorcov.

Je to najlepší spôsob, ako skontrolovať kruhové odkazy v zošite alebo pracovnom hárku. Tu sú kroky, ktoré musíte vedieť:

• Otvorte pracovný hárok, o ktorom máte podozrenie, že obsahuje kruhové vzorce.
• Kliknite na Vzorce menu na páse s nástrojmi Excel.

• V Audit vzorcov skupina príkazov, kliknite na Kontrola chýbrozbaľovací zoznam.
• Ukážte kurzorom na Kruhové odkazy možnosť zobraziť všetky dostupné inštancie vo všetkých pracovných hárkoch.
• Vyberte každú inštanciu a Excel automaticky určí tieto bunky.

Ak sa vám metóda kontroly chýb nezdá dostatočne efektívna na zistenie veľkého počtu vzorcov s kruhovými odkazmi, môžete vyskúšať tieto kroky a použiť metódu sledovania vzorcov:

• Vyberte ľubovoľnú odkazovanú bunku alebo bunku obsahujúcu vzorec.
• Choďte na Vzorce v ponuke Excel na páse s nástrojmi a potom kliknite Stopové precedensy.
• Excel zvýrazní bunky, ktoré ovplyvňujú výsledok vzorca, v aktuálnej bunke šípkami.

• Môžete tiež kliknúť Stopové závislé na objavenie buniek ovplyvnených hodnotou odkazu na vybratú bunku.
• Ak Excel po kliknutí na vyššie uvedené príkazy nezobrazuje žiadne chyby, v pracovnom hárku nie sú žiadne kruhové odkazy.
• Opakujte kroky na všetkých hárkoch, aby ste skontrolovali celý zošit.

Táto metóda je obzvlášť dôležitá, keď sú v pracovnom hárku programu Excel aktívne funkcie iteračného výpočtu.

• Kliknite na Súbor na páse s nástrojmi Excel a potom otvorte Možnosti programu Excel z bočného panela, ktorý sa zobrazí.
• Na Možnosti programu Excel dialógovom okne vyberte Vzorce z bočného panela a potom zrušte začiarknutie políčka pre Povoliť iteračný výpočet.

• Kliknite OK aby sa zmeny uložili.
• Teraz vám zošit alebo pracovný hárok automaticky zobrazí upozornenie na kruhový odkaz.
• Ak sa nezobrazí okamžite, uložte zošit, zatvorte ho a znova ho otvorte, aby sa zobrazilo upozornenie.
• Keď uvidíte varovanie, skontrolujte Stavový riadok Excelu vedieť, ktorá bunka je v kruhovom odkaze.

Bezpečným spôsobom, ako opraviť jednu alebo viacero chýb kruhového odkazu v Exceli, je ručná úprava každého vzorca, ktorý ho obsahuje. Týmto spôsobom môžete vyriešiť náhodné aj nepriame kruhové odkazy v Exceli.

Ďalším jednoduchým spôsobom je odstrániť vzorec z aktuálnej bunky a umiestniť ho niekde inde v rámci toho istého pracovného hárka.

Podobne ako Excel, ani Tabuľky Google nepovoľujú žiadne kruhové odkazy v jednom alebo viacerých vzorcoch, ak ste neaktivovali funkciu iteračného výpočtu. Tabuľky Google preto okamžite zobrazia #REF! chyba v bunke, ktorá obsahuje kruhový vzorec.

Preto môžete jednoducho vybrať všetky bunky, ktoré obsahujú #REF! varovanie, aby ste našli a vyriešili kruhové odkazy v Tabuľkách Google.

Takže teraz poznáte všetky tajomstvá, ako nájsť kruhové odkazy v Exceli. Použite metódu, ktorá sa vám páči, a zbavte sa všetkých kruhových odkazov, aby ste sa nemuseli nechať zmiasť nespoľahlivými údajmi zo správy Excel alebo matematického dashboardu.

Ďalej na rade najlepšie šablóny kalendára Excel pre každý rok a najlepšie šablóny rozpočtu Excel.