Si está leyendo este tutorial, hay una gran posibilidad de que haya escuchado (o incluso usado) la Tabla dinámica de Excel. Es una de las características más poderosas en Excel (no es broma).

La mejor parte sobre el uso de una tabla pivote es que incluso si no sabe nada en Excel, puede hacer cosas realmente increíbles con una comprensión muy básica de ello.

Empecemos.

Haga clic aquí para descargar los datos de muestra y seguir.

¿Qué es una tabla pivote y por qué debería importarme?

Una tabla dinámica es una herramienta en Microsoft Excel que le permite resumir rápidamente enormes conjuntos de datos (con unos pocos clics).

Incluso si eres absolutamente nuevo en el mundo de Excel, puedes usar fácilmente una tabla dinamica. Es tan fácil como arrastrar y soltar encabezados de filas / columnas para crear informes.

Supongamos que tiene un conjunto de datos como se muestra a continuación:

Este es un dato de ventas que consta de ~ 1000 filas.

Tiene los datos de ventas por región, tipo de detallista 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 rendimiento de The Home Depot con otros minoristas del sur?

Puede continuar y usar las funciones de Excel para darle las respuestas a estas preguntas, pero ¿qué pasa si de pronto su jefe presenta una lista de cinco preguntas más?

Tendrá que volver a los datos y crear nuevas fórmulas cada vez que haya un cambio.

Aquí es donde Excel Pivot Tables es realmente útil.

En cuestión de segundos, una tabla dinámica responderá a todas estas preguntas (como aprenderá a continuación).

Pero el beneficio real es que puede acomodar a su jefe movido por datos al responder a sus preguntas de inmediato.

Es muy simple, puede tomarse unos minutos y mostrarle a su jefe cómo hacerlo él mismo.

Con suerte, ahora tienes una idea de por qué las tablas pivote son tan increíbles. Avancemos y creemos una tabla dinámica usando el conjunto de datos (mostrado arriba).

Insertar una tabla dinámica en Excel

Estos son los pasos para crear una tabla dinámica usando los datos que se muestran arriba:

  • Haga clic en cualquier parte del conjunto de datos.
  • Vaya a Insertar ->  Tabla dinámica.

En el cuadro de diálogo Crear tabla pivote, 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 en función de su conjunto de datos. Si sus datos no tienen filas / columnas en blanco, Excel identificará 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 ‘Elegir dónde desea ubicar el informe de la tabla dinámica’, especifique la ubicación. De lo contrario, se crea una nueva hoja de cálculo con la tabla pivote.
  • Haga clic en Aceptar.

Tan pronto como haga clic en Aceptar, se creará una nueva hoja de cálculo con la tabla dinámica.

Mientras se creó la tabla dinámica, no vería datos en ella. Todo lo que vería es el nombre de la tabla dinámica y una sola instrucción de línea a la izquierda, y los campos de tabla dinámica a la derecha.

Crear una tabla pivote en Excel - Hoja de cálculo de tabla dinámica en blanco

Ahora, antes de comenzar a analizar datos usando esta tabla dinámica, comprendamos cuáles son los aspectos prácticos que conforman una tabla dinámica de Excel.

Las tuercas y los pernos 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ás sobre:

  • Pivot Cache
  • Área de valores
  • Área de filas
  • Área de Columnas
  • Área de filtros

Pivot Cache

Tan pronto como creas una tabla dinámica usando los datos, ocurre algo en el  Excel toma una instantánea de los datos y los almacena en su memoria. Esta instantánea se llama Pivot Cache.

Cuando crea diferentes vistas utilizando una tabla dinámica, Excel no vuelve a la fuente de datos, sino que utiliza la memoria caché dinámica para analizar rápidamente los datos y darle el resumen / resultados.

La razón por la que se genera un caché dinámico es optimizar el funcionamiento de la tabla dinámica. Incluso cuando tiene miles de filas de datos, una tabla dinámica es muy rápida al resumir los datos. Puede arrastrar y soltar elementos en los cuadros filas / columnas / valores / filtros y actualizará instantáneamente los resultados.

Nota: Una desventaja de la memoria 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, una copia de esa información se almacena en el Pivot Cache.

En este ejemplo, tiene las ventas totales en cada mes para las cuatro regiones.

Área de filas

Los encabezados a la izquierda del área de Valores forman el área 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 Columnas.

En el siguiente ejemplo, el área Columnas contiene los meses (resaltados en rojo):

Área de filtros

El área de filtros es un filtro opcional que puede usar para profundizar en el conjunto de datos.

Por ejemplo, si solo desea ver las ventas de un producto, puede seleccionar esa opción del menú desplegable (resaltado en la imagen a continuación) y la tabla dinámica se actualizará solo con los datos de el producto seleccionado.

Analizando datos usando la tabla pivote

Ahora, intentemos responder las preguntas utilizando la tabla dinámica que hemos creado.

Haga clic aquí para descargar los datos de muestra y seguir.

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, puede desear todas las regiones a la izquierda y las ventas totales al lado. Una vez que tenga esta claridad en mente, puede simplemente arrastrar y soltar los campos relevantes en la tabla dinámica.

En la sección Pivot Tabe Fields, tiene los campos y las áreas (como se destaca a continuación):

Crear una tabla dinámica en Excel - Campos y área

Los campos se crean en base a los datos de back-end 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, donde simplemente puede arrastrar un campo y colocarlo en una de las cuatro áreas. Tan pronto como hagas esto, aparecerá en la tabla dinámica en la hoja de trabajo.

Crear una tabla pivote en Excel - Demostración

Ahora intentemos responder a las preguntas que su gerente tuvo al usar 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 suelta el campo Ingresos en el área Valores, se convierte en Suma de ingresos. De forma predeterminada, Excel suma todos los valores para una región determinada y muestra el total. Si lo desea, puede cambiar esto a Cuenta, 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.

Crear una tabla dinámica en Excel - Q1a

Q2 ¿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 existan otros campos en la sección de área y desee eliminarlos, simplemente selecciónelo y arrástrelo fuera de este.

Obtendrás una tabla dinámica como se muestra a continuación:

Crear una tabla dinámica en Excel - Q2a

Tenga en cuenta que, de manera predeterminada, los artículos (en este caso, los clientes) están ordenados alfabéticamente.

Para obtener los cinco principales minoristas, puede simplemente ordenar esta lista y usar los cinco nombres principales de los clientes. Para hacer esto:

  • Haga clic derecho en cualquier celda en el área de Valores.
  • Vaya a Ordenar -> Ordenar de mayor a menor.

Esto le dará una lista ordenada basada en las ventas totales.

Crear una tabla dinámica en Excel - Q2d

P3: ¿Cómo se comparó el rendimiento de The Home Depot con otros minoristas del sur?

Puede hacer un montón de análisis para esta pregunta, pero aquí vamos a intentar y comparar las ventas.

Arrastre el campo de 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ás algo como se muestra a continuación:

Crear una tabla dinámica en Excel - Q3a

Ahora arrastre el campo Ingresos en el área de Valores y tendrá las ventas para cada cliente (así como también la región en general).

Crear una tabla pivote en Excel - Q3b

Puede ordenar los minoristas según las cifras de ventas siguiendo los pasos a continuación:

  • Haga clic derecho en una celda que tenga el valor de venta de cualquier minorista.
  • Vaya a Ordenar -> Ordenar de mayor a menor.

Esto ordenaría instantáneamente a todos los minoristas por el valor de las ventas.

Ahora puede escanear rápidamente a través de la región sur e identificar que las ventas de The Home Depot fueron de 3004600 e hizo mejor que cuatro minoristas en la región sur.

Crear una tabla dinámica en Excel - Q3c

Ahora hay más de una manera de desollar al gato. También puede poner la Región en el área 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 Excel Pivot Tables y lo ayude a comenzar a usarlo.

Categorías: Vba

Deja un comentario

WhatsApp Necesitas Ayuda?
A %d blogueros les gusta esto: