Cyklické odkazy v programe Microsoft Excel

Domnievame sa, že cyklické odkazy v programe Excel sú nesprávnym výrazom. Skutočne to je dosť často, ale stále nie vždy. Niekedy sa používajú celkom vedome. Dozvieme sa, aké sú kruhové odkazy, ako ich vytvoriť, ako nájsť existujúce dokumenty v dokumente, ako s nimi pracovať, alebo ako ich odstrániť, ak je to potrebné.

Použitie kruhových odkazov

Najprv zistíme, aký je cyklický odkaz. V skutočnosti je to výraz, ktorý sa pomocou formulí v iných bunkách vzťahuje na seba. Tiež to môže byť odkaz nachádzajúci sa v prvku listu, na ktorý odkazuje.

Treba poznamenať, že moderné verzie programu Excel automaticky zablokujú proces vykonávania cyklických operácií. Je to spôsobené tým, že takéto výrazy sú prevažne chybné a slučky vytvárajú konštantný proces prepočítania a výpočtu, čo vytvára dodatočné zaťaženie systému.

Vytvorenie kruhového odkazu

Teraz sa pozrime, ako vytvoriť najjednoduchší cyklický výraz. Toto bude odkaz umiestnený v tej istej bunke, na ktorú odkazuje.

1. Vyberte element listu A1 a napíšte nasledujúci výraz:

   =A1

   Potom kliknite na tlačidlo Enter na klávesnici.

2. Potom sa zobrazí dialógové okno s upozornením na cyklický výraz. Kliknite na tlačidlo "OK" .
3. Tak sme získali cyklickú operáciu na plátku, v ktorom sa bunka vzťahuje na seba.

Poďme trochu komplikovať úlohu a vytvoriť cyklický výraz z niekoľkých buniek.

1. Napíšte číslo ľubovoľnému prvku listu. Nech je to bunka A1 a číslo 5 .
2. V inej bunke ( B1 ) píšeme výraz:

   =C1

3. Do nasledujúceho prvku ( C1 ) píšeme nasledujúci vzorec:

   =A1

4. Potom sa vrátime do bunky A1 , v ktorej je nastavené číslo 5 . Odkazujeme na to v prvku B1 :

   =B1

   Stlačte tlačidlo Enter .

5. Takže cyklus je uzavretý a získali sme klasický cyklický odkaz. Po zatvorení výstražného okna vidíme, že program označil cyklický odkaz modrými šípkami na hárku, ktoré sa nazývajú stopové šípky.

Teraz začneme vytvoriť cyklický výraz pomocou tabuľky príkladov. Máme tabuľku na predaj potravín. Skladá sa zo štyroch stĺpcov, ktoré označujú názov výrobku, počet predaných výrobkov, cenu a výšku tržieb z predaja celého objemu. V tabuľke v poslednom stĺpci už existujú vzorce. V nich sa výnos vypočíta vynásobením množstva cenou.

1. Ak chcete opraviť vzorec na prvom riadku, vyberte prvok hárku s množstvom prvého produktu ( B2 ). Namiesto statickej hodnoty ( 6 ) vložíme vzorec, ktorý vezme množstvo tovaru rozdelením celkovej sumy ( D2 ) o cenu ( C2 ):

   =D2/C2

   Klikneme na tlačidlo Enter .

2. Máme prvé cyklické spojenie, vzťah, v ktorom je zvyčajne označený šípkou stopy. Ale ako vidíte, výsledok je chybný a rovný nule, pretože už bolo povedané, Excel zablokuje vykonanie cyklických operácií.
3. Skopírujte výraz do všetkých ostatných buniek v stĺpci s počtom produktov. Ak to chcete urobiť, nastavte kurzor do pravého dolného rohu elementu, ktorý už obsahuje vzorec. Kurzor sa premení na kríž, ktorý sa zvyčajne nazýva značka naplnenia. Zatvorte ľavé tlačidlo myši a potiahnite tento kríž na koniec tabuľky.
4. Ako môžete vidieť, výraz bol skopírovaný do všetkých prvkov stĺpca. Existuje však iba jeden vzťah označený šípkou stopy. Poznamenávme to v budúcnosti.

Hľadanie kruhových odkazov

Ako sme videli vyššie, nie vždy program označuje vzťah kruhového odkazu na objekty, aj keď existuje na hárku. Vzhľadom na skutočnosť, že drvivá väčšina cyklických operácií je škodlivá, je potrebné ich odstrániť. Ale za to musia byť najprv nájdené. Ako sa to dá urobiť, ak výrazy nie sú označené šípkou? Vypracujme túto úlohu.

1. Ak otvoríte informačné okno pri spustení súboru programu Excel, ktorý obsahuje kruhový odkaz, mali by ste ho nájsť. Preto sa presunieme na kartu "Vzorce" . Kliknite na pásik na trojuholníku, ktorý sa nachádza napravo od tlačidla "Skontrolujte chyby" nachádzajúce sa v poli "Formulár závislostí" . Otvorí sa ponuka, v ktorej sa kurzor presunie na položku "Cyklické prepojenia" . Potom nasledujúca ponuka otvorí zoznam adries prvkov listov, v ktorých program našiel cyklické výrazy.
2. Keď kliknete na konkrétnu adresu, vyberie sa príslušná bunka na hárku.

Existuje ďalší spôsob, ako zistiť, kde sa nachádza kruhový odkaz. Správa o tomto probléme a adresa prvku obsahujúceho takýto výraz sa nachádza na ľavej strane stavového riadka, ktorá sa nachádza v spodnej časti okna programu Excel. Avšak na rozdiel od predchádzajúceho variantu sa na stavovom riadku zobrazia adresy nie všetkých prvkov obsahujúcich kruhové odkazy, ak je ich veľa, ale iba jedna z nich, ktorá sa objavila pred ostatnými.

Okrem toho, ak sa nachádzate v knihe obsahujúcej cyklický výraz, nie na hárku, na ktorom sa nachádza, na druhej strane sa v stavovom riadku zobrazí iba chybové hlásenie bez adresy.

Lekcia: Ako nájsť kruhové odkazy v programe Excel

Oprava cyklických odkazov

Ako už bolo uvedené vyššie, vo väčšine prípadov sú cyklické operácie zlo, z ktorého by sa človek mal zbaviť. Preto je prirodzené, že po objavení cyklickej komunikácie je potrebné ju opraviť, aby sa vzorec dostal do normálnej podoby.

Aby bolo možné opraviť cyklickú závislosť, je potrebné vysledovať celé prepojenie buniek. Aj keď kontrola špecifikovala konkrétnu bunku, chyba nemusí byť sama o sebe, ale v inom prvku reťazca závislosti.

1. V našom prípade napriek skutočnosti, že program správne ukázal na jednu z buniek slučky ( D6 ), skutočná chyba leží v inej bunke. Vyberte element D6 a zistite, z ktorých buniek vytiahne hodnotu. Pozrite sa na výraz v riadku vzorcov. Ako vidíte, hodnota v tomto prvku listu sa vytvorí vynásobením obsahu buniek B6 a C6 .
2. Prejdite do bunky C6 . Vyberte ho a pozrite sa na riadok vzorca. Ako vidíte, je to obvyklá statická hodnota ( 1000 ), ktorá nie je výsledkom výpočtu vzorca. Preto môžeme s istotou povedať, že zadaný prvok neobsahuje chybu, ktorá spôsobuje vytvorenie cyklických operácií.
3. Prejdite na ďalšiu bunku ( B6 ). Po výbere vo vzorcovom riadku vidíme, že obsahuje vypočítaný výraz ( = D6 / C6 ), ktorý ťahá dáta z iných prvkov tabuľky, najmä z bunky D6 . Bunka D6 teda odkazuje na údaje elementu B6 a naopak, čo spôsobuje fixáciu.

   Tu sme vypočítali pomer pomerne rýchlo, ale v skutočnosti existujú prípady, keď v procese výpočtu existuje veľa buniek, a nie tri prvky, ako máme. Potom môže vyhľadávanie trvať pomerne dlho, pretože musíte študovať každý prvok cyklickosti.

4. Teraz musíme pochopiť, ktorá bunka ( B6 alebo D6 ) obsahuje chybu. Hoci formálne to nie je ani chyba, ale jednoducho nadmerné používanie odkazov, ktoré vedú k slučke. Počas procesu rozhodovania, ktorý bunku chcete upraviť, musíte použiť logiku. Neexistuje žiadny jasný algoritmus akcie. V každom prípade bude táto logika vlastná.

   Napríklad, ak v našej tabuľke celková čiastka by mala byť vypočítaná vynásobením množstva skutočne predaného tovaru za jeho cenu, potom môžeme povedať, že odkaz, ktorý vypočítava sumu z celkovej predajnej sumy, je zjavne nadbytočný. Preto ho vymažeme a nahradíme ho statickou hodnotou.

5. Vykonávame podobnú operáciu na všetkých ostatných cyklických výrazoch, ak existujú na hárku. Po odstránení všetkých kruhových odkazov z knihy by správa o výskyte tohto problému mala zmiznúť zo stavovej lišty.

   Okrem toho, ak boli cyklické výrazy úplne odstránené, môžete ich zistiť pomocou nástroja na kontrolu chýb. Prejdite na kartu "Formuláre" a kliknite na trojuholník, ktorý už poznáme, napravo od tlačidla "Skontrolujte chyby" na paneli s nástrojmi "Formuláre závislostí" . Ak položka "Cyklické odkazy" nie je v ponuke aktívna, odstránili sme z dokumentu všetky takéto objekty. V opačnom prípade budete musieť použiť postup odstránenia na položky, ktoré sú v zozname, rovnakým spôsobom ako predtým.

Povolenie cyklických operácií

V predchádzajúcej časti lekcie sme hovorili hlavne o tom, ako riešiť cyklické odkazy alebo ako ich nájsť. Ale skôr sa rozhovor týkal aj skutočnosti, že v niektorých prípadoch môžu byť naozaj užitočné a vedome používané používateľom. Napríklad, táto metóda sa často používa pri iteračných výpočtoch pri konštrukcii ekonomických modelov. Ale problém je, že bez ohľadu na to, či vedome alebo nevedome používate cyklický výraz, program Excel predvolene naďalej blokuje operáciu na nich, aby nedochádzalo k nadmernému preťaženiu systému. V tomto prípade sa stane naliehavou otázkou násilného zablokovania takého zámku. Pozrime sa, ako to urobiť.

1. Najskôr prejdeme na kartu Súbor aplikácie programu Excel.
2. Potom kliknite na položku "Možnosti" , ktorá sa nachádza na ľavej strane okna, ktoré sa otvorí.
3. Otvorí sa okno možností programu Excel. Musíme prejsť na kartu "Vzorce" .
4. V okne, ktoré sa otvorí, budete môcť vykonávať cyklické operácie. Presuneme sa do pravého bloku tohto okna, kde sa nachádzajú samotné nastavenia programu Excel. Budeme pracovať s konfiguračným blokom "Možnosti výpočtu" , ktorý sa nachádza na najvyššej úrovni.

   Ak chcete povoliť používanie cyklických výrazov, musíte zaškrtnúť políčko vedľa možnosti Povoliť výpočty . Okrem toho môžete v tom istom bloku nastaviť maximálny počet opakovaní a relatívnu chybu. Predvolene sa ich hodnoty rovnajú 100 a 0,001. Vo väčšine prípadov nemusíte tieto parametre meniť, hoci v prípade potreby môžete vykonať zmeny v zadaných poliach. Tu je však potrebné vziať do úvahy, že príliš veľa opakovaní môže viesť k vážnemu zaťaženiu programu a systému ako celku, najmä ak pracujete so súborom obsahujúcim mnoho cyklických výrazov.

   Takže začiarknite políčko "Povoliť iteračné výpočty" a potom kliknite na tlačidlo "OK" umiestnené v spodnej časti okna možností programu Excel, aby sa nové nastavenia prejavili.

5. Potom prejdeme automaticky na hárok aktuálnej knihy. Ako vidíte, v bunkách, v ktorých sa nachádzajú cyklické vzorce, sú teraz hodnoty vypočítané správne. Program neblokuje výpočty v nich.

Stojí však za zmienku, že začlenenie cyklických operácií by sa nemalo zneužívať. Túto funkciu použite iba vtedy, ak je používateľ plne presvedčený o svojej potrebe. Nesprávne zahrnutie cyklických operácií môže nielen viesť k nadmernému pracovnému zaťaženiu systému a spomaliť výpočet pri práci s dokumentom, ale používateľ môže neúmyselne vložiť nesprávny cyklický výraz, ktorý program bude okamžite zablokovaný.

Ako vidíme, v prevažnej väčšine prípadov sú cyklické odkazy fenoménom, ktorý je potrebné riešiť. Ak chcete to urobiť, najprv by ste mali nájsť najviac cyklický vzťah, potom vypočítať bunku, kde je chyba obsiahnutá, a napokon ju odstrániť vhodnými úpravami. Ale v niektorých prípadoch môžu byť cyklické operácie užitočné vo výpočtoch a vedome vykonané používateľom. Ale aj vtedy stojí za to ich používať opatrne, správne nastaviť Excel a poznať rozsah pridania takýchto prepojení, ktoré pri hromadnom použití môžu spomaliť systém.

• ← Formuláre zadávania údajov v programe Microsoft Excel
• Obnovenie chýbajúcich hárkov v programe Microsoft Excel →