¿Estás buscando una manera efectiva de organizar y analizar tus datos en Google Sheets? ¡Entonces estás en el lugar correcto! En este artículo, te enseñaremos cómo utilizar la función QUERY de Google Sheets, una poderosa herramienta que te permitirá extraer y filtrar información de manera fácil y rápida. También exploraremos las cláusulas estándar y te presentaremos una alternativa a la función QUERY que puede resultarte útil. ¿Estás listo para descubrir cómo sacarle el máximo provecho a Google Sheets? ¡Sigue leyendo!
Si ha estado siguiendo este blog por un tiempo, es posible que recuerde la función CONSULTA para Google Sheets. Lo mencioné como una posible solución para un par de casos. Pero eso está lejos de ser suficiente para descubrir todo su potencial. Hoy ya es hora de que conozcamos adecuadamente a este superhéroe de las hojas de cálculo. Y adivina qué: también habrá una herramienta igualmente notable 🙂
¿Sabías que la función CONSULTA de Google Sheets se considera la más poderosa en hojas de cálculo? Su peculiar sintaxis favorece decenas de operaciones diferentes. Intentemos descomponer sus partes para aprenderlas de una vez por todas, ¿vale?
Cláusulas utilizadas en las fórmulas de CONSULTA de Google Sheets
El lenguaje de consulta consta de 10 cláusulas. Pueden asustar a primera vista, especialmente si no estás familiarizado con SQL. Pero te prometo que una vez que los conozcas, tendrás a tu disposición una poderosa arma de hoja de cálculo.
Voy a cubrir cada cláusula y proporcionaré ejemplos de fórmulas usando esta lista de estudiantes imaginarios y sus materias en papel:
Sí, soy uno de esos bichos raros que piensan que Plutón debería ser un planeta 🙂
Consejo. Se pueden utilizar varias cláusulas dentro de una función CONSULTA de Google Sheets. Si los anida todos, asegúrese de seguir el orden de aparición en este artículo.
Seleccionar (todas o columnas específicas)
La primera cláusula – seleccionar – se utiliza para indicar qué columnas necesita devolver con la CONSULTA de Google Sheets desde otra hoja o tabla.
Ejemplo 1. Seleccionar todas las columnas
Para recuperar todas y cada una de las columnas, utilice seleccionar con un asterisco – seleccionar *
=QUERY(Papers!A1:G11,"select *")
Consejo. Si omites el seleccionar parámetro, Google Sheets QUERY devolverá todas las columnas de forma predeterminada:
=QUERY(Papers!A1:G11)
Ejemplo 2. Seleccionar columnas específicas
Para extraer solo ciertas columnas, enumerelas después del seleccionar cláusula:
=QUERY(Papers!A1:G11, "select A,B,C")
Consejo. Las columnas de interés se copiarán en el mismo orden en que las menciona en la fórmula:
=QUERY(Papers!A1:G11, "select C,B,A")
CONSULTA de Google Sheets: cláusula Where
Consulta de Hojas de cálculo de Google dónde se utiliza para establecer las condiciones de los datos que desea obtener. En otras palabras, actúa como un filtro.
Si utiliza esta cláusula, la función CONSULTA de Google Sheets buscará en las columnas valores que cumplan con sus condiciones y le devolverá todas las coincidencias.
Consejo. Dónde puede funcionar sin el seleccionar cláusula.
Como es habitual, para concretar condiciones, existen conjuntos de condiciones especiales. operadores para ti:
- operadores de comparación simples (para valores numéricos): =, <>, >, >=, <, <=
- operadores de comparación complejos (para cuerdas): contiene, comienza con, termina con, coincide, != (no coincide / no es igual a), como.
- operadores lógicos para combinar varias condiciones: y, o, no.
- operadores para en blanco / no vacío: es nulo, no es nulo.
Consejo. Si está molesto o preocupado por tener que volver a tratar con una cantidad tan grande de operadores, lo sentimos. Nuestro Múltiples coincidencias de Vlookup encontrará todas las coincidencias y creará fórmulas de CONSULTA en Google Sheets si es necesario.
Veamos cómo se comportan estos operadores en fórmulas.
Ejemplo 1. Dónde con numeros
añadiré dónde a mi CONSULTA de Google Sheets desde arriba para obtener información sobre aquellos planetas que tienen más de 10 lunas:
=QUERY(Papers!A1:G11,"select A,B,C,F where F>=10")
Consejo. También mencioné la columna F para buscar solo para asegurarme de que se cumpla el criterio. Pero es completamente opcional. No es necesario incluir columnas con condiciones en el resultado:
=QUERY(Papers!A1:G11,"select A,B,C where F>=10")
Ejemplo 2. Dónde con cadenas de texto
- Quiero ver todas las filas donde está la calificación. F o F+. usaré el contiene operador para eso:
=QUERY(Papers!A1:G11,"select A,B,C,G where G contains 'F'")
Nota. No olvides rodear tu texto entre comillas.
- Para obtener todas las filas con F solo, solo reemplaza contiene con signo igual (=):
=QUERY(Papers!A1:G11,"select A,B,C,G where G='F'") - Para consultar los trabajos que aún están por entregar (donde falta la calificación), marque la columna GRAMO para espacios en blanco:
=QUERY(Papers!A1:G11,"select A,B,C,G where G is null'")
Ejemplo 3. Dónde con fechas
Adivina qué: ¡Google Sheets QUERY incluso ha logrado controlar las fechas!
Dado que las hojas de cálculo almacenan fechas como números de serie, normalmente hay que recurrir a la ayuda de funciones especiales como FECHA o VALORFECHA, AÑO, MES, HORA, etc.
Pero QUERY ha encontrado su camino entre las fechas. Para ingresarlos correctamente, simplemente escriba la palabra fecha y luego agregue la fecha con el formato aaaa-mm-dd: fecha ‘2022-01-01’
Aquí está mi fórmula para obtener todas las filas con una fecha de discurso antes del 1 de enero de 2022:
=QUERY(Papers!A1:G11,"select A,B,C where B<date '2022-01-01'")
Ejemplo 4. Combina varias condiciones
Para utilizar un determinado período de tiempo como criterio, deberá combinar dos condiciones.
Intentemos recuperar los artículos que se entregaron en otoño de 2019. El primer criterio debe ser una fecha. a partir del 1 de septiembre de 2021el segundo – en o antes del 30 de noviembre de 2021:
=QUERY(Papers!A1:G11,"select A,B,C where B>=date '2021-09-01' and B<=date '2021-11-30'")
O puedo seleccionar artículos según estos parámetros:
- antes del 31 de diciembre de 2021 (B
) - tener A o A+ como calificación (G contiene ‘A’)
- o B/B+ (G contiene ‘B’)
=QUERY(Papers!A1:G11,"select A,B,C,G where B<date '2021-12-31' and G contains 'A' or G contains 'B'")
Consejo. Si tu cabeza ya está a punto de explotar, no te rindas todavía. Existe una herramienta que es perfectamente capaz de construir todas estas fórmulas por ti, sin importar el número de criterios. Salta directamente a el final del artículo para conocerlo.
CONSULTA de Hojas de cálculo de Google – Agrupar por
Consulta de Hojas de cálculo de Google agrupar por El comando se utiliza para concatenar filas. Sin embargo, debe utilizar algunas funciones agregadas para poder resumirlas.
Nota. Agrupar por siempre debe seguir el seleccionar cláusula.
Lamentablemente, no hay nada que agrupar en mi tabla ya que no hay valores recurrentes. Así que déjame ajustarlo un poco.
Supongamos que todos los trabajos deben ser preparados por solo 3 estudiantes. Puedo encontrar la calificación más alta que obtuvo cada estudiante. Pero como son letras, es la función MIN la que debo aplicar a la columna G:
=QUERY(Papers!A1:G11,"select A,min(G) group by A")
Nota. Si no utiliza una función agregada con ninguna columna en el seleccionar cláusula (columna A en mi ejemplo), debes duplicarlos todos en el agrupar por cláusula.
CONSULTA de Hojas de cálculo de Google – Pivote
Consulta de Hojas de cálculo de Google pivote La cláusula funciona al revés, si se me permite decirlo. Transpone datos de una columna a una fila con nuevas columnas, agrupando otros valores en consecuencia.
Para aquellos que estén lidiando con citas, puede ser un verdadero descubrimiento. Podrá echar un vistazo rápido a todos los años distintos en esa columna de origen.
Nota. Cuando se trata de pivotecada columna utilizada en el seleccionar La cláusula debe cubrirse con una función agregada. De lo contrario, debe mencionarse en el agrupar por comando siguiendo tu pivote.
Recuerde, mi tabla ahora menciona solo 3 estudiantes. Voy a hacer que la función me diga cuántos informes realizó cada alumno:
=QUERY(Papers!A1:G11,"select count(G) pivot A")
CONSULTA de Hojas de cálculo de Google – Ordenar por
Éste es bastante fácil 🙂 Se utiliza para ordenar el resultado por los valores en ciertas columnas.
Consejo. Todas las cláusulas anteriores son opcionales al utilizar ordenar por. yo suelo seleccionar para devolver menos columnas con fines de demostración.
Volvamos a mi tabla original y ordenemos los informes por fecha del discurso.
Esta próxima fórmula de CONSULTA de Google Sheets me dará las columnas A, B y C, pero al mismo tiempo las ordenará por fecha en la columna B:
=QUERY(Papers!A1:G11,"select A,B,C order by B")
Límite
¿Qué pasaría si te dijera que no tienes que incluir todas y cada una de las filas en el resultado? ¿Qué pasaría si te dijera que Google Sheets QUERY solo puede extraer una cierta cantidad de las primeras coincidencias que encuentra?
Bueno el límite La cláusula está diseñada para ayudarle con eso. Limita el número de filas a devolver por el número dado.
Consejo. Siéntete libre de usar límite sin otras cláusulas previas.
Esta fórmula mostrará las primeras 5 filas donde la columna con calificaciones contiene una marca (no está vacía):
=QUERY(Papers!A1:G11,"select A,B,C,G where G is not null limit 5")
Compensar
Esta cláusula es algo opuesta a la anterior. Mientras límite le obtiene el número de filas que especifica, compensar los salta, recuperando el resto.
Consejo. Compensar Tampoco requiere ninguna otra cláusula.
=QUERY(Papers!A1:G11,"select A,B,C,G where G is not null offset 5")
Si intentas usar ambos límite y compensarsucederá lo siguiente:
- Compensar saltará filas al principio.
- Límite devolverá un número de las siguientes filas.
=QUERY(Papers!A1:G11,"select A,B,C,G where G is not null limit 3 offset 3")
De 11 filas de datos (la primera es un encabezado y la función CONSULTA en Google Sheets hace un buen trabajo al comprenderlo), el desplazamiento omite las primeras 3 filas. El límite devuelve las 3 filas siguientes (a partir de la cuarta):
CONSULTA de Hojas de cálculo de Google – Etiqueta
Consulta de Hojas de cálculo de Google etiqueta El comando le permite cambiar los nombres de los encabezados de las columnas.
Consejo. Otras cláusulas son opcionales para etiqueta también.
Pon el etiqueta primero, seguido del ID de la columna y un nuevo nombre. Si cambia el nombre de algunas columnas, separe cada nuevo par de etiqueta de columna con una coma:
=QUERY(Papers!A1:G11,"select A,B,C label A 'Name', B 'Date'")
Formato
El formato La cláusula permite modificar el formato de todos los valores de una columna. Para ello, necesitará un patrón detrás del formato deseado.
Consejo. La cláusula de formato también puede reproducirse sola en la CONSULTA de Google Sheets.
=QUERY(Papers!A1:G11,"select A,B,C limit 3 format B 'mm-dd, yyyy, ddd'")
Consejo. Mencioné algunos formatos de fecha para Google Sheets QUERY en esta publicación de blog. Se pueden tomar otros formatos directamente de las hojas de cálculo: Formato > Número > Más formatos > Formato de número personalizado.
Opciones
Éste se utiliza para establecer algunas configuraciones adicionales para los datos de resultados.
Por ejemplo, un comando como sin_valores devolverá celdas formateadas únicamente.
La forma más rápida de crear fórmulas de CONSULTA: múltiples coincidencias de Vlookup
Por poderosa que sea la función CONSULTA en Google Sheets, puede requerir una curva de aprendizaje para dominarla. Una cosa es ilustrar cada cláusula por separado en una tabla pequeña y otra completamente distinta intentar construir todo correctamente con unas pocas cláusulas y una tabla mucho más grande.
Es por eso que decidimos vestir Google Sheets QUERY con una interfaz fácil de usar y convertirlo en un complemento.
Por qué Múltiples coincidencias de BUSCARV ¿Es mejor que las fórmulas?
Bueno, con el complemento hay absolutamente no hay necesidad de:
- averiguar cualquier cosa sobre esos clausulas. Es realmente fácil crear muchas condiciones complejas en el complemento: tantas como necesites a pesar de su orden para obtener tantas coincidencias como necesites.
Nota. Actualmente se incorporaron a la herramienta las siguientes cláusulas: seleccionar, dónde, limitar, y compensar. Si su tarea también requiere otras cláusulas, comente a continuación; tal vez nos ayude a mejorar;)
- saber cómo entrar operadores: simplemente elija uno de una lista desplegable.
- dar vueltas a lo correcto forma de ingresar fecha y hora. El complemento le permite ingresarlos como solía hacerlo según la configuración regional de su hoja de cálculo.
Consejo. Siempre hay una pista disponible en la herramienta con ejemplos de diferentes tipos de datos.
Como un primausted será capaz de:
- avance ambos resultado y la fórmula
- hacer ajustes rápidos a tu criterio
- seleccione un lugar para el resultado
- insertar el resultado como ambos Fórmula de CONSULTA o como valores
No estoy bromeando, compruébalo tú mismo. Aunque este GIF fue acelerado, me tomó menos de un minuto ajustar todos los criterios y obtener el resultado:
Si tienes curiosidad, aquí tienes un vídeo detallado que muestra cómo funciona el complemento:
Espero que le des una oportunidad al complemento y consíguelo en Google Workspace Marketplace. No seas tímido y comparte tus comentarios, especialmente si hay algo que no te gusta.
Además, no dudes en consultar su página de tutoriales o su página de inicio.
Error 403 The request cannot be completed because you have exceeded your quota. : quotaExceeded
Preguntas frecuentes sobre la función QUERY de Google Sheets
La función QUERY de Google Sheets es una poderosa herramienta que permite filtrar, ordenar y manipular datos dentro de una hoja de cálculo. A continuación, responderemos a algunas de las preguntas más frecuentes sobre cómo utilizar esta función y exploraremos una herramienta alternativa.
¿Qué es la función QUERY de Google Sheets?
La función QUERY es una fórmula incorporada en Google Sheets que permite realizar consultas específicas a un conjunto de datos almacenados en una hoja de cálculo. Con esta función, puedes seleccionar columnas, filtrar filas y aplicar criterios de búsqueda para obtener los datos que necesitas.
¿Cómo se utiliza la función QUERY?
Para utilizar la función QUERY, sigue estos pasos:
- Abre una hoja de cálculo en Google Sheets.
- Selecciona la celda donde deseas que aparezca el resultado de la consulta.
- Escribe la fórmula QUERY seguida de paréntesis.
- Dentro de los paréntesis, especifica el rango de datos que deseas consultar.
- A continuación, utiliza las cláusulas estándar de la función QUERY para filtrar y ordenar los datos según tus necesidades.
- Finalmente, cierra los paréntesis y presiona Enter para ver los resultados de la consulta.
Para obtener información más detallada sobre cómo utilizar la función QUERY, te recomendamos leer la documentación oficial de Google Sheets.
¿Cuáles son las cláusulas estándar de la función QUERY?
Las cláusulas estándar de la función QUERY te permiten filtrar y ordenar los datos de tu hoja de cálculo. Algunas de las cláusulas más utilizadas incluyen:
- SELECT: se utiliza para seleccionar las columnas que deseas incluir en el resultado de la consulta.
- WHERE: se utiliza para filtrar las filas según un criterio de búsqueda específico.
- ORDER BY: se utiliza para ordenar los datos según una columna determinada.
- LIMIT: se utiliza para limitar el número de filas que se mostrarán en el resultado de la consulta.
¿Existe una herramienta alternativa para la función QUERY?
Sí, existe una herramienta alternativa muy útil llamada “Filtros” en Google Sheets. Los filtros te permiten realizar operaciones similares a la función QUERY, pero utilizando una interfaz gráfica más intuitiva.
Para utilizar los filtros en Google Sheets, sigue estos pasos:
- Selecciona el rango de datos que deseas filtrar.
- Haz clic en el menú “Datos” y selecciona “Filtro”.
- Aparecerán pequeñas flechas en las cabeceras de tus columnas.
- Haz clic en una flecha y selecciona los criterios de filtrado deseados.
- Los datos se filtrarán automáticamente según tus elecciones.
Los filtros son una alternativa conveniente si prefieres evitar el uso de fórmulas y quieres una forma más visual de trabajar con tus datos.
Esperamos que estas preguntas frecuentes te hayan ayudado a comprender mejor cómo utilizar la función QUERY de Google Sheets y a conocer una alternativa para realizar tareas similares. Recuerda que la práctica y la exploración son clave para dominar estas herramientas y aprovechar al máximo tus hojas de cálculo.