În cazul în care aveți nevoie să prelucrați informații în Google Sheets, funcția QUERY vă poate fi de mare ajutor! Aceasta oferă o modalitate eficientă de a căuta și filtra datele, similar cu utilizarea unei baze de date, permițându-vă să organizați și să analizați informațiile în diverse moduri. În continuare, vă vom explica cum să o folosiți eficient.
Înțelegerea și utilizarea funcției QUERY
Funcția QUERY nu este complicată dacă ați lucrat anterior cu baze de date și limbajul SQL. Structura unei formule QUERY este asemănătoare cu sintaxa SQL, transpunând puterea interogărilor bazelor de date în Google Sheets.
O formulă tipică QUERY are formatul: =QUERY(date, interogare, anteturi). În acest format, „date” este intervalul de celule pe care doriți să îl analizați (de exemplu, „A2:D12” sau „A:D”), iar „interogare” este comanda de căutare propriu-zisă.
Argumentul opțional „anteturi” specifică numărul de rânduri care definesc antetul tabelului. Dacă, de exemplu, aveți un antet care se extinde pe două rânduri, precum „Nume” în celula A1 și „Prenume” în A2, acest argument va indica funcției QUERY să utilizeze ambele rânduri ca un antet combinat.
În exemplul ce urmează, ne vom uita la un tabel („Lista de angajați”) dintr-o foaie Google Sheets, unde avem informații despre angajați, inclusiv numele, numărul de identificare, data nașterii și participarea la un training obligatoriu.
Pe o altă foaie, putem folosi o formulă QUERY pentru a genera o listă cu angajații care nu au participat la trainingul obligatoriu. Această listă va conține numerele de identificare, prenumele, numele de familie și informația despre participarea la training.
Pentru datele de mai sus, formula ar fi: =QUERY(‘Lista de angajați’!A2:E12, „SELECT A, B, C, E WHERE E = ‘Nu'”). Această formulă caută în intervalul A2:E12 din foaia „Lista de angajați”.
Asemenea unei interogări SQL, funcția QUERY selectează coloanele care trebuie afișate (SELECT) și definește condițiile de căutare (WHERE). Formula de mai sus va returna coloanele A, B, C și E pentru acele rânduri în care valoarea din coloana E („Participare training”) este „Nu”.
Rezultatul arată că patru angajați nu au participat la training. Funcția QUERY a extras aceste informații, inclusiv numele și numerele de identificare, și le-a afișat într-o listă separată.
Acest exemplu utilizează un interval specific de date. Însă, puteți modifica formula pentru a interoga toate datele din coloanele A până la E. Astfel, veți putea continua să adăugați noi angajați, iar formula QUERY se va actualiza automat la fiecare adăugare sau modificare a datelor.
Formula corectă în acest caz este: =QUERY(‘Lista de angajați’!A2:E, „Select A, B, C, E WHERE E = ‘Nu'”). Această variantă ignoră antetul inițial „Angajați” din celula A1.
Dacă, de exemplu, adăugăm un al 11-lea angajat care nu a participat la training (Christine Smith), formula QUERY se actualizează automat și include noul angajat în listă.
Tehnici avansate cu funcția QUERY
Funcția QUERY este foarte flexibilă. Puteți include în interogări operații logice (AND, OR) sau funcții Google (COUNT). De asemenea, operatorii de comparație (mai mare decât, mai mic decât etc.) vă permit să căutați valori între anumite limite.
Utilizarea operatorilor de comparație
Funcția QUERY poate folosi operatori de comparație (mai mic decât, mai mare decât, egal cu) pentru a restrânge și filtra datele. Pentru a ilustra acest lucru, vom adăuga o coloană suplimentară (F) la foaia „Lista de angajați” care va conține numărul de premii câștigate de fiecare angajat.
Cu QUERY, putem căuta angajații care au câștigat cel puțin un premiu. Formula pentru această interogare ar fi: =QUERY(‘Lista de angajați’!A2:F12, „SELECT A, B, C, D, E, F WHERE F > 0”).
Această formulă folosește operatorul „mai mare decât” (>) pentru a căuta valorile din coloana F care sunt peste zero.
După cum se vede, funcția QUERY a returnat o listă cu opt angajați care au câștigat cel puțin un premiu. Din cei 11 angajați, trei nu au câștigat niciodată un premiu.
Combinarea AND și OR
Funcțiile logice AND și OR pot fi integrate în formulele QUERY pentru a adăuga criterii complexe de căutare.
Un exemplu bun pentru a testa AND este să căutăm date între două valori. Pentru lista de angajați, putem enumera angajații născuți între 1980 și 1989.
Această căutare folosește operatorii de comparație „mai mare sau egal cu” (>=) și „mai mic sau egal cu” (
Formula pentru această operație este: =QUERY(‘Lista de angajați’!A2:E12, „SELECT A, B, C, D, E WHERE D >= DATE ‘1980-1-1’ and D
Aici vedem că trei angajați, născuți în 1980, 1986 și 1983, îndeplinesc condițiile specificate.
Similar, putem folosi OR pentru a exclude anumiți angajați. Dacă folosim aceleași date și schimbăm condițiile cu OR, putem exclude angajații născuți în anii 1980.
Formula ar fi: =QUERY(‘Lista de angajați’!A2:E12, „SELECT A, B, C, D, E WHERE D >= DATE ‘1989-12-31’ sau D
Din cei 10 angajați, trei sunt născuți în anii 1980. Exemplul de mai sus îi afișează pe ceilalți șapte, născuți înainte sau după intervalul exclus.
Utilizarea COUNT cu QUERY
Pe lângă căutarea și returnarea de date, funcția QUERY se poate combina și cu funcții precum COUNT, pentru a manipula informațiile. Să presupunem că dorim să numărăm angajații care au participat și cei care nu au participat la trainingul obligatoriu.
Pentru aceasta, putem combina QUERY cu COUNT astfel: =QUERY(‘Lista de angajați’!A2:E12, „SELECT E, COUNT(E) group by E”).
Analizând coloana E („Training frecventat”), funcția QUERY a folosit COUNT pentru a număra câte apariții are fiecare valoare („Da” sau „Nu”). Astfel, vedem că șase angajați au urmat trainingul, iar patru nu.
Această formulă poate fi ușor modificată și folosită cu alte funcții Google, cum ar fi SUM.