Cómo crear una tabla dinámica en Excel

Las tablas dinámicas son, sin duda, la herramienta más potente de Excel para resumir datos de forma sencilla y totalmente intuitiva. Se trata de tablas que resumen datos de una tabla o lista en forma de tabla y que podemos configurar de forma totalmente personalizada para hacer cualquier tipo de informe sin necesidad de saber hacer funciones o fórmulas. Tienen la gran ventaja de que se actualizan conforme introducimos nuevos datos en la tabla de origen que contiene los datos y, además, es posible hacer gráficos sobre las tablas dinámicas que también se actualizarán automáticamente conforme tengamos nuevos datos, los gráficos de tablas dinámicas reciben el nombre de gráficos dinámicos.

Insertar tablas dinámicas

En el apartado de “insertar” del menú superior de Excel tenemos los dos apartados para insertar tanto tablas como gráficos dinámicos, la tabla dinámica se inserta desde el apartado de tablas.

En cuanto a la inserción de tablas dinámicas tenemos dos opciones, la de tabla dinámica a secas, con la que podremos estructurar libremente o la de tabla recomendadas, en la que Excel nos facilitará algunas opciones que facilitan la construcción de tablas. La primera opción nos llevará al siguiente cuadro de diálogo.

En primer lugar, introduciremos el rango de celdas que contiene los datos que vamos a resumir con la tabla. Puede ser un rango de celdas con o sin formato de tabla o incluso otra tabla dinámica. Además, es posible obtener los datos directamente de una fuente externa a través de la segunda opción o un modelo de datos.

En segundo lugar, debemos introducir dónde queremos situar la tabla dinámica. Podemos situarla en una nueva hoja, o bien, en la misma hoja en la celda que escribamos en el cuadro de texto de “ubicación”. Sólo con estos dos datos ya tenemos la tabla dinámica insertada, aunque no tiene ni estructura ni contenido. En este momento veremos cómo los menús y opciones han cambiado. Por un lado, tenemos la tabla dinámica insertada sin estructura, que tiene este aspecto.

En segundo lugar, se han activado dos secciones adicionales en el menú superior, una de “análisis de tabla dinámica” que permite ejecutar todas las configuraciones de tabla y la de “diseño”, que permite configurar el estilo y algunos datos de su estructura.

Por último, veremos un menú de opciones a la derecha de la hoja con el que podemos construir la estructura de la tabla dinámica y su contenido, que sería el siguiente paso. Para estructurarla arrastraremos los campos que vemos en la parte superior hacia una de las 4 opciones que tenemos más abajo: filtros, filas, columnas, valores. No es obligatoria que haya un campo en cada parte de la tabla dinámica, el único dato imprescindible es el de valores.

• Filas y columnas son dos de las categorías de nuestros datos.
• Valores: aquí pondremos el dato que queremos visualizar teniendo en cuenta que el dato corresponderá al de la fila y columna.
• Filtros: permite filtrar la información en base a alguna categoría.

El ejemplo de datos de esta tabla dinámica contiene información de una tabla con sobre ventas del equipo comercial de una empresa con datos sobre fecha, producto, zona, canales de venta, nombre del comercial e importes. Un resumen que nos podría interesar sería el de importe vendido por meses y zonas geográficas, por ejemplo, en ese caso lo estructuraremos así.

Y la tabla dinámica obtenida será esta de aquí. En las filas tendremos las zonas, en las columnas los meses y, como información calculada, en este caso de suma, el importe total vendido. Además, por defecto, Excel calculará los totales, tanto por fila como por columna de forma que, en nuestro ejemplo veremos las ventas totales llevadas a cabo en cada zona y las ventas totales registradas en cada mes.

Podemos también asignar, por ejemplo, el año como elemento filtro, de forma que veamos los datos según el año seleccionado del desplegable y, además, asignar una subcategoría a las zonas de forma que podamos ampliar el detalla de cada zona y ver, por ejemplo, los datos por cada comercial dentro de la zona. La estructura sería esta.

Con esta estructura la tabla dinámica quedará así. Vemos la subcategoría por comercial y un resumen (suma) de datos de cada zona y de cada comercial junto al filtro por años en la cabecera.

El desplegable de filtro funciona exactamente igual que el que hemos visto en el capítulo de filtros. Basta con seleccionar uno o varios de los elementos de la lista para que los datos de la tabla dinámica cambien.

Otra cosa muy interesante de las subcategorías, además de que también la podemos utilizar en las columnas, es que podemos cerrar el detalle con los botones de contraer y expandir situados a la izquierda de cada categoría, y sólo abrirlos cuando necesitemos el detalle. En el caso anterior, si cerramos todas las categorías la tabla dinámica quedará más compacta, como se muestra en la imagen.

Por último, podemos personalizar el formato de los campos con la flecha desplegable que hay en el campo asignado a las diferentes áreas.

Entre las opciones que tenemos podemos mover el campo dentro del área o entre las áreas con las dos primeras secciones del menú, quitar el campo o configurar el campo con la última opción.

El menú de configuración es diferente en función de si el campo forma parte del área de valores o de las áreas de filtro, columna o fila. Las opciones de configuración de un campo que se encuentra en el área de valores son las siguientes.

Podemos cambiar el nombre del campo, resumir los valores por varias funciones diferentes (recuento, promedio, máximo, mínimo, producto, contar, desviación estándar y varianza), cambiar el formato del número con el botón inferior izquierdo o mostrar los valores como cálculo a través de la pestaña que lleva este nombre, con la que accedemos a este cuadro de diálogo.

Del desplegable central podemos seleccionar varias opciones de cálculo para los datos como mostrar los datos como porcentaje del total, entre otros. Algunas de las opciones nos permiten escoger un elemento base, es decir, un elemento que actúa como divisor. Para hacerlo sólo tenemos que escoger en qué campo se encuentra la base y después, de la lista de la derecha, seleccionar el dato que actuará como base.