La funció QUERY us permet extreure informació d'un rang o full de dades sencer mitjançant ordres de consulta flexibles. Aprendre a utilitzar la funció de CONSULTA de Fulls de càlcul de Google us permet accedir a una potent eina de cerca.
Si alguna vegada heu escrit consultes SQL per treure dades d'una base de dades, reconeixeu la funció QUERY. Si no teniu experiència amb bases de dades, la funció QUERY és encara molt fàcil d'aprendre.
Què és la funció QUERY?
La funció té tres paràmetres principals:
=QUERY(dades, consulta, capçaleres)
Aquests paràmetres són bastant senzills.
- Data: l'interval de cel·les que contenen les dades d'origen
- Query: una declaració de cerca que descriu com extreure el que voleu de les dades d'origen
- Headers: un argument opcional que us permet combinar diverses capçaleres a l'interval d'origen en una sola capçalera al full de destinació
La flexibilitat i el poder de la funció QUERY prové de l'argument Consulta, com veureu a continuació.
Com crear una fórmula de consulta senzilla
La fórmula QUERY és especialment útil quan teniu un conjunt de dades molt gran del qual necessiteu extreure i filtrar dades.
Els exemples següents utilitzen les estadístiques de rendiment de l'escola secundària SAT dels EUA. En aquest primer exemple, aprendràs a escriure una fórmula de CONSULTA senzilla que retorni totes les escoles secundàries i les seves dades on hi ha "Nova York" al nom de l'escola.
-
Creeu un full nou per col·locar els resultats de la consulta. A la cel·la superior esquerra, escriviu =Consulta (. Quan feu això, veureu una finestra emergent amb arguments de necessitat, un exemple i informació útil sobre la funció.
Image -
A continuació, suposant que teniu les dades d'origen al Full 1, ompliu la funció de la següent manera:
=Consulta(Full1!A1:F460, "SELECCIONA B, C, D, E, F ON B LIKE '%New York%'")
Aquesta fórmula inclou els arguments següents:
- Rang de cel·les: l'interval de dades d'A1 a F460 a Sheet1
- Declaració SELECT: una instrucció SELECT que demana qualsevol dada a les columnes B, C, D, E i F on la columna B conté text que conté la paraula "Nova York". " en ell.
Image El caràcter "%" és un comodí que podeu utilitzar per cercar parts de cadenes o números en qualsevol conjunt de dades. Si deixeu "%" de descompte al davant de la cadena, es tornaria qualsevol nom de l'escola que comenci amb el text "Nova York".
-
Si voleu trobar el nom d'una escola exacta a la llista, podeu escriure la consulta:
=Consulta(Full1!A1:F460, "SELECT B, C, D, E, F WHERE B='New York Harbor High School'")
S'utilitza l'operador =per trobar una coincidència exacta i es pot utilitzar per trobar text o números coincidents a qualsevol columna.
Image
Com que la funció de consulta de fulls de càlcul de Google és molt fàcil d'entendre i d'utilitzar, podeu extreure qualsevol dada de qualsevol conjunt de dades gran mitjançant declaracions de consulta senzilles com les anteriors.
Utilitzar la funció QUERY amb un operador de comparació
Els operadors de comparació us permeten utilitzar la funció QUERY per filtrar les dades que no compleixen cap condició.
Tens accés a tots els operadors següents en una funció QUERY:
- =: els valors coincideixen amb el valor de cerca
- <: els valors són inferiors al valor de cerca
- >: els valors són superiors al valor de cerca
- <=: els valors són inferiors o iguals al valor de cerca
- >=: els valors són superiors o iguals al valor de cerca
- i !=: el valor de cerca i els valors d'origen no són iguals
Utilitzant el mateix conjunt de dades d'exemple SAT anterior, fem una ullada a com veure quines escoles tenien una mitjana matemàtica mitjana superior als 500 punts.
-
A la cel·la superior esquerra d'un full en blanc, empleneu la funció CONSULTA de la manera següent:
=Consulta(Full1!A1:F460, "SELECT B, C, D, E, F ON E > 500")
Aquesta fórmula demana qualsevol dada on la columna E contingui un valor que sigui superior a 500.
Image -
També podeu incloure operadors lògics com AND i OR per cercar diverses condicions. Per exemple, per obtenir puntuacions només per a escoles amb més de 600 participants i una mitjana de lectura crítica entre 400 i 600, hauríeu d'escriure la següent funció de CONSULTA:
=Consulta(Full1!A1:F460, "SELECT B, C, D, E, F ON C > 600 I D > 400 I D < 600")
Image -
Els operadors lògics i de comparació us ofereixen moltes maneres diferents d'extraure dades d'un full de càlcul d'origen. Us permeten filtrar informació important fins i tot a partir de conjunts de dades molt grans.
Usos avançats de la funció QUERY
Hi ha algunes altres funcions que podeu afegir a la funció QUERY amb algunes ordres addicionals. Aquestes ordres us permeten agregar valors, comptar valors, demanar dades i trobar valors màxims.
-
L'ús de GROUP en una funció QUERY us permet agregar valors en diverses files. Per exemple, podeu fer la mitjana de les notes de cada estudiant mitjançant la funció GRUP. Per fer-ho, escriviu:
=Consulta(Full1!A1:B24, "SELECCIONA A, AGRUPAR AVG(B) PER A")
Image -
Si feu servir COUNT en una funció QUERY, podeu comptar el nombre d'escoles amb una puntuació mitjana d'escriptura superior a 500 mitjançant la següent funció QUERY:
=CONSULTA(Full1!A2:F460, "SELECCIONA B, COUNT (F) GRUP PER B")
Image -
Feu servir ORDER BY en una funció de CONSULTA, podeu trobar escoles amb puntuacions mitjanes matemàtiques màximes i ordenar la llista per aquestes puntuacions.
=CONSULTA(Full1!A2:F460, "SELECCIONA B, MAX (E) GRUP PER B ORDENA PER MAX(E)")
Image
