03/29/2024

Cum să utilizați funcția QUERY în Foi de calcul Google

Dacă trebuie să manipulați datele din Foi de calcul Google, funcția QUERY vă poate ajuta! Aduce o căutare puternică, în stilul unei baze de date, în foaia de calcul, astfel încât să puteți căuta și filtra datele în orice format doriți. Vă vom explica cum să îl utilizați.

Folosind funcția 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 tipice QUERY este similar cu SQL și aduce puterea căutărilor în bazele de date în Foi de calcul Google.

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

Argumentul opțional „anteturi” stabilește numărul de rânduri de antet de inclus în partea de sus a intervalului de date. Dacă aveți un antet care se întinde pe două celule, cum ar fi „Primul” în A1 și „Nume” în A2, aceasta ar specifica că QUERY utilizează conținutul primelor două rânduri ca antet combinat.

În exemplul de mai jos, o foaie (numită „Lista de personal”) a unei foi de calcul Google Sheets include o listă de angajați. Include numele lor, numerele de identificare a angajaților, datele de naștere și dacă au participat la sesiunea de formare obligatorie a angajaților.

Pe o a doua foaie, puteți folosi 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 a angajaților, prenumele, numele de familie și dacă au participat la sesiunea de formare.

  Siri nu funcționează pe iPhone sau iPad? 7 probleme rezolvate

Pentru a face acest lucru cu datele afișate mai sus, puteți tasta =QUERY(‘Staff List’!A2:E12, „SELECT A, B, C, E WHERE E = ‘Nu’”). Aceasta interogează datele din intervalul A2 până la E12 pe foaia „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ă a tuturor rândurilor care se potrivesc în care valoarea din coloana E („Presentare frecventată”) 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 le afișa numele și numerele de identificare a angajaților într-o listă separată.

Acest exemplu folosește o gamă foarte specifică de date. Puteți modifica 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 la listă. Formula QUERY pe care ați folosit-o se va actualiza automat ori de câte ori adăugați noi angajați sau când cineva participă la sesiunea de instruire.

Formula corectă pentru aceasta este =QUERY(‘Staff List’!A2:E, „Select 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 la 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 QUERY

Funcția QUERY este versatilă. Vă permite să utilizați alte operații logice (cum ar fi AND și SAU) sau funcții Google (cum ar fi COUNT) ca parte a căutării dvs. De asemenea, puteți utiliza operatori de comparare (mai mare decât, mai mic decât și așa mai departe) pentru a găsi valori între două cifre.

  14 cele mai bune portofele Litecoin din 2023 pentru tranzacții fără probleme

Utilizarea operatorilor de comparație cu QUERY

Puteți utiliza QUERY cu operatori de comparație (cum ar fi mai mic decât, mai mare decât sau egal cu) pentru a restrânge și a filtra datele. Pentru a face acest lucru, vom adăuga o coloană suplimentară (F) la foaia noastră „Lista personalului” cu numărul de premii câștigate de 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(‘Staff List’!A2:F12, „SELECT A, B, C, D, E, F WHERE F > 0”).

Aceasta utilizează un operator de comparație mai mare decât (>) 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 totalul de 11 angajați, trei nu au câștigat niciodată un premiu.

Folosind AND și SAU cu QUERY

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

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

Acest lucru profită și de operatorii de comparație, cum ar fi mai mare sau egal cu (>=) și mai mic sau egal cu (

Formatul acestei formule este =QUERY(‘Staff List’!A2:E12, „SELECT A, B, C, D, E WHERE D >= DATE ‘1980-1-1’ and D

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 SAU pentru a produce rezultate similare. Dacă folosim aceleași date, dar schimbăm datele și folosim SAU, putem exclude toți angajații care s-au născut în anii 1980.

  Cele mai bune filme TCM de retransmis pe HBO Max (iunie 2020)

Formatul acestei formule ar fi =QUERY(‘Staff List’!A2:E12, „SELECT A, B, C, D, E WHERE D >= DATE ‘1989-12-31’ sau D

Din cei 10 angajați inițiali, trei s-au născut în anii 1980. Exemplul de mai sus îi arată pe restul de șapte, care s-au născut înainte sau după datele pe care le-am exclus.

Se utilizează COUNT cu QUERY

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

Pentru a face acest lucru, puteți combina QUERY cu COUNT în felul acesta =QUERY(‘Lista de personal’!A2:E12, „SELECT E, COUNT(E) group by E”).

Concentrându-se pe coloana E („Instruire frecventată”), funcția QUERY a folosit COUNT pentru a număra de câte ori a fost găsit fiecare tip de valoare (un șir de text „Da” sau „Nu”). Din lista noastră, șase angajați au finalizat instruirea, iar patru nu.

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