Jak najít kruhové odkazy v Excelu

Při práci s , se můžete občas setkat s tím, co je známé jako kruhový odkaz. To je, když vzorec v buňce odkazuje zpět na sebe, což způsobuje nekonečnou smyčku v procesu výpočtu.

Chcete-li najít cyklické odkazy v Excelu, použijte nástroj „Kontrola chyb“ na kartě „Vzorce“ k identifikaci buněk s kruhovou závislostí.

Tento článek ukazuje, jak najít cyklické odkazy v Excelu a opravit je. Dozvíte se také, jak jim předcházet při práci s velkými nebo složitými listy.

Obsah

• Co jsou kruhové odkazy
• Okno kruhové referenční chybové zprávy
• Jak najít kruhové odkazy v Excelu
• Jak najít kruhové odkazy pomocí stavového řádku aplikace Excel
• Jak najít skryté kruhové odkazy v Excelu
  • Jak používat nástroj Trace Dependent Tool
  • Nástroj sledování precedentů
• 2 způsoby, jak odstranit kruhové odkazy v aplikaci Excel
  • 2. Použijte alternativní vzorce
• 5 tipů, jak zabránit kruhovým odkazům
  • 1. Buďte opatrní při výběru rozsahů
  • 2. Pečlivě plánujte své vzorce
  • 3. Použijte alternativní vzorce
  • 4. Rozdělte složité vzorce na menší části
  • 5. Opravte problémy za pochodu
• Jak povolit iterativní výpočet a proč se obtěžovat?

Co jsou kruhové odkazy

Kruhové odkazy se v aplikaci Excel vyskytují, když výpočet vzorce v buňce závisí na vlastní hodnotě buňky. To vytváří smyčku a vede k nesprávným nebo nepředvídatelným výsledkům.

To je snazší pochopit na jednoduchém příkladu.

Předpokládejme, že máte dvě čísla v buňkách A1 a B1. Chcete v buňce A3 použít vzorec, který sečte dvě čísla dohromady. Vzorec v buňce A3 by měl vypadat takto:

=A1 + B1

Pokud však uděláte chybu a zahrnete do výpočtů C1, máte nyní kruhový odkaz:

=A1 + B1 + C1

To může být zjevný problém, kterému je třeba se vyhnout při práci se dvěma buňkami. Když však pracujete s velkými rozsahy buněk, chyby jsou pravděpodobnější.

Naštěstí je Excel navržen tak, aby takové situace řešil zobrazením varovné zprávy, když je detekován cyklický odkaz.

V některých případech se však můžete setkat s cyklickým odkazem, aniž byste obdrželi jakékoli upozornění. Proto je důležité vědět, jak tyto problémy najít a vyřešit ručně.

Okno kruhové referenční chybové zprávy

Ve výchozím nastavení Excel sleduje cyklické odkazy při zadávání vzorců do buněk. Když v buňce s problémem stisknete klávesu Enter, ve vyskakovacím okně se zobrazí chybová zpráva kruhového odkazu.

Toto je vysvětlující text:

Existuje jeden nebo více kruhových odkazů, kde vzorec odkazuje přímo nebo nepřímo na vlastní buňku. To by mohlo způsobit jejich nesprávný výpočet. Zkuste odstranit nebo změnit tyto odkazy nebo přesunout vzorce do jiných buněk.

Když se zobrazí toto dialogové okno, měli byste kliknout na „OK“ a problém vyřešit pomocí metod v tomto článku.

Chybu však nemusíte hned opravovat. S tabulkou můžete pokračovat v práci s úmyslem vyřešit problém později.

V takovém případě musíte vědět, jak najít cyklické odkazy v Excelu poté, co jste zavřeli varovné pole.

Jak najít kruhové odkazy v Excelu

Excel má vestavěný nástroj pro kontrolu chyb, který najde několik druhů chyb, včetně cyklických odkazů. K nástroji se dostanete na kartě Vzorce.

Tento obrázek ukazuje umístění ve skupině Auditování vzorců.

Nyní víte, jak nástroj najít, zde jsou podrobné kroky k jeho použití:

1. Přejděte na kartu Vzorce.

1. Klikněte na rozevírací seznam Kontrola chyb ve skupině Auditování vzorců.

2. Z rozevíracího seznamu vyberte „Circular References“.

3. Kliknutím na odkaz přeskočíte kurzor na první buňku s problémem.

Obrázek níže ukazuje, že nástroj našel kruhovou chybu v buňce G1. Zobrazená buňka je hypertextový odkaz.

Když kliknete na odkaz, kurzor se přesune na buňku. To je velmi užitečné při velkém objemu dat.

Jakmile vyřešíte první kruhový odkaz, můžete se vrátit k nástroji Kontrola chyb a najít další na listu.

Pomocí kroků v dalších částech tohoto článku problém vyřešte a pomocí nástroje přejděte k dalšímu problému.

Pokud již neexistují žádné kruhové problémy, položka nabídky „Kruhové odkazy“ v rozevírací nabídce bude zašedlá. Nemáte žádnou další práci.

Jak najít kruhové odkazy pomocí stavového řádku aplikace Excel

Dalším způsobem, jak zjistit kruhový odkaz, je podívat se na stavový řádek v dolní části listu aplikace Excel.

Pokud je v listu jeden nebo více problémů, stavový řádek zobrazí umístění odkazu na jednu problémovou buňku.

Pokud je například v buňce C1 kruhový odkaz a s tabulkou nejsou žádné další problémy, na stavovém řádku se zobrazí: „Kruhové odkazy: C1“.

Tento obrázek ukazuje, kde vidíte displej.

Jak najít skryté kruhové odkazy v Excelu

Skryté kruhové odkazy v jsou kruhové odkazy, které nejsou okamžitě detekovány vestavěnými chybovými funkcemi. Jinými slovy, Excel nezobrazuje standardní varování.

Stávají se, když buňka odkazuje na jinou buňku nepřímo, prostřednictvím řady mezilehlých buněk, a nakonec odkazuje zpět na původní buňku.

Excel má dva nástroje, které vám pomohou sledovat vztahy a zkoumat tyto problémy:

1. Nástroj Trace Dependents

2. Nástroj sledování precedentů

Jak používat nástroj Trace Dependent Tool

Zkušení uživatelé Excelu využívají nástroj Trace Dependents k prozkoumání těchto problémů a opravě cyklických odkazů. Výběrem jedné nebo více buněk a použitím tohoto nástroje vám Excel zobrazí závislé buňky.

Nástroj je dostupný v části Kontrola vzorců na kartě Vzorce.

Chcete-li nástroj použít, postupujte takto:

1. Klikněte na buňku nebo buňky, pro které chcete trasovat závislé osoby.

2. Přejděte na kartu Vzorce na pásu karet aplikace Excel.

3. Klikněte na tlačítko 'Trace Dependents' ve skupině 'Formula Auditing'.

4. Zkontrolujte šipku nebo šipky ukazující závislosti.

Excel zobrazuje šipky od hodnoty aktivní buňky k buňkám, které na ní závisí. Modrá šipka na obrázku níže ukazuje, že buňka D6 je závislá na buňce B2.

Nástroj sledování precedentů

Precedenty jsou opakem závislých. Nástroj Trace Precedents vám umožňuje vybrat buňku se vzorcem a zobrazit všechny buňky, na kterých závisí, tj. precedenty.

Nástroj je dostupný v části Kontrola vzorců na kartě Vzorce.

Chcete-li nástroj použít, postupujte takto:

1. Klikněte na buňku obsahující vzorec, který chcete prozkoumat.

2. Přejděte na kartu Vzorec na pásu karet Excel.

3. Klikněte na tlačítko 'Trace Precedents' ve skupině 'Formula Auditing'.

4. Zkontrolujte šipky trasování, které Excel zobrazuje.

Modré šipky na obrázku níže ukazují, že buňka D6 je závislá na rozsahu buněk A2:B5.

Použití obou nástrojů vám pomůže najít cyklické odkazy aplikace Excel, které nejsou okamžitě zřejmé. Dalším krokem je opravit problém ve vybrané buňce. Poté můžete pokračovat v hledání stejného problému v jiných buňkách.

2 způsoby, jak odstranit kruhové odkazy v aplikaci Excel

Existují dva hlavní způsoby, jak vyřešit cyklické odkazy:

• porušení kruhové reference

• pomocí alternativních vzorců

Pojďme si vysvětlit oba způsoby.

1. Porušení kruhové reference

Přerušení cyklického odkazu v aplikaci Excel je účinný způsob, jak problém vyřešit. To vyžaduje, abyste identifikovali buňky zahrnuté v cyklickém odkazu a podle toho upravili jejich vzorce.

Chcete-li to provést, postupujte podle kroků popsaných výše a vyhledejte adresu buňky.

Poté byste měli prozkoumat vzorec na řádku vzorců. Hledejte odkaz na samotnou buňku.

V našem prvním příkladu kruhového odkazu obsahoval vzorec pro sčítání v buňce C1 odkaz na samotnou buňku:

=A1 + B1 + C1

To lze opravit odstraněním konečného přídavku. Jakmile je tento problém vyřešen, přejděte k vyhledání dalších kruhových odkazů a jejich řešení.

2. Použijte alternativní vzorce

Některé z nejčastějších příčin kruhových odkazů jsou při sčítání nebo průměrování buněk. Chybou je zahrnout do vzorce buňku s výsledky.

V našem předchozím příkladu vzorec používal operátory sčítání k výpočtu součtu rozsahu buněk. Alternativní metodou je použití funkce SUM Excel.

Chcete-li opravit příklad, nahradíte vzorec:

=SUM(A1:B1)

Podobně můžete zprůměrovat rozsah pomocí vzorce jako =(A1+B1)/2. Je však méně pravděpodobné, že funkce AVERAGE povede k chybě.

=PRŮMĚR (A1:B1)

5 tipů, jak zabránit kruhovým odkazům

Chcete-li zabránit cyklickým odkazům v aplikaci Microsoft Excel, je nezbytné jasně porozumět tomu, jak se vaše vzorce vzájemně ovlivňují v listu aplikace Excel.

Identifikací vztahů mezi buňkami a vědomím toho, jak tato spojení spolupracují, se můžete vyhnout vytváření kruhových vzorců.

Zde je našich pět nejlepších tipů, které vám pomohou předejít kruhovým odkazům.

1. Buďte opatrní při výběru rozsahů

Podle mých zkušeností je jednou z nejčastějších příčin kruhových odkazů, když uživatelé rychle vybírají rozsahy, které mají zahrnout do svých vzorců.

Při svislém a/nebo vodorovném přejetí do skupiny buněk je snadné vybrat jednu další buňku, která sousedí se správným rozsahem. Pokud je tato buňka také buňkou výsledků, neúmyslně jste vytvořili kruhový odkaz.

K této chybě obvykle dochází při práci s velkými sadami dat, jako je provádění testů chí-kvadrát. Řešením je být opatrný při výběru sortimentu.

2. Pečlivě plánujte své vzorce

Před zadáním vzorců se zamyslete nad logikou vašich výpočtů a nad tím, jak by měly procházet buňkami.

Výpočty si můžete předem zmapovat pomocí nástrojů pro vytváření diagramů nebo tabule.

To vám může pomoci identifikovat všechny možné smyčky, které by mohly vést k kruhovým odkazům.

3. Použijte alternativní vzorce

Některé funkce, jako je INDIRECT nebo OFFSET , mohou při nesprávném použití neúmyslně vytvořit kruhové odkazy.

Zvažte použití alternativních funkcí, které poskytují podobné funkce bez rizika cyklických odkazů.

4. Rozdělte složité vzorce na menší části

Namísto vytváření jediného složitého vzorce s více vnořenými funkcemi zvažte rozdělení výpočtů na menší, lépe ovladatelné části.

To nejen usnadňuje pochopení a údržbu vašeho sešitu, ale také snižuje šance na zavedení kruhového odkazu.

5. Opravte problémy za pochodu

Při práci s velkými nebo komplikovanými sešity je také nezbytné sledovat kruhové odkazy. Namísto ignorování problémů, dokud nebude sestavena velká sada dat a vzorců, je lepší opravit každou chybu, jakmile se objeví.

Můžete se rozhodnout na krátkou chvíli ignorovat kruhovou referenční zprávu. Měli byste však použít funkci Kontrola chyb, abyste dohnali existující cyklické odkazy zanedlouho.

To usnadňuje jejich opravu a zabraňuje jejich vlivům na váš sešit.

Jak povolit iterativní výpočet a proč se obtěžovat?

Obvykle se chcete vyhnout kruhovým odkazům v listu. Existují však některé scénáře, kdy jsou nezbytné.

Například některé inženýrské nebo vědecké výpočty potřebují použít výsledek předchozího výpočtu. Toto je známé jako iterativní řešení.

Každá smyčka zpřesňuje výsledky výpočtu, dokud není dosaženo specifické prahové hodnoty nebo cíle.

V těchto případech je nezbytné povolit Iterativní výpočty v nastavení Excelu, aby bylo umožněno zamýšlené cyklické odkazy. To umožňuje softwaru opakovat výpočty stanovený početkrát a konvergovat k řešení.

Chcete-li, aby kruhový odkaz fungoval v souboru aplikace Excel, postupujte takto:

1. Klepněte na kartu Soubor.

2. Vyberte Možnosti.

3. Vyberte kategorii Vzorce.

4. V části „Možnosti výpočtu“ zaškrtněte políčko „Povolit iterativní výpočet“.

5. Změňte číslo v poli maximálních iterací nebo ponechte jako výchozí hodnotu 100.

6. Použijte pole maximální změny ke snížení počtu iterací podle vašich požadavků.