Jak najít kruhové odkazy v Excelu 4 způsoby

Viděli jste chybu kruhového odkazu na listu aplikace Excel a přemýšleli jste, zda je list poškozený? Věř mi! Nejste první, kdo čelí této chybě Excelu. Stovky a tisíce uživatelů aplikace Excel se každý den setkávají s cyklickými referenčními chybami. Nebojte se! Zde je návod, jak najít cyklické odkazy v Excelu a vyřešit je.

Jako vášnivý uživatel Excelu málokdy zadáte stejná data do mnoha buněk. Místo toho importujete sadu dat ze zdroje a pomocí odkazování na buňky vytvoříte vzorce. Vytvoříte tak dynamickou tabulku.

Když někdo změní hodnoty buněk odkazů na buňky, list se automaticky aktualizuje. Tomu se říká automatizace analýzy dat a datoví analytici si rádi hrají s odkazy na buňky.

Problém však nastává, když úmyslně nebo náhodně zadáte adresu buňky, kde právě provádíte výpočet. Nebo když vytváříte mnoho vzorců v celém listu, buňka obsahující vzorec se nepřímo objeví v jiném vzorci.

V takových a mnoha dalších podobných situacích se v Excelu zobrazí chyba kruhového odkazu. Čtěte dále a dozvíte se, co je to kruhový odkaz, jeho důvody, proč se mu vyhnout a hlavně, jak najít kruhové odkazy v Excelu, abyste se těchto anomálií nadobro zbavili.

Excel narazí na chybu cyklického odkazu, když vidí, že jedna nebo více funkcí listu odkazuje zpět na adresu buňky, kde byly jejich funkce vytvořeny.

Může to pro vás znít matoucí, takže přemýšlejte o tom, jak vážné to může být pro aplikaci Excel. Stručně řečeno, nikdy nechcete vložit adresu buňky do vzorce, kde ji vytváříte. Vizuální vysvětlení viz následující příklad:

Snažíte se množit 10 z buňky B1 a výsledek vložte do buňky B1 sám.

Nyní, ve výše uvedeném scénáři, stačí stisknout OK, Excel chybu zavře a jako vypočítaný výsledek zobrazí nulu. Toto je jen přímý příklad.

Nyní si představte, že v účetním listu, listu finančního plánování, mzdovém listu zaměstnanců, listu plateb dodavatele a tak dále jsou stovky vzorců.

Budete důvěřovat chybným datům vypočítaným Excelem kvůli kruhové referenční chybě a to zase ovlivní vaše obchodní nebo profesionální rozhodování.

Kromě toho může kruhové odkazování výrazně snížit rychlost výpočtů v aplikaci Excel. V závažných případech může samotná chyba způsobit extrémně pomalý výkon v počítači, pokud není okamžitě řešena.

Pokud však pracujete na pokročilých vzorcích Excelu, kde váš datový model vyžaduje zprostředkovatelské vstupy z kruhového odkazu, abyste mohli pokračovat s dlouhou funkcí, možná jej budete muset použít. Ve skutečnosti vám Excel umožňuje použít tuto metodu výpočtu v případě potřeby.

Například v jednotkách na výrobu energie, jako jsou větrné mlýny, musíte vypočítat recyklovanou výstupní energii ze zlomku vstupní energie. Alternativně v chemické továrně inženýři používají výstupní vřeteno jako vstup pro stejný nebo jiný kotel nebo procesní nádobu. Takové technické výpočty na vysoké úrovni skutečně vyžadují kruhové odkazy pro iterativní výpočty.

Je zřejmé, že vyskakovací okno s kruhovou referencí není chybová zpráva. Je to způsob, jak vás Excel informovat, že narazil na kruhový odkaz v jednom nebo více vzorcích, což může ovlivnit jeho výpočetní algoritmus a výkon.

Abyste předešli škodám způsobeným cyklickými odkazy ve vzorcích aplikace Excel, musíte vědět, kdy se objevují a kdy ne. Níže najdete příklady:

• V libovolném otevřeném listu nebo sešitu vytvoříte vzorec a poprvé zadáte kruhový odkaz.
• Vy nebo někdo jiný jste ze souboru aplikace Excel odstranili všechny cyklické odkazy a uložili jej. Nyní znovu vy nebo jiný uživatel zadejte kruhový odkaz do stejného souboru.
• Zavřeli jste všechny otevřené sešity. Nyní otevřete jiný sešit a zadejte vzorec obsahující kruhové odkazování.

• Právě jste otevřeli sešit, který dříve obsahoval jeden nebo více kruhových odkazů. Uvidíte varování.
• Jakmile vám aplikace Excel jednou zobrazí varovnou zprávu o kruhovém odkazu a vy kliknete OK, již nebude zobrazovat stejné vyskakovací okno ve stejné relaci, i když budete vytvářet více kruhových odkazovaných vzorců.

Proto musíte pečlivě hledat výše uvedené případy, abyste zachytili cyklická referenční varování a vyřešili je, pokud nehledáte iterativní výpočty.

Pokud se chcete naučit, jak najít cyklické odkazy v Excelu, musíte rozumět jejich typům. Protože vyhledání a odstranění cyklických odkazů v Excelu není automatický úkol. Níže naleznete běžné typy této chyby:

• K zamýšleným kruhovým odkazům dochází, když je záměrně použijete pro účely iterativních výpočtů. Například potřebujete vložit časové razítko do jiné buňky, když někdo zadá hodnotu do odkazované buňky pomocí nestálých a logických funkcí jako IF, OR, SUMIF atd.

• Nezamýšlené kruhové odkazování, když se pokusíte pracovat na vzorci aplikace Excel rychleji a omylem odkazujete na buňku obsahující vzorec. Například musíte použít SOUČET funkce z buňky A1 na A9 a načíst výsledek v buňce A10. Ale omylem zadáte vzorec takto do buňky A10:

=SUM(A2:A10)

• Skryté kruhové odkazy jsou ty, které nezobrazují žádné varovné zprávy, když vytváříte vzorec nebo poprvé otevřete list. Stává se to proto, že někdo povolil iterativní výpočet na souboru Excel.

• Nepřímé kruhové odkazy jsou také oblíbené a objevují se, když vytvoříte nový vzorec v jakékoli buňce, například v buňce A1a tento nový vzorec by používal jinou funkci v jiné buňce, jako je buňka B1, který již používá buňku A1 jako reference.

Nyní, když jste pochopili základy cyklických odkazů v Excelu, pojďme zjistit, jak najít cyklické odkazy v Excelu níže:

Za předpokladu, že je funkce iterativního výpočtu deaktivována, můžete na stavovém řádku aplikace Excel vidět následující oznámení o cyklických odkazech:

• Kruhové odkazy: C3 (nebo jakýkoli jiný odkaz na buňku), když jste na listu obsahujícím kruhový vzorec v sešitu.

• Pouze uvidíte Kruhové odkazy pokud jste na listu, který žádné neobsahuje, ale sešit má několik těchto kruhových vzorců.

Je to nejlepší způsob, jak zkontrolovat kruhové odkazy v sešitu nebo listu. Zde jsou kroky, které musíte znát:

• Otevřete list, o kterém máte podezření, že obsahuje kruhové vzorce.
• Klikněte na Vzorce nabídce na pásu karet Excelu.

• V Audit vzorce skupina příkazů, klepněte na Kontrola chybrozbalovací seznam.
• Najeďte kurzorem na Kruhové odkazy možnost zobrazit všechny dostupné instance ve všech listech.
• Vyberte každou instanci a Excel tyto buňky automaticky určí.

Pokud vám metoda Error Checking nepřipadá dostatečně účinná pro detekci velkého počtu vzorců s cyklickými odkazy, můžete zkusit použít metodu sledování vzorců pomocí těchto kroků:

• Vyberte libovolnou odkazovanou buňku nebo buňku obsahující vzorec.
• Přejít na Vzorce na pásu karet aplikace Excel a potom klepněte na Stopové precedenty.
• Excel zvýrazní buňky, které ovlivňují výsledek vzorce, v aktuální buňce pomocí šipek.

• Můžete také kliknout Stopové závislé k nalezení buněk ovlivněných hodnotou odkazu na vybranou buňku.
• Pokud Excel po kliknutí na výše uvedené příkazy nezobrazí žádné chyby, nejsou v listu žádné kruhové odkazy.
• Opakováním kroků na všech listech zkontrolujte celý sešit.

Tato metoda je zvláště důležitá, když jsou na listu aplikace Excel aktivní funkce iterativního výpočtu.

• Klikněte na Soubor na pásu karet Excel a poté otevřete Možnosti aplikace Excel z postranního panelu, který se zobrazí.
• Na Možnosti aplikace Excel dialogovém okně vyberte Vzorce z postranního panelu a poté zrušte zaškrtnutí políčka pro Povolit iterativní výpočet.

• Klikněte OK pro uložení změn.
• Nyní vám sešit nebo list automaticky zobrazí upozornění na kruhový odkaz.
• Pokud se nezobrazí okamžitě, uložte sešit, zavřete jej a znovu otevřete, abyste viděli varování.
• Jakmile uvidíte varování, zkontrolujte Stavový řádek Excelu abyste věděli, která buňka je v kruhovém odkazu.

Bezpečným způsobem, jak opravit jednu nebo více chyb kruhového odkazu v aplikaci Excel, je ruční úprava každého vzorce, který jej obsahuje. Tímto způsobem můžete vyřešit náhodné i nepřímé cyklické odkazy v aplikaci Excel.

Dalším snadným způsobem je odebrat vzorec z aktuální buňky a umístit jej někam jinam do stejného listu.

Stejně jako Excel ani Tabulky Google nepovolují žádné kruhové odkazy v jednom nebo více vzorcích, pokud jste neaktivovali funkci iterativního výpočtu. Tabulky Google proto okamžitě zobrazí #REF! chyba na buňce, která obsahuje kruhový vzorec.

Proto můžete jednoduše vybrat všechny buňky, které obsahují #REF! upozornění na vyhledání a vyřešení kruhových odkazů v Tabulkách Google.

Nyní tedy znáte všechna tajemství, jak najít kruhové odkazy v Excelu. Použijte metodu, která se vám líbí, a zbavte se všech kruhových odkazů, abyste se nenechali zmást nespolehlivými údaji ze sestavy Excel nebo matematického řídicího panelu.

Další na řadě je nejlepší šablony kalendáře Excel pro každý rok a nejlepší šablony rozpočtu Excel.