Si está leyendo este tutorial, es muy probable que haya oído hablar (o incluso que haya utilizado) la tabla dinámica de Excel. Es una de las funciones más poderosas de Excel (no es broma).
La mejor parte de usar una tabla dinámica es que incluso si no sabe nada de Excel, puede hacer cosas increíbles con ella con una comprensión muy básica.
Empecemos.
haga clic aquí para descargar los datos de muestra y seguirlos.
¿Qué es una tabla dinámica y por qué debería importarle?
Una tabla dinámica es una herramienta de Microsoft Excel que le permite resumir rápidamente grandes conjuntos de datos (con unos pocos clics).
Incluso si es absolutamente nuevo en el mundo de Excel, puede usar fácilmente una tabla dinámica. Es tan fácil como arrastrar y soltar los encabezados de filas / columnas para crear informes.
Suponga que tiene un conjunto de datos como se muestra a continuación:
Estos son datos de ventas que constan de ~ 1000 filas.
Tiene los datos de ventas por región, tipo de minorista y cliente.
Ahora su jefe puede querer saber algunas cosas de estos datos:
- ¿Cuáles fueron las ventas totales en la región Sur en 2016?
- ¿Cuáles son los cinco principales minoristas por ventas?
- ¿Cómo se comparó el desempeño de The Home Depot con el de otros minoristas del Sur?
Puede seguir adelante y usar las funciones de Excel para darle las respuestas a estas preguntas, pero ¿qué pasa si de repente su jefe presenta una lista de cinco preguntas más?
Deberá volver a los datos y crear nuevas fórmulas cada vez que haya un cambio.
Aquí es donde las tablas dinámicas de Excel son realmente útiles.
En segundos, una tabla dinámica responderá a todas estas preguntas (como verá a continuación).
Pero el beneficio real es que puede adaptarse a su jefe meticuloso basado en datos respondiendo sus preguntas de inmediato.
Es tan simple que también puede tomarse unos minutos y mostrarle a su jefe cómo hacerlo él mismo.
Con suerte, ahora tiene una idea de por qué las tablas dinámicas son tan increíbles. Sigamos adelante y creemos una tabla dinámica utilizando el conjunto de datos (que se muestra arriba).
Insertar una tabla dinámica en Excel
Estos son los pasos para crear una tabla dinámica con los datos que se muestran arriba:
- Haga clic en cualquier parte del conjunto de datos.
- Vaya a Insertar -> Tablas -> Tabla dinámica.
- En el cuadro de diálogo Crear tabla dinámica, las opciones predeterminadas funcionan bien en la mayoría de los casos. Aquí hay un par de cosas para verificar:
- Tabla / Rango: Se completa de forma predeterminada según su conjunto de datos. Si sus datos no tienen filas / columnas en blanco, Excel identificaría automáticamente el rango correcto. Puede cambiar esto manualmente si es necesario.
- Si desea crear la tabla dinámica en una ubicación específica, en la opción "Elija dónde desea que se coloque el informe de la tabla dinámica", especifique la ubicación. De lo contrario, se crea una nueva hoja de trabajo con la tabla dinámica.
- Haga clic en Aceptar.
Tan pronto como haga clic en Aceptar, se creará una nueva hoja de trabajo con la tabla dinámica.
Si bien se ha creado la tabla dinámica, no verá datos en ella. Todo lo que verá es el nombre de la tabla dinámica y una instrucción de una sola línea a la izquierda, y los campos de la tabla dinámica a la derecha.
Ahora, antes de pasar al análisis de datos utilizando esta tabla dinámica, comprendamos cuáles son las tuercas y tornillos que forman una tabla dinámica de Excel.
Las tuercas y tornillos de una tabla dinámica de Excel
Para usar una tabla dinámica de manera eficiente, es importante conocer los componentes que crean una tabla dinámica.
En esta sección, aprenderá sobre:
- Caché de pivote
- Área de valores
- Área de filas
- Área de columnas
- Área de filtros
Caché de pivote
Tan pronto como crea una tabla dinámica con los datos, algo sucede en el backend. Excel toma una instantánea de los datos y la almacena en su memoria. Esta instantánea se llama caché dinámica.
Cuando crea diferentes vistas usando una tabla dinámica, Excel no vuelve a la fuente de datos, sino que usa la caché dinámica para analizar rápidamente los datos y brindarle el resumen / resultados.
La razón por la que se genera una caché dinámica es para optimizar el funcionamiento de la tabla dinámica. Incluso cuando tiene miles de filas de datos, una tabla dinámica es muy rápida para resumir los datos. Puede arrastrar y soltar elementos en los cuadros de filas / columnas / valores / filtros y actualizará instantáneamente los resultados.
Nota: Una desventaja de la caché dinámica es que aumenta el tamaño de su libro de trabajo. Dado que es una réplica de los datos de origen, cuando crea una tabla dinámica, se almacena una copia de esos datos en la caché dinámica.
Lee mas: Qué es Pivot Cache y cómo utilizarlo mejor.
Área de valores
El área de valores es lo que contiene los cálculos / valores.
Según el conjunto de datos que se muestra al comienzo del tutorial, si desea calcular rápidamente las ventas totales por región en cada mes, puede obtener una tabla dinámica como se muestra a continuación (veremos cómo crear esto más adelante en el tutorial) .
El área resaltada en naranja es el Área de valores.
En este ejemplo, tiene las ventas totales de cada mes para las cuatro regiones.
Área de filas
Los encabezados a la izquierda del área de Valores forman el área de Filas.
En el siguiente ejemplo, el área Filas contiene las regiones (resaltadas en rojo):
Área de columnas
Los encabezados en la parte superior del área de Valores forman el área de Columnas.
En el siguiente ejemplo, el área de Columnas contiene los meses (resaltados en rojo):
Área de filtros
El área de filtros es un filtro opcional que puede utilizar para profundizar más en el conjunto de datos.
Por ejemplo, si solo desea ver las ventas de los minoristas multilínea, puede seleccionar esa opción del menú desplegable (resaltado en la imagen a continuación) y la tabla dinámica se actualizará con los datos de los minoristas multilínea únicamente.
Análisis de datos mediante la tabla dinámica
Ahora, intentemos responder las preguntas utilizando la tabla dinámica que hemos creado.
haga clic aquí para descargar los datos de muestra y seguirlos.
Para analizar datos utilizando una tabla dinámica, debe decidir cómo desea que se vea el resumen de datos en el resultado final. Por ejemplo, es posible que desee todas las regiones a la izquierda y las ventas totales justo al lado. Una vez que tenga esta claridad en mente, simplemente arrastre y suelte los campos relevantes en la tabla dinámica.
En la sección Campos de tabla dinámica, tiene los campos y las áreas (como se resalta a continuación):
Los campos se crean en función de los datos de backend utilizados para la tabla dinámica. La sección Áreas es donde coloca los campos y, de acuerdo con el lugar al que va un campo, sus datos se actualizan en la tabla dinámica.
Es un mecanismo simple de arrastrar y soltar, en el que simplemente puede arrastrar un campo y colocarlo en una de las cuatro áreas. Tan pronto como haga esto, aparecerá en la tabla dinámica en la hoja de trabajo.
Ahora intentemos responder las preguntas que tuvo su gerente usando esta tabla dinámica.
P1: ¿Cuáles fueron las ventas totales en la región Sur?
Arrastre el campo Región en el área Filas y el campo Ingresos en el área Valores. Actualizaría automáticamente la tabla dinámica en la hoja de trabajo.
Tenga en cuenta que tan pronto como suelte el campo Ingresos en el área Valores, se convierte en Suma de ingresos. De forma predeterminada, Excel suma todos los valores de una región determinada y muestra el total. Si lo desea, puede cambiar esto a Recuento, Promedio u otras métricas de estadísticas. En este caso, la suma es lo que necesitábamos.
La respuesta a esta pregunta sería 21225800.
P2 ¿Cuáles son los cinco principales minoristas por ventas?
Arrastre el campo Cliente en el área Fila y el campo Ingresos en el área de valores. En caso de que haya otros campos en la sección del área y desee eliminarlo, simplemente selecciónelo y arrástrelo fuera de él.
Obtendrá una tabla dinámica como se muestra a continuación:
Tenga en cuenta que, de forma predeterminada, los artículos (en este caso los clientes) se ordenan alfabéticamente.
Para obtener los cinco minoristas principales, simplemente puede ordenar esta lista y usar los cinco nombres de clientes principales. Para hacer esto:
- Haga clic con el botón derecho en cualquier celda del área Valores.
- Vaya a Ordenar -> Ordenar de mayor a menor.
Esto le dará una lista ordenada basada en las ventas totales.
P3: ¿Cómo se compara el desempeño de The Home Depot con el de otros minoristas del Sur?
Puede hacer muchos análisis para esta pregunta, pero aquí intentemos comparar las ventas.
Arrastre el campo Región en el área Filas. Ahora arrastre el campo Cliente en el área Filas debajo del campo Región. Cuando haga esto, Excel entenderá que desea categorizar sus datos primero por región y luego por clientes dentro de las regiones. Tendrá algo como se muestra a continuación:
Ahora arrastre el campo Ingresos en el área Valores y tendrá las ventas de cada cliente (así como la región en general).
Puede ordenar los minoristas en función de las cifras de ventas siguiendo los pasos a continuación:
- Haga clic con el botón derecho en una celda que tenga el valor de venta de cualquier minorista.
- Vaya a Ordenar -> Ordenar de mayor a menor.
Esto clasificaría instantáneamente a todos los minoristas por valor de venta.
Ahora puede escanear rápidamente a través de la región sur e identificar que las ventas de The Home Depot fueron 3004600 y le fue mejor que cuatro minoristas en la región sur.
Ahora hay más de una forma de despellejar al gato. También puede poner la Región en el área de Filtro y luego solo seleccionar la Región Sur.
haga clic aquí para descargar los datos de muestra.
Espero que este tutorial le brinde una descripción general básica de las tablas dinámicas de Excel y lo ayude a comenzar con él.
Aquí hay más tutoriales de tablas dinámicas que pueden gustarle:
- Preparación de datos de origen para la tabla dinámica.
- Cómo aplicar formato condicional en una tabla dinámica en Excel.
- Cómo agrupar fechas en tablas dinámicas en Excel.
- Cómo agrupar números en una tabla dinámica en Excel.
- Cómo filtrar datos en una tabla dinámica en Excel.
- Usando Slicers en Excel Pivot Table.
- Cómo reemplazar celdas en blanco con ceros en tablas dinámicas de Excel.
- Cómo agregar y usar campos calculados de una tabla dinámica de Excel.
- Cómo actualizar la tabla dinámica en Excel.
