➤ Tabla dinámica de Excel: ejemplo, crear, actualizar ... 🕹

¿Tiene grandes listas de datos y desea resumirlas en un informe? ¿O analizarlos según diferentes puntos de vista y criterios de búsqueda? Excel contiene una herramienta especialmente dedicada a estas tareas: ¡la tabla dinámica!

Cuando se trabaja con grandes cantidades de datos, es común querer sintetizarlos, ya sea para obtener una visión global y rápidamente comprensible de un conjunto de valores o, por el contrario, para estudiar un subconjunto con mayor precisión que cumpla con criterios específicos.

Por supuesto, puede tomarse el tiempo para ordenar, filtrar y luego informar manualmente sus datos valiosos en una nueva tabla que satisfaga sus necesidades. Sin embargo, cada vez que agregue o elimine datos de su lista inicial, correrá el riesgo de que su tabla de destino los "olvide" y tendrá que verificar periódicamente sus fórmulas de cálculo para evitarlo. Además, si desea agrupar y resumir sus datos de acuerdo con diferentes criterios, entonces tendrá que modificar manualmente la estructura de su tabla de destino, o incluso crear varias tablas de resumen si desea mantener diferentes puntos de vista sobre sus datos. Estas manipulaciones rápidamente resultarán tediosas y una fuente de error.

Afortunadamente, la herramienta de tabla dinámica de Excel le permite realizar todas estas tareas con solo unos pocos clics y sin escribir ninguna fórmula. Con el fin de presentarte los diferentes conceptos necesarios para el uso de tablas dinámicas, esta práctica ficha se acompaña deun libro de trabajo de ejemplo, descargable gratis aquí. Todas las operaciones descritas en esta guía se basan en este archivo: por lo tanto, recomendamos encarecidamente que lo utilice en paralelo con su lectura.

Descargue una tabla dinámica de muestra para Excel

Esta hoja se elaboró con la versión Office 2019 de Excel. Sin embargo, todas las funciones presentadas están disponibles de manera idéntica en las versiones Office 2016 y Microsoft 365 (ex-Office 365) de Excel, excepto por la creación de estilos de tabla dinámica personalizados que no se ofrecen en la versión web de la hoja de cálculo de Microsoft.

¿Por qué utilizar una tabla dinámica?

Está trabajando en una gran lista de datos, por ejemplo, un diario de ventas, y desea conocer rápidamente la suma de los elementos de esta lista agrupándolos simultáneamente según varios criterios, por ejemplo, por categoría de producto y mes de venta. En este caso, el uso de una tabla dinámica le permitirá obtener rápidamente este resultado, elegir cómo se organizarán los datos de resumen y cambiar instantáneamente este diseño cuando lo necesite. El siguiente diagrama ilustra cómo una tabla dinámica sintetiza datos y los organiza visualmente.

Tabla dinámica de Excel: ejemplo, crear, actualizar ...

Hablamos de una "tabla de referencias cruzadas" porque las categorías utilizadas para agrupar los datos se pueden dividir en filas y columnas. En el ejemplo de una tabla dinámica en la parte superior derecha de la imagen, la celda amarilla en la "intersección" de los campos "Enero" y "Categoría 1" devuelve la suma de todos los elementos de la lista de datos inicial que contenían el " Valores de enero "y" Categoría 1 "en las columnas" Mes "y" Categoría ". Dans le cas présent, un seul élément répond à ces deux critères, mais la liste de données pourrait contenir des milliers d'éléments qui seraient alors traités automatiquement par le tableau croisé dynamique, sans que vous ayez besoin de trier ou de filtrer les données au previo.

Hablamos también de una tabla "dinámica" porque, por un lado, su disposición se puede cambiar instantáneamente mediante simples operaciones con el ratón o el teclado y, por otro lado, cualquier adición, modificación o borrado de datos en la lista inicial. se tiene en cuenta automáticamente en el resultado de las agrupaciones.

¿Cómo crear una tabla dinámica con Excel?

Antes de generar una tabla dinámica, debe asegurarse de que los datos de su lista inicial cumplan con los siguientes requisitos previos:

Los datos de la misma columna deben ser todos del mismo tipo (número, fecha, texto, etc.).
La primera fila de su lista de datos debe contener los encabezados de las columnas.
Su lista no debe contener celdas combinadas.
Su lista no debe contener celdas vacías (Excel puede reconocer y manejar celdas vacías si existen, pero es mejor evitarlas tanto como sea posible).

Una vez que se cumplan estas condiciones, puede comenzar a crear su tabla dinámica.

Haga clic en algún lugar de la lista de datos, luego, en la pestaña Inserción en la cinta, haga clic en Tabla dinámica.

En el cuadro de diálogo Crear tabla dinámica, verifique que el campo Mesa / Playa contiene las referencias de la lista de datos. De forma predeterminada, si hizo clic correctamente en algún lugar de la lista y no contiene celdas en blanco ni celdas combinadas, Excel determinó correctamente su tamaño. Si no, haga clic en icono de flecha hacia arriba a la derecha del campo Mesa / Playa y seleccione manualmente toda la lista de datos.
En la sección Elija una ubicación para su informe de tabla dinámica, verifique que el Nueva hoja de cálculo está chequeado. Por supuesto, puede marcar la casilla Hoja de cálculo existente y seleccione manualmente una ubicación en la misma hoja que la lista de datos, pero la legibilidad del conjunto puede ser menos buena.
Valide la creación de la tabla dinámica haciendo clic en el botón OK del cuadro de diálogo Crear tabla dinámica.

Excel inserta una nueva hoja de trabajo en el libro y aparece un panel llamado Campos de tabla dinámica a la derecha de la pantalla.

El panel Campos de tabla dinámica consta de cinco marcos. El primero te muestra la lista de campos de datos disponibles, que corresponde a los encabezados de columna de la lista de datos inicial. Los otros cuatro fotogramas representan el zonas de la tabla dinámica: es moviendo el campos de datos disponibles en lo diferente zonas que va a crear el diseño de tabla dinámica.
En la lista de campos de datos disponibles, marque las casillas a la izquierda de las palabras Región, Producto et Venta. Ha aparecido una tabla dinámica en su hoja de trabajo, y si observa los cuatro marcos inferiores, verá que las palabras Región et Producto se han agregado en el área líneas y que las palabras Suma de ventas apareció en el área Valeurs.

En este punto, ha creado una tabla dinámica que agrupa sus datos por región y luego por nombre de producto y tiene un subtotal para cada categoría, así como un total general en la parte inferior de la tabla. También encontrará que las categorías en la tabla dinámica están organizadas en el mismo orden que los campos de datos en el marco. líneas en el panel Campos de tabla dinámica disponibles. Recuerde este principio, es el corazón del sistema de organización de una tabla dinámica.

En el marco líneas en el panel Campos de tabla dinámica, haga clic en el flecha gris a la derecha del nombre Región y, en el menú contextual que aparece, haga clic en bajar, o haga clic con el botón izquierdo y mantenga presionado el nombre Región y bájalo bajo el nombre Producto antes de soltar el botón del mouse.

En el marco líneas, el apellido Producto ahora está en primera posición y el nombre Región en segundo. En el lado de la tabla dinámica, encontrará que el orden de las agrupaciones de datos y los subtotales también se ha invertido.

Esta simple manipulación le brinda una descripción general del poder y la flexibilidad de una tabla dinámica. En segundos, tiene una perspectiva completamente nueva de sus datos sin necesidad de ordenarlos o reorganizarlos en la lista original.

En el panel Campos de tabla dinámica disponibles a la derecha de la pantalla, en el marco superior, haga clic con el botón derecho en el nombre Mes luego seleccione Agregar a etiquetas de columna, o haga clic con el botón izquierdo y mantenga presionado el nombre Mes y muévelo a la zona Columnas antes de soltar el botón del mouse.

En la hoja de cálculo, la tabla dinámica se ha expandido a tres columnas adicionales y ahora muestra, además de los subtotales por nombre de producto y región, subtotales mensuales para cada una de estas categorías, así como un total general a la derecha de la tabla.

En este punto, ha creado una tabla dinámica completa: a partir de una lista de datos organizados en filas, ha obtenido un informe de resumen que agrupa sus datos en categorías, los divide en filas y columnas y los muestra subtotales. Intente cambiar la distribución de categorías entre filas y columnas para comprender cómo Excel cambia la tabla dinámica.

En el panel Campos de tabla dinámica disponibles, use el mouse para pasar el nombre Mes del Area Columnas a la zona líneas, en la última posición después de los nombres Producto et Región, luego pasa el nombre Producto del Area líneas a la zona Columnas y observe los cambios que se producen en cada paso de la tabla dinámica.

¿Cómo formatear una tabla dinámica con Excel?

Para que su tabla dinámica sea más fácil y agradable de leer, especialmente cuando tiene una gran cantidad de categorías y datos, Excel ofrece (muchísimas) opciones de formato. Por ejemplo, puede elegir un diseño diferente para las categorías, formatos numéricos personalizados para los datos del área de valores, colores para los encabezados y bordes, y mucho más.

¿Cómo cambiar el formato numérico de los valores?

Notarás que los datos en el área Valeurs de la tabla dinámica aparecen como números con dos posiciones decimales, sin un separador de miles, lo que no es muy legible para los valores de moneda. Puede optar por mostrar estos datos en cualquier formato de número disponible en Excel.

En el panel Campos de tabla dinámica disponibles, en el Valeurs, haga clic en el nombre Suma de vientoes entonces Parámetros de campos de valor.

En el cuadro de diálogo Configuración para los campos de valor que se abre, haga clic en el botón Formato numérico.

El cuadro de diálogo Formato de celda se abre. En él, puede seleccionar cualquier formato de celda existente en Excel o crear un formato personalizado haciendo clic en la categoría personnalisé. En este caso, haga clic en la categoría Monetario luego valide su elección haciendo clic en el botón OK en la parte inferior derecha del cuadro de diálogo.

De vuelta en el cuadro de diálogo Parámetros de campos de valor, haga clic en el botón OK en la parte inferior derecha para aplicar su cambio a la tabla dinámica. Datos de zona Valeurs ahora se muestran como valores de moneda, con un separador de miles, dos lugares decimales y el símbolo €.

¿Cómo cambiar el ancho de las columnas?

Al crear una tabla dinámica, el ancho de las columnas se adapta automáticamente al contenido de las celdas, lo que puede no dar el resultado deseado si, por ejemplo, desea que todas las columnas tengan el mismo ancho. Afortunadamente, es muy fácil cambiar el ancho de la columna manualmente y mantenerlo cuando se realizan actualizaciones de tabla dinámica.

Para dar el mismo ancho a varias columnas, seleccione las que desea modificar haciendo clic en su etiqueta (las letras A B C D, etc., ubicado encima de la primera fila de la hoja de trabajo). Por ejemplo, haga clic con el botón izquierdo y mantenga presionada la etiqueta de la columna B, mueva el cursor a la etiqueta de la columna E luego suelte el botón izquierdo del mouse. Todas las columnas de B à E luego se seleccionan.

Luego, mueva el mouse sobre la línea de separación entre las etiquetas de las columnas E et F hasta que el cursor cambie a una doble flecha negra. Luego haga clic con el botón izquierdo y mantenga presionado y mueva el cursor hacia la derecha para aumentar el tamaño de las columnas o hacia la izquierda para reducirlo. Cuando suelta el botón izquierdo del mouse, todas las columnas seleccionadas anteriormente asumen el mismo ancho.

Método alternativo: seleccione las columnas cuyo ancho desea cambiar haciendo clic en su etiqueta (como se explicó anteriormente), haga clic con el botón derecho en cualquier lugar del área de selección y luego elija Ancho de columna desde el menú contextual. Se abre un cuadro de diálogo Ancho de columna en el que puede ingresar directamente un número para definir el ancho de las columnas seleccionadas. Este método es más preciso, pero menos intuitivo que el anterior.

Una vez que haya asignado los anchos deseados a sus columnas, es necesario pasar por las opciones de la tabla dinámica para que estas configuraciones se mantengan al actualizar la tabla. De hecho, de forma predeterminada, Excel ajusta automáticamente el ancho de las columnas a su contenido al actualizar la tabla dinámica. Afortunadamente, esta opción se puede cambiar con unos pocos clics.

En la hoja de trabajo, haga clic en cualquier lugar dentro de la tabla dinámica. Esto abre la pestaña Análisis de tabla dinámica en la cinta. Haga clic en él, luego en el menú Opciones de tabla dinámica y finalmente en el botón de pago.

También puede hacer clic con el botón derecho en cualquier celda de la tabla dinámica y, en el menú contextual, hacer clic en Opciones de tabla dinámica.

Con cualquiera de los métodos descritos anteriormente, el cuadro de diálogo Opciones de tabla dinámica aparece en su pantalla. Tiene varias pestañas y se abre por defecto a la titulada Diseño y formato. Eso es bueno, es el que nos interesa en este caso. Encuentra la caja Ajustar automáticamente el ancho de la columna al actualizar y desmarque. Aprovecha para marcar que la casilla Mantenga el formato de celda al actualizar, justo arriba, está marcado. De esta manera, puede estar seguro de que todo el formato que ha aplicado manualmente se conservará bien al actualizar la tabla dinámica.

¿Cómo cambiar el estilo y el diseño de la tabla dinámica?

Hay muchas posibilidades en Excel para cambiar la forma en que la tabla dinámica organiza visualmente sus datos, como mostrar los totales generales de filas y columnas, dónde se encuentran los subtotales o cómo se anidan las categorías de datos. El software de hoja de cálculo de Microsoft también le permite elegir de una lista de estilos de tabla listos para usar (y bastante elegantes en su mayor parte) que se pueden aplicar instantáneamente a su tabla dinámica, lo que le ahorra la molestia de formatear. Para permitirle concentrarse en el contenido. Y, si ninguno de los estilos predefinidos cumple con sus expectativas, es muy posible crear el suyo propio, que luego estará disponible para todas sus tablas dinámicas futuras. Tenga en cuenta que la creación de estilos personalizados no está disponible en la versión web de Excel. Por otro lado, si creó un estilo de tabla personalizado en un archivo en una versión de escritorio de Excel y luego abre ese archivo en la versión web, su estilo personalizado estará presente y será utilizable, pero no editable.

En la hoja de trabajo, haga clic en cualquier celda de la tabla dinámica para abrir la pestaña creación en la cinta, luego haga clic en la cinta. Esta pestaña tiene tres secciones: Diseño, Opciones de estilo de tabla dinámica y Estilos de tabla dinámica.

En la sección Diseño, el botón Subtotales le permite elegir entre tres opciones de visualización, cuyo nombre se explica por sí mismo: No mostrar subtotales, Mostrar todos los subtotales en la parte inferior del grupo et Mostrar todos los subtotales en la parte superior del grupo (esta es la opción habilitada por defecto). Pruebe cada uno de ellos para ver cómo cambia la apariencia de la tabla dinámica, luego cambie la pantalla a los subtotales en la parte inferior del grupo antes de continuar. Una última opción llamada Incluir elementos filtrados en totales aparece al final de la lista pero está inactivo por el momento porque no ha aplicado ningún filtro a su tabla dinámica. Como sugiere su nombre, permite incluir en los subtotales el valor de los datos ocultos por un filtro, lo que puede dar lugar a errores en la lectura e interpretación de los resultados de una tabla dinámica. Por tanto, debe utilizarse con precaución.

Aún en la sección Diseño, el botón Grandes totales ofrece cuatro posibilidades de visualización: Deshabilitado para filas y columnas, Habilitado para filas y columnas, habilitado solo para filas et Habilitado solo para columnas. Nuevamente, inténtelo varias veces y luego vuelva a la opción Habilitado para filas y columnas antes de seguir adelante.

El siguiente botón de la sección Diseño se llama Diseño de informe. Le permite elegir cómo Excel organiza las categorías de datos que ha colocado en el cuadro. líneas de la tabla dinámica.

La primera opción, Mostrar en forma compacta, es el que se usa por defecto y por tanto el que ves desde el principio. En este modo de visualización, las categorías de datos de la zona líneas se agrupan en una sola columna y se muestran como una estructura de árbol, con una sangría derecha aplicada a cada subcategoría y un subtotal para cada categoría que se puede mostrar en la parte superior o inferior del grupo (ver la sección anterior de esta hoja práctica). Esta disposición ofrece la ventaja de optimizar el espacio que ocupan las categorías en ancho y, por tanto, dejar más espacio para la visualización de valores. Su inconveniente es que agrupa los filtros aplicables a las diferentes categorías en un solo botón (presente en la celda A4 en nuestro ejemplo), lo que puede hacer que el filtrado en varias categorías sea menos legible. Este aspecto se desarrollará más adelante en esta ficha práctica.

La segunda opción disponible, Ver en vista de esquema, es bastante similar al anterior: las categorías de datos de la zona líneas siempre se muestran como una estructura de árbol y se puede mostrar un subtotal para cada categoría en la parte superior o inferior del grupo. Sin embargo, las categorías del área líneas esta vez se distribuyen en varias columnas, una columna por categoría para ser más precisos. Por lo tanto, este modo utiliza más espacio en ancho para la visualización de categorías y puede dar una impresión de "desorden" con las zonas vacías en cada grupo. Sin embargo, dado que cada categoría tiene su propia columna, este modo ofrece la ventaja de asignar un botón de filtro a cada categoría, lo que hace que el filtrado de varios criterios sea muy legible.

La tercera y última opción se llama Ver en forma tabular. Esta vez, las categorías de la zona líneas ya no se muestran en forma de estructura de árbol sino en forma de tabla clásica y, lo que es más importante, los subtotales deben encontrarse en la parte inferior de cada grupo. Como en la pantalla en el modo Plan, cada categoría de la zona líneas se distribuye en una columna individual y, por lo tanto, ofrece su propio botón de filtro.

Finalmente, el botón Diseño de informe le ofrece dos opciones adicionales: Repite todas las etiquetas de los elementos ou No repita las etiquetas de los artículos. Estas dos opciones controlan realmente un solo parámetro, lo que permite activarlo o desactivarlo. De forma predeterminada, la repetición de etiquetas de elementos está desactivada, por lo que verá espacios vacíos en cada grupo de categorías. Si hace clic en la opción Repite todas las etiquetas de los elementos, luego el nombre de cada categoría se repetirá en su columna hasta el siguiente grupo. Esto puede resultar útil si una categoría tiene muchos elementos y su longitud excede la altura de visualización de su mesa. Tenga en cuenta que esta opción no funciona en mostrar en forma compacta.

La sección Diseño tiene un menú final llamado Líneas vacías que contiene dos opciones: Insertar un salto de línea después de cada elemento ou Eliminar salto de línea después de cada elemento. La primera opción inserta una fila en blanco después de cada grupo de categorías, lo que puede ser útil para ventilar su tabla dinámica, especialmente si ha optado por la repetir las etiquetas de los artículos. La segunda opción simplemente elimina las líneas vacías insertadas por la primera.

La siguiente sección de la pestaña creación en la cinta se llama Opciones de estilo de tabla dinámica y contiene cuatro casillas de verificación, cada una de las cuales le permite habilitar una opción de formato específica. Por defecto, las opciones Encabezados de fila et Encabezados de columna están marcadas y las opciones Franja de líneas et Columnas de banda están sin marcar.

Si desmarca la casilla Encabezados de fila, el formato de la primera fila de cada grupo de categorías desaparecerá.

Si desmarca la casilla Encabezados de columna, es el formato de los encabezados de columna de la tabla dinámica lo que luego desaparece.

Las casas Franja de líneas et Columnas de banda producir diferentes efectos dependiendo del estilo de tabla dinámica aplicado. Verá cómo elegir un estilo de tabla más adelante en este instructivo, así que no dude en activar y desactivar estas opciones después de un cambio de estilo para ver cómo afectan el formato de la tabla. En general, las opciones Franja de líneas et Columnas de banda facilitan la distinción entre filas y columnas, mostrando los bordes del separador o aplicando un formato alternativo de filas o columnas. Intente activar y desactivar estas opciones para ver el resultado en el estilo de tabla actual.

La última parte de la sección Diseño de pestañas creación tiene derecho Estilos de tabla dinámica y contiene los famosos estilos de tabla listos para usar que puede aplicar instantáneamente a su tabla dinámica.

La sección Estilos de tabla dinámica le ofrece una primera muestra de los estilos de tabla disponibles, pero hay muchos más: haga clic en el flechas a la derecha de la muestra para desplazarse por los estilos disponibles o en el flecha rematada con una línea para mostrar una lista desplegable de todos los estilos existentes. Al pasar el mouse sobre los estilos de la tabla, sin siquiera tener que hacer clic en ellos, verá que Excel aplica temporalmente el formato a la tabla dinámica, lo que es muy útil para comparar rápidamente las diversas posibilidades disponibles.

Para aplicar permanentemente el estilo de tabla que le gusta, simplemente haga clic en él en la lista desplegable, el formato correspondiente se aplicará a toda la tabla dinámica. Por supuesto, puede cambiar el estilo de la tabla tantas veces como desee simplemente repitiendo el paso anterior.

Tenga en cuenta que los estilos que se ofrecen dependen del esquema de color utilizado en su libro de Excel, y si ninguno de los colores de tabla ofrecidos es adecuado para usted, puede obtener otros nuevos cambiando el esquema de color a través de la pestaña Disposición cinta, sección Temas, menú De color.

Si a pesar de todo no encuentras lo que buscas entre la multitud de estilos de mesa que se ofrecen, es posible crear estilos completamente personalizados. Tenga en cuenta que, como se explicó anteriormente, esta función no está disponible en la versión web de Excel.

En la pestaña creación, en la sección Estilos de tabla dinámica, muestre la lista de estilos disponibles haciendo clic en el flecha rematada con una línea luego, debajo de la lista, haga clic en Nuevo estilo de tabla dinámica. Se abre un cuadro de diálogo con el mismo nombre.

En el campo Apellido, puede asignar un nombre personalizado a su estilo de tabla dinámica. Entonces las cosas se complican un poco más. Cada entrada de la lista Elementos de la tabla representa un área de la tabla dinámica a la que se le puede dar un formato especial. Por lo tanto, debe configurar cada elemento individualmente hasta obtener el resultado deseado, lo que puede llevar más o menos tiempo dependiendo de la complejidad del estilo de tabla que desee lograr.

Para cambiar el formato de un área de la tabla, haga clic en su nombre en la lista de elementos de la tabla, por ejemplo en Filas de encabezado, entonces el botón Formato bajo esta misma lista. Se abre un cuadro de diálogo Formato de celdas. Contiene tres pestañas, Police, frontera et relleno, que le permiten establecer varias configuraciones de formato. Funcionan exactamente igual que al formatear las celdas directamente en una hoja de trabajo. Experimente y aplique diferentes formatos y luego haga clic en el botón OK en la parte inferior derecha del cuadro de diálogo Formato de celdas. Repita la operación varias veces con otras entradas en la lista de elementos de la Tabla. Cuando haya terminado, observe la sección Vista previa a la derecha del cuadro de diálogo Nuevo estilo de tabla dinámica. Como sugiere el nombre, le ofrece una vista previa del formato que aplicará su estilo de tabla personalizado.

Si está satisfecho con el resultado, haga clic en el botón OK en la parte inferior derecha del cuadro de diálogo Nuevo estilo de tabla dinámica para guardar su estilo personalizado. Ahora estará disponible en la lista de Estilos de tabla dinámica pestaña creación cinta. Por supuesto, puede modificarlo, duplicarlo o eliminarlo con un simple clic derecho sobre él.

El número de elementos de la tabla que se pueden personalizar es bastante grande y probablemente le llevará algún tiempo comprender completamente a qué área de la tabla dinámica corresponde cada uno. No dudes en guardar un estilo personalizado que irás evolucionando con el tiempo probándolo en las diferentes tablas dinámicas con las que trabajas. El potencial creativo de Excel es inmenso y (casi) solo está limitado por su imaginación y experiencia.

¿Cómo ordenar y filtrar una tabla dinámica en Excel?

Como cualquier rango de datos organizado como una tabla en Excel, una tabla dinámica se puede ordenar y filtrar automáticamente usando las herramientas proporcionadas para este propósito. En cuanto a la clasificación, encontramos las funciones clásicas de clasificación ascendente, descendente y personalizada. En el lado del filtrado, los filtros automáticos y los segmentos están presentes, así como un área de filtro específica para las tablas dinámicas, que se puede utilizar para una mayor legibilidad.

¿Cómo ordenar los datos en una tabla dinámica?

Primera solución: utilice los filtros automáticos. Clickea en el botón blanco que contiene un triángulo negro que está a la derecha de la celda del encabezado de la columna por la que desea ordenar sus datos, por ejemplo, la columna Región. Aparece un menú contextual. Las dos primeras opciones le permiten ordenar sus datos en orden ascendente o descendente (de la A a la Z o de la Z a la A en el caso de una columna que contenga texto).

También puede optar por utilizar Opciones de clasificación adicionales. Estas opciones adicionales serán diferentes según el tipo de datos de la columna (texto, número, fecha, hora). En todos los casos, excepto en los números, puede optar por ordenar los datos manualmente arrastrando y soltando con el mouse.

Segunda solución: haga clic con el botón derecho en una celda de la tabla dinámica que contenga un dato, no importa si es una categoría o un valor, y en el menú contextual que aparece, haga clic en Trier luego elija su opción de clasificación como antes.

¿Cómo filtrar datos en una tabla dinámica?

Al igual que con la clasificación, el método más intuitivo y sencillo es utilizar filtros automáticos. Clickea en el botón blanco que contiene un triángulo negro que está a la derecha de la celda del encabezado de la columna por la que desea filtrar sus datos, por ejemplo, la columna Región. Aparece un menú contextual.

En la segunda mitad de este menú, una lista de casillas de verificación le permite seleccionar qué datos mostrar u ocultar, simplemente marcando o desmarcando la casilla correspondiente. Por ejemplo, desmarque las casillas Ile-de-España et Nouvelle-Aquitaine luego validar haciendo clic en el botón OK en la parte inferior del menú. La tabla dinámica ahora solo muestra datos de la región Auvernia-Rhône-Alpes.

Puede combinar los criterios de filtrado en varias categorías diferentes. Por ejemplo, abra el filtro automático de la columna. Mes y en la lista de casillas de verificación desmarcar las correspondientes a los meses de enero et Febrero, luego valide este filtrado haciendo clic en el botón OK. La tabla dinámica ahora muestra datos de la región Auvernia-Rhône-Alpes del mes de Marte uniquement.

Para eliminar los criterios de filtro aplicados a una columna, abra el filtro automático para la columna en cuestión y haga clic en Filtro claro. Repita esto para cada columna para la que desee eliminar el filtrado.

Segundo método: use el área de filtros específicos de la tabla dinámica. Haga clic en algún lugar de la tabla dinámica para que aparezca el panel Campos de tabla dinámica en el lado derecho de la ventana. Elija una de las categorías presentes en las zonas líneas ou Columnas, haz clic en él y luego selecciona Mover al área de filtro del informe desde el menú contextual. También puede mover la categoría elegida de su zona actual a la zona Filtrar arrastrando y soltando con el ratón.

Para nuestro ejemplo, primero arrastre la categoría Región del Area líneas a la zona Filtrar, luego la categoría Producto del Area Columnas a la zona Filtrar.

Verá que la estructura de la tabla dinámica ha cambiado: los subtotales por región y por producto han desaparecido de la tabla, y arriba han aparecido dos filas que corresponden a los filtros de la tabla dinámica. Cada uno de ellos contiene un filtro automático que funciona como los vistos anteriormente, pero de una manera ligeramente simplificada.

Haga clic en el filtro automático en la fila titulada Región, se abrirá un menú contextual en el que podrá seleccionar una de las entradas de esta categoría. De forma predeterminada, solo puede seleccionar una entrada de la lista. Marcando la casilla Seleccionar varios elementos en la parte inferior de la lista, puede realizar una selección múltiple. Sin embargo, este tipo de filtrado con varios criterios dentro de la misma categoría no se recomienda en este modo de visualización, como ilustraremos a continuación. Existe una herramienta más adecuada para el filtrado con múltiples criterios (segmentos) que veremos más adelante en esta práctica ficha. Para nuestro ejemplo, abra el filtro automático de la fila. Región, seleccione el elemento Ile-de-España y validar haciendo clic en el botón OK, luego abra el filtro automático de la línea Producto, seleccione el elemento Producto A y validar haciendo clic en OK.

La tabla dinámica ahora solo muestra valores para categorías Ile-de-España et Producto A y los criterios de filtrado utilizados son claramente visibles por encima del rango de la tabla, lo que facilita enormemente la lectura y evita errores de interpretación de datos al realizar varios filtrados sucesivos. Si hubiera marcado la opción Seleccionar varios elementos en el paso anterior, y si se hubiera realizado un filtrado con varios criterios en la misma categoría, entonces la línea de filtrado habría mostrado "Varios elementos" en lugar del nombre exacto del criterio de filtrado. Esto requeriría que vuelva a abrir la lista de filtros periódicamente para recordar qué criterios se aplican.

Uso de la zona Filtres de la tabla dinámica, en lugar de los filtros automáticos clásicos presentes en cada columna, es por lo tanto particularmente útil para trabajar en subconjuntos precisos de una lista de datos que comprende muchas categorías.

Finalmente, un tercer método, que combinará las ventajas de los dos anteriores, es decir, mostrar los subtotales de todas las categorías en la tabla dinámica y mostrar permanentemente los criterios utilizados en caso de filtrado múltiple: los segmentos. Comience volviendo a la estructura anterior de la tabla dinámica. En el panel Campos de tabla dinámica, recupere la categoría Región en primera posición en la zona líneas y la categoria Producto en la zona Columnas.

Haga clic en cualquier celda de la tabla dinámica para abrir la pestaña Análisis de tabla dinámica en la cinta y haga clic en él. En la sección Filtro, haga clic en el botón Insertar segmento.

Se abre un cuadro de diálogo titulado Insertar segmentos. Contiene la lista de categorías de su tabla dinámica en forma de casillas de verificación. Para nuestro ejemplo, marque las casillas Región et Producto luego valide su selección haciendo clic en el botón OK en la parte inferior del cuadro de diálogo.

Aparecieron dos objetos en la hoja de trabajo, encima de la tabla dinámica. Estos son los famosos segmentos Excel, herramientas de filtrado visual muy prácticas y ergonómicas. Primero, con el mouse, arrástrelos y suéltelos a la derecha de la tabla dinámica para no ocultarla.

Les segments fonctionnent de la même manière que les auto-filtres, en vous permettant de choisir les catégories à afficher ou à masquer en cliquant simplement sur leur nom à l'intérieur de la liste, mais à la différence de taille que les segments resteront visibles permanentemente. En el segmento Regiónhacer clic en el icono que representa una lista de validación a la derecha del nombre "Región" para activar la selección múltiple, luego haga clic en los elementos Auvernia-Rhône-Alpes et Nouvelle-Aquitaine. En el segmento Producto, active la selección múltiple como antes, luego haga clic en los elementos Producto B et Producto C. La tabla dinámica se adapta instantáneamente a sus cambios y los criterios de filtro utilizados permanecen visibles en todo momento en los segmentos. Obviamente, puede cambiar sus filtros simplemente haciendo clic en los elementos correspondientes en los segmentos.

Para borrar los filtros aplicados mediante segmentos, haga clic en icono de embudo y cruz roja en el encabezado del segmento correspondiente.

Para eliminar completamente un segmento, haga clic derecho sobre él y seleccione Borrar " ... ".

¿Cómo actualizar una tabla dinámica con Excel?

Otra ventaja de las tablas dinámicas es que se pueden actualizar instantáneamente cuando se realizan cambios en la lista de datos inicial. Ya sea que haya agregado, editado o eliminado filas, o incluso agregado columnas que representan nuevas categorías, es posible actualizar la tabla dinámica con solo unos pocos clics.

Realice algunas modificaciones en la lista de datos inicial: reemplace algunos valores con un cero y agregue una línea al final de la lista con nuevas entradas para las categorías Regiones et Mes, por ejemplo "Occitanie" y "Avril".

Regrese a la hoja de trabajo que contiene la tabla dinámica y haga clic en cualquier celda de la tabla dinámica para abrir la pestaña Análisis de tabla dinámica en la cinta, luego haga clic en la cinta. En la sección de Datos, haga clic en el botón Actualizar entonces Refrescar todo. También puede hacer clic con el botón derecho en una celda de la tabla dinámica y elegir Actualizar.

La tabla dinámica se ha actualizado: los valores muestran claramente las cantidades que modificó en la lista de datos inicial (los ceros). Sin embargo, la fila agregada al final de la lista no aparece porque está fuera del rango de datos en el que se basa la tabla dinámica. No se preocupe, es muy fácil cambiar el rango de datos de origen e incluso configurarlo para que se expanda automáticamente cada vez que agrega una fila o columna.

En la pestaña Análisis de tabla dinámica, en la sección Datos, haga clic en el botón Cambiar la fuente de datos. Excel lo lleva de regreso a la hoja que contiene la lista de datos original y abre un cuadro de diálogo llamado Editar fuente de datos de tabla dinámica. Puede ingresar directamente las referencias de la fuente de datos en el campo Mesa / Playa usando el teclado o seleccione el rango en la hoja usando el mouse. Validar la modificación del rango haciendo clic en el botón OK en la parte inferior del cuadro de diálogo.

Regresa automáticamente a la hoja que contiene la tabla dinámica que, esta vez, muestra una nueva categoría llamada Occitania y una subcategoría llamada Abril.

Para evitar tener que cambiar el rango de datos de origen manualmente cada vez que agrega una fila o columna, la mejor manera es convertir el rango de datos a mesa estructurada. Para hacer esto, regrese a la hoja de trabajo que contiene el rango de datos de origen y selecciónelo en su totalidad. Luego haga clic en la pestaña Inserción cinta luego en el botón Cuadro.

Se abre el cuadro de diálogo Crear tabla. Compruebe que las referencias contenidas en el campo ¿Dónde se encuentran los datos de su tabla? cubra todo el rango de datos de origen y que la casilla de verificación Mi mesa tiene encabezados está marcado, luego validar haciendo clic en el botón OK.

Luego, la lista de datos cambia de apariencia. Sobre todo, se ha convertido en una tabla estructurada que tiene varias ventajas: se puede hacer referencia a ella por un nombre en lugar de por su dirección, y las adiciones de filas y columnas se tendrán en cuenta automáticamente en su alcance. Antes de continuar, asigne un nombre a esta tabla estructurada: permanezca en la misma hoja y haga clic en la pestaña Diseño de mesa en la cinta, luego en el campo Nombre de la tabla y darle un nombre significativo, por ejemplo Venta.

Regrese a la hoja que contiene la tabla dinámica, haga clic en una celda de esta última, en la pestaña Análisis de tabla dinámica cinta y, finalmente, el botón Cambiar la fuente de datos. En el cuadro de diálogo Editar fuente de datos de tabla dinámica, en el Mesa / Playas, simplemente ingrese el nombre de su tabla estructurada, Venta en este caso, y validar pulsando el botón OK.

La tabla dinámica ahora está vinculada a la tabla estructurada Venta. Cualquier adición de fila o columna a esta tabla estructurada se tendrá en cuenta al actualizar la tabla dinámica.

Regresar a la hoja que contiene la tabla estructurada Venta y vaya a la última celda de la tabla (D29). Presione la tecla Tabulación en tu teclado. Se agrega una fila en blanco a la tabla estructurada, con el mismo formato que las filas anteriores.

Complete esta fila con datos nuevos, por ejemplo, "Normandía", "Mayo", "Producto C" y "4952.38". Luego regrese a la hoja que contiene la tabla dinámica, haga clic en la pestaña Análisis de tabla dinámica cinta, en el botón Actualizar puis Refrescar todo. La tabla dinámica se actualiza y muestra una nueva categoría llamada Normandía y una subcategoría llamada más.

Ahora puede agregar tantos elementos como desee a su lista de datos inicial sin preocuparse de que se ignoren al actualizar la tabla dinámica.

Las tablas dinámicas ofrecen multitud de otras funciones más o menos sofisticadas, lo que permite obtener resultados más o menos complejos. Por ejemplo, pueden mostrar valores no como sumas sino como porcentajes o diferencias de un valor base de la lista de datos inicial, y mucho más. Sin embargo, estas funciones están más allá del alcance de esta guía práctica para una introducción al uso de tablas dinámicas. Sin embargo, los conceptos presentados en esta hoja le brindan los conocimientos necesarios para el uso de las tablas dinámicas que responderán a la mayoría de los casos de figuras que podrá encontrar.

Tabla dinámica de Excel: ejemplo, crear, actualizar ...