Závislosti a chybová hlášení v Excelu - Užitečné věci pro Office, šablony, Excel, Word, prezentace
Hledání
Novinky
Návštěvy
Dnes: 2
Celkem: 32022

Závislosti a chybová hlášení v Excelu

  • Skupina Závislosti vzorců
    Skupina Závislosti vzorců
  • Zobrazení předchůdců
    Zobrazení předchůdců
  • Zobrazení následníků
    Zobrazení následníků
  • Hodnota není k dispozici
    Hodnota není k dispozici
  • Změna po úpravě vzorce
    Změna po úpravě vzorce
  • Špatně zadané argumenty
    Špatně zadané argumenty
  • Odstranění buňky B1
    Odstranění buňky B1
  • Chybové hlášení #Číslo!
    Chybové hlášení #Číslo!
  • Práce s oblastmi buněk
    Práce s oblastmi buněk
  • Chybové hlášení s upozorněním
    Chybové hlášení s upozorněním
  • Nabídka upozornění na chybu
    Nabídka upozornění na chybu
  • Příkaz pro hledání chyby
    Příkaz pro hledání chyby

Při výpočtech v programu Excel se lze dopustit dvou základních typů chyb. Jsou to ty, které na sebe upozorní přímo chybovým hlášením nebo ty, které na sebe neupozorňují, ale výsledky vypadají nereálně. 

Jak sledovat vtahy mezi vzorci a buňkami

Při hledání chyb v obou případech lze použít skupinu ikon Závislosti vzorců, která se nachází na kartě Vzorce. Tyto ikony ukážou, jak jsou spolu jednotlivé buňky provázány. To znamená, které buňky jsou používány buňkami jinými, a naopak, na které buňky se daná buňka odkazuje. Stačí si všimnout, že se zde nachází ikony s názvem Předchůdci nebo Následníci

K čemu slouží Předchůdci

Jestliže se kontrolují výsledné hodnoty nebo se hledá chyba, je třeba zjistit buňky, které obsahují hodnoty nebo vzorce, jež ovlivňují daný výsledek. Tedy buňky, na které se daný vzorec odkazuje. Těmto buňkám se říká buňky předcházející neboli předchůdci.

Musí být aktivní ta buňka, pro kterou se kontroluje výsledek či hledá chyba. Poté se na kartě Vzorce klepne na ikonu Předchůdci. Zobrazí se modré šipky závislostí, které ukazují vztah mezi aktivní buňkou a s ní souvisejícími buňkami. Pokud se na vybranou buňku odkazuje buňka v jiném listu nebo sešitu, směřuje od vybrané buňky černá šipka s ikonou listu.

Jestliže se klepne znovu na ikonu Předchůdci, zobrazí se další úroveň šipek ukazující, z kterých hodnot daná buňka vychází. Když budou zobrazeny již všechny úrovně šipek, další se zobrazovat nebudou.

Když se celý vztah prohlédne, snadněji se chyba najde a opraví. A pokud je třeba jednotlivé úrovně šipek postupně zrušit, klepne se jednoduše na ikonu Odebrat šipky.

Kdy se použijí Následníci

Jestliže se ale má měnit v rámci výpočtů v tabulce nějaký koeficient nebo hodnotu procenta pro další výpočty (například cen, úroku či daňových poplatků). Je určitě užitečné vědět, co dalšího změna této hodnoty ovlivní v dané tabulce. To znamená, že je potřeba si vyhledat a označit ty buňky, které se na danou buňku svým vzorcem odkazují.

Je tedy třeba zůstat na této buňce aktivní a poté na kartě Vzorce klepnout na ikonu Následníci. Zobrazí se opět šipky závislostí, které ukážou na buňky, které změna dané buňky může ovlivnit.

Pokud se klepne opakovaně na ikonu Následníci, zobrazí se další úroveň šipek závislostí. Jakmile již budou zobrazeny všechny úrovně šipek, další se zobrazovat nebudou.

Nyní se dá celý vztah aktivní buňky s buňkami souvisejícími lehce prohlédnout, případně obsah zvolených buněk upravit.

Poté, když již šipky nejsou třeba, je lze zase pomocí ikony Odebrat šipky skrýt

Chybová hlášení ve vzorcích

Některé chyby se mohou objevit hned při vytváření vzorců v tabulce jako chybová hlášení v jedné nebo i více buňkách. Existuje deset základních chybových hlášení, s kterými se v Excelu lze setkat. Objeví se vždy v buňkách obsahující chybné vzorce a v buňkách se vzorci, které se odkazují na chybné vzorce nebo v buňkách se špatnými odkazy. 

####

Tato chyba vznikne tehdy, když není sloupec dost široký a číselnou hodnotu nelze uvnitř buňky zobrazit. Stačí tedy jen sloupec rozšířit případně použít jiný, vhodnější číselný formát pro daný údaj. Druhou příčinou chyby může být použití záporného data nebo času, proto při odečítání kalendářních dat nebo časových údajů je třeba zkontrolovat správnost vzorce.

#NENÍ_K_DISPOZICI (#N/A)

Tímto chybovým hlášením Excel říká, že hodnota není pro daný vzorec nebo funkci k dispozici.  Může se stát, že hodnota zatím do tabulky nebyla zadána, ale také je možné, že se vynechal některý z argumentů u použité funkce či se argument nebo odkaz na určitý argument nezadal správně. V takovém případě je třeba funkci zkontrolovat a argumenty opravit. Nejčastěji se s ní dá setkat u funkce Svyhledat či Vvyhledat.

#DĚLENÍ_NULOU! (#DIV/0!)

Pokud je snaha někde dělit nulou, tak se zobrazí dané chybové hlášení. Nemusí se jednat jen o přímé dělení nulou, ale také o odkaz na buňku s nulovou hodnotou nebo na buňku prázdnou. To znamená, že v takovém případě je třeba změnit dělitele nebo odkaz na buňku, která obsahuje nulu jako dělitele. 

Pokud se jako dělitel může v některých buňkách tabulky vyskytnout nula, je užitečné použít pro výpočet funkci Když, která dělitele nejprve otestuje a pak se teprve výpočet provede, případně zůstane buňka prázdná.  Funkci Když lze také použít ve tvaru  =když(je.chybndn(vzorec);““;vzorec).  V tomto případě, když Excel zjistí, že výsledkem by bylo chybové hlášení, tak nezobrazí žádný výsledek.

#HODNOTA!

Tato chyba se zobrazí při použití špatného typu argumentu nebo operandu. V takovém případě je třeba vzorec znovu zkontrolovat. Mohli být například zadán text do vzorce nebo špatný odkaz na buňku či vzorec. Také mohla být zadaná špatně oblast argumentu u funkce, například pro funkci, která umí počítat pouze s jednou hodnotou, byla zadána oblast s více buňkami.

#ODKAZ! (#REF!)

Uvedená chyba se zobrazí tehdy, když odkaz ve vzorci na danou buňku není platný (už třeba neexistuje vlivem odstranění nebo nevhodného kopírování vzorce).  Nejčastěji k chybě dochází tehdy, když se odstraní buňka nebo buňky, na které se vzorec v buňce odkazuje. Často se pak toto hlášení objeví ve všech vzorcích závislých na prvně objeveném #ODKAZ! (#REF!).

Někdy stačí opravit odkazy v prvním vzorci a ostatní se poté automaticky opraví sami. Proto před odstraňováním buněk je užitečné, nechat si zobrazit následníky buňky, která má být odstraněna. Tím je hned vidět, co vše se bude odstraněním buňky měnit, a tak se snadno objeví místa, kde může vzniknout chyba.

#NÁZEV? (#NAME?)

Chyba se zobrazí tehdy, jestliže Excel nerozpozná text ve vzorci. Co se mohlo stát? Například vzorec se odkazuje na název oblasti, jejíž pojmenování bylo zrušeno nebo ještě nebylo vytvořeno. Také jen mohla nastat chyba v zápisu názvu oblasti nebo funkce. Často se tato chyba objeví i tehdy, když text používaný ve vzorci se neuzavře do uvozovek nebo při práci s oblastí buněk se zapomene napsat dvojtečka (například se napíše PRŮMĚR(B2B15) místo PRŮMĚR(B2:B15)). Při této chybě je třeba zápis vzorce znovu zkontrolovat a ověřit existenci a správnost použitých názvů.

#ČÍSLO! (#NUM!)

Jestliže se ve vzorci nebo funkci (například je snaha počítat odmocninu ze záporného čísla nebo logaritmus z nuly) použije neplatná číselná hodnota, tak se zobrazí toto hlášení. Může se zobrazit i tehdy, když do funkce zadá nesprávný typ argumentu nebo když se používá cyklické řešení vzorců.

Méně často bývá příčinou této chyby zadání takového vzorce, že výsledek je příliš velký nebo malý a Excel ho neumí zobrazit. Tehdy je třeba vzorec opravit tak, aby výsledek byl mezi hodnotami -1*10307 a 1*10307. To si lze například vyzkoušet vypočítat faktoriál z čísla 300. (=FAKTORIÁL((300)))

#NULL!

Uvedená chyba se může zobrazit při použití nesprávného operátoru oblasti nebo nesprávného odkazu na buňku. Často se objeví při určování průniku dvou polí, které se neprotínají. Potom je třeba odkaz změnit tak, aby se oblasti překrývaly nebo zkontrolovat použité operátory. Pozor, operátorem průniku je mezera mezi oblastmi, ale při sčítání dvou oblastí je třeba použít středník.

#PŘESAH! (#SPILL!)

U nově přidaných maticových funkcí nové generace jako Sort, Sortby nebo Filter se může objevit i nové chybové hlášení, které se ve starších verzích Excelu nenachází. 

Zobrazí se tehdy, když v oblastech pro výpočet u těchto maticových funkcí něco je (nebo je v dané oblasti buňka sloučená).

#VÝPOČET (#CALC! )  

Jedná se také o nové chybové hlášení, které může mít více příčin. Obecně se dá ale říct to, že se zobrazí tehdy, když Excel narazí na typ výpočtu, algoritmus, scénář, který není v současnosti podporován např. u vnořeného pole (matice v matici), u pole oblastí (odkazy na oblast v matici), prázdné pole (u funkce Filter), příliš mnoho buněk (funkce, které odkazují na víc buněk (více než 10 000)). Nejčastěji se s ní dá setkat právě u vnořovaných poli či u polí oblastí.

Jak se pracuje s chybami ve vzorcích

Obsahuje-li buňka vzorec s chybou, zobrazí se mimo chybového hlášení i v levém horním rohu buňky zelený (standardní barva) trojúhelníček. Jestliže se na buňku klepne, zobrazí se vedle buňky žluté tlačítko s vykřičníkem. Pokud se na tlačítko klepne, otevře se nabídka různých příkazů, které mohou pomoci daný problém řešit, přičemž první položka vzniklý problém popisuje.

Pro hledání chyb, lze také využít ikonu Kontrola chyb. Kde, pokud v její nabídce je k dispozici příkaz Najít chybu, stačí na něj klepnout a poté si projít možné příčiny a řešení chyb. 

Pokud tento příkaz není k dispozici, je nejlepší si nechat zobrazit Předchůdce a chybu vyhledat a opravit.

Někdy se však může stát (i když by se nemělo), že při otevření tabulek vytvářených ve starších verzích Excelu se zobrazí chybové hlášení (u buněk jsou malé zelené trojúhelníčky v levém horním rohu), i když jsou vzorce správné. Někdy to souvisí s tím, že je jinak nastavený formát v určitých buňkách a Excelu by se zde líbil formát jiný, někdy pak s tím, že několik vzorců je jiných než vzorce okolní a Excel vyhodnotí, že by to mělo být jinak. Příčin je hned několik.

Takže pokud se ví, že tam je vše v pořádku, tak pro dané buňky se dá zvolit příkaz Ignorovat chybu. Při příštích kontrolách budou tyto údajné chyby již přeskakovány.