Uno de los principales motivos por los que decidí empezar a usar las hojas de cálculo de Google es por la ventaja de tener disponible siempre mis documentos sin tener que ir cargando con ellos a cuesta y poder usarlos desde cualquier soporte y sistema operativo. Pero pasar de usar Excel a usar las hojas de calculo de Google no ha sido una tarea fácil debido a que la funcionalidad que ofrece esta limitada, de momento, por el soporte sobre el que se ejecuta: El navegador Web. Pero esta limitación es temporal debido a que los navegadores están evolucionando rápidamente y dentro de poco tiempo no distinguiremos entre aplicaciones de escritorio y aplicaciones web.
Pero lo cierto es que hoy por hoy, intentar hacer las mismas cosas que hacemos con Excel en las hojas de calculo de Google requieren un poco más de esfuerzo. Una de esas cosas que yo usaba con frecuencia eran las tablas dinámicas. Siempre me ha parecido un sistema ideal para analizar información agrupándola por conceptos y es lo primero que he echado en falta en el sistema de Google. Después de investigar un poco he encontrado una solución que solventa esta carencia y que pasaré a explicar.
Lo primero es preparar un conjunto de datos sobre los que trabajar. Yo he preparado una serie de datos simulando una facturación de clientes. En la lista aparece la fecha de emisión de la factura, el nombre del cliente, y el importe facturado. El mes se ha calculado usando la formula month que extrae el mes de una fecha. El resultado es el que podéis ver en el cuadro de la derecha.
Ahora lo que queremos hacer es analizar la evolución de cada cliente por meses. Aquí es donde entra en juego la función que nos permitirá resolver el problema. La función a usar es query. Esta función nos permite realizar consultas sobre un conjunto de datos. La sintaxis es muy sencilla. query(rando de datos;consulta a ejecutar). La sintaxis a utilizar en la consulta es similar a la utilizado en SQL con algunas limitaciones y peculiaridades.
Vamos con un ejemplo: Queremos agrupar la facturación de cada cliente para tener un total por año. La sintaxis para la consulta seria SELECT B, SUM(C) GROUP BY B. Aunque es bastante evidente la expresión indica que seleccionemos la columna B, sume la C agrupando los valores del campo B. En mi caso, el texto completo de la fórmula es el siguiente:
Pero esto solo soluciona la mitad del problema porque no hemos conseguido agruparlo por mes. Nuestra primera intención podría ser incluirlo en la consulta como SELECT B, D, SUM(C) GROUP BY B, C y, aunque sintacticamente es correcto, el resultado no lo es del todo porque, efectivamente lo ha agrupado pero de forma secuencial y nosotros queremos transponer los meses para poder compararlos.
Aquí es donde debemos incluir la instruccion PIVOT que nos permitirá transponer los valores que queramos. La formula finalmente quedará como
El resultado es bastante buenos si le aplicamos formato a las celdas y conseguimos darle un aspecto más formal marcando los bordes para delimitar los datos. Para finalizar podremos generar un gráfico sobre los datos calculados de la formula anterior. A diferencia a las tablas dinámicas de Excel, no podemos seleccionar los datos para filtrar el contenido dinámicamente, pero podríamos generar un listado y filtrar los datos con unas lista desplegable. Para poder incluir condiciones deberemos usar la cláusula WHERE dentro de la consulta.
Para finalizar os dejo unos enlaces con el ejemplo completo para que podáis consultarlo y a la documentación ampliada. En el ejemplo encontrareis una hoja con el ejemplo básico y otro un poco más elaborado simulando una tabla dinámica con una lista desplegable. Como la hoja no es editable para poder usar la lista tendréis que crearos una copia del documento para poder verlo funcionar completamente.
Ejemplo: link
Documentación funciones: link
Documentación función Query: link
Pero lo cierto es que hoy por hoy, intentar hacer las mismas cosas que hacemos con Excel en las hojas de calculo de Google requieren un poco más de esfuerzo. Una de esas cosas que yo usaba con frecuencia eran las tablas dinámicas. Siempre me ha parecido un sistema ideal para analizar información agrupándola por conceptos y es lo primero que he echado en falta en el sistema de Google. Después de investigar un poco he encontrado una solución que solventa esta carencia y que pasaré a explicar.
Ahora lo que queremos hacer es analizar la evolución de cada cliente por meses. Aquí es donde entra en juego la función que nos permitirá resolver el problema. La función a usar es query. Esta función nos permite realizar consultas sobre un conjunto de datos. La sintaxis es muy sencilla. query(rando de datos;consulta a ejecutar). La sintaxis a utilizar en la consulta es similar a la utilizado en SQL con algunas limitaciones y peculiaridades.
Vamos con un ejemplo: Queremos agrupar la facturación de cada cliente para tener un total por año. La sintaxis para la consulta seria SELECT B, SUM(C) GROUP BY B. Aunque es bastante evidente la expresión indica que seleccionemos la columna B, sume la C agrupando los valores del campo B. En mi caso, el texto completo de la fórmula es el siguiente:
=QUERY(A3:D23;"SELECT B, SUM(C) GROUP BY B").
"Nota: Es importante que el nombre esté en mayúsculas."
Pero esto solo soluciona la mitad del problema porque no hemos conseguido agruparlo por mes. Nuestra primera intención podría ser incluirlo en la consulta como SELECT B, D, SUM(C) GROUP BY B, C y, aunque sintacticamente es correcto, el resultado no lo es del todo porque, efectivamente lo ha agrupado pero de forma secuencial y nosotros queremos transponer los meses para poder compararlos.
=QUERY(A2:D23;"SELECT B,SUM(C) GROUP BY B PIVOT D").
Para finalizar os dejo unos enlaces con el ejemplo completo para que podáis consultarlo y a la documentación ampliada. En el ejemplo encontrareis una hoja con el ejemplo básico y otro un poco más elaborado simulando una tabla dinámica con una lista desplegable. Como la hoja no es editable para poder usar la lista tendréis que crearos una copia del documento para poder verlo funcionar completamente.
Ejemplo: link
Documentación funciones: link
Documentación función Query: link
Hola, sabes como hacer para que el mes 10 no se colocoque detrás del 1?
ResponderEliminar