Tablas dinámicas

Elegir una tabla dinámica recomendada

Una tabla dinámica permite resumir, analizar, explorar y presentar datos de síntesis. Excel Microsoft 365, además, puede sugerir varias tablas dinámicas que se adaptan particularmente a los datos de la hoja de cálculo, ayudando de este modo al usuario a sintetizarlos y analizarlos.

Excel analiza los datos seleccionados y sugiere algunos tipos de tablas que se adaptan a ellos.

El botón Tabla dinámica en blanco situado en la parte inferior izquierda de la ventana permite, como indica su nombre, ignorar las sugerencias de Excel y definir desde cero la tabla dinámica.

La nueva hoja de cálculo (denominada Hojax) que crea esa tabla...

Crear una tabla dinámica

Una tabla dinámica permite resumir, analizar, explorar y presentar datos de síntesis.

Para usar una de las tablas dinámicas recomendadas por Excel, vaya al apartado Elegir una tabla dinámica recomendada, en este capítulo.

Aparecen las pestañas contextuales Analizar tabla dinámica...

Crear una tabla dinámica basada en varias tablas

Esta función permite listar los campos de varias tablas en el panel Campos de tabla dinámica, lo que facilita la creación de una única tabla dinámica.

Para importar varias tablas en una lista de campos de tabla dinámica, puede importarlas simultáneamente desde una base de datos relacional (véase el capítulo Importar datos - Importar datos de una base de datos de Access), o bien importar las tablas una a una desde una única fuente de datos o desde fuentes de datos diferentes, añadirlas a un modelo de datos de Excel, crear relaciones y utilizar este modelo de datos como base de una tabla dinámica.

En el contexto de este libro, usaremos el ejemplo de la importación de varias tablas desde una base de datos de Access, con las que hemos creado una plantilla de datos de Excel.

Para nuestro ejemplo, hemos seleccionado la opción Tablas del modelo de datos del libro. Nuestro modelo de datos contiene 8 Tablas previamente importadas desde una base de datos...

Administrar los campos de una tabla dinámica

Agregar o eliminar un campo

 Haga clic con el botón secundario en el nombre del campo (sección de Campos del panel Campos de tabla dinámica) y seleccione la opción correspondiente a la zona en que desea colocar el campo:

Agregar a filtro de informe	Para que los valores de este campo se propongan, en forma de lista, como criterios de filtro de la tabla dinámica.
Agregar a etiquetas de fila	Para que los valores de este campo se conviertan en encabezados de fila de la tabla.
Agregar a etiquetas de columna	Para que los valores de este campo se conviertan en encabezados de columna de la tabla.
Agregar a valores	Para que los valores de este campo se utilicen con objeto de rellenar las diferentes celdas de la tabla dinámica. Por defecto, Excel utiliza la función SUMA para resumir los datos numéricos, y la función CONTAR para los datos de tipo texto. Observe que es posible cambiar esta función de resumen, igual que es posible efectuar un cálculo personalizado con esos datos (véase Modificar la función de resumen o el cálculo personalizado de un campo).

Esta tabla dinámica permite mostrar la suma de las ventas por equipo y por región. Los campos situados en la lista dinámica aparecen en las Áreas correspondientes del panel Campos de tabla dinámica.

También es posible arrastrar el campo...

Insertar un campo calculado

Como ejemplo de esta función, vamos a calcular porcentaje del 15% sobre las ventas.

Los valores del campo calculado aparecen en la tabla; el nombre del nuevo campo puede verse en la lista de los campos. Observe también que, por defecto, la función SUMA se ha aplicado automáticamente a este campo.

Modificar la función de resumen o el cálculo personalizado de un campo

Recordemos que, por defecto, se aplica la función SUMA a los datos numéricos de la zona VALORES y la función CONTAR a los datos de tipo texto.

Se abre el cuadro de diálogo Configuración de campo de valor.

También puede acceder a este cuadro de diálogo mediante la pestaña Analizar tabla dinámica - grupo Campo activo - botón Configuración de campo.

La zona Nombre personalizado contiene el nombre del campo activo, o el nombre del origen si no se ha definido ningún nombre personalizado, precedido del tipo del cálculo efectuado.

Utilizar los campos de totales y de subtotales

Para ilustrar este apartado, vamos a utilizar una tabla dinámica simple en la que se calcularán por defecto las sumas de las ventas (zona Valores) por Equipo, Categoría y por Región. 

Filtrar una tabla dinámica

Para filtrar los datos de una tabla dinámica a partir de uno o varios campos, puede utilizar un filtro normal, que se puede aplicar desde una lista desplegable, o bien utilizar los filtros de segmentación, que proporcionan una representación visual de los datos filtrados.

El campo utilizado para filtrar forma parte de la tabla

 Haga clic en el botón , situado a la derecha de Etiquetas de fila o Etiquetas de columna.

Se abre un menú y en él se muestra la lista de los valores del campo. Todos los valores están marcados y, por lo tanto, aparecen en la tabla.

 Para quitar uno o varios valores de la tabla, desmarque la opción correspondiente. Para conservar un solo valor, desmarque la opción (Seleccionar todo) y luego marque el valor que desee mostrar.

 Haga clic en ACEPTAR.

En este ejemplo, la columna Centro ya no se ve en la tabla. El botón , que aparece a la derecha de Etiquetas de columna, indica que se ha aplicado un filtro a este campo. El símbolo también aparece en la lista de los campos de la tabla dinámica.

 Para cancelar este filtro, haga clic en el botón o en el símbolo que aparece en la lista de los campos y luego en la opción Borrar filtro de «campo».

 Para filtrar en función de los valores de la tabla dinámica, haga clic en el botón y luego en la opción Filtros de valor:

Haga clic entonces en la opción que le permita definir el intervalo de valores que se emplearán en el filtro. Por ejemplo, para filtrar los valores comprendidos entre 10.000...

Agrupar los datos de una tabla dinámica

Esta función le permite agrupar valores de uno o varios campos por criterios de selección o por campo.

Desde la versión 2016, los datos pertenecientes a los campos de tipo Fecha y hora añadidos en Columnas o en Filas se agrupan automáticamente por años, trimestres y meses. Puede deshacer este agrupamiento para ver de nuevo las fechas y aplicar el agrupamiento que mejor le convenga.

Tomemos el ejemplo de una tabla dinámica presentada por Equipo y Fechas.

Agrupar por campo

 Para resumir elementos de tipo numérico o de fecha en el informe, haga clic en una de las etiquetas de fila o de columna correspondientes al campo de la tabla dinámica que desea agrupar.

En nuestro ejemplo, vamos a agrupar el campo Fechas por año.

 Active si es preciso la pestaña Analizar tabla dinámica y haga clic en el botón Grupo.

 Haga clic en la opción Crear grupo de campo.

La opción Crear grupo de campo aparecerá en gris (inaccesible) si el elemento seleccionado no es un número, una fecha o una hora.

 En el cuadro de diálogo Agrupar, modifique si es preciso el primer valor que debe integrarse en la agrupación (número, fecha u hora) en la zona Comenzar en, y la última, en la zona Terminar en.

Los valores propuestos por defecto corresponden a los valores mínimo y máximo...

Filtrar las fechas de forma interactiva (filtro de escala de tiempo)

Es posible utilizar una escala de tiempo para mostrar los datos correspondientes a diferentes periodos.

En este ejemplo, hemos aplicado un filtro de escala de tiempo basándonos en el campo Fechas.

Si la barra de desplazamiento no incluye ningún cursor, puede señalar el extremo de la barra central (azul) y arrastrarlo hasta el periodo deseado.

En este ejemplo, la tabla dinámica muestra únicamente los valores cuya fecha está comprendida entre el 1 y el 31 de marzo de 2020.

Cambiar el diseño y la presentación de una tabla dinámica

Cambiar el formato de un campo de fila

 Para cambiar el formato de un campo de fila, selecciónelo y haga clic en el botón Configuración de campo del grupo Campo activo de la pestaña Analizar tabla dinámica.

También puede hacer doble clic en el campo de fila correspondiente en modo esquema o tabular.

 Active la pestaña Diseño e impresión.

 Para mostrar los elementos del campo de forma jerárquica, active la opción Mostrar etiquetas de elementos en formato esquemático. A continuación puede pedir que se muestren (u oculten) las etiquetas del campo siguiente en la misma columna de forma compacta marcando la opción...

Usar las ideas de análisis propuestas por Excel

Esta función nos da la posibilidad de analizar nuestros datos con modelos de tendencias y resúmenes visuales adecuados.

A la derecha de la ventana aparece el panel Analizar datos.

Puede refinar sus ideas seleccionando únicamente los campos que desea consultar.

La opción No es un valor corresponde a campos que generalmente no se calculan.

En nuestro ejemplo, el análisis de datos se realiza en los campos Equipo, Región y ventas resumidos por valores calculados con ayuda de la función Suma.

Si ha seleccionado insertar una tabla dinámica, se coloca en una hoja nueva con el nombre Sugerencia1. Mientras que si ha elegido insertar un gráfico, este se coloca en la hoja de cálculo activa.

La función Analizar datos...