Funkce QUERY umožňuje získat informace z rozsahu nebo celého listu dat pomocí flexibilních příkazů dotazu. Když se naučíte používat funkci QUERY v Tabulkách Google, získáte přístup k výkonnému vyhledávacímu nástroji.
Pokud jste někdy psali SQL dotazy pro získání dat z databáze, pak znáte funkci QUERY. Pokud nemáte zkušenosti s databází, funkce QUERY je stále velmi snadné se naučit.
Co je funkce QUERY?
Funkce má tři hlavní parametry:
=QUERY(data, dotaz, záhlaví)
Tyto parametry jsou poměrně jednoduché.
- Data: Rozsah buněk, které obsahují zdrojová data
- Query: Vyhledávací příkaz popisující, jak extrahovat to, co chcete ze zdrojových dat
- Headers: Volitelný argument, který umožňuje zkombinovat více záhlaví ve zdrojovém rozsahu do jednoho záhlaví v cílovém listu
Flexibilita a síla funkce QUERY vychází z argumentu Query, jak uvidíte níže.
Jak vytvořit jednoduchý vzorec DOTAZU
Vzorec QUERY je zvláště užitečný, když máte velmi velký soubor dat, ze kterého potřebujete extrahovat a filtrovat data.
Následující příklady používají statistiky výkonnosti středních škol SAT v USA. V tomto prvním příkladu se naučíte, jak napsat jednoduchý vzorec QUERY, který vrátí všechny střední školy a jejich data, kde je v názvu školy „New York“.
-
Vytvořte nový list pro umístění výsledků dotazu. V levé horní buňce zadejte =Query(. Když to uděláte, zobrazí se vyskakovací okno s požadovanými argumenty, příkladem a užitečnými informacemi o funkci.
Image -
Dále, za předpokladu, že máte zdrojová data v Listu1, vyplňte funkci následovně:
=Dotaz(List1!A1:F460, "SELECT B, C, D, E, F WHERE B LIKE '%New York%'")
Tento vzorec obsahuje následující argumenty:
- Rozsah buněk: Rozsah dat v A1 až F460 v Listu1
- Příkaz SELECT: Příkaz SELECT, který vyžaduje jakákoli data ve sloupcích B, C, D, E a F, kde sloupec B obsahuje text se slovem „New York " v něm.
Image Znak "%" je zástupný znak, který můžete použít k vyhledávání částí řetězců nebo čísel v libovolné sadě dat. Ponechání "%" mimo přední část řetězce vrátí jakýkoli název školy, který začíná textem "New York".
-
Pokud byste chtěli najít název přesné školy ze seznamu, můžete zadat dotaz:
=Dotaz(List1!A1:F460, "SELECT B, C, D, E, F WHERE B='New York Harbor High School'")
Pomocí operátoru =najde přesnou shodu a lze jej použít k nalezení shodného textu nebo čísel v libovolném sloupci.
Image
Vzhledem k tomu, že funkce QUERY v Tabulkách Google je velmi snadno pochopitelná a použitelná, můžete pomocí jednoduchých dotazů, jako jsou ty výše, získat jakákoli data z jakékoli velké datové sady.
Použijte funkci QUERY s operátorem porovnání
Operátory porovnávání umožňují pomocí funkce QUERY odfiltrovat data, která nesplňují podmínku.
Ve funkci QUERY máte přístup ke všem následujícím operátorům:
- =: Hodnoty odpovídají hledané hodnotě
- <: Hodnoty jsou menší než hledaná hodnota
- >: Hodnoty jsou větší než hledaná hodnota
- <=: Hodnoty jsou menší nebo rovny hledané hodnotě
- >=: Hodnoty jsou větší nebo rovny hledané hodnotě
- a !=: Hodnota hledání a zdrojové hodnoty se nerovnají
Pomocí stejné sady dat příkladu SAT výše se podívejme, jak zjistit, které školy měly průměrný matematický průměr vyšší než 500 bodů.
-
V levé horní buňce prázdného listu vyplňte funkci QUERY následovně:
=Dotaz(List1!A1:F460, "SELECT B, C, D, E, F WHERE E > 500")
Tento vzorec vyžaduje všechna data, kde sloupec E obsahuje hodnotu větší než 500.
Image -
Můžete také zahrnout logické operátory jako AND a OR pro hledání více podmínek. Chcete-li například získat skóre pouze pro školy s více než 600 účastníky testů a průměrem kritického čtení mezi 400 a 600, zadejte následující funkci QUERY:
=Dotaz(List1!A1:F460, "SELECT B, C, D, E, F WHERE C > 600 AND D > 400 AND D < 600")
Image - Porovnání a logické operátory vám poskytují mnoho různých způsobů, jak získat data ze zdrojové tabulky. Umožňují vám odfiltrovat důležité části informací i z velmi velkých souborů dat.
Pokročilé použití funkce QUERY
Existuje několik dalších funkcí, které můžete přidat k funkci QUERY pomocí některých dalších příkazů. Tyto příkazy umožňují agregovat hodnoty, počítat hodnoty, data objednávek a najít maximální hodnoty.
-
Použití GROUP ve funkci QUERY umožňuje agregovat hodnoty do více řádků. Můžete například zprůměrovat známky každého studenta pomocí funkce GROUP. Chcete-li to provést, zadejte:
=Dotaz(List1!A1:B24, "SELECT A, AVG(B) GROUP BY A")
Image -
Pomocí COUNT ve funkci QUERY můžete spočítat počet škol s průměrným skóre v psaní vyšším než 500 pomocí následující funkce QUERY:
=QUERY(List1!A2:F460, "SELECT B, COUNT (F) GROUP BY B")
Image -
Pomocí ORDER BY ve funkci QUERY můžete najít školy s maximálním matematickým průměrným skóre a seřadit seznam podle těchto skóre.
=QUERY(List1!A2:F460, "VYBRAT B, MAX (E) SKUPINA PODLE B ORDER MAX(E)")
Image
