Jste zde: Úvodní stránka » excel » zaklady » chybove-hodnoty

Chybové hodnoty - Excel

Videokurzy Excel

Ve spolupráci se SEDUO jsem vytvořil několik videokurzů:

Jak na Excel - naučte se efektivně využívat Excel - nejoblíbenější (Hodnocení 97%, přes 13.000 studentů, + 750 pozitivních zpětných vazeb) - Získat kurz
MaxiKurz Excel - získejte lepší práci, více peněz, staňte se nepostradatelní (přes 6 hodin videí, 160 lekcí) - Získat kurz
G tabulky... - pokud nemáte rádi Excel využijte kurz na Google-tabulky

Jak na chybové hodnoty v buňkách Microsoft Excel. Než opravíte chybu v buňce, musíme vědět, co jednotlivé chyby znamenají. Jakmile víte, čeho se chyby týká, můžete ji opravit. Jak na to se dozvíte v tomto článku.

23.7.2019 - doplněno o nové chybové hodnoty

Chybové hodnoty

Při práci v Microsoft Excelu, se stane, že místo požadovaného výsledku se v buňce objeví chybová hodnota (na první pohled nesmyslný údaj jako například #DIV/0!, #N/A, atd.). Na druhý pohled tento údaj vůbec nesmyslný není, označuje, co jste udělali za chybu (druh chyby).

Využitím znalosti co konkrétní chybová hodnota představuje, opravíte chybu o mnoho rychleji.

Seznam chyb:

(1) #NULL!
(2) #DIV/0! - do Excel 2003, #DĚLENÍ_NULOU! - od Excel 2007
(3) #HODNOTA!
(4) #REF! - do Excel 2003, #ODKAZ! - od Excel 2007
(5) #NÁZEV?
(6) #NUM! - do Excel 2003, #ČÍSLO! - od Excel 2007
(7) #N/A - do Excel 2003, #NENÍ_K_DISPOZICI - od Excel 2007
(8) #GETTING_DATA - do Excel 2007 EN, #NAČÍTÁNÍ_DAT - od Excel 2007 CZ

Poznámka: Jelikož si Microsoft libuje v překladech, začal od verze Excel 2007, překládat názvy chybových hodnot. Místo elegantního #N/A je nově #NENÍ_K_DISPOZICI, atd.

Nové chybové hodnoty

V Insider verzi Excel 365 (zatím 07/2019, ale brzo budou k dispozici ve všch verzích 365) jsou k dispozici nové chybové hodnoty. Chyby reagují na nové funkce FILTER, SORT, atd.

(9) #PŘESAH! v angličtině #SPILL!
(12) #UNKNOWN! v angličtině #UNKNOWN!
(13) #POLE! v angličtině #FIELD
(14) #VÝPOČET! v angličtině #CALC!

Proč nepřeložil rovhnou chybovou hodnotu #UNKNOWN! ? Když ostatní Microsoft překládal, stejně tak proč jsou volné čísla chyb 10 a 11 a proč existuje chyby 13, když v několika číselnicích Microsoft číslo 1š přeskajuje? To je mi záhadou, ale možná se můžeme těšit na další dvě nové chyby ;)

Nechybová hodnota:

####

Pozor: pokud máte buňku úzkou, může se za zobrazením nechybové hodnoty skrývat hodnota chybová. Zjistíte například zvětšením šířky buňky.

Jak zjistit typ chyby a počet chyb?

CHYBA.TYP - Typ (číslo) chyby v Excelu
Počet chybových hodnot v oblasti -

Využití: například při "ošetřování" buněk ve formulářích (než je tento vyplněn), dále při programování ve VBA, atd.

Chybová hodnota #NULL!

Chybová hodnota #NULL! se objeví v případě, že určíte průnik dvou oblastí, které se nepřekrývají.

Možná oprava:

Upravit oblast tak ať se překrývá (funkce POLÍČKO).

Chybová hodnota #DIV/0! nebo-li #DĚLENÍ_NULOU!

MS Excel - chyba #DIV/0! nebo #DĚLENÍ_NULOU!

Chybová hodnota #DIV/0! #DĚLENÍ_NULOU! se objeví v případě, že ve vzorci dělíte 0 (nulou).

Poznámka: Podle verze Excelu (97, 2003, 2007, 2010) obdržíte buď #DIV/0! nebo #DĚLENÍ_NULOU!

Možná oprava:

Zkontrolujete vzorec, zda někde nedělíte nulou (=10/0, třeba tento nesmysl :)).

Chybová hodnota #HODNOTA!

Chybová hodnota #HODNOTA! se objeví v případě, že používáte nesprávný typ argumentu nebo operandu, nebo když funkce automatické opravy vzorců nemůže chybný vzorec opravit.

Možné příčiny:

text v matematických operacích - Excel nemůže sečíst
Chybné argumenty například u vyhledávacích funkcí

Možná oprava:

Místo součtu použít =SUMA(C6;C7)
Pro zadávání hodnot použijte ověření dat (uživatel musí zadat číslo)
zkontrolujete funkci, zda neobsahuje překlep, odkaz ne nevhodný argument

Někdy může být oprava chyby složitější, ale čím více podobných chyb odstraníte, tím rychleji získáte rutinu v jejich odstraňování (případně k této chybě ani nedojde).

Chybová hodnota #REF! nebo-li #ODKAZ!

Chybová hodnota #REF! (#ODKAZ!) se objeví v případě, že je odkaz na buňku neplatný.

Možná oprava:

Často se stane, když se odkazujete mezi listy a jeden list smažete. Již neexistuje odkaz na tento list (chyba #REF! (#ODKAZ!) se může se objevit přímo ve vzorci).

Chybová hodnota #NÁZEV?

Chybová hodnota #NÁZEV? se objeví v případě, že text ve vzorci není rozpoznán.

Možná oprava:

Špatně naspané názvy funkcí. (Např. místo SUMA máte, naspáno SSUMA.)
Chybně zadaný název buňky/oblasti, oblast neexistuje.

Chybová hodnota #NUM! nebo-li #ČÍSLO!

Chybová hodnota #NUM! (#ČÍSLO!) se objeví v případě výskytu problému s číslem ve vzorci nebo funkci například výpočet úrokové míry.

Možná oprava:

Nalezení chyby je složitější. Zkuste si projit nápovědu k dané funkcí. Můžou chybět uvozovky, středník, číslo nemá patřičný formát. Nejedná se o funkci, která interaguje.

Chybová hodnota #N/A nebo-li #NENÍ_K_DISPOZICI

MS Excel - chyba #N/A nebo #NENÍ_K_DISPOZICI

Chybová hodnota #N/A (#NENÍ_K_DISPOZICI) se objeví v případě, že hodnota není funkci nebo není pro vzorec dostupná. Budou-li buňky v listu obsahovat data, která ještě nejsou k dispozici. Vzorce, které odkazují na tyto buňky, budou vracet #N/A (#NENÍ_K_DISPOZICI) místo pokusu o výpočet hodnoty.

Možná oprava:

Najít zatím nevyplněnou buňku, případně vzorec doplnit o kontrolu. Když nebudou známy všechny hodnoty tak se nic nezobrazí (funkce CHYBA.TYP). Nebo se s touto hláškou smířit a počkat až budou k dispozici vypočtené hodnoty.

Chybová hodnota #GETTING_DATA nebo-li #NAČÍTÁNÍ_DAT

Chybová hodnota #GETTING_DATA (#NAČÍTÁNÍ_DAT) se objeví v případě načítaní dat přes OLAP. Mnohdy jde jen dočasnou chybu, než se data aktualizují z databáze (nedostupnost / rychlost).

Možná oprava:

Počkat na načtení dat z externí databáze.

Chybová hodnota #PŘESAH! neboli #SPILL!

Chybová hodnota #PŘESAH! neboli #SPILL! se objeví v případě, že výsledek dynamické funkce zasahuje do oblasti ve které už je nějaký text, případně v oblasti kde se tvoří dynamický výsledek funkcí (SORT, FILTER) je sloučená buňka.

Možná oprava:

Smazat zasahující texty případně odsloučit (rozdělit) buňky.

Chybová hodnota #UNKNOWN!

Chybová hodnota #UNKNOWN! (v češtině i angličtině stejný název). Více inforrmací v přípravě.

Možná oprava:

v přípravě.

Chybová hodnota #POLE! neboli #FIELD!

Chybová hodnota #POLE! neboli #FIELD! se zobrazí pokud v se odkazované pole nenajde v propojeném datovém typu. Například při použití datových typů Země, Město ....

Možná oprava:

Upravit odkaz na správně definovaný typ.

Chybová hodnota #VÝPOČET! neboli #CALC!

Chybová hodnota #VÝPOČET! neboli #CALC! například pokud zadáte v dynamicky tvořené funkci RANDARRAY chybně rozměr matice (neboli matice nemůže mít nula řádku/sloupců).


=RANDARRAY(0;4;1;10)

Možná oprava:

Upravit rozsah oblasti (počty čárků sloupců).

Malá buňka "nechybová hodnota" ####

Pozor: pokud máte buňku úzkou, může se za zaobrazením nechybové hodnoty skrývat hodnota chybová (tj. Excel není schopen zbrazit celý obsah buňky - #NENÍ_K_DISPOZICI je o mnoh delší než #N/A).

Hodnota zadaná do buňky je příliš velká (dlouhá) a Excel jí nemůže zobrazit v buňce.

Možné opravy:

změnit údaj v buňce (zkrátit)
rozšířit sloupec
změnit formátování buňky (zalamovat)

Typ chyby v Excelu

Jak zjistit typ chyby pomocí funkce (vzorce):


=CHYBA.TYP(chyba)

Chyba - je typ chyby, jejíž číslo chcete zjistit.

Čísla chyb:

#NULL! - číslo 1
#DIV/0! (#DĚLENÍ_NULOU!) - číslo 2
#HODNOTA! - číslo 3
#REF! (#ODKAZ!) - číslo 4
#NÁZEV? - číslo 5
#NUM! (#ČÍSLO!) - číslo 6
#N/A (#NENÍ_K_DISPOZICI) - číslo 7
#NAČÍTÁNÍ_DAT - číslo 8
Cokoli jiného - chyba tj. #N/A

Příklad:

Pomocí následujícího vzorce (funkce) lze zjistit, zda buňka E50 obsahuje chybovou hodnotu #N/A. Pokud buňka tuto hodnotu obsahuje, zobrazí se text "V buňce chybí hodnota". V opačném případě je vrácena hodnota buňky E50.

KDYŽ(CHYBA.TYP(E50)=7, "V buňce chybí hodnota", E50).

Počet chybových hodnot v oblasti

Jak zjistitk kolik chybových hodnot se nachází v oblasti. Například v Tabulce A1:B300? Jednoduše pomocí funkce COUNTIF a názvu hledané chyby například #HODNOTA! :


=COUNTIF(A1:B300;"#HODNOTA!")

Případně přes pojmenovanou oblast, kdy název oblasti je zároveň v buňce B13.


=COUNTIF(NEPŘÍMÝ.ODKAZ(B13);"#DĚLENÍ_NULOU!")

Závěrem

Jak odhalujete chyby vy. Máte na to nějaký elegantní postup?

Článek byl aktualizován: 19.09.2020 11:07

Odměna

Ušetřil vám tento web čas, peníze? Pomohl vyřešit problém? Jste ochotni poskytnout symbolickou odměnu na další rozvoj? Vybrte si formu odměny, která vám vyhovuje.

Pavel Lasák - autor webu

Microsoft Office (Word, Excel, Google tabulky, PowerPoint) se věnuji od roku 2000 (od 2004 na této doméně) - V roce 2017 jsem od Microsoft získal prestižní ocenění MVP (zatím jsem jediný z ČR v kategorií Excel). Své vědomosti a zkušenosti dávám k dispozici i on-line ve videích pro SEDUO. Ve firmách školím a konzultuji, učím na MUNI. Tento web již tvořím přes 15 let. Zdarma je zde přes 1.000 návodu, tipů a triků, včetně přes 250 různých šablon, sešitů.

Komentáře

Marek

Přidáno: 19.11.13 08:45

Zkoušel jsem to všelijak, ale když v buňce E50 není chyba 7, tak vzorec "KDYŽ(CHYBA.TYP(E50)=7, "V buňce chybí hodnota", E50)" vyhodí chybu 7 a ne hodnotu buňky E50. Nevíte co s tím?

Pavel Lasák

Přidáno: 19.11.13 19:44

To Marek: Dobrý postřeh, napadlo mě toto řešení: =IFERROR(KDYŽ(CHYBA.TYP(C4)=7; "Chybová hodnota";C4);C4 )

Radek

Přidáno: 12.08.14 15:35

Dobrý den, nikde jsem nenašel řešení následujícího problému: Mám provázáno asi 7 buněk ale pouhým sčítáním (sloupec cca 50 řádků), a následným překopírováním obsahu (A1=A2) a dalším sčítáním (na jiný list). Vše fungovalo OK, avšak najednou se při dopsání čísla "nerefreshovaly" hodnoty a buňky, které byly navázané (správně navázané). Všechny buňky teď musím postupně procházet a rozkliknout do psaní (F2) a potvrdit (ENTER). Tím se obsah "aktualizuje". Kde je chyba? Zkoušel jsem stejný soubor otevřít v OpenOffice a je v pořádku. Proč Excel 2000 původně jel a nyní stávkuje? Díky za odpověď, raypet@seznam.cz

Vráťa

Přidáno: 15.06.15 08:14

Dobrý den, chtěl jsem nasimulovat chybu #NAČÍTÁNÍ_DAT jednoduchým příkladem a stále se mi nedaří tu chybu vyvolat. Díky za odpověď janko_v@volny.cz

Pavel Lasák

Přidáno: 18.07.15 07:54

To Vráťa: Například dočasně znepřístupnit externí zdroj dat (OLAP).

eLCHa

Přidáno: 01.11.16 09:47

Jen poznámka #NUM! je hodnota mimo povolený rozsah - obecně nebo v argumentu funkce lze ji vyvolat například takto Mimo rozsah povolený v aplikaci: =1,79769313486231*(10^309) Nebo nesmyslný argument =ÚROKOVÁ.MÍRA(5;100;1000) zjednodušeně: argumenty Splátka a Souč_hod musí mít opačnou orientaci ;)) (jeden kladný a jeden záporný) z nápvědy Vrátí úrokovou sazbu vztaženou k úročenému období anuity. Funkce ÚROKOVÁ.MÍRA je počítána pomocí iterací a může mít žádné nebo několik řešení. Jestliže úspěšné výsledky funkce ÚROKOVÁ.MÍRA nekonvergují do meze 0,0000001 po 20 iteracích, vrátí funkce ÚROKOVÁ.MÍRA chybovou hodnotu #ČÍSLO!.