Pri práci so vzorcami programu Excel sa vám môže niekedy zobraziť nasledujúce upozornenie.
Táto výzva vám povie, že vo vašom hárku je kruhový odkaz, čo môže viesť k nesprávnemu výpočtu alebo vzorcom. Tiež vás žiada, aby ste vyriešili tento problém s obežnými referenciami a vyriešili ho.
V tomto tutoriále sa budem zaoberať všetkým, čo potrebujete vedieť o kruhovej referencii a tiež ako nájsť a odstrániť kruhové odkazy v programe Excel.
Začnime teda!
Čo je to kruhová referencia v programe Excel?
Jednoducho povedané, kruhový odkaz sa stane, keď máte v bunke vzorec - ktorý sám používa na výpočet bunku (do ktorej bola zadaná).
Pokúsim sa to vysvetliť na jednoduchom príklade.
Predpokladajme, že máte množinu údajov v bunke A1: A5 a v bunke A6 používate nasledujúci vzorec:
= SUM (A1: A6)
Tým získate kruhové referenčné upozornenie.
Dôvodom je, že chcete sčítať hodnoty v bunke A1: A6 a výsledok by mal byť v bunke A6.
To vytvára slučku, pretože Excel stále pridáva novú hodnotu do bunky A6, ktorá sa stále mení (teda kruhová referenčná slučka).
Ako nájsť kruhové referencie v programe Excel?
Aj keď je výzva s upozornením na kruhové referencie taká láskavá, že vám povie, že existuje vo vašom pracovnom hárku, nehovorí vám, kde sa to deje a aké odkazy na bunky to spôsobujú.
Ak sa teda pokúšate nájsť a spracovať kruhové odkazy v pracovnom hárku, musíte vedieť, ako ich nejako nájsť.
Nasledujú kroky na nájdenie kruhového odkazu v programe Excel:
- Aktivujte pracovný hárok, ktorý má kruhovú referenciu
- Kliknite na kartu Vzorce
- V skupine Úpravy vzorcov kliknite na rozbaľovaciu ikonu Kontrola chýb (malá šípka smerujúca nadol vpravo)
- Ukážte kurzorom na možnosť Kruhové referencie. Ukáže vám bunku, ktorá má v pracovnom hárku kruhovú referenciu
- Kliknite na adresu bunky (ktorá je zobrazená) a dostanete sa do bunky v pracovnom hárku.
Keď ste problém vyriešili, môžete znova postupovať podľa rovnakých krokov vyššie a zobrazí sa viac odkazov na bunky, ktoré majú kruhový odkaz. Ak neexistuje, neuvidíte žiadny odkaz na bunku,
Ďalším rýchlym a jednoduchým spôsobom, ako nájsť kruhovú referenciu, je pohľad na stavový riadok. V ľavej časti vám ukáže text Kruhový odkaz spolu s adresou bunky.
Pri práci s kruhovými referenciami musíte vedieť niekoľko vecí:
- V prípade, že je povolený iteračný výpočet (popísaný neskôr v tomto návode), v stavovom riadku sa nezobrazí kruhová adresa referenčnej bunky
- V prípade, že kruhový odkaz nie je v aktívnom hárku (ale v iných listoch toho istého zošita), zobrazí sa iba kruhový odkaz a nie adresa bunky
- V prípade, že raz dostanete upozornenie na kruhovú referenciu a zrušíte ho, nabudúce sa už výzva nezobrazí.
- Ak otvoríte zošit, ktorý má kruhovú referenciu, zobrazí sa výzva hneď, ako sa zošit otvorí.
Ako odstrániť kruhovú referenciu v programe Excel?
Keď zistíte, že sa vo vašom hárku nachádzajú kruhové odkazy, je načase ich odstrániť (pokiaľ ich z nejakého dôvodu nechcete mať).
Bohužiaľ to nie je také jednoduché ako stlačenie klávesu Delete. Pretože tieto závisia od vzorcov a každý vzorec je iný, musíte to analyzovať od prípadu k prípadu.
V prípade, že ide iba o to, že problém omylom spôsobí odkaz na bunku, môžete to jednoducho opraviť úpravou referencie.
Ale niekedy to nie je také jednoduché.
Kruhový odkaz môže byť tiež spôsobený niekoľkými bunkami, ktoré sa navzájom napájajú na mnohých úrovniach.
Ukážem vám príklad.
Nižšie je v bunke C6 kruhová referencia, ale nie je to len jednoduchý prípad vlastnej referencie. Je viacúrovňový, kde bunky, ktoré používa vo výpočtoch, na seba tiež odkazujú.
- Vzorce v bunke A6 sú = SUM (A1: A5)+C6
- Vzorec je bunka C1 je = A6*0,1
- Vzorec v bunke C6 je = A6+C1
Vo vyššie uvedenom príklade je výsledok v bunke C6 závislý od hodnôt v bunkách A6 a C1, ktoré sú zase závislé od bunky C6 (čo spôsobuje kruhovú referenčnú chybu)
A opäť som si vybral skutočne jednoduchý príklad len na ukážkové účely. V skutočnosti to môže byť dosť ťažké zistiť a možno ďaleko v tom istom pracovnom hárku alebo dokonca rozptýlené vo viacerých pracovných hárkoch.
V takom prípade existuje jeden spôsob, ako identifikovať bunky, ktoré spôsobujú kruhovú referenciu, a potom ich liečiť.
Je to pomocou možnosti Trace Precedents.
Nasledujú kroky na použitie precedensov na nájdenie buniek, ktoré sa napájajú do bunky s kruhovým odkazom:
- Vyberte bunku, ktorá má kruhovú referenciu
- Kliknite na kartu Vzorce
- Kliknite na položku Predchádzajúce stopy
Vyššie uvedené kroky by vám ukázali modré šípky, ktoré vám povedia, aké bunky sa vkladajú do vzorca vo vybratej bunke. Týmto spôsobom môžete skontrolovať vzorce a bunky a zbaviť sa kruhovej referencie.
V prípade, že pracujete s komplexnými finančnými modelmi, je možné, že tieto precedensy siahajú aj do viacerých úrovní.
To funguje dobre, ak máte všetky vzorce odkazujúce na bunky v tom istom hárku. Ak je to vo viacerých pracovných listoch, táto metóda nie je účinná.
Ako povoliť/zakázať iteračné výpočty v programe Excel
Keď máte v bunke kruhovú referenciu, zobrazí sa najskôr výzva s upozornením, ako je uvedené nižšie, a ak zatvoríte toto dialógové okno, v bunke sa zobrazí 0.
Je to preto, že keď existuje kruhový odkaz, je to nekonečná slučka a Excel ho nechce zachytiť. Vráti teda 0.
Ale v niektorých prípadoch môžete skutočne chcieť, aby bol kruhový odkaz aktívny a urobili niekoľko iterácií. V takom prípade môžete namiesto nekonečnej slučky rozhodnúť, koľkokrát by sa mala slučka spustiť.
Toto sa volá iteračný výpočet v programe Excel.
Nasledujú kroky na povolenie a konfiguráciu iteračných výpočtov v programe Excel:
- Kliknite na kartu Súbor
- Kliknite na Možnosti. Tým sa otvorí dialógové okno Možnosti programu Excel
- Na ľavom paneli vyberte položku Vzorec
- V časti Možnosti výpočtu začiarknite políčko „Povoliť iteračný výpočet“. Tu môžete zadať maximálne iterácie a maximálnu hodnotu zmeny
To je všetko! Vyššie uvedené kroky by umožnili iteračný výpočet v programe Excel.
Dovoľte mi tiež rýchlo vysvetliť dve možnosti v iteratívnom výpočte:
- Maximálny počet iterácií: Toto je maximálny počet prípadov, ktoré chcete, aby Excel vypočítal, než vám poskytne konečný výsledok. Ak to teda zadáte ako 100, Excel spustí slučku 100 -krát, než vám poskytne konečný výsledok.
- Maximálna zmena: Toto je maximálna zmena, ktorá ak sa nedosiahne medzi iteráciami, výpočet by sa zastavil. Štandardne je hodnota 0,001. Čím je táto hodnota nižšia, tým bude výsledok presnejší.
Nezabudnite, že čím viac krát sa iterácie spustia, tým viac času a zdrojov potrebuje Excel na to, aby to zvládol. V prípade, že ponecháte maximálne množstvo iterácií, môže to viesť k spomaleniu alebo zlyhaniu programu Excel.
Poznámka: Keď sú povolené iteračné výpočty, Excel vám nezobrazí výzvu na kruhovú referenčnú referenciu a teraz ju zobrazí aj v stavovom riadku.Zámerne pomocou kruhových referencií
Vo väčšine prípadov by bola prítomnosť kruhového odkazu vo vašom hárku chybou. A preto vám Excel zobrazí výzvu, ktorá znie: „Skúste odstrániť alebo zmeniť tieto odkazy alebo presunúť vzorce do rôznych buniek.“
Môžu však existovať konkrétne prípady, keď potrebujete kruhovú referenciu, aby ste dosiahli požadovaný výsledok.
Jeden taký konkrétny prípad, o ktorom som už písal, je získanie časovej pečiatky v bunke v bunke v programe Excel.
Predpokladajme napríklad, že chcete vytvoriť vzorec, aby sa každý záznam vykonal v bunke v stĺpci A, časová pečiatka sa zobrazila v stĺpci B (ako je uvedené nižšie):
Aj keď môžete vložiť časovú pečiatku ľahko, použite nasledujúci vzorec:
= IF (A2 "", IF (B2 "", B2, NOW ()), "")
Problém s vyššie uvedeným vzorcom je, že by aktualizoval všetky časové pečiatky hneď po vykonaní akejkoľvek zmeny v pracovnom hárku alebo po opätovnom otvorení pracovného hárka (pretože vzorec TERAZ je prchavý)
Na vyriešenie tohto problému môžete použiť kruhovú referenčnú metódu. Použite rovnaký vzorec, ale povoľte iteračný výpočet.
Existuje aj niekoľko ďalších prípadov, kde je potrebné mať možnosť používať kruhovú referenciu (jeden príklad nájdete tu).
Poznámka: Aj keď môžu existovať prípady, kedy môžete použiť kruhovú referenciu, považujem za najlepšie vyhnúť sa jej používaniu. Kruhové odkazy môžu tiež ovplyvniť výkon vášho zošita a môžu ho spomaliť. V zriedkavých prípadoch, keď to potrebujete, vždy uprednostním použitie kódov VBA na vykonanie práce.Dúfam, že ste našli tento návod užitočný!
Užitočné môžu byť aj ďalšie návody na Excel:
- #REF! Chyba v programe Excel; Ako opraviť referenčnú chybu!
- Spracovanie chýb programu Excel VBA
- Použite IFERROR s VLOOKUP a zbavte sa #N/A chýb
- Ako odkazovať na iný list alebo zošit v programe Excel (s príkladmi)
- Absolútne, relatívne a zmiešané bunkové referencie v programe Excel
