Funkce QUERY vám umožňuje získávat informace z rozsahu nebo celého listu dat pomocí flexibilních příkazů dotazu. Naučit se používat funkci Tabulky Google QUERY vám poskytne přístup k výkonnému vyhledávacímu nástroji. Pokud jste někdy psali dotazy SQL za účelem získání dat z databáze, pak poznáte funkci QUERY. Pokud nemáte zkušenosti s databází, funkci 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
- Dotaz: Vyhledávací příkaz popisující, jak ze zdrojových dat extrahovat to, co chcete
- Záhlaví: Volitelný argument, který vám umožní kombinovat více záhlaví ve zdrojovém rozsahu do jednoho záhlaví v cílovém listu
Flexibilita a síla funkce QUERY pochází z argumentu Query, jak uvidíte níže.
Jak vytvořit jednoduchý vzorec QUERY
Vzorec QUERY je obzvláště užitečný, když máte velmi rozsáhlou datovou sadu, ze které potřebujete data extrahovat a filtrovat. Následující příklady používají statistiky výkonnosti středních škol v USA SAT. 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 „New York“ ve jménu školy.
-
Vytvořte nový list pro umístění výsledků dotazu. V levém horním typu buňky = Dotaz (. Když to uděláte, zobrazí se vyskakovací okno s argumenty require, příkladem a užitečnými informacemi o funkci.
-
Dále za předpokladu, že máte zdrojová data v List1, vyplňte funkci následujícím způsobem:
= 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 List1
- Příkaz SELECT: Příkaz SELECT, který požaduje všechna data ve sloupcích B, C, D, E a F, kde sloupec B obsahuje text, který obsahuje slovo „New York“.
Znak „%“ je zástupný znak, který můžete použít k hledání částí řetězců nebo čísel v jakékoli sadě dat. Ponecháním „%“ na přední straně řetězce se vrátí jakýkoli název školy, který začíná textem „New York“.
-
Pokud byste chtěli v seznamu najít název konkrétní školy, můžete zadat dotaz:
= Dotaz (List1! A1: F460, „SELECT B, C, D, E, F WHERE B = ‚New York Harbor High School'“). Za použití = operátor najde přesnou shodu a lze jej použít k nalezení shodného textu nebo čísel v libovolném sloupci.
Protože je funkce Google Sheets QUERY velmi snadno srozumitelná a používaná, můžete všechna data vytáhnout z jakékoli velké datové sady pomocí jednoduchých dotazů, jako jsou ty výše.
Použijte funkci QUERY s operátorem porovnání
Porovnávací operátoři vám umožňují použít funkci QUERY k odfiltrování dat, 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! =: Hledaná hodnota a zdrojové hodnoty nejsou stejné
Pomocí výše uvedené sady příkladů dat SAT se podívejme na to, jak zjistit, které školy měly průměrný matematický průměr nad 500 bodů.
-
V levé horní buňce prázdného listu vyplňte funkci QUERY následujícím způsobem:
= 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, která je větší než 500.
-
Můžete také zahrnout logické operátory, jako jsou 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 testujícími a kritickým průměrem č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")
-
Srovnávací a logické operátory vám poskytují mnoho různých způsobů, jak načítat data ze zdrojové tabulky. Umožňují vám odfiltrovat důležité informace i z velmi velkých datových sad.
Pokročilé použití funkce QUERY
Existuje několik dalších funkcí, které můžete přidat do funkce QUERY pomocí několika dalších příkazů. Tyto příkazy vám umožňují agregovat hodnoty, počítat hodnoty, data objednávek a najít maximální hodnoty.
-
Použití funkce SKUPINA ve funkci QUERY vám umožňuje agregovat hodnoty do více řádků. Pomocí funkce SKUPINA můžete například průměrovat testovací známky pro každého studenta. Chcete -li to provést, zadejte:
= Dotaz (List1! A1: B24, „SELECT A, AVG (B) GROUP BY A“)
-
Pomocí funkce COUNT ve funkci QUERY byste mohli pomocí následující funkce QUERY spočítat počet škol s průměrným skóre psaní nad 500:
= QUERY (Sheet1! A2: F460, „SELECT B, COUNT (F) GROUP BY B“)
-
Pomocí funkce ORDER BY ve funkci QUERY můžete najít školy s maximálním průměrným skóre z matematiky a uspořádat seznam podle těchto skóre.
= QUERY (Sheet1! A2: F460, „SELECT B, MAX (E) GROUP BY B ORDER BY MAX (E)“)
