Funkcija QUERY vam omogoča pridobivanje informacij iz obsega ali celotnega lista podatkov z uporabo prilagodljivih poizvedovalnih ukazov. Če se naučite uporabljati funkcijo Google Preglednice QUERY, dobite dostop do zmogljivega orodja za iskanje.
Če ste kdaj pisali poizvedbe SQL za pridobivanje podatkov iz baze podatkov, boste prepoznali funkcijo QUERY. Če nimate izkušenj z bazo podatkov, se je funkcije QUERY še vedno zelo enostavno naučiti.
Kaj je funkcija QUERY?
Funkcija ima tri glavne parametre:
=QUERY(podatki, poizvedba, glave)
Ti parametri so dokaj enostavni.
- Podatki: obseg celic, ki vsebujejo izvorne podatke
- Query: iskalni stavek, ki opisuje, kako iz izvornih podatkov izvleči, kar želite
- Headers: izbirni argument, ki vam omogoča združevanje več glav v izvornem obsegu v eno samo glavo na ciljnem listu
Prilagodljivost in moč funkcije QUERY izvirata iz argumenta Query, kot boste videli spodaj.
Kako ustvariti preprosto formulo POIZVEDBE
Formula QUERY je še posebej uporabna, ko imate zelo velik nabor podatkov, iz katerega morate ekstrahirati in filtrirati podatke.
Naslednji primeri uporabljajo statistične podatke o srednješolski uspešnosti U. S. SAT. V tem prvem primeru se boste naučili napisati preprosto formulo QUERY, ki vrne vse srednje šole in njihove podatke, kjer je v imenu šole »New York«.
-
Ustvarite nov list za umestitev rezultatov poizvedbe. V zgornjo levo celico vnesite =Poizvedba(. Ko to storite, boste videli pojavno okno z zahtevanimi argumenti, primerom in koristnimi informacijami o funkciji.
Image -
Nato, ob predpostavki, da imate izvorne podatke v Sheet1, izpolnite funkcijo na naslednji način:
=Poizvedba(Sheet1!A1:F460, "SELECT B, C, D, E, F WHERE B LIKE '%New York%'")
Ta formula vključuje naslednje argumente:
- Razpon celic: obseg podatkov v A1 do F460 na listu1
- Stavek SELECT: Stavek SELECT, ki zahteva podatke v stolpcih B, C, D, E in F, kjer stolpec B vsebuje besedilo z besedo »New York« " v njej.
Image Znak »%« je nadomestni znak, ki ga lahko uporabite za iskanje delov nizov ali števil v katerem koli nizu podatkov. Če pustite »%« na začetku niza, bi vrnili vsako ime šole, ki se začne z besedilom »New York«.
-
Če želite na seznamu najti točno ime šole, lahko vnesete poizvedbo:
=Poizvedba (Sheet1!A1:F460, "SELECT B, C, D, E, F WHERE B='New York Harbor High School'")
Uporaba operatorja =najde natančno ujemanje in ga je mogoče uporabiti za iskanje ujemajočega se besedila ali številk v katerem koli stolpcu.
Image
Ker je funkcija Google Preglednic QUERY zelo enostavna za razumevanje in uporabo, lahko povlečete katere koli podatke iz katerega koli velikega nabora podatkov z uporabo preprostih stavkov poizvedbe, kot so zgornji.
Uporabite funkcijo QUERY s primerjalnim operatorjem
Operatorji primerjave vam omogočajo uporabo funkcije QUERY za filtriranje podatkov, ki ne izpolnjujejo pogoja.
V funkciji QUERY imate dostop do vseh naslednjih operatorjev:
- =: Vrednosti se ujemajo z iskalno vrednostjo
- <: Vrednosti so manjše od iskalne vrednosti
- >: Vrednosti so večje od iskalne vrednosti
- <=: Vrednosti so manjše ali enake iskalni vrednosti
- >=: Vrednosti so večje ali enake iskalni vrednosti
- in !=: Vrednost iskanja in izvorne vrednosti niso enake
Uporabimo isti nabor primerov podatkov SAT zgoraj, poglejmo, kako ugotoviti, katere šole so imele povprečno povprečje matematike nad 500 točk.
-
V zgornji levi celici praznega lista izpolnite funkcijo QUERY, kot sledi:
=Poizvedba(Sheet1!A1:F460, "SELECT B, C, D, E, F WHERE E > 500")
Ta formula kliče vse podatke, kjer stolpec E vsebuje vrednost, ki je večja od 500.
Image -
Za iskanje več pogojev lahko vključite tudi logične operatorje, kot sta IN in ALI. Na primer, če želite pridobiti rezultate samo za šole z več kot 600 udeleženci testov in povprečjem kritičnega branja med 400 in 600, bi vnesli naslednjo funkcijo QUERY:
=Poizvedba(Sheet1!A1:F460, "IZBERI B, C, D, E, F WHERE C > 600 IN D > 400 IN D < 600")
Image - Primerjalni in logični operatorji vam ponujajo veliko različnih načinov pridobivanja podatkov iz izvorne preglednice. Omogočajo vam filtriranje pomembnih delov informacij iz celo zelo velikih nizov podatkov.
Napredne uporabe funkcije QUERY
Obstaja še nekaj drugih funkcij, ki jih lahko dodate funkciji QUERY z nekaterimi dodatnimi ukazi. Ti ukazi vam omogočajo združevanje vrednosti, štetje vrednosti, razvrščanje podatkov in iskanje največjih vrednosti.
-
Uporaba GROUP v funkciji QUERY vam omogoča združevanje vrednosti v več vrsticah. Na primer, s funkcijo GROUP lahko izračunate povprečje testnih ocen za vsakega študenta. Če želite to narediti, vnesite:
=Poizvedba(Sheet1!A1:B24, "SELECT A, AVG(B) GROUP BY A")
Image -
Z uporabo funkcije COUNT v funkciji QUERY lahko preštejete število šol s povprečnim rezultatom pisanja nad 500 z uporabo naslednje funkcije QUERY:
=QUERY(Sheet1!A2:F460, "SELECT B, COUNT (F) GROUP BY B")
Image -
Z uporabo ORDER BY v funkciji QUERY lahko poiščete šole z najvišjimi povprečnimi rezultati matematike in razvrstite seznam po teh rezultatih.
=POIZVEDBA(Sheet1!A2:F460, "IZBERI B, NAJVEČ (E) SKUPINA PO B VRSTNI RED PO NAJVIŠJE(E)")
Image
