V této lekci si řekneme něco o funkci QUERY. Jak uvidíte, dají se s ní dělat úžasné věci i když ani zdaleka neprobereme všechny její možnosti.
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 tam už můžete řádit podle libosti.
Nápověda k samotné funkci QUERY je česky https://support.google.com/docs/answer/3093343
ale bližší popis samotného dotazovacího jazyka (Google Visualization API Query Language) už je v angličtině https://developers.google.com/chart/interactive/docs/querylanguage
Funkce QUERY slouží k získání nějaké podmnožiny z většího balíku dat. Pokud budete mít tabulku zaměstnanců a budete potřebovat z ní vybrat všechny ženy ve věku 30 - 35 let, pak s funkcí QUERY je to práce na na minutku.
Hned si to vyzkoušíme, na prvním listu Lidi je připravený seznam 5000 náhodně vygenerovaných anglických jmen a příjmení, ve sloupci C je M pro muže a F pro ženy, věk je náhodné číslo v rozmezí 24 - 58 let.
Poznámka k datům. V jednom sloupci musí být data jednoho typu, text, číslo nebo datum a čas. V případě, že bude ve sloupci několik typů dat, funkce QUERY pro dotaz použije ten typ, který v datech převažuje, buňky s jiným typem dat se pro dotaz budou jevit jako by byla prádná.
Začneme něčím jednoduchým, funkcí, která vybere všechny lidi, kterým je právě 30 let.
Zapíšeme ji takto
=QUERY(A2:D5001;"select * where D=30")
Vidíte, že prvním parametrem je oblast, ve které jsou data a druhý parametr je vlastní dotaz.
Protože jsme zapsali oblast takto, musíme vzorec použít na listu Lidi. Pokud bychom ho chtěli použít na jiném listu, museli bychom oblast definovat včetně jména listu, tedy Lidi!A2:D5001.
V mojí tabulce je tento vzorec v buňce G2.
A teď k samotnému dotazu. Za slovem select můžeme uvést jména sloupců, které má výsledek obsahovat.
Pokud bychom chtěli jen jméno a příjmení (sloupce A a B) vypadal by vzorec takto.
=QUERY(A2:D5001;"select A, B where D=30")
Hvězdička pak jednoduše znamená "vezmi všechny sloupce".
Část dotazu za slovem where obsahuje podmínku, kterou musí vybrané záznamy splňovat.
Takže where D=30 nám vybere všechny, kterým je právě 30 let.
Podobně where D < 30 všechny mladší než 30 a where D <= 30 všechny třicetileté a mladší.
Pokud potřebujeme přidat další podmínku, spojíme ji s první podmínkou spojkou and pokud musí být obě splněny najednou nebo spojou or, pokud má platit jen jedna z nich.
And i or můžete psát malými i velkými písmeny, takže si vyberte co vám vyhovuje abyste se ve vzorcích lépe orientovali. Takže podmínka
where D=24 or D=58 vybere všechny, jejich věk je právě 24, nebo 58.
Celý vzorec pak bude vypadat takto
=QUERY(A2:D5001;"select * where D=24 or D=58")
Pokud zadáme nesmyslnou podmínku, třeba
where D=24 AND D=58
čili vyber všechny, kterým je 24 a současně 58 let, objeví se v buňce chybová hláška #N/A s poznámkou, že vyhledávací dotaz vrátil prázdný výstup.
Teď si zkuste na listu Lidi zadat do buňky G6 libovolný text a do buňky G2 zkopírujte vzorec
=QUERY(A2:D5001;"select * where D=24")
Místo aby se data načetla, objeví se v buňce hláška #REF! a poznámka Výsledek pole nebyl rozšířen, protože by přepsal data v buňce G6.
Se stejným chováním jsme se už setkali u funkce IMPORTRANGE v jedné z minulých lekci.
Prostě výsledek funkce si nedovolí přepsat vaše data v tabulce.
Poté co text v buňce G6 smažete, funkce QUERY se provede a seznam lidí se objeví.
A stejně tak pokud teď zkusíte kamkoliv do dat načtených funkcí QUERY zadat libovolný text, dostane vložený text přednost, vloží se do buňky, všechna data načtená funkcí QUERY zmizí a v buňce se opět objeví #REF! a poznámka Výsledek pole nebyl rozšířen, protože by přepsal data v buňce ...
Podmínek můžeme do sekce where dát samozřejmě kolik potřebujeme, takže třeba vybrat všechny ženy s příjmením Blunt, starší než 30 let bychom mohli vzorcem
=QUERY(A2:D5001;"select * where B = 'Blunt' and C = 'F' and D > 30")
Všimněte si, že pokud v dotazu porovnáváme texty, musí být uzavřené do jednoduchých uvozovek. Pokud v našich datech některé políčko není vyplněné, bude mít pro funkci QUERY hodnotu null. Pokud tedy potřebujeme zkontrolovat, zda jsou v našich datech všechny položky vyplněné, pak to můžeme udělat vzorcem
=QUERY(A2:D5001;"select * where A is null or B is null or C is null or D is null")
Pro porovnání s hodnotou null se nepoužívá rovnítko ale právě výraz is null případně is not null pokud chceme vybrat neprázdná políčka.
Pokud bude v buňce hodnota #N/A s poznámkou Vyhledávací dotaz vrátil prázdný výstup, pak to znamená, že vše je vyplněno správně.
Vyzkoušejte si, že po smazání libovolného údaje v datech se dotyčný člověk okamžitě objeví ve výsledku QUERY.
U číselných hodnot jsou podmínky pro porovnání celkem jasné rovnost, menší, větší, menší nebo rovno a větší nebo rovno (=, <, >, <=, >= ) a porovnání zda je hodnota null víc možností porovnání u čísel nemáme. U textů jsou možnosti funkce QUERY daleko širší. Ukážeme a vyzkoušíme si alespoň několik z nich.
Contains
Contains vyhledává zadaný text kdekoliv v textu konkrétní hodnoty. Takže vzorec
=QUERY(A2:D5001;"select * where A contains 'Cha'")
nám vybere všechny v jejichž jménu bude obsažený text Cha takže nám vypíše všechny Charlese, Charlie a Charlotte.
Pozor, záleží zde na velikosti písmen, pokud vzorec změníme na
=QUERY(A2:D5001;"select * where A contains 'cha'")
Charlesové, Charlie a Charlotte zmizí a načte se seznam kde budou jména Michael a Richard.
Co v případě, že chceme vybrat záznamy bez ohledu na velikost písmen? V tomto konkrétním případě by se dala použít tento tvar, ve kterém bychom vyjmenovali obě varianty.
=QUERY(A2:D5001;"select * where A contains 'Cha' or A contains 'cha'")
Ale daleko lepší bude si text převést na malá písmena funkcí lower() nebo na velká funkcí upper() a pak porovnávat. Funkci bychom pak zapsali takto
=QUERY(A2:D5001;"select * where lower(A) contains 'cha'")
stejný výsledek by měl i zápis s převodem na velká písmena
=QUERY(A2:D5001;"select * where upper(A) contains 'CHA'")
Starts with a ends with
Jak je z názvů zřejmé, vyhledává texty, které začínají nebo končí zadaným textem.
Takže náš seznam Charlot a Charlesů bychom mohli vyhledat takto
=QUERY(A2:D5001;"select * where lower(A) starts with 'cha'")
Opět platí, že velikost písmene rozhoduje, proto jsme použili funkci lower(). Načte nám tak i Charlese, pokud by ho někdo zapsal omylem jako charles.
Obdobně můžeme vyhledat podle koncového textu, třeba všechny, jejichž jméno končí na w bychom našli takto
=QUERY(A2:D5001;"select * where lower(A) ends with 'w'")
Dostaneme tak seznam v němž budou všichni jménem Andrew a Matthew.
Matches
Vyhledává pomocí regulárních výrazů a umožňuje tak vyhledat v textech prakticky cokoliv. Výborný nástroj pro lidi, kteří regulární výrazy ovládají. Já se mezi ně netroufnu počítat, takže uvedu jen jednoduchý příklad
=QUERY(A2:D5001;"select * where lower(A) matches '.*nn.*'")
Regulární výraz podle kterého proběhne vyhledávání je opět uzavřený v jednoduchých uvozovkách a v našem případě má tvar .*nn.*
Tečka je zástupný symbol pro libovolný znak a hvězdička znamená libovolný počet opakování přičemž počet opakování může být i 0.
Takže když si regulární výraz převedeme do češtiny, tak znamená asi tohle.
Na začátku textu může být cokoliv, pak musí být znaky nn a dál už zase může být cokoliv.
Přičemž to cokoliv mohou být libovolné znaky, ale taky to nemusí být žádný znak, protože zápis .* znamená jakýkoliv znak s počtem opakování začínajícím od 0.
V našem případě nám vzorec vybere jména jako Hannah, Shannon nebo Connor, ale pokud bychom v seznamu měli někoho jehož jméno by začínalo Nn, případně končilo nn, objevil by se v seznamu taky, můžete si sami vyzkoušet.
Like
Like je jednodušší verze předchozích regulárních výrazů, protože má jen 2 zástupné znaky, které lze použít. Znak % (procenta), který znamená libovolný počet jakýchkoliv znaků a to opět od 0, takže vlastně odpovídá kombinaci .* z matches. Druhým znakem je _ (podtržítko), které zastupuje právě jeden znak.
Pokud bychom přepsali minulý příklad, kde jsme hledali jména obsahující znaky nn pak by to vypadalo takto
=QUERY(A2:D5001;"select * where lower(A) like '%nn%'")
Když zkusíme hledat jména obsahující kdekoliv kombinaci ph zapsali bychom to takto
=QUERY(A2:D5001;"select * where lower(A) like '%ph%'")
No a pokud by nás zajímali jen ti, jejichž jméno začíná na Wi, pak bychom použili zápis
=QUERY(A2:D5001;"select * where lower(A) like 'wi%'")
Všimněte si, že hledaný text musíme zapsat malými písmeny, protože funkcí lower(A) převádíme jména na malá písmena.
Tak, tím máme probranou sekci where, tedy podmínky jaká data vybrat a pustíme se do další volitelné části příkazu select, kterým je group by.
Group by
Group by použijeme v případech, kdy nás nezajímají jednotlivé řádky dat, ale potřebujeme nějaký údaj, který charakterizuje celou skupinu řádků. Například chceme znát počty lidí pracujících v jednotlivých odděleních, ale nepotřebujeme mít v seznamu jejich jména.
Na našich datech si zkusíme zjistit četnost výskytu jednotlivých příjmení, čili zajímá nás kolik máme v našem seznamu lidí, kteří se jmenují třeba Smith nebo Hill.
Počet se v dotazu zapisuje pomocí funkce count(), třeba count(B).
QUERY funkci pro zjištění počtu záznamů ve sloupci B bychom pak zapsali jednoduše takto
=QUERY(A2:D5001;"select count(B)")
Započtou se jen vyplněné řádky, výsledek bude 5000, zkuste si nějaké příjmení v sloupci B smazat, výsledný počet se změní na 4999.
No a dotaz, který nám vypíše počty jednotlivých příjmení v celém seznamu bude vypadat takto.
=QUERY(A2:D5001;"select B, count(B) group by B")
Snadno tak zjistíte, že v seznamu je 87 lidí s příjmením Bacon atd. Výsledný seznam je řazený podle abecedy. Bohužel se tak nedozvíme kolik je to mužů a kolik žen, takže si dotaz upravíme aby zahrnoval i sloupec C s údaji o pohlaví.
=QUERY(A2:D5001;"select B, C, count(B) group by C, B")
Malá poznámka. Když v části select vybereme několik sloupců (count(B) se nepočítá), musíme v sekci group by vytvořit skupiny podle všech těchto sloupců. Takže třeba vzorec
=QUERY(A2:D5001;"select B, C, count(B) group by B")
by skončil chybou, protože vybíráme data ze slouců B a C, ale skupiny vytváříme jen podle sloupce B.
Výsledek bude seskupený tak, jak jsme napsali vzorec, tedy nejprve podle sloupce C (opět podle abecedy, takže ženy jako první) a až poté podle příjmení, Bacon atd.
Rázem tak zjistíte, že příjmení Bacon má 46 žen a 41 mužů, jen pro zjištění počtu mužů musíme odrolovat až na konec seznamu žen.
Pokud bychom chtěli mít stejná příjmení pohromadě, pak prostě v group by prohodíme pořadí sloupců, tedy
=QUERY(A2:D5001;"select B, C, count(B) group by B, C")
Výsledek by vypadal takto
Samozřejmě spolu s group by můžeme použít i podmínku where, takže pokud bychom chtěli zjistit četnost jednotlivých příjmení všech žen pod 30 let, můžeme to snadno zjistit tímto vzorcem
=QUERY(A2:D5001;"select A, C, count(A) where C= 'F' and D< 30 group by A, C")
nebo jednodušeji
=QUERY(A2:D5001;"select A, count(A) where C= 'F' and D< 30 group by A")
protože v tomto konkrétním případě sloupec C ve výsledku nepotřebujeme, víme, že vybíráme jen samé ženy.
Ještě poznámku k funkci count(), který vrací počet záznamů v uvedeném sloupci.
Další podobné agregační funkce, které můžeme použít jsou
avg() - vrací průměrnou hodnotu, celkem logicky funguje jen pro číselné hodnoty.
max() a min() vrací maximální resp. minimální hodnotu. Obě funkce jsou stejně jako count() použitelné nejen na číselné sloupce, ale i na sloupce obsahující datum, nebo texty. U dat platí, že čím starší datum tím menší hodnota. Texty jsou porovnávány podle abecedy a řazení bere v úvahu velikost písmen.
sum() - vrací součet hodnot ve sloupci. Stejně jako avg() je použitelné jen pro číselné hodnoty.
Kromě agregačních funkcí, které pracují vždy s celým blokem dat se v dotazu dají použít takzvané skalární funkce, které buď parametr nemají žádný, nebo je parametrem jedna konkrétní hodnota dat.
Dvě z nich lower() a upper() jsme už zmínili a použili. A už jen telegraficky několik funkcí u nichž musí být parametrem datum
year() - vrací rok jako číslo, třeba 2020, month() - vrací měsíc. Pozor, měsíce jsou číslované od nuly, tedy leden 0, únor 1 atd.
day() - den v měsíci, 1 - 31, hour(), minute(), second(), millisecond() vrací hodinu, minutu, sekundu a milisekundu z předaného parametru. Všechny parametry si můžete podrobně pročíst na stránce https://developers.google.com/chart/interactive/docs/querylanguage
Dotaz může mít ještě řadu dalších parametrů, ale to už by se nám článek neúnosně natahoval.
Spíš si řekneme, že funkce QUERY může mít i třetí parametr o kterém jsme zatím nemluvili.
Tento parametr určuje kolik řádků tvoří hlavičku dat.
V našem případě máme hlavičku o výšce jednoho řádku je to první řádek naší tabulky.
Zkuste si tento jednoduchý vzorec.
=QUERY(A2:D5001;"select B limit 10")
Použili jsme v něm jeden z těch parametrů, které jsme přeskočili, limit(). Už z názvu je jasné, že slouží k omezení délky dat, které nám QUERY vypíše, v tomto případě tak získáme jen 10 prvních příjmení místo 5000, které bychom získali bez tohoto parametru.
Vidíte, že první příjmení Gibbs je vypsané přímo do buňky kde je vzorec.
Pokud bychom chtěli aby nám QUERY automaticky vypsalo i hlavičku, tedy abychom věděli, že Gibbs je příjmení, pak bychom vzorec museli upravit takto
=QUERY(A1:D5001;"select B limit 10"; 1)
Všimněte si, že jsme jednak doplnili třetí parametr, říkáme tak, že výška hlavičky je jeden řádek.
Zároveň ale musíme změnit i datovou oblast, ze které data bereme, aby skutečně tu hlavičku obsahovala, proto jsme ji změnili na A1:D5001.
Pokud bychom oblast ponechali od řádku 2, čili A2:D5001 funkce by nám jako hlavičku vypsala jméno prvního člověka v seznamu, tedy pana Gibbse.
Jinak samozřejmě třetí parametr používat nemusíte a hlavičku si nad vzorec doplníte ručně. Využití je spíš pro případ, kdy vzorec máte na stejném místě, měníte ho a potřebujete vědět, který sloupec co znamená.
Ještě si řekneme, že jako datovou oblast můžeme použít pojmenovanou oblast. Platí to obecně kdekoliv v Tabulkách, už jsme se o tom zmiňovali v jedné z minulých lekcí. Přes menu Data - Pojmenované rozsahy si pojmenujete část tabulky a tento název pak můžete použít v QUERY.
V mojí tabulce má oblast A2:D5001 název jmena_anglicka takže vzorec pak bude vypadat například takto
=QUERY(jmena_anglicka;"select B")
Název oblasti se zapisuje bez uvozovek. Hlavní výhodou je, že se nemusíte starat o to kde data leží.
My jsme se zatím v našich příkladech pohybovali jen na jednom listu, ale pokud budete mít data na mnoha různých listech, pak budete muset vzorec zapisovat i s názvem listu, třeba
=QUERY(Přehled_za_rok_2019!A1:F2000;"select …..")
zatímco u pojmenované oblasti může vzorec vypadat třeba
=QUERY(rok_2019; "select …..")
Ještě si ukážeme jednu vychytávku a sice že text dotazu nemusíme psát přímo do funkce QUERY, ale může být uložen v některé buňce. Dotaz pak může vypadat třeba takto
=QUERY(jmena_anglicka; G1)
Do buňky G1 si pak zadáme jen samotný text dotazu, třeba select B limit 10.
Pokud potřebujete používat několik předem daných dotazů, můžete si texty dotazů zapsat do buněk tabulky a dotaz pak spustit tím, že dotaz vyberete ze seznamu.
Vše si můžete prohlédnout na listu Dotazy.
Do sloupce F jsem zapsal několik textů pro dotazy, použil jsem ty, co jsme probrali v této lekci.
Vedle nich jsem do sloupce E zadal texty podle kterých budeme dotaz vybírat.
Pak jsem vybral buňku C1 ve které budeme mít rozbalovací seznam pro výběr dotazu.
Přes Data - Ověřování dat jsem nadefinoval, že pro buňku C1 se budou data vybírat z rozsahu F1:F6. Ověřování dat jsme už použili v jedné z minulých lekcí, panel s nastavením vypadá takto.
Tím nám buňka C1 bude fungovat jako rozbalovací seznam, ve kterém uvidíme naše názvy dotazů ze sloupce E. Pro funkci QUERY ale nepotřebujeme název ze sloupce E, ale text dotazu ze sloupce F.
S tím nám pomůže funkce VLOOKUP, v českém překladu se jmenuje SVYHLEDAT https://support.google.com/docs/answer/3093318
Funkce funguje tak, že vezme hodnotu, kterou má jako první parametr a hledá ji v prvním sloupci bloku, který je uvedený v druhém parametru. Pokud požadovanou hodnotu najde vrátí hodnotu ze stejného řádku, ale jiného sloupce. Kterého to nám určí 3. parametr. Funkce je v buňce B3 a vypadá takto
=VLOOKUP(B1; E1:F6; 2; false)
Čili vezmi text, který je v buňce B1, zkus ho najít v prvním sloupci bloku E1:F6 a vrať hodnotu ze sloupce 2, tedy ze sloupce F (sloupec E je v tomto kontextu sloupec 1).
Čtvrtý parametr určuje jestli jsou texty ve sloupci E setříděné podle abecedy. Pokud ho vynecháme, nebo nastavíme na true a texty setříděné nebudou, nebude funkce VLOOKUP fungovat správně. Nejste-li si setříděním jisti, radši vždy nastavte tento parametr na false.
Jakmile teď v buňce B1 vybereme některý z dotazů, promítne se nám jeho text do buňky B3 a QUERY funkce v buňce A9 načte odpovídající data.
A ještě zmíním jednu možnost jak si práci s dotazy usnadnit. Pomocí jednoduchého vzorce se text dotazu může automaticky složit z hodnot, které zadáme do předem daných buněk tabulky. Malou ukázku můžete vidět na listu Dotazy 2.
Nejprve jsem do buňky C1 nastavil přes Data - Ověřování dat možnost vybrat pouze ze dvou hodnot M a F. Panel pro nastavení vypadá takto.
Pak jsem pro buňky C2:C3 opět přes ověřování dat nastavil, aby do nich bylo možné zadat pouze číslo. Panel by měl vypadat takto.
V době kdy to budete zkoušet už možná vše bude fungovat správně, nicméně já jsem narazil na následující chybu.
Na Gmail účtu se Tabulkám nelíbila hodnota 100 zadaná jako maximum, přestože tam hodnotu samy doplní ve chvíli, kdy volbu Kritéria přepnete na číslo. Navíc políčka pro minimální a maximální hodnotu byla zamčená, takže hodnoty 10 a 100 se nedaly změnit. Panel vypadal takto a pravidlo nebylo možné nastavit.
Na firemním účtu se chyba neobjevila, pravidlo nastavit šlo, nicméně obě pole byla opět zamčená, takže nešlo nastavit jiné rozmezí než 10 - 100.
Ale obejdeme se samozřejmě i bez nastavování těchto pravidel, v případě, že do buněk zadáme místo čísla text, dotaz prostě nebude fungovat a nevypíše nám žádná data.
Tak, teď už jen zbývá sestavit výsledný text dotazu. Napíšeme si ho třeba do buňky C5 a bude vypadat takto.
="select * where C = ' " & C1 & " ' and D >= " & C2 & " and D <= " & C3
Pokud máte se vzorci zkušenosti je vám asi vše jasné. Znak & spojuje dohromady jednotlivé texty, které jsou součástí vzorce, nebo berou obsah z jiných buněk.
první text ve vzorci je
select * where C = '
za něj se připojí hodnota z buňky C1 (hodnota M nebo F) a dál se doplní další text ze vzorce, tedy
' and D >=
opět pomocí & se přidá text z buňky C2 tedy minimální věk, dál se ze vzorce doplní další část
and D <=
a nakonec se doplní hodnota z buňky C3 tedy maximální věk.
Jen pozor, že ve pokud v dotazu porovnáváme textové hodnoty, tak musí být hodnota uzavřena mezi jednoduché uvozovky, jsou v první a druhé části textu.
Výsledný dotaz pak podle obsahu buněk C1 - C3 může vypadat třeba takto.
select * where C = 'F' and D >= 22 and D <= 39
Na obrázku vidíte jak samotný vzorec ve stavovém řádku, tak i jeho výsledek v buňce C5.
No a už zbývá jen napsat samotný vzorec pro QUERY, já jsem ho napsal do buňky A7 a vypadá takto
=query(jmena_anglicka;C5)
Poté už můžete měnit parametry v buňkách C1 - C3 a vždy se vám načtou odpovídající data.