Una de las preguntas más frecuentes relacionadas a Google Sheets es como hacer para generar informes o filtros con una fórmula.
La respuesta puede estar en QUERY, una de las funciones más poderosas y versátiles que nos ofrece Google Sheets.
Que es la función QUERY
QUERY es una función exclusiva de Google Sheets que filtra y resume de la manera que queramos, la información de alguna (o varias) de nuestras hojas de cálculo.
Query en español significa consulta, y esta basada en la función QUERY del lenguaje MySQL, el cual se usa para manejar bases de datos.
Por eso, cuando te enfrentes por primera vez a esta función, si nunca has tenido acercamientos con MySQL, se va a sentir raro. Es totalmente distinta a cualquier otra función de Google Sheets o Excel.
Es una función muy poderosa y muy flexible que permite hacer muchas cosas, pero por ahora veremos sus versiones mas básicas.
En la academia de Automatización JuanSGuzmán encontrarás un curso paso a paso del uso de la función QUERY, así como un curso avanzado de la misma.
Ejemplo de la función QUERY
Digamos que tenemos la siguiente fuente de datos:
En resumen, es una serie de registros que cuenta, entre otros, con los siguientes campos:
- Segmento
- País
- Producto
- Ventas
- Costo
- Utilidad
- Fecha
Ahora quiero poder mostrar en un informe solo algunos de estos datos. Por ejemplo me gustaría ver el país, el producto y las ventas.
Pero adicionalmente podría querer ver estos campos solo para una o dos categorias específicas
Y por ultimo, me gustaría poder ordenarla por ventas de mayor a menor.
Y de pronto me gustaria solo ver los campos con las 5 mayores ventas.
También podría querer ordenarlo por fechas.
Todo esto lo puedo hacer con una sola función: QUERY.
En este primer ejemplo queremos llegar a lo siguiente:
Es decir, vamos a ver solo 4 campos: Segmento, País, Producto y Ventas.
Adicionalmente vamos a filtrar los campos que tengan el segmento «Gobierno» y el país «Alemania»
La función se vería así:
=QUERY(Datos!A1:M701;»SELECT A,B,C,H WHERE A=’Gobierno’ AND B=’Alemania’»;1)
Pero no te asustes, ya veremos esta función en detalle.
Función QUERY básica
Empecemos por la forma más básica de QUERY. Sería así:
=QUERY(«Datos!A1:M701;»SELECT *»;1)
Entonces, la función tiene tres componentes, así:
=QUERY(Fuente de datos, Consulta, Encabezados)
Veamos las tres partes en detalle
Fuente de datos
La fuente de datos es nuestra tabla de origen, la cual puede estar en la misma hoja, en otra hoja e inclusive en otro archivo. Hasta podría «construirla» con varias fuentes con la ayuda de la función arreglo {}, pero eso lo dejamos para otro día.
Puede ser un rango directo como A1:L20, un rango infinito como A1:L, un intervalo con nombre o una fórmula que nos arroje un rango.
Encabezados
Voy en desorden, porque dejo la mas importante de ultima.
Encabezados simplemente me dice si traigo o no encabezados.
Normalmente es 0 o 1, donde 0 es que no traiga los encabezados de la tabla de origen, y 1 es que traiga el encabezado.
Esto dependerá de cada caso específico. En nuestro caso, como la fuente de datos tiene encabezados y además queremos traerlo, podríamos «1».
También la podríamos dejar vacía.
Consulta
Este es el corazón de la función QUERY y es la parte más complicada de entender, pero la que, después de trabajarla unas cuantas veces, dominaremos y nos dará el poder y la flexibilidad de esta función. Por eso dediquémosle una sección entera:
Consultas en la función QUERY
Consulta general
Si vemos el ejemplo anterior, la consulta (que siempre va en comillas) es:
«SELECT *», que me arroja:
Vamos a ir «traduciendo» este lenguaje SQL poco a poco.
«SELECT *» quiere decir «Seleccione todo».
Es la forma más general de QUERY, y lo que hace básicamente es copiar la fuente de origen. Crea un duplicado exacto.
Personalmente, siempre que construyo una función QUERY empiezo con «SELECT *» y poco a poco voy agregando condiciones adicionales.
También podemos dejar la consulta vacía, y sería el equivalente a «SELECT *».
Así quedaría nuestra función:
=QUERY(Datos!A1:M700)
Filtrando columnas
Lo primero que podemos hacer es «decirle» a QUERY que solo nos muestre ciertas columnas
En nuestro ejemplo podemos entonces mejorar nuestra condición de la siguiente manera:
«SELECT A,B,F»
Entonces, sin ningún comando adicional, le estamos diciendo «Solo muestre las columnas A, B y F».
Tengan en cuenta que el orden en que escriba las columnas es el orden en que las va a mostrar.
Esto me trae otra ventaja de QUERY y es que puedo reordenar mis columnas como quiera. Por ejemplo podría poner:
«SELECT F,A,D,B»
Lo que si no me permite es repetir columnas
Una Condición
Después de filtrar las columnas, puedo dar mi siguiente instrucción y es filtrar por alguna o varias condiciones de las filas.
Es importante observar que la columna de la condición no tiene que involucrar necesariamente una de las columnas que se muestre.
Por ahora no incluyamos las columnas, y solo mostremos todas las columnas, concentrándonos en las condiciones, volviendo a nuestro leal asterisco «*».
Nuestra primera condición va a ser un país.
Quiero ver todos los campos cuyo país sea Canadá. Nuestro primer ejemplo entonces va a ser:
«SELECT * WHERE B=’Canadá’»
Nota: Si vas a mostrar todas las columnas, puedes omitir el comando SELECT. Quedaría así la consulta: «WHERE B=’Canadá»
Listemos entonces ciertas características de las condiciones
- Para estas condiciones siempre vamos a usar el comando «WHERE».
- Vamos a proporcionar la columna donde queremos buscar la condición.
- Después vamos con un operador de comparación, en este caso «=»
- Por último damos la condición, la cual debe estar entre comillas sencillas.
Así como pusimos B=’Canadá’ podríamos poner C=’Carretera’ o A=’Gobierno’
Varias condiciones
Aquí empezamos a ver el gran potencial de QUERY, y es que puedo poner todas las condiciones que quiera.
Digamos que ahora quiero ver los registros del país «Alemania» y del producto «Montana».
A la construcción actual tengo que agregarle el comando «AND» que me junta todas las condiciones. El ejemplo entonces sería:
«SELECT * WHERE B=’Alemania’ AND C=’Montana’»
Por último combinemos los filtros de columnas y de filas en una sola. Sería algo así:
=QUERY(Datos!A1:M701;»SELECT A,B,C,H WHERE B=’Alemania’ AND C=’Montana’»;1)
En la academia de Automatización JuanSGuzmán encontrarás un curso paso a paso del uso de la función QUERY, así como un curso avanzado de la misma.
Ordenar
Para terminar esta introducción de Query, donde vemos sus funcionalidades como una especie de «filtro dinámico», vamos a ordenar nuestros datos, con la ayuda del comando ORDER BY.
A diferencia de WHERE, ORDER BY tiene dos argumentos: la columna que voy a ordenar y si va a ser de menor a mayor (ASC) o de mayor a menor (DESC)
Este comando siempre debe ir después de WHERE. En otras palabras: primero filtro y después ordeno.
Aunque puede darse el caso que no filtre y solo ordene.
En nuestro ejemplo, una vez filtrado por país y producto, ahora voy a ordenar por segmento de mayor a menor:
=QUERY(Datos!A1:M;"SELECT * WHERE B='Alemania' AND C='Montana' ORDER BY A DESC";1)
Limitar
Una última cosa que puedo hacer es limitar la cantidad de datos que muestre mi Query. Puedo por ejemplo mostrar solo 5, 10 o 26 resultados. Lo que yo quiera.
Para esto voy a usar el comando LIMIT, despues de WHERE y de ORDER BY.
Otra vez, podría no usar WHERE, ni ORDER BY y simplemente mostrar mi base maestra, pero solo los primeros 10 items, con el comando
LIMIT 10
Entonces si en nuestro ejemplo quisieramos filtar por pais y producto, ordenando por segmento y mostrando los primeros 15 resultados, nuestro código se vería así:
=QUERY(Datos!A1:M;"SELECT * WHERE B='Alemania' AND C='Montana' ORDER BY A DESC LIMIT 15";1)
Siguientes pasos
Esto es solo el abrebocas a lo que puede hacer QUERY.
Entre las cosas que nos quedan por explorar estan:
- Dar nuevos nombres a las columnas
- Campos calculados
- Agrupar por filas
- Agrupar por columnas
- No mostrar ciertas filas
- Trabajar con fechas
- Hacer «Subqueries»
- Hacer queries dinámicas, con filtros del usuario.
Todo esto lo iremos viendo en artículos posteriores.
Conclusión
Hasta ahora estamos rozando la superficie de lo que podemos hacer con la función QUERY, pero sinceramente creo que es una función a la que debemos ir acercándonos poco a poco hasta dominarla.
Por ahora te invito a practicar con alguna de tus bases, para así, ir familiarizándote con esta magnífica función.
Suscríbete para acceder al curso «Descubriendo el poder de Google Sheets» y empezar tu camino a la maestría de Sheets, Forms y Apps Script
Deja una respuesta