Pokud potřebujete manipulovat s daty v Tabulkách Google, může vám pomoci funkce QUERY! Přináší do tabulky výkonné vyhledávání ve stylu databáze, takže můžete vyhledávat a filtrovat data v libovolném formátu. Provedeme vás, jak jej používat.
Table of Contents
Pomocí funkce QUERY
Zvládnutí funkce QUERY není příliš obtížné, pokud jste někdy komunikovali s databází pomocí SQL. Formát typické funkce QUERY je podobný SQL a přináší do Tabulek Google sílu vyhledávání v databázi.
Formát vzorce, který používá funkci QUERY, je =QUERY(data, dotaz, záhlaví). „data“ nahradíte rozsahem buněk (například „A2:D12“ nebo „A:D“) a „dotaz“ svým vyhledávacím dotazem.
Volitelný argument „headers“ nastavuje počet řádků záhlaví, které mají být zahrnuty v horní části rozsahu dat. Pokud máte záhlaví, které se rozprostírá přes dvě buňky, například „First“ v A1 a „Name“ v A2, určí to, že QUERY použije obsah prvních dvou řádků jako kombinované záhlaví.
V níže uvedeném příkladu obsahuje list (nazývaný „Seznam zaměstnanců“) tabulky Tabulek Google seznam zaměstnanců. Zahrnuje jejich jména, identifikační čísla zaměstnanců, data narození a to, zda se zúčastnili povinného školení zaměstnanců.
Na druhém listu můžete pomocí vzorce QUERY vytáhnout seznam všech zaměstnanců, kteří se nezúčastnili povinného školení. Tento seznam bude obsahovat identifikační čísla zaměstnanců, jména, příjmení a informace o tom, zda se školení zúčastnili.
Chcete-li to provést s údaji uvedenými výše, můžete zadat =QUERY(‚Seznam zaměstnanců‘!A2:E12, “SELECT A, B, C, E WHERE E = ‚Ne’”). Tím se zjišťují údaje z rozsahu A2 až E12 na listu „Seznam zaměstnanců“.
Stejně jako typický SQL dotaz, funkce QUERY vybírá sloupce k zobrazení (SELECT) a identifikuje parametry pro hledání (WHERE). Vrací sloupce A, B, C a E, které poskytují seznam všech odpovídajících řádků, ve kterých je hodnota ve sloupci E („Absolvované školení“) textový řetězec obsahující „Ne“.
Jak je uvedeno výše, čtyři zaměstnanci z původního seznamu se nezúčastnili školení. Funkce QUERY poskytla tyto informace, stejně jako odpovídající sloupce, aby se zobrazila jejich jména a identifikační čísla zaměstnanců v samostatném seznamu.
Tento příklad používá velmi specifický rozsah dat. Můžete to změnit, abyste se dotazovali na všechna data ve sloupcích A až E. To by vám umožnilo pokračovat v přidávání nových zaměstnanců do seznamu. Vzorec QUERY, který jste použili, se také automaticky aktualizuje, kdykoli přidáte nové zaměstnance nebo když se někdo zúčastní školení.
Správný vzorec pro to je =QUERY(‚Seznam zaměstnanců‘!A2:E, “Vyberte A, B, C, E, KDE E = ‚Ne’”). Tento vzorec ignoruje počáteční název „Zaměstnanci“ v buňce A1.
Pokud do úvodního seznamu přidáte 11. zaměstnance, který se nezúčastnil školení, jak je uvedeno níže (Christine Smith), vzorec QUERY se také aktualizuje a zobrazí nového zaměstnance.
Pokročilé vzorce QUERY
Funkce QUERY je všestranná. Umožňuje vám používat další logické operace (jako AND a OR) nebo funkce Google (jako COUNT) jako součást vašeho vyhledávání. K nalezení hodnot mezi dvěma číslicemi můžete také použít porovnávací operátory (větší než, menší než atd.).
Použití porovnávací operátory s QUERY
K zúžení a filtrování dat můžete použít QUERY s operátory porovnání (např. menší než, větší než nebo rovno). Za tímto účelem přidáme na náš list „Seznam zaměstnanců“ další sloupec (F) s počtem ocenění, které každý zaměstnanec vyhrál.
Pomocí QUERY můžeme vyhledat všechny zaměstnance, kteří získali alespoň jedno ocenění. Formát tohoto vzorce je =QUERY(‚Seznam zaměstnanců‘!A2:F12, “SELECT A, B, C, D, E, F WHERE F > 0”).
To používá operátor porovnání větší než (>) k hledání hodnot nad nulou ve sloupci F.
Výše uvedený příklad ukazuje, že funkce QUERY vrátila seznam osmi zaměstnanců, kteří získali jedno nebo více ocenění. Z celkového počtu 11 zaměstnanců tři nikdy nezískali ocenění.
Pomocí AND a OR s QUERY
Funkce vnořených logických operátorů, jako je AND a OR, fungují dobře v rámci většího vzorce QUERY a přidávají do vzorce více vyhledávacích kritérií.
Dobrým způsobem testování AND je vyhledávání dat mezi dvěma daty. Pokud použijeme náš příklad seznamu zaměstnanců, mohli bychom uvést všechny zaměstnance narozené v letech 1980 až 1989.
To také využívá porovnávací operátory, jako je větší nebo rovno (>=) a menší nebo rovno (
Formát tohoto vzorce je =QUERY(‚Seznam zaměstnanců‘!A2:E12, “SELECT A, B, C, D, E WHERE D >= DATE ‚1980-1-1‘ and D
Jak je uvedeno výše, tři zaměstnanci narození v letech 1980, 1986 a 1983 splňují tyto požadavky.
K podobným výsledkům můžete také použít OR. Pokud použijeme stejná data, ale přepneme data a použijeme NEBO, můžeme vyloučit všechny zaměstnance, kteří se narodili v 80. letech.
Formát tohoto vzorce by byl =QUERY(‚Seznam zaměstnanců‘!A2:E12, “SELECT A, B, C, D, E WHERE D >= DATE ‚1989-12-31‘ or D
Z původních 10 zaměstnanců se tři narodili v 80. letech. Výše uvedený příklad ukazuje zbývajících sedm, kteří se všichni narodili před nebo po datech, která jsme vyloučili.
Používá se COUNT s QUERY
Spíše než jen vyhledávat a vracet data, můžete také kombinovat QUERY s dalšími funkcemi, jako je COUNT, pro manipulaci s daty. Řekněme, že chceme vyčistit řadu všech zaměstnanců na našem seznamu, kteří absolvovali a nezúčastnili povinného školení.
Chcete-li to provést, můžete zkombinovat QUERY s COUNT takto =QUERY(‚Seznam zaměstnanců‘!A2:E12, “SELECT E, COUNT(E) group by E”).
Se zaměřením na sloupec E („Attended Training“), funkce QUERY použila COUNT k počítání, kolikrát byl nalezen každý typ hodnoty (textový řetězec „Ano“ nebo „Ne“). Z našeho seznamu šest zaměstnanců školení dokončilo a čtyři ne.
Tento vzorec můžete snadno změnit a použít s jinými typy funkcí Google, jako je SUM.