Jedním z nejčastějších chybových varování, se kterými se uživatelé v Excelu setkávají, je „Circular Reference“. Tisíce uživatelů mají stejný problém a dochází k němu, když vzorec přímo nebo nepřímo odkazuje zpět na svou vlastní buňku, což způsobuje nekonečnou smyčku výpočtů.
Například máte dvě hodnoty v buňkách B1 a B2. Když vzorec =B1+B2 zadáte do B2, vytvoří se kruhový odkaz; vzorec v B2 se opakovaně přepočítává, protože při každém výpočtu se hodnota B2 změnila.
Většina kruhových odkazů jsou nezamýšlené chyby; Excel vás na to upozorní. Existují však také zamýšlené kruhové odkazy, které se používají k provádění iterativních výpočtů. Nezamýšlené kruhové odkazy v listu mohou způsobit nesprávný výpočet vzorce.
Proto v tomto článku vysvětlíme vše, co potřebujete vědět o cyklických odkazech a také o tom, jak najít, opravit, odstranit a používat cyklické odkazy v Excelu.
Jak najít a zpracovat kruhový odkaz v Excelu
Při práci s Excelem se někdy setkáváme s chybami cyklického odkazu, ke kterým dochází, když zadáte vzorec, který obsahuje buňku, ve které je váš vzorec umístěn. V zásadě se to stane, když se váš vzorec pokouší vypočítat sám sebe.
Například máte sloupec čísel v buňce A1:A4 a v buňce A5 používáte funkci SUM (=SUM(A1:A5)). Buňka A5 přímo odkazuje na svou vlastní buňku, což je špatně. Proto obdržíte následující kruhové referenční varování:
Jakmile se zobrazí výše uvedená varovná zpráva, můžete kliknout na tlačítko ‚Nápověda‘, abyste se o chybě dozvěděli více, nebo zavřít okno chybové zprávy kliknutím na tlačítko ‚OK‘ nebo ‚X‘ a jako výsledek získat ‚0‘.
Někdy mohou kruhové referenční smyčky způsobit selhání výpočtu nebo zpomalení výkonu listu. Kruhové odkazy mohou také vést k řadě dalších problémů, které nebudou okamžitě zřejmé. Těmto je tedy nejlepší se vyhnout.
Přímé a nepřímé kruhové odkazy
Kruhové reference lze rozdělit do dvou typů: Přímé kruhové reference a Nepřímé kruhové reference.
Přímý odkaz
Přímý kruhový odkaz je docela jednoduchý. Varovná zpráva o přímém kruhovém odkazu se objeví, když vzorec přímo odkazuje zpět na svou vlastní buňku.
V níže uvedeném příkladu vzorec v buňce A2 přímo odkazuje na vlastní buňku (A2).
Jakmile se objeví varovná zpráva, můžete kliknout na „OK“, ale výsledkem bude pouze „0“.
Nepřímý kruhový odkaz
Nepřímý cyklický odkaz v aplikaci Excel nastane, když hodnota ve vzorci odkazuje zpět na vlastní buňku, ale ne přímo. Jinými slovy, kruhový odkaz může být tvořen dvěma buňkami, které na sebe odkazují.
Pojďme si to vysvětlit na tomto jednoduchém příkladu.
Nyní hodnota začíná od A1, která má hodnotu 20.
Dále buňka C3 odkazuje na buňku A1.
Potom buňka A5 odkazuje na buňku C3.
Nyní nahraďte hodnotu 20 v buňce A1 vzorcem, jak je uvedeno níže. Každá další buňka je závislá na buňce A1. Když použijete odkaz na jakoukoli jinou předchozí buňku vzorce v A1, způsobí to upozornění na kruhový odkaz. Protože vzorec v A1 odkazuje na buňku A5, která odkazuje na C3, a buňka C3 odkazuje zpět na A1, proto kruhový odkaz.
Když kliknete na „OK“, výsledkem bude hodnota 0 v buňce A1 a Excel vytvoří propojený řádek zobrazující precedenty trasování a závislé trasování, jak je znázorněno níže. Tuto funkci můžeme použít ke snadnému vyhledání a opravě/odstranění cyklických odkazů.
Jak povolit / zakázat kruhové odkazy v aplikaci Excel
Ve výchozím nastavení jsou iterativní výpočty v Excelu vypnuty (zakázány). Iterativní výpočty jsou opakující se výpočty, dokud nesplní určitou podmínku. Když je zakázáno, Excel zobrazí zprávu Circular Reference a jako výsledek vrátí 0.
Někdy jsou však pro výpočet smyčky potřeba kruhové odkazy. Chcete-li použít kruhový odkaz, musíte ve svém Excelu povolit iterativní výpočty, které vám umožní provádět výpočty. Nyní vám ukážeme, jak můžete povolit nebo zakázat iterativní výpočty.
V Excelu 2010, Excel 2013, Excel 2016, Excel 2019 a Microsoft 365 přejděte na kartu „Soubor“ v levém horním rohu Excelu a poté klikněte na „Možnosti“ v levém podokně.
V okně Možnosti aplikace Excel přejděte na kartu „Vzorec“ a zaškrtněte políčko „Povolit iterativní výpočet“ v části „Možnosti výpočtu“. Poté kliknutím na „OK“ uložte změny.
To umožní iterativní výpočet a tím umožní kruhový odkaz.
Chcete-li toho dosáhnout ve starších verzích aplikace Excel, postupujte takto:
- V Excelu 2007 klikněte na tlačítko Office > Možnosti aplikace Excel > Vzorce > oblast Iterace.
- V Excelu 2003 a dřívějších verzích musíte přejít na Nabídka > Nástroje > Možnosti > karta Výpočet.
Maximální počet iterací a maximální parametry změny
Jakmile povolíte iterativní výpočty, můžete ovládat iterativní výpočty zadáním dvou možností dostupných v části Povolit iterativní výpočet, jak je znázorněno na obrázku níže.
- Maximální počet iterací – Toto číslo udává, kolikrát se má vzorec přepočítat, než vám poskytne konečný výsledek. Výchozí hodnota je 100. Pokud ji změníte na „50“, Excel zopakuje výpočty 50krát, než vám poskytne konečný výsledek. Pamatujte, že čím vyšší je počet iterací, tím více zdrojů a času zabere výpočet.
- Maximální změna – Určuje maximální změnu mezi výsledky výpočtu. Tato hodnota určuje přesnost výsledku. Čím menší číslo, tím přesnější bude výsledek a tím déle bude výpočet listu trvat.
Pokud je povolena možnost iterativních výpočtů, neobdržíte žádné varování, kdykoli je ve vašem listu cyklický odkaz. Interaktivní výpočet povolte pouze tehdy, když je to nezbytně nutné.
Najděte Circular Reference v Excelu
Předpokládejme, že máte velkou datovou sadu a dostali jste varování o kruhové referenci, budete muset ještě zjistit, kde (ve které buňce) se chyba vyskytla, abyste ji mohli opravit. Chcete-li najít cyklické odkazy v aplikaci Excel, postupujte takto:
Použití nástroje pro kontrolu chyb
Nejprve otevřete list, kde se kruhový odkaz stal. Přejděte na kartu „Vzorec“, klikněte na šipku vedle nástroje „Kontrola chyb“. Poté stačí najet kurzorem na možnost ‚Kruhové odkazy‘, Excel vám zobrazí seznam všech buněk, které se týkají kruhového odkazu, jak je znázorněno níže.
Klikněte na libovolnou adresu buňky, kterou chcete v seznamu, a přenese vás na adresu buňky, abyste problém vyřešili.
Pomocí stavového řádku
Kruhový odkaz můžete také najít na stavovém řádku. Na stavovém řádku aplikace Excel vám zobrazí nejnovější adresu buňky s kruhovým odkazem, například „Kruhové odkazy: B6“ (viz níže uvedený snímek obrazovky).
Existují určité věci, které byste měli vědět, když pracujete s kruhovými referencemi:
- Když je povolena možnost Iterativní výpočet, na stavovém řádku se nezobrazí adresa buňky kruhového odkazu, takže jej musíte zakázat, než začnete v sešitu hledat kruhové odkazy.
- V případě, že kruhový odkaz není v aktivním listu nalezen, stavový řádek zobrazuje pouze „Kruhové odkazy“ bez adresy buňky.
- Výzvu k cyklickému odkazu dostanete pouze jednou a po kliknutí na „OK“ se výzva příště již nezobrazí.
- Pokud váš sešit obsahuje cyklické odkazy, zobrazí se výzva při každém jeho otevření, dokud nevyřešíte cyklický odkaz nebo dokud nezapnete iterativní výpočet.
Odeberte kruhový odkaz v aplikaci Excel
Nalezení kruhových referencí je snadné, ale opravit je tak jednoduché není. Bohužel v Excelu neexistuje žádná možnost, která by vám umožnila odstranit všechny kruhové odkazy najednou.
Chcete-li opravit kruhové odkazy, musíte najít každý kruhový odkaz jednotlivě a pokusit se jej upravit, kruhový vzorec úplně odstranit nebo jej nahradit jiným.
Někdy v jednoduchých vzorcích stačí přenastavit parametry vzorce tak, aby se neodkazoval zpět na sebe. Například změňte vzorec v B6 na =SUM(B1:B5)*A5 (změna B6 na B5).
Výsledek výpočtu vrátí jako „756“.
V případech, kdy je obtížné najít cyklický odkaz Excelu, můžete použít funkce Trace Precedents a Trace Dependents k jeho zpětnému dohledání ke zdroji a vyřešení jednoho po druhém. Šipka ukazuje, které buňky jsou ovlivněny aktivní buňkou.
Existují dvě metody trasování, které vám mohou pomoci odstranit cyklické odkazy zobrazením vztahů mezi vzorci a buňkami.
Chcete-li získat přístup k metodám sledování, přejděte na kartu „Vzorce“ a poté ve skupině Audit vzorců klikněte na „Precedenty sledování“ nebo „Závislé na sledování“.
Stopové precedenty
Když vyberete tuto možnost, bude zpětně sledovat buňky, které ovlivňují hodnotu aktivní buňky. Vykreslí modrou čáru, která označuje, které buňky ovlivňují aktuální buňku. Klávesová zkratka pro použití precedentů trasování je Alt + T U T.
V níže uvedeném příkladu ukazuje modrá šipka buňky, které ovlivňují hodnotu B6, jsou B1:B6 a A5. Jak můžete vidět níže, buňka B6 je také součástí vzorce, což z ní dělá kruhový odkaz a způsobí, že vzorec jako výsledek vrátí „0“.
To lze snadno opravit nahrazením B6 za B5 v argumentu SUM: =SUM(B1:B5).
Stopové závislé
Funkce trasování závislé sleduje buňky, které jsou závislé na vybrané buňce. Tato funkce nakreslí modrou čáru označující, které buňky jsou ovlivněny vybranou buňkou. To znamená, že zobrazuje, které buňky obsahují vzorce, které odkazují na aktivní buňku. Klávesová zkratka pro použití závislých je Alt + T U D.
V následujícím příkladu je buňka D3 ovlivněna B4. Jeho hodnota závisí na B4, aby přinesla výsledky. Závislý na stopě tedy nakreslí modrou čáru od B4 do D3, což naznačuje, že D3 je závislý na B4.
Záměrné používání kruhových odkazů v Excelu
Záměrné použití kruhových odkazů se nedoporučuje, ale mohou nastat některé vzácné případy, kdy budete potřebovat kruhový odkaz, abyste mohli získat požadovaný výstup.
Pojďme si to vysvětlit na příkladu.
Chcete-li začít, povolte v sešitu aplikace Excel „Iterativní výpočet“. Jakmile povolíte Iterativní výpočet, můžete začít používat kruhové odkazy ve svůj prospěch.
Předpokládejme, že kupujete dům a chcete svému agentovi dát 2% provizi z celkových nákladů na dům. Celkové náklady budou vypočítány v buňce B6 a procento provize (poplatek agentovi) se vypočítá v B4. Provize se počítá z celkových nákladů a celkové náklady zahrnují provizi. Vzhledem k tomu, že buňky B4 a B6 na sobě závisí, vytváří kruhovou referenci.
Zadejte vzorec pro výpočet celkových nákladů do buňky B6:
=SUM(B1:B4)
Protože celkové náklady zahrnují poplatek za agenta, zahrnuli jsme do výše uvedeného vzorce B4.
Pro výpočet Agent Fee ve výši 2% vložte tento vzorec do B4:
=B6*2%Nyní vzorec v buňce B4 závisí na hodnotě B6 pro výpočet 2 % z celkového poplatku a vzorec v B6 závisí na B4 pro výpočet celkových nákladů (včetně poplatku za agenta), proto kruhový odkaz.
Pokud je povolen iterativní výpočet, Excel vám ve výsledku nedá varování ani 0. Místo toho bude výsledek buněk B6 a B4 vypočítán, jak je uvedeno výše.
Možnost iterativních výpočtů je obvykle ve výchozím nastavení zakázána. Pokud jste jej nezapnuli a když zadáte vzorec do B4, vytvoří se kruhová reference. Excel vydá varování a když klepnete na „OK“, zobrazí se šipka traceru.
A je to. To bylo vše, co potřebujete vědět o cyklických odkazech v Excelu.