Utilizarea funcției QUERY

Funcția QUERY nu este prea dificil de stăpânit dacă ați interacționat vreodată cu o bază de date folosind SQL. Formatul unei funcții tip QUERY este similar cu SQL și aduce puterea căutărilor în baze de date în Google Sheets.

Formatul unei formule care utilizează funcția QUERY este = QUERY (date, interogare, anteturi). Înlocuiți „date” cu intervalul de celule (de exemplu, „A2: D12” sau „A: D”) și „interogare” cu interogarea dvs. de căutare.

Argumentul „antet” opțional stabilește numărul de rânduri de antet pe care trebuie să le includă în partea de sus a intervalului de date. Dacă aveți un antet care se răspândește pe două celule, cum ar fi „First” în A1 și „Name” în A2, acest lucru ar specifica faptul că QUERY va folosi conținutul primelor două rânduri ca antet combinat.

În exemplul de mai jos, o foaie (numită „Lista personalului”) dintr-o foaie de calcul Google Sheets include o listă de angajați. Acesta include numele lor, numerele de identificare ale angajaților, datele nașterii și dacă au participat la sesiunea lor obligatorie de formare a angajaților.

Într-o a doua foaie, puteți utiliza o formulă QUERY pentru a trage o listă cu toți angajații care nu au participat la sesiunea de formare obligatorie. Această listă va include numerele de identificare ale angajaților, prenumele, prenumele și dacă au participat la sesiunea de formare.

Pentru a face acest lucru cu datele prezentate mai sus, puteți tasta = QUERY (‘Lista personalului! A2: E12, “SELECT A, B, C, E WHERE E =’ Nu ‘”). Acest lucru interogează datele din intervalul A2 până la E12 din fișa „Lista personalului”.

Ca o interogare SQL tipică, funcția QUERY selectează coloanele de afișat (SELECT) și identifică parametrii pentru căutare (WHERE). Acesta returnează coloanele A, B, C și E, oferind o listă cu toate rândurile potrivite în care valoarea din coloana E („Pregătire instruită”) este un șir de text care conține „Nu”.

După cum se arată mai sus, patru angajați din lista inițială nu au participat la o sesiune de formare. Funcția QUERY a furnizat aceste informații, precum și coloane potrivite pentru a afișa numele lor și numerele de identificare ale angajaților într-o listă separată.

Acest exemplu utilizează o gamă foarte specifică de date. Puteți schimba acest lucru pentru a interoga toate datele din coloanele A la E. Acest lucru vă va permite să continuați să adăugați noi angajați în listă. Formula QUERY pe care ați utilizat-o se va actualiza automat de fiecare dată când adăugați noi angajați sau când cineva participă la sesiunea de formare.

Formula corectă pentru aceasta este = QUERY (‘Lista personalului’! A2: E, “Selectați A, B, C, E WHERE E = ‘Nu'”). Această formulă ignoră titlul inițial „Angajați” din celula A1.

Dacă adăugați un al 11-lea angajat care nu a participat la formare pe lista inițială, așa cum se arată mai jos (Christine Smith), formula QUERY se actualizează, de asemenea, și afișează noul angajat.

Formule avansate de interogare

Funcția QUERY este versatilă. Vă permite să utilizați alte operații logice (cum ar fi AND și OR) sau funcții Google (cum ar fi COUNT) ca parte a căutării dvs. Puteți utiliza, de asemenea, operatori de comparație (mai mare decât, mai puțin decât, etc.) pentru a găsi valori între două cifre.
Utilizarea operatorilor de comparație cu QUERY

Puteți utiliza QUERY cu operatorii de comparație (cum ar fi mai puțin, mai mare sau mai mare decât) pentru a restrânge și filtra date. Pentru a face acest lucru, vom adăuga o coloană suplimentară (F) în fișa noastră „Lista personalului” cu numărul de premii pe care le-a câștigat fiecare angajat.

Folosind QUERY, putem căuta toți angajații care au câștigat cel puțin un premiu. Formatul acestei formule este = QUERY (“Lista personalului”! A2: F12, “SELECTĂ A, B, C, D, E, F WHERE F> 0”).

Aceasta folosește un operator mai mare decât comparația (>) pentru a căuta valori peste zero în coloana F.

Exemplul de mai sus arată că funcția QUERY a returnat o listă de opt angajați care au câștigat unul sau mai multe premii. Din 11 angajați, trei nu au câștigat niciodată un premiu.

Folosind AND și OR cu QUERY

Funcțiile de operator logic imbricate precum AND și OR funcționează bine într-o formulă QUERY mai mare pentru a adăuga mai multe criterii de căutare la formula dvs.

O modalitate bună de a testa AND este de a căuta date între două date. Dacă folosim exemplul listei de angajați, am putea enumera toți angajații născuți între 1980 și 1989.

Acest lucru profită de asemenea de operatorii de comparație, ca mai mari sau egali cu (> =) și mai mici sau egali cu (<=).

Formatul acestei formule este = QUERY (‘Lista personalului’! A2: E12, “SELECT A, B, C, D, E WHERE D> = DATA ‘1980-1-1’ și D <= DATA ‘1989-12 -31’ “). Aceasta folosește, de asemenea, o funcție suplimentară DATE cuibărită pentru a analiza corect timestamps-urile de date, și caută toate zilele de naștere între 1 și 1 ianuarie 1980 și 31 decembrie 1989.

După cum se arată mai sus, trei angajați care s-au născut în 1980, 1986 și 1983 îndeplinesc aceste cerințe.

De asemenea, puteți utiliza OR pentru a produce rezultate similare. Dacă folosim aceleași date, dar schimbăm datele și folosim OR, putem exclude toți angajații care s-au născut în anii ’80.

Formatul acestei formule ar fi = QUERY (‘Lista personalului’! A2: E12, “SELECT A, B, C, D, E WHERE D> = DATA ‘1989-12-31’ sau D <= DATA ‘1980- 1-1’ “).

Din cei 10 angajați originali, trei s-au născut în anii ’80. Exemplul de mai sus arată celelalte șapte, care s-au născut toate înainte sau după datele pe care le-am exclus.

Utilizarea COUNT cu QUERY

În loc să căutați și să returnați date, puteți, de asemenea, să amestecați QUERY cu alte funcții, cum ar fi COUNT, pentru a manipula datele. Să spunem că vrem să ștergem un număr de angajați de pe lista noastră care au participat și nu au participat la sesiunea de pregătire obligatorie.

Pentru a face acest lucru, puteți combina QUERY cu COUNT astfel = QUERY („Lista cu personalul”! A2: E12, „SELECT E, COUNT (E) group by E”).

O formulă din Google Foi, care utilizează o funcție QUERY combinată cu o COUNT pentru a număra numărul de mențiuni cu o anumită valoare într-o coloană.

Concentrându-se pe coloana E („Training Attended”), funcția QUERY folosea COUNT pentru a număra de câte ori a fost găsit fiecare tip de valoare (un „Text” „Da” sau „Nu”). Din lista noastră, șase angajați au finalizat pregătirea și patru nu au reușit.

Puteți modifica cu ușurință această formulă și să o utilizați cu alte tipuri de funcții Google, cum ar fi SUM.