V této lekci si ukážeme použití funkcí FILTER, SORT a UNIQUE. Tyto 3 funkce řadí Google do jedné skupiny funkcí pro filtrování dat.
Funkce FILTER
Funkce FILTER podobně jako QUERY slouží k získání nějaké podmnožiny dat podle zadaných kritérií. Funkce FILTER je ale podstatně jednodušší než QUERY, takže si nebudete muset pamatovat spousty parametrů a možností. Na druhou stranu toho zase umí mnohem míň než QUERY. Můžete si otevřít moji tabulku, je nasdílená pro čtení, takže si vzorce můžete jen prohlížet. Udělejte si kopii tabulky na svůj Disk ať si můžete vše pořádně vyzkoušet. V tabulce je na listu Lidi připravený blok dat, použil jsem data z lekce o QUERY, jen jsem je trochu zkrátil, data jsou v bloku A2:D100. Funkce FILTER může mít 2 nebo 3 parametry, její česká nápověda je na této stránce. První parametr definuje blok dat, který budeme filtrovat. Blok můžeme zadat klasicky, tedy třeba A2:D100, nebo použít pojmenovaný rozsah, který nám usnadní zápis zejména v případě, že data budeme mít na jiném listu než funkce FILTER. Já jsem si nadefinoval rozsah data, který zahrnuje rozsah A2:D100 a potom rozsahy pro každý sloupec zvlášť a to jmena, prijmeni, pohlavi a vek.
Druhý parametr obsahuje podmínku, kterou musí záznamy splňovat aby je funkce zahrnula do výstupu. Pokud opět použijeme pojmenované rozsahy, můžeme podmínku zapsat třeba vek=30 případně pohlavi="F". Můžeme použít i normální zápis D2:D100 = 30 a C2:C100="F".
V obou případech ale platí, že rozsah sloupců v podmínce musí zahrnovat stejné sloupce jako datová oblast v prvním parametru. Pokud by se lišil, byla by výsledkem funkce hláška #N/A s poznámkou, vysvětlující kde jste udělali chybu v rozměrech obou oblastí.
A zkusíme si první vzorec, vybereme seznam všech žen.
=filter(data; pohlavi = "F")
V případě, že bychom neměli pojmenované rozsahy, museli bychom vzorec zapsat takto
=filter(A2:D100; C2:C100="F")
nebo pokud bychom vzorec psali na jiném listu, tak dokonce
=filter(Lidi!A2:D100; Lidi!C2:C100="F")
Pokud ve výstupu nepotřebujeme všechny sloupce, je možné vzorec zapsat tak, že sloupec podle kterého filtrujeme nemusí být součástí bloku, který je v prvním parametru. Například takto.
=filter(jmena; pohlavi = "F")
Vidíte, že vybíráme jen hodnoty ze sloupce A a podmínku máme ve sloupci C.
Pokud bychom potřebovali vybrat nejen jména, ale jméno a příjmení všech žen, pak nám naše pojmenované oblasti nebudou stačit. Museli bychom si nadefinovat další, která by zahrnovala sloupce A a B, nebo prostě pro definici oblasti použijeme klasický zápis
=filter(A2:B100; pohlavi ="F")
Funkce může mít i třetí parametr s druhou podmínkou. Můžeme tedy vybrat například všechny muže starší než 30 let takto.
=filter(data; pohlavi = "M"; vek > 30)
Pokud bychom chtěli filtrovat podle 3 podmínek, pak už nám to funkce FILTER přímo neumožní, protože parametry pro filtr má pouze 2.
Ale můžeme si pomoci tak, že provedeme první filtrování a získaný výstup použijeme jako vstup pro další filtr. Ukázka je na listu Dvojitý filtr.
Nejprve si vzorcem v buňce A4 vyhledáme všechny muže.
=filter(data; pohlavi = "M")
Tím máme vypsané pouze muže a napíšeme si vzorec, který ze seznamu mužů vybere všechny v rozmezí 30 - 40 let včetně.
Tady už si nebudeme definovat pojmenované oblasti, ale vzorec zapíšeme klasicky, je v buňce F4.
=filter(A4:D104;D4:D104 >= 30;D4:D104 <= 40)
Musíme pamatovat na to aby počet řádků datové oblasti i obou filtrů byl dostatečně velký. Zde jsem ho pro jistotu nadefinoval na 100 řádků, stejně jako je počet řádků výchozí datové oblasti. Pokud oblast nadefinujeme delší než data skutečně budou, tak to samozřejmě ničemu nevadí.
Tímto způsobem můžeme filtrování zřetězit několikrát a při každém stupni filtrování máme k dispozici dvě podmínky podle kterých můžeme filtrovat.
Při tomto způsobu vidíme v tabulce výsledky jednotlivých filtrování. Někdy to může být výhoda, vizuálně si ověříme, že každý stupeň filtrování funguje a jsou v něm správné výsledky.
Pokud bychom chtěli oba filtry zapsat do jednoho vzorce, tedy výsledek prvního filtru použít jako vstup pro druhou funkci FILTER, pak narazíme na to, že počet řádků v podmínce musí odpovídat počtu vstupních řádků. Ten ale předem neznáme, počet bude pokaždé jiný, v závislosti na prvním filtru a vstupních datech.
Asi by šel napsat vzorec, který by tenhle problém řešil a tvar podmínky sestavil správně podle výsledku prvního filtru, ale myslím, že jednodušší je použít 2 samostatné filtry se zápisem do tabulky tak, jak jsme si ukázali, nebo třeba funkci QUERY, kterou dokážeme výsledná data získat v jednom kroku.
Parametr pro filtrování může mít i jinou podobu než podmínku. Vzorec může vypadat třeba takto.
=filter(A4:D10; {true; true; false; true; true; false; true})
Případně můžete použít i českou variantu a místo true použít PRAVDA a místo false NEPRAVDA.
Opět počet true/false ve složených závorkách musí odpovídat délce dat, jinak funkce vypíše chybu. Jsou-li parametry v pořádku, vypíše funkce ty záznamy, které mají v podmínce uvedeno true.
V téhle podobě asi vzorec psát nebudete, bylo by to značně nepraktické, zvlášť pro větší množství dat. Ale místo toho můžete odkázat na místo v tabulce, kde funkce tenhle seznam true/false najde.
Ukázku si můžete prohlédnout na listu Dvojitý filtr, kde máme ve dvou krocích vyfiltrovaný seznam lidí.
Ve sloupci J jsem vybral oblast J4:J104 a přes Vložit - Zaškrtávací políčko jsem do sloupce vložil zaškrtávací pole. Nezaškrtnuté pole odpovídá stavu false (NEPRAVDA) a zaškrtnuté samozřejmě vrací true (PRAVDA).
Do buňky L4 jsem pak vložil vzorec
=filter(F4:G100; J4:J100)
No a parametr J4:J100 nám vrátí náš seznam true/false, které záznamy zobrazit a které ne. Pokud není žádné políčko zaškrtnuté, bude v buňce zobrazeno #N/A s poznámkou.
Vyzkoušejte si, že pokud ve sloupci J některé políčko zaškrtnete, jméno a příjmení (ve vzorci jsou jen sloupce F a G) se vám hned promítne do sloupců L a M. Pokud vzorec zadáme tak, že počet řádků dat se bude lišit od počtu true/false, která nám vrátí druhý parametr, dostaneme stejnou hlášku jako jsme si již ukazovali, upozornění, že počet řádků dat a filtrovací podmínky se liší.
Funkce SORT
Jak už je z názvu zřejmé, funkce slouží k řazení dat. Českou nápovědu si můžete pročíst na této stránce. Příklady si můžete zkoušet v mojí tabulce na listu Sort.
Funkce má minimálně 3 parametry.
První parametr je oblast, kterou chceme třídit. Zadáme ji jako vždy, třeba A2:D100, pro použití na jiném listu Lidi!A2:D100 nebo můžeme použít pojmenované oblasti a uvést název oblasti, v tomto případě bychom použili naši oblast data.
Druhý parametr udává sloupec, podle kterého chceme data řadit. Pokud sloupec leží v bloku který třídíme, zadáme parametr jako pořadové číslo sloupce, přičemž první sloupec má číslo 1.
Pokud tedy budeme třídit oblast A2:D100 a druhý parametr bude 3, znamená to, že chceme oblast seřadit podle sloupce C (muž/žena).
Může se stát, že chceme třídit podle sloupce, který leží mimo tříděnou oblast. Například chceme mít jako výstup pouze jména (první parametr A2:A100, nebo jmena), ale setříděná podle kritéria muž/žena. V tom případě musíme druhý parametr zadat jako oblast obsahující sloupec, podle kterého se má třídit, tedy v tomto případě D2:D100, nebo využijeme naši pojmenovanou oblast vek. Stejně jako jsme to viděli u funkce FILTER počet řádků v prvním a druhém parametru se musí rovnat.
S využitím pojmenovaných oblastí by výsledný vzorec byl.
=sort(jmena;pohlavi;true)
Nebo bez pojmenovaných oblastí
=sort(Lidi!A2:A100; Lidi!C2:C100; true)
Takto bychom vzorec mohli použít na libovolném listu, pro použití na listu Lidi bychom mohli název listu ze vzorce vynechat.
Třetí parametr udává jestli chceme výstup setříděný vzestupně, pak použijeme parametr true, případně PRAVDA. A logicky pro sestupné třídění použijeme false nebo NEPRAVDA.
Pokud potřebujeme oblast setřídit podle více sloupců najednou, pak prostě přidáváme další dvojice parametrů. Pro setřídění oblasti data podle sloupce muž/žena a poté podle věku, bychom vzorec zapsali takto.
=sort(data; 3; true; 4; true)
Výstup by pak vypadal takto.
Unique
Funkce UNIQUE je poměrně jednoduchá, má jediný parametr, oblast dat na kterou ji chceme aplikovat a funguje tak, že vrátí data dané oblasti, ale vypustí z nich duplicitní řádky.
Pokud si na listu Unique zkusíte do buňky A1 zadat vzorec
=UNIQUE(data)
tak zjistíte, že výsledná data mají 98 řádků tedy jen o jeden méně než vstupní oblast na listu Lidi.
Je to proto, že ve vstupních datech se vyskytují jen 2 shodné řádky a jeden z nich funkce vypustila. Jsou to poslední 2 řádky, které vypadají takto.
Zkuste si vzorce
=UNIQUE(jmena)
nebo
=UNIQUE(prijmeni)
abyste získali seznam jedinečných jmen a příjmení.
Bude to samozřejmě seznam jmen nebo příjmení mužů i žen. Pokud bychom chtěli získat například jedinečný seznam jmen všech mužů, museli bychom funkci UNIQUE aplikovat na seznam, do kterého jsme si předem vyfiltrovali jen muže.
Funkci ovšem nemusíme oblast zadat přímo, ale můžeme jí jako vstup předat blok dat, který získáme jako výsledek jiné funkce.
U funkce FILTER jsme měli příklad, který získal údaje všech mužů v seznamu. Vypadal takto.
=filter(data; pohlavi = "M")
Mírně si ho upravíme abychom získali jen jména
=filter(jmena; pohlavi = "M")
a získáme tento výsledek.
A teď si obě funkce spojíme dohromady, tedy výsledek filtru dáme jako vstup pro UNIQUE.
=unique(filter(jmena; pohlavi = "M"))
Tím získáme seznam všech mužských jmen bez duplicit, jehož začátek bude vypadat takto.
No a přidáme ještě poslední funkci z naší trojice a výsledný seznam si seřadíme podle abecedy. Výsledná funkce bude vypadat takto. =sort(unique(filter(jmena; pohlavi = "M")); 1; true) a její výsledek bude vypadat takto.
Při psaní podobných složených vzorců můžete snadno udělat chybu v jednotlivých závorkách, nebo se stane, že použijete omylem jako oddělovač parametrů čárku místo sloupce. Tady hodně záleží na typu chyby, kterou jste ve vzorci udělali.
Někdy se v chybové hlášce dozvíte, že některá z funkcí má špatný počet parametrů, ale také se můžete dočkat jen hlášky Chyba při analýze vzorce, která vám příliš nepomůže.
Pokud se vám stále nedaří na chybu přijít, rozložte si vzorec do jednotlivých částí a až po jejich vyzkoušení z nich zkuste sestavit ten výsledný.
Pokud Vás zajímá cokoliv dalšího, obraťte se na nás na ahoj@appsatori.eu :)
留言