V dnešním videu se podíváme na 10 chybových hlášek v Excelu, které na vás mohou vyskočit u práce s excelovými funkcemi. Ukážeme si, jaké jsou nejčastější důvody vzniku chybových hlášek jako dělení nulou, není k dispozici, hodnota, číslo nebo přesah dat. A hlavně si ukážeme, jak chybové hlášky odstranit. V Excelu se vyskytuje deset chybových hlášek, které na vás mohou vyskočit. Postupně si je projdeme a vysvětlíme si, proč vznikají a co s nimi můžete udělat.
Excelový soubor ke stažení
Chybové hlášky v Excelu
A teď se pojďme podívat na jednotlivé chybové hlášky v Excelu.
#DĚLENÍ_NULOU! / #DIV/0
První chybová hláška, která na vás v Excelu může vyskočit je chybová hláška dělení nulou. Jak název napovídá, objeví se tato chybová hláška ve chvíli, kdy v Excelu dělíte jiné číslo nulou. V tabulce máme dva sloupce hodnot. Hodnoty mezi sebou vydělíme. Vzorec potvrdíme a stáhneme dolů. Na některých řádcích se objevila chybová hláška dělení nulou a jsou to ty řádky, kde jsme dělili nulou. Abyste se této chybové hlášce vyhnuli, tak můžete buď vzorec zabalit do excelové funkce IFERROR, ve které určíte, co se má stát, pokud vzorec vrátí chybu. Zabalíme tedy vzorec do IFERROR, kde hodnotou je vzorec a jako druhý parametr vyplníme, co se má vrátit při chybě, třeba nula. Potvrdíme a stáhneme vzorec dolů a chybové hlášky zmizely. Příznivci funkce KDYŽ mohou zabalit vzorec do funkce KDYŽ, kde podmínkou bude, že pokud se jmenovatel rovná nule, tak chceme vrátit nulu, v opačném případě chceme spočítat náš vzorec. Funkci potvrdíme a pošleme ji dolů.
#NENÍ_K_DISPOZICI / #N/A
Další běžnou chybovou hláškou je chyba není k dispozici. Tato chybová hláška se nejčastěji objeví, kdy Excel nemůže najít hledanou hodnotu. Důvodů může být několik. Základní důvody si ukážeme na následujícím příkladu s vyhledáváním. V příkladu máme zdrojovou tabulku a vedle tabulky chceme z tabulky vyhledat hodnoty pro produkty C a A. K vyhledání použijeme funkci SVYHLEDAT. Napíšeme funkci SVYHLEDAT, kde jako co hledáme označíme produkt, produkt hledáme v tabulce, odpověď se nachází ve druhém sloupci a hledáme přesnou shodu. Funkci potvrdíme a vrátila se správná hodnota. Pokud bychom teď funkci stáhli o řádek níže pro produkt A, tak se vrátí tato chyba. A je to proto, protože jsme nezafixovali buňky a funkce se posunula spolu s potažením dolů. Když do funkce klikneme, zjistíme, že se rozsah tabulky posunul a tím pádem teď nezahrnuje produkt A. Chybu napravíme tak, že se vrátíme k funkci a zafixujeme buňky tabulky klávesou F4. Funkci potvrdíme, stáhneme dolů a teď se vrací správný výsledek.
Stejně tak by se tato chyba objevila, pokud bychom produkt A změnily na produkt Z, který v tabulce vůbec není. Řešením je zabalit funkci SVYHLEDAT například do funkce IFERROR, kde opět specifikujeme, co se má stát, pokud funkce produkt nenajde. Například, že chceme vrátit slovo Nenalezeno v uvozovkách. Potvrdíme a chybová hláška zmizela.
Chybová hláška není k dispozici se objeví i v případě, že hledané slovo špatně napíšete, a to včetně nadbytečných mezer. Napíšeme za hledaný produkt A mezeru navíc, potvrdíme a funkce SVYHLEDAT vrátí chybovou hlášku není k dispozici, jelikož se nadbytečná mezera bere jako součást slova. Řešením je použít ve funkci SVYHLEDAT na hledaný produkt funkci PROČISTIT, která z buňky odstraní všechny nadbytečné mezery.
#NÁZEV? / #NAME?
Chybová hláška název se objeví většinou ve chvíli, kdy jsme udělali nějaký překlep v názvu excelové funkce. Pokud například na součet následujících hodnot použijeme funkci SUMA, kterou ale napíšeme špatně jako třeba SUM, a funkci potvrdíme, tak se vrátí chybová hláška název. A značí to, že jsme udělali chybu v názvu funkce. Ta samá chybová hláška se vám vrátí ve chvíli, kdy použijete jiný název funkce pro svou verzi Excelu. Klasicky, když zaměníte anglický název funkce s českým názvem. Třeba u funkce SVYHLEDAT. Pokud v české verzi Excelu použiji například místo funkce SVYHLEDAT funkci VLOOKUP, tak jelikož je tento název pro moji verzi Excelu nesprávný, tak se vrátí chybová hláška název. Jediným řešením je vrátit se k funkci a opravit její název.
#HODNOTA! / #VALUE!
Chybová hláška hodnota se objeví většinou tehdy, když máme nesoulad v datových typech. Jako například v dalším příkladu. Máme zde malou tabulku hodnot, kde opět hodnoty mezi sebou dělíme. Nicméně v tabulce se nám mezi čísly připletly i textové hodnoty. Po vydělení hodnot se na některých řádcích objeví chybová hláška hodnota, která nám oznamuje, že máme nesoulad v datových typech. V tomto případě nemůžeme počítat s textovou hodnotou. Řešením je buď vyčistit zdrojová data, aby se datové typy ve sloupcích shodovaly a nebo opět zabalit vzorec nebo funkci do jedné z excelových funkcí, které si umí s chybovými hláškami poradit.
##########
Někdy se vám mohou v buňce objevit pouze křížky. Nejedná se tak docela o chybovou hlášku, ale o to, že šířka buňky neodpovídá obsahu v buňce. Tedy, že šířka buňky není dostatečná. Jako v následujícím příkladu, kde v buňce máme křížky. Když klikneme na buňku, tak zjistíme, že v ní máme datum včetně času. Obsah buňky je tedy moc dlouhý. Řešením je šířku buňky přizpůsobit. Buď si můžeme zvolit přesnou šířku sloupce tak, že na něj klikneme pravým tlačítkem myši a vybereme šířka sloupce a nebo na sloupec dvakrát poklikáme, čímž se jeho šířka automaticky přizpůsobí obsahu buňky.
#NULL!
Chybová hláška NULL není velmi častá. Někteří z vás na ní možná ani nikdy nenarazily. Tato chybová hláška se většinou objeví, pokud středník nebo dvojtečku ve funkci nahradíte mezerou. Pokud bychom v následujícím příkladu chtěli sečíst tyto dvě hodnoty pomocí funkce SUMA, a místo dvojtečky nebo čárky mezi buňkami napsali ve funkci mezeru a funkci potvrdili, tak se vrátí chybová hláška NULL. Pokud funkci SUMA opravíme a buňky klasicky označíme, tak funkce SUMA vrátí správný součet hodnot.
#ODKAZ! / #REF!
Chybová hláška odkaz se objeví většinou ve chvíli, kdy je nějaká chyba v odkazu ve funkci. Klasicky se vám to může stát, když smažete buňku, na kterou se odkazujete v nějakém vzorci nebo funkci. Máme zde tři hodnoty, které vedle v buňce sčítáme. Pokud smažu jeden z řádků, tak se místo součtu objeví chybová hláška odkaz. Když klikneme na vzorec, tak zjistíme, že místo jednoho odkazu na buňku je chyba, jelikož jsme si jednu z buněk smazali. Řešením je, pokud si toho všimnete hned, použít klávesovou kombinaci CTRL+Z pro vrácení kroku a nebo tlačítko zpět na liště v Excelu.
#ČÍSLO! / #NUM!
Chybová hláška číslo se objeví ve chvíli, kdy zadaný výpočet nemůže být provedený. Například, pokud chcete vypočítat mocninu ze záporného čísla. Na následující záporné číslo použijeme excelovou funkci pro výpočet druhé mocniny (SQRTPI). Funkci potvrdíme a jelikož se snažíme spočítat druhou mocninu ze záporného čísla, tak funkce vrátí chybovou hlášku číslo. Stejně tak se tato chyba objeví u některých excelových funkcí, ve kterých špatně vyplníme parametry. Stát se to může například u funkce DATEDIF, ve které se nejprve vyplňuje počáteční datum, následně konečné datum a jednotky, ve kterých chceme dobu mezi uvedenými datumy spočítat, například ve dnech. Funkci potvrdíme a vrátila se chyba. A je to proto, protože jsme jako počáteční datum označili datum, které je až po konečném datu. Když datumy přehodíme, funkce vrátí správný výsledek. Tuto chybu vrací i iterační funkce, pokud opět vyplníte špatně parametry. Jako například u funkce XIRR, která počítá vnitřní výnosové procento. U tohoto vzorce musí být první hodnota záporná, zde ji ovšem máme kladnou a proto funkce vrací chybovou hlášku číslo. Pokud hodnotu přepíšeme na zápornou, funkce vrátí správnou hodnotu.
Uživatelé novějších Excelů, kteří mají přístup k dynamickým maticovým funkcím se mohou setkat s dalšími dvěma chybovými hláškami.
#PŘESAH_DAT! / #SPILL
Chybová hláška přesah dat se objeví, když použijete dynamickou maticovou funkci, která se rozlívá do okolních buněk a něco vám v rozlití funkce brání. Máme zde seznam hodnot a vedle chceme vrátit unikátní seznam těchto hodnot. K tomu použijeme funkci UNIQUE, ve funkci UNIQUE označíme seznam hodnot a funkci potvrdíme. Vrátila se chybová hláška přesah hodnot a je to proto, protože funkce UNIQUE chce vrátit tři produkty, nicméně ji v tom brání tato buňka, kde máme již něco vyplněného. Pokud obsah této buňky, která funkci UNIQUE brání, smažeme, tak se funkce UNIQUE rozlije a vrátí správný výsledek. Jediným řešením je odstranit z cesty funkci vše, co jí překáží v rozlití.
#VÝPOČET! / #CALC!
Další chybovou hláškou, na kterou mohou uživatelé nových Excelů narazit, je chybová hláška výpočet. Tato chybová hláška se může objevit například u funkce FILTER. Na následující tabulku zkusíme uplatnit funkci FILTER, kde budeme chtít tabulku vyfiltrovat pro produkt Z, který ovšem v tabulce není. Funkci potvrdíme a vrátila se chybová hláška výpočet. A je to protože funkce FILTER nemohla hledanou hodnotu najít a provést tedy výpočet ve funkci FILTER.
Řešením je využít i dalšího nepovinného parametru ve funkci FILTER, pokud prázdné. Vrátíme se k funkci a v posledním parametru vyplníme „Nenalezeno“. Potvrdíme a chybová hláška zmizela. Pokud změníme jeden z produktů na produkt Z, tak funkce FILTER vrátí správný výsledek.
Tak toto byly chybové hlášky v Excelu, se kterými se můžete setkat.
2 komentáře
😊
Nepřesnost u chybové hlášky #N/A
Tato chyba se objeví i při funkci INDEX při “nalezení” prázdné buňky místo číselné hodnoty.
Následné odstranění a dosazení čísla 0 do této buňky je horor.
SVYHLEDAT mým požadavkům nevyhovuje.