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,24 | Hodnotilo: 17
 

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)
user avatar michal
Odpovědět
Funkce pro hledání a párování buněk v Calcu
25. 09. 2019, 08:15:53
Prosím o radu. Funkce VLOOKUP mi fungovala. Ale ted v jiném souboru mi jde jen první řádek. Tam mi hodnotu najde, když ale natáhnu vzoreček do dalších řádku, tak mi to nefunguje. Proč??
user avatar kamlan
Odpovědět
Re:Funkce pro hledání a párování buněk v Calcu
25. 09. 2019, 14:08:21
To je dotaz spíše do fóra. Nejlépe kdybyste tam připojil i ukázkový soubor (lze např. nahráním na uloz.to a uvedením odkazu) a popsal jak natahujete ten vzoreček do dalšího řádku (přetažením za růžek nejspíše).
Ale možná vám může chybět znak $ v adrese buňky (třeba jen A3 namísto $A$3 a natahování do jiných buněk to pak mění adresu - např. kdybyste do B3 dal =A3, tak přetažením do B4 by vzniklo =A4 namísto požadovaného "stejného A3" neboli $A$3).
user avatar Petra
Odpovědět
Funkce
1. 10. 2019, 14:58:17
Prosím existuje funkce, kdy mám dvě tabulky. Obě mají dva sloupce. V jednom sloupci u obou tabulek jsou shodné některé symboly. V druhém sloupci jsou různá čísla. Na základě těchto symbolů potřebuji aby se mi přetáhly čísla z jedné tabulky do té druhé a nahradila ty čísla. Snad je to dost srozumitelné. Nevíte jestli to umí nějaká funkce?
user avatar neutr
Odpovědět
Re:Funkce
1. 10. 2019, 17:07:45
Tohle není snadné vysvětlit ale lze to udělat a pokud tomu správně rozumím tak vícero způsoby.

Ale není to na diskusi zde. Tahle rubrika je k úplně jinému tématu - konkrétně k článku u kterého je vedena.

Tohle je typické téma pro fórum. Přihlašte se jako nový uživatel (pokud už nejste registrovaná) a založte nové téma tématu Calc kde je prostor na takovéhle téma.

Tady se dá čekat že dojde třeba na ten VLOOKUP ale k tomu je pár detailů - nejsou tam možné duplicity? nejspíš se jedná o změny ceníku nějakého eshopu. K tomu řeknu jen tolik že pokud nejsou vyhledávací parametry shodné (znaky ve vyhledávaných výrazech - kolik shodných znaků se musí najít? - jsou to alespoň unikátní celá slova?). Sem už ale neodpovídejte.

Většinou takové téma potřebuje upřesnění. Představte si 2-10 odpovědí za sebou pod každým z příspěvků zde - za 10 let.

Odpovědět

 

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.

 
EDU Trainings
Tomáš Hanusek

Tomáš Hanusek

 

Public Relations

IPv4 zastarává, i tak si však zaslouží kvalitní zabezpečení

Znáte souvislost mezi doménami a typickými IP adresami? Není od věci si uvědomit, že zatímco domény jsou určeny pro zjednodušení hledání webové stránky na internetu, IP adresy slouží primárně pro výpočetní techniku, konkrétně pro přesné vyhledání konkrétního serveru nebo počítače.

Pokračování ...


IT Systems - předplatné
SAM v kostce 2019
 
 
woo jaw demo hz