Ako nájsť kruhové odkazy v Exceli, aby ste sa vyhli chybným údajom

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, v ktorej 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.

Čo je to kruhový odkaz v Exceli?

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 vynásobiť 10 z bunky B1 a výsledok vložiť do samotnej bunky B1 .

Ak teraz vo vyššie uvedenom scenári stlačíte tlačidlo OK , Excel zatvorí chybu a zobrazí nulu ako vypočítaný výsledok. Toto je len jasný príklad.

Čo je kruhový odkaz v Exceli

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 chyby kruhového odkazu, čo následne 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.

Kedy sa v Exceli zobrazuje chyba kruhového odkazu?

Je jasné, že vyskakovacie okno s kruhovou referenciou nie je chybové hlásenie. Je to spôsob, akým 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.

Kedy sa vám v Exceli zobrazuje chyba kruhového odkazu

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

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

Typy kruhových odkazov v Exceli

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

Príklad použitia kruhovej referencie

• Zamýšľané kruhové odkazy sa vyskytujú, keď ich zámerne použijete na účely iteračných výpočtov. Napríklad, musíte zadať č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ý odkaz v Exceli

• 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ť funkciu SUM z bunky A1 do A9 a načítať výsledok do bunky 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.

Príklad nepriameho kruhového odkazu

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

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

Ako nájsť kruhové odkazy v Exceli: zo stavového riadka

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.

Zobrazenie kruhových odkazov v stavovom riadku Excelu

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

Ako nájsť kruhové odkazy v Exceli: pomocou kontroly chýb

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 ponuku Vzorce na páse s nástrojmi Excel.

Použite kontrolu chýb pre Ako nájsť kruhové odkazy v Exceli

• V skupine príkazov Kontrola vzorcov kliknite na rozbaľovací zoznam Kontrola chýb  .
• Umiestnením kurzora nad možnosť Kruhové odkazy zobrazíte všetky dostupné inštancie vo všetkých hárkoch.
• Vyberte každú inštanciu a Excel automaticky určí tieto bunky.

Ako nájsť kruhové odkazy v Exceli: zo sledovania vzorcov

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.
• Prejdite na kartu Vzorce na páse s nástrojmi programu Excel a potom kliknite na položku Sledovať precedensy .
• Excel zvýrazní bunky, ktoré ovplyvňujú výsledok vzorca, v aktuálnej bunke šípkami.

Ako nájsť kruhové odkazy v Exceli použite sledovanie vzorcov

• Môžete tiež kliknúť na položku Sledovať závislé a objaviť bunky ovplyvnené hodnotou odkazu na vybratú bunku.
• Ak Excel po kliknutí na vyššie uvedené príkazy nezobrazí ž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.

Ako nájsť kruhové odkazy v Exceli: Použitie možností Excelu

• Kliknite na kartu Súbor na páse s nástrojmi programu Excel a potom na zobrazenom bočnom paneli otvorte Možnosti programu Excel .
• V dialógovom okne Možnosti programu Excel vyberte na bočnom paneli ponuku Vzorce a potom zrušte začiarknutie políčka Povoliť iteračný výpočet .

Ako deaktivovať iteračný výpočet v Exceli

• Kliknutím na OK uložte zmeny.
• 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 upozornenie, skontrolujte stavový riadok Excelu , aby ste vedeli, ktorá bunka je v kruhovom odkaze.

Ako vyriešiť kruhové odkazy v Exceli

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.

Ako vyriešiť 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.

Ako nájsť kruhové referencie v Tabuľkách Google (bonusová metóda)

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.

Tabuľky Google Ako nájsť kruhové referencie

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.

Kruhové odkazy programu Excel: Záverečné slová

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 vynechajte všetky kruhové odkazy, aby ste sa nenechali zmiasť nespoľahlivými údajmi zo správy programu Excel alebo matematického dashboardu.

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