Kuidas kasutada Google'i arvutustabelite funktsiooni QUERY

Funktsioon QUERY võimaldab paindlike päringukäskude abil hankida teavet vahemikust või tervest andmelehest. Google'i arvutustabelite funktsiooni QUERY kasutamise õppimine annab teile juurdepääsu võimsale otsingutööriistale.

Kui olete kunagi kirjutanud SQL-päringuid, et andmeid andmebaasist välja võtta, tunnete ära funktsiooni QUERY. Kui teil pole andmebaasikogemust, on QUERY funktsiooni siiski väga lihtne õppida.

Mis on funktsioon QUERY?

Funktsioonil on kolm peamist parameetrit:

=PÄRING(andmed, päring, päised)

Need parameetrid on üsna lihtsad.

• Andmed: lahtrite vahemik, mis sisaldab lähteandmeid
• Päring: Otsinguavaldus, mis kirjeldab, kuidas lähteandmetest soovitud välja võtta
• Päised: valikuline argument, mis võimaldab ühendada mitu lähtevahemiku päist sihtlehel üheks päiseks

Funktsiooni QUERY paindlikkus ja võimsus tuleneb päringu argumendist, nagu näete allpool.

Kuidas luua lihtsat QUERY valemit

Valem QUERY on eriti kasulik, kui teil on väga suur andmekogum, millest peate andmeid eraldama ja filtreerima.

Järgmised näited kasutavad USA SAT-i keskkoolide tulemuslikkuse statistikat. Selles esimeses näites saate teada, kuidas kirjutada lihtsat QUERY valemit, mis tagastab kõik keskkoolid ja nende andmed, kus kooli nimes on "New York".

1. Looge päringutulemuste paigutamiseks uus leht. Ülemises vasakpoolses lahtritüübis =päring(. Kui teete seda, näete hüpikakent nõutavate argumentide, näite ja kasuliku teabega funktsiooni kohta.

   

2. Järgmiseks, eeldades, et lähteandmed on lehel Sheet1, täitke funktsioon järgmiselt.

   =Päring (Sheet1!A1:F460,"VALI B, C, D, E, F, KUS B MEELDIB '%New York%'").

   See valem sisaldab järgmisi argumente:

   • Rakkude vahemik: andmete vahemik vahemikus A1 kuni F460 lehel Sheet1
   • VALI avaldus: SELECT-lause, mis nõuab mis tahes andmeid veergudes B, C, D, E ja F, kus veerg B sisaldab teksti, milles on sõna "New York".

   

   Märk "%" on metamärk, mida saate kasutada stringide või numbrite osade otsimiseks mis tahes andmekogumist. Kui jätate stringi esiosast välja "%", tagastatakse kõik koolinimed, mis algavad tekstiga "New York".

3. Kui soovite loendist leida täpse kooli nime, võite sisestada päringu:

   =Päring (Sheet1!A1:F460,"SELECT B, C, D, E, F WHERE B = "New York Harbori keskkool").

   Kasutades = operaator leiab täpse vaste ja seda saab kasutada sobiva teksti või numbrite leidmiseks mis tahes veerus.

   

Kuna Google'i arvutustabelite QUERY funktsiooni on väga lihtne mõista ja kasutada, saate ülaltoodud lihtsate päringulausete abil mis tahes suurest andmekomplektist kõik andmed välja tõmmata.

Kasutage funktsiooni QUERY koos võrdlusoperaatoriga

Võrdlusoperaatorid võimaldavad kasutada funktsiooni QUERY, et filtreerida välja andmed, mis tingimusele ei vasta.

Funktsioonis QUERY on teil juurdepääs kõikidele järgmistele operaatoritele:

• =: väärtused vastavad otsinguväärtusele
• <: väärtused on otsinguväärtusest väiksemad
• >: väärtused on otsinguväärtusest suuremad
• <=: väärtused on otsinguväärtusest väiksemad või sellega võrdsed
• >=: väärtused on otsinguväärtusest suuremad või sellega võrdsed
• <> ja !=: otsinguväärtus ja lähteväärtus ei ole võrdsed

Kasutades ülaltoodud sama SAT-i näidisandmekogumit, vaatame, kuidas näha, millistes koolides oli keskmine matemaatika keskmine üle 500 punkti.

1. Tühja lehe ülemises vasakpoolses lahtris täitke funktsioon QUERY järgmiselt.

   =Päring (Sheet1!A1:F460,"SELECT B, C, D, E, F WHERE E > 500")

   See valem nõuab mis tahes andmeid, mille veerg E sisaldab väärtust, mis on suurem kui 500.

   

2. Mitme tingimuse otsimiseks võite lisada ka loogilisi operaatoreid, nagu AND ja OR. Näiteks selleks, et saada hinded ainult koolidele, kus on üle 600 testija ja kriitilise lugemise keskmine on 400 ja 600 vahel, sisestage järgmine funktsioon QUERY:

   =Päring (Sheet1!A1:F460,"VALI B, C, D, E, F, KUS C > 600 JA D > 400 JA D < 600")

   

3. Võrdlus- ja loogilised operaatorid pakuvad teile palju erinevaid viise andmete hankimiseks lähtearvutustabelist. Need võimaldavad teil isegi väga suurtest andmekogumitest välja filtreerida olulise teabe.

Funktsiooni QUERY täpsemad kasutusvõimalused

Funktsioonile QUERY saate lisada veel mõned funktsioonid mõne lisakäsuga. Need käsud võimaldavad teil väärtusi koondada, väärtusi lugeda, andmeid tellida ja maksimaalseid väärtusi leida.

1. Funktsiooni GROUP kasutamine funktsioonis QUERY võimaldab koondada väärtusi mitmele reale. Näiteks saate funktsiooni GROUP abil määrata iga õpilase testihinnete keskmised. Selleks tippige:

   =Päring (Sheet1!A1:B24,"SELECT A, AVG(B) GROUP BY A")

   

2. Kasutades funktsiooni QUERY funktsiooni COUNT, saate järgmise funktsiooni QUERY abil lugeda koolide arvu, mille keskmine skoor on üle 500:

   =PÄRING(Sheet1!A2:F460,"VALI B, COUNT (F) GROUP BY B")

   

3. Funktsioonis QUERY kasutades ORDER BY abil saate leida maksimaalse matemaatika keskmise punktisummaga koole ja järjestada nimekirja nende punktide järgi.

   =QUERY(Sheet1!A2:F460,"SELECT B, MAX (E) GROUP BY B ORDER BY MAX(E)")