<< Finanční funkce | Funkce - Obsah | Matematické funkce >>

Testování hodnot

Tyto funkce testují, zda jde o daný typ dat – text, číslo, logická hodnota, odkaz, prázdná buňka apod. Výsledkem jsou logické hodnoty PRAVDA a NEPRAVDA podle toho, zda odpovídá daný typ nebo ne. Syntaxe je u všech funkcí stejná.

JE.TEXT(hodnota) – testuje hodnotu, zda je textem

JE.NETEXT(hodnota) – testuje hodnotu, zda není textem

JE.ČÍSLO(hodnota) – testuje hodnotu, zda je číslem

JE.PRÁZDNÉ(hodnota) – testuje hodnotu, zda je prázdná (nevyplněná buňka)

JE.LOGHODN(hodnota) – testuje hodnotu, zda je logickou hodnotou (PRAVDA, NEPRAVDA)

JE.ODKAZ(hodnota) – testuje hodnotu, zda je odkazem

Funkce JE.TEXT

Tato funkce řeší problém zejména v případě, jestliže obdržíme soubor v Excelu získaný importem a zjistíme, že v něm nemůžeme provádět výpočty. Například při sčítání čísel nebo času nefunguje funkce SUMA. Nastavení formátu buňky nepomáhá. V takovém případě je vhodné se přesvědčit funkcí JE.TEXT, zda opravdu jde o text a případně použít funkci HODNOTA pro převod textu na číslo.

Na tento problém se podíváme blíže v příkladu 2_5_7_test1.xls – list „je.text“, viz také obrázek.

Sloupec „A“ obsahuje hodnoty, které se na první pohled zdají být čísly. Ve zvýrazněném řádku jsme použili funkci suma, abychom tyto hodnoty sečetli. Výsledek této funkce je však 0. Je to z toho důvodu, že funkce SUMA považuje textový obsah buňky za nulovou hodnotu.

Tuto skutečnost jsme si potvrdili ve sloupci „C“ pomocí funkce JE.TEXT. Ve všech případech vrátila logickou hodnotu PRAVDA.

Vzniklý problém řešíme ve sloupci E, kde jsme použili pro převod textových hodnot na hodnoty číselné funkci HODNOTA. I když obsah tohoto sloupce vypadá naprosto stejně jako obsah sloupce „A“, funkce SUMA tentokrát pracuje správně. Výsledek vidíme opět ve zvýrazněném řádku.

Pozn.: za textovou hodnotu bude považováno i to, jestliže v buňce před číslem je zapsán znak apostrof (‘).

Funkci nyní otestujeme na listě Procvičení 1 v příkladu 2_5_7_test1.xls.

Funkce JE.ČÍSLO

Touto funkcí v podstatě můžeme nahradit předchozí funkci JE.TEXT s tím, že se ověřuje, zda v buňce je zapsáno skutečně číslo.

Funkci si prohlédneme opět v příkladu 2_5_7_test1.xls na listě „je.číslo“, viz obrázek.

Ve sloupci A jsou zapsány hodnoty. Tyto zápisy vyhodnocujeme pomocí funkce JE.ČÍSLO, kterou jsme zavedli do sloupce C. Pouze jediná z hodnot (ve žluté buňce) je vyhodnocena jako číslo (výsledek funkce je tedy logická hodnota PRAVDA).

Ve zvýrazněném řádku jsme do sloupce A zavedli funkci SUMA. Funkce v tomto případě dává výsledek rovný buňce (žluté), kde je skutečné číslo. Ostatní (textové) hodnoty považuje za nulu, a tedy je do součtu nezahrne.

Aby funkce SUMA vypočetla správný výsledek, musíme ve sloupci E opět použít funkci HODNOTA, která údaje převede na skutečná čísla.

Funkce JE.NETEXT

Touto funkcí vyloučíme, že v buňce je zapsán text.

V příkladu 2_5_7_test1.xls na listě „je.netext“ probereme jednotlivé možnosti tak, jak jsou pod sebou napsány ve sloupci A, viz obrázek.

A4 – do této buňky byl zapsán výraz PRAVDA. Tento výraz je ve skutečnosti logickou hodnotou, proto jej funkce vyhodnotila pomocí logické hodnoty PRAVDA (sloupec C).

A5 – do této buňky byl zapsán výraz pravda, stejně jako v předchozím případě. Buňka však je formátována jako text (s dodatečným zarovnáním na střed, aby zápis vypadal stejně jako zápis v buňce A4). Funkce JE.NETEXT vyhodnotila, že ve skutečnosti o text jde, a vypsala jako výsledek tedy logickou hodnotu NEPRAVDA.

A6 – v buňce je zapsán normální text (slovo „Excel“), takže výsledkem funkce JE.NETEXT (sloupec C) je opět hodnota NEPRAVDA.

A7 – buňka se jeví jako prázdná, ale ve skutečnosti do ní byl zapsán znak mezera, který není viditelný. Proto funkce JE.NETEXT vyhodnotila obsah této buňky jako NEPRAVDA.

A8 – tato buňka je skutečně prázdná, tedy neosahuje žádný (ani neviditelný) znak či text. Funkcí JE.NETEXT získáváme výsledek PRAVDA.

A9 – v buňce je zapsáno číslo, výsledek funkce JE.NETEXT je tedy hodnota PRAVDA.

A10 – v buňce je zapsáno datum, které je jedním z číselných formátů, i tady je výsledkem PRAVDA.

A11 – buňka byla předem zformátována jako text a poté do ní bylo zapsáno datum. Proto je považováno za text, i když vypadá stejně jako datum zapsané v buňce A10.

A12 – do buňky byl umístěn znak (šipka), který je považován za textový znak, proto je výsledkem funkce NEPRAVDA.

K procvičení funkce využijeme list „Procvičeni 3“ v příkladu 2_5_7_test1.xls.

Funkce JE.PRÁZDNÉ

Tuto funkci představujeme v příkladu 2_5_7_test1.xls na listě „je.prázdné“. Má za úkol otestovat, zda v buňce nejsou zapsány žádné, tedy ani neviditelné znaky.

Ve sloupci A vidíme pod sebou dvě buňky (žlutá barva), které se jeví jako prázdné. V případě buňky A4 funkce potvrzuje, že jde skutečně o prázdnou buňku. Buňka A5 byla však indikována jako neprázdná. Je v ní totiž uložen znak mezera.

Tuto skutečnost jsme dále prověřili pomocí textové funkce ZNAK, jejíž vzorec jsme zapsali do buněk E4 a E5. V buňce E4 se zobrazuje chybové hlášení #HODNOTA!, ke kterému došlo proto, že v buňce A4 není nic uloženo a tudíž neexistuje žádný kód znaku. V buňce E5 se zobrazila hodnota 32, což je kód pro znak mezera.

K procvičení poslouží list Procvičení 4 v příkladu 2_5_7_test1.xls.

V praxi méně významné jsou další dvě informační funkce, které zde uvádíme pro úplnost.

Funkce JE.LOGHODN

Tato funkce testuje, zda je v buňce uložena jedna z možných logických hodnot – PRAVDA nebo NEPRAVDA.

Chování funkce budeme sledovat v příkladu 2_5_7_text2.xls na listě je.loghodn

Do sloupce A jsme zapsali opět různé údaje a využili v něm různé formáty buněk. Výraz PRAVDA zapsaný do buňky A4 se vzhledově neliší od stejného výrazu zapsaného do buňky B5. V prvním případě je však vyhodnocen jako PRAVDA (vzorcem zavedeným ve sloupci C), protože byl zapsán do buňky s obecným formátem, a je tedy považován za logickou hodnotu. Ve druhém případě byla buňka (A5) předem zformátovaná jako text, údaj tedy je vyhodnocen jako NEPRAVDA (není považován za logickou hodnotu).

V buňce A6 je uložen výraz NEPRAVDA. Buňka opět má obecný formát, výraz je tedy považován za logickou hodnotu.

V ostatních buňkách ve sloupci A jsou vloženy různé jiné hodnoty (texty, čísla, datum). Takové hodnoty samozřejmě nemohou být logickými hodnotami. Za logické hodnoty nejsou považovány ani číslice 1 a 0 (viz výsledky funkce ve sloupci C).

Funkce JE.ODKAZ

Výsledek funkce nezávisí na tom, co je samotným obsahem testované buňky (zda je to text, číslo, logická hodnota či prázdná buňka), ale indikuje čistě, zda argumentem je odkaz. Testujeme-li buňku s libovolnou existující adresou, bude funkce vždy vracet výsledek PRAVDA.

Některé případy funkce jsme demonstrovali v příkladu 2_5_7_test2.xls na listě „je.odkaz“

Zaměříme se na oblast zvýrazněnou žlutou barvou.

V řádku 11 je výsledek vzorce =JE.ODKAZ(ceny) vyhodnocen jako PRAVDA. Je to z toho důvodu, že v pomocné tabulce Ceník je pojmenovaná oblast „ceny“ (vyznačena bleděmodrou barvou). Argument funkce je tedy odkazem, a sice odkazem na název oblasti.

V řádku 12 je výsledek vzorce =JE.ODKAZ(auto) vyhodnocen jako NEPRAVDA. Název oblasti auto není v sešitě definován, a proto nemůže být považován za odkaz.

Testování chyb

Přehled jednotlivých typů chyb

Při práci v Excelu se sotva vyhneme tomu, aby výpočty neskončily chybou. V buňce se pak místo výsledku zobrazí jedna z chybových hodnot:

1. NULL! prázdná množina
2. DIV/0! dělení nulou např. 1/0
3. HODNOTA! matematické operace s textem, chybný argument funkce
4. REF! chybný odkaz na buňku, např. po odstranění sloupce, při kopírování vzorců (argument je za okrajem plochy Excel)
5. NÁZEV? chybný název funkce, oblasti
6. NUM! číslo příliš velké, malé, chybný argument ve funkci
7. N/A nenalezení dat při vyhledávání, chybí argument

   1. nedostatek místa pro zobrazení údaje, záporná hodnota data nebo času, záporné číslo zapsané do buňky, která byla předtím zformátována jako datum (v testování chyb však zjistíme, že toto není chybou v pravém slova smyslu).

Dříve, než začneme chyby testovat pomocí informačních funkcí, seznámíme se s jejich typy a příčinami, kdy jednotlivé chyby mohou nastat.

Otevřeme soubor s příkladem 2_5_7_prehled_chyb.xls, kde na každém listě je předveden jeden typ chyby s možnostmi, kdy k takové může dojít.

Na listě nazvaném „HODNOTA“ jsou ve sloupcích B a C uvedeny operandy, tj. hodnoty, se kterými provedeme výpočet ve sloupci D. Ve sloupci E jsme tento výpočet ještě pro větší názornost rozepsali. V posledním sloupci F je stručné vysvětlení k dané chybě.

V řádku 4 mezi sebou násobíme hodnoty. Jedna z buněk, na které odkazujeme ve vzorci, však obsahuje textový údaj.

V řádku 5 byla zavedena funkce DZÍSKAT, nenalezla však hodnotu zadanou v oblasti kritérií.

V řádku 6 je funkce znak s argumentem -5. Argumentem této funkce však mohou být pouze kladná čísla v intervalu 0-255.

Řádek 7 ukazuje, že k této chybě dojde i tehdy, jestliže nesprávně zapíšeme maticový vzorec. Zápis maticového vzorce je potřeba ukončit klávesovou kombinací CTRL+SHIFT+ENTER, nikoliv pouhým stisknutím klávesy ENTER.

Poslední řádek 8 ukazuje funkci POZVYHLEDAT, kde byl chybně uveden její první argument. Tento argument odkazuje na oblast, ale přípustná je zde pouze jedna hodnota (numerická, textová nebo logická).

Na listě nazvaném „NAZEV“ si ukážeme tři typické možnosti, kdy se zobrazí toto chybové hlášení.

Na řádku 4 provádíme výpočet, kde operandem je název oblasti (cena), který nebyl definován nebo byl odstraněn.

Na řádku 5 jsme zadali jako argument název oblasti (činitel), který nebyl definován nebo byl odstraněn.

Na řádku 6 byl použit název funkce, která neexistuje.

POZOR – tuto chybu jsme umístili do samostatného sešitu 2_5_7_chyba_ref.xls. Je to z toho důvodu, že při otevření sešitu se zobrazí upozornění – vysvětlení viz dále. Při otevření toto upozornění ignorujeme křížkem nebo zvolíme příkaz „Neaktualizovat“.

List „REF“ ukazuje chybu, ke které dojde, když v odkazu nebo v argumentu funkce byla použita adresa odstraněné buňky.

Na řádku 4 došlo k případu, že buňka, která byla operandem vzorce, byla odstraněna. Adresa odstraněné buňky je v takovém případě nahrazena již přímo ve vzorečku chybovým hlášením #ref!, stejně jako se zobrazuje v samotné buňce.

Obdobný případ je na řádku 5. Zde funkce ZAOKROUHLIT používala jako argument odkaz na buňku, tato buňka však byla odstraněna. I v tomto případě se již v samotném vzorci zobrazí chybové hlášení #ref! místo argumentu.

Řádek 6 – zde je ve vzorci použit název listu, který v sešitě chybí (byl odstraněn) nebo neodpovídá jeho název. Právě k tomuto řádku se vztahuje i upozornění, zobrazované při otevření sešitu. Vzhledem ke skutečnosti, že zdrojová data byla odstraněna, nemá však význam – propojení neexistuje.

Na listě „NUM“ předvádíme chybové hlášení, které indikuje numerickou chybu. Tato numerická chyba je specifická pro některé funkce. Mimoto k ní může dojít, je-li výsledkem příliš vysoká nebo příliš nízká hodnota.

Řádek 4 – funkce DZÍSKAT nalezla vícekrát stejnou hodnotu (číslo 171).

Řádek 5 – funkce FAKTORIÁL (tj. násobení řady hodnot l*2*3*4*n) dosáhla extrémně vysoké hodnoty. Poslední přípustná hodnota této funkce pro n=170.

Řádek 6 – funkce DATUM má v argumentu uvedeny odkazy na buňky, které neobsahují číselnou hodnotu (v tomto případě jsou buňky, na které se odkazují argumenty, prázdné).

Řádky 7 a 8 se vztahují k funkci ÚROKOVÁ.MÍRA. V prvním případě musí být jeden z argumentů záporné číslo. Ve druhém případě je nevhodná hodnota (0,02) pro poslední argument (zvaný Odhad). Hodnota je příliš nízká, jestliže ji zvýšíme o řád, funkce najde správný výsledek.

Řádky 9 a 10 ukazují situaci, kdy výsledek přesahuje přípustné maximální a minimální numerické hodnoty, se kterými program může pracovat.

List „NA“ ukazuje několik operací, kde je nedostupná hodnota, se kterou funkce pracuje. K této chybě může dojít v případě maticové funkce (řádky 4-6). Dalším příkladem je funkce POZVYHLEDAT, kde může nastat jedna z následujících příčin:

chybná adresa oblasti argumentu

chybné třídění prohledávané oblasti

nenalezená hodnota

K nejtypičtějším chybám patří chyba dělení nulou, která dává chybové hlášení #DIV/0!. Chybu si prohlédneme v samostatném příkladu 2_5_7_deleni0.xls, který jsme rozšířili o praktickou ukázku, viz obrázek

Nejprve se zaměříme na list nazvaný Dělení nulou. K chybě může dojít v těchto případech:

do buňky, na kterou se při dělení odkazujeme, je přímo zapsána hodnota 0 (řádek 4)

do buňky, na kterou se při dělení odkazujeme, nebyla zapsána žádná hodnota a prázdná buňka je považována za nulu (řádek 5)

zkopírujeme-li vzorec do oblasti, kde jsou zatím nevyplněné buňky (řádek 6)

provedeme-li určité výpočty, jejichž výsledkem jsou extrémně nízké nebo vysoké hodnoty. Chyba vznikne na základě algoritmu výpočtu. V řádku 6 a 7 toto ukazujeme na výpočtu mocniny (přímá operace nebo s použitím funkce POWER).

Pro ilustraci této chyby jsme použili ještě tabulku na listě Praktická ukázka. Zde počítáme průměry výsledků podniku za čtvrtletí. Funkce PRŮMĚR matematicky znamená součet hodnot / počet hodnot. Jsou-li v tabulce nevyplněné hodnoty, které jsou považovány za nulu, funkce vrací chybu #DIV/0!.

Informační funkce pro testování chyb

Popsané chyby můžeme testovat pomocí několika informačních funkcí, které vyhodnocují , zda hodnota je chyba a o jaký typ chyby se jedná:

Funkce CHYBA.TYP

Tato funkce vrátí číslo chyby podle následující tabulky:

1. NULL! 1
2. DIV/0! 2
3. HODNOTA! 3
4. REF! 4
5. NÁZEV? 5
6. NUM! 6
7. N/A 7

Není -li chyba nalezena, je výsledkem funkce #N/A

Blíže se na funkci podíváme v příkladu 2_5_7_testovani_chyb.xls na listě „chyba.typ“, viz také obrázek.

V tabulce ve sloupci D je funkce, která vrací chybu, ve sloupci E si můžeme prohlédnout rozpis vzorce zadaného do sloupce D, ve sloupci F je výsledek funkce CHYBA.TYP, ve sloupci G rozpis tohoto vzorce a v posledním sloupci najdeme stručný komentář.

V příkladu přidáváme TYP, kde výsledek funkce CHYBA.TYP je využíván ve funkci INDEX.

Funkce JE.CHYBNHODN

Tato funkce testuje hodnotu, zda je chybou libovolného typu (#NULL!, #DIV/0!, #HODNOTA!, #REF!, #NÁZEV?, #NUM!, #N/A).

Různé typy chyb vyhodnocené touto funkcí si prohlédneme v příkladu 2_5_7_testovani_chyb.xls na listě „je.chybnhodn“.

Ve sloupci B uvádíme vzorce, které vrátily chybu, ve sloupci C jejich rozpis, ve vzorci E chybu testujeme pomocí funkce JE.CHYBHODN, sloupec F uvádí rozpis vzorce JE.CHYBHODN a ve sloupci G popisujeme druh chyby.

Výsledkem této funkce jsou logické hodnoty PRAVDA nebo NEPRAVDA.

Funkce JE.CHYBA

Tato funkce testuje hodnotu, zda je jakoukoliv chybou kromě chyby #N/A.

Různé typy chyb vyhodnocené touto funkcí si prohlédneme v příkladu 2_5_7_testovani_chyb.xls na listě je.chyba.

Ve sloupci B uvádíme vzorce, které vrátily chybu, ve sloupci C jejich rozpis, ve vzorci E chybu testujeme pomocí funkce JE.CHYBA, sloupec F uvádí rozpis vzorce JE.CHYBA a ve sloupci G popisujeme druh chyby.

Výsledkem této funkce jsou logické hodnoty PRAVDA nebo NEPRAVDA.

Funkce JE.NEDEF

Tato funkce testuje hodnotu, zda je chybou typu #N/A

Různé typy chyb vyhodnocené touto funkcí si prohlédneme v příkladu 2_5_7_testovani_chyb.xls na listě „je.nedef“.

Ve sloupci B uvádíme vzorce, které vrátily chybu, ve sloupci C jejich rozpis, ve vzorci E chybu testujeme pomocí funkce JE. NEDEF, sloupec F uvádí rozpis vzorce JE. NEDEF a ve sloupci G popisujeme druh chyby.

Výsledkem této funkce jsou logické hodnoty PRAVDA nebo NEPRAVDA.

Ve spojení s funkcí KDYŽ, umožňují tyto funkce ošetřovat případné chybné hodnoty.

Funkce pro testování chyb by samy o sobě neměly velký význam. Dají se však využít spíše pro ošetření chyb v tom smyslu, aby k nim nedošlo. V tomto případě však musíme tyto funkce propojit s jinými funkcemi – zejména např. s funkcí KDYŽ nebo INDEX. Této problematice budou věnovány další samostatné příklady.

V příkladu 2_5_7_testovani_chyb.xls připojujeme ještě list „Souhrnná tabulka“, kde shrnujeme výsledky všech popsaných informačních funkcí pro testování chyb.