Si pasa demasiado tiempo en tareas de datos repetitivos en las hojas de Google, no está solo. Solía ​​perder horas cada semana a los mismos viejos pasos, hasta que me topé con una sola función que me levantó el trabajo pesado. Déjame presentarte la función de consulta.

5

Clasificación automatizada que se actualiza en tiempo vivo

La forma más inmediata de la función de consulta mejoró mis hojas de cálculo es eliminar mis dolores de individuo de reacción constante. Usted sabe ese ciclo frustrante en el que clasifica sus datos, luego, una hora posteriormente, entra una nueva información, y de repente su directorio ordenada está completamente fuera de servicio nuevamente. Ese es el indicado.

Digamos que desea rastrear el inventario de su empresa por precio. En empleo de retornar a clasificar sin detener, puede usar una consulta como esta:

=QUERY(A:G, "SELECT * ORDER BY E desc")

En este ejemplo, le está diciendo a Google Sheets que extraiga todo de las columnas A a G y lo ordene por la columna E (que tiene precios, en este caso) en orden descendente.

Inmediatamente, sus autos más caros flotan directamente a la cima.

Lo que hace de esto un cambio de recreo es que no deja de funcionar. Cuando se agrega un nuevo automóvil de $ 250,000 a su hoja de cálculo, aparece instantáneamente en la parte superior de los resultados de su consulta. No hay falta de retornar a clasificar.

Lo que solía llevarme varios minutos de hacer clic y deslizar durante todo el día ahora sucede automáticamente. Más importante aún, nunca más me pierdo los artículos de adhesión prioridad porque siempre están encajado donde deberían estar.

4

Combinando múltiples pasos en una fórmula

No puedo decirte cuántas veces solía encontrarme haciendo este ballet: filtrar los datos, clasificarlos, ocultar ciertas columnas y tal vez agrupar las cosas juntas. Ahora, gracias a la consulta, puedo hacer todo eso de una sola vez.

Digamos que quiero preparar una revisión de ventas, y necesito hacer lo próximo:

• Filtrar solo los autos vendidos

• Retire cualquier automóvil por debajo de $ 30,000

• Excluir los teslas

• Ordenar por precio para ver las ventas más grandes primero

Eso es cuatro pasos separados. Si tuviera que hacer esto manualmente y cometer un error en cualquier empleo, tendría que comenzar de nuevo. En cambio, escribo:

=QUERY(Test!A:G, "SELECT * WHERE F = TRUE AND E > 30000 AND NOT B contains 'Tesla' ORDER BY E desc")

Una fórmula reemplaza cuatro pasos. Oportuno a que inserté la función de consulta en una hoja en blanco, agregué el nombre de la hoja con una marca de interjección (¡prueba!) Antaño de especificar las columnas con mis datos.

La mejor parte es que puedo ser aún más elegante con cláusulas como pivote, formalidad, etc. Por ejemplo, cuando necesito ver cómo se venden las ofertas de cada año, agrego Agrupar:

=QUERY(Test!A:G, "SELECT D, SUM(E) WHERE F = TRUE GROUP BY D ORDER BY SUM(E) desc")

Esto me muestra los ingresos hasta ahora de los lanzamientos de cada año, clasificados automáticamente de los más altos a los más bajos.

Los ahorros de tiempo se suman rápidamente. Lo que solía llevarme de 10 a 15 minutos de hacer clic, filtrar y organizar ahora tarda 30 segundos en escribir una fórmula. Y a diferencia de mi antiguo proceso de varios pasos, nunca accidentalmente brinco un paso o arruino el pedido.

3

Manejo de conjuntos de datos masivos sin retraso

Imagine esto: está tratando de encontrar a sus 100 clientes más recientes de una cojín de datos de más de 50,000 filas para cursar una campaña de correo electrónico específica. Eso es suficiente simple, ¿verdad?

Inexacto. Cada vez que intente clasificar, filtrar o maniobrar ese tipo de conjunto de datos masivo, las hojas de Google probablemente se congelarán. Traté esto hasta que descubrí que la consulta podría manejar conjuntos de datos voluminosos.

Aquí hay un ejemplo:

=QUERY('50000 Sales Records'!A:N, "SELECT * ORDER BY H desc LIMIT 100")

La primera fórmula obtendrá los 100 envíos más recientes, ya que la columna H incluye fechas de emisión. Mientras tanto, el segundo obtendrá las órdenes 61 a 160.

En empleo de hacer el proceso de su computadora y mostrar las más de 50,000 filas, mientras se toma su tiempo dulce, solo para que pueda ver los 100 mejores, la consulta (con la cláusula término y desplazamiento) es lo suficientemente inteligente como para agarrar exactamente lo que necesita y dejar el resto solo.

Incluso puede usar término y compensación con todas las otras características de consulta (grupos, clasificación, filtrado, sin batallar contra su navegador y perder el tiempo.

2

Prospección de datos en múltiples hojas o archivos

¿Todavía está copiando datos manualmente entre hojas de cálculo o libros de trabajo solo para hacer un crónica? Puedes detener ahora. La consulta le permite analizar datos en múltiples hojas, o incluso en archivos completamente diferentes, sin tocar CTRL+C.

Combinar múltiples pestañas de una vez

Digamos que tiene datos trimestrales divididos en pestañas como Sales_Q1 y Sales_Q2, puede fusionarlos en un solo conjunto de datos utilizando corchetes. Luego, ejecute su prospección como lo haría en una sola hoja.

=QUERY({Sales_Q1!A:N; Sales_Q2!A:N; Sales_Q3!A:N; Sales_Q4!A:N}, "SELECT Col3, Col1, SUM(Col9) WHERE Col5 = 'C' GROUP BY Col3, Col1")

Digamos que la columna 3 (COL3) es el tipo de aspecto, columna 1 La región, columna 9 las unidades vendidas y la columna 5 la prioridad del orden. Simplemente asegúrese de que la estructura (columnas) de cada hoja coincida y que esté agudo para comenzar.

Acabo de combinar cuatro hojas con datos de diferentes trimestres para que se vendan las unidades totales por artículo y región para pedidos de prioridad C. ¡Peasy comprensible!

Extraer datos de otra hoja de cálculo de Google (no se requiere descarga)

Si necesita datos de un archivo completamente diferente, puede usar ImportRange con consulta para traerlo. Digamos que desea traer datos de las ventas de nuestro automóvil y yuxtaponarlo con los datos que hemos obtenido de las cuatro hojas, puede usar esta fórmula:

=QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/yoursheetID/edit", "Test!A:G"), "SELECT Col4, SUM(Col5) GROUP BY Col4")

Deberá otorgar permiso antiguamente de poder extraer datos de la hoja de cálculo externa.

Una vez que otorga comunicación, puede obtener datos de la hoja de cálculo externa en tiempo vivo, y se actualizará si los datos de origen cambian.

La consulta le permite analizar datos en pestañas y archivos sin tener que inaugurar una segunda pestaña o archivo.

1

Ordenar y filtrar sin reescribir fórmulas

¿Desea ordenar o filtrar sus datos de forma diferente sin reescribir toda su fórmula de consulta cada vez? Puede, con una configuración simple usando citas dobles y ampersands:

=QUERY({Sales_Q1!A:N; Sales_Q2!A:N; Sales_Q3!A:N; Sales_Q4!A:N}, "SELECT * WHERE Col1 = '"&G21&"'", 1)

Cuando su superior quiere ver datos europeos, simplemente escribe “Europa” en la celda G21.

Cuando quiere datos de África subsahariana, escribes “África subsahariana”. Solo asegúrese de que sea una entrada válida en la columna 1. La fórmula permanece igual, pero los resultados se actualizan al instante. Una vez que te entiendes, es increíblemente poderoso.

La verdadera hechicería ocurrió cuando comencé a usar esto para los rangos de plazo. Las fechas en las fórmulas de consulta son notoriamente exigentes. Deben estar exactamente en el formato correcto (aaa yyy-mm-dd) o todo se rompe. Pero con las referencias de celdas, puede configurar controles de plazo comprensible de usar:

=QUERY({Sales_Q1!A:N; Sales_Q2!A:N; Sales_Q3!A:N; Sales_Q4!A:N}, "SELECT * WHERE Col6 >= date '"&TEXT(G142, "yyyy-mm-dd")&"' AND Col6

Ahora, tengo la celda G142 para la plazo de inicio, la celda I142 para la plazo de finalización y la columna 6 que representa la columna Data de pedido. Cuando determinado pregunta cuáles fueron nuestras ventas del 15 de marzo de 2011 al 30 de abril de 2015, solo cambio esas dos celdas en empleo de batallar con la sintaxis de fórmula.

La mejor parte es que puedo compartir estas hojas de cálculo con colegas que no son expertos en fórmulas. Ven celdas de entrada limpias donde pueden cambiar la región o ajustar los rangos de plazo, y no tienen idea de que hay una consulta compleja que se ejecuta detrás de decorado.

La función de consulta en las hojas de Google no es solo una fórmula. Es una potencia de automatización de datos en toda regla. Ya sea que esté disputando decenas de miles de filas, copiando datos entre sábanas o simplemente enfermo de clasificación y filtrado repetitivos, la consulta lo maneja todo con facilidad (y estilo).

Es rápido. Es flexible. Y una vez que comience a usar la función de consulta en las hojas de Google, se preguntará seriamente cómo lo logró sin ella.