Předchozí díly volného seriálu:
- Důvěřuj, ale prověřuj – funkce pro kontrolu hodnot
- Funkce pro práci s textem – nahrazování, převod z čísel a na čísla, další možnosti
- Funkce pro práci s textem – spojování, pročištění, vyhledávání
- Možnosti kopírování a vkládání obsahu buněk
- Vkládání a úpravy funkcí pomocí průvodce
- Funkce v OpenOffice.org Calc – síla, kterou je obtížné zkrotit
- Logické funkce 2 – AND, OR
- Logické funkce 1 - funkce IF
Stanovení frekvencí odpovědí – spočtení výskytů
Funkce typu count
V příkladu je 14 respondentů, u kterých je zaznamenáno jejich pohlaví, ve dvou případech odpověď chybí. Pomocí funkce COUNTIF zjistíte, kolikrát se v oblasti B2:B15 vyskytuje záznam „muž“ (řádek 17) a kolikrát záznam „žena“ (řádek 18).
Funkci COUNTIF je možné zadat vyhledávací kritérium různými způsoby – v příkladu je použit odkaz na buňku s vyhledávacím vzorem, kterým je přesný text. V tomto případě pak COUNTIF hledá přesnou shodu. Pokud bude buňka v prohledávané oblasti obsahovat byť jen mezeru před či za slovem, nebude toto nalezeno, resp. započítáno. COUNTIF však ignoruje velikost písmen.
Je tedy potřeba vědět, v jakých datech se bude vyhledávat, a tomu přizpůsobit taktiku – buďto nejprve upravit prohledávaná data (např. odstranit nadbytečné mezery pomocí TRIM), nebo použít jako vyhledávací vzor regulární výraz: chcete-li vyhledat „muž“ v jakémkoli jeho výskytu, použijte vzor „.*muž.*“ - vyhledají se tak i tvary jako „ muž “, „muži“, „vzmuž se“ atp. I v tomto případě je ignorována velikost písmen.
Uvedený vzor je zapsán pomocí regulárního výrazu: tečka znamená libovolný znak a hvězdička za ní pak libovolné množství opakování takového znaku, prakticky tedy cokoliv. Regulární výrazy vypadají na první pohled složitě, ale s jejich pomocí lze poměrně jednoduše a stručně vyřešit i náročné operace s textem.
V příkladu jsou dále použity funkce COUNTBLANK a COUNTA. Prvně jmenovaná spočítá, kolik je v zadané oblasti prázdných buněk. Buňky musejí být opravdu prázdné – pokud je v buňce vzorec, byť jeho výsledkem je prázdný řetězec (např =""), nebude tato buňka započtena jako prázdná. Funkce COUNTA vrací právě opačný výsledek, tj. počet neprázdných buněk v zadané oblasti a započteny jsou i buňky se vzorci, jejichž výsledkem je prázdný řetězec. Součtem výsledků obou funkcí nad stejnou oblastí je počet buněk v této oblasti.
K dispozici je ještě funkce COUNT, která spočítá, kolik je v zadané oblasti či seznamu argumentů číselných hodnot, přičemž se započítávají i vzorce, jejich výsledkem je číslo.
Funkce typu lookup
Velmi výkonnou sadu funkcí představují VLOOKUP, HLOOKUP a LOOKUP. Jejich účelem je jakési párování či dohledávání dat. Vše bude lépe zřetelné z praktické ukázky.
Tabulka v oblasti A11:B23 obsahuje údaje o obratu za všechna pracoviště podniku, tabulka v oblasti A1:B8 pak adresy několika těchto vybraných pracovišť, ke kterým potřebujete doplnit údaje o obratu. K tomu výborně poslouží funkce VLOOKUP, kterou zapíšete do sloupce C na řádky 2 až 8. Důležitým požadavkem je, aby obě tabulky byly propojitelné pomocí nějakého jednoznačného identifikátoru jednotlivých záznamů. V tomto případě je to „číslo pracoviště“.
Funkce VLOOKUP zapsaná v buňce C2, tj. =VLOOKUP(A2;$A$12:$B$23;2;0)
, pak udělá to, že kód pracoviště uvedený v buňce A2 bude hledat v prvním sloupci zadané oblasti A12:B23 (zde zapsáno pomocí $ fixace řádků a sloupců kvůli kopírování vzorce) a jakmile tento kód pracoviště najde, jako výsledek vrátí hodnotu z 2. sloupce této oblasti, v tomto případě z buňky B23. Poslední čtvrtý parametr funkce VLOOKUP – zde je to 0 – udává, jaká „taktika“ se použije při hledání zadané hodnoty. Použijte 1, pokud si jste jisti, že první sloupec prohledávané oblasti je setříděn, a to vzestupně. Jinak použijte 0, jako v uvedeném příkladě. Schválně zde vyzkoušejte nastavit 1.
Pokud jako čtvrtý parametr zadáte 1, VLOOKUP předpokládá, že první sloupec prohledávané oblasti je seřazen vzestupně, použije jednodušší a rychlejší prohledávací mechanismus, ale u nesetříděných dat se vám často stane, že se zastaví příliš brzy s tím, že nenašel shodný záznam (hodnota #N/A), nebo když najde záznam podobný, což může být často nežádoucí. Naproti tomu při nastavení tohoto parametru na 0 je prohledáván vždy celý první sloupec zadané oblasti a hledá se přesná shoda. Tento postup je však výpočetně náročnější, což se projeví v případě použití tisíců funkcí VLOOKUP v sešitu či v případě rozsáhlých prohledávaných oblastí – proto nezadávejte prohledávanou oblast zbytečně velkou (např. všechny řádky v listu).
Jako párovací kritérium lze použít čísla jako ve výše uvedeném příkladu, nebo text jako v následujícím příkladu.
Seskupování dat pomocí VLOOKUP
V tomto případě potřebujete seskupit sumy za jednotlivé měsíce pod sebe tak, aby bylo možné vytvořit přehledový graf. Ve sloupci G je funkce VLOOKUP, která hledá hodnoty z buněk sloupce D v prvním sloupci oblasti A1:B25 (resp. např. A1:B1000) a vrací 2. sloupec z odpovídajícího řádku. Jako párovací kritérium jsou nyní použity řetězce 2009_01, 2009_02 atd. U funkce VLOOKUP je navíc ve sloupci G pomocí funkce ISNA ošetřena návratová hodnota #N/A pro případ nenalezení kritéria, takže se při nenalezení vloží prázdný řetězec:
=IF(ISNA(VLOOKUP(D2;$A$1:$B$25;2;0));"";VLOOKUP(D2;$A$1:$B$25;2;0))
Pro snadné vytvoření posloupnosti kódů ve sloupci D byla využita také funkce VLOOKUP. Nejprve byla do sloupce F jednoduše vložena posloupnost měsíců využitím vkládání seznamů (do buňky F2 zapíšete pouze „leden“ a tažením za pravý dolní roh této buňky rozkopírujete posloupnost měsíců libovolně daleko směrem dolů), rok je pak automaticky doplňován pomocí rozkopírovaného vzorce z E3:
=IF(F3="leden";E2+1;E2)
V buňce E2 je startovací rok a další roky se pak už řídí měsícem ve vedlejším sloupci a rokem o buňku výše). Takto připravené roky a měsíce jsou pak převedeny pomocí vzorce
=E2&"_"&VLOOKUP(F2;$I$2:$J$13;2;0)
na vyhledávací kritérium – k roku a podtržítku je ještě přidán kód měsíce z tabulky v oblasti I2:J13 (ve vzorci opět zafixováno pomocí $ pro účely kopírování). Kódy ve sloupci J jsou vloženy jako text, aby funkce VLOOKUP nevynechala v případě čísla počáteční nulu (šlo by vyřešit jinak, např. formátováním výstupu funkce VLOOKUP pomocí funkce TEXT, kterou znáte z předchozích dílů).
Velmi podobnou funkcí k VLOOKUP je funkce HLOOKUP, která se liší pouze tím, že párovací kritérium hledá v prvním řádku prohledávané oblasti (VLOOKUP v prvním sloupci) a vrací pak hodnotu z buňky v patřičném sloupci a ze zadaného řádku ve vyhledávané oblasti. Vše je jasné z následujícího schématu.
Srovnání funkcí VLOOKUP a HLOOKUP
Rozdíl v obou funkcích si snadno zapamatujete podle jejich prvních písmen: Vertical (vertikální, tj. svislý) a Horizontal (horizontální, tj. vodorovný).
Kromě těchto dvou funkcí existuje ještě obecnější LOOKUP. Její výhodou je, že umí pracovat s kombinací sloupce a řádku či nespojitou oblastí, nevýhodou pak, že neumí vyhledávat v netříděných datech. Vše bude opět lépe zřejmé z příkladu.
Syntaxe funkce LOOKUP
Vzorec v buňce B13 =LOOKUP(A13;C1:G1;B2:B9)
hledá hodnotu z buňky A13, což je číslo 5, v prohledávané oblasti C1:G1 a pokud ji najde (zde ve 4. buňce výběru), tak z výsledkové oblasti B2:B9 vrátí hodnotu z pozice, na které byla nalezena hledaná hodnota v prohledávané oblasti, zde tedy ze 4. buňky, což je „duben“.
Prohledávaná oblast však musí být seřazena vzestupně, což v uvedeném případě neplatí (1, 2, 3, 5, 4) a proto funkce LOOKUP v buňce B11 selhává – hledá číslo 4, při procházení prohledávané oblasti narazí na 3 a pak 5, z čehož usoudí, že 4 již dále nebude (předpoklad vzestupně tříděných dat), jako pozice hledané hodnoty je vzata 3. buňka s číslem 3 a je vrácena hodnota ze 3. buňky výsledkové oblasti, což je „březen“.
Prohledávaná a výsledková oblast musí být u LOOKUP tvořena jen jedním řádkem nebo jedním sloupcem. Rozdíl mezi funkcemi VLOOKUP resp. HLOOKUP a LOOKUP je zřejmý z následující ukázky.
Srovnání funkcí LOOKUP a VLOOKUP
V případě VLOOKUP a HLOOKUP je prohledávaná a výsledková oblast součástí jedné, souvislé oblasti a prohledávaná oblast je přitom první sloupec, resp. první řádek této oblasti. V případě LOOKUP to můžou být rozdílně situované oblasti v řádcích či sloupcích, nebo i kombinace řádku a sloupce. LOOKUP selhává vždy při prohledávání nesetříděné oblasti, VLOOKUP a HLOOKUP jen pokud její poslední, čtvrtý parametr není nastaven nebo je nastaven na 1 – vizte řádky 13. a 14. v porovnání s řádkem 15.
Stáhněte si ukázky použité v článku: Funkce pro hledání a párování buněk - příklady
Funkce pro vyhledávání v rámci jedné buňky naleznete v článku Funkce pro práci s textem – spojování, pročištění, vyhledávání.