Jak najít kruhové odkazy v Excelu, abyste se vyhnuli chybným datům

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 omylem 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.

Co je kruhový odkaz v Excelu?

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 dát adresu buňky do vzorce, kde ji vytváříte. Vizuální vysvětlení viz následující příklad:

Pokoušíte se vynásobit 10 z buňky B1 a výsledek vložit do samotné buňky B1 .

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

Co je kruhový odkaz v Excelu

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 aplikací Excel kvůli chybě kruhové reference, což 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á ji 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.

Kdy se v Excelu zobrazuje chyba kruhového odkazu?

Je zřejmé, že kruhové vyskakovací okno s odkazem 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 se vyhnuli š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í.

Kdy se v Excelu zobrazuje chyba kruhového odkazu

• 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 s kruhovým odkazem a kliknete na tlačítko OK , nebude se již ve stejné relaci zobrazovat stejné vyskakovací okno, i když budete vytvářet více vzorců s kruhovým odkazem.

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

Typy kruhových odkazů v Excelu

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:

Příklad použití kruhové reference

• 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ý odkaz v Excelu

• 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 funkci SUM z buňky A1 do A9 a načíst výsledek do buňky 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.

Příklad nepřímé kruhové reference

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

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

Jak najít kruhové odkazy v Excelu: Ze stavového řádku

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.

Zobrazení kruhových odkazů ve stavovém řádku aplikace Excel

• Kruhové odkazy uvidíte pouze v případě, že jste na listu, který žádné neobsahuje, ale sešit obsahuje několik těchto kruhových vzorců.

Jak najít kruhové odkazy v Excelu: pomocí kontroly chyb

Je to nejlepší způsob, jak zkontrolovat cyklické 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 nabídku Vzorce na pásu karet aplikace Excel.

Použijte kontrolu chyb pro Jak najít kruhové odkazy v Excelu

• Ve skupině příkazů Auditování vzorců klikněte na rozevírací seznam Kontrola chyb  .
• Umístěním kurzoru na možnost Kruhové odkazy zobrazíte všechny dostupné instance ve všech listech.
• Vyberte každou instanci a Excel tyto buňky automaticky určí.

Jak najít kruhové odkazy v Excelu: Z trasování vzorců

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řejděte na kartu Vzorce na pásu karet aplikace Excel a poté klikněte na možnost Trasovat precedenty .
• Excel zvýrazní buňky, které ovlivňují výsledek vzorce, v aktuální buňce pomocí šipek.

Použijte trasování vzorců pro Jak najít kruhové odkazy v Excelu

• Můžete také kliknout na možnost Trasovat závislé a objevit buňky ovlivněné 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.

Jak najít kruhové odkazy v Excelu: Použití možností Excelu

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

Jak deaktivovat iterativní výpočet v Excelu

• Klepnutím na tlačítko OK uložte změny.
• 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 jej otevřete, abyste viděli varování.
• Jakmile uvidíte varování, zkontrolujte stavový řádek aplikace Excel , abyste věděli, která buňka je v kruhovém odkazu.

Jak vyřešit kruhové odkazy v Excelu

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.

Jak vyřešit kruhové odkazy v Excelu

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.

Jak najít kruhové reference v Tabulkách Google (bonusová metoda)

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.

Tabulky Google, jak najít kruhové reference

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.

Kruhové odkazy Excelu: Závěrečná slova

Nyní tedy znáte všechna tajemství, jak najít kruhové odkazy v Excelu. Použijte metodu, která se vám líbí, a vynechejte všechny kruhové odkazy, abyste se nenechali zmást nespolehlivými daty z Excelové sestavy nebo matematického řídicího panelu.

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