Introducción a tablas dinámicas en Google Sheets

Las tablas dinámicas son unas de las herramientas más útiles que nos dan las hojas de cálculo

Hoy veremos que es una tabla dinámica, como se crea y como se modifica una tabla dinámica en Google Sheets.

¿Qué es una tabla dinámica?

La tabla dinámica son de esos conceptos que cuando uno solo los ha oído nombrar piensa que es básicamente una tabla muy bien hecha. (La ignorancia es atrevida, ya me ha pasado).

Pero también es de esas cosas que marcan un antes y un después en el trabajo con hojas de cálculo. Uno no puede saber como vivió sin ella por tanto tiempo.

También es uno de esos conceptos tan obvios pero a la vez tan difíciles de explicar.

Es importante también saber en este punto que las tablas dinámicas en Sheets y en Excel son exactamente lo mismo. Que se construyan de manera diferente y que tengan opciones avanzadas distintas, es otra cosa. Pero conceptualmente son lo mismo.

Intentemos una definición:

Una tabla dinámica es una forma de resumir y reorganizar los datos de una tabla.

Pero veámoslo mejor en un ejemplo:

Digamos que tenemos una base muy sencilla con datos de clientes y ventas.

Ahora quiero resumir esos datos para poder tener información que me pueda servir para tomar decisiones. Por ejemplo podría querer ver como están distribuidas las ventas por cliente, y así mismo el total de ventas.

Esto lo podría hacer de varias maneras:

• Podría manualmente ir sumando los valores de cada cliente uno por uno.
• Podría usar formulas avanzadas como SUMAR.SI o SUMAR.SI.CONJUNTO
• La más fácil de todas: podría usar una tabla dinámica

En nuestro ejemplo, una tabla dinámica se vería así:

Y esto se hace con un par de clics (Ya les mostraré como).

Como pueden empezar a ver, la tabla dinámica es una herramienta tan flexible y poderosa que antes de construir una, deberíamos tener claro que información queremos, o en otras palabras, que preguntas queremos respondernos.

¿A qué preguntas puede responder una tabla dinámica en Google Sheets?

La tabla dinámica es un instrumento muy flexible. Tan flexible, que si tenemos una base con muchas columnas nos puede abrumar si no hemos planeado antes como la queremos usar. Entonces hagamos el ejercicio: ¿Qué nos podríamos preguntar de nuestra base? O, mejor dicho ¿Qué información queremos extraer de los datos que ya tenemos?

Algunas de las posibles preguntas de una base de ventas básica de una compañía que vende productos de distintas categorías en varios países de Latinoamérica serian:

• ¿Cuántas unidades se vendieron durante el año 2017?
• ¿Cuál fue el total vendido en Argentina en abril de 2018?
• ¿Cuál fue el promedio de pedido de la categoría de electrodomésticos?
• ¿Cómo evolucionó la venta de la categoría de ropa desde el 2016 al 2019?
• ¿Cuál fue el mejor trimestre de ventas para Colombia durante el 2017?
• ¿Cuál fue el ranking de ventas por países en 2019?
• ¿Cuál fue el producto menos vendido en Perú durante 2016?
• ¿Cuáles productos no se vendieron en Argentina y Perú en el 2018?

Entonces podemos decir que para empezar a construir una tabla dinámica, necesitamos dos cosas:

1. Una base de datos.
2. Pregunta (o preguntas) a responder.

Otra vez pregunto entonces ¿Qué es una tabla dinámica?

Una tabla dinámica me permite llegar a información específica, mediante la reorganización de mis campos (columnas) en una tabla bidimensional.

Por ejemplo, volviendo a nuestro ejemplo, si tengo las siguientes columnas en mi base:

• País
• Categoría
• Unidades
• Precio
• Ventas
• Producto
• Vendedor
• Fecha

En mi tabla dinámica podría “jugar” con estas 8 variables de la manera “que se me dé la gana”, resumiendo alguna de las otras columnas.

Por ejemplo, podría sumar las ventas, poniendo en las filas los países, y en las columnas los años.

También podría sumar las unidades pero esta vez poniendo en las filas las categorías, y en las columnas los países.

También podría promediar los precios, pero poniendo primero en las filas las categorías, y después otra vez en las filas, los productos.

Las opciones son innumerables, de ahí la importancia de tener muy clara la pregunta que queremos responder.

Construyendo nuestra primera tabla en Google Sheets

Crear una tabla dinámica en Google Sheets es realmente fácil.

La dificultad o el secreto esta es en saber agregar las columnas, las filas y los valores que van a “popular” nuestra tabla.

Vamos por pasos, y poco a poco aumentemos la complejidad. Para esto tomemos un ejemplo sencillo y empecemos a preguntarnos cosas de nuestra base.

Nuestro ejemplo es una base de ventas de la empresa “Ayayay”.

En este primer paso solo vamos a tener dos columnas en nuestra base maestra. El cliente y el valor de la venta.

Ahora empecémonos a hacer preguntas.

Pregunta 1. ¿Cuál es el total de mis ventas?

Esta es una pregunta tan sencilla, que la podemos saber, simplemente escogiendo nuestros datos, y viendo la esquina inferior izquierda de nuestra pantalla:

O con una sencilla formula de suma

Pero es lo suficientemente simple para “meter nuestro primer pie al agua” antes de entrar de lleno a la piscina de las tablas dinámicas.

Con una tabla dinámica, la respuesta a esta pregunta se ve así:

Hasta ahora nada sorprendente ¿verdad? Vamos con pasos de bebé. Ten paciencia.

Veamos como llegar en seis sencillos pasos a esta tabla dinámica en Google Sheets:

1. Escoger el intervalo completo, o pararse en alguna de las celdas del intervalo (para que Google Sheets identifique automáticamente el intervalo).
2. Ir a “Insertar” > “Tabla Dinámica”
3. Aparece el cuadro de “Crear Tabla dinámica”. Para este ejemplo dejamos seleccionada la opción de “Nueva Hoja”, y damos clic en “Crear”. Nota: Es una buena práctica verificar que el intervalo deseado coincida con el que aparece en el campo “Intervalo de Datos”. Si no es así, desde ahí podemos modificarlo.
4. El anterior paso nos crea una nueva hoja, nos lleva a ella y aparece un panel nuevo a la derecha llamado “Editor de Tablas Dinámicas” En el campo “valores” de este panel damos clic en “Añadir”.
5. Nos aparece un menú desplegable. Vamos a escoger el campo “Ventas” (que es el campo donde están los datos que queremos totalizar).
6. ¡Y listo! (En realidad eran cinco pasos)

Antes de poder pasar a responder nuevas preguntas, podemos entrar a descomponerla y entender sus partes.

Componentes de una tabla dinámica

La tabla dinámica tiene cuatro componentes básicos:

• Valores
• Filas
• Columnas
• Filtros

Vamos a verlos uno por uno, pero los entenderemos mejor con los ejemplos de más adelante.

Valores (Métricas)

Los valores se refiere a lo que voy a resumir, que normalmente son campos numéricos que se pueden contar, sumar o promediar. En el ejemplo, lo que hicimos fue mirar la suma total de las ventas.

Es importante resaltar suma total y ventas, porque en vez del total, yo podría mostrar el promedio, o el máximo, o el minino, o contar las ventas.

Y en vez de ventas podrían ser las unidades, los precios, o cualquier otra cosa

Filas y Columnas (Dimensiones)

Las filas y las columnas son la manera en que puedo mostrar la información. Si quiero ver las cosas de manera vertical, la organizo por filas, si lo quiero ver de manera horizontal, lo organizo por columnas. Estas dimensiones normalmente son campos de texto. Esto lo veremos más claro en el próximo ejemplo.

Filtros

Los filtros son maneras de limitar la tabla para que solo se muestren ciertas cosas. De nuevo, es más fácil entenderlo con ejemplos.

Ahora que ya conocemos los elementos básicos de una tabla dinámica podemos empezar a jugar con nuestra tabla. Para eso vamos a la siguiente pregunta:

Preguntas a responder con tablas dinámicas

Pregunta 2. ¿Cuanto le vendí a cada cliente?

Para esto tenemos que añadir el campo cliente de alguna manera a nuestra tabla dinámica. Veamos como.

La primera opción es añadir el campo “Cliente” a la sección “Filas”

Lo que nos resulta en lo siguiente:

Entonces, adicional al total que ya teníamos, ahora tenemos nuestro primer desagregado, y nos respondemos nuestra segunda pregunta.

Intentemos ahora pasarlo a las columnas a ver como se ve.

Lo podemos hacer de dos maneras:

Primera manera: eliminando el campo actual y agregando uno nuevo. Para esto:

1. Eliminamos el campo “Cliente” de la sección “Fila”
2. Agregamos el campo “Cliente” a la sección “Columna”

Lo que nos resulta en:

Es lo mismo pero presentado de manera diferernte.

Para evitarnos tantos pasos, lo anterior lo hubiéramos podido hacer de otra forma:

Arrastramos el campo “Cliente” de filas y lo llevamos a columnas.

Vamos a otra pregunta:

Pregunta 3. ¿Cuantas ventas hice?

Ya no quiero saber el total de ventas, si no cuantas ventas hice. Es decir ya no quiero saber la suma, sino que quiero contar los campos.

Para esto simplemente tenemos que cambiar el tipo de resumen en el campo valor.

No me molesta seguir clasificándolo por cliente por lo que no voy a quitar el campo “Cliente”. Entonces empezamos de esto:

Vamos simplemente a cambiar la opción del desplegable del campo “Valor”.

Para responder a la pregunta, vamos a escoger la opción “COUNT” o “COUNTA” que en este caso da lo mismo.

Nota: Si no te aparece el “Editor de Tablas Dinámicas”, asegúrense de estar seleccionando alguna de las celdas de la tabla dinámica.

Ahora nos quedaría así nuestra tabla

Respondiendo así nuestra pregunta de cuantas ventas se hicieron.

Ahora podemos ir un paso más allá, y responder las 3 preguntas con la misma tabla.

Para esto simplemente añadimos otra vez las ventas a la sección de Valores

Y nos aseguramos que en el campo “Sumar por” este seleccionada la opción “SUM”

Y ahora quedaríamos con la siguiente tabla dinámica:

Y ya que estamos aquí, agreguemos un poco de formato:

Como ven, estamos respondiendo a las tres preguntas que nos hicimos, más una más:

• ¿Cuánto vendimos en total? $20.323
• ¿Cuánto vendimos a cada cliente? Podemos ver los 6 clientes con sus ventas
• ¿Cuántas ventas se hicieron? 38
• ¿Cuántas ventas se hicieron a cada cliente? También podemos ver una columna con la cantidad de ventas a cada cliente

Encontrarás un Curso Introductorio a Tablas Dinámicas en la academia de Sheets JuanSGuzman

Podemos adicionalmente modificar una de las preguntas de la siguiente manera:

Pregunta 4. ¿A que cliente le vendimos mas? ¿A que cliente le vendimos menos?

Es lo mismo que querer ordenar los clientes de acuerdo las ventas por orden descendente.

Para esto simplemente tenemos que:

1. Dentro de “Cliente”, cambiar el “Ordenar por” del campo “Cliente” por el campo “Ventas”, que está en la sección Filas
2. Cambiar el “Orden” por “Descendente”.

Lo anterior me da:

También podríamos ordenarlo al revés, en orden ascendente. O, ordenarlo por el número de ventas.

Con los datos que tenemos, estas son las preguntas básicas todas respondidas con pocos clics, y sin tener que saber una sola formula. Podrían haber otras como el promedio de las ventas, el máximo de las ventas, pero no mucho más.

Para poder hacernos más preguntas tenemos que incluir más datos, entonces agreguémosle a nuestra base el campo fecha, el campo año, el campo mes y el campo país.

Nota: Tengan en cuenta que ya teniendo la fecha, no necesitaríamos el año, ya que con técnicas avanzadas podemos agruparlo por año, pero para mantenernos básicos por ahora vamos a dejarlos como campos separados.

Así quedaría nuestra base con más campos:

Ahora nos podemos hacer preguntas más complejas.

Pregunta 5. ¿Cuanto vendimos el año pasado, mes a mes?

Para no tener que crear una nueva tabla dinámica desde cero, vamos a ampliar el intervalo de la tabla que ya tenemos.

Después reemplazaríamos el campo de la sección filas de “Cliente” a “Año”.

Hasta ahora nada nuevo, solo estamos repitiendo lo que ya habíamos hecho.

Lo que si vamos a incluir es una “subfila”. Después de año, vamos a incluir el mes. Para esto simplemente tenemos que agregar un nuevo campo a la sección filas, sin eliminar la anterior, tal y como ya lo habíamos hecho para los valores.

Lo que si hay que tener en cuenta, es que el orden en que estén los valores, importa.

Es importante para nuestro caso, que el campo “Año” este primero, y debajo este el campo “Mes”

Resumiendo, entonces los pasos serían:

1. Seleccionamos la tabla dinámica actual.
2. Nos vamos al primer campo del “Editor de Tablas Dinámicas” y actualizamos el intervalo.
3. Eliminamos el campo “Cliente” de la sección Filas
4. Incluimos el campo “Año” en la sección “Filas”
5. Incluimos el campo “Mes” en la sección “Filas”

Y este es el resultado:

Y así, podríamos incluir más “subfilas”, por ejemplo semana, día, etc.

¿Empiezan a ver el potencial de las tablas dinámicas? ¿Cómo con unos pocos clics logramos hacer unos informes extraordinarios?

Nota: Tengan en cuenta que no hemos modificado la sección “Valores”

Otra cosa que podemos ver en este ejemplo, son esos cuadros a la derecha que me vuelven la tabla “más dinámica” por así decirlo. Es decir que me permiten “cerrarlo” o “abrirlo” para entrar a más o a menos detalle.

Por ejemplo podría “cerrar” los dos años y me quedaría así:

O abrir un solo año

Pregunta 6. ¿Cuánto vendió mes a mes durante el 2019 el Cliente B?

Vamos a responder una pregunta más solo para ver como interactúan las columnas y las filas.

Para poder responder esto, podemos hacer una combinación entre las preguntas anteriores.

Para responder esto quiero volver a agregar el campo “Cliente” a mi tabla, para poder ver todos mis clientes mes a mes.

Aquí es donde nuestras tablas dinámicas empiezan a personalizarse. Hay muchas opciones para mostrar lo mismo..

Una primera opción es pasar el campo “Cliente” a “Columnas”

Voy a quitar el numero de ventas para que no se asusten con la complejidad, lo que me da:

Aquí vemos entonces una matriz bidimensional donde estamos respondiendo todas las preguntas que nos hemos hecho hasta ahora.

Otra opción es “subir” el campo año y el campo mes a Columna, y dejar cliente en filas. Es exactamente lo mismo, pero visto de otra forma.

Depende mucho de como queramos mostrar la información.

Otra opción, es agregar cliente como otra “subfila”:

Esta no me gusta mucho, pero puede haber casos en que sea útil.

Ahora es un buen momento para hablar de los totales.

Totales y Subtotales en Tablas dinámicas

A veces pueden ser necesarios o innecesarios los totales y subtotales en nuestras tablas dinámicas. Por ejemplo, en el ejemplo anterior me gustaría ver el total del año, pero no el total por mes, ni el “total total”.

En nuestro “Editor de Tablas dinámicas” en cada campo que hemos puesto ya sea en Filas o Columnas, tenemos una casilla de “Mostrar totales”.

Vamos a “deseleccionar” la de cliente y la de año. Este es el resultado:

Mucho más limpio ¿no?

Otra vez, depende del caso y de lo que queramos mostrar.

Pero ahora volvemos a la pregunta ¿Cuanto vendió mes a mes durante el 2019 el Cliente B?

En este momento vemos un buen panorama total, pero ¿Qué pasa si yo tuviera 100 clientes, y 20 años, y solo quiero mostrar el cliente específico, y solo para 2019?

Aquí es donde es útil la parte que nos falta explorar de las tablas dinámicas: los filtros.

Usando Filtros en nuestras tablas dinámicas

Volvamos al cruce de fechas y clientes que ya habíamos hecho, solo por que me gusta más.

Nota: Acuérdense que es muy fácil volver las filas columnas y viceversa, simplemente arrastrando el campo de una sección a otra en nuestro “Editor de Tablas dinámicas” (el panel a la derecha).

Los filtros funcionan igual que cualquier otro filtro en Sheets.

Lo que vamos a hacer es agregar un filtro para años, y escoger el 2019.

Luego vamos a agregar un filtro para clientes y escogemos al Cliente B.

Esto se hace igual que como lo hemos hecho con los valores.

Entonces los pasos son:

1. Escogemos la tabla dinámica, o cualquier celda de la misma.
2. Vamos al “Editor de Tablas Dinámicas”. En la sección Filtros, damos clic en el botón “añadir”
3. Escogemos el campo “año”
4. Esto nos crea un filtro para año. Vamos a seleccionar el filtro, dar clic en Borrar, y después, escoger el año deseado, en este caso el 2019.

Nos queda así:

Ahora repetimos los mismos pasos pero para el campo Cliente, y escogemos al “Cliente B”. Y nos queda así:

Conclusión

Como ven, aunque el tutorial ha estado largo, esto se hace en cuestión de minutos. Con las tablas dinámicas podemos resumir nuestra información como queramos. Es simple cuestión de imaginación y de hacernos las preguntas correctas.

Nos quedan faltando temas avanzados que podemos resolver en tutoriales mas avanzados, como por ejemplo

• Creando tablas en la misma hoja
• Copiando y pegando tablas dinamicas
• Porcentajes
• Tablas más rápidas con las recomendaciones de Google Sheets
• Formulas especificas para tablas dinámicas
• Formateando con temas
• Slicers

Pero con lo que vimos en este artículo tienen el 80% de lo que necesitan para empezar con tablas dinámicas.