Este artículo discutirá cómo analizar datos usando una tabla dinámica. Para la formación, puede utilizar la tabla disponible en este enlace (tabla simple.xlsx).
Lo primero que debe analizar con una tabla dinámica es resumir los subtotales. En nuestro ejemplo, esto puede ser la necesidad de calcular los volúmenes de ventas de todas las tiendas para cada fecha.
Para hacer esto, haga clic en cualquier encabezado de fila de la tabla dinámica (en nuestro ejemplo, estos son los campos Fecha, punto de venta y Marca de celular), y en las pestañas abiertas Trabajar con tablas dinámicas ir a la pestaña Opciones. Tienes que presionar el botón en él. Opciones de campo en un grupo campo activo.
En la ventana que se abre, la primera pestaña será la pestaña.
La ausencia de dicho marcador significa que no ha seleccionado un encabezado de fila, es decir, el cursor se coloca en una celda con un valor numérico.
Marcador Subtotales y filtros Puede seleccionar una condición para mostrar subtotales. Se ofrecen las siguientes condiciones:
Al configurar el subtotal automático, obtenemos la siguiente tabla, que contiene subtotales para cada condición:
Si configura subtotales con el comando Opciones de campo, no da resultados visibles, verifique la configuración para mostrar subtotales usando el comando subtotales grupos Diseño pestañas Constructor.
Digamos que queremos mostrar subtotales solo para fechas, ocultando subtotales para puntos de venta. Para hacer esto, haga clic en cualquier campo de la tabla con el nombre de la tienda y llame al menú contextual. En él, debe desmarcar la condición. Subtotal: punto de venta. Como podemos ver, los subtotales quedan solo para las fechas:
A menudo es necesario ordenar los datos en una tabla dinámica para comprenderlos mejor. Para hacer esto, simplemente seleccione el campo por el que desea ordenar, vaya a la pestaña General, en un grupo Edición haga clic en el botón Ordenar y filtrar y establezca las condiciones de clasificación que necesita.
Una función muy útil para analizar información en una tabla dinámica es la capacidad de agrupar datos. Por ejemplo, necesitamos agrupar nuestras ventas por semana del mes. Para ello, seleccione las fechas que se incluyen en la primera semana (15.05-21.05):
Tenga en cuenta que para facilitar la selección, hemos colapsado los datos de tiendas individuales usando el botón + en el lado izquierdo de la celda con el nombre de la tienda.
A continuación, debe ejecutar el comando Agrupar por selección grupos Grupo pestañas Opciones. Aparecerá una nueva columna en la tabla, en la que el campo Grupo 1 fusionará los campos que hemos seleccionado.
Todo lo que queda es cambiar el nombre del grupo simplemente editando la celda:
Para desagrupar, solo use el comando Desagrupar del mismo grupo, después de seleccionar el campo a desagrupar. Tenga en cuenta que no puede desagrupar un campo que incluimos en la condición de creación de la tabla dinámica; por ejemplo, el campo Punto de venta o la fecha.
Consideremos otra forma de mostrar datos que nos ayudarán a analizar la información de nuestra tabla. Por ejemplo, necesitamos saber el volumen de ingresos no en términos monetarios, sino como un porcentaje de los ingresos totales para todo el período de ventas.
Para hacer esto, seleccione cualquier celda en la columna Ingresos de nuestra tabla dinámica. Después de eso, debe ejecutar el comando Opciones de campo en un grupo campo activo pestañas Opciones.
En el cuadro de diálogo que se abre, vaya a la pestaña Cálculos adicionales y seleccione del menú desplegable Porcentaje del total por columna. Después de hacer clic en el botón Aceptar, nuestra tabla se verá así:
Si los datos de la tabla no se mostrarán como porcentajes, verifique la configuración del formato numérico de las celdas (esto se puede hacer inmediatamente en el cuadro de diálogo Opciones de campo haciendo clic en el botón Formato numérico, o llamando a la ventana correspondiente desde el menú contextual).
Para analizar listas de datos (tablas de datos) en Excel 2007 que tienen muchas filas o registros, se suelen utilizar herramientas como las tablas dinámicas. Las tablas dinámicas facilitan mucho la visualización, manipulación y resumen de datos en las listas de Excel 2007.
Para que Excel funcione con los datos ingresados como una tabla de datos, y no como un simple conjunto de datos, debe tener el formato de una tabla. Para hacer esto, haga clic en el botón Tabla en la pestaña Insertar. En el cuadro de diálogo Crear tabla (Figura 1), especifique el rango previsto en el que se colocará la tabla y seleccione la casilla de verificación Tabla con encabezados.
Arroz. una.
El formato se aplicará a este rango, es decir, se aplica el estilo rápido de la tabla, establecido por defecto, mientras que se activan las herramientas contextuales bajo el nombre general "Trabajar con tablas", que se incluyen en la pestaña contextual Diseño. También puede usar la herramienta Formatear como tabla en la pestaña Inicio para formatear una tabla.
Arroz. 2.
Para construir un informe sobre esta tabla, es recomendable utilizar la poderosa herramienta "Pivot Table". Para aplicar esta herramienta a listas de datos o tablas de datos, se debe activar una de las celdas de la tabla de datos, por ejemplo, la celda de la tabla "Stock en stock". Luego haga clic en el botón "Tabla dinámica", que se encuentra en la pestaña "Insertar" en el grupo "Tabla" (Figura 3).
Arroz. 3.
En el cuadro de diálogo Crear tabla dinámica que se abre, debe seleccionar (resaltar) una tabla o rango e indicar dónde desea colocar el informe (preferiblemente en una nueva hoja), luego haga clic en Aceptar. Se abrirá un asistente especial para tablas dinámicas (Figura 4).
Arroz. cuatro
La imagen del informe (PivotTable1) se muestra en el lado izquierdo de la hoja de cálculo y las herramientas para crear una tabla dinámica se encuentran en el lado derecho de la hoja: cuatro áreas vacías y una lista de campos. Para crear un informe, en el lado derecho, arrastre los campos obligatorios a las áreas correspondientes de la tabla dinámica: "Filtro de informe", "Nombres de columna", "Nombres de fila" y "Valores".
Por ejemplo, si selecciona los campos: Número de almacén, Nombre, Precio (UAH) y los arrastra a las áreas correspondientes: "Nombres de columna", "Nombres de fila" y "Valores", entonces se mostrarán en estas áreas en el lado derecho. En este caso, se creará una tabla dinámica o informe en el lado izquierdo de la hoja de trabajo (Fig. 5).
Arroz. 5.
Cabe señalar que en el área "Valores" se realizan algunos cálculos matemáticos, por ejemplo, sumatoria (campo Suma por Precio). Para cambiar el tipo de cálculo, en el área "Valores", haga clic izquierdo en el campo "Cantidad por campo de precio" y seleccione el comando "Configuración de campo de valor" en el menú que se abre, luego en el cuadro de diálogo "Configuración de campo de valor". , seleccione la función requerida y haga clic en el botón Aceptar.
Para cambiar la estructura de la tabla dinámica, en el lado derecho de la hoja, arrastre los campos a otra área de la tabla dinámica o elimínelos. Cabe señalar que para eliminar un campo, debe arrastrarlo fuera de la tabla.
Las poderosas herramientas para analizar datos en Excel 2007 son:
Las herramientas de análisis hipotético se colocan en la pestaña Datos del grupo Herramientas de datos y Buscar soluciones en la pestaña Datos del grupo Análisis.
La selección de parámetros proporciona una solución al problema de la selección de un parámetro para una función de un argumento. Scenario Manager está diseñado para crear escenarios (primero agregue varios escenarios para diferentes casos) y luego revise los escenarios para predecir el proceso y generar un informe sobre el escenario.
El programa "Búsqueda de soluciones" está destinado a resolver sistemas complejos de ecuaciones, problemas de optimización lineal y no lineal. El complemento Solver se basa en métodos iterativos.
Excel no es solo un editor de hojas de cálculo, sino también una poderosa herramienta para varios cálculos matemáticos y estadísticos. La aplicación cuenta con una enorme cantidad de funciones diseñadas para estas tareas. Sin embargo, no todas estas funciones están habilitadas de forma predeterminada. Es a tales funciones ocultas a las que pertenece el conjunto de herramientas. "Análisis de los datos". Veamos cómo se puede habilitar.
Para aprovechar las características proporcionadas por la función "Análisis de los datos", necesita activar el grupo de herramientas "Paquete de análisis" siguiendo ciertos pasos en las Preferencias de Microsoft Excel. El algoritmo de estas acciones es casi el mismo para las versiones 2010, 2013 y 2016 del programa, y solo tiene diferencias menores en la versión 2007.
Después de completar estos pasos, la función especificada se activará y su conjunto de herramientas estará disponible en la cinta de opciones de Excel.
Ahora podemos lanzar cualquiera de las herramientas del grupo. "Análisis de los datos".
El trabajo en cada función tiene su propio algoritmo de acciones. Uso de algunas de las herramientas del grupo "Análisis de los datos" descrito en lecciones separadas.
Como puede ver, aunque la caja de herramientas "Paquete de análisis" y no está activado por defecto, el proceso de activación es bastante sencillo. Al mismo tiempo, sin conocer un algoritmo claro de acciones, es poco probable que el usuario pueda activar rápidamente esta función estadística muy útil.
TAREA 1
Análisis de datos estadísticos en MS Excel
Propósito del trabajo: aprender a procesar datos estadísticos utilizando las funciones integradas de MS Excel; explore las características del paquete de análisis y sus herramientas: " Generación de números aleatorios», "Gráfico de barras" , " Estadísticas descriptivas" en el ejemplo de las medidas de velocidad de procesamiento.
De acuerdo con las pautas para el trabajo de laboratorio "Medición de la velocidad de los vehículos" (en la disciplina "Investigación y diseño de carreteras"), procese los datos de medición experimental utilizando métodos estadísticos matemáticos en el programa Excel. Para qué:
1. Calcule las características estadísticas utilizando las funciones integradas: - el valor mínimo de la velocidad de movimiento Vmin;
El valor máximo de la velocidad de movimiento Vmax; - el valor medio de la velocidad de movimiento Vav;
Desviaciones estandar;
Desviación estándar de la Sp media;
Coeficiente de Student (para determinar el intervalo de confianza) t; - intervalo de confianza para Р = 0,95.
2. Obtenga características estadísticas usando la herramienta "Estadísticas descriptivas"del paquete adicional "Análisis de datos".
3. Construya un histograma de la distribución de velocidades.
4. Construya una curva acumulativa (curva de frecuencia acumulativa).
5. Construya una curva teórica para la distribución de la velocidad de movimiento.
Para obtener una cantidad suficiente de datos iniciales (resultados de la medición de la velocidad), use un experimento de simulación usando el “ Generación de números aleatorios» complementos "Análisis de datos".
Al realizar p.p. 3 y 4, seleccione el intervalo de velocidad ("bolsillo" - en la terminología de Excel), que le permite obtener el histograma más simétrico que demuestra la ley de distribución normal.
Se proporciona una ejecución de muestra en el archivo adjunto BasicsPC1-Student.xls.
Pautas
Supongamos que hemos realizado una serie de 10 experimentos, midiendo algún valor X. Tabla 1. Vista aproximada de la hoja "Procesamiento de experimentos"
Las entradas en las columnas D y E son sugerencias que lo ayudarán a determinar qué características calcularemos. La columna F debería estar vacía por ahora, nuestras fórmulas se colocarán en ella.
Empecemos a procesar los resultados calculando el número de experimentos n.
Para determinar el número de valores, se usa una función especial llamada COUNT. Para ingresar una fórmula con funciones, use el Asistente de funciones, que se inicia con el comando "Insertar función" a través del menú "Insertar" - "Función" o el botón en la barra de herramientas con la etiqueta f x .
Hagamos clic en la celda F6, donde debería estar el resultado e iniciemos el Asistente de funciones.
El primer paso de trabajo (figura 1) sirve para seleccionar la función deseada.
Las funciones estadísticas se utilizan para procesar los datos experimentales. Por lo tanto, antes que nada, en la lista de categorías, seleccione la categoría " Estadística". Aparece una lista de funciones estadísticas en la segunda ventana.
La lista de funciones está ordenada alfabéticamente, lo que facilita encontrar la función CONTAR que necesitamos ("Cuenta la cantidad de números en la lista de argumentos").
Con esta función resaltada, presione el botón Ok y vaya al paso 2.
El segundo paso (Figura 2) se usa para establecer los argumentos de la función.
La función CONTAR necesita especificar qué números necesita contar o en qué celdas se encuentran estos números. Las siguientes dos etapas de procesamiento de una serie de experimentos se llevan a cabo de manera similar.
En la celda F7, usando la función PROMEDIO, se calcula el valor medio de la muestra, en la celda F8, la desviación estándar de la muestra, usando la función DESVEST. .
El mismo rango de celdas sirve como argumentos para estas funciones.
Para calcular el intervalo de confianza, es necesario determinar el coeficiente de Student. Depende de la probabilidad de error (con una fiabilidad comúnmente dada del 95%, la probabilidad de error es del 5%) y del número de grados de libertad n-1).
Para encontrar el coeficiente de Student se utiliza la función estadística de Excel STUDISTRIP ("Distribución inversa de Student"). Una característica de esta función es que el primer argumento, el número 5% (o 0,05) se ingresa en la ventana correspondiente desde el teclado. Para el segundo, especifique la dirección de la celda donde se encuentra el valor n, luego agregue "-1" en la ventana. Obtenemos la entrada " F6-1".
La fórmula de multiplicación habitual se utiliza para encontrar el intervalo de confianza. Por supuesto, en lugar de letras, debe haber direcciones de celda donde se ubique el coeficiente de Student y la desviación estándar de la media. Como regla general, el valor del intervalo de confianza se redondea a una cifra significativa, el mismo orden del entorno debe ser para la media. Por tanto, el resultado final se puede escribir de la siguiente manera: con un 95% de confianza, X = 14,80 ± 0,05. En conclusión, calculamos el error relativo en la determinación de X: = CI / X cf (fórmula: “=F11/F7”). El valor del error relativo se suele expresar en porcentaje, tenemos 0,3%.
Las tareas 2 y 3 se realizan utilizando el complemento "Paquete de análisis" (desde el menú Herramientas .Análisis de datos Histograma).
Para instalar el complemento, abra el menú Herramientas Complementos y de la lista propuesta de complementos disponibles para la instalación, seleccione "Paquete de análisis" (ver Fig. Instalación de complementos
Excel en computadora.doc).
