Análisis de datos de Excel: tablas dinámicas
El análisis de datos sobre un gran conjunto de datos suele ser necesario e importante. Implica resumir los datos, obtener los valores necesarios y presentar los resultados.
Excel proporciona una tabla dinámica para permitirle resumir miles de valores de datos de manera fácil y rápida para obtener los resultados requeridos.
Considere la siguiente tabla de datos de ventas. A partir de estos datos, es posible que deba resumir las ventas totales por región, por mes o por vendedor. La forma más sencilla de manejar estas tareas es crear una tabla dinámica que pueda modificar dinámicamente para resumir los resultados de la forma que desee.
Crear tabla dinámica
Para crear tablas dinámicas, asegúrese de que la primera fila tenga encabezados.
- Haga clic en la tabla.
- Haga clic en la pestaña INSERTAR en la cinta.
- Haga clic en Tabla dinámica en el grupo Tablas. Aparece el cuadro de diálogo de tabla dinámica.
Como puede ver en el cuadro de diálogo, puede usar una tabla o un rango del libro de trabajo actual o usar una fuente de datos externa.
- En el cuadro Tabla / Rango, escriba el nombre de la tabla.
- Haga clic en Nueva hoja de trabajo para indicarle a Excel dónde guardar la tabla dinámica.
- Haga clic en Aceptar.
Aparecen una tabla dinámica en blanco y una lista de campos de tabla dinámica.
Tablas dinámicas recomendadas
En caso de que sea nuevo en las tablas dinámicas o no sepa qué campos seleccionar de los datos, puede utilizar el Recommended PivotTables que proporciona Excel.
Haga clic en la tabla de datos.
Haga clic en la pestaña INSERTAR.
Haga clic en Tablas dinámicas recomendadas en el grupo Tablas. Aparece el cuadro de diálogo Tablas dinámicas recomendadas.
En el cuadro de diálogo de tablas dinámicas recomendadas, se muestran las posibles tablas dinámicas personalizadas que se adaptan a sus datos.
- Haga clic en cada una de las opciones de la tabla dinámica para ver la vista previa en el lado derecho.
- Haga clic en la tabla dinámica Suma del monto del pedido por vendedor y mes.
Haga clic en Aceptar. La tabla dinámica seleccionada aparece en una nueva hoja de trabajo. Puede observar los campos de la tabla dinámica que se seleccionaron en la lista de campos de la tabla dinámica.
Campos de tabla dinámica
Los encabezados en su tabla de datos aparecerán como los campos en la tabla dinámica.
Puede seleccionarlos / deseleccionarlos para cambiar instantáneamente su tabla dinámica para mostrar solo la información que desea y de la manera que desea. Por ejemplo, si desea mostrar la información de la cuenta en lugar de la información del monto del pedido, anule la selección de Monto del pedido y seleccione Cuenta.
Áreas de tabla dinámica
Incluso puede cambiar el diseño de su tabla dinámica al instante. Puede usar las áreas de tabla dinámica para lograr esto.
En las áreas de tabla dinámica, puede elegir:
- Qué campos mostrar como filas
- Qué campos mostrar como columnas
- Cómo resumir sus datos
- Filtros para cualquiera de los campos
- Cuándo actualizar el diseño de su tabla dinámica
- Puede actualizarlo instantáneamente mientras arrastra los campos a través de áreas, o
- Puede aplazar la actualización y obtenerla actualizada solo cuando haga clic en ACTUALIZAR
Una actualización instantánea le ayuda a jugar con los diferentes diseños y elegir el que se adapte a los requisitos de su informe.
Puede simplemente arrastrar los campos a través de estas áreas y observar el diseño de la tabla dinámica mientras lo hace.
Anidamiento en la tabla dinámica
Si tiene más de un campo en cualquiera de las áreas, el anidamiento ocurre en el orden en que coloca los campos en esa área. Puede cambiar el orden arrastrando los campos y observar cómo cambia el anidamiento. En las opciones de diseño anteriores, puede observar que
- Los meses están en columnas.
- Región y vendedor en filas en ese orden. es decir, los valores de los vendedores se anidan en valores de región.
- El resumen es por la suma del monto del pedido.
- No se eligen filtros.
La tabla dinámica resultante es la siguiente:
En las áreas de la tabla dinámica, en filas, haga clic en región y arrástrela debajo del vendedor de manera que se vea de la siguiente manera:
El orden de anidación cambia y la tabla dinámica resultante es la siguiente:
Note- Puede observar claramente que el diseño con el orden de anidamiento - Región y luego Vendedor rinde un informe mejor y compacto que el que tiene el orden de anidamiento - Vendedor y luego Región. En caso de que el vendedor represente más de un área y necesite resumir las ventas por vendedor, entonces el segundo diseño habría sido una mejor opción.
Filtros
Puede asignar un filtro a uno de los campos para poder cambiar dinámicamente la tabla dinámica en función de los valores de ese campo.
Arrastre Región de Filas a Filtros en las Áreas de tabla dinámica.
El filtro con la etiqueta Región aparece encima de la tabla dinámica (en caso de que no tenga filas vacías encima de la tabla dinámica, la tabla dinámica se empuja hacia abajo para dejar espacio para el filtro.
Puedes ver eso -
- Los valores del vendedor aparecen en filas.
- Los valores de los meses aparecen en columnas.
- El filtro de región aparece en la parte superior con la opción predeterminada seleccionada como TODAS.
- El valor resumido es la suma del monto del pedido
- Suma de la cantidad de la orden El vendedor aparece en la columna Total general
- Suma del monto del pedido mensual aparece en la fila Gran total
Haga clic en la flecha del cuadro a la derecha de la región del filtro. Aparece una lista desplegable con los valores de la región del campo.
- Marque la opción Select Multiple Items. Aparecen casillas de verificación para todos los valores.
- Seleccione Sur y Oeste, anule la selección de los demás valores y haga clic en Aceptar.
Los datos pertenecientes a las regiones sur y oeste solo se resumirán como se muestra en la captura de pantalla que se muestra a continuación:
Puede ver que junto a la Región de filtro, Multiple ItemsSe muestra, lo que indica que ha seleccionado más de un elemento. Sin embargo, en el informe que se muestra no se sabe cuántos elementos y / o qué elementos se seleccionan. En tal caso, usar Slicers es una mejor opción para filtrar.
Rebanadoras
Puede utilizar Slicers para tener una mayor claridad sobre qué elementos se filtraron los datos.
Haga clic en ANALIZAR debajo de HERRAMIENTAS PIVOTABLES en la cinta.
Haga clic en Insertar rebanador en el grupo Filtro. Aparece el cuadro Insertar rebanadores. Contiene todos los campos de sus datos.
Seleccione los campos Región y mes. Haga clic en Aceptar.
Las rebanadoras para cada uno de los campos seleccionados aparecen con todos los valores seleccionados por defecto. Las herramientas de la cortadora aparecen en la cinta para trabajar en la configuración, apariencia y sensación de la cortadora.
- Seleccione Sur y Oeste en el Rebanador de Región.
- Seleccione febrero y marzo en la cortadora por mes.
- Mantenga presionada la tecla Ctrl mientras selecciona varios valores en un Slicer.
Los elementos seleccionados en las rebanadoras se resaltan. Se mostrará una tabla dinámica con valores resumidos para los elementos seleccionados.
Resumir valores mediante otros cálculos
En los ejemplos hasta ahora, ha visto valores resumidos por Sum. Sin embargo, también puede utilizar otros cálculos si es necesario.
En la lista de campos de la tabla dinámica
- Seleccione la cuenta de campo.
- Deseleccione el Importe del pedido de campo.
- Arrastre el campo Cuenta al área Resumen de valores. De forma predeterminada, se mostrará la suma de la cuenta.
- Haga clic en la flecha del lado derecho del cuadro.
- En el menú desplegable que aparece, haga clic en Configuración del campo de valor.
Aparece el cuadro Configuración del campo de valor. Varios tipos de cálculos aparecen como una lista en el campo Resumir valor por:
- Seleccione Recuento en la lista.
- El Nombre personalizado cambia automáticamente a Recuento de cuenta. Haga clic en Aceptar.
La tabla dinámica resume los valores de la cuenta por recuento.
Herramientas de tabla dinámica
Siga los pasos que se indican a continuación para aprender a usar las herramientas de tabla dinámica.
- Seleccione la tabla dinámica.
Las siguientes herramientas de tabla dinámica aparecen en la cinta:
- ANALYZE
- DESIGN
ANALIZAR
Algunos de los ANALYZE Los comandos de la cinta son:
- Establecer opciones de tabla dinámica
- Configuración de campo de valor para el campo seleccionado
- Expandir campo
- Contraer campo
- Insertar rebanador
- Insertar línea de tiempo
- Actualizar datos
- Cambiar fuente de datos
- Mover tabla dinámica
- Resolver orden (si hay más cálculos)
- PivotChart
DISEÑO
Algunos de los DESIGN Los comandos de la cinta son:
- Diseño de tabla dinámica
- Opciones para subtotales
- Opciones para totales generales
- Formularios de diseño de informes
- Opciones para filas en blanco
- Opciones de estilo de tabla dinámica
- Estilos de tabla dinámica
Campo en expansión y contracción
Puede expandir o contraer todos los elementos de un campo seleccionado de dos maneras:
- Seleccionando el símbolo o a la izquierda del campo seleccionado.
- Haciendo clic en Expandir campo o Contraer campo en la cinta ANALIZAR.
Seleccionando el símbolo Expandir o Colapsar a la izquierda del campo seleccionado
- Seleccione la celda que contiene Este en la tabla dinámica.
- Haga clic en el símbolo Colapsar a la izquierda de Este.
Todos los elementos de Este se contraerán. El símbolo Colapsar a la izquierda de Este cambia al símbolo Expandir .
Puede observar que solo los elementos debajo de Este están contraídos. El resto de los elementos de la tabla dinámica son como son.
Haga clic en el símbolo Expandir a la izquierda de Este. Se mostrarán todos los elementos debajo de Este.
Uso de ANALYZE en la cinta
Puede contraer o expandir todos los elementos de la tabla dinámica a la vez con los comandos Expandir campo y Contraer campo en la cinta.
- Haga clic en la celda que contiene Este en la tabla dinámica.
- Haga clic en la pestaña ANALIZAR en la cinta.
- Haga clic en Contraer campo en el grupo Campo activo.
Todos los elementos del campo Este en la tabla dinámica se contraerán.
Haga clic en Expandir campo en el grupo Campo activo.
Se mostrarán todos los elementos.
Estilos de presentación de informes
Puede elegir el estilo de presentación de su tabla dinámica, ya que lo incluiría como informe. Seleccione un estilo que se adapte al resto de su presentación o informe. Sin embargo, no te aburras con los estilos porque siempre es mejor un informe que impacta en mostrar los resultados que uno colorido, que no resalta los puntos de datos importantes.
- Haga clic en Este en la tabla dinámica.
- Haga clic en ANALIZAR.
- Haga clic en Configuración de campo en el grupo Campo activo. Aparece el cuadro de diálogo Configuración de campo.
- Haga clic en la pestaña Diseño e impresión.
- Marque Insertar línea en blanco después de la etiqueta de cada artículo.
Se mostrarán filas en blanco después de cada valor del campo Región.
Puede insertar filas en blanco desde el DESIGN pestaña también.
- Haga clic en la pestaña DISEÑO.
- Haga clic en Diseño de informe en el grupo Diseño.
- Seleccione Mostrar en forma de esquema en la lista desplegable.
- Pase el mouse sobre los estilos de tabla dinámica. Aparecerá una vista previa del estilo en el que se coloca el mouse.
- Seleccione el estilo que se adapte a su informe.
Se mostrará la tabla dinámica en forma de esquema con el estilo seleccionado.
Línea de tiempo en tablas dinámicas
Para comprender cómo usar la línea de tiempo, considere el siguiente ejemplo en el que los datos de ventas de varios artículos se proporcionan al vendedor y a la ubicación. Hay un total de 1891 filas de datos.
Cree una tabla dinámica a partir de este rango con -
- Ubicación y vendedor en filas en ese orden
- Producto en columnas
- Suma de la cantidad en valores resumidos
- Haga clic en la tabla dinámica.
- Haga clic en la pestaña INSERTAR.
- Haga clic en Línea de tiempo en el grupo Filtros. Aparece Insertar líneas de tiempo.
Haga clic en Fecha y haga clic en Aceptar. Aparece el cuadro de diálogo Línea de tiempo y las herramientas de la línea de tiempo aparecen en la cinta.
- En el cuadro de diálogo Línea de tiempo, seleccione MESES.
- En la lista desplegable, seleccione TRIMESTRES.
- Haga clic en 2014 Q2.
- Mantenga presionada la tecla Mayús y arrastre hasta el cuarto trimestre de 2014.
La línea de tiempo se selecciona para Q2 - Q4 2014.
La tabla dinámica se filtra a esta línea de tiempo.