Acerca de la fórmula QUERY.

Esta es una fórmula exclusiva de Google Sheets (no la encontrarás en Microsoft Excel), y es asombrosa, ya que permite realizar queries (consultas) en una hoja de cálculo (google spreadsheet) usando sentencias en SQL, como si tu hoja de cálculo fuera una base de datos. También es un buen punto de partida para familiarizase con el lenguaje SQL, pues usa una estructura básica. Y hasta puede convertirse en un buen sustituto de una tabla dinámica.

Para poder crear los ejemplos, estoy usando datos del CENACE (Centro Nacional de Control de Energía): precios de nodos distribuidos (https://datos.gob.mx/busca/dataset/precios-de-nodos-distribuidos/resource/0adafacb-2229-46e5-9755-2c2ed865a401). En el archivo de trabajo hay dos páginas básicas: i) raw_data y ii) data. La primera muestra los datos tal cual fueron descargados. La segunda muestra los datos con unos cuantos cambios en el formato y la separación de la fecha en día, mes y año. Consulta aquí el libro de trabajo, y sigue los ejemplos para que puedas conocer el potencial de esta fórmula: https://docs.google.com/spreadsheets/d/12u9_ao-w3dBJwNlekcXvfm_Ei7vw0ydVJxGkzDNOSLI/edit#gid=1195793894.

La fórmula requiere dos argumentos: datos y query. Y así están estructurados todos los ejemplos.

Ejemplo 0. Extraer todos los datos.

Con este query, tomarás todos los datos del origen, y serán llevado al lugar donde has puesto la fórmula. En este caso, los datos están en la hoja “data”, y con esta fórmula los estamos llevando a la hoja “ejemplo0”, con origen en la celda A1. La fórmula queda así:

=QUERY(data!A:J,“SELECT *”)

• Notarás que todos los datos de la hoja de origen fueron llevados a la hoja de destino.
• La sentencia “SELECT *” indica que se deben seleccionar todo los datos de origen.
• Compruébalo aquí: ver archivo.

Ejemplo 1. Extraer las primeras tres columnas.

Con este query, tomarás las primeras tes columnas de los datos del origen, y serán llevado al lugar donde has puesto la fórmula. En este caso, los datos están en la hoja “data”, y con esta fórmula los estamos llevando a la hoja “ejemplo1”, con origen en la celda A1. La fórmula queda así:

=QUERY(data!A:J,“SELECT A, B, C”)

Notarás que todos los datos de la hoja de origen fueron llevados a la hoja de destino. Compruébalo aquí: ver archivo.

Ejemplo 2. Extraer todas las columnas donde la “zona de carga” es “ACAPULCO”

Empezamos con los filtros. Para facilitar las cosas, incluiremos todas las columnas (“select *”), y agregaremos a continuación un filtro a la columna F para encontrar los valores correspondientes a ACAPULCO (“where F = ‘ACAPULCO'”). ¡No olvides las comillas!:

=QUERY(data!A:J,“SELECT * WHERE F = ‘ACAPULCO'”)

Comprueba el resultado aquí: ver archivo.

Ejemplo 3. Extraer todas las columnas donde la “zona de carga” es “ACAPULCO” Y “ZACAPU”

La fórmula es prácticamente la misma, pero agregaremos un operador (OR) al filtro para obtener los resultados coincidentes con el requerimiento:

=QUERY(data!A:J,“SELECT * WHERE (F = ‘ACAPULCO’ OR F = ‘ZACAPU’)”)

Comprueba el resultado aquí: ver archivo.

Ejemplo 4. Extraer todas las columnas donde la “zona de carga” es “ACAPULCO” Y “ZACAPU”, y donde la hora sea menor o igual a 12.

Ahora tenemos un filtro combinado (aplicado a dos atributos/columnas). El primero filtro está aplicado a la columna F (zona de carga); y agregaremos un filtro aplicado a la hora (columna E):

=QUERY(data!A:J,“SELECT * WHERE (F = ‘ACAPULCO’ OR F = ‘ZACAPU’) AND (E <= 12)”)

Comprueba el resultado aquí: ver archivo.

Ejemplo 5. Extraer zona de carga, hora, precio zonal y fecha, donde la “zona de carga” es “ACAPULCO”.

Con este query vamos a extraer solamente algunas columnas, en el orden en que las necesitamos, y aquí también estamos usando filtro (con el comando WHERE):

=QUERY(data!A:J,“SELECT F, E, G, A WHERE (F = ‘ACAPULCO’)”)

Comprueba el resultado aquí: ver archivo.

Ejemplo 6. Extraer zona de carga, hora y precio promedio por hora, donde la “zona de carga” es “ACAPULCO”.

Con este query vamos a extraer solamente algunas columnas, en el orden en que las necesitamos, y aquí también estamos usando filtro (con el comando WHERE). Además de lo anterior, haremos una operación matemática con el precio, calculando el promedio por hora (avg(G)). Es posible hacer operaciones de suma y promedio, pero siempre debes indicar cómo están agrupados los datos. En este bloque, se trata de los precios de la zona de carga “Acapulco” (columna F), y queremos un promedio por hora (columna E).

=QUERY(data!A:J,“SELECT F, E, avg(G) WHERE (F = ‘ACAPULCO’) GROUP BY F, E“)

En automático, la fórmula cambia el encabezado de la tercera columna (donde calculamos el promedio), indicando que se trata del promedio del precio zonal: “avg Precio Zonal ($/MWh)”.

Comprueba el resultado aquí: ver archivo.

Ejemplo 7. Extraer zona de carga, hora y precio promedio por hora, donde la “zona de carga” es “ACAPULCO”. Al final, cambiar nombres de las columnas.

Ahora cambiaremos los encabezados del query. Usaremos el comando LABEL, seguido de la indicación de la columna y el nombre que necesitamos. Por ejemplo, la columna F ahora se llamará “ZONA”, la columna E ahora se llamará “HORA” (con mayúsculas).

=QUERY(data!A:J,“SELECT F, E, avg(G) WHERE (F = ‘ACAPULCO’) GROUP BY F, E LABEL F ‘ZONA’, E ‘HORA’, avg(G) ‘PRECIO PROMEDIO’“)

Comprueba el resultado aquí: ver archivo.

Ejemplo 8. Extraer zona de carga, hora y precio promedio por hora para todas las zonas de carga. Mostrar una columna por cada hora.

Otra de las maravillas de esta fórmula es el comando PIVOT. A partir de un conjunto de datos, el comando pivot despliega y recalcula la información en columnas a partir de un campo de referencia (que se usa como pivote). En este caso, usaremos el campo “HORA” como pivote. El primer cálculo es el promedio por zona, y al agregar el comando PIVOT, los datos se recalculan mostrando el promedio por hora.

=QUERY(data!A:J,“SELECT F, avg(G) GROUP BY F PIVOT E LABEL F ‘ZONA'”)

Comprueba el resultado aquí: ver archivo.

Ejemplo 9. Extraer zona de carga, hora y precio promedio por hora para todas las zonas de carga y por semana. Mostrar una columna por cada hora.

Para mostrar los datos por semana, agregamos el número de semana usando WEEKNUM en la columna K. Y reagrupamos los datos en la fórmula (agregando el número de semana):

=QUERY(data!A:J,“SELECT F, K, avg(G) GROUP BY F, K PIVOT E LABEL F ‘ZONA’, K ‘SEMANA'”)

Como lo puedes notar, puedes agrupar por múltiples columnas (aquí lo hicimos por zona y semana), y además desplegar cálculos basados en otro campo (en este caso, por hora). Comprueba el resultado aquí: ver archivo.

Conoce el potencial de toda la suite de Google, obtén una prueba gratuita de la suite; más información en este enlace (Google): https://goo.gl/ZPXKU2