Pokud pracujete s daty v Google Tabulkách, funkce QUERY je neocenitelným pomocníkem. Umožňuje vám provádět sofistikované vyhledávání dat, podobně jako v databázi, takže můžete efektivně filtrovat a extrahovat informace z různých formátů. V následujícím textu se podíváme, jak ji využít.
Jak používat funkci QUERY
Používání funkce QUERY není nijak složité, zvláště pokud máte zkušenosti s jazykem SQL. Formát této funkce je podobný SQL dotazům, což vám umožňuje používat pokročilé vyhledávací možnosti přímo v Google Tabulkách.
Základní formát vzorce funkce QUERY je: =QUERY(data, dotaz, záhlaví). Parametr „data“ se nahrazuje rozsahem buněk, například „A2:D12“ nebo „A:D“. Parametr „dotaz“ reprezentuje váš vyhledávací dotaz.
Volitelný parametr „záhlaví“ určuje, kolik řádků se má brát jako záhlaví dat. Pokud máte například záhlaví rozložené do dvou buněk, třeba „Jméno“ v A1 a „Příjmení“ v A2, funkce QUERY správně použije oba řádky jako kombinované záhlaví.
V následujícím příkladu budeme pracovat s listem „Seznam zaměstnanců“ v Google Tabulkách, kde je uložen seznam zaměstnanců včetně jejich jmen, identifikačních čísel, dat narození a informace o tom, zda absolvovali povinné školení.
Na dalším listu můžeme pomocí funkce QUERY vytvořit seznam všech zaměstnanců, kteří se školení nezúčastnili. Tento seznam bude obsahovat jejich identifikační čísla, jména, příjmení a informaci o absolvování školení.
Pro získání těchto dat můžeme zadat vzorec: =QUERY(‚Seznam zaměstnanců‘!A2:E12, „SELECT A, B, C, E WHERE E = ‚Ne'“). Tento vzorec prohledává data v rozsahu A2 až E12 listu „Seznam zaměstnanců“.
Podobně jako SQL dotazy, funkce QUERY používá klauzuli SELECT pro určení sloupců k zobrazení a klauzuli WHERE pro definování vyhledávacích parametrů. Výsledkem bude seznam s údaji ze sloupců A, B, C a E pro všechny řádky, kde sloupec E („Absolvované školení“) obsahuje text „Ne“.
Jak je vidět, funkce QUERY vybrala ze seznamu čtyři zaměstnance, kteří se nezúčastnili školení, a zobrazila je v novém seznamu spolu s jejich jmény a identifikačními čísly.
V příkladu jsme použili konkrétní rozsah dat, ale tento rozsah lze upravit pro dotazování na všechna data ve sloupcích A až E. Takto můžete průběžně přidávat nové zaměstnance do seznamu. Vzorec QUERY se automaticky aktualizuje, pokud přidáte nové zaměstnance nebo se někdo zúčastní školení.
Správný vzorec pro tento účel je: =QUERY(‚Seznam zaměstnanců‘!A2:E, „SELECT A, B, C, E WHERE E = ‚Ne'“). Tento vzorec ignoruje název „Zaměstnanci“ v buňce A1.
Pokud do původního seznamu přidáte jedenáctého zaměstnance, například Christine Smith, která se školení nezúčastnila, vzorec QUERY se automaticky aktualizuje a nového zaměstnance zobrazí.
Pokročilé použití funkce QUERY
Funkce QUERY je velmi flexibilní. Umožňuje vám používat logické operace (jako AND a OR) nebo funkce Google (jako COUNT) jako součást vyhledávání. Pro hledání hodnot v určitém rozsahu můžete využít operátory porovnání (větší než, menší než atd.).
Použití operátorů porovnání
Pro zpřesnění filtrování dat můžete použít funkci QUERY s operátory porovnání (např. menší než, větší než nebo rovno). V našem příkladu přidáme do listu „Seznam zaměstnanců“ sloupec F, kde budeme evidovat počet ocenění, které každý zaměstnanec získal.
Pomocí funkce QUERY můžeme vyhledat všechny zaměstnance, kteří získali alespoň jedno ocenění. Vzorec bude vypadat takto: =QUERY(‚Seznam zaměstnanců‘!A2:F12, „SELECT A, B, C, D, E, F WHERE F > 0“).
Tento vzorec používá operátor větší než (>) pro vyhledání hodnot větších než nula ve sloupci F.
Výsledkem je seznam osmi zaměstnanců, kteří získali jedno nebo více ocenění. Ze všech 11 zaměstnanců tři žádné ocenění nezískali.
Použití AND a OR
Logické operátory AND a OR lze v rámci vzorce QUERY vnořovat, a tím přidat do vyhledávání více kritérií.
Pro demonstraci použití operátoru AND můžeme vyhledat všechny zaměstnance narozené mezi roky 1980 a 1989.
V tomto případě využijeme také operátory porovnání, jako je větší nebo rovno (>=) a menší nebo rovno (<=).
Vzorec by vypadal takto: =QUERY(‚Seznam zaměstnanců‘!A2:E12, „SELECT A, B, C, D, E WHERE D >= DATE ‚1980-1-1‘ and D <= DATE ‚1989-12-31′“).
Z našeho seznamu splňují kritéria tři zaměstnanci narození v letech 1980, 1986 a 1983.
Podobně můžeme použít operátor OR. Pokud použijeme stejná data, ale změníme podmínky na OR, můžeme vybrat všechny zaměstnance, kteří se nenarodili v 80. letech.
Vzorec by vypadal následovně: =QUERY(‚Seznam zaměstnanců‘!A2:E12, „SELECT A, B, C, D, E WHERE D < DATE ‚1980-1-1‘ or D > DATE ‚1989-12-31′“).
Z původních 10 zaměstnanců se tři narodili v 80. letech. Výše uvedený příklad zobrazuje zbývajících sedm, kteří se narodili před nebo po uvedeném období.
Použití COUNT s QUERY
Funkci QUERY lze kombinovat s dalšími funkcemi, jako je COUNT, pro manipulaci s daty. Řekněme, že chceme zjistit, kolik zaměstnanců z našeho seznamu absolvovalo povinné školení a kolik nikoliv.
Vzorec pro to bude vypadat takto: =QUERY(‚Seznam zaměstnanců‘!A2:E12, „SELECT E, COUNT(E) group by E“).
Soustředíme se na sloupec E („Absolvované školení“). Funkce QUERY pomocí funkce COUNT spočítá, kolikrát se v tomto sloupci objevuje každá z hodnot („Ano“ a „Ne“). Z výsledku vidíme, že šest zaměstnanců školení dokončilo a čtyři nikoliv.
Tento vzorec můžete jednoduše upravit a použít ho s jinými funkcemi Google, jako je SUM.