Tabulky - informační funkce

V dnešní lekci si řekneme něco o informačních funkcích, které můžete do tabulky vkládat přes volbu Vložit - Funkce - Informace. Jejich seznam můžete najít v nápovědě ke všem tabulkovým funkcím, když si seznam vyfiltrujete podle skupiny Info.

Tato skupina funkcí vám pomůže při automatické kontrole správnosti dat v tabulce, zjištění chyb ve vzorcích a dalších činnostech. Opět si můžete otevřít moji tabulku, uložit si kopii na svůj Disk a zkoušet. Většinu uváděných příkladů najdete v tabulce.

A teď už k jednotlivým funkcím.

ISBLANK()

Zápis je jednoduchý, funkce má jen jeden parametr a použijeme ji třeba takto. =ISBLANK(B2) Funkce vrací TRUE, pokud je buňka B2 prázdná, jinak FALSE. Vyzkoušejte si v tabulce, že pokud do buňky zadáte například mezeru, funkce ISBLANK správně vrátí FALSE, přestože vizuálně buňka vypadá jako prázdná. Kromě mezer může buňka obsahovat i řadu dalších neviditelných znaků, které se do ní mohou dostat vložením přes CTRL-V a podobně.

ISDATE()

Opět funkce s jedním parametrem, kterým je adresa jedné buňky. =ISDATE(B3) Opět vrací TRUE nebo FALSE podle toho jestli hodnota v buňce je datum nebo ne. Pokud používáte vzorce nebo skripty, které spoléhají na to, že jako vstup bude datum, určitě funkci použijte buď pro ruční opravu vstupních dat, nebo vlastní výsledek funkce. To co vizuálně vidíme v buňce záleží totiž i na tom jak je hodnota v buňce naformátovaná. Na obrázku například v buňce B3 vidíme hodnotu 0:00:00. Tato hodnota by mohla být datum naformátované aby zobrazovala jen čas (Formát - Číslo - Čas), ale taky by to mohl být obyčejný text. Teprve když se podíváme do stavového řádku, vidíme, že buňka obsahuje hodnotu 12.1.2020 0:00:00 a jde tedy o datum. Takže pokud ve vašich vzorcích budete pracovat s daty, je určitě dobré se funkcí ISDATE přesvědčit jestli opravdu o data jde.

ISEMAIL()

Funkce pro kontrolu formální správnosti e-mailové adresy. Zápis je opět jednoduchý a opět funkce vrací TRUE nebo FALSE. =ISEMAIL(B4) Funkce vám samozřejmě nezjistí, jestli e-mailová adresa například pepanovak@seznam.cz existuje, ale pouze zkontroluje, jestli e-mail odpovídá daným pravidlům, tedy jestli obsahuje znaky zavináč a tečku a nejsou v něm použity nepovolené znaky. Vyzkoušejte si, že funkce správně vrátí FALSE například na e-mail ve kterém použijete českou diakritiku, třeba pepanovák@seznam.cz Poslední část e-mailu za tečkou je název top-level domény a i tu dokáže funkce zkontrolovat. Přesvědčete se, že překlep pepanovak@seznam.cs vrací FALSE, protože cs není platná top-level doména.

ISNUMBER()

Opět obdoba předchozích funkcí, tentokrát kontrola jestli je v buňce zadané číslo, zase vrací TRUE nebo FALSE. =ISNUMBER(B5) U čísel se můžete dostat do problémů, když použijete u čísel omylem desetinnou tečku místo naší čárky. Pokud zadáte do buňky hodnotu například 320.1 pak vám hodnotu automaticky zarovnají vlevo, protože hodnotu vyhodnotí ne jako číslo ale jako text. Ale může se stát, že máte sloupec zarovnaný na střed a chyby si nevšimnete. Pak je dobré funkcí ISNUMBER ověřit co vlastně v buňce máme. Ještě může nastat horší situace, pokud číslo, které omylem zadáte s tečkou bude začínat jako existující české datum. Zkuste si do některé buňky zadat hodnotu 2.2 nebo třeba 3.11, já jsem v tabulce v buňce B6 použil hodnotu 3.2. Zjistíte, že tabulky hodnotu zarovnaly doprava jako číslo a když se podíváte do stavového řádku, zjistíte, že buňka opravdu obsahuje kompletní české datum, mém případě 3.2.2020. V buňce ale vidím hodnotu 3.2 a snadno si ji můžu splést se správně zadaným číslem 3,2.

Další problém je, že funkce ISNUMBER v buňce A6 bere datum jako číslo a tedy vrací TRUE, takže ta nám s odhalením našeho překlepu nepomůže. Museli bychom použít funkci ISDATE abychom odhalili, že do buňky B6 jsme omylem místo čísla 3,2 zadali ve skutečnosti datum 3. února. Pro buňku B6 jsem kontrolu ISDATE dal do buňky A7 a vidíte, že vrací TRUE. Pokud by v buňce B6 bylo číslo, vracela by samozřejmě FALSE. Ještě horší je pokud si zkusíte k buňce B6 přičítat nějaké hodnoty. V buňce D6 je vzorec =B6 + 50 a výsledek je 24.3 takže ne číslo, ale tabulky k datu 3. února přičetly 50 dní a vzniklo ne číslo, ale opět datum, 24. březen 2020. Teď se podívejte na buňku D7 kde je vzorec =SUM(D5:D6) tedy sečti hodnoty 10 a naše omylem vzniklé březnové datum. Vidíte, že výsledek je 43924 a pokud si nevšimnete, že vlastně sčítáte číslo a datum, budete marně bádat, kde se tenhle nesmyslný výsledek vzal. A kde se tedy vzal, proč je výsledek právě takový? Funkce SUM sčítá pouze čísla a protože zjistila, že jedna z hodnot je datum, převedla si ho interně na číslo. V tabulkách k tomu slouží funkce N() detailní popis je zde , kterou jsem pro kontrolu výsledku zapsal do buňky E6. Funkce N() se v Google tabulkách příliš nepoužívá je zařazena jen kvůli kompatibilitě vzorců s Excelem nebo Open Office. Nicméně funguje a v případě, že je jejím parametrem datum, vrací počet dní mezi zadaným datem a 30.12.1899. Jak vidíte v buňce E6 v našem případě je tento počet dní roven 43914. No a k výsledku už funkce SUM připočetla 10 z buňky D5 a máme vysvětlení našeho na první pohled šíleného výsledku 43924 v buňce D7. Takže vidíte, že je opravdu potřeba si dávat pozor, zda údaje se kterými se chystáme pracovat jsou v pořádku a ve správném formátu.

ISTEXT()

Opět obdoba předchozích funkcí, tentokrát test, zda buňka obsahuje text, opět vrací TRUE nebo FALSE. =ISTEXT(B8) Při prázdné buňce vrací funkce FALSE, ale stejně jako jsme viděli u funkce ISBLANK pokud do buňky vložíme například mezeru nebo jiný neviditelný znak, bude nám už funkce vracet TRUE. V tabulce jsem do buňky B8 zadal několik mezer, vidíte, že buňka vypadá prázdná, ale ISTEXT v buňce A8 hlásí TRUE, takže nějaký neviditelný obsah v buňce je. Zkontrolovat to můžeme třeba funkcí JOIN, zadal jsem jí do buňky B9 ve tvaru =JOIN("";"x";B8;"x") Funkce vezme znak x, za něj připojí obsah buňky B8 a pak opět znak x. Výsledek vypadá takto, vidíte, že obě x jsou od sebe oddělena několika mezerami.

Pokud bude v buňce vzorec jehož výsledkem je text, pak funkce ISTEXT vrátí TRUE. Příklad vidíte v buňce A9 kam jsem zadal vzorec =ISTEXT(B9) a v buňce B9 máme náš vzorec s funkcí JOIN. No a protože výsledkem funkce JOIN je text, bude výsledek v buňce A9 TRUE.

ISNONTEXT()

Opak předchozí funkce ISTEXT. Vrací FALSE pokud je parametr přímo text, třeba =ISNONTEXT("xxx") nám vrátí FALSE, nebo vzorec =ISNONTEXT(B10) v buňce A11, nám vrátí FALSE v případě, že bude v buňce jakýkoliv text, tedy i mezery a TRUE pokud obsah buňky smažeme, nebo zadáme číslo datum nebo vzorec.

ISFORMULA()

Opět jednoduchá funkce, která vrací TRUE pokud je v cílové buňce vzorec, jinak vrací FALSE. Příklad vidíte v buňce A12, je tam vzorec =ISFORMULA(B12) Protože vrací TRUE, znamená to, že hodnota 5, kterou vidíme v buňce B12 není prosté číslo zadané do buňky, ale výsledek vzorce. Můžete se přesvědčit, že v buňce B12 je vzorec =SUM(2;3)

A pokud bychom buňku B12 otestovali funkcí ISNUMBER, vrátila by nám TRUE, protože výsledkem funkce SUM je číslo.

ISODD()

Funkce se používá pro zjištění, zda je číslo liché, pak vrací TRUE, jinak FALSE. Pro prázdnou buňku vrací samozřejmě FALSE. Stejně jako u funkce ISNUMBER se můžete dostat do problémů, když se vám povede místo desetinné čárky použít tečku a do buňky se tak uloží datum. V buňce B13 je místo čísla 2,2 datum 2.2.2020 a ISODD ho vyhodnotila FALSE, tedy sudé.

Pokud bude v buňce text, skončí funkce chybou s vysvětlující poznámkou, ukázka je v buňce A14.

ISEVEN()

Obdoba minulé funkce, tato vrací TRUE pro sudá a FALSE pro lichá čísla. A stejný možný problém s datem jako u minulé funkce. Pro prázdnou buňku vrací TRUE.

ISLOGICAL()

Funkce vyhodnocuje, zda je parametr logická hodnota TRUE nebo FALSE. Použití vidíte na v buňkách A16 a A17. V buňce B16 je vložené zaškrtávací tlačítko, které vrací TRUE nebo FALSE podle stavu zaškrtnutí, takže funkce ISLOGICAL(B16) v buňce A16 správně vrací TRUE. Hodnotu TRUE nebo FALSE lze do buňky zadat i jako běžný text, tabulka ho správně rozpozná jako logickou hodnotu a vždy ho v buňce vypíše velkými písmeny, i když jste psali malými. Takto je hodnota FALSE zadána do buňky B17. Ještě drobná zajímavost, ve vzorcích lze používat i české verze PRAVDA a NEPRAVDA, ale pokud PRAVDA napíšete do buňky, tabulky to vezmou jako běžný text a ne logickou hodnotu.

ISREF()

Funkce vrací TRUE pokud je parametr platný odkaz na buňku, jinak FALSE. Parametr musí být skutečný odkaz na buňku, pokud bude zadán jako text, třeba =ISREF("A1") vrátí funkce FALSE.

TYPE()

Funkce, která může zastoupit řadu ostatních jednoúčelových funkcí, protože vrací číslo podle typu obsahu konkrétní buňky. V tabulce jsou ukázky na řádcích 19 - 27. V případě, že je v buňce libovolný vzorec nebo funkce, TYPE vyhodnotí pouze jakého typu je jeho výsledek. Nerozlišuje tedy, jestli je výsledek přímo zadaný v buňce, nebo je výsledkem funkce nebo vzorce. A jaké hodnoty funkce vrací? 1 - pro číslo ať už zadané nebo jako výsledek vzorce a také pro prázdnou buňku 2 - pro text 4 - pro logické hodnoty TRUE, FALSE opět buď zadané nebo jako výsledek vzorce 16 - pokud buňka obsahuje chybu jako výsledek vzorce nebo funkce 64 - pro pole 128 - pro všechny ostatní případy, například pro obrázky vložené do buňky

ISERROR()

Funkce vyhodnocuje, jestli buňka nebo výraz obsahuje chybu. Nerozlišuje typ chyby, ale vrací TRUE pro všechny typy chyb #DIV/0!, #N/A, #NAME?,#NULL!, #NUM!, #VALUE!, #REF!. Několik příkladů chyb je v tabulce na řádcích 28 - 31.

ISERR()

Funkce vrací TRUE pokud buňka obsahuje jakoukoliv jinou chybu než #N/A. Pro chybu #N/A, nebo pokud buňka chybu vůbec neobsahuje vrací FALSE. Ukázky jsou na řádcích 32 - 34.

ISNA()

Opak minulé funkce, vrací TRUE, pokud je v cílové buňce chyba #N/A, jinak vždy vrací FALSE. Ukázky na řádcích 35 - 37.

ERROR.TYPE()

Obdoba funkce TYPE, ale pro chyby. Vrací číslo podle toho o jaký typ chyby jde. Vrácená čísla pro jednotlivé chyby jsou následující. 1 - #NULL! 2 - #DIV/0! 3 - #VALUE! 4 - #REF! 5 - #NAME? 6 - #NUM! 7 - #N/A 8 - pro všechny ostatní typy chyb

NA()

Funkce, která vrací chybu #N/A. K čemu může být dobré záměrně vyvolat chybu? Může nás to upozornit, že v našich datech je něco špatně a na základě toho zastavit další výpočty, které by byly nesprávné. Pro ukázku jsem v buňce A39 použil vzorec =IF(ISBLANK(B39);0;B39) Vzorec by měl otestovat, jestli je do buňky B39 zadaná hodnota. Funkci IF probereme v jiné lekci, ale funguje tak, že vyhodnotí první parametr a pokud je výsledek TRUE, vrátí hodnotu z druhého parametru, pokud FALSE, vrátí hodnotu z třetího parametru. Pokud tedy necháme buňku B39 prázdnou, budeme mít v buňce A39 hodnotu 0, jinak hodnotu z buňky B39. Pokud uživatel do buňky B39 zapomene zadat hodnotu a výsledek z A39 (nulu) někde jinde použijeme k výpočtu, dostaneme výsledek jako kdyby uživatel do B39 zadal nulu, čili nesmyslný výsledek. Lepší tedy bude funkci IF upravit takto. =IF(ISBLANK(B39);NA();B39) Pokud uživatel do buňky B39 nic nezadá, bude výsledek v A39 chyba #N/A a další výpočty používající hodnotu z buňky A39 vůbec neproběhnou, resp. také skončí s chybou.

CELL()

Funkce CELL() nám umožní zjistit o buňce tabulky nejrůznější údaje. Funkce má dva parametry, první udává jaké údaje o buňce chceme zjistit a druhá adresu buňky. První parametr může nabývat těchto hodnot. "address" Vrací adresu buňky z druhého parametru. Příklad je na řádcích A41 a A42. Všimněte si, že funkce CELL vrací absolutní odkaz na buňku, čili včetně znaků $. V buňce A42 je vzorec =CELL("address";B42:D53) tedy není použita jedna buňka, ale větší oblast. Vidíte, že funkce vrací adresu levého horního rohu oblasti. Místo adresy oblasti můžeme samozřejmě použít i pojmenované oblasti, jako v buňce A43 kde je vzorec =CELL("address";data) No a oblast data má adresu D13:E19, takže vidíte, že vzorec správně vrací levý horní roh oblasti data, čili hodnotu $D$13. "col" S tímto parametrem CELL vrací číslo sloupce. Sloupce jsou číslované od jedničky a opět platí, že je-li oblast větší než jedna buňka vrací se číslo sloupce pro levý horní roh oblasti. "row" Obdoba col, jen vrací číslo řádku levého horního rohu. "contents" Vrací obsah buňky. V buňce A45 si všimněte, že pokud buňka obsahuje chybu, přenese funkce CELL chybu včetně hlášky. "prefix" Vrací znak, který udává zarovnání textu v buňce. Pro text zarovnaný doleva vrací jednoduché uvozovky ('), pro zarovnání doprava normální uvozovky (") a pro zarovnání na střed znak stříška (^). Pozor, pokud vzorec použijete a poté přes menu změníte zarovnání buňky, výsledek funkce CELL se nezmění. Ke změně dojde až ve chvíli, kdy změníte obsah buňky. "type" Vrací typ dat v buňce. Možné hodnoty jsou "b" (blank) pro práznou buňku, "l" (label) pro text a "v" (value) pro jakýkoliv jiný typ dat. "width" Vrací počet znaků 0 (nula) který se vejde do šířky buňky při základní velikosti písma. Výsledek vzorce se bohužel nezmění když šířku sloupce dodatečně změníme, takže použití je poměrně omezené.

Pokud máte jakékoliv doplňující otázky, obraťte se na nás na ahoj@appsatori.eu

Rádi Vám poradíme.:)