Funkce pro hledání a párování buněk v Calcu

calc.png Při práci s většími objemy dat je často potřeba v datech vyhledávat, přeskupovat je, párovat. I pro tyto účely disponuje Calc řadou funkcí. Například potřebujete při zpracování výsledků průzkumu stanovit frekvence, resp. podíly odpovědí u jednotlivých otázek, jako je třeba pohlaví respondenta.  

Předchozí díly volného seriálu:

Stanovení frekvencí odpovědí – spočtení výskytů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.

Párování dat pomocí VLOOKUPPárování dat pomocí VLOOKUP

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í VLOOKUPSeskupová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 HLOOKUPSrovná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 LOOKUPSyntaxe 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 VLOOKUPSrovná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í.

 

(Jako ve škole) Průměr: 1,27 | Hodnotilo: 15
 

Komentáře

user avatar Jitka
Odpovědět
Funkce pro hledání a párování buněk v Calcu
27. 09. 2011, 14:56:23
Parádní článek, moc pomohl. Díky
user avatar Jiří D.
Odpovědět
Funkce pro hledání a párování buněk v Calcu
21. 03. 2014, 18:14:39
Dobrý den, jaká funkce mi pomůže a jak, vytvořit interaktivní formulář na vyplňování ke konkrétnímu jménu třeba telefon a mail ? Příklad když do jedné buňky vyberu s roletky jméno osoby tak do dalších buněk v listu se mi vyplní další údaje o osobě třeba ten telefon. Chápu to že musím mít někde v listu uležené ty telefony přiřazené k osobě ale jak to propojím aby OpenOffice věděl kam co doplnit a do jaké buňky při zadání konkrétní osoby z roletky. Děkuji
user avatar lp.
Odpovědět
Funkce pro hledání a párování buněk v Calcu
21. 03. 2014, 23:13:54
To je fór? Pokud ne, tak si přečti článek nad dotazem.
user avatar Jirka
Odpovědět
Funkce pro hledání a párování buněk v Calcu
22. 10. 2014, 19:29:53
Díky moc za článek, vzorec IF - ISNA - VLOOKUP mě vytáhl z bryndy :)
user avatar lp.
Odpovědět
Re:Funkce pro hledání a párování buněk v Calcu
23. 10. 2014, 16:44:07
Pokud používáš některou z posledních verzí calcu, tak se podívej na funkci IFNA (v době psaní článku nebyla implemntována)

Přidat názor

 

Nejsou podporovány žádné značky, komentáře jsou jen čistě textové. Více o diskuzích najdete v nápovědě. Diskuzi můžete sledovat pomocí RSS kanálu.

 
Tomáš Hanusek

Tomáš Hanusek

 
 
 
woo jaw demo hz