Cyklické odkazy v aplikaci Microsoft Excel

Domníváme se, že cyklické odkazy v aplikaci Excel jsou chybným výrazem. Je to docela často, ale stále ne vždy. Někdy se používají zcela vědomě. Zjistíme, jaké jsou kruhové odkazy, jak je vytvářet, jak najít v dokumentu existující dokumenty, jak s nimi pracovat nebo jak je v případě potřeby odstranit.

Použití kruhových odkazů

Nejprve se podívejme, jaký je cyklický odkaz. Ve skutečnosti je to výraz, který se prostřednictvím formulace v jiných buňkách vztahuje na sebe. Také to může být odkaz umístěný v elementu listu, ke kterému odkazuje.

Je třeba poznamenat, že moderní verze aplikace Excel automaticky zablokují proces cyklické operace. To je způsobeno tím, že takové výrazy jsou naprosto nesprávné a smyčka vytváří konstantní proces přepočítávání a výpočtu, což vytváří další zátěž systému.

Vytvoření kruhového odkazu

Teď se podíváme, jak vytvořit nejjednodušší cyklický výraz. Bude to odkaz umístěný ve stejné buňce, na kterou odkazuje.

1. Vyberte prvek listu A1 a do něj zapište následující výraz:

   =A1

   Pak klikněte na tlačítko Enter na klávesnici.

2. Poté se zobrazí dialogové okno pro varování o cyklickém výrazu. Klikněte na tlačítko "OK" .
3. Tak jsme získali cyklickou operaci na listu, ve kterém se buňka označuje sama.

Pojďme trochu komplikovat úkol a vytvořit cyklický výraz z několika buněk.

1. Napište číslo na libovolný prvek listu. Nechť je buňka A1 a číslo 5 .
2. V jiné buňce ( B1 ) píšeme výraz:

   =C1

3. K dalšímu prvku ( C1 ) zapíšeme tento vzorec:

   =A1

4. Poté se vrátíme do buňky A1 , ve které je nastaveno číslo 5 . Odkazujeme na to v prvku B1 :

   =B1

   Stiskněte tlačítko Enter .

5. Cyklus je tedy uzavřen a my jsme získali klasickou cyklickou referenci. Po zavření výstražného okna zjistíme, že program označil cyklický odkaz modrými šipkami na listu, které se nazývají stopové šipky.

Teď pokračujeme k vytvoření cyklického výrazu pomocí tabulky příkladů. Máme stůl na prodej potravin. Skládá se ze čtyř sloupců, které uvádějí název produktu, počet prodaných produktů, cenu a výši výnosu z prodeje celého objemu. V tabulce v posledním sloupci již existují vzorce. V nich se výnos vypočítá vynásobením množství podle ceny.

1. Chcete-li stanovit vzorec v prvním řádku, vyberte prvek listu s množstvím prvního produktu ( B2 ). Namísto statické hodnoty ( 6 ) do ní vložíme vzorec, který bude mít množství zboží rozdělením celkové částky ( D2 ) o cenu ( C2 ):

   =D2/C2

   Klepněte na tlačítko Enter .

2. Máme první cyklické spojení, jehož vztah je obvykle označován šipkou stopy. Ale jak vidíte, výsledek je chybný a rovná se nule, protože už bylo řečeno, Excel zablokuje provádění cyklických operací.
3. Zkopírujte výraz na všechny ostatní buňky ve sloupci s počtem produktů. Chcete-li to provést, nastavte kurzor do pravého dolního rohu prvku, který již obsahuje vzorec. Kurzor je převeden na kříž, který se obvykle nazývá značka naplnění. Slepte levým tlačítkem myši a přetáhněte tento kříž na konec tabulky dolů.
4. Jak vidíte, výraz byl zkopírován do všech prvků sloupce. Existuje však pouze jeden vztah označený šipkou stopy. Uvědomme si to pro budoucnost.

Vyhledání kruhových odkazů

Jak jsme viděli výše, ne vždy program označuje vztah kruhového odkazu na objekty, i když existuje na listu. Vzhledem k tomu, že převážná většina cyklických operací je škodlivá, je třeba je odstranit. Ale za tím musí být nejprve nalezeny. Jak lze toto provést, pokud výrazy nejsou označeny šipkou? Pojďme se zabývat tímto úkolem.

1. Pokud otevřete informační okno při spuštění souboru aplikace Excel, který obsahuje kruhový odkaz, měli byste ho najít. Chcete-li to provést, přejděte na kartu "Formuláře" . Klikněte na pásku na trojúhelníku, která se nachází napravo od tlačítka "Kontrola chyb" , umístěného v poli nástrojů "Formula Dependencies" . Otevře se menu, ve kterém můžete přesunout kurzor na položku "Cyklické odkazy" . Poté se v dalším menu otevře seznam adres prvků listů, ve kterých program nalezl cyklické výrazy.
2. Po klepnutí na konkrétní adresu je vybrána odpovídající buňka na listu.

Existuje další způsob, jak zjistit, kde se nachází kruhové spojení. Zpráva o tomto problému a adresa prvku obsahujícího tento výraz se nachází na levé straně stavového řádku, který se nachází v dolní části okna aplikace Excel. Nicméně na rozdíl od předchozí varianty se na stavovém řádku zobrazí adresy ne všech prvků obsahujících kruhové odkazy, pokud je spousta, ale pouze jedna z nich, která se objevila před ostatními.

Navíc pokud jste v knize obsahující cyklický výraz, nikoliv na listu, kde se nachází, ale na druhé straně se v stavovém řádku zobrazí pouze chybová zpráva bez adresy.

Lekce: Jak najít kruhovou referenci v aplikaci Excel

Opravy cyklických vazeb

Jak již bylo uvedeno výše, v převážné většině případů jsou cyklické operace zlem, z něhož by se člověk měl zbavit. Proto je přirozené, že po objevení cyklické komunikace je nutné jej opravit, aby se vzorec dostal do normální podoby.

Abychom napravili cyklickou závislost, je nutné vysledovat celé propojení buněk. I když kontrola určila konkrétní buňku, chyba nemusí být sama o sobě, ale v jiném prvku řetězce závislosti.

1. V našem případě, přestože program správně ukázal na jednu z buněk smyčky ( D6 ), skutečná chyba leží v jiné buňce. Vyberte prvek D6 a zjistěte, od kterých buněk vytáhne hodnotu. Podívejte se na výraz v řádku vzorců. Jak je vidět, hodnota v tomto prvku listu je tvořena vynásobením obsahu buněk B6 a C6 .
2. Jděte do buňky C6 . Vyberte jej a podívejte se na řádek vzorce. Jak vidíte, je to obvyklá statická hodnota ( 1000 ), která není výsledkem výpočtu vzorce. Proto můžeme s jistotou říci, že zadaný prvek neobsahuje chybu, která způsobuje vytváření cyklických operací.
3. Přejděte na další buňku ( B6 ). Po výběru ve vzorci vidíme, že obsahuje vypočtený výraz ( = D6 / C6 ), který vytahuje data z jiných prvků tabulky, zejména z buňky D6 . Proto se buňka D6 vztahuje k datům prvku B6 a naopak, což způsobuje fixaci.

   Zde jsme vypočítali poměr poměrně rychle, ale ve skutečnosti existují případy, kdy se ve výpočtovém procesu účastní mnoho buněk, a ne tři prvky, jako my. Poté může vyhledávání trvat poměrně dlouho, protože musíte studovat každý prvek cyklickosti.

4. Nyní musíme pochopit, která buňka ( B6 nebo D6 ) obsahuje chybu. Přestože to není ani formální chyba, nýbrž prosté nadměrné používání odkazů, které vedou ke smyčce. Během procesu rozhodování, který buňku je třeba upravit, musíte použít logiku. Neexistuje žádný jasný algoritmus akce. V každém případě bude tato logika vlastní.

   Například pokud v naší tabulce celková částka by měla být vypočtena vynásobením množství skutečně prodávaného zboží jeho cenou, pak můžeme říci, že odkaz, který vypočítává částku z celkové částky prodeje, je zjevně nadbytečný. Proto jej odstraníme a nahradíme ho statickou hodnotou.

5. Provádíme podobnou operaci na všech ostatních cyklických výrazech, pokud existují na listu. Poté, co byly z knihy odstraněny všechny kruhovité odkazy, zpráva o přítomnosti tohoto problému by měla zmizet ze stavového řádku.

   Kromě toho, pokud byly cyklické výrazy zcela smazány, zjistíte pomocí nástroje pro kontrolu chyb. Přejděte na kartu "Formuláře" a klikněte na trojúhelník, který již víme, napravo od tlačítka "Kontrola chyb" na panelu nástrojů "Formuláře závislostí" . Není-li položka "Cyklické odkazy" v nabídce aktivní, pak jsme z dokumentu odstranili všechny takové objekty. V opačném případě budete muset použít postup odstranění položek, které jsou v seznamu, stejným způsobem jako dříve.

Povolení cyklických operací

V předchozí části lekce jsme se zabývali především tím, jak řešit cyklické odkazy nebo jak je najít. Ale dříve se konverzace týkala také skutečnosti, že v některých případech mohou být užitečné a vědomě používané uživatelem. Například tato metoda je často používána pro iterativní výpočty při konstrukci ekonomických modelů. Problém je však v tom, že bez ohledu na to, zda vědomě nebo nevědomě používáte cyklický výraz, aplikace Excel ve výchozím nastavení stále blokuje operaci na nich, aby nedošlo k nadměrnému přetížení systému. V takovém případě se otázka násilného vypnutí takového zámku stane aktuální. Uvidíme, jak to udělat.

1. Nejprve se přesuneme na kartu "Soubor" aplikace aplikace Excel.
2. Dále klikněte na položku "Možnosti" , která se nachází na levé straně okna, které se otevře.
3. Otevře se okno Možnosti aplikace Excel. Musíme jít na kartu "Formule" .
4. V okně se otevře, že budete moci provádět cyklické operace. Přesuneme se do pravého bloku tohoto okna, kde se nacházejí nastavení aplikace Excel. Budeme pracovat s konfiguračním blokem "Možnosti výpočtu" , který je umístěn na vrcholu.

   Chcete-li povolit použití cyklických výrazů, zaškrtněte políčko vedle možnosti Povolit výpočty . Kromě toho můžete ve stejném bloku nastavit maximální počet iterací a relativní chybu. Ve výchozím nastavení se jejich hodnoty rovnají 100 a 0,001. Ve většině případů tyto parametry nemusíte měnit, ačkoli je možné provést změny v zadaných polích, je-li to nutné nebo pokud je to požadováno. Zde je však třeba vzít v úvahu, že příliš mnoho iterací může vést k vážnému zatížení programu a systému jako celku, zvláště pokud pracujete se souborem obsahujícím mnoho cyklických výrazů.

   Takže zaškrtněte volbu "Povolit iterativní výpočty" a potom klepněte na tlačítko "OK" umístěné v dolní části okna možností aplikace Excel, aby se nové nastavení projevilo.

5. Poté automaticky přejdeme na list aktuální knihy. Jak je vidět, v buňkách, ve kterých jsou umístěny cyklické vzorce, jsou nyní hodnoty vypočítány správně. Program neblokuje výpočty v nich.

Je však třeba poznamenat, že začlenění cyklických operací by nemělo být zneužíváno. Tuto funkci použijte pouze tehdy, pokud je uživatel plně přesvědčen o své potřebě. Nepřiměřené zahrnutí cyklických operací může nejen vést k nadměrnému zatížení systému a zpomalit výpočet při práci s dokumentem, ale uživatel může neúmyslně zadat chybný cyklický výraz, který program implicitně zablokuje.

Jak vidíme, v převážnou většině případů jsou cyklické odkazy fenoménem, ​​který je třeba řešit. Chcete-li to provést, nejprve byste měli najít nejvíce cyklický vztah, pak vypočítat buňku, kde je chyba obsažena, a nakonec ji odstranit vhodnými úpravami. V některých případech však cyklické operace mohou být užitečné pro výpočty a vědomě provedené uživatelem. Ale i tak je dobré je používat s opatrností, správným nastavením aplikace Excel a znalostí rozsahu přidávání takových odkazů, které při hromadném použití mohou zpomalit systém.

• ← Formuláře zadávání dat v aplikaci Microsoft Excel
• Obnova chybějících listů v aplikaci Microsoft Excel →