Trabajar con tablas dinámicas en Excel

Ejemplo real

En este tutorial utilizaremos un conjunto de datos sobre ventas globales de bicicletas entre 2011 y 2016. Este dataset incluye información demográfica de clientes y productos junto con columnas de beneficio, coste e ingresos. Crearás una tabla dinámica a partir de este dataset para poder analizar los datos.

Los datos están disponibles aquí para que puedas seguir el paso a paso.

Revisar los datos

Empecemos revisando una muestra de los datos para este tutorial. Tenemos una tabla con 11 columnas, que incluyen campos de tipo fecha, texto y numérico. A partir de este subconjunto de datos, vemos muchas formas de trabajar con la información para realizar el análisis y encontrar insights de valor.

Crear tu primera tabla dinámica

Para crear tu primera tabla dinámica, selecciona la tabla de la que quieres partir, ve a la pestaña Insertar y elige Tabla dinámica entre las opciones.

Una vez hecho esto, verás una ventana emergente que te preguntará si quieres cambiar el rango de datos del que crear la tabla dinámica y si quieres que se cree en una nueva hoja o en una hoja existente. Para este tutorial, las opciones por defecto nos sirven. Haz clic en Aceptar y crea la tabla dinámica en la nueva hoja.

En la nueva hoja verás la estructura base de la tabla dinámica que se ha creado automáticamente. A la izquierda de la pantalla aparece el contorno que indica dónde se mostrará la tabla cuando esté configurada. A la derecha, verás el panel de campos de la tabla dinámica, donde harás la mayor parte del trabajo.

Cuando tus datos ya están en una tabla de Excel, la tabla dinámica incluirá automáticamente todo su contenido. No obstante, si tus datos no están dentro de un objeto tabla, puedes seleccionar manualmente qué quieres incluir resaltando el rango y creando la tabla dinámica del mismo modo que antes.

Añadir tus datos

Supondremos que cada fila está asociada a un cliente individual; por tanto, no hay pedidos repetidos. Es una visión muy simplificada del conjunto de datos, pero como no hay un identificador único de cliente, para este tutorial será más sencillo asumir un único cliente por fila.

Primero, vamos a llevar un campo de categoría a la sección de Filas. Usaremos Country como categoría; búscalo en la lista de campos del editor de la tabla dinámica y arrástralo a Filas. Después, necesitamos el valor que queremos evaluar: en este caso, queremos contar clientes, pero como no tenemos un campo de ID, podemos usar el campo Customer_Age, que arrastraremos a la sección de Valores. De forma predeterminada, la agregación es SUMA, pero debemos cambiarla a CONTAR. Para corregirlo, haz clic derecho en Customer_Age dentro de Valores, selecciona Configuración de campo, cambia Resumir de Suma a Recuento y haz clic en Aceptar.

¡Buen trabajo! Ahora podemos ver un recuento de clientes por Country desde el que hicieron el pedido. Está bien, pero organicémoslo por Country con más clientes. Para ello, haz clic derecho en cualquier valor de la columna Count of Customer_Age, elige Ordenar y luego Ordenar de mayor a menor. Vemos que United States tuvo más clientes, con 39.206.

Un paso más

Ahora que ya sabemos configurar una tabla dinámica básica, vayamos un poco más allá y añadamos filtros y columnas, y veamos cómo actualizar las tablas dinámicas. Quitaremos las filas y valores añadidos en el paso anterior haciendo clic derecho sobre ellos y seleccionando Quitar campo. Ya tenemos un lienzo en blanco.

Para esta tabla dinámica, queremos responder a la pregunta: «En 2015, ¿qué trimestre generó más ingresos y a qué producto/subcategoría pertenecía?»

Empecemos añadiendo las filas por las que queremos analizar: en nuestro caso, serán Product_Category y Sub_Category. A continuación, para las columnas queremos ver el desglose por Quarters. Pero espera, no tenemos una columna Quarters... no pasa nada; Excel ha detectado automáticamente una fecha en los datos, así que cuando arrastres Date a Columnas, verás que aparecen dos campos nuevos: Years y Quarters. Como no necesitamos ver los datos a nivel de fecha individual, podemos quitar ese campo de la sección Columnas.

Como queremos filtrar por 2015 para nuestra pregunta, podemos mover el campo Years a la sección Filtros. Tu tabla dinámica debería verse así:

Ahora que tenemos la estructura, podemos preseleccionar el año haciendo clic en el desplegable de Years y eligiendo 2015. Así quedará nuestra tabla dinámica.

Actualmente, nuestros datos están a un nivel unitario: el precio y el coste unitarios se asocian a un solo artículo. Queremos resumir la información correctamente, así que debemos volver de la tabla dinámica a la tabla de datos.

En la hoja de datos, crearemos una nueva columna calculada para obtener los ingresos por fila. Para el cálculo, multiplicaremos Order Quantity por Unit Price.

Para añadir una nueva columna, vuelve a la hoja de datos y, junto a la columna Unit Price, en la fila L2, añadiremos el nuevo cálculo; la fórmula será:

[@[Order_Quantity]]*[@[Unit_Price]]

Ahora tenemos una columna nueva que podemos usar en la tabla dinámica. Vuelve a la hoja de la tabla dinámica y verás que el campo aún no aparece. Para actualizarla, haz clic derecho en cualquier campo y selecciona Actualizar.

Se ha añadido la nueva columna y podemos arrastrarla a la sección Valores. Si quieres añadir más columnas, puedes usar nuestra chuleta de Excel para ver otros tipos de cálculos que puedes aplicar.

Está un poco desordenado, así que vamos a limpiarlo quitando todos los totales generales. Ve a la pestaña Diseño, abre el desplegable de Totales generales y elige Desactivados para filas y columnas. Después, actualicemos las cifras para que muestren moneda. Selecciona los valores de la tabla y, en la pestaña Inicio, cambia el formato a Moneda.

Perfecto, ya tenemos nuestra tabla dinámica final. A partir de ella, vemos que las bicicletas generaron los mayores ingresos en 2015, especialmente en el cuarto trimestre (Q4). Además, dentro de las bicicletas que vende la empresa, las de carretera (Road Bikes) son las más populares y las que más ingresos generan.

¡Sigue explorando!

Este tutorial ha sido una buena introducción a las tablas dinámicas en Excel; si has podido seguirlo sin problemas, ¡enhorabuena! Si te has atascado en algún punto, puedes encontrar el archivo con la solución aquí.

Prueba a experimentar con un conjunto de datos más complejo, aplicando distintos atributos, jugando con ellos y viendo si puedes dar sentido a la información.

Si quieres seguir poniendo a prueba tus habilidades en Excel, échale un vistazo a nuestro curso Data Analysis in Excel, si aún no lo has hecho.