Funkcija QUERY ļauj iegūt informāciju no diapazona vai visas datu lapas, izmantojot elastīgas vaicājuma komandas. Apgūstot Google izklājlapu funkciju QUERY, varat piekļūt jaudīgam meklēšanas rīkam.
Ja kādreiz esat rakstījis SQL vaicājumus, lai iegūtu datus no datu bāzes, tad atpazīsiet funkciju QUERY. Ja jums nav pieredzes datubāzē, funkciju QUERY joprojām ir ļoti viegli apgūt.
Kas ir QUERY funkcija?
Funkcijai ir trīs galvenie parametri:
=VAIKĀJUMS(dati, vaicājums, galvenes)
Šie parametri ir diezgan vienkārši.
- Data: šūnu diapazons, kas satur avota datus
- Vaicājums: meklēšanas priekšraksts, kurā aprakstīts, kā iegūt vajadzīgo no avota datiem
- Headers: izvēles arguments, kas ļauj apvienot vairākas avota diapazona galvenes vienā galamērķa lapas galvenē
Funkcijas QUERY elastība un jauda izriet no argumenta Query, kā jūs redzēsiet tālāk.
Kā izveidot vienkāršu QUERY formulu
Formula QUERY ir īpaši noderīga, ja jums ir ļoti liela datu kopa, no kuras nepieciešams iegūt un filtrēt datus.
Šajos piemēros izmantota ASV SAT vidusskolas snieguma statistika. Šajā pirmajā piemērā jūs uzzināsit, kā uzrakstīt vienkāršu QUERY formulu, kas atgriež visas vidusskolas un to datus, kur skolas nosaukumā ir “Ņujorka”.
-
Izveidojiet jaunu lapu vaicājuma rezultātu ievietošanai. Augšējā kreisajā šūnā ierakstiet =Vaicājums(. Kad to izdarīsit, tiks parādīts uznirstošais logs ar prasītajiem argumentiem, piemēru un noderīgu informāciju par funkciju.
Image -
Pēc tam, pieņemot, ka 1. lapā ir avota dati, aizpildiet funkciju šādi:
=Vaicājums(1. lapa!A1:F460, "ATLASĪT B, C, D, E, F, KUR B PATĪK '%Ņujorka%'")
Šajā formulā ir iekļauti šādi argumenti:
- Šūnu diapazons: datu diapazons no A1 līdz F460 lapā 1
- SELECT Paziņojums: SELECT priekšraksts, kas pieprasa visus datus kolonnās B, C, D, E un F, kur B kolonnā ir teksts ar vārdu "Ņujorka " tajā.
Image Zīmols "%" ir aizstājējzīme, ko varat izmantot, lai meklētu virkņu vai ciparu daļas jebkurā datu kopā. Atstājot "%" no virknes priekšpuses, tiks atgriezts jebkurš skolas nosaukums, kas sākas ar tekstu "Ņujorka".
-
Ja vēlaties sarakstā atrast precīzu skolas nosaukumu, varat ievadīt vaicājumu:
=Vaicājums(Sheet1!A1:F460, "SELECT B, C, D, E, F WHERE B='Ņujorkas ostas vidusskola")
Izmantojot operatoru =, tiek atrasta precīza atbilstība, un to var izmantot, lai atrastu atbilstošu tekstu vai ciparus jebkurā kolonnā.
Image
Tā kā Google izklājlapu QUERY funkcija ir ļoti viegli saprotama un lietojama, varat izvilkt jebkurus datus no jebkuras lielas datu kopas, izmantojot vienkāršus vaicājuma priekšrakstus, piemēram, iepriekš minētos.
Izmantojiet funkciju QUERY ar salīdzināšanas operatoru
Salīdzināšanas operatori ļauj izmantot funkciju QUERY, lai filtrētu datus, kas neatbilst nosacījumam.
Jums ir piekļuve visiem šiem operatoriem funkcijā QUERY:
- =: vērtības atbilst meklēšanas vērtībai
- <: vērtības ir mazākas par meklēšanas vērtību
- >: vērtības ir lielākas par meklēšanas vērtību
- <=: vērtības ir mazākas vai vienādas ar meklēšanas vērtību
- >=: vērtības ir lielākas vai vienādas ar meklēšanas vērtību
- un !=: meklēšanas vērtība un avota vērtības nav vienādas
Izmantojot to pašu iepriekš minēto SAT piemēru datu kopu, apskatīsim, kā noskaidrot, kurās skolās vidējais matemātikas rādītājs bija virs 500 punktiem.
-
Tukšas lapas augšējā kreisajā šūnā aizpildiet funkciju QUERY šādi:
=Vaicājums(1. lapa!A1:F460, "SELECT B, C, D, E, F WHERE E > 500")
Šī formula pieprasa visus datus, kuru kolonnā E ir vērtība, kas ir lielāka par 500.
Image -
Varat iekļaut arī loģiskos operatorus, piemēram, UN un VAI, lai meklētu vairākus nosacījumus. Piemēram, lai iegūtu rezultātus tikai skolām, kurās ir vairāk nekā 600 eksāmenu kārtotāju un kritiskās lasīšanas vidējais rādītājs ir no 400 līdz 600, jums ir jāievada šāda funkcija QUERY:
=Vaicājums(1. lapa!A1:F460, "ATLASĪT B, C, D, E, F, KUR C > 600 UN D > 400 UN D < 600")
Image - Salīdzinājums un loģiskie operatori nodrošina daudz dažādu veidu, kā iegūt datus no avota izklājlapas. Tie ļauj filtrēt svarīgu informāciju pat no ļoti lielām datu kopām.
Funkcijas QUERY uzlabotas izmantošanas iespējas
Ir dažas citas funkcijas, kuras varat pievienot funkcijai QUERY ar dažām papildu komandām. Šīs komandas ļauj apkopot vērtības, saskaitīt vērtības, pasūtīt datus un atrast maksimālās vērtības.
-
Izmantojot GROUP funkcijā QUERY, varat apkopot vērtības vairākās rindās. Piemēram, izmantojot funkciju GROUP, katram skolēnam varat aprēķināt vidējo pārbaudes atzīmes. Lai to izdarītu, ierakstiet:
=Vaicājums(1. lapa!A1:B24, "ATLASĪT A, AVG(B) GROUP BY A")
Image -
Izmantojot COUNT funkcijā QUERY, varat saskaitīt skolu skaitu, kuru vidējais rakstīšanas rezultāts pārsniedz 500, izmantojot šādu funkciju QUERY:
=VAIKĀJUMS(1. lapa!A2:F460, "ATLASĪT B, SKAITĪT (F) GROUP BY B")
Image -
Izmantojot ORDER BY funkcijā QUERY, varat atrast skolas ar maksimālo matemātikas vidējo punktu skaitu un sakārtot sarakstu pēc šiem rādītājiem.
=VAIKĀJUMS(1. lapa!A2:F460, "SELECT B, MAX (E) GROUP BY B ORDER BY MAX(E)")
Image
