Cómo utilizar la función QUERY de Google Sheets

Hojas de cálculo de Google CONSULTA es una herramienta muy potente y versátil. Tanto es así que puede considerarse una ventanilla única para todos sus requisitos lógicos, de búsqueda, suma, recuento, promedio, filtrado y ordenación. La función obtiene información específica de un conjunto de datos mediante una sentencia de consulta, de forma muy similar a la obtención de conjuntos de resultados de una base de datos mediante consultas. La sintaxis de la función lenguaje de consulta utilizado en esta función es similar a SQL.

Sintaxis

• datos - es la referencia al rango de celdas sobre el que queremos realizar la consulta.
• consulta - es el texto con el que la función QUERY obtiene la información que buscamos en el conjunto de datos. Como se espera que sea una cadena, debe ir entre comillas. También puede ser una referencia a una celda, donde se almacena el texto de la consulta.
• cabeceras - es un parámetro opcional que indica el número de filas de encabezado en la parte superior de los datos. Si se omite, Google Sheets adivina el valor basándose en el contenido del archivo datos.

Cláusulas y operadores

El lenguaje de consulta utilizado en la función QUERY de Google Sheets es un lenguaje basado en texto similar a SQL. La consulta utiliza cláusulas para realizar acciones. A continuación se muestra una lista de las posibles cláusulas que se pueden utilizar para realizar una amplia variedad de acciones en los datos.

Si utiliza mucho la función QUERY, también se dará cuenta de que hace uso de una serie de operadores lógicos para establecer condiciones dentro de la función. A continuación encontrará una lista de operadores lógicos que puede utilizar.

Funciones básicas de QUERY

En primer lugar, para entender cómo se utiliza la función, considere los siguientes datos de ejemplo. Se trata de información correspondiente a una lista de estudiantes que se han matriculado en diversos cursos de una universidad. En los próximos ejemplos, introduciré la función en la celda H1. También se mostrará en la barra de funciones en las instantáneas.

Ejemplo #1: Seleccionar columnas en función de una condición

Empezaré con una demostración muy básica, y añadiré más complejidad a lo largo del artículo para ayudarte a entender la función paso a paso. Para empezar, usaré la función QUERY para obtener los nombres de los estudiantes que residen en el campus.

Antes de continuar, repasaré algunos de los conceptos básicos de QUERY en este primer ejemplo. Para empezar, observe que los datos seleccionados incluyen los encabezados, que se utilizarán como parte de la salida de la función. Puede ver que la función devolvió no sólo los nombres de los estudiantes que residen en el campus, sino también el encabezado Nombre, ¡que es muy útil para hacer un seguimiento de lo que significan los resultados!

Después de seleccionar los datos, la parte principal de la consulta aparece entre comillas como un comando de cadena de texto. He utilizado dos de las cláusulas enumeradas en el cuadro anterior: Select y Where. El comando ‘select’ se usa para devolver columnas - decirle a la función que “Seleccione A” lleva a una salida de toda la columna A.

La cláusula ‘where’ le permite añadir una condición. Aquí, he añadido la condición de que la columna F sea igual a “Sí”. Para las filas en las que la columna F es “No”, la función ignora esos datos y no los incluye en la salida.

Combinando las dos cláusulas en una sola consulta, he escrito “Select A where F = ‘Yes’”. Se trata básicamente de indicar a la función que devuelva todos los nombres (de la columna A) de los estudiantes que viven en el campus (los datos de la columna F son ‘Sí’).

Consejo: Si desea seleccionar todas las columnas del conjunto de datos, puede escribir Select * para conseguirlo rápidamente en lugar de enumerar cada columna.

Ejemplo #2: Seleccionar columnas en función de una condición

Ahora mostraré el mismo ejemplo, pero encontrando a los estudiantes que NO están en el campus.

La única diferencia es que he cambiado el operador lógico de la cláusula ‘where’ a <>, que significa “no igual”. Así que le pido a la función que me dé la lista de nombres de alumnos que NO tienen un ‘Sí’ en la columna F.

Ejemplo #3: Seleccionar varias columnas

Ahora voy a buscar los nombres, edades y departamentos de los alumnos que han cursado más de 7 asignaturas.

De nuevo, empiezo con la cláusula Select, pero esta vez selecciono varias columnas enumerándolas separadas por comas. He seleccionado las columnas A, B y C. También he cambiado la cláusula Where para filtrar los datos de los alumnos que tienen menos de 7 cursos.

Ejemplo #4: Múltiples condiciones WHERE

Ahora daré un paso más. Obtendré los nombres, departamentos y fechas de incorporación de los estudiantes de 25 años o menos que se hayan incorporado a la universidad entre el 25 de diciembre de 2016 y el 20 de enero de 2017. Tenga en cuenta que, en el texto de la consulta, las fechas siempre tienen que ir con el formato aaaa-mm-dd, entre comillas simples.

Como puede ver, puede añadir múltiples condiciones Where simplemente separándolas con “and”. Esto le permite filtrar datos de cualquier forma que pueda imaginar.

Ejemplo #5: Celdas de referencia

¿Y si necesitas hacer referencia a la fecha desde una celda? No hay problema. Puedes hacerlo con la ayuda de operadores de concatenación y una función de texto. Al hacer esto en el ejemplo siguiente, obtendrá los nombres y las fechas de incorporación de los alumnos que se incorporaron después del 1 de enero de 2017.

Es importante tener en cuenta que las fechas sólo pueden tener el formato aaaa-mm-dd, y deben ir entre comillas simples y precedidas de la cadena “date”. Así, para indicar el 1 de enero de 2020 en la consulta, deberá escribir: date ‘2020-01-01’.

Funciones aritméticas y de agregación

Además de las cláusulas enumeradas anteriormente, puede utilizar funciones aritméticas normales en sus consultas. Operaciones como la suma, la resta, la multiplicación y la división, así como las funciones de agregación suma, media, recuento, máximo y mínimo, son todas ellas válidas.

Ejemplo #6: Multiplicación y suma

Empezaré demostrando algunas funciones aritméticas sencillas: multiplicación y suma. En este ejemplo, quiero que la edad esté en meses en lugar de en años (así que multiplicaré por 12) y quiero sumar un crédito por cada alumno.

Como puede ver, todo lo que tiene que hacer es realizar la operación en la columna de su elección. El resultado no es muy bonito, pero más adelante veremos cómo cambiar las cabeceras y el formato.

Ejemplo #7: Media

Ahora mostraré una operación de agregación: la función promedio. A la media de una columna se accede con la abreviatura ‘avg’. También tienes que decirle a la función qué elementos deben promediarse juntos, es decir, cómo quieres que se agrupen los datos. Por eso también le he dicho a la función que agrupe los datos por la columna C (dept), utilizando la cláusula Group by.

Ya has visto que la función QUERY utiliza funciones aritméticas básicas como la multiplicación y la suma, así como operaciones de agregación como la media.

Funciones QUERY complejas

Ahora que has visto algunos ejemplos básicos utilizando las cláusulas Select y Where para filtrar un conjunto de datos, así como algunas funciones aritméticas, mostraré algunos ejemplos más complejos utilizando una variedad de cláusulas y operaciones.

Ejemplo #8: Seleccionar, Sumar y Agrupar por

Puede utilizar una combinación de Select, Sum, y Group By para listar todos los departamentos y mostrar el número de cursos tomados de cada departamento. La cláusula Agrupar por se utiliza junto con las funciones de agregación (como Suma) para indicar a la función cómo deben agruparse y sumarse los datos; de lo contrario, la función de agregación no funciona.

Ejemplo #9: Etiquetar y clasificar

En el ejemplo anterior, observará que la función QUERY devuelve la segunda columna con el encabezado “suma Cursos”. Honestamente, esto es un poco incómodo - por suerte, puedes arreglarlo renombrándola. Además de eso, también voy a utilizar la segunda columna (ahora renombrada a ‘Cursos Tomados’) para ordenar en orden ascendente. He aquí cómo hacerlo.

Ejemplo #10: Recuento

¿Puede mostrar el número de casos en los que el estudiante está tomando cursos de cada departamento? Por supuesto. La función QUERY también le ofrece esta posibilidad: puede utilizar el operador de recuento para conseguirlo.

En lugar de utilizar el operador Suma para sumar todos los cursos realizados, estoy utilizando el operador Cuenta para contar el número de veces que un estudiante realiza cualquier número de créditos dentro de cada departamento. También estoy utilizando las cláusulas Seleccionar, Agrupar por y Etiquetar para obtener el resultado deseado.

Ejemplo #11: Límite y orden por

La cláusula Limit limita los resultados a un número especificado. Por ejemplo, en la captura de pantalla siguiente limito la salida a 10 filas. Esta cláusula se utiliza a menudo junto con la cláusula Ordenar por, que pone los datos en orden ascendente o descendente.

Aquí, he utilizado la cláusula Order by para ordenar los datos de menor a mayor edad, y luego he utilizado la cláusula Limit para limitar la salida a 10 - dándome los 10 estudiantes más jóvenes.

Ejemplo #12: Pivote

La cláusula Pivot le permite crear sus propias tablas pivotantes utilizando la función QUERY. En el ejemplo siguiente, he tomado un ejemplo anterior y lo he pivotado por la columna A. El resultado es que los nombres de los estudiantes son ahora los encabezados, con los datos que he seleccionado en las filas debajo de cada nombre.

Utilizar la cláusula Pivot es un uso algo más avanzado de las consultas, pero si juegas con ella tú mismo, ¡descubrirás sus múltiples usos!

Uso de ‘OR’ en las consultas

Hasta ahora, has visto muchas consultas que utilizan ‘y’ para añadir criterios adicionales a la consulta. Pero también puedes utilizar ‘o’, al igual que en otras funciones de Google Sheets, como las sentencias IF.

Ejemplo #13: Utilización de OR en una consulta

Este ejemplo es una réplica de un ejemplo anterior, pero en lugar de especificar que la edad debe ser de 25 años o menos Y la fecha de unión debe estar comprendida entre las fechas especificadas, estoy especificando que cualquier punto de datos que cumpla CUALQUIERA de estos criterios, pasará el filtro y aparecerá en la salida.

Cabeceras

Hasta ahora, todos los ejemplos han sido con una sola fila de encabezados en los datos, y un ‘1’ en el parámetro de encabezado dentro de la función QUERY. Ahora, te mostraré qué hacer si tienes encabezados que abarcan varias filas.

Ejemplo #14: Varias filas de cabeceras

En cabecera es una entrada opcional que resulta útil cuando las cabeceras abarcan varias filas. En tales casos, este parámetro le ayuda a combinar fácilmente los encabezados en una sola fila, como se muestra a continuación.

Todo lo que tienes que hacer es poner un ‘2’ como el cabecera después de la consulta. Independientemente del número de filas que abarquen las cabeceras, introduzca ese número en el parámetro cabecera parámetro - ¡así de sencillo!

Consulta de varias pestañas u hojas

¿Qué ocurre cuando los datos están repartidos en varias pestañas de la hoja de cálculo, o incluso en una hoja diferente? Puedes seguir utilizando la función QUERY en esas pestañas y hojas.

Ejemplo #15: Consulta a través de pestañas u hojas

En este ejemplo, puede ver cómo utilizar la función QUERY en varias pestañas. La clave es tener los datos en el mismo formato en todas las hojas - así que en este ejemplo, las edades necesitan estar en la segunda columna en ambas hojas. Entonces, el formato cambia un poco - en lugar de referirse a las columnas por su letra (columna A, B, etc.) necesitará referirse a ellas por su número (Col1, Col2, etc.).

Por último, también tendrás que encerrar los rangos de datos entre llaves {} y separarlos con punto y coma. Las distintas pestañas u hojas se identifican con el nombre de la hoja seguido de un signo de exclamación y, a continuación, el rango de celdas dentro de la hoja. Todo esto tiene más sentido si observas el siguiente ejemplo.

Sin duda, esta es una de las funciones complejas de dominar en Google Sheets. Puede que merezca la pena dedicarle tiempo a dominarla, ya que es sin duda una de las herramientas más potentes de Hojas de cálculo de Google. Te animamos a que sigas explorando la CONSULTA función aquí.

No deje de consultar esta importante entrada del blog sobre Cómo crear tablas dinámicas en Google Sheets.