Funktsioon QUERY võimaldab paindlike päringukäskude abil hankida teavet vahemikust või tervelt andmelehelt. 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 funktsiooni QUERY 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: lähteandmeid sisaldavate lahtrite vahemik
- Query: Otsingulause, mis kirjeldab, kuidas lähteandmetest soovitud välja võtta
- Headers: 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 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".
-
Looge päringutulemuste paigutamiseks uus leht. Tippige vasakpoolsesse ülemisse lahtrisse =Query(. Kui teete seda, näete hüpikakent nõutavate argumentide, näite ja kasuliku teabega funktsiooni kohta.
Image -
Järgmiseks, eeldades, et lähteandmed on lehel Sheet1, täitke funktsioon järgmiselt:
=Päring(Sheet1!A1:F460, "SELECT B, C, D, E, F WHERE B LIKE '%New York%'")
See valem sisaldab järgmisi argumente:
- Lahtide vahemik: andmete vahemik A1 kuni F460 lehel Sheet1
- SELECT lause: SELECT-lause, mis nõuab mis tahes andmeid veergudes B, C, D, E ja F, kus veerg B sisaldab teksti, mis sisaldab sõna "New York " selles.
Image 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".
-
Kui soovite loendist leida täpse kooli nime, võite sisestada päringu:
=Päring(Leht1!A1:F460, "SELECT B, C, D, E, F WHERE B='New Yorgi sadama keskkool'")
Kasutades operaatorit =, leiab täpse vaste ja seda saab kasutada sobiva teksti või numbrite leidmiseks mis tahes veerus.
Image
Kuna Google'i arvutustabelite QUERY funktsiooni on väga lihtne mõista ja kasutada, saate mis tahes suurest andmekomplektist kõik andmed välja tõmmata, kasutades lihtsaid päringulauseid, nagu ül altoodud.
Kasutage funktsiooni QUERY koos võrdlusoperaatoriga
Võrdlusoperaatorid võimaldavad teil kasutada funktsiooni QUERY, et filtreerida välja andmed, mis ei vasta tingimusele.
Teil on funktsioonis QUERY juurdepääs kõikidele järgmistele operaatoritele:
- =: väärtused vastavad otsinguväärtusele
- <: väärtused on väiksemad kui otsinguväärtus
- >: väärtused on suuremad kui otsinguväärtus
- <=: 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 ül altoodud sama SAT-i näidisandmekogumit, vaatame, kuidas näha, milliste koolide keskmine matemaatika keskmine oli üle 500 punkti.
-
Täitke tühja lehe vasakpoolses ülanurgas funktsioon QUERY järgmiselt:
=Päring(Leht1!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.
Image -
Mitme tingimuse otsimiseks võite lisada ka loogilisi operaatoreid nagu JA ja VÕI. Näiteks selleks, et saada hinded ainult koolidele, kus on üle 600 testi sooritaja ja kriitilise lugemise keskmine on 400 ja 600 vahel, sisestage järgmine funktsioon QUERY:
=Päring(Leht1!A1:F460, "VALI B, C, D, E, F WHERE C > 600 AND D > 400 JA D < 600")
Image - Võrdlus ja loogilised operaatorid pakuvad teile palju erinevaid viise andmete hankimiseks lähtearvutustabelist. Need võimaldavad teil isegi väga suurtest andmekogumitest olulise teabe välja filtreerida.
Funktsiooni QUERY täpsemad kasutusvõimalused
On veel mõned funktsioonid, mida saate mõne lisakäsuga funktsioonile QUERY lisada. Need käsud võimaldavad teil koondada väärtusi, loendada väärtusi, tellida andmeid ja leida maksimaalseid väärtusi.
-
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")
Image -
Kasutades funktsiooni QUERY funktsiooni COUNT, saate järgmise funktsiooni QUERY abil lugeda koolide arvu, mille keskmine skoor on üle 500:
=QUERY(Sheet1!A2:F460, "SELECT B, COUNT (F) GROUP BY B")
Image -
Kasutades ORDER BY funktsioonis QUERY, saate leida koole, mille matemaatika keskmised tulemused on maksimaalsed, ja järjestada loendi nende punktide järgi.
=QUERY(Sheet1!A2:F460, "SELECT B, MAX (E) GROUP BY B ORDER BY MAX(E)")
Image
