Funkcija QUERY leidžia gauti informaciją iš diapazono arba viso duomenų lapo naudojant lanksčias užklausos komandas. Išmokę naudotis „Google“skaičiuoklių QUERY funkcija, galėsite pasiekti galingą paieškos įrankį.
Jei kada nors rašėte SQL užklausas, kad gautumėte duomenis iš duomenų bazės, atpažinsite funkciją QUERY. Jei neturite patirties su duomenų baze, funkciją QUERY vis tiek labai lengva išmokti.
Kas yra QUERY funkcija?
Funkcija turi tris pagrindinius parametrus:
=QUERY(duomenys, užklausa, antraštės)
Šie parametrai yra gana paprasti.
- Data: langelių diapazonas, kuriame yra š altinio duomenys
- Užklausa: paieškos sakinys, aprašantis, kaip iš š altinio duomenų išgauti tai, ko norite
- Antraštės: pasirenkamas argumentas, leidžiantis sujungti kelias š altinio diapazono antraštes į vieną paskirties lapo antraštę
Funkcijos QUERY lankstumas ir galia kyla iš užklausos argumento, kaip matysite toliau.
Kaip sukurti paprastą QUERY formulę
Formulė QUERY ypač naudinga, kai turite labai didelį duomenų rinkinį, iš kurio reikia išgauti ir filtruoti duomenis.
Toliau pateikiamuose pavyzdžiuose naudojama JAV SAT vidurinės mokyklos veiklos statistika. Šiame pirmame pavyzdyje sužinosite, kaip parašyti paprastą QUERY formulę, kuri pateikia visas aukštąsias mokyklas ir jų duomenis, kai mokyklos pavadinime yra „Niujorkas“.
-
Sukurkite naują lapą užklausos rezultatams patalpinti. Viršutiniame kairiajame langelyje įveskite =Užklausa(. Kai tai padarysite, pamatysite iššokantįjį langą su reikalaujamais argumentais, pavyzdžiu ir naudinga informacija apie funkciją.
Image -
Toliau, darant prielaidą, kad 1 lape turite š altinio duomenis, užpildykite funkciją taip:
=Užklausa(1 lapas!A1:F460, "PASIRINKITE B, C, D, E, F, KUR B PATINKA '%Niujorkas%'")
Ši formulė apima šiuos argumentus:
- Ląstelių diapazonas: duomenų diapazonas nuo A1 iki F460 1 lape
- SELECT pareiškimas: SELECT sakinys, kuris reikalauja bet kokių duomenų B, C, D, E ir F stulpeliuose, kur B stulpelyje yra tekstas, kuriame yra žodis „Niujorkas " jame.
Image Simbolis „%“yra pakaitos simbolis, kurį galite naudoti norėdami ieškoti eilučių ar skaičių dalių bet kuriame duomenų rinkinyje. Jei eilutės priekyje paliksite „%“, bus grąžintas bet koks mokyklos pavadinimas, prasidedantis tekstu „Niujorkas“.
-
Jei norite iš sąrašo rasti tikslios mokyklos pavadinimą, galite įvesti užklausą:
=Užklausa(1 lapas!A1:F460, "SELECT B, C, D, E, F WHERE B="Niujorko uosto vidurinė mokykla")
Naudodami operatorių =suranda tikslią atitiktį ir gali būti naudojama ieškant atitinkančio teksto ar skaičių bet kuriame stulpelyje.
Image
Kadangi „Google“skaičiuoklių QUERY funkcija yra labai lengva suprasti ir naudoti, galite ištraukti bet kokius duomenis iš bet kokio didelio duomenų rinkinio naudodami paprastus užklausos teiginius, tokius kaip aukščiau.
Naudokite funkciją QUERY su palyginimo operatoriumi
Palyginimo operatoriai leidžia naudoti funkciją QUERY, kad išfiltruotumėte duomenis, kurie neatitinka sąlygos.
Naudodami QUERY funkciją turite prieigą prie visų šių operatorių:
- =: reikšmės atitinka paieškos reikšmę
- <: reikšmės mažesnės nei paieškos reikšmė
- >: reikšmės didesnės nei paieškos reikšmė
- <=: reikšmės yra mažesnės arba lygios paieškos vertei
- >=: reikšmės yra didesnės arba lygios paieškos vertei
- ir !=: paieškos reikšmė ir š altinio reikšmės nėra lygios
Naudodami tą patį anksčiau pateiktą SAT pavyzdinį duomenų rinkinį, pažiūrėkime, kaip sužinoti, kurių mokyklų matematikos vidurkis buvo didesnis nei 500 taškų.
-
Tuščio lapo viršutiniame kairiajame langelyje užpildykite funkciją QUERY taip:
=Užklausa(1 lapas!A1:F460, "SELECT B, C, D, E, F WHERE E > 500")
Ši formulė reikalauja bet kokių duomenų, kurių E stulpelyje yra reikšmė, didesnė nei 500.
Image -
Jei norite ieškoti kelių sąlygų, taip pat galite įtraukti loginius operatorius, pvz., AND ir OR. Pavyzdžiui, norėdami gauti balus tik mokyklose, kuriose yra daugiau nei 600 egzaminų dalyvių, o kritinio skaitymo vidurkis yra nuo 400 iki 600, turite įvesti šią QUERY funkciją:
=Užklausa(Sheet1!A1:F460, "SELECT B, C, D, E, F WHERE C > 600 AND D > 400 AND D < 600")
Image - Palyginimas ir loginiai operatoriai suteikia daug skirtingų būdų, kaip gauti duomenis iš š altinio skaičiuoklės. Jie leidžia išfiltruoti svarbią informaciją net iš labai didelių duomenų rinkinių.
Išplėstinis QUERY funkcijos naudojimas
Yra keletas kitų funkcijų, kurias galite pridėti prie funkcijos QUERY naudodami keletą papildomų komandų. Šios komandos leidžia kaupti reikšmes, skaičiuoti reikšmes, užsakyti duomenis ir rasti maksimalias reikšmes.
-
Naudodami GROUP funkciją QUERY, galite kaupti reikšmes keliose eilutėse. Pavyzdžiui, naudodami funkciją GROUP, galite apskaičiuoti kiekvieno mokinio testo pažymių vidurkį. Norėdami tai padaryti, įveskite:
=Užklausa(1 lapas!A1:B24, "SELECT A, AVG(B) GROUP BY A")
Image -
Naudodami funkciją QUERY, galite suskaičiuoti mokyklų, kurių rašymo vidurkis yra didesnis nei 500, skaičių, naudodami šią QUERY funkciją:
=QUERY(1 lapas!A2:F460, "SELECT B, COUNT (F) GROUP BY B")
Image -
Naudodami ORDER BY funkciją QUERY, galite rasti mokyklas, kurių matematikos vidurkis yra didžiausias, ir sąrašą surikiuoti pagal šiuos balus.
=QUERY(1 lapas!A2:F460, "SELECT B, MAX (E) GROUP BY B ORDER BY MAX(E)")
Image
