¡Acceso ilimitado 24/7 a todos nuestros libros y vídeos! Descubra la Biblioteca Online ENI. Pulse aquí
¡Acceso ilimitado 24/7 a todos nuestros libros y vídeos! Descubra la Biblioteca Online ENI. Pulse aquí
  1. Libros
  2. Microsoft® Office 2021 : Word, Excel, PowerPoint, Outlook
  3. Tablas dinámicas
Extrait - Microsoft® Office 2021 : Word, Excel, PowerPoint, Outlook Domine las funciones avanzadas de la suite Microsoft
Extractos del libro
Microsoft® Office 2021 : Word, Excel, PowerPoint, Outlook Domine las funciones avanzadas de la suite Microsoft Volver a la página de compra del libro

Tablas dinámicas

Elegir una tabla dinámica recomendada

Una tabla dinámica permite resumir, analizar, explorar y presentar datos de síntesis. Excel 2021, 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 recomendadasimages/xl12RB292101.png del grupo Tablas.

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

images/xl29_01.png

 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 dinámica muestra...

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 images/xlrIC-09.png del grupo Tablas

Observe que la lista asociada al botón Tabla dinámica también ofrece la opción Desde Datos externos de origen.

images/xl29_03.png

 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 images/xlIC-005n.png 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 images/xlIC-005n.png para activar la primera celda en la que se colocará el informe....

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, 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 la lista del botón Tabla dinámica y luego elija 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 10 Tablas previamente importadas desde una base de datos de Access.

images/xl29_08.png

 Haga clic en el botón Abrir.

 Elija a continuación entre crear la nueva tabla dinámica...

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 los gastos de inscripción por deporte y por ciudad. Los campos situados en la lista dinámica aparecen en las Áreas correspondientes del panel Campos de tabla dinámica.

images/xl29_10.png

También es posible arrastrar...

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, haga clic a continuación en el botón Campos, elementos y conjuntos de la pestaña Analizar tabla dinámica - grupo Cálculos.

 Haga clic en el botó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 el importe de los gastos de inscripción con un incremento del 3 %.

images/xl29_15.png

 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.

images/xl29_16.png

Para eliminar un campo calculado, abra la lista Nombre del cuadro de diálogo Insertar campo calculado (grupo Cálculos - opción Campos, elementos y conjuntos - opción Campo calculado) para seleccionarlo; luego haga 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.

images/xl29_17.png

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.

images/xl29_18.png

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...

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 los gastos de inscripción (campo Total general) por Deporte y porCiudad.

images/xl29_22.png

 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.

images/xl29_23.png

 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ático del cuadro Subtotales.

 Para calcular los subtotales usando una función diferente, o bien calcular varios tipos de subtotales, active la opción Personalizado 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 Aceptar.

Para saber más acerca...

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 images/xltab01.PNG, 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.

images/xl29_27.png

 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 Zamora ya no se ve en la tabla. El botón images/xltab02.PNG, que aparece a la derecha de Etiquetas de columna, indica que se ha aplicado un filtro a este campo. Este símbolo también aparece en la lista de los campos de la tabla dinámica.
images/xl29_28.png

 Para cancelar este filtro, haga clic en el botón images/xltab02.PNG 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 images/xltab01.PNG y luego en la opción Filtros de valor:

images/xl29_29.png

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 1.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 Deporte y Fecha de competición

images/xl29_35.png

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 Fecha de competición por año.

 Active si es preciso la pestaña Analizar tabla dinámica - grupo Grupo y haga clic en el botón Crear grupo de campo de la lista desplegable Grupo.

images/xl29_36.png

Haga clic en la opción Agrupar campos.

La opción Agrupar campos 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...

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 Fecha de competición.

images/xl29_41.png

 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 de competición está comprendida entre el 1 y el 30 de septiembre de 2020.

images/xl29_42.png

El filtro de línea de tiempo se administra como un filtro de segmentación (véase Filtrar una tabla dinámica...

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.

images/xl29_43.png

 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 Mostrar...

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.