Přeskočit navigaci | Přeskočit na novinky
     

Jste zde: Úvodní stránka » excel » funkce-google » QUERY-funkce-Google-tabulky
Microsoft Excel logo

QUERY – funkce - Google tabulky

Videokurzy Excel

Ve spolupráci se SEDUO jsem vytvořil několik videokurzů:

Tvořte SQL dotazy v tabulkách od Google. Škoda jen, že tuto funkci nemá Excel (snad to čte Microsoft a v 2019 bude!) No a pro Google (třeba také někdo čte - mohl by doplnit o JOIN). Neplést si QUERY Editorem v Power BI (potažmo v Excel).

Úvodem do funkce QUERY

Pokud ještě neznáte funkci QUERY, která je v Google tabulkách doporučuji se na ni zaměřit. Stejně jako na pár dalších funkcí (IMPORTRANGE? Atd.). Funkce QUERY umí provádět SQL dotazy nad tabulkou (nebo tabulkami). Což je neskutečná pomoc (pokud dobře využijete).

Protože tato funkce toho umí opravdu hodně, napsal jsem tento rozsahlý članek, který je rozdělen na jednotlivé kapitoly ať se dá najít co potřebujete:

Syntaxe QUERY

Nejprve začneme syntaxi funkce.

QUERY(data, query, [headers])

Co jednotlivé argumenty znamenají:

  • data – rozsah z kterého se bude provádět. Může být buď jedna tabulka, pojmenovaný rozsah, nebo i odkaz na více tabulek.
  • query – zde se vkládá SQL dotaz, tj. umí zobrazovat sloupce, vybírat podle podmínek (WHERE), třídit ASC, DESC, využívat operator LIKE, grupovat GRUP BY, atd.
  • headers - [ volitelně ] – sděluje kolik řádku má záhlaví, pokud jeden, nemusí se uvídět, nebo stačí uvést -1.

Sice zde bych mohl skončit (máte syntaxi), ale proč si použití neukázat na praktických příkladech. Navíc jsem už mnoho z ukázek prakticky použil a mám je připravené, tak proč nemít vše na jednom místě.

Pro práci budu používat demo tabulku, kterou si budu průběžně upravovat ať mohu vše ukázat.

QUERY data pro demo

Základy QUERY

Začnem základem, prostě si vypíšeme celou tabulku (Poznámka: Tabulka včetně záhlaví je v oblasti B6 až G20). V SQL se používá SELECT, stejně jako v SQL. Což také využijeme. Neboli využijete jen SELECT s hvězdičkou (*), který zajístí vypsaní všch hodnot.

=QUERY(B6:G20 ; "SELECT *")

nebo

=QUERY(B6:G20 ; "select *")

SELECT může být psán i malými písmeny select, Google zvládne vše.

V jednoduchosti je síla a dojde jen k vypsání celé tabulky. Čimž si můžete otestovat, že vše funguje jak má.

Poznámka: Můžete se setkat i se zápisem, kdy oblast tabulky je ve složených závorkách (jde o to, že můžete se místo písmen sloupce odkazovat na číslo sloupce, případně přidávat více oblastí/tabulek - o tom podrobněji dále v článku):

=QUERY({B6:G20} ; "SELECT *")

Poslední možnosti jak se odkázet na tabulku je použít její pojmenovaný rozsah:

=QUERY(PojmenovanýRozsah ; "SELECT *") QUERY SELECT základ

Tak a máte za sebou úplný základ funkce QUERY.

Pokročilejší QUERY

Kromě výpisu celé tabulky, lze výpis omezit, například jen určité sloupce. Sloupce se dají vybírat buď pomocí písmena sloupce, nebo číslo sloupce. Záleží jak je na tabulku odkazováno.

Pokud je vybraná tabulka, klasicky odkaz, může být i jiný list.

=QUERY(B6:G20 ; "SELECT B,C")

Pokud vybraná tabulka obsahuje složené závorky, odkazuje se na číslo sloupce Col2 (Col a číslo přislušného sloupce v oblasti, kdy pokud oblast začína od B tak první sloupce je B).

=QUERY({B6:G20} ; "select Col1, Col2") QUERY SELECT pokročilenjší

Podmínky v QUERY

Sloupce vybrat umíte z předchozí kapitoly, co ale zobrazit příslušné záznamy (řádky)? Lze za využití podmínky WHERE. Například z druhého sloupce chci jen vypsat VIP regiony.

=QUERY(B6:G20 ; "select B,C WHERE C = 'VIP'")

Co ale když se budete chtít odkazovat do buňky, ve které bude volba které regiony chcete zobrazit? Jednoduché jen se využije & a odakz an buňku, neboli propojování argumentů. Pozor u čísel funguje malinko jinak, což je popsáno v další kapitole.

=QUERY(B6:G20 ; "select B,C WHERE C = '" & G40 & "'")

Opět se lze odkazovat i na čísla sloupců, pokud bude tabulka v složených závorkách:

=QUERY({B6:G20} ; "select Col1,Col2 WHERE Col2 = '" & G40 & "'")

Co ale když cjhci pracovat s buňkami prázdné, Nutmo se odkazát přes IS NOT NULL

=QUERY(B6:G20 ; "select B,C WHERE C is not null")

Poznámka: U textu nefunguje != , <> (nerovná se / různé). NEboli neprojde != ““ nebo <> ““

Které jsou prazdné, již psát nemusím, ale pro jistotu:

=QUERY(B6:G20 ; "select B,C WHERE C is null")

Operátor LIKE v QUERY dotazu

Když už si hrajeme s texty, co když nebudu hledat celý název ale jeho část. Využijeme % a _

  • % jakykoliv počet znaků, v Excel *
  • _ podtržítko, právě jeden znak, v Excel

Co když je část textu? Neboli hledám písmeno A (pozor rozlišuje velká a malá písmena!)

=QUERY(B6:G20 ; "select B,C, E WHERE B LIKE '%A%'")

V některých nápovědách je pro LIKE a hledané použity dvojité uvozovky např. "%A%"" což v mých Google tabulkách nefunguje :( tak kdyby někomu ten apostrof nefungoval.

Opět se mohu odlkazovat na buňku (např. I45) kde je jen hledaní písmeno/slovo

=QUERY(B6:G20 ; "select B,C WHERE B LIKE '%" &I45 &"%'")

Nebo na buňku (J45) kde jsou i a %,_ (nebo-li zástupné znaky), pro náš případ (%A% )

=QUERY(B6:G20 ; "select B,C WHERE B LIKE '" & J45 &"'")

Pro případ, že chci slovo na S, které má pak další 4 písmena

=QUERY(B6:G20 ; "select B,C, E WHERE B LIKE 'S____'")

A případně, jak hledat (selektovat) přes číslo sloupce, pokud má někdo raději

=QUERY({B6:G20} ; "select Col1,Col2 WHERE Col1 LIKE '" & J45 &"'")

Operátor LIKE v QUERY negace

Co ale v případě pokud budu chcít použít Like v negaci? Například z MySQL znáte NOT LIKE. Jenže to v QUERY nezafunguje. NOT musíte dat před písmeno (označení) sloupce.

=QUERY(B6:G20 ; "select B,C, E WHERE NOT B LIKE '%A%'")

Podobně i pro další ukázky, kterí již prezentovat nemusím ;)


QUERY - Podmínky číslo

Vybírat můžete i podle čísla. Neboli je rovno, větší, menší.

Například potřebuji všechny regiony s počtem obyvatel ve sloupci E větší než 5000000.

=QUERY(B6:G20 ; "select B,C, E WHERE E > 5000000")

Pokud je číslo podle kterého se hledá v buňce (I23). Pozor hledaní a operatory se zapisuji odlišně než, když hledáte podle textu.

=QUERY(B6:G20 ; "select B,C, E WHERE E >" & I23)

Nebo i když je operátor mimo buňku (H34) a hodnota (I34). Poznámka: Připomínat, že v Google tabulkách se musí = (rovná se) psát s apostrofem, asi nemusím. Je to podobně jako v Excel.

=QUERY(B6:G20 ; "select B,C, D WHERE D " & H34 & I34)

Nerovná se. Jak jsem poznamenal, jiné než u textu, kde je is not...

=QUERY(B6:G20 ; "select B,C, E WHERE D != 92228531")

nebo:

=QUERY(B6:G20 ; "select B,C, E WHERE D <> 92228531")

pro čísla může být i více podmínek, což popíšu v další kapitole, podobně jako práce s datumem (což je také číslo).


QUERY - Podmínky datum

Podobně jako s číslem lze pracovat i s datumem. Aby datum ve funkci zafungoval, musí být ve tvaru rrrr-MM-dd (někde jsou uvedeny i jiné formy datum, ale v testovacích příkladech mi funguje jen výše uvedený typ ve formátu rrr-MM-dd). Odkaz na číslo nefunguje, pokud sloupec s datum není naformatován jako číslo.

=QUERY(B6:G20 ; "select B, D WHERE D > date '2018-01-10'")

Odkazovat se možno i na buňku s datum. Poznámka: Datum musí být jako text. Při zadání datumu do buňky se může automaticky změnit jako datum (lze předejít využítím apostrofu ' a následně zadaním datum). DAtum je v buňce E24

=QUERY(B6:G20 ; "select B, D WHERE D > date '" & E24&"'")

Pokud je ve sloupci F datum naformátovan jako číslo, lze využít "klasiku" a pracovat jako s číslem.

=QUERY(B6:G20 ; "select B, D WHERE F = 43110")

Více podmínek využití OR, AND

Mnohdy není jen jedna podmínka, ale více. Proto můžete využít OR, AND:

  • OR v četšině NEBO, pokud musí být splněna jenda nebo druhá podmíny (nebo obě)
  • AND v češtině A, pokud musí být splněny obě podmínky zároveň.

NEBO

Zobrazit VIP nebo none.

=QUERY(B6:G20 ; "select B,C WHERE C = 'VIP' OR C = 'none'")

A

Zobrazit řádky splňující VIP a Region (sloupec C) začínající na M ;)

=QUERY(B6:G20 ; "select B,C, E WHERE B LIKE '%M%' AND C = 'VIP'")

Opět jde jen o základ, dají se vytvořit složitější kreace a kombinovat OR a AND. Ale tohle přesahuje možnosti tohoto článku.


Řazení pomocí QUERY

Mate vyselektována (vyfiltrována) požadovaná data, jen je ještě potřebujete seřadit. Máte možnost řadit vzestupně sestupně.

=QUERY(B6:G20 ; "select B,C WHERE C = 'VIP' ORDER BY B")

Pro vzestupné řazení je možno doplnit i o způsob řazení ASC (tím je jasné že bude vzestupně)

=QUERY(B6:G20 ; "select B,C WHERE C = 'VIP' ORDER BY B ASC")

Pro sestupné seřazení nutno využít (doplnit) o způsob řazení, nebo-li DESC

=QUERY(B6:G20 ; "select B,C WHERE C = 'VIP' ORDER BY B DESC")

Jako v předchozích případech můžete místo písmena sloupce využít i označení Col1, ale to je určitě jasné a ukázky nepotřebuje.


Grupování v QUERY

Při použití funkcí se možno seskupit stejné data (sečíst platy u mužů, žen) atd.

=QUERY(B6:G20 ; "select C, SUM (D) GROUP BY C ")

Poznámka: V grupování se používají funkce. Názvy funkcí jsou samozdřejmě v angličtině. Ale tohle nechám na další článek. Přeci neskončím u funkci suma a průměr atd.


Ostantí tipy pro jednu tabulku

Záhlaví

Změnit počet řádku v záhlaví (pokud máte například přes dva řádky), využijete třetí argument ať google zpracuje. Ve výsledné tabulce pak se tyto hodnoty sloučí (vloží) do jednoho řádku.

=QUERY(B5:G20 ; "SELECT B, C" ;2)

Přejmenovat sloupce názvy sloupců

Zahlaví z více řádku je jedna věc. Druhou může býr přejmenování záhlaví. Například z angličtiny na češtinu. Pro přejmenování sloupce použít LABEL (obdoba AS v SQL) název sloupce a pak jeho název.

=QUERY(B6:G20 ; "SELECT B, C LABEL B 'Můj název 1', C 'Můj název 2'")

Další tipy v přípravě


Více tabulek do QUERY - základ

Sloučení dvou tabulek a vypis prvního sloupce:

=QUERY({B6:E15;H6:K15} ; "select Col1")

Pokud označíme více dat budou ve sloučených datech prázdná místa, proto přidáme podmínku, že určitý sloupce nebude nulový (ale to už je opakování)

=QUERY({B6:E15;H6:K15} ; "select Col1 WHERE Col1 is not null")

Pokročilejší práce s více tabulkami

Pokročilejší užití více tabulek již není problém, funguje to co v základu (podmínky, řazení, atd.), jen máte „větší“ datový set.

Takže to nechám plně na vás, případně pro sebe do dalšího článku ;)


Další tipy

Na základě tipů v komentářích, psotupně doplním. Pokud máte příklad zajimavého použití této funkce, můžete zmínit v komentářích. Případně pokud máte nějaký problém, určitě vám někdo z čtenářů odpoví.

V plánu využití funkce IMPORTRANGE, ARRAY...

Problém, nezobrazuje texty

Funkce nezobrazuje textové hodnoty pokud sloupec má více číselných údajů.

Cituji Google náůpovědu k funkci: "In case of mixed data types in a single column, the majority data type determines the data type of the column for query purposes. Minority data types are considered null values".

Co s tím jak to obejít? Využil jsme funkci ARRAYFORMULA a TO_TEXT.

=QUERY(ARRAYFORMULA({TO_TEXT(B6:E15);TO_TEXT(H6:K15)}) ; "select Col1 WHERE Col1 is not null")

Máte-li elegantnější řešení, můžete zmínit v komentářích. Rád ho zde doplním.


Článek byl aktualizován: 21.08.2018 10:21

Odměna

Ušetřil vám tento web čas, peníze? Pomohl vyřešit problém? Jste ochotni poskytnout symbolickou odměnu na další rozvoj? Vybrte si formu odměny, která vám vyhovuje.


Pavel Lasák - autor webu

Pavel Lasák

Microsoft Office (Word, Excel, Google tabulky, PowerPoint) se věnuji od roku 2000 (od 2004 na této doméně) - V roce 2017 jsem od Microsoft získal prestižní ocenění MVP (zatím jsem jediný z ČR v kategorií Excel). Své vědomosti a zkušenosti dávám k dispozici i on-line ve videích pro SEDUO. Ve firmách školím a konzultuji, učím na MUNI. Tento web již tvořím přes 15 let. Zdarma je zde přes 1.000 návodu, tipů a triků, včetně přes 250 různých šablon, sešitů.

   Pavel Lasák LinkedIn Profil    Pavel Lasák Google+ Profil    Pavel Lasák facebook Profil    Pavel Lasák twitter Profil


Komentáře zatím nejsou

Můžete být prvními co zanechají smysluplný komentář.







Sdílejte

Pomohl Vám návod?
Sdílejte na Facebooku, G+
LinkedIn...

Nové články


TOPlist Licence Creative Commons webarchiv rss XML

Stránky o MS Office (Excel) produktu společnosti Microsoft. Neslouží jako technická podpora.
| Email na autora: pavel.lasak@gmail.com | Copyright © : Pavel Lasák 2004 - 2018 | 1432

Tento web zatím neprošel jazykovou korekturou. Beta verze redakčního systému.