Campos Calculados en QUERY en Google Sheets

Campos Calculados con Query en Google Sheets

En este artículo veremos como crear nuevas columnas y otros campos calculados con QUERY en Google Sheets y dar nombres a estos campos calculados, y a cualquier otra columna

Aquí pueden ver el video de este tutorial

Como lo vimos en la guía básica de QUERY, esta es una función muy completa, que puede ser tan sencilla o tan compleja como queramos.

Si han trabajado con tablas dinámicas en Sheets o Excel pueden estar familiarizados con el concepto de los campos calculados; básicamente son columnas que no existen y que son el producto de hacer operaciones sobre otras columnas de nuestra base

El concepto es el mismo pero hoy lo vamos a aplicar a nuestras consultas en QUERY dentro de Sheets.

Operaciones básicas con una sola columna

Lo primero que puedo hacer es operaciones básicas como sumas, restas, multiplicaciones y divisiones

Por ejemplo puedo tener una columna de ventas en pesos en mi base, pero quiero ver los valores en dólares, entonces necesito dividir la columna de ventas por mi tasa de cambio (asumamos que es constante y de 4000).

La formula sería:

=QUERY(A1:B;"SELECT A,B,A/4000")

Ahora hagamos lo mismo con los costos. Quedaría así:

=QUERY(A1:B;"SELECT A,B,A/4000,B/4000")

Lo mejor es que no necesito tener las columnas originales. Simplemente puedo dejar los campos calculados, así:

=QUERY(A1:B;"SELECT A/4000,B/4000")

Lo único que no me gusta son esos encabezados que me genera automáticamente QUERY y que son nada amigables.

Vamos a cambiarlos con la ayuda del comando LABEL seguido del campo y seguido nombre que queremos darle al campo. Para las ventas en dólares queremos llamarlo ‘Ventas USD’. Quedaría mi QUERY así:

=QUERY(A1:B;"SELECT A/4000,B/4000 LABEL A/4000 'Ventas USD'")

Fíjense que para Sheets, la columna se llama “A/4000”, entonces cualquier cosa que quiera hacer dentro de mi QUERY, ya sea filtrar, ordenar, limitar, o en este caso renombrar, debe hacerse llamando a “A/4000”.

Hagamos lo mismo ahora para los costos en dólares.

=QUERY(A1:B;"SELECT A/4000,B/4000 LABEL A/4000 'Ventas USD', B/4000 'Costos USD'")

Campos calculados con varias columnas

También puedo usar varias columnas en mis campos calculados.

Por ejemplo si quiero saber la utilidad (olvidémonos de los dólares por ahora), solo tenemos que restar la columna B de la A y renombrar nuestro campo calculado ‘Utilidad’. Quedaría así:

=QUERY(A1:B;"SELECT A-B LABEL A-B 'Utilidad'")

Operaciones más avanzadas con parentesis

Puedo ser un poco más creativo y hacer sumas, divisiones, multiplicaciones y más, ayudándome de los paréntesis.

Por ejemplo podriamos querer ver el margen de utilidad. La formula sería (Ventas-Costos)/Ventas. En mi QUERY podria hacer este campo calculado asi:

=QUERY(A1:B;"SELECT (A-B)/A LABEL (A-B)/A 'Margen'")

¿Vemos todo lo anterior junto en un solo comando? No se vayan a asustar. Lo único que le agregué que no hemos visto es la utilidad en dólares para que todo quede en la misma moneda.

=QUERY(A1:B;"SELECT A/4000,B/4000,(A-B)/4000,(A-B)/A LABEL A/4000 'Ventas USD', B/4000 'Costos USD', (A-B)/4000 'Utilidad USD',(A-B)/A 'Margen'")

El formato bonito se los dejo a ustedes.

Entonces fíjense que no tuve que modificar nada de mi base original para hacer cálculos adicionales y resumirlo.

Esto lo podemos llevar un paso mas alla y usar todas las funcionalidades de QUERY: filtrar, ordenar, limitar, agrupar, etc.

Agrupar por fechas

Aparte de estas fórmulas “manuales” que podemos hacer con nuestras columnas, también tenemos unas pocas funciones que podemos usar específicamente con fechas.

Así como en una tabla dinámica puedo agrupar un campo de fecha por año, mes o día, puedo hacer lo mismo con QUERY.

Esto me evita tener que crear una columna adicional solo para año, mes o día

Le he agregado una columna de fecha a mis datos

Si hago un QUERY básico, me mostrara las fechas individuales, pero si uso los campos calculados, después me hará la vida más fácil cuando quiera agrupar, filtrar u ordenar por año o por mes. Quedaría así:

=QUERY(A1:C;"SELECT YEAR(A),MONTH(A),B,C LABEL YEAR(A) 'AÑO', MONTH(A) 'MES'")

Conclusión

Cada vez vamos aprendiendo más de QUERY. Poco a poco vamos viendo su verdadero potencial. Si tienen dudas adicionales de como hacer algo específico, cuéntenme en los comentarios.

26 comentarios en “Campos Calculados en QUERY en Google Sheets”

  1. Hola, tengo varias hojas que estoy importando en una sola y a su vez me estoy trayendo solo algunos registros de esas hojas.. y por ejemplo si la columna A tiene un valor X .. en la primera columna me traigo el valor de la columna Y, sino en la primera columna me traigo el valor de la columna Z.
    Para eso estoy usando Query y me funciona perfecto. El problema que tengo es quiero como segunda columna traer el valor de dos columnas de las otras planillas, es decir concatenar el resultado de la columna M y N pero como estoy dentro de la función query, no me está dejando.. no me funciona el concat ni ninguna otra función. Cómo puedo hacer?

    1. Hola! Es un poco mas enredado, porque no lo puedes hacer dentro de la consulta del QUERY, sino en la parte de los datos (Primer argumento)
      Te toca hacer el CONCAT en una matriz aparte usando arrayformula (o MAP + LAMBDA)
      En otras palabras te toca traer 2 o 3 importranges diferentes, asi:
      =QUERY({IMPORTRANGE(Primera columna)\ArrayFormula(CONCAT(IMPORTRANGE(2 y 3))\IMPORTRANGE(el resto de columnas)};Consulta)
      Saludos!

  2. Hola, Como creo una nueva columna con un único valor, por ejemplo la columna mes y que en los datos que arroja el query aparezca Enero (esto no está en la data original).

    1. No se puede con QUERY hasta onde yo se, te tocaria usar una columna auxiliar desde la construcción de la tabla argumento QUERY. Algo asi
      =QUERy(ArrayFormula(TEXTO(“Datos!F2:F”;”mmmm”));”SELECT Col1″)
      Saludos!

  3. HOla como puedo aplicar suma producto a dos columnas? porque si pongo SUM(Col1)*SUM(Col2), suma las columnas y el resultado los multiplica, no como actua SUMAPRODUCTO, gracias!

  4. Hola Juan, muchas gracias por tus explicaciones.

    ¿Cómo podemos juntar con QUERY() dos columnas de cadena de texto, en una única columna? Gracias!

  5. Una consulta, en el WHERE se puede indicar el valor que está en otra celda? Es decir que en lugar de poner por ejemplo WHERE A=2019 pueda poner algo como WHERE A=J5

  6. Hola:
    Considerando el valor en el mes la consulta no coincide;

    Ejemplo: Mes 2 según tabla es 911.600 (Ventas-18/02/2019), en la consulta aparece en el mes 1 (enero) 911.600.

    Quisiera saber cual seria el motivo. Gracias.

  7. Hola Juan, gracias
    Estoy agregando a mi Query lo del filtro año pero en la fila debajo del titulo se pone year(), como se puede evitar eso y que solo inicie poniendo el año en la columna
    SI(Y(C2=””;C3=””;G2=”Fecha Z-A”);QUERY(‘BD Cambio’!A5:G;”Select A,B,C,D,F, YEAR(F) ORDER BY G DESC”;0);

  8. Muy buena explicación.

    Pregunto.

    ¿Puedo calcular subtotales de un grupo de registros (filas) como lo haría una tabla dinámica?

    Saludos

    1. Hola Julian

      Desafortunadamente no hay una opción para totales o subtotales.

      El total lo podemos hacer con un truco que involucra arreglos, pero para el subtotal si tendría que concatenar multiples queries o usar funciones instaladas como SUPERSQL, que lo explicaré en otro video o artículo.

      Un gran saludo!

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *

Este sitio usa Akismet para reducir el spam. Aprende cómo se procesan los datos de tus comentarios.

Pin It on Pinterest

Share This