2 Análisis de datos datos
Instalar el complemento Análisis de datos Estadística descriptiva Histograma Diagrama de Pareto
Microsoft Excel 2016
Para utilizar la opción Análisis de datos, primero debemos verificar si está instalado con el siguiente procedimiento:
Active la ficha Datos / busca el grupo análisis – la opción análisis de datos. Si observa la siguiente siguiente imagen, quiere decir que el complemento no o ha sido instalado:
o
o
La opción análisis de datos, debería de estar después del grupo esquema, según observa en la imagen, no se encuentra. Entonces procederemos a instalar el complemento.
IMPORTANTE: Para instalar el complemento, no requiere del DVD de office 2016, solo es cuestión de activar una opción.
PROF. CHRISTIAN MONTOYA
1
Microsoft Excel 2016
Hacer clic en la ficha archivo / clic en opciones
Seleccione la categoría complemento
Clic en complementos
PROF. CHRISTIAN MONTOYA
2
Microsoft Excel 2016
En la parte inferior de la ventana, haz clic en el botón
Ir…
En la siguiente ventana debemos activar la opción herramienta para análisis
finalmente haz clic en aceptar.
COMPROBANDO LA INSTALACIÓN
Active la ficha Datos / busca el grupo análisis / análisis de datos
PROF. CHRISTIAN MONTOYA
3
Microsoft Excel 2016
Es un complemento de Excel, que nos ayuda a realizar un resumen estadístico de los datos que están en la hoja de cálculo de forma rápida y sencilla. Es recomendable que los datos estén en una sola columna o en una sola fila. Verifiquemos si el complemento se encuentra instalado:
Active la ficha Datos / busca el grupo análisis / análisis de datos
Haz clic sobre la opción análisis de datos, dentro de la ventana encontrara otras herramientas estadísticas.
PROF. CHRISTIAN MONTOYA
4
Microsoft Excel 2016
En el siguiente ejercicio, aplique la herramienta: estadística descriptiva, para obtener un resumen de datos estadística de la variable sueldo.
Active la ficha Datos / busca el grupo análisis / clic en análisis de datos
En la siguiente ventana seleccione estadística descriptiva y acepte.
PROF. CHRISTIAN MONTOYA
5
Microsoft Excel 2016
Después de hacer clic en aceptar, veremos la siguiente ventana:
OPCIÓN
RANGO DE ENTRADA AGRUPADO POR RÓTULOS EN LA PRIMERA FILA OPCIONES DE SALIDA RESUMEN ESTADÍSTICA
DESCRIPCIÓN
Es el rango de datos que desea analizar. Esto significa como están los datos en la hoja, en filas o columnas. Indica si la selección realizada en rango de entrada, involucra a los títulos del rango. Indica donde se presentará los resultados del análisis. Si está activo indica que Excel muestra una serie de respuestas estadísticas, como el promedio, el máximo, el mínimo, etc.
RANGO DE ENTRADA AGRUPADO POR RÓTULOS EN LA PRIMERA FILA OPCIONES DE SALIDA
: : : :
D2:D21 columnas activado rango de salida: active la celda G4
RESUMEN ESTADÍSTICA
:
activado
PROF. CHRISTIAN MONTOYA
6
Microsoft Excel 2016
Especifique las siguientes opciones en la ventana y luego acepte:
Los resultados finales de la estadística descriptiva son los siguientes:
PROF. CHRISTIAN MONTOYA
7
Microsoft Excel 2016
Es un tipo de grafico especial, que representa una variable en forma de barras; donde el tamaño de cada barra es proporcional a la frecuencia de los valores representados. EJERCICIO 1 : HISTOGRAMA SIN RANGO DE CLASE
Los datos que se dan a continuación representan los pesos en Kg. de 80 person as, realice el histograma correspondiente.
SOLUCIÓN
Active la ficha Datos / busca el grupo análisis / análisis de datos
Haz clic en la opción análisis de datos
PROF. CHRISTIAN MONTOYA
8
Microsoft Excel 2016
En la siguiente ventana mostrada, seleccione la opción histograma.
Definamos las siguientes opciones en la ventana Histograma:
PROF. CHRISTIAN MONTOYA
9
Microsoft Excel 2016
OPCIÓN
Rango de entrada Rango de clase Opciones de salida Crear gráfico
DESCRIPCIÓN
Es el rango donde se encuentra los datos a trabajar. Es el rango de cómo se agruparan los datos. Donde se muestran los resultados del histograma. Active la opción para que Excel genere de forma automática la gráfica.
Según nuestro ejercicio, vamos establecer las siguientes opciones: RANGO DE ENTRADA
:
B2:G15
OPCIONES DE SALIDA
:
marque la opción Rango de salida y seleccione una celda por debajo de los datos
CREAR GRÁFICO
:
active la opción
Finalmente haz clic en aceptar.
A continuación, veremos los siguientes resultados:
PROF. CHRISTIAN MONTOYA
10
Microsoft Excel 2016
Seleccionamos el gráfico
Hacemos clic derecho sobre una de las barras
Del menú contextual seleccione / dar formato a series de datos
A continuación, vera un panel a la derecha de la pantalla
Cambie la opción ancho del intervalo a: 0%
PROF. CHRISTIAN MONTOYA
11
Microsoft Excel 2016
Elimine la leyenda de la gráfica
Finalmente debe observar el gráfico, como en la siguiente imagen:
Seleccione una de las barras / asigne un contorno de forma (ficha formato)
Modifique el rango de clase, como en la siguiente imagen:
Agregue a la gráfica la opción etiquetas de datos
PROF. CHRISTIAN MONTOYA
12
Microsoft Excel 2016
EJERCICIO 2 : HISTOGRAMA CON RANGO DE CLASE
Trabajaremos con los mismos datos que los del ejercicio 1. La diferencia es que nosotros vamos a formar grupos de 5 en 5. También construiremos el histograma. Recuerden que estos datos representan los pesos en Kg. de 80 personas.
SOLUCIÓN
Active la ficha Datos / busca el grupo análisis / análisis de datos
Haz clic en la opción análisis de datos
PROF. CHRISTIAN MONTOYA
13
Microsoft Excel 2016
En la siguiente ventana mostrada, seleccione la opción histograma.
Definamos las siguientes opciones en la ventana Histograma:
PROF. CHRISTIAN MONTOYA
14
Microsoft Excel 2016
Según nuestro ejercicio, vamos establecer las siguientes opciones: RANGO DE ENTRADA
:
B2:G15
RANGO DE CLASE
:
D19:D23
OPCIONES DE SALIDA
:
marque la opción Rango de salida seleccione la celda F18
CREAR GRÁFICO
:
active la opción
Finalmente haz clic en aceptar.
A continuación, veremos los siguientes resultados:
PROF. CHRISTIAN MONTOYA
15
Microsoft Excel 2016
Seleccionamos el gráfico
Hacemos clic derecho sobre una de las barras
Del menú contextual seleccione / dar formato a series de datos
A continuación, vera un panel a la derecha de la pantalla
Cambie la opción ancho del intervalo a: 0%
PROF. CHRISTIAN MONTOYA
16
Microsoft Excel 2016
Elimine la leyenda de la gráfica
Finalmente debe observar el gráfico, como en la siguiente imagen:
Seleccione una de las barras / asigne un contorno de forma (ficha formato)
Modifique el rango de clase, como en la siguiente imagen:
Agregue a la gráfica la opción etiquetas de datos
PROF. CHRISTIAN MONTOYA
17
Microsoft Excel 2016
Muchos de nosotros nos preguntaremos que es la regla de Pareto o en que consiste esa regla.
¿Qué es el diagrama del Pareto?
Es una herramienta para la gestión de la calidad Esto quiere decir tener la mayor rentabilidad para la empresa, mejorando algunos aspectos que dificulten obtener el mayor beneficio.
El diagrama de Pareto también es conocido como la Regla del 80 / 20 Este diagrama no está basado en cálculos matemáticos, pues, todo lo contrario, es un método gráfico que lo podemos desarrollar usando Microsoft Excel. Este método gráfico nos determina los problemas más importantes de la calidad, y por consiguiente saber que problemas debemos priorizar.
¿Para qué sirve el diagrama de Pareto? La gráfica de Pareto se puede utilizar para varios propósitos durante un proyecto de mejoras: 1. Nos sirva para analizar las causas de los problemas. 2. Para estudiar los resultados obtenidos. 3. Para planear una mejora continua.
PROF. CHRISTIAN MONTOYA
18
Microsoft Excel 2016
Veamos el siguiente caso Asuma que trabaja en el área comercial de una empresa que se dedica a la venta de productos de la marca San Jorge. El gerente comercial de la empresa, decide realizar un análisis de las ventas, ya que observa que las ventas han decaído en un 2.35%. Usted como asistente comercial, recolecta las opiniones de los vendedores, para conocer cuáles son las posibles problemáticas, y así, poder atender dichas dificultades; ya que en el mercado existen otros proveedores que pueden ofrecer mejores ofertas. Es en este caso, aplicaremos el diagrama de Pareto.
Después de recolectar la información de los clientes a través de encuestas, obtenemos los siguientes resultados:
Causas No entregan publicidad Demora en la entrega de pedidos El tipo de pago solo es al contado Visitas poco frecuentes No otorgan promociones
Nro. de Clientes 35 57 74 28 67
Solución: 1. Copiamos la lista en una hoja de cálculo y ordenamos los datos, para obtener el siguiente resultado:
PROF. CHRISTIAN MONTOYA
19
Microsoft Excel 2016
2. Adicionamos las siguientes columnas, y asignamos fórmulas:
Obtener la frecuencia acumulada
Para la primera causa, se copia igual Para la segunda causa se realiza la fórmula: Celda E4
= c4 + e3
Copie la fórmula de la celda E4, hasta completar la columna
Obtener el % acumulado
La fórmula será la siguiente: Celda D3
= e3 / 200
Copie la fórmula de la celda D3, hasta completar la columna
PROF. CHRISTIAN MONTOYA
20
Microsoft Excel 2016
3. A continuación, realizamos la gráfica:
Seleccione el rango B2:D7
Active la ficha Insertar / y seleccione el gráfico de columna agrupada 2D
Observe el resultado:
PROF. CHRISTIAN MONTOYA
21
Microsoft Excel 2016
4. Para apreciar mejor el porcentaje acumulado, realizamos lo siguiente:
Seleccionamos la gráfica
Activamos la ficha gráfica / seleccione la opción cambiar tipo de gráfico
En la ventana mostrada / seleccione cuadro combinado
Modifique las opciones como se observan en la imagen:
PROF. CHRISTIAN MONTOYA
22
Microsoft Excel 2016
5. Veamos finalmente el resultado del gráfico:
6. Modificando las opciones de gráfico
Elimine el título de la gráfica
Cambie el incremento de la categoría Y al valor de 20.
Dibuje una línea vertical, en la gráfica para un mejor análisis
7. Todo lo que está a la izquierda de la línea vertical roja, son las problemáticas a las que debemos dar una solución inmediata para aumentar la reantabilidad.
PROF. CHRISTIAN MONTOYA
23