Funkcia QUERY vám umožňuje získať informácie z rozsahu alebo celého hárka údajov pomocou flexibilných príkazov dotazu. Naučte sa používať funkciu QUERY v Tabuľkách Google a získate prístup k výkonnému vyhľadávaciemu nástroju.
Ak ste niekedy písali SQL dotazy na získanie údajov z databázy, potom poznáte funkciu QUERY. Ak nemáte skúsenosti s databázou, funkciu QUERY sa stále veľmi ľahko naučíte.
Čo je funkcia QUERY?
Funkcia má tri hlavné parametre:
=QUERY(dáta, dopyt, hlavičky)
Tieto parametre sú pomerne jednoduché.
- Údaje: Rozsah buniek, ktoré obsahujú zdrojové údaje
- Query: Hľadanie popisujúce, ako zo zdrojových údajov extrahovať to, čo chcete
- Headers: Voliteľný argument, ktorý vám umožňuje skombinovať viacero hlavičiek v zdrojovom rozsahu do jednej hlavičky v cieľovom hárku
Flexibilita a sila funkcie QUERY vychádza z argumentu Query, ako uvidíte nižšie.
Ako vytvoriť jednoduchý vzorec QUERY
Vzorec QUERY je obzvlášť užitočný, keď máte veľmi veľkú množinu údajov, z ktorej potrebujete extrahovať a filtrovať údaje.
Nasledujúce príklady používajú štatistiku výkonnosti stredných škôl SAT v USA. V tomto prvom príklade sa naučíte, ako napísať jednoduchý vzorec QUERY, ktorý vráti všetky stredné školy a ich údaje, kde je v názve školy „New York“.
-
Vytvorte nový hárok na umiestnenie výsledkov dotazu. V ľavej hornej bunke napíšte =Query(. Keď to urobíte, zobrazí sa kontextové okno s požadovanými argumentmi, príkladom a užitočnými informáciami o funkcii.
Image -
Ďalej, za predpokladu, že máte zdrojové údaje v Hárok1, vyplňte funkciu takto:
=Dopyt(Hárok1!A1:F460, "VYBERTE B, C, D, E, F KDE B LIKE '%New York%'")
Tento vzorec obsahuje nasledujúce argumenty:
- Rozsah buniek: Rozsah údajov v A1 až F460 v hárku 1
- Výpis SELECT: Príkaz SELECT, ktorý vyžaduje akékoľvek údaje v stĺpcoch B, C, D, E a F, pričom stĺpec B obsahuje text so slovom „New York " v ňom.
Image Znak "%" je zástupný znak, ktorý môžete použiť na vyhľadávanie častí reťazcov alebo čísel v ľubovoľnej množine údajov. Ak ponecháte „%“mimo prednej časti reťazca, vráti sa akýkoľvek názov školy, ktorý začína textom „New York“.
-
Ak by ste chceli nájsť presný názov školy zo zoznamu, môžete zadať dopyt:
=Dotaz(Hárok1!A1:F460, "SELECT B, C, D, E, F WHERE B='New York Harbor High School'")
Pomocou operátora =nájde presnú zhodu a dá sa použiť na nájdenie zhodného textu alebo čísel v ľubovoľnom stĺpci.
Image
Vzhľadom na to, že funkcia QUERY v Tabulkách Google je veľmi jednoduchá na pochopenie a používanie, môžete z akejkoľvek veľkej množiny údajov získať akékoľvek údaje pomocou jednoduchých príkazov dotazu, ako sú tie vyššie.
Použite funkciu QUERY s operátorom porovnávania
Operátory porovnávania vám umožňujú použiť funkciu QUERY na odfiltrovanie údajov, ktoré nespĺňajú podmienku.
Máte prístup ku všetkým nasledujúcim operátorom vo funkcii QUERY:
- =: Hodnoty zodpovedajú hodnote vyhľadávania
- <: Hodnoty sú nižšie ako vyhľadávaná hodnota
- >: Hodnoty sú väčšie ako hľadaná hodnota
- <=: Hodnoty sú menšie alebo rovné hodnote vyhľadávania
- >=: Hodnoty sú väčšie alebo rovné hodnote vyhľadávania
- a !=: Hľadaná hodnota a zdrojové hodnoty sa nezhodujú
Pomocou rovnakého príkladu údajov SAT vyššie sa pozrime na to, ako zistiť, ktoré školy mali priemerný matematický priemer vyšší ako 500 bodov.
-
V ľavej hornej bunke prázdneho hárka vyplňte funkciu QUERY takto:
=Dopyt(Hárok1!A1:F460, "SELECT B, C, D, E, F WHERE E > 500")
Tento vzorec vyžaduje všetky údaje, kde stĺpec E obsahuje hodnotu väčšiu ako 500.
Image -
Môžete tiež zahrnúť logické operátory ako AND a OR na vyhľadávanie viacerých podmienok. Ak chcete napríklad získať skóre iba pre školy s viac ako 600 účastníkmi testov a priemerom kritického čítania medzi 400 a 600, zadajte nasledujúcu funkciu QUERY:
=Dopyt(Hárok1!A1:F460, "VYBERTE B, C, D, E, F KDE C > 600 A D > 400 A D < 600")
Image - Porovnávanie a logické operátory vám poskytujú mnoho rôznych spôsobov, ako získať údaje zo zdrojovej tabuľky. Umožňujú vám odfiltrovať dôležité časti informácií aj z veľmi veľkých množín údajov.
Pokročilé využitie funkcie QUERY
Existuje niekoľko ďalších funkcií, ktoré môžete pridať do funkcie QUERY pomocou niektorých dodatočných príkazov. Tieto príkazy vám umožňujú agregovať hodnoty, počítať hodnoty, údaje o objednávkach a nájsť maximálne hodnoty.
-
Používanie GROUP vo funkcii QUERY vám umožňuje agregovať hodnoty vo viacerých riadkoch. Môžete napríklad spriemerovať známky každého študenta pomocou funkcie GROUP. Ak to chcete urobiť, zadajte:
=Dopyt(Hárok1!A1:B24, "VYBERTE A, AVG(B) GROUP BY A")
Image -
Pomocou COUNT vo funkcii QUERY môžete spočítať počet škôl s priemerným skóre v písaní nad 500 pomocou nasledujúcej funkcie QUERY:
=QUERY(Hárok1!A2:F460, "SELECT B, COUNT (F) GROUP BY B")
Image -
Pomocou funkcie ORDER BY vo funkcii QUERY môžete nájsť školy s maximálnym matematickým priemerným skóre a zoradiť zoznam podľa týchto skóre.
=QUERY(Hárok1!A2:F460, "VYBERTE B, MAXIMÁLNE (E) SKUPINA PODĽA B PORADIE PODĽA MAXIMÁLNEHO (E)")
Image
