Ve spolupráci se SEDUO jsem vytvořil několik videokurzů:
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
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.
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.
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.
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 ;)
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.
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! se objeví v případě, že určíte průnik dvou oblastí, které se nepřekrývají.
Možná oprava:
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! 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:
Možná oprava:
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! (#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? se objeví v případě, že text ve vzorci není rozpoznán.
Možná oprava:
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 (#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 (#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! 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! (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! 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! 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ů).
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:
Jak zjistit typ chyby pomocí funkce (vzorce):
=CHYBA.TYP(chyba)
Chyba - je typ chyby, jejíž číslo chcete zjistit.
Čísla chyb:
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).
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!")
Jak odhalujete chyby vy. Máte na to nějaký elegantní postup?
Článek byl aktualizován: 19.09.2020 11:07
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.
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ů.
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?
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 )
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
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
Přidáno: 18.07.15 07:54
To Vráťa: Například dočasně znepřístupnit externí zdroj dat (OLAP).
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!.
Přidáno: 01.11.16 10:12
šouplo se to do jednoho odstavce, takže z toho chytří asi nebudete ;))
Pomohl Vám návod? Sdílejte na Facebooku, G+ |
||
LinkedIn... |
Stránky o MS Office (Excel) produktu společnosti Microsoft. Neslouží jako technická podpora.
| Email na autora: pavel.lasak@gmail.com | Copyright © : Pavel Lasák 2004 - 2024 |