Campos Calculados en QUERY en Google Sheets

Campos Calculados con Query en Google Sheets
— by

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.

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

,

Boletín

Nuestras últimas noticias en tu correo electrónico

Respuestas

  1. Avatar de Antonio
    Antonio

    Hola. Es posible poner la multiplicación de dos columnas?
    Algo así?
    »SELECT A,B, (A*B)»
    Cuál sería la sintaxis si fuese posible?
    Muchas gracias

    1. Avatar de jsguzmanb
      jsguzmanb

      Si se puede. Parecido a como lo hiciste
      SELECT A*B LABEL A*B ‘Producto’
      Saludos!

  2. Avatar de Alex Ipanaque
    Alex Ipanaque

    Hola Juan,
    Se pueden poner condicionales en el Query? por Ejemplo:

    = QUERY (Data!A1:D,»SELECT A,B,C,SI(D=»,0,D)»)

    Muchas Gracias.

    1. Avatar de jsguzmanb
      jsguzmanb

      Hola!

      Claro que si

      Para esto usamos la claúsula WHERE

      Saludos!

  3. Avatar de Jorge Villalba
    Jorge Villalba

    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.

    1. Avatar de jsguzmanb
      jsguzmanb

      Hola

      Lo que pasa es que en Month, enero es 0

      Un gran saludo!

  4. Avatar de Kevin
    Kevin

    Hola, sabes si es posible mostrar por ejemplo el ultimo campo registrado según fecha con query?

    Muy buenos tus videos, y tu sitio.

    Saludos

    1. Avatar de jsguzmanb
      jsguzmanb

      Hola Kevin

      Mil gracias!

      Para esto tendrias que usar al final del query, el comando ORDER BY F DESC

      Donde F es la columna donde esta la fecha

      Ojalá te sirva

      Saludos!

  5. Avatar de MAlexRM
    MAlexRM

    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);

    1. Avatar de jsguzmanb
      jsguzmanb

      Hola!

      Ponle al final LABEL YEAR(F) ‘Año’

      Cuéntame si te funcionó

      Saludos!

  6. Avatar de Julian Ojeda
    Julian Ojeda

    Muy buena explicación.

    Pregunto.

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

    Saludos

    1. Avatar de jsguzmanb
      jsguzmanb

      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 una respuesta

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