Při práci se vzorci aplikace Excel se vám může někdy zobrazit následující varovná výzva.
Tato výzva vám řekne, že je v listu kruhový odkaz, což může vést k nesprávnému výpočtu podle vzorců. Také vás požádá, abyste tento problém s kruhovými referencemi vyřešili a roztřídili.
V tomto tutoriálu se budu zabývat vším, co potřebujete vědět o kruhovém odkazu, a také jak najít a odstranit kruhové odkazy v aplikaci Excel.
Začněme tedy!
Co je to kruhová reference v Excelu?
Jednoduše řečeno, kruhový odkaz se stane, když v buňce skončíte se vzorcem - který sám používá k výpočtu buňku (do které byla zadána).
Pokusím se to vysvětlit na jednoduchém příkladu.
Předpokládejme, že máte datovou sadu v buňce A1: A5 a v buňce A6 použijete níže uvedený vzorec:
= SUM (A1: A6)
Tím získáte kruhové referenční varování.
Důvodem je, že chcete sečíst hodnoty v buňce A1: A6 a výsledek by měl být v buňce A6.
Tím se vytvoří smyčka, protože Excel stále přidává novou hodnotu do buňky A6, která se stále mění (tedy kruhová referenční smyčka).
Jak najít kruhové reference v Excelu?
Přestože je upozornění na kruhový odkaz dostatečně laskavé, aby vám řeklo, že existuje ve vašem listu, neřekne vám, kde se to děje a jaké odkazy na buňky to způsobují.
Pokud se tedy pokoušíte najít a zpracovat kruhové odkazy v listu, musíte znát způsob, jak je nějak najít.
Níže jsou uvedeny kroky k nalezení kruhové reference v aplikaci Excel:
- Aktivujte list s kruhovou referencí
- Klikněte na kartu Vzorce
- Ve skupině Vzorec úpravy klikněte na rozevírací ikonu Kontrola chyb (malá šipka směřující dolů vpravo)
- Umístěte kurzor na možnost Kruhové odkazy. Ukáže vám buňku, která má v listu kruhovou referenci
- Klikněte na adresu buňky (která se zobrazí) a dostanete se do buňky v listu.
Jakmile problém vyřešíte, můžete znovu postupovat podle výše uvedených kroků a zobrazí více odkazů na buňky, které mají kruhový odkaz. Pokud žádný není, neuvidíte žádný odkaz na buňku,
Další rychlý a snadný způsob, jak najít kruhovou referenci, je podívat se na stavový řádek. V levé části vám ukáže text Kruhový odkaz spolu s adresou buňky.
Při práci s kruhovými referencemi byste měli vědět několik věcí:
- V případě, že je povolen iterativní výpočet (popsaný dále v tomto kurzu), stavový řádek nezobrazí kruhovou adresu referenční buňky
- V případě, že kruhový odkaz není v aktivním listu (ale v jiných listech ve stejném sešitu), zobrazí se pouze kruhový odkaz, nikoli adresa buňky
- V případě, že jednou dostanete varovnou výzvu s kruhovým odkazem a vy ji odmítnete, příště se výzva znovu nezobrazí.
- Pokud otevřete sešit s kruhovou referencí, zobrazí se výzva, jakmile se sešit otevře.
Jak odebrat kruhovou referenci v Excelu?
Jakmile zjistíte, že ve vašem listu jsou kruhové odkazy, je čas je odstranit (pokud nechcete, aby tam z nějakého důvodu byly).
Bohužel to není tak jednoduché, jako stisknout klávesu Delete. Protože tyto jsou závislé na vzorcích a každý vzorec je jiný, musíte to analyzovat případ od případu.
V případě, že problém způsobí omylem odkaz na buňku, můžete jej jednoduše opravit úpravou odkazu.
Ale někdy to není tak jednoduché.
Kruhový odkaz může být také způsoben několika buňkami, které se do sebe vkládají na mnoha úrovních.
Ukážu vám příklad.
Níže je v buňce C6 kruhová reference, ale není to jen jednoduchý případ sebereference. Je to víceúrovňové, kde buňky, které používá ve výpočtech, na sebe také odkazují.
- Vzorce v buňce A6 jsou = SUMA (A1: A5)+C6
- Vzorec je buňka C1 je = A6*0,1
- Vzorec v buňce C6 je = A6+C1
Ve výše uvedeném příkladu je výsledek v buňce C6 závislý na hodnotách v buňce A6 a C1, které jsou zase závislé na buňce C6 (což způsobuje kruhovou referenční chybu)
A opět jsem vybral opravdu jednoduchý příklad jen pro ukázkové účely. Ve skutečnosti by to mohlo být docela obtížné zjistit a možná i daleko ve stejném pracovním listu nebo dokonce roztroušených na více pracovních listech.
V takovém případě existuje jeden způsob, jak identifikovat buňky, které způsobují kruhový odkaz, a poté je ošetřit.
Je to pomocí možnosti Trace Precedents.
Níže jsou uvedeny kroky k použití precedentů trasování k nalezení buněk, které jsou napájeny buňkou, která má kruhovou referenci:
- Vyberte buňku, která má kruhovou referenci
- Klikněte na kartu Vzorce
- Klikněte na Precedensy trasování
Výše uvedené kroky vám ukážou modré šipky, které vám řeknou, jaké buňky se vkládají do vzorce ve vybrané buňce. Tímto způsobem můžete zkontrolovat vzorce a buňky a zbavit se kruhové reference.
V případě, že pracujete se složitými finančními modely, je možné, že tyto precedenty také zasahují do několika úrovní.
To funguje dobře, pokud máte všechny vzorce odkazující na buňky ve stejném listu. Pokud je to ve více listech, tato metoda není účinná.
Jak povolit/zakázat iterativní výpočty v aplikaci Excel
Když máte v buňce kruhový odkaz, nejprve se zobrazí varovná výzva, jak je uvedeno níže, a pokud toto dialogové okno zavřete, výsledkem bude 0 v buňce.
Důvodem je, že když existuje kruhový odkaz, je to nekonečná smyčka a Excel to nechce dohnat. Vrátí tedy 0.
Ale v některých případech můžete skutečně chtít, aby byl kruhový odkaz aktivní a provedli několik iterací. V takovém případě můžete místo nekonečné smyčky rozhodnout, kolikrát by měla být smyčka spuštěna.
Tomu se říká iterativní výpočet v Excelu.
Níže jsou uvedeny kroky k povolení a konfiguraci iteračních výpočtů v aplikaci Excel:
- Klikněte na kartu Soubor
- Klikněte na Možnosti. Tím se otevře dialogové okno Možnosti aplikace Excel
- V levém podokně vyberte vzorec
- V části Možnosti výpočtu zaškrtněte políčko „Povolit iterativní výpočet“. Zde můžete zadat maximální počet iterací a maximální hodnotu změny
A je to! Výše uvedené kroky by umožnily iterativní výpočet v aplikaci Excel.
Dovolte mi také rychle vysvětlit dvě možnosti v iteračním výpočtu:
- Maximální počet iterací: Toto je maximální počet případů, které chcete, aby Excel vypočítal, než vám poskytne konečný výsledek. Pokud to tedy zadáte jako 100, Excel spustí smyčku 100krát, než vám poskytne konečný výsledek.
- Maximální změna: Toto je maximální změna, která by v případě, že by nebyla dosažena mezi iteracemi, byla zastavena. Ve výchozím nastavení je hodnota 0,001. Čím nižší je tato hodnota, tím přesnější bude výsledek.
Pamatujte, že čím vícerát se iterace spustí, tím více času a prostředků to Excel potřebuje. Pokud ponecháte maximální počet iterací na vysoké úrovni, může to vést ke zpomalení nebo zhroucení aplikace Excel.
Poznámka: Jsou -li povoleny iterativní výpočty, Excel vám nezobrazí výzvu s kruhovým odkazem a nyní ji také zobrazí ve stavovém řádku.Schválně pomocí kruhových odkazů
Ve většině případů by byla přítomnost kruhového odkazu v listu chybou. A proto vám Excel zobrazí výzvu, která zní: „Zkuste tyto odkazy odstranit nebo změnit nebo přesunout vzorce do jiných buněk.“
Mohou však existovat určité konkrétní případy, kdy potřebujete kruhovou referenci, abyste mohli dosáhnout požadovaného výsledku.
Jeden takový konkrétní případ, o kterém jsem již psal, o získání časového razítka v buňce v buňce v aplikaci Excel.
Předpokládejme například, že chcete vytvořit vzorec, aby se každý záznam provedl v buňce ve sloupci A, časové razítko se zobrazilo ve sloupci B (jak je uvedeno níže):
Časové razítko můžete snadno vložit pomocí následujícího vzorce:
= IF (A2 "", IF (B2 "", B2, NOW ()), "")
Problém s výše uvedeným vzorcem je ten, že by aktualizoval všechna časová razítka, jakmile se v listu provede nějaká změna, nebo pokud se list znovu otevře (protože vzorec NOW je nestálý)
Chcete -li tento problém vyřešit, můžete použít kruhovou referenční metodu. Použijte stejný vzorec, ale povolte iterativní výpočet.
Existují i další případy, kdy je žádoucí mít schopnost používat kruhovou referenci (jeden příklad najdete zde).
Poznámka: I když mohou existovat případy, kdy můžete použít kruhovou referenci, považuji za nejlepší se jí vyhnout. Kruhové odkazy mohou také mít vliv na výkon vašeho sešitu a mohou jej zpomalit. Ve vzácných případech, kdy to potřebujete, vždy dávám přednost použití kódů VBA k dokončení práce.Doufám, že jste našli tento návod užitečný!
Mohly by se vám hodit další tutoriály k Excelu:
- #REF! Chyba v Excelu; Jak opravit referenční chybu!
- Zpracování chyb aplikace Excel VBA
- Použijte IFERROR s VLOOKUP a zbavte se #N/A chyb
- Jak odkazovat na jiný list nebo sešit v aplikaci Excel (s příklady)
- Absolutní, relativní a smíšené odkazy na buňky v Excelu
