Cómo usar tablas dinámicas en Google Sheets

Las hojas de cálculo ofrecen potentes capacidades de análisis, pero a veces parece que les falta esa capa adicional de información. Cuando hay una gran cantidad de datos, es difícil resumir o sacar conclusiones de una vista de hoja de cálculo tabular básica.

Ingrese: la tabla dinámica.

La mayoría de los usuarios avanzados de Excel emplean tablas dinámicas como su pan y mantequilla, pero Google Sheets ofrece la misma herramienta, por lo que puede usar tablas dinámicas mientras mantiene las cosas en G Suite. En este artículo, veremos cómo crear tablas dinámicas en Hojas de cálculo de Google.

¿Qué son las tablas dinámicas?

En su forma más simple, una hoja de cálculo es solo un conjunto de columnas y filas. Cuando una columna y una fila se encuentran, se forman celdas. Puede usar fórmulas para registrar datos dentro de estas celdas, y cuando su hoja de cálculo es pequeña, es lo suficientemente simple para leer y comprender los números.

Pero a medida que su hoja de cálculo comienza a crecer, sacar conclusiones requiere un poco más de poder. Ahí es donde entran las tablas dinámicas. Una tabla dinámica toma un gran conjunto de datos y lo resume.

Piénselo de esta manera: las hojas de cálculo normales tienen esencialmente «datos planos» representados por dos ejes, horizontal (columnas) y vertical (filas):

Para obtener más información, deberá agregar datos en otro nivel. En el caso anterior, por ejemplo, comienza con cada venta como su propia fila, y cada columna ofrece información diferente sobre esa venta. Pero si cambias (o pivote) los ejes de la tabla, puede agregar otra dimensión:

Ahora, no está viendo las cosas por venta individual. En su lugar, está mirando datos agregados: ¿Cuántas unidades vendimos en cada región para cada fecha de envío?

Así que esa es la idea aproximada: puede tomar una tabla bidimensional y girarla alrededor de una agregación de datos para introducir una tercera dimensión. Y así es como se obtiene una tabla dinámica. Hacerlo le ayuda a ver a vista de pájaro, derivar el significado de grandes cantidades de datos y sacar a la luz conocimientos únicos.

Si bien puede obtener muchos de estos conocimientos mediante fórmulas, la tabla dinámica le permite destilarlos en una fracción del tiempo y con menos posibilidades de error humano. Además, cada vez que su jefe solicita un nuevo informe basado en el mismo conjunto de datos, puede generarlo con unos pocos clics, en lugar de comenzar desde cero.

Cómo usar tablas dinámicas en Google Sheets

Las tablas dinámicas de Google Sheets son tan fáciles de usar como potentes. Aquí hay un vistazo rápido a cómo usarlos, seguido de un tutorial más detallado.

1. Abra una hoja de cálculo de Google Sheets y seleccione todas las celdas que contienen datos.

2. Hacer clic Datos > Tabla dinámica.

3. Compruebe si los análisis de tabla dinámica sugeridos por Google responden a sus preguntas.

4. Para crear una tabla dinámica personalizada, haga clic en Agregar junto a Filas y Columnas para seleccionar los datos que le gustaría analizar.

5. Hacer clic Agregar junto a Valores para seleccionar los valores que desea mostrar dentro de las filas y columnas.

6. Hacer clic Filtros para mostrar solo los valores que cumplen ciertos criterios.

Para este tutorial, hemos creado una hoja de cálculo de Google Sheets con datos ficticios. Abra la hoja de Google, haga una copia y luego siga nuestro tutorial detallado a continuación.

Crea la tabla dinámica

Tiene una hoja llena de datos sin procesar, por lo que lo primero que debe hacer es convertirla en una tabla dinámica.

Seleccione todas las celdas que contienen datos (command o ctrl + A es un atajo útil). Luego haga clic en Datos > Tabla dinámica…, Como se muestra abajo.

Esto creará una nueva hoja en su hoja de cálculo llamada «Tabla dinámica». Y ahí es donde trabajará.

Aprenda el editor de tablas dinámicas

Con su tabla dinámica generada, está listo para comenzar a hacer algunos análisis. Para hacerlo, utilizará el editor de tablas dinámicas para crear diferentes vistas de sus datos. Verá el editor en el lado derecho de su hoja de cálculo de Google Sheets.

El editor ofrece dos formas de analizar: utilizando las sugerencias de Google o eligiendo sus dimensiones manualmente.

Tablas dinámicas sugeridas

Google, siendo Google, sabe lo que quiere saber antes de que usted sepa que quiere saberlo. En «Sugerido» en el editor, Google ofrece análisis para su conjunto de datos.

Por ejemplo, dado nuestro ejemplo de conjunto de datos, sugiere los siguientes análisis:

• Promedio de horas invertidas para cada tipo de proyecto

• Recuento de nombre de cliente para cada tipo de proyecto

• Suma de la cantidad facturada para cada tipo de proyecto

Si hace clic en cualquiera de las opciones sugeridas, Google Sheets creará automáticamente su tabla dinámica inicial. Por ejemplo, haga clic en la tercera opción («Suma de la cantidad facturada para cada tipo de proyecto») y verá los tipos de proyectos en la columna A y la cantidad total facturada para cada uno en la columna B.

Opciones manuales

Si el análisis sugerido no es lo que está buscando, o si desea realizar un tipo de análisis diferente, puede generar manualmente su resultado preferido.

Encontrará cuatro opciones en el lado derecho de su hoja que le permiten insertar datos en su tabla dinámica:

Estas son las diversas dimensiones que puede utilizar para analizar sus datos. Veremos un análisis de ejemplo para mostrarle cómo usarlos, pero primero, comience por eliminar las selecciones existentes (creadas por el análisis sugerido que acabamos de realizar) haciendo clic en X para el Filas y Valores opciones.

Ahora debería volver a su tabla dinámica vacía original con la que comenzó. Aquí está el análisis que queremos hacer:

Para cada uno de nuestros clientes, en diferentes tipos de proyectos, ¿cuánto facturamos en 2017?

En este caso, buscamos cuatro cosas:

• Para cada cliente

• Entre tipos de proyectos

• Monto total facturado

• En 2017

Como puede adivinar por la noche, cada una de las piezas se alinea con uno de nuestros elementos: filas, columnas, valores y filtros.

• Filas y columnas ayudarlo a construir el conjunto de datos bidimensionales en el que puede calcular sus valores de tercera dimensión. En este caso, nuestros datos base son Nombre del cliente (fila) y Tipo de proyecto (columna).

• La valor queremos entrar en las celdas donde se encuentran el Nombre del cliente y el Tipo de proyecto es Monto total facturado.

• ¿Cómo mostramos datos de solo 2017? Ahí es donde el filtrar entra. El filtro le permite analizar sólo un subconjunto específico de datos.

Haga clic en «Agregar» para cualquiera de esas cuatro opciones, y obtendrá un menú desplegable con los nombres de las columnas de su hoja de datos original. Si hace clic en uno de esos nombres de columna, los datos se agregarán en el formato dado.

Construye el informe

Ahora vayamos a construir realmente esta cosa. Recuerde, esta es la pregunta que nos hacemos:

Para cada uno de nuestros clientes, en diferentes tipos de proyectos, ¿cuánto facturamos en 2017?

Paso 1: agregar filas

Primero, necesitamos configurar nuestra tabla para tener tanto la lista de clientes como los tipos de proyectos. Haga clic en Agregar junto a Filasy seleccione el nombre del cliente columna de la que extraer datos.

Como implican las selecciones, ahora verá todos los nombres de sus clientes como filas en su tabla dinámica.

Tomó la parte seleccionada de los datos originales, eliminó los duplicados y ahora le muestra los datos en un informe fácil de digerir. La columna A ahora tiene una lista única de clientes en orden alfabético (AZ) de forma predeterminada.

Por supuesto, todo lo que ha hecho hasta ahora es agregar una columna existente a su tabla dinámica. Deberá agregar más datos si realmente desea obtener valor de su informe.

Paso 2: agregar columnas

El siguiente paso es agregar el tipo de proyecto como columnas. En el editor de tablas dinámicas, haga clic en Agregar junto a Columnasy seleccione Tipo de proyecto. Aquí está el resultado:

Paso 3: agregar valores

Ahora que tenemos nuestras filas y columnas, necesitaremos traer valores calculados para cada celda individual en la tabla dinámica para ver el monto total facturado. En el editor de tablas dinámicas, haga clic en Agregar junto a Valoresy seleccione Cantidad cobrada.

Para asegurarse de que está viendo un monto total facturado (en comparación, por ejemplo, con el monto promedio facturado), deberá dirigirse al Resumir por campo y seleccione SUMA.

Ahora tenemos información útil: el monto total facturado por cada tipo de proyecto que hemos completado para un cliente determinado.

También verá que el «Total general» se agrega y calcula automáticamente. Eso nos permite ver el monto total que le hemos facturado a cada cliente. y la cantidad total que hemos facturado por un tipo de proyecto determinado en todos los clientes.

Paso 4: agregar filtros

Ya puede ver el poder de la tabla dinámica, pero lo que hemos creado todavía no responde a nuestra pregunta: todavía no hemos filtrado la tabla para mostrar solo los valores de 2017.

Para hacer esto, haga clic en Agregar al lado de Filtros opción y seleccione Año. Tanto 2017 como 2018 (los dos años de nuestro conjunto de datos original) se marcarán de forma predeterminada. Anule la selección de 2018 y haga clic en OK para actualizar la tabla para que solo muestre datos de 2017.

Y eso es eso. Ahora tiene una tabla dinámica que responde a la pregunta:

Para cada uno de nuestros clientes, en diferentes tipos de proyectos, ¿cuánto facturamos en 2017?

Leer la tabla dinámica

Con toda la información que queremos justo frente a nosotros, ahora podemos responder casi cualquier pregunta que tengamos sobre los datos. Para solidificar nuestra comprensión del uso de tablas dinámicas en Google Sheets, veremos dos ejemplos más.

¿A qué cliente facturamos más en 2017?

Para responder a esta pregunta, necesitaremos simplificar nuestro informe: solo necesitamos los nombres de nuestros clientes como filas y la suma del monto facturado como valores.

Primero, deberá eliminar el Tipo de proyecto de las columnas haciendo clic en la X superior derecha en el Columnas sección junto a Tipo de proyecto.

Siguiente, debajo nombre del cliente, Seleccione Ordenar por > SUMA de la cantidad facturaday la tabla se reordenará para mostrarle los datos en orden ascendente.

Ahora podemos responder a nuestra pregunta: facturamos más a la empresa de muestra «Questindustries» en 2017, a $ 1,700.

¿Qué tipo de proyecto tuvo la tarifa por hora más alta en promedio?

Aquí, cambiaremos nuestro análisis de mirar el monto total facturado a la tarifa promedio por hora más alta para cada tipo de proyecto.

Para hacer esto, cambie el nombre del cliente por el tipo de proyecto en el Filas sección haciendo clic en la X superior derecha para borrar su selección. Luego seleccione Tipo de proyecto como su nuevo valor de filas.

Entonces, en el Valores sección, eliminar Cantidad cobrada y seleccione Tarifa por hora en lugar de.

Entonces cambia el Valores ajuste de SUMA a PROMEDIO para ver el monto promedio facturado, no la suma. Verá que la tarifa por hora promedio más alta que cobramos en 2017 fue de $ 68.00 por edición.

---

¿Por qué utilizar Hojas de cálculo de Google para tablas dinámicas?

Zapier le ayuda a introducir todos los datos de su empresa en Hojas de cálculo de Google sin mover un dedo. Una vez que tenga todos esos datos en un solo lugar, debe analizarlos, y ahora puede hacerlo de manera eficiente utilizando tablas dinámicas. Con las tablas dinámicas en Google Sheets, puede desbloquear el potencial de sus datos y extraer la información para todas las partes interesadas sin utilizar fórmulas complicadas.

Una vez que haya dominado los conceptos básicos, intente llevar las cosas al siguiente nivel. Utilice nuestra hoja de cálculo de muestra para ver qué tipo de información puede encontrar con solo unos pocos clics.