Ediciones ENI Editorial | líder en informática
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.
Si el rango de celdas en el que se basa la tabla dinámica contiene encabezados de columna o ya está organizado en una tabla de Excel, haga clic en una de las celdas. De lo contrario, seleccione el rango de celdas que desea tratar en la tabla dinámica.
Asegúrese de que sus datos no contienen filas vacías.
Active la pestaña Insertar y
haga clic en el botón Tablas dinámicas recomendadas
del grupo Tablas.
Excel analiza los datos seleccionados y sugiere algunos tipos de tablas que se adaptan a ellos.
Haga clic en una de las disposiciones de tablas dinámicas de la parte izquierda de la ventana para obtener una vista previa en la parte derecha.
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.
Si la tabla dinámica propuesta se adapta a lo que necesita, haga clic en el botón Aceptar.
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.
Si el rango de celdas a las que afecta la tabla dinámica contiene encabezados de columnas o ya está organizado en una tabla de datos, haga clic en una de las celdas de la tabla; en caso contrario, seleccione el rango de celdas que se van a tratar en la tabla dinámica.
Asegúrese de que los datos no contienen filas vacías.
Active la pestaña Insertar y
haga clic en el botón Tabla dinámica
del grupo Tablas.
Asegúrese de que el cuadro Seleccione una tabla o rango contenga
exactamente los datos que se deben analizar; en caso contrario,
utilice el botón 
para seleccionarlos.
Elija entre ubicar la tabla dinámica
en una nueva hoja o en una de las hojas del libro. Para ello, active
la opción Nueva hoja de cálculo en
el primer caso, o la opción Hoja de cálculo existente en
el segundo; si ha elegido esta última opción,
utilice el botón para activar
la primera celda en la que se colocará el informe.
Haga clic en el botón Aceptar.
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.
Active la pestaña Insertar, haga clic en el menú desplegable del botón Tabla dinámica.
Active la opción Desde Datos externos de origen.
Haga clic en el botón Elegir conexión del cuadro de diálogo Tabla dinámica desde un origen externo.
Active la ficha Tablas y haga clic en el elemento que desee.
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
Para añadir un campo calculado a la tabla dinámica, haga clic en la tabla dinámica con objeto de que se activen las herramientas específicas, vaya al grupo Cálculos de la pestaña Analizar tabla dinámica y luego haga clic en el botón Campos, elementos y conjuntos.
Haga clic en la opción Campo calculado.
En el cuadro de diálogo Insertar campo calculado, escriba el Nombre del nuevo campo calculado.
Introduzca la Fórmula que elija; para integrar uno de los campos existentes, seleccione el campo correspondiente en la lista Campos y luego haga clic en el botón Insertar campo. Complete la fórmula según el cálculo que se deba efectuar.
Como ejemplo de esta función, vamos a calcular porcentaje del 15% sobre las ventas.
Haga clic en el botón Aceptar para confirmar.
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.
Para eliminar un campo calculado, abra la lista Nombre del cuadro de diálogo Insertar campo calculado (botón Cálculos - opción Campos, elementos y conjuntos - botón Campo calculado) para seleccionarlo; luego haga clic en el botón Eliminar. También puede hacer clic...
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.
Para cambiar la función de resumen utilizada o efectuar un cálculo personalizado con objeto de tratar los datos de un campo de la zona Valores, haga clic en el nombre del campo correspondiente en esta zona y active la opción Configuración de campo de valor.
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.
Para cambiar la función de resumen, active la ficha Resumir valores por y, en el cuadro Resumir campo de valor por, haga clic en la función de resumen que desee.
Para utilizar un cálculo personalizado, active la ficha Mostrar valores como y abra la lista desplegable Mostrar valores como.
Luego haga clic en el cálculo personalizado que más le convenga:
-
% de: para mostrar el valor en forma de porcentaje del valor del Elemento base en el Campo base.
-
Diferencia...
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.
Seleccione uno de los elementos del campo de fila o de columna para el que desea añadir subtotales o totales.
En el grupo Campo activo, haga clic en el botón Configuración de campo (pestaña Analizar tabla dinámica) para mostrar el cuadro de diálogo Configuración de campo.
Para calcular el subtotal de la etiqueta de fila o de columna seleccionada usando la función de resumen por defecto (SUMA para los valores numéricos, CONTAR para los valores de tipo texto), active la opción Automáticos del cuadro Subtotales.
Para calcular los subtotales usando una función diferente, o bien calcular varios tipos de subtotales, active la opción Personalizados y escoja la función o las funciones que quiere utilizar, usando la tecla Ctrl para seleccionar varias funciones si es preciso.
Para cambiar el modo de visualización de los subtotales, active la ficha Diseño e impresión; active, si no lo está, la opción Mostrar etiquetas de elementos en formato esquemático y marque la opción Mostrar subtotales en la parte superior de cada grupo. Confirme mediante...
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.
, 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.
Haga clic en la tabla dinámica.
Haga clic en el botón Insertar escala de tiempo del grupo Filtrar de la pestaña Analizar tabla dinámica.
En el cuadro de diálogo Introducir escalas de tiempo, marque el campo o los campos de tipo fecha que quiere utilizar para aplicar el filtro cronológico y confirme pulsando Aceptar.
En este ejemplo, hemos aplicado un filtro de escala de tiempo basándonos en el campo Fechas.
Para cambiar el período aplicado a este filtro, abra la lista de la periodicidad actual (MESES en nuestro ejemplo), y seleccione la opción AÑOS, TRIMESTRES, MESES o DÍAS.
Para moverse por la línea de tiempo, puede utilizar la barra de desplazamiento horizontal situada en la parte inferior de la ventana.
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.
El filtro de línea de tiempo se administra como un filtro de segmentación (véase Filtrar una tabla dinámica - Filtrar una tabla dinámica usando un filtro...
Cambiar el diseño y la presentación de una tabla dinámica
Cambiar el formato de la tabla dinámica
Para cambiar el formato de la tabla dinámica, active la pestaña Diseño y haga clic en el botón Diseño de informe del grupo Diseño; active la opción:
-
Mostrar en forma compacta para mantener los campos de la izquierda en una columna aplicándoles una sangría a fin de mostrar la relación de anidación entre los datos conectados (forma activada por defecto).
-
Mostrar en forma de esquema para presentar los datos según el estilo habitual de una tabla dinámica.
-
Mostrar en formato tabular para presentar los datos en un formato de tabla tradicional.
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...
Volver a calcular una tabla dinámica
Cuando el rango de celdas de origen de la tabla dinámica se modifica, estos cambios no se reflejan automáticamente en la tabla: es preciso actualizarla.
Haga clic si es preciso en la tabla dinámica.
Active la pestaña Analizar tabla dinámica y haga clic en la parte inferior del botón Actualizar del grupo Datos.
Haga clic en la opción Actualizar para actualizar la información del libro que provenga del origen de datos o en la opción Actualizar todo para actualizar toda la información que provenga de la fuente datos.
También puede utilizar la opción Actualizar del menú contextual de la tabla dinámica.
Eliminar una tabla dinámica
Haga clic dentro de la tabla dinámica.
Active si es preciso la pestaña Analizar tabla dinámica.
Haga clic en el botón Borrar del grupo Acciones y luego en la opción Borrar todo.
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.
Seleccione una celda dentro del rango de datos que va a analizar, luego active la pestaña Inicio y haga clic en el botón Analizar datos del grupo Análisis.
A la derecha de la ventana aparece el panel Analizar datos.
Puede refinar sus ideas seleccionando únicamente los campos que desea consultar.
En el panel Analizar datos,
haga clic en el boton 
.
En la lista Incluir campos, seleccione los que se deben usar y en la lista Resumir valor por, defina la función de cálculo (Suma o Promedio).
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.
Haga clic en el botón Actualizar.
Haga clic en el enlace Insertar tabla dinámica, Insertar gráfico, etc. en la parte inferior de la miniatura correspondiente al análisis que desea insertar en el libro.
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...