Cómo crear y usar tablas dinámicas en Hojas de cálculo de Google

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 cantidad enorme de datos, es difícil resumir o sacar conclusiones desde una vista básica de hoja de cálculo.

Ahí es donde entran en juego las tablas dinámicas. La mayoría de los usuarios avanzados de Excel utilizan tablas dinámicas como su sustento básico. Pero también puedes utilizar tablas dinámicas en Hojas de cálculo de Google. 

Aquí te mostraré cómo crear tablas dinámicas en Hojas de cálculo de Google. Para seguir, copie nuestra hoja de cálculo de demostración y luego juegue con ella mientras avanza en el tutorial.

• ¿Qué es una tabla dinámica? 

• Cómo usar una tabla dinámica en Hojas de cálculo de Google

• Cómo personalizar una tabla dinámica en Hojas de cálculo de Google

• Cómo leer una tabla dinámica en Hojas de cálculo de Google

• Cómo actualizar una tabla dinámica en Hojas de cálculo de Google

• Cómo crear y usar tablas dinámicas en Hojas de cálculo de Google: Preguntas frecuentes

¿Qué es una tabla dinámica en Hojas de cálculo de Google?

Una tabla dinámica toma un conjunto grande de datos, generalmente en una hoja de cálculo, y los resume. 

Si está trabajando con una hoja de cálculo pequeña, probablemente no necesite una tabla dinámica. Esto se debe a que los conjuntos de datos pequeños suelen ser lo suficientemente sencillos como para escanearlos y darle sentido a la información. Pero a medida que su hoja de cálculo comienza a crecer, sacar conclusiones requiere un poco más de potencia. Ahí es donde entran en juego las tablas dinámicas. 

Piénsalo de esta manera. Las hojas de cálculo normales esencialmente tienen "datos planos" representados por dos ejes: horizontal (columnas) y vertical (filas).

En el siguiente ejemplo, cada venta se muestra como su propia fila y cada columna ofrece información diferente sobre esa venta.

Si desea obtener más información, deberá agregar datos en otro nivel. Para hacer esto, es necesario desplazar (o pivotar) los ejes de la tabla. Como se muestra en el ejemplo a continuación, ahora ya no está mirando sus datos por venta individual. En cambio, lo que busca son datos agregados: ¿cuántas unidades vendió cada representante de ventas de cada producto? 

Así que esa es la idea básica: se toma una tabla bidimensional y se gira para introducir una tercera dimensión. 

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

Cómo usar una tabla dinámica en Hojas de cálculo de Google

A continuación se muestra una descripción rápida de cómo utilizar una tabla dinámica de Hojas de cálculo de Google. (Siga desplazándose para obtener más detalles y practicar con nuestra hoja de cálculo de demostración).

1. Abra una hoja de cálculo de Google Sheets.

2. Seleccione todas las celdas con datos de origen que desee utilizar (incluidos los encabezados de columna).

3. Haga clic en Insertar y seleccione Tabla dinámica.

4. En el panel Crear tabla dinámica que aparece, elija si desea insertar su tabla dinámica en una hoja nueva o en una hoja existente. Luego haga clic en Crear. 

5. En el panel del editor de tablas dinámicas , junto a Filas y Columnas, haga clic en Agregar para cada una. Esto le permite seleccionar los datos que desea analizar. 

6. En el mismo panel, junto a Valores, haga clic en Agregar. Esto le permite seleccionar los valores que desea mostrar dentro de las filas y columnas. 

7. Si desea mostrar solo los valores que cumplen determinados criterios, agregue filtros. 

Los datos de su tabla dinámica cambiarán automáticamente si cambian los datos de origen. Si no ve los cambios reflejados en su tabla dinámica, actualice su página. La actualización puede tardar un minuto, dependiendo del volumen de cambios en los datos.

Cómo crear una tabla dinámica en Hojas de cálculo de Google

Las tablas dinámicas de Hojas de cálculo de Google son tan fáciles de crear como potentes. Aquí encontrará más detalles sobre cómo crear y utilizar una tabla dinámica en Hojas de cálculo de Google. 

1. Seleccione todas las celdas con datos de origen que desee utilizar, incluidos los encabezados de columna. Para resaltar todos sus datos a la vez, utilice el atajo de teclado: command+A en Mac o ctrl+A en Windows. (Si su conjunto de datos contiene columnas sin encabezados, deberá nombrar estas columnas para crear una tabla dinámica).

2. Haga clic en Insertar y seleccione Tabla dinámica.

   

3. En el panel Crear tabla dinámica que aparece, elija si desea insertar su tabla dinámica en una hoja nueva o en una hoja existente. Luego haga clic en Crear. 

   

Google Sheets completará una tabla dinámica dondequiera que usted lo indique (una hoja de cálculo nueva o existente). Y aquí es desde donde trabajarás.

Cómo editar una tabla dinámica en Hojas de cálculo de Google

Cuando Google Sheets complete por primera vez su tabla dinámica, aparecerá automáticamente un panel de edición de tabla dinámica . Aquí es donde personalizarás tu tabla dinámica para crear diferentes informes. 

Si cierra el editor pero necesita acceder a él nuevamente, pase el cursor sobre la tabla dinámica y haga clic en Editar. 

El editor ofrece dos formas de analizar sus datos: utilizar las sugerencias de Google Sheets o elegir manualmente las dimensiones de su tabla.

Utilice los análisis de datos sugeridos de Hojas de cálculo de Google

Hay muchas posibilidades de que Google Sheets sepa lo que quieres saber incluso antes de que tú mismo lo quieras saber: después de todo, es un producto de Google. En el panel del editor de tablas dinámicas , encontrará una lista de formas sugeridas para analizar su conjunto de datos. (Si no ve la lista, haga clic en el símbolo de cursor hacia abajo [∨] junto a Sugerido). 

Por ejemplo, Google Sheets sugiere los siguientes análisis para nuestro conjunto de datos de ejemplo:

• Promedio de horas dedicadas a cada tipo de proyecto

• Recuento de nombres de clientes para cada tipo de proyecto

• Suma del importe facturado para cada tipo de proyecto

Si hace clic en cualquiera de las opciones sugeridas, Google Sheets creará automáticamente su tabla dinámica. Por ejemplo, si hago clic en Suma del monto facturado para cada tipo de proyecto, Hojas de cálculo de Google completará los tipos de proyecto en la columna A y el monto total facturado para cada uno en la columna B. 

Y si desea modificar la tabla, puede editar la configuración en el panel del editor de tablas dinámicas .

Personaliza tu análisis de datos 

Si los análisis sugeridos no son lo que estás buscando, o si deseas realizar un tipo de análisis diferente, puedes crear manualmente tu tabla dinámica personalizando cuatro opciones en el editor: Filas, Columnas, Valores y Filtros. 

Cómo personalizar tu tabla dinámica en Hojas de cálculo de Google 

Hay no menos de 19,329 formas de personalizar una tabla dinámica en Hojas de cálculo de Google. Para ayudarlo a comprender cómo usar cada elemento de la tabla dinámica, le mostraré cómo editar la tabla dinámica para analizar cuánto facturamos en 2023 para cada cliente en diferentes tipos de proyectos. Convenientemente (definitivamente fue así por diseño), cada elemento que buscamos (cliente, tipo de proyecto, monto total facturado y año) se alinea perfectamente con nuestras opciones de editor. 

• Las filas y columnas le ayudan a crear el conjunto de datos bidimensionales sobre el cual puede calcular los valores de la tercera dimensión. En este caso, nuestros datos base son Nombre del cliente (fila) y Tipo de proyecto (columna).

• El valor que queremos obtener en las celdas donde se encuentran Nombre del cliente y Tipo de proyecto es Monto total facturado.

• El filtro le permite analizar solo un subconjunto específico de datos. En este caso, queremos utilizar datos de sólo 2023. 

Antes de poder crear el informe, debemos comenzar con una tabla dinámica vacía. Si utilizó uno de los análisis sugeridos de Hojas de cálculo de Google en el paso anterior, borre sus selecciones haciendo clic en la X junto a las opciones Filas y Valores . 

Ahora pasemos a construir realmente esta cosa. A modo de recordatorio, esta es la pregunta que nos hacemos: 

¿Cuánto facturamos en 2023 para cada cliente en los diferentes tipos de proyectos? 

Agregar filas a su tabla dinámica 

Primero, necesitamos configurar nuestra tabla para mostrar la lista de clientes y los tipos de proyectos. En el panel del editor de tablas dinámicas , junto a Filas, haga clic en Agregar y seleccione Nombre del cliente. 

Google Sheets toma automáticamente los datos de origen de los nombres de los clientes, elimina los duplicados y completa una lista de valores únicos como filas en su tabla dinámica. De forma predeterminada, los nombres de los clientes aparecerán en orden alfabético (de la A a la Z). 

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

Agregar columnas a su tabla dinámica 

El siguiente paso es agregar el Tipo de Proyecto como columnas. En el editor, junto a Columnas, haga clic en Agregar y seleccione Tipo de proyecto. Ahora su tabla debería mostrar cada tipo de proyecto como encabezados de columna separados en las columnas B a G. 

Agregar valores a su tabla dinámica 

Ahora necesitamos Hojas de cálculo de Google para completar el monto total facturado para cada cliente y tipo de proyecto. 

En el editor, junto a Valores, haga clic en Agregar y, a continuación, haga clic en Monto facturado. Asegúrese de que el menú desplegable bajo Resumir por esté configurado en SUMA. De esta manera, obtienes el importe total facturado frente a, digamos, el importe promedio facturado. 

Ahora la tabla dinámica nos proporciona información útil: el importe total facturado por cada tipo de proyecto que hemos completado para un cliente determinado. 

Y para ayudarnos a comprender el monto total facturado a cada cliente y el monto total facturado para un tipo de proyecto determinado entre todos los clientes, Google Sheets calcula automáticamente el total general en la columna G. 

Añade filtros a tu tabla dinámica 

Estamos a un paso de responder nuestra pregunta original. Ahora necesitamos agregar un filtro para mostrar solo los valores de 2023. (Nota: puede aplicar un filtro a los datos en su tabla dinámica según cualquier columna de sus datos de origen). 

En el editor, junto a Filtros, haga clic en Agregar y seleccione Año. De forma predeterminada, se seleccionan todos los años disponibles (2022 y 2023) de los datos de origen. Anule la selección 2022 y haga clic en vale. 

¡Eso es todo! Ahora tienes una tabla dinámica que responde a la pregunta: "¿ Cuánto facturamos en 2023 para cada cliente en los diferentes tipos de proyectos?"

Cómo leer una tabla dinámica de Hojas de cálculo de Google 

Usando la tabla dinámica que creamos en la sección, ahora podemos responder casi cualquier pregunta que tengamos sobre los datos de origen. Este es el paso final para convertirse en un profesional de las tablas dinámicas. 

Para ayudarlo a comprender cómo leer una tabla dinámica de Hojas de cálculo de Google, analizaremos dos preguntas de ejemplo más. 

• ¿A qué cliente le facturamos más en 2023? 

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

¿A qué cliente le facturamos más en 2023?

Para responder a esta pregunta, solo necesitamos los nombres de nuestros clientes como filas y la suma del monto facturado a ellos como valores. La buena noticia es que esto significa que tenemos que simplificar nuestra tabla dinámica. 

Si aún no está visible, abra el panel del editor de tablas dinámicas . 

1. En la sección Columnas , junto a Tipo de proyecto, haga clic en X para eliminar esta opción. 

2. En la sección Filas , dentro de la opción Nombre del cliente , haga clic en el símbolo de cursor hacia abajo (∨) debajo de Ordenar por. Luego haga clic en SUMA del monto facturado.

   

La tabla dinámica se actualiza automáticamente para mostrar el total general del monto facturado, por cliente, en orden ascendente. Ahora sabemos que en 2023, la empresa a la que más le facturamos, con un total general de $1,700.00, era Questindustries. 

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

Para responder a esta pregunta, cambiaremos nuestro análisis desde el monto total facturado a la tarifa por hora promedio más alta para cada tipo de proyecto.

Antes de comenzar, abra el panel del editor de tabla dinámica y elimine todas las opciones excepto el filtro existente. 

Nota: Los datos de origen solo se pueden usar una vez por opción en el panel del editor de tabla dinámica . Por ejemplo, si agrega Tipo de proyecto como sus filas, no podrá usar estos datos para sus columnas. 

1. Al lado de Filas, haga clic en Agregar. Luego haga clic en Tipo de proyecto. 

2. Al lado de Valores, haga clic en Agregar. Luego haga clic en Tarifa por hora. 

3. En la sección Valores , en Resumir por, haga clic en el símbolo de cursor hacia abajo (∨) y haga clic en PROMEDIO. 

   

Ahora tenemos nuestra respuesta: la tarifa por hora promedio más alta cobrada en 2023 fue $68.00 por Edición de copias. 

Cómo actualizar una tabla dinámica en Hojas de cálculo de Google

Digamos que has editado tus datos fuente originales. Estos cambios deberían reflejarse automáticamente en su tabla dinámica. Pero si ese no es el caso, haga clic en el icono Actualizar en su navegador web. 

Sin embargo, si ha agregado nuevas filas o columnas de datos a sus datos fuente originales, una simple actualización no resolverá el problema. En su lugar, actualice el rango de datos de su tabla dinámica para incluir las nuevas filas o columnas. 

1. Coloque el cursor sobre la tabla dinámica y haga clic en Editar. 

2. En el campo Rango de datos del editor de tabla dinámica, ingrese el nuevo rango de datos. 

Eso es todo.

Cómo crear y usar tablas dinámicas en Hojas de cálculo de Google: Preguntas frecuentes

¿Aún tienes preguntas sobre cómo usar tablas dinámicas en Hojas de cálculo de Google? Consulta las respuestas a estas preguntas frecuentes para que puedas aprovechar al máximo tus hojas de cálculo. 

¿Las tablas dinámicas se actualizan automáticamente en Hojas de cálculo de Google? 

Si edita valores de celda en sus datos de origen originales y esas celdas se capturan dentro del rango de datos seleccionado de su tabla dinámica, estas actualizaciones se reflejarán automáticamente en su tabla dinámica. 

Sin embargo, si agrega nuevas filas o columnas de datos fuera del rango de datos seleccionado de su tabla dinámica, deberá actualizar manualmente el rango de datos de su tabla dinámica para capturar estos nuevos valores. 

¿Puede una tabla dinámica extraer información de varias hojas de cálculo?

Las tablas dinámicas en Hojas de cálculo de Google solo pueden hacer referencia a una única hoja de cálculo. Si desea que su tabla dinámica haga referencia a datos de varias hojas de cálculo, primero deberá combinar esos datos en una sola hoja de cálculo. Luego, puedes crear una tabla dinámica como lo harías normalmente.  

¿Es posible tener dos tablas dinámicas en una hoja? 

Puede insertar varias tablas dinámicas en una hoja de cálculo de Google Sheets.

1. Crea una tabla dinámica como lo harías normalmente. 

2. En el panel Crear tabla dinámica que aparece, seleccione Hoja existente e ingrese la hoja de cálculo y la celda donde desea agregar su nueva tabla dinámica. 

3. Haga clic en Crear. 

¿Es posible fusionar dos tablas dinámicas? 

No es posible fusionar dos tablas dinámicas en Hojas de cálculo de Google, pero existe una solución alternativa. Combine los datos de origen originales de ambas tablas dinámicas en una hoja de cálculo y luego cree una nueva tabla dinámica.

Automatizar Hojas de cálculo de Google 

Con las tablas dinámicas, es fácil dar sentido a grandes conjuntos de datos: no se necesitan fórmulas complicadas. Una vez que hayas comprendido claramente los conceptos básicos, usa Zapier para conectar Google Sheets con tus otras aplicaciones preferidas, de modo que puedas automatizar aún más tareas relacionadas con las hojas de cálculo. Por ejemplo, puede agregar automáticamente datos de clientes futuribles e información proveniente de envíos de formularios a hojas de cálculo existentes. 

Aquí tienes algunas plantillas de Zap que te ayudarán a empezar.

Lecturas relacionadas: 

• Cómo usar el formato condicional en Hojas de cálculo de Google

• Cómo agregar un menú desplegable en Hojas de cálculo de Google

• Complementos de Hojas de cálculo de Google para potenciar tu trabajo

• 21 Plantillas gratuitas de Hojas de cálculo de Google para aumentar la productividad

Este artículo fue publicado originalmente en septiembre 2018 por John Thomas. La actualización más reciente fue en marzo 2024