Excel 2016

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




Hacer clic en la ficha archivo / clic en opciones



Seleccione la categoría complemento

Clic en complementos


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


3


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:






Haz clic sobre la opción análisis de datos, dentro de la ventana encontrara otras herramientas estadísticas.


4


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.


5


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


6


Especifique las siguientes opciones en la ventana y luego acepte:



Los resultados finales de la estadística descriptiva son los siguientes:


7


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 




Haz clic en la opción análisis de datos


8


En la siguiente ventana mostrada, seleccione la opción histograma.



Definamos las siguientes opciones en la ventana Histograma:


9


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:


10




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%


11


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


12


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 




Haz clic en la opción análisis de datos


13




En la siguiente ventana mostrada, seleccione la opción histograma.



Definamos las siguientes opciones en la ventana Histograma:


14




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:


15




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%


16


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


17


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.


18


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:


19


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


20


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:




21


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:


22


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.


23

Excel 2016

Recommend Documents