# KDYŽ se řekne Excelu

Podmíněné formátování a součty, ověření dat

autor: Ing. Petr Pecháček
http://officir.web3.cz

Sešit Excelu s uvedenými příklady (komprese ZIP)

## Na úvod

### Funkce KDYŽ, logické funkce

Abychom mohli ověřovat vstupy a případně je zahrnovat do součtu, je třeba znát základní principy vkládání podmínek. Pro tyto účely slouží především funkce KDYŽ. Její syntaxe, jak asi víte, je:

    KDYŽ(podmínka; hodnota či vzorec v případě splnění podmínky; hodnota či vzorec v případě nesplnění podmínky)

Do místa větvení lze také dosadit další "rozcestník" KDYŽ. Takto může být vnořeno až sedm "křižovatek Y". Pokud luštíte několik vnořených funkcí v sešitu od druhé osoby, doporučuji nakreslit si vývojový diagram.

Abychom ulehčili funkci KDYŽ a pomohli ji rozšířit, upneme pozornost k logickým funkcím A, NE a NEBO. Funkce A(podmínka 1; podmínka 2; ...) vrací hodnotu PRAVDA, jestliže jsou současně splněny všechny námi zadané podmínky. Funkce NE(logická hodnota) obrací výsledek, který je jí předán v podobě PRAVDA či NEPRAVDA. Funkce NEBO(podmínka 1; podmínka 2; ...) vrací PRAVDA, pokud je splněna alespoň jedna zadaná podmínka. Podívejte se na příklady.

Příklad z řádku 12 je trochu specifický, neboť vynechává přímé užití funkce KDYŽ (viz řádek níže). S tímto zápisem se častěji setkáte u podmíněného formátování. Vzorec z řádku 14 doporučuji nepoužívat. S funkcí STEJNÉ jste se již mohl setkat dříve při práci s textem. Funkce ZVOLIT pak naznačuje možné užití v případě, kdy tvoříme podmínky na základě kladných celých čísel a vlastně je takovou jednoduchou ukázkou, kterou znají programátoři VBA jako SELECT..CASE, což si můžete představit jako křižovatku s libovolným množstvím ze středu jdoucích ulic narozdíl od KDYŽ, představující pouze tvar "Y".

Pozn. 1: Je-li v příkladu zmíněno, že se jedná o maticový vzorec, pak pro tuto chvíli postačí vědět, že takové vzorce se potvrzují stiskem Ctrl+Shift+Enter místo klasického ENTER.

Pozn. 2: Nejste sami, komu funkce NE "ne a ne" jít na rozum. Ale! Někdy je jednodušší stanovit podmínku pro opak toho, co chceme, resp. doplněk množiny a pak před podmínku postavit NE. Vzpomeňte si na to, až budete sedět nad algoritmy pro VBA! Něco ze života? Když budete chtít najít jeden velký korálek mezi tisíci malými, použijete síto. No a to je přeci řešení vycházející z podstaty malých korálků, tj. pracujeme s malými korálky, abychom získali to, co hledáme - korálek velký.

### Funkce pro ověření obsahu

Někdy můžeme potřebovat pracovat jen s daty určitého typu (textové buňky, číselné buňky, buňky prázdné, ...). Pro tyto účely slouží informační funkce typu "JE". Abyste věděli, co můžete očekávat, prohlédněte si následující příklady. Řádek 13 si obzvláště vryjte do paměti, neboť prázdný řetězec (dvojí uvozovky vedle sebe) se vyskytuje v buňkách často (například jako výsledek větvení funkce KDYŽ). V následující ukázce je oblast A2:A17 pojmenována "data".

## Podmíněné formátování

Podmíněné formátování je postaveno na otázce "KDYŽ", přestože vlastní funkce KDYŽ se třeba ani v zápisu neobjeví. Takové formátování definuje pouze vizuální podobu písma, pozadí a ohraničení v případě splnění kritérií. Volba se skrývá pod menu Formát / Podmíněné formátování....
Lze porovnávat obsah buňky s hodnotou (hodnotami v případě rozmezí) nebo vložit vlastní vzorec. Maximálně lze využít tří nezávislých podmínek. Není-li podmínka splněna, formát buňky se automaticky přenastaví do podoby, jakou jste buňce dali v tabulce listu. Kopírujete-li formát buňky, pak s ním kopírujete i podmíněné formátování. Je-li v podmínce obsažen relativní odkaz, pak je tento odkaz také relativně použit v ostatních buňkách. Jak tedy nadefinovat stejnou podmínku pro sousedící buňky?

1. 1. Definujte podmíněný formát s použitím relativních odkazů pro první buňku.
2. 2. Táhněte pravým tlačítkem myši úchytný bod této buňky přes buňky sousedící.
3. 3. Zvolte Vyplnit formáty...

Je čas na některé užitečné příklady.

## Podmíněné součty

K podmíněnému sčítání, tedy sčítání položek, které splňují určitou podmínku, slouží především funkce SUMIF (případně jen samotná SUMA) a funkce COUNTIF. Úplnou syntaxi zápisu doporučuji nastudovat v nápovědě. My se vrhneme přímo na praktické příklady.

Příklad sběru ukazuje, jak pracovat s tabulkou, kterou jste obdrželi od druhé osoby. Taková forma tabulky není příliš vhodná a její lepší zpracování najdete v přiloženém sešitu. Tu správnou "štábní kulturu" pak podobným tabulkám dávají souhrny a kontingenční tabulky, ale to jsme již mimo rámec dnešního tématu.

## Ověřování dat

Ověřování dat slouží k minimalizaci překlepů a usnadňuje výběr platných hodnot zadávaných do buňky. Volbu najdete pod menu Data / Ověření. Pro data můžete určovat dolní a horní mez, nastavit délku řetězce, nechat vybírat položky ze seznamu a v neposlední řadě omezit vstupy podle vlastního vzorce. Asi nejčastější volbou je Povolit: seznam. Zdrojem je pak seznam položek oddělených navzájem středníkem, odkaz na oblast listu danou adresou nebo jejím jménem a v přiloženém sešitu naleznete i "švindl" - seznam vzorců. Dialog na základě vstupních hodnot je dán často opomíjenými nastavením na kartě Chybové hlášení. Prostudujte i kartu Zpráva při zadávání.

Styl "stop" je pro zadavatele dat konečnou instancí, kdy mu není dovoleno zadat jiná data než povolená. Ostatní dvě činí z ověření dat jakousi databanku dat, ze které uživatel čerpat může, ale nemusí.

Malý trik: Zvolte Nastavení / Povolit: jakoukoliv hodnotu, dále zrušte zaškrnutí pro zobrazení chybových hlášení na kartě Chybová hlášení a vyplňte obsah zprávy na kartě Zpráva při zadávání. Trik slouží k zobrazení jakési nápovědy při klepnutí na buňku. Výhodu oproti komentáři poznáte ve chvíli, kdy se buňka vyskytuje na kraji okna. Komentář se beze studu rozbaluje mimo viditelnou část obrazovky, tato nápověda je stále viditelná (MS Excel 2000).

Ověřování dat má charakter akce "akce-reakce", proto popis psanou formou je komplikovaný a nebude zde detailně probírán. Užitečné příklady najdete v přiloženém sešitu. Pouze jejich testováním si je osvojíte. Jen si dovolím pár zásad:

- Krátké a neměnné seznamy zapisujte přímo do dialogu, položky oddělujte středníkem.
- Delší seznamy vytvářejte na zvláštních listech a držte se pravidla "co seznam, to jeden sloupec".
- Při odkazování se na seznam buněk pojmenované oblasti je velmi efektivní použít oblast definovanou dynamicky (viz 3. díl).
- Nic vám nebrání používat spolu s ověřením podmíněné formátování.
- Zbytečnou práci si ušetříte při vzpomínce na existenci volby Použít tyto změny u všech ostatních buněk se stejným nastavením na kartě Nastavení.

### Bohužel...

Styl "stop" nikterak nezabrání vstupu kopírováním hodnot. A nejenže nezabrání, kopírovaná data smažou(!) veškerá ověření vstupu dané buňky. (Náprava tohoto nedostatku je možná, ovšem podle všeho jen s pomocí VBA.) S problémy jsem se také setkal, když jsem soubor vytvořený původně v Excelu 2000 otevřel ve verzi 97. Ověření prostě zmizelo...

Sešit Excelu s uvedenými příklady (komprese ZIP)

pro časopis