Excel

Tablas dinámicas de Excel

Excel Pivot Tables

Resumen | ¿Por qué Pivot? | Consejos | Ejemplos de | Capacitación

Las tablas dinámicas son una de las características más poderosas y útiles de Excel. Con muy poco esfuerzo, puede utilizar una tabla dinámica para crear informes atractivos para grandes conjuntos de datos. Este artículo es una introducción a las tablas dinámicas y sus beneficios, y un tutorial paso a paso con datos de muestra.





Toma el Data de muestra y pruébalo. Aprender tablas dinámicas es una habilidad que le recompensará una y otra vez. Las tablas dinámicas pueden aumentar drásticamente su eficiencia en Excel.

¿Qué es una tabla dinámica?

Puede pensar en una tabla dinámica como un informe. Sin embargo, a diferencia de un informe estático, una tabla dinámica proporciona una vista interactiva de sus datos . Con muy poco esfuerzo (y sin fórmulas) puede mirar el mismos datos desde muchas perspectivas diferentes . Puede agrupar datos en categorías, dividir los datos en años y meses, filtrar datos para incluir o excluir categorías e incluso crear gráficos.





La belleza de las tablas dinámicas es que le permiten explorar sus datos de forma interactiva de diferentes formas.

Contenido

Tutorial paso a paso

Para comprender las tablas dinámicas, debe trabajar con ellas usted mismo. En esta sección, crearemos varias tablas dinámicas paso a paso a partir de un conjunto de datos de muestra. Con experiencia, las tablas dinámicas a continuación se pueden construir en aproximadamente 5 minutos.



Data de muestra

los Data de muestra contiene 452 registros con 5 campos de información: Fecha, Color, Unidades, Ventas y Región. Estos datos son perfectos para una tabla dinámica.

Muestra de datos de ventas ya en una tabla de Excel

Datos en un Tabla de Excel llamado 'Table1'. Las tablas de Excel son una excelente manera de crear tablas dinámicas , porque se ajustan automáticamente a medida que se agregan o eliminan datos.

Nota: sé que estos datos son muy genéricos. Pero los datos genéricos son buenos para comprender las tablas dinámicas; no querrá tropezar con un detalle cuando aprenda las partes divertidas.

Insertar tabla dinámica

1. Para comenzar, seleccione cualquier celda en los datos y haga clic en Tabla dinámica en la pestaña Insertar de la cinta:

Click the button at Insert>Tabla dinámica Tabla dinámica' />

Excel mostrará la ventana Crear tabla dinámica. Observe que el rango de datos ya está completo. La ubicación predeterminada para una nueva tabla dinámica es Nueva hoja de trabajo.

2. Anule la ubicación predeterminada e ingrese H4 para colocar la tabla dinámica en la hoja de trabajo actual:

Ventana Crear tabla dinámica

3. Haga clic en Aceptar y Excel creará una tabla dinámica vacía a partir de la celda H4.

Nueva tabla dinámica vacía mirando a la celda H4

Nota: existen buenas razones para colocar una tabla dinámica en una hoja de trabajo diferente. Sin embargo, al aprender las tablas dinámicas, es útil ver los datos de origen y la tabla dinámica al mismo tiempo.

Excel también muestra el panel Campos de tabla dinámica, que está vacío en este momento. Tenga en cuenta que los cinco campos se enumeran, pero no se utilizan:

Panel de campos para una nueva tabla dinámica vacía

Para crear una tabla dinámica, arrastre los campos a un área de Columnas, Filas o Valores. El área Filtros se utiliza para aplicar filtros globales a una tabla dinámica.

Nota: el panel de campos de la tabla dinámica muestra cómo se usaron los campos para crear una tabla dinámica. Aprender a 'leer' el panel de campos requiere un poco de práctica. Ver a continuación y también aquí para más ejemplos.

Agregar campos

1. Arrastre el campo Ventas al área Valores.

Excel calcula un total general, 26356. Esta es la suma de todos los valores de ventas en todo el conjunto de datos:

Gran total de todos los datos en el conjunto de datos

2. Arrastre el campo Color al área Filas.

Excel desglosa las ventas por color. Puede ver que el azul es el más vendido, mientras que el rojo es el último:

Desglose por color

Observe que el Gran Total sigue siendo 26356. Esto tiene sentido, porque todavía estamos informando sobre el conjunto completo de datos.

Echemos un vistazo al panel de campos en este punto. Puede ver que Color es un campo de fila y Ventas es un campo de valor:

Panel de campos de tabla dinámica: ventas por color

Formato de número

Las tablas dinámicas pueden aplicar y mantener el formato de números automáticamente en campos numéricos. Se trata de un gran ahorro de tiempo cuando los datos cambian con frecuencia.

1. Haga clic con el botón derecho en cualquier número de ventas y elija Formato de número:

Haga clic con el botón derecho y seleccione Formato de número

2. Aplique el formato de moneda con cero decimales y haga clic en Aceptar:

Formato de número de moneda con cero posiciones decimales

En la tabla dinámica resultante, todos los valores de ventas tienen el formato de moneda aplicado:

Tabla dinámica con formato de moneda aplicado

El formato de moneda seguirá aplicándose a los valores de Ventas, incluso cuando se reconfigura la tabla dinámica o se agregan nuevos datos.

Ordenar por valor

1. Haga clic con el botón derecho en cualquier valor de Ventas y elija Ordenar> De mayor a menor.

Right-click and select Sort>De mayor a menor De mayor a menor' />

Excel ahora enumera primero los colores más vendidos. Este orden de clasificación se mantendrá cuando cambien los datos o cuando se reconfigure la tabla dinámica.

Desglose por color, los colores más vendidos primero

Actualizar datos

Los datos de la tabla dinámica deben 'actualizarse' para poder traer actualizaciones. Para reforzar cómo funciona esto, haremos un gran cambio en los datos de origen y lo veremos fluir hacia la tabla dinámica.

1. Seleccione la celda F5 y cambie $ 11.00 a $ 2000.

2. Haga clic con el botón derecho en cualquier lugar de la tabla dinámica y seleccione 'Actualizar'.

Para actualizar los datos, haga clic derecho y elija

Observe que 'Rojo' es ahora el color más vendido y se mueve automáticamente a la parte superior:

Tabla dinámica después de la actualización de datos

3. Cambie F5 de nuevo a $ 11,00 y actualice el pivote nuevamente.

¿Cuál de las siguientes opciones es verdadera para la función is hoy?

Nota: cambiar F5 a $ 2000 no es realista, pero es una buena manera de forzar un cambio que puede ver fácilmente en la tabla dinámica. Intente cambiar un color existente por uno nuevo, como 'Dorado' o 'Negro'. Cuando actualice, verá aparecer el nuevo color. Puede usar deshacer para volver a los datos originales y girar.

Segundo campo de valor

Puede agregar más de un campo como campo de valor.

1. Arrastre Unidades al área Valor para ver Ventas y Unidades juntas:

Desglose por color con Ventas y Unidades

Porcentaje del total

Hay diferentes formas de mostrar los valores. Una opción es mostrar los valores como un porcentaje del total. Si desea mostrar el mismo campo de diferentes formas, agregue el campo dos veces.

1. Quite las unidades del área de valores

2. Agregue el campo Ventas (nuevamente) al área Valores.

3. Haga clic con el botón derecho en la segunda instancia y elija '% del total general':

Right-click select Show values as>porcentaje del total porcentaje del total' />

El resultado es un desglose por color junto con un porcentaje del total:

Tabla dinámica: desglose por color con porcentaje

Nota: el formato numérico para el porcentaje también se ha ajustado para mostrar 1 decimal.

Aquí está el panel Campos en este punto:

Panel de campos de tabla dinámica: ventas por color con porcentaje

Agrupar por fecha

Las tablas dinámicas tienen una característica especial para agrupar fechas en unidades como años, meses y trimestres. Esta agrupación se puede personalizar.

1. Elimine el segundo campo Ventas (Ventas2).

2. Arrastre el campo Fecha al área Columnas.

3. Haga clic con el botón derecho en una fecha en el área del encabezado y elija 'Grupo':

Haga clic derecho en una fecha y seleccione el grupo

4. Cuando aparezca la ventana Grupo, agrupe solo por años (anule la selección de Meses y trimestres):

Configuración de agrupación de fechas: agrupar solo por años

Ahora tenemos una tabla dinámica que agrupa las ventas por color y año:

Tabla dinámica bidireccional: ventas por color y año

Tenga en cuenta que no hay ventas de plata en 2016 y 2017. Podemos suponer que el plateado se introdujo como un nuevo color en 2018. Las tablas dinámicas a menudo revelan patrones en los datos que son difíciles de ver de otra manera.

Aquí está el panel Campos en este punto:

Panel de campos de tabla dinámica: ventas por color y por año

Pivote bidireccional

Las tablas dinámicas pueden trazar datos en varios arreglos bidimensionales.

1. Arrastre el campo Fecha fuera del área de columnas.

2. Arrastre Región al área Columnas.

Excel crea una tabla dinámica bidireccional que desglosa las ventas por color y región:

Tabla dinámica bidireccional: ventas por color y región

3. Intercambie Región y Color (es decir, arrastre Región al área Filas y Color al área Columnas).

Excel crea otra tabla dinámica bidimensional:

Tabla dinámica bidireccional: ventas por región y color

Una vez más, observe que las ventas totales ($ 26,356) son las mismas en todas las tablas dinámicas anteriores . Cada tabla presenta una vista diferente de la mismos datos , por lo que todos suman el mismo total .

El ejemplo anterior muestra la rapidez con la que puede crear diferentes tablas dinámicas a partir de los mismos datos. Puedes crear muchos otros tipos de tablas dinámicas , utilizando todo tipo de datos.

Beneficios clave de la tabla dinámica

Sencillez . Las tablas dinámicas básicas son muy sencillas de configurar y personalizar. No es necesario aprender fórmulas complicadas.

Velocidad . Puede crear un informe útil y atractivo con una tabla dinámica en minutos. Incluso si eres muy bueno con las fórmulas, Las tablas dinámicas son más rápidas de configurar y requieren mucho menos esfuerzo. .

Flexibilidad . A diferencia de las fórmulas, las tablas dinámicas no lo bloquean en una vista particular de sus datos. Puede reorganizar rápidamente la mesa pivotante para adaptarla a sus necesidades. Usted puede incluso clonar una tabla dinámica y construir una vista separada.

Precisión . Siempre que una tabla dinámica esté configurada correctamente, puede estar seguro de que los resultados son precisos. De hecho, una tabla dinámica a menudo resaltará los problemas en los datos más rápido que cualquier otra herramienta.

Formateo . Una tabla dinámica puede aplicar automáticamente un formato de estilo y número coherente, incluso cuando los datos cambian.

Actualizaciones . Las tablas dinámicas están diseñadas para actualizaciones continuas. Si basa una tabla dinámica en una tabla de Excel, la tabla cambia de tamaño según sea necesario con nuevos datos. Todo lo que necesita hacer es hacer clic en Actualizar y su tabla dinámica le mostrará lo último.

Filtración . Las tablas dinámicas contienen varias herramientas para filtrar datos. ¿Necesita mirar a América del Norte y Asia, pero excluir Europa? Una tabla dinámica lo simplifica.

Gráficos . Una vez que tenga una tabla dinámica, puede crear un gráfico dinámico .

Más recursos de tablas dinámicas

Autor Dave Bruns Adjuntos Expediente datos de muestra para tabla dinámica.xlsx


^