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.
Hola Juan, muchas gracias por tus explicaciones.
¿Cómo podemos juntar con QUERY() dos columnas de cadena de texto, en una única columna? Gracias!
La finalidad es para usar la nueva columna como filtro de dos condiciones, por ejemplo para un VLOOKUP()
Hola!
Se llama un subquery
Algo asi
QUERY(ArrayFormula(A1:A&B1:B);…
Saludos!
Genial mil gracias!
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
Asi es
Quedaria asi
«WHERE A='»&A1&»‘»)
Saludos!
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
Si se puede. Parecido a como lo hiciste
SELECT A*B LABEL A*B ‘Producto’
Saludos!
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.
Hola!
Claro que si
Para esto usamos la claúsula WHERE
Saludos!
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.
Hola
Lo que pasa es que en Month, enero es 0
Un gran saludo!
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
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!
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);
Hola!
Ponle al final LABEL YEAR(F) ‘Año’
Cuéntame si te funcionó
Saludos!
Muy buena explicación.
Pregunto.
¿Puedo calcular subtotales de un grupo de registros (filas) como lo haría una tabla dinámica?
Saludos
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!