¿Cómo crear una tabla dinámica de Excel?
¿Qué es la tabla dinámica? Es extraño para los estudiantes que son nuevos en el análisis de datos. Las tablas dinámicas son una herramienta poderosa para clasificar, resumir y calcular datos. Pueden resumir detalles de datos dispersos, por lo que deben dominarse en la etapa introductoria del análisis de datos.
El uso de tablas dinámicas puede mejorar en gran medida la eficiencia del análisis de datos, y las tablas dinámicas son fáciles de operar y se puede lograr una agregación de datos compleja arrastrando las opciones del menú. Esta sección resume la función común "colección sugerida" de tablas dinámicas. Aprendamos sobre ella juntos.
En primer lugar, esta es la primera vez que veo una tabla dinámica.
A continuación se muestra el volumen de ventas y los detalles de ventas de cada vendedor del 1 al 12 en 2021. Entonces, ¿cómo resumir el volumen de ventas y el volumen de ventas de cada vendedor en función de estos detalles de ventas? Los datos de muestra son los siguientes.
Después de aprender las funciones de Excel, lo habitual es utilizar la función SUMAR.SI. Si está utilizando la función SUMIFS para múltiples condiciones, use la función SUMAR.SI para resumir y sumar los datos como se muestra a continuación.
Usar Excel para resolver problemas es lento. Aquí, la función de tabla dinámica se utiliza para resolver en tres pasos.
Paso 1: seleccione cualquier posición de celda de la fuente de datos y seleccione el informe de tabla dinámica en la pestaña "Insertar".
Paso 2: Selecciona el rango donde se generará la tabla dinámica. Aquí, elija Generar nueva hoja de trabajo.
Paso 3: en el campo de la tabla dinámica, arrastre al vendedor a la fila, arrastre el volumen de ventas y el volumen de ventas al valor para el resumen y podrá obtener el resultado del resumen.
2. Funciones de uso común de las tablas dinámicas
1. Clasificación numérica
Haga clic con el botón derecho en la variable que desea ordenar y seleccione clasificación descendente o ascendente en la clasificación. .
2. Conceptos básicos del resumen de valores
Haga clic con el botón derecho en la variable que debe resumirse y seleccione el método que se resumirá según el resumen de valores. De forma predeterminada, las sumas totales se realizan en campos numéricos y los totales de recuento se realizan en campos de texto.
3. Modo de visualización numérica
Por defecto, los campos de datos no se calculan. Si desea calcular el porcentaje de las ventas de cada vendedor sobre las ventas totales, puede hacer clic derecho y seleccionar el porcentaje total en el modo de visualización numérica para obtener los resultados resumidos.
4. Campos calculados
Seleccione cualquier lugar de la tabla dinámica, seleccione los campos, elementos y conjuntos en la pestaña "Análisis de tabla dinámica" e inserte el campo calculado. Inserte un campo calculado, defina un nombre como precio unitario de ventas y escriba la fórmula: =Volumen de ventas/Volumen de ventas, donde se deben insertar tanto el volumen de ventas como el volumen de ventas. Haga clic en Agregar para completar el campo calculado y luego haga clic en Aceptar para generar el campo calculado.
Al mismo tiempo, puede ordenar el precio unitario de ventas en orden descendente y usar la función de columna de datos en el formato condicional para ver intuitivamente que el precio unitario no relacionado con las ventas es el más alto y las ventas de Wang Hong. El precio unitario es el más bajo.
5. Diseño de tabla de análisis de datos
Si queremos ver el volumen de ventas y el estado de las ventas de cada venta, el vendedor debe hacer coincidir el número de trabajo de cada vendedor, luego colocar el ventas Arrastre los números de personal y empleado a la fila al mismo tiempo.
Después de generar los detalles, descubrimos que los detalles de los datos se presentaban por filas porque se agregaban números de empleados a las filas, lo cual era inconsistente con nuestra distribución habitual de la tabla por columnas, y era necesario ajustar la tabla dinámica. .
Haga clic en el informe de tabla dinámica, seleccione la pestaña Diseño de informe en Diseño y luego seleccione Mostrar como tabla.
Luego seleccione la pestaña Resumen de categorías en el diseño y elija no mostrar resúmenes de categorías.
Puede obtener el resultado de cambiar la tabla de datos de la tabla de la izquierda a la tabla de la derecha.
6. Agrupación manual
Cuando arrastramos campos en filas, la tabla dinámica de Excel los agrupará automáticamente de forma predeterminada. Por ejemplo, queremos ver el volumen de ventas mensuales y las ventas de cada vendedor en 2021 y arrastrar la fecha de ventas a la fila para agruparlas automáticamente por mes.
Los siguientes son los resultados de ventas mensuales y datos dinámicos de ventas para todos los vendedores.
Si queremos cambiar la disposición mensual a trimestral podemos seleccionar el área a agrupar manualmente y hacer clic derecho sobre el grupo.
Selecciona el trimestre de la combinación a agrupar manualmente.
Los resultados de la agrupación son los siguientes.
7. Función de página de filtrado de informes
Lo que se obtiene arriba es el volumen de ventas y el volumen de ventas de cada trimestre después de agrupar manualmente a todos los vendedores. Si necesita obtener detalles específicos para cada vendedor, seleccionamos la tabla dinámica y hacemos clic en la opción para mostrar la página de filtro de informes en la pestaña Análisis de tabla dinámica.
Seleccione los campos de la página de filtro de informes que desea mostrar. Por ejemplo, el campo "Vendedor" seleccionado aquí es el campo que se agregó al área de filtro cuando se creó la tabla dinámica.
El volumen de ventas y las ventas de cada vendedor para cada trimestre son los siguientes, con detalles específicos distribuidos en cada tabla separada.
8. Producción de gráficos de tabla dinámica
Necesitamos visualizar el volumen de ventas y las ventas de cada vendedor, seleccionar el área de datos y en la pestaña "Análisis de tabla dinámica" insertar un gráfico dinámico. .
Inserte un gráfico de mezcla de ventas, seleccione aquí un gráfico de líneas y seleccione el eje secundario.
Después de crear el gráfico, encontré que el gráfico tiene muchos botones de campo. Aquí puede hacer clic derecho en el botón del campo de valor en el gráfico y ocultar todos los campos del gráfico.
El ajuste del formato del gráfico se explicará en detalle en el contenido de visualización de datos de Excel. El formato del gráfico ajustado es el siguiente.
9. Insertar una segmentación de datos
Si desea ver el volumen de ventas y las ventas del vendedor cada mes, puede insertar una segmentación de datos en la pestaña Análisis de tabla dinámica. Inserte la segmentación, seleccione la fecha de venta aquí y haga clic en Aceptar.
Haga clic con el botón derecho en la segmentación, haga clic en Tamaño y propiedades, seleccione 6 para el número de columnas y las segmentaciones se pueden fusionar en una segmentación horizontal con 2 filas y 6 columnas.
Los siguientes son los resultados de la división de datos.
Lo anterior es un resumen de las operaciones de las tablas dinámicas de Excel, que básicamente cubre todas las operaciones básicas de las tablas dinámicas. El uso competente de esta parte de la función de tabla dinámica puede hacer que el procesamiento y análisis de datos en el trabajo diario sea más fácil y conveniente. Se recomienda marcar la aplicación como favorita.