Jedným z najbežnejších upozornení na chyby, s ktorými sa používatelia v Exceli stretávajú, je „Circular Reference“. Tisíce používateľov majú rovnaký problém a vyskytuje sa, keď vzorec priamo alebo nepriamo odkazuje späť na svoju vlastnú bunku, čo spôsobuje nekonečnú slučku výpočtov.
Napríklad máte dve hodnoty v bunkách B1 a B2. Keď vzorec =B1+B2 zadáte do B2, vytvorí sa kruhový odkaz; vzorec v B2 sa opakovane prepočítava, pretože pri každom výpočte sa hodnota B2 zmenila.
Väčšina kruhových odkazov sú neúmyselné chyby; Excel vás na to upozorní. Existujú však aj zamýšľané kruhové odkazy, ktoré sa používajú na iteračné výpočty. Neúmyselné kruhové odkazy v pracovnom hárku môžu spôsobiť nesprávny výpočet vzorca.
Preto v tomto článku vysvetlíme všetko, čo potrebujete vedieť o kruhových odkazoch a tiež o tom, ako nájsť, opraviť, odstrániť a použiť kruhové odkazy v Exceli.
Ako nájsť a spracovať kruhový odkaz v Exceli
Pri práci s Excelom sa niekedy stretávame s chybami kruhového odkazu, ku ktorým dochádza, keď zadávate vzorec, ktorý obsahuje bunku, v ktorej sa nachádza váš vzorec. V podstate sa to stane, keď sa váš vzorec pokúša vypočítať sám.
Napríklad v bunke A1:A4 máte stĺpec čísel a v bunke A5 používate funkciu SUM (=SUM(A1:A5)). Bunka A5 priamo odkazuje na svoju vlastnú bunku, čo je nesprávne. Preto dostanete nasledujúce kruhové referenčné varovanie:
Keď dostanete vyššie uvedené varovné hlásenie, môžete kliknúť na tlačidlo „Pomocník“, aby ste sa dozvedeli viac o chybe, alebo zavrieť okno chybového hlásenia kliknutím na tlačidlo „OK“ alebo „X“ a ako výsledok získať „0“.
Niekedy môžu kruhové referenčné slučky spôsobiť zlyhanie výpočtu alebo spomalenie výkonu pracovného hárka. Kruhový odkaz môže tiež viesť k množstvu ďalších problémov, ktoré nebudú zrejmé okamžite. Preto je najlepšie sa im vyhnúť.
Priame a nepriame obežníky
Kruhové odkazy možno rozdeliť do dvoch typov: priame kruhové odkazy a nepriame kruhové odkazy.
Priama referencia
Priamy kruhový odkaz je celkom jednoduchý. Výstražné hlásenie priameho kruhového odkazu sa objaví, keď vzorec priamo odkazuje späť na svoju vlastnú bunku.
V nižšie uvedenom príklade vzorec v bunke A2 priamo odkazuje na svoju vlastnú bunku (A2).
Keď sa objaví varovné hlásenie, môžete kliknúť na „OK“, ale výsledkom bude iba „0“.
Nepriamy kruhový odkaz
Nepriamy kruhový odkaz v Exceli sa vyskytuje, keď hodnota vo vzorci odkazuje späť na svoju vlastnú bunku, ale nie priamo. Inými slovami, kruhový odkaz môže byť tvorený dvoma bunkami, ktoré sa navzájom odkazujú.
Poďme si to vysvetliť na tomto jednoduchom príklade.
Teraz hodnota začína od A1, ktorá má hodnotu 20.
Ďalej bunka C3 odkazuje na bunku A1.
Potom bunka A5 odkazuje na bunku C3.
Teraz nahraďte hodnotu 20 v bunke A1 vzorcom, ako je uvedené nižšie. Každá ďalšia bunka je závislá od bunky A1. Keď použijete odkaz na akúkoľvek inú predchádzajúcu bunku vzorca v A1, spôsobí to cyklické upozornenie na odkaz. Pretože vzorec v A1 odkazuje na bunku A5, ktorá odkazuje na C3, a bunka C3 odkazuje späť na A1, teda kruhový odkaz.
Keď kliknete na „OK“, výsledkom bude hodnota 0 v bunke A1 a Excel vytvorí prepojený riadok zobrazujúci precedensy sledovania a závislosti od sledovania, ako je znázornené nižšie. Túto funkciu môžeme použiť na jednoduché vyhľadanie a opravu/odstránenie kruhových odkazov.
Ako povoliť / zakázať kruhové odkazy v Exceli
V predvolenom nastavení sú iteračné výpočty v Exceli vypnuté (zakázané). Iteratívne výpočty sú opakujúce sa výpočty, kým nesplní špecifickú podmienku. Keď je zakázaná, Excel zobrazí správu Kruhový odkaz a ako výsledok vráti 0.
Niekedy sú však na výpočet slučky potrebné kruhové referencie. Ak chcete použiť kruhový odkaz, musíte vo svojom Exceli povoliť iteračné výpočty, ktoré vám umožnia vykonávať výpočty. Teraz vám ukážeme, ako môžete povoliť alebo zakázať iteračné výpočty.
V Exceli 2010, Exceli 2013, Exceli 2016, Exceli 2019 a Microsoft 365 prejdite na kartu „Súbor“ v ľavom hornom rohu Excelu a potom kliknite na „Možnosti“ na ľavej table.
V okne Možnosti programu Excel prejdite na kartu „Vzorec“ a začiarknite políčko „Povoliť iteračný výpočet“ v časti „Možnosti výpočtu“. Potom kliknutím na „OK“ uložte zmeny.
To umožní iteračný výpočet a tým umožní kruhový odkaz.
Ak to chcete dosiahnuť v predchádzajúcich verziách programu Excel, postupujte takto:
- V Exceli 2007 kliknite na tlačidlo Office > Možnosti Excelu > Vzorce > oblasť Iterácia.
- V Exceli 2003 a starších verziách musíte prejsť do ponuky > Nástroje > Možnosti > karta Výpočet.
Maximálne iterácie a maximálne parametre zmeny
Keď povolíte iteračné výpočty, môžete ovládať iteračné výpočty zadaním dvoch možností dostupných v časti Povoliť iteračný výpočet, ako je znázornené na obrázku nižšie.
- Maximálne iterácie – Toto číslo udáva, koľkokrát by sa mal vzorec prepočítať, kým získate konečný výsledok. Predvolená hodnota je 100. Ak ju zmeníte na „50“, Excel zopakuje výpočty 50-krát, kým vám poskytne konečný výsledok. Pamätajte, že čím vyšší je počet iterácií, tým viac zdrojov a času je potrebný na výpočet.
- Maximálna zmena – Určuje maximálnu zmenu medzi výsledkami výpočtu. Táto hodnota určuje presnosť výsledku. Čím menšie číslo, tým presnejší bude výsledok a tým dlhšie bude výpočet pracovného hárka trvať.
Ak je povolená možnosť iteračných výpočtov, nedostanete žiadne upozornenie, keď sa vo vašom hárku nachádza kruhový odkaz. Interaktívny výpočet povoľte len vtedy, keď je to absolútne nevyhnutné.
Nájdite kruhový odkaz v Exceli
Predpokladajme, že máte veľkú množinu údajov a dostali ste varovanie s kruhovou referenciou, stále budete musieť zistiť, kde (v ktorej bunke) sa chyba vyskytla, aby ste ju mohli opraviť. Ak chcete nájsť cyklické odkazy v Exceli, postupujte takto:
Používanie nástroja na kontrolu chýb
Najprv otvorte pracovný hárok, kde sa kruhový odkaz stal. Prejdite na kartu „Vzorec“, kliknite na šípku vedľa nástroja „Kontrola chýb“. Potom stačí umiestniť kurzor myši na možnosť „Kruhové odkazy“, Excel vám zobrazí zoznam všetkých buniek, ktoré sú zahrnuté v kruhovom odkaze, ako je uvedené nižšie.
Kliknite na ktorúkoľvek adresu bunky, ktorú chcete v zozname, a presmeruje vás na túto adresu bunky, aby ste problém vyriešili.
Používanie stavového riadka
Kruhový odkaz nájdete aj na stavovom riadku. Na stavovom riadku Excelu sa zobrazí najnovšia adresa bunky s kruhovým odkazom, napríklad „Kruhové odkazy: B6“ (pozri snímku obrazovky nižšie).
Pri manipulácii s kruhovým odkazom by ste mali vedieť niekoľko vecí:
- Keď je povolená možnosť Iteratívneho výpočtu, v stavovom riadku sa nezobrazí adresa bunky kruhového odkazu, takže ho musíte vypnúť skôr, ako začnete v zošite hľadať kruhové odkazy.
- V prípade, že sa v aktívnom hárku nenájde kruhový odkaz, v stavovom riadku sa zobrazí iba „Kruhové odkazy“ bez adresy bunky.
- Výzva s kruhovým odkazom sa zobrazí iba raz a po kliknutí na tlačidlo „OK“ sa výzva nabudúce už nezobrazí.
- Ak má váš zošit kruhové odkazy, zobrazí sa výzva pri každom jeho otvorení, kým nevyriešite kruhový odkaz alebo kým nezapnete iteračný výpočet.
Odstráňte kruhový odkaz v Exceli
Nájdenie kruhových referencií je jednoduché, ale opraviť ich nie je také jednoduché. Bohužiaľ, v Exceli neexistuje žiadna možnosť, ktorá by vám umožnila odstrániť všetky kruhové odkazy naraz.
Ak chcete opraviť kruhové odkazy, musíte nájsť každý kruhový odkaz jednotlivo a pokúsiť sa ho upraviť, úplne odstrániť kruhový vzorec alebo ho nahradiť iným.
Niekedy v jednoduchých vzorcoch stačí upraviť parametre vzorca tak, aby sa nevracal späť na seba. Napríklad zmeňte vzorec v B6 na =SUM(B1:B5)*A5 (zmena B6 na B5).
Výsledok výpočtu vráti ako „756“.
V prípadoch, keď je ťažké nájsť cyklický odkaz Excelu, môžete použiť funkcie Sledovať precedensy a Sledovať závislé na jeho spätné vysledovanie k zdroju a vyriešenie jedného po druhom. Šípka ukazuje, ktoré bunky sú ovplyvnené aktívnou bunkou.
Existujú dve metódy sledovania, ktoré vám môžu pomôcť odstrániť kruhové odkazy zobrazením vzťahov medzi vzorcami a bunkami.
Ak chcete získať prístup k metódam sledovania, prejdite na kartu „Vzorce“ a potom kliknite na položku „Precedenty sledovania“ alebo „Závislé na sledovaní“ v skupine Audit vzorcov.
Stopové precedensy
Keď vyberiete túto možnosť, spätne sleduje bunky, ktoré ovplyvňujú hodnotu aktívnej bunky. Nakreslí modrú čiaru označujúcu, ktoré bunky ovplyvňujú aktuálnu bunku. Klávesová skratka na použitie precedensov sledovania je Alt + T U T.
V nižšie uvedenom príklade modrá šípka ukazuje bunky, ktoré ovplyvňujú hodnotu B6, sú B1:B6 a A5. Ako vidíte nižšie, bunka B6 je tiež súčasťou vzorca, čo z nej robí kruhový odkaz a spôsobí, že vzorec vo výsledku vráti „0“.
To sa dá ľahko opraviť nahradením B6 za B5 v argumente SUM: =SUM(B1:B5).
Stopové závislé
Funkcia trace dependents sleduje bunky, ktoré sú závislé od vybranej bunky. Táto funkcia nakreslí modrú čiaru označujúcu, ktoré bunky sú ovplyvnené vybratou bunkou. To znamená, že zobrazuje, ktoré bunky obsahujú vzorce, ktoré odkazujú na aktívnu bunku. Klávesová skratka na použitie závislých je Alt + T U D.
V nasledujúcom príklade je bunka D3 ovplyvnená B4. Pri dosahovaní výsledkov závisí od hodnoty B4. Závislý od stopy teda nakreslí modrú čiaru od B4 do D3, čo naznačuje, že D3 je závislý od B4.
Zámerné používanie kruhových odkazov v Exceli
Zámerné používanie kruhových odkazov sa neodporúča, ale môžu sa vyskytnúť ojedinelé prípady, keď potrebujete kruhový odkaz, aby ste mohli získať požadovaný výstup.
Vysvetlíme si to na príklade.
Ak chcete začať, povoľte v zošite programu Excel „Iteratívny výpočet“. Keď povolíte Iteratívny výpočet, môžete začať používať kruhové referencie vo svoj prospech.
Predpokladajme, že kupujete dom a chcete svojmu agentovi dať 2% províziu z celkových nákladov na dom. Celkové náklady sa vypočítajú v bunke B6 a percento provízie (poplatok za agenta) sa vypočíta v B4. Provízia sa počíta z celkových nákladov a celkové náklady zahŕňajú aj províziu. Keďže bunky B4 a B6 na sebe závisia, vytvára to kruhový odkaz.
Do bunky B6 zadajte vzorec na výpočet celkových nákladov:
=SUM(B1:B4)
Keďže celkové náklady zahŕňajú poplatok za agenta, do vyššie uvedeného vzorca sme zahrnuli B4.
Ak chcete vypočítať poplatok za agenta vo výške 2 %, vložte tento vzorec do B4:
=B6*2%Teraz vzorec v bunke B4 závisí od hodnoty B6 na výpočet 2 % z celkového poplatku a vzorec v B6 závisí od B4 na výpočet celkových nákladov (vrátane poplatku za agenta), teda kruhový odkaz.
Ak je povolený iteračný výpočet, Excel vám vo výsledku nedá varovanie ani 0. Namiesto toho sa vypočíta výsledok buniek B6 a B4, ako je uvedené vyššie.
Možnosť iteračných výpočtov je zvyčajne štandardne vypnutá. Ak ste ho nezapli a keď zadáte vzorec do B4, vytvorí sa kruhový odkaz. Excel vydá varovanie a keď kliknete na „OK“, zobrazí sa šípka sledovača.
to je všetko. Toto bolo všetko, čo potrebujete vedieť o kruhových odkazoch v Exceli.