Zjišťování chyb ve vzorcích

V následující tabulce najdete souhrn některých nejběžnějších chyb, které uživatelé při zadávání vzorců dělají. Pak taky vysvětluje, jak takové chyby opravit:

Je třeba zkontrolovat následující skutečnosti	Další informace
Každá funkce musí začínat symbolem = (rovná se)	Pokud znaménko rovná se vynecháte, může se zadaný text zobrazit jako text nebo jako datum. Pokud například zadáte SUMA(A1:A10),Excel zobrazí textový řetězec SUMA(A1:A10) a neprovede výpočet. Pokud zadáte 11/2, zobrazí Excel datum 2. listopadu (za předpokladu, že formát buňky je Obecný) místo dělení 11 číslem 2.
Párování všech levých a pravých závorek	Zkontrolujte, že máte všechny závorky v páru (vždycky levou i pravou). Když ve vzorci použijete funkci, je důležité, aby každá závorka byla ve správné pozici, aby funkce fungovala správně. Tady je příklad: Vzorec = KDYŽ(B5<0);"Není platné";B5*1,05) nebude fungovat, protože obsahuje dvě pravé závorky a jenom jednu levou závorku (přitom by v něm měla být jenom jedna levá a jedna pravá závorka). Vzorec musí vypadat takhle: =KDYŽ(B5<0;"Není platné";B5*1,05).
Označení oblasti pomocí dvojtečky	Když chcete odkazovat na oblast buněk, oddělte odkaz na první buňku v oblasti od poslední buňky v oblasti pomocí dvojtečky (:). Například =SUMA(A1:A5), ne =SUMA(A1 A5), která by vrátila #NULL! Chyba.
Zadání všech požadovaných argumentů	Některé funkce vyžadovaly argumenty. Taky se ujistěte, že jste nezadali příliš moc argumentů.
Zadání správných argumentů	Některé funkce, jako třeba SUMA, vyžadují číselné argumenty. Jiné funkce, jako třeba NAHRADIT, vyžadují minimálně pro jeden ze svých argumentů textovou hodnotu. Pokud jako argument použijete nesprávný typ dat, Excel může vrátit neočekávané výsledky nebo zobrazit chybu.
Počet vnořených funkcí nesmí přesáhnout 64	V rámci jedné funkce můžete zadat nebo vnořit maximálně 64 úrovní funkcí.
Uzavření názvů jiných listů do jednoduchých uvozovek	Pokud vzorec odkazuje na hodnoty nebo buňky v jiných listech nebo sešitech a název daného listu nebo sešitu obsahuje mezery nebo jiné než abecední znaky, musíte jeho název zadat do jednoduchých uvozovek ('), například ='Čtvrtletní data'!D3 nebo =‘123’!A1.
Když na list odkazujete ve vzorci, umístěte za jeho název vykřičník (!)	Pokud třeba chcete, aby se vám vrátila hodnota buňky D3 z listu s názvem Čtvrtletní data ve stejném sešitu, použijte vzorec ='Čtvrtletní data'!D3.
Uvedení cesty k externím sešitům	Zkontrolujte, jestli každý externí odkaz obsahuje název sešitu a cestu k danému sešitu. Odkaz na sešit obsahuje název sešitu a musí být uzavřený v závorkách ([název_sešitu.xlsx]). Odkaz musí taky obsahovat název listu v sešitu. Odkazovat můžete ve vzorci i na sešit, který v Excelu zrovna otevřený není. Stačí zadat úplnou cestu k souboru, jak vidíte třeba tady: =ŘÁDKY('C:\My Documents\[Operace Q2.xlsx]Prodej'!A1:A8). Tenhle vzorec vrátí počet řádků v oblasti, která zahrnuje buňky A1 až A8 v jiném sešitu (8). Poznámka: Pokud celá cesta obsahuje mezery, jako je to třeba v příkladu výš, cestu je potřeba uzavřít do jednoduchých uvozovek (na začátku cesty a za název listu před vykřičníkem).
Zadání čísel bez formátování	Když čísla zadáváte do vzorce, neformátujte je. Pokud je hodnota, kterou chcete zadat, třeba 1,000 Kč, do vzorce zadejte 1000. Když jako součást čísla zadáte taky čárku, Excel ji bude pokládat za oddělovač. Jestli chcete, aby se čísla zobrazovala s oddělovačem tisíců nebo miliónů nebo se symbolem měny, naformátujte buňky až po zadání čísel. Pokud například chcete k hodnotě v buňce A3 přidat hodnotu 3100 a zadáte vzorec =SUMA(3,100;A3),Excel sečte čísla 3 a 100 a pak tento součet přičte k hodnotě A3 místo 3100 do A3, což by bylo =SUMA(3100;A3). Nebo pokud zadáte vzorec =ABS(-2;134), Excel zobrazí chybu, protože funkce ABS přijímá pouze jeden argument: =ABS(-2134).

Můžete využít určitá pravidla pro kontrolu chyb ve vzorcích. Tato pravidla nezaručují, že se na příslušném listu nevyskytují žádné chyby, ale odhalí většinu běžných chyb. Podle potřeby můžete jednotlivá pravidla vypnout nebo zapnout.

Chyby jde označovat a opravovat dvěma způsoby. Je možné opravovat jednu chybu po druhé (jako právě při kontrole pravopisu), nebo je jde opravovat hned, jak je při zadávání dat na listu uděláte.

Chybu můžete vyřešit pomocí možností, které excel zobrazí, nebo můžete chybu ignorovat tak, že vyberete Ignorovat chybu. Pokud ignorujete chybu v konkrétní buňce, nezobrazí se chyba v této buňce při dalších kontrolách chyb. Všechny dříve ignorované chyby ale můžete obnovit, aby se znovu zobrazily.

1. V Excelu ve Windows přejděte na Možnosti > souborů > vzorce nebo
   v Excelu na Macu vyberte nabídku Excel > Předvolby > Kontrola chyb.

2. V části Kontrola chyb zaškrtněte políčko Povolit kontrolu chyb v pozadí. Každá nalezená chyba se označí trojúhelníkem v levém horním rohu buňky.

   

3. Jestliže chcete změnit barvu trojúhelníku označujícího chybu, vyberte požadovanou barvu v seznamu Označit chyby pomocí této barvy.

4. V části Pravidla kontroly aplikace Excel zaškrtněte nebo zrušte zaškrtnutí políček u libovolného z následujících pravidel:

   • Buňky obsahující vzorce, které vedou k chybě: Vzorec nepoužívá očekávanou syntaxi, argumenty ani datové typy. Mezi chybové hodnoty patří #DIV/0!, #N/A, #NAME?, #NULL!, #NUM!, #REF! a #VALUE!. Každá z těchto chybových hodnot má různé příčiny a řeší se různými způsoby.

     Poznámka: Pokud zadáte chybovou hodnotu přímo do buňky, uloží se jako tato chybová hodnota, ale nebude označena jako chyba. Pokud ale na tuto buňku odkazuje vzorec v jiné buňce, vzorec vrátí chybovou hodnotu z téhle buňky.

   • Nekonzistentní vzorec počítaný sloupec v tabulkách: Počítaný sloupec může obsahovat jednotlivé vzorce, které se liší od vzorce hlavního sloupce, což vytvoří výjimku. Výjimky počítaného sloupce se vytvoří v případě, že uděláte některou z těchto věcí:

     • Zadáte do buňky počítaného sloupce jiná data než vzorec.

     • Zadejte vzorec do buňky počítaného sloupce a pak stiskněte Ctrl +Z nebo vyberte Zpět na panelu nástrojů Rychlý přístup.

     • Zadáte do počítaného sloupce, který již obsahuje nejméně jednu výjimku, nový vzorec.

     • Zkopírujete do počítaného sloupce data neodpovídající vzorci daného počítaného sloupce. Pokud kopírovaná data obsahují vzorec, přepíše tento vzorec data v počítaném sloupci.

     • Přesunete nebo odstraníte v jiné oblasti listu buňku, na kterou odkazuje jeden či více řádků počítaného sloupce.

   • Buňky obsahující roky reprezentované jako 2 číslice: Buňka obsahuje textové datum, které lze při použití ve vzorcích chybně interpretovat jako nesprávné století. Třeba datum ve vzorci =ROK("1/1/31") může představovat rok 1931 nebo 2031. Tohle pravidlo slouží ke kontrole dvojznačných kalendářních dat zadaných ve formátu textu.

   • Čísla formátovaná jako text nebo před kterými předchází apostrof: Buňka obsahuje čísla uložená jako text. K tomu obvykle dochází při importu dat z jiných zdrojů. Čísla uložená jako text můžou způsobit neočekávané výsledky řazení, proto je nejlepší je převést na čísla. '=SUMA(A1:A10) se považuje za text.

   • Vzorce nekonzistentní s jinými vzorci v oblasti: Vzorec neodpovídá vzoru jiných vzorců v jeho blízkosti. V mnoha případech se vzorce sousedící s jinými vzorci liší pouze v použitých odkazech. V následujícím příkladu čtyř sousedních vzorců excel zobrazí chybu vedle vzorce =SUMA(A10:C10) v buňce D4, protože sousední vzorce se zvýší o jeden řádek a ten se zvýší o 8 řádků – Excel očekává vzorec =SUMA(A4:C4).

     

     Pokud odkazy použité ve vzorci nejsou konzistentní s odkazy v sousedních vzorcích, excel zobrazí chybu.

   • Vzorce, které vynechají buňky v oblasti: Vzorec nemusí automaticky obsahovat odkazy na data, která vložíte mezi původní oblast dat a buňku obsahující vzorec. Toto pravidlo porovnává odkazy ve vzorci s oblastí buněk, která ve skutečnosti sousedí s buňkou obsahující vzorec. Pokud sousední buňky obsahují další hodnoty a nejsou prázdné, Excel zobrazí vedle vzorce chybu.

     Excel například při použití tohoto pravidla vloží chybu vedle vzorce =SUMA(D2:D4), protože buňky D5, D6 a D7 sousedí s buňkami odkazovanými ve vzorci a buňkou, která obsahuje vzorec (D8), a tyto buňky obsahují data, na která by vzorec měl odkazovat.

     

   • Odemknuté buňky obsahující vzorce: Vzorec není uzamčen pro ochranu. Ve výchozím nastavení jsou všechny buňky na listu uzamčené, takže je nelze změnit, když je list zamknutý. Díky tomu se můžete vyhnout neúmyslným chybám, jako je náhodné odstranění nebo změna vzorců. Tato chyba značí, že buňka byla nastavena tak, aby byla odemknutá, ale list není zamknutý. Zkontrolujte, jestli nechcete buňku zamknout.

   • Vzorce odkazující na prázdné buňky: Pokud vzorec obsahuje odkaz na prázdnou buňku, může to způsobit neočekávané výsledky, jako v následujícím příkladu.

     Předpokládejme, že chcete vypočítat průměr z čísel uvedených v následujícím sloupci buněk. Pokud je třetí buňka prázdná, není zahrnutá do výpočtu a výsledek je 22,75. Jestli třetí buňka obsahuje nulu, výsledek bude 18,2.

     

   • Data zadaná v tabulce jsou neplatná: V tabulce došlo k chybě ověření. Zkontrolujte nastavení ověření buňky tak, že přejdete na kartu Data > skupině Datové nástroje > Ověření dat.

1. Vyberte list, ve kterém chcete hledat chyby.

2. Pokud je list počítán ručně, přepočtěte jej stisknutím klávesy F9.

   Pokud se dialogové okno Kontrola chyb nezobrazí, vyberte Vzorce > Závislosti vzorců > Kontrola chyb.

3. Pokud jste dříve ignorovali nějaké chyby, můžete je znovu zkontrolovat následujícím způsobem: Přejděte do části Možnosti > souborů > Vzorce. V Excelu na Macu vyberte nabídku Excel > Předvolby > Kontrola chyb.

   V části Kontrola chyb vyberte Resetovat ignorované chyby > OK.

   

   Poznámka: Obnovením ignorovaných chyb obnovíte všechny chyby na všech listech v aktivním sešitu.

   Tip: Můžete taky umístit dialogové okno Kontrola chyb přímo pod řádek vzorců.

   

4. Vyberte jedno z tlačítek akcí na pravé straně dialogového okna. Dostupné akce jsou jiné pro každý typ chyby.

5. Vyberte Další.

1. Vedle buňky vyberte Kontrola chyb a pak vyberte požadovanou možnost. Dostupné příkazy se pro každý typ chyby liší a první položka popisuje chybu.

   Pokud vyberete Možnost Ignorovat chybu, bude chyba označena tak, aby byla ignorována při každé po sobě jdoucí kontrole.

   

Pokud vzorec nemůže správně vyhodnotit výsledek, excel zobrazí chybovou hodnotu, například #####, #DIV/0!, #N/A, #NAME?, #NULL!, #NUM!, #REF!a #VALUE!. Každý typ chyby má různé příčiny a různá řešení.

Následující tabulka obsahuje odkazy na články, které tyhle chyby podrobně popisují, a nabízí taky stručný popis, díky kterému budete moci začít pracovat.

Téma	Popis
Oprava chyby ####	Excel tuhle chybu zobrazí, když sloupec není dost široký, aby se v něm zobrazily všechny znaky v buňce, nebo když buňka obsahuje zápornou hodnotu data nebo času. To se může stát třeba v případě, že vzorec odečte datum v budoucnosti od data v minulosti (třeba =06/15/2008-07/01/2008. Výsledkem je pak záporná hodnota data. Tip: Pokuste se buňku automaticky přizpůsobit poklikáním mezi záhlaví sloupců. Pokud se ### zobrazí, protože Excel nemůže zobrazit všechny znaky, opraví se to.
Oprava chyby #DIV/0! #BUSY!	Excel tuhle chybu zobrazí při dělení čísla nulou (0) nebo buňkou, která neobsahuje žádnou hodnotu. Tip: Přidejte obslužnou rutinu chyby, jako v následujícím příkladu: =KDYŽ(C2;B2/C2;0).
Oprava chyby #NENÍ_K_DISPOZICI	Excel tuhle chybu zobrazí, když hodnota není pro funkci nebo vzorec dostupná. Pokud používáte funkci jako SVYHLEDAT, shoduje se vámi hledaná položka s položkou v prohledávané oblasti? Většinou ne. Zkuste použít funkci CHYBHODN k potlačení chyby #NENÍ_K_DISPOZICI. V takovém případě byste mohli použít: =CHYBHODN(SVYHLEDAT(D2;$D$6:$E$8;2;PRAVDA);0)
Oprava chyby #NÁZEV? #BUSY!	Tahle chyba se zobrazí, když Excel nerozpozná text ve vzorci. Například název oblasti nebo název funkce může být nesprávně napsaný. Poznámka: Pokud používáte funkci, zkontrolujte, že je její název správně napsaný. V tomto případě je SUMA napsáno nesprávně. Odeberte "e" a Excel ho opraví.
Oprava chyby #NULL! chyba	Excel tuhle chybu zobrazí, když zadáte průnik dvou oblastí, které se ve skutečnosti neprotínají (nekříží). Jako operátor průniku funguje znak mezery, který odděluje odkazy ve vzorci. Poznámka: Ujistěte se, že jsou oblasti správně oddělené – oblasti C2:C3 a E4:E6 se neprotínají, takže zadání vzorce =SUMA(C2:C3 E4:E6) vrátí #NULL! . Když mezi oblastmi C a E umístíte čárku, opraví se hodnota =SUMA(C2:C3;E4:E6)
Oprava chyby #ČÍSLO! chyba	Excel tuhle chybu zobrazí, když vzorec nebo funkce obsahuje neplatné číselné hodnoty. Používáte funkci, která provádí iterace, třeba funkci MÍRA.VÝNOSNOSTI nebo ÚROKOVÁ.MÍRA? Pokud ano, je chyba #NUM!. pravděpodobně výsledkem toho, že funkce nemůže najít výsledek. Kroky řešení zkuste najít v tématech nápovědy.
Oprava chyby #ODKAZ! #BUSY!	Excel zobrazí tuto chybu, když odkaz na buňku není platný. Mohli jste například odstranit buňky, na které odkazovaly jiné vzorce, nebo vložit buňky, které jste přesunuli nad buňky odkazované jinými vzorci. Odstranili jste omylem řádek nebo sloupec? Ve vzorci =SUMA(A2;B2;C2) jsme odstranili sloupec B a podívejte se, co se stalo. Můžete buď použít příkaz Zpět (Ctrl+Z) a vrátit zpět operaci odstranění, můžete vzorec vytvořit znovu nebo můžete použít odkaz na souvislou oblast, třeba =SUMA(A2:C2), který by se při odstranění sloupce B aktualizoval.
Oprava chyby #HODNOTA! chyba	Excel může tuhle chybu zobrazit, když váš vzorec obsahuje buňky, které obsahují různé datové typy. Používáte matematické operátory (+, -, *, / ^) s různými typy dat? V takovém případě zkuste místo nich použít funkci. Tento problém vyřeší funkce =SUMA(F2:F5).

Pokud nejsou buňky na listu viditelné, můžete je watch a jejich vzorce na panelu nástrojů Okna kukátka. Okno kukátka nabízí praktický způsob prohlížení, kontroly a ověřování výpočtů a výsledků vzorců ve velkých listech. Díky oknu kukátka se nemusíte opakovaně posouvat v listu ani přecházet do jeho různých částí.

Tento panel nástrojů můžete přesunout nebo ukotvit jako jakýkoliv jiný panel nástrojů. Můžete ho třeba ukotvit v dolní části okna. Panel nástrojů sleduje následující vlastnosti buňky: 1) Sešit, 2) List, 3) Název (pokud má buňka odpovídající pojmenovanou oblast), 4) Adresa buňky, 5) Hodnota a 6) Vzorec.

Přidání buněk do okna kukátka

1. Vyberte buňky, které chcete sledovat.

   Pokud chcete vybrat všechny buňky na listu se vzorci, přejděte na Domů > Úpravy > vyberte Najít & Vybrat (nebo můžete použít Ctrl+G nebo Control+G na Macu)> Přejít na zvláštní > Vzorce.

   

2. Přejděte na Vzorce > Vzorec auditování > vyberte Okno Kukátko.

3. Vyberte Přidat kukátku.

   

4. Potvrďte, že jste vybrali všechny buňky, které chcete watch, a vyberte Přidat.

   

5. Pokud chcete změnit šířku sloupce Okno kukátka, přetáhněte okraj na pravé straně záhlaví sloupce.

6. Pokud chcete zobrazit buňku, na kterou odkazuje položka v panelu nástrojů Okno kukátka, poklikejte na danou položku.

   Poznámka: Buňky s externími odkazy na jiné sešity se v panelu nástrojů Okno kukátka zobrazí, jenom když jsou dané sešity otevřené.

Odebrání buněk z Okna kukátka

1. Pokud se panel nástrojů Okno kukátka nezobrazuje, přejděte na Vzorce > Závislosti vzorců > vyberte Okno Kukátko.

2. Vyberte buňky, které chcete odebrat.

   Pokud chcete vybrat více buněk, stiskněte klávesu CTRL a pak vyberte buňky.

3. Vyberte Odstranit kukátku.

   

Někdy není lehké pochopit, jak vnořený vzorec došel ke konečnému výsledku, protože provádí řadu průběžných výpočtů a logických testů. Dialogové okno Vyhodnocení vzorce ale umožňuje zobrazit různé části vnořeného vzorce, které se za účelem výpočtu vzorce vyhodnocují. Například vzorec =KDYŽ(PRŮMĚR(D2:D5)>50;SUMA(E2:E5);0) je srozumitelnější, když uvidíte následující průběžné výsledky:

V dialogovém okně Vyhodnocení vzorce	Popis
=KDYŽ(PRŮMĚR(D2:D5) > 50;SUMA(E2:E5);0)	Nejdřív se zobrazí vnořený vzorec. Funkce PRŮMĚR a SUMA jsou vnořené ve funkci KDYŽ. Oblast buněk D2:D5 obsahuje hodnoty 55, 35, 45 a 25. Výsledek funkce PRŮMĚR(D2:D5) je tím pádem 40.
=KDYŽ(40>50;SUMA(E2:E5);0)	Oblast buněk D2:D5 obsahuje hodnoty 55, 35, 45 a 25. Výsledek funkce PRŮMĚR(D2:D5) je tím pádem 40.
=KDYŽ(Nepravda;SUMA(E2:E5);0)	40 není větší než 50, a proto je výraz v prvním argumentu funkce KDYŽ (argumentu Podmínka) Nepravda. Funkce KDYŽ vrátí hodnotu třetího argumentu (argumentu Ne). Funkce SUMA se nevyhodnocuje, protože je druhým argumentem funkce KDYŽ (argument Ano) a vrátí se, jenom když je výraz pravdivý.

1. Vyberte buňky, které chcete vyhodnotit. Buňky můžete upravovat jenom jednotlivě.

2. Přejděte na Vzorce > Vzorec auditování > Vyhodnotit vzorec.

3. Výběrem Vyhodnotit prozkoumejte hodnotu podtrženého odkazu. Výsledek vyhodnocení se zobrazí kurzívou.

   Pokud je podtržená část vzorce odkazem na jiný vzorec, vyberte Krokovat a zobrazte druhý vzorec v poli Vyhodnocení . Výběrem Vystoupit se vraťte k předchozí buňce a vzorci.

   Tlačítko Vstoupit není pro odkaz dostupné při druhém zobrazení odkazu ve vzorci, a to ani když vzorec odkazuje na buňku v samostatném sešitu.

4. Pokračujte ve výběru Vyhodnotit , dokud se nevyhodnotí všechny části vzorce.

5. Pokud chcete vyhodnocení znovu zobrazit, vyberte Restartovat.

6. Pokud chcete hodnocení ukončit, vyberte Zavřít.