Tutorial de Funciones estadísticas de Excel

Prévia do material em texto

TUTORIAL DE FUNCIONES ESTADÍSTICAS DE EXCEL 
 
1. REPRESENTACIONES GRAFICAS CON EXCEL 
 
a. Elabore una tabla de resultados de las variables a analizar (Ej. Estado Civil) 
 
b. Seleccione las celdas que corresponden a las variables (Soltero, Casado, Viudo) y el valor de 
frecuencia absoluta 
 
 
c. Seleccionar INSERTAR/GRAFICO CIRCULAR o el que correspondiere para el tipo de variable. 
Completar con los datos (Titulo, Referencias) 
 
 
 
 
 
 
2. CALCULO ME MEDIDAS DE TENDENCIA CENTRAL Y DISPERSIÓN 
 
a. Elabore una tabla con los datos del ejercicio 2 de la semana 1 
 
 
 
b. Para el cálculo del promedio, por ejemplo, debemos situarnos en la celda correspondiente (B116) 
e insertar la función estadística correspondiente y el rango de las coordenadas de la serie de datos que analizaremos 
(B2:B114). 
 
 
Ubicados en la celda B116, hacemos clic en ∫x / Insertar función / PROMEDIO y seleccionamos el RANGO DE DATOS 
de la serie a analizar y damos Aceptar. Apareciendo el valor del promedio de esa serie de datos (Edad promedio, 
por ejemplo) 
 
 
Las demás medidas de tendencia central y dispersión que deben emplearse son: 
Moda: Función MODA.UNO 
Mediana: Función MEDIANA 
Varianza: Función VAR.P 
Desviación Estándar: Función DESVEST.M 
3. CALCULO PARA AJUSTAR A DISTRIBUCIÓN BINOMIAL 
 
a. Elaboramos una tabla con los datos consignados 
 
 
 
b. Para el cálculo de la probabilidad (p) en la celda correspondiente insertamos la función 
=DISTR.BINOM.N y completamos los siguientes datos (Ej. Para el valor 0v (0 veces)). 
 
 
Donde Números de éxito corresponde al valor 0 (0 veces) 
Ensayos: Es el número total de ensayos totales (7 veces) 
Prob_Exito: Probabilidad de éxito del ensayo (50% ó 0,5) 
Acumulado: Probabilidad bruta (debemos colocar 0 = Falso) ya que no queremos calcular la probabilidad 
acumulada. 
 
c. Para el cálculo ajustado (N aj) debemos introducir manualmente el cálculo para la celda 
correspondiente. Debemos multiplicar el valor de probabilidad por el valor de la población total (220 
trabajadores). =+(N3*220) 
Repetimos las fórmulas para los demás eventos esperados (de 1v hasta 7v) 
 
d. La representación gráfica la hacemos con los datos de la tabla valores de x (veces) versus N y de N 
aj. 
x N p N aj
0 v 5 0,0078125 1,71875
1 v 7 0,0546875 12,03125
2 v 20 0,1640625 36,09375
3 v 96 0,2734375 60,15625
4 v 67 0,2734375 60,15625
5 v 18 0,1640625 36,09375
6 v 5 0,0546875 12,03125
7 v 2 0,0078125 1,71875
 
 
 
4. DISTRIBUCIÓN POISSON 
 
a. Elaboramos Tabla y realizamos los cálculos correspondientes en las celdas 
 
 
 
b. Para el cálculo de distribución Poisson, insertamos la función =POISSON.DIST 
Y completamos la ventana de datos: 
 
X N P N ajust
0 v 2 0,006737947 0,87593311
1 v 10 0,033689735 4,37966555
2 v 28 0,084224337 10,9491639
3 v 45 0,140373896 18,2486065
4 v 24 0,17546737 22,8107581
5 v 10 0,17546737 22,8107581
6 v 5 0,146222808 19,0089651
7 v 3 0,104444863 13,5778322
8 v 1 0,065278039 8,48614512
9v 1 0,036265577 4,71452506
10 v 1 0,018132789 2,35726253
Total 130 128,219615
 
Donde X: es el valor del número de evento, Media: es el valor medio esperado (de 0-10, sería el 5) y en 
Acumulado: 0 (probabilidad bruta de ese evento). El valor de N aj será el valor de probabilidad por el valor de N 
(130 trabajadores) 
 
c. Para la representación gráfica se realiza con los valores de x (eventos: accidentes) versus N y N aj. 
 
 
 
5. PARA EL CALCULO DE DISTRIBUCIONES CONTÍNUAS DE PROBABILIDAD (CURVA NORMAL) 
 
a. Debemos conocer los datos de: Promedio (µ) Desviación estándar (δ) y los límites inferiores y 
superiores. 
 
b. Para calcular la probabilidad acumulada para cada área de la curva de distribución normal usamos 
las siguientes funciones y límites. 
 
 
Prom 168
DS 10
Li 155
Ls 175
a) <155 0,09680048
b) 155≤x≤165 0,28528809
c) 165<x<170 0,19717113
d) 170<x<175 0,17877664
e) >175 0,24196365
Ej. Para el cálculo de probabilidad para valores menores a 155. Donde X es el valor que se desea la 
distribución, Media (promedio: 168), Desv Estándar (10) y en Acumulado colocamos 1 (verdadero: 
acumulado) 
Para un valor entre 155-165 insertamos la sentencia =DISTR.NORM.N(165;168;10;1)-
DISTR.NORM.N(155;168;10;1), donde se resta la probabilidad de 165 menos la probabilidad de 155. 
Para un valor 165-170, 170-175 repetimos la sentencia anterior reemplazando los valores de x y restando 
los correspondientes valores de p. 
Para el valor >175, usamos la sentencia =1-DISTR.NORM.N(175;168;10;1), donde restamos del valor 1 
(uno) la probabilidad para x=175. 
 
6. PARA EL CALCULO DE INTERVALO DE CONFIANZA 
 
a. Elaboramos las tablas de resultados correspondientes con los datos del enunciado 
 
 
b. Calculamos el promedio y desviación estándar 
c. Calculamos el IC (95 o 90%) según corresponda insertando la función 
=INTERVALO.CONFIANZA.NORM 
 
 
Pozo Arsenico 
P1 34,4
P2 33,3
P3 35,3
P4 32,6
P5 34,7
Promedio: 34,06
DS: 1,1
Niv Conf: 0,95
Muestra: 5
ɑ 0,05
IC 95%: 0,96
L Sup 35,02
L Inf 33,10
IC 90%: 0,81
L Sup 34,87
L Inf 33,10
Donde Alfa es el nivel de significancia (0,05 o 5%) por ejemplo para el IC del 95%. Desv Estándar (el valor 
calculado o informado) y Tamaño (de la muestra: 5) 
 
El resultado me indica el valor por encima y por debajo del valor de la media (±) 
 
 
 
7. PARA CONTRASTE DE MEDIAS MUESTRALES 
 
a. Elaboro tabla de resultados y calculo el promedio/desv estándar 
 
b. En Excel/Opciones de Excel/Complementos debemos tener señalado y habilitado HERRAMIENTAS 
DE ANALISIS y aparecerá en la barra de tareas del Excel (así podremos hacer los análisis estadísticos) 
 
 
c. Seleccionamos ANALISIS DE DATOS, elegimos el análisis a realizar 
A B
13 9
16 10
15 12
12 13
10 11
12 7
14 8
9 10
8 15
12 9
12,1 10,4
2,55821118 2,41292814
 
 
d. Completamos la Ventana 
 
 
e. Saliendo los resultados en el lugar de salida elegido 
 
 
8. PARA ANALISIS DE CORRELACION 
 
a. Elaboramos la tabla de datos en columnas 
 
 
b. Ejecutamos el análisis estadístico con la función =COEF.DE.CORREL, donde los datos de cada 
columna se colocan en el valor de matriz 1 o 2. 
 
 
Edad PT Alb
1 4,55 1,9
1 4,4 1,75
1 4,35 1,8
6 4,65 2,3
6 4,9 2,4
6 5,4 2,6
12 6 2,85
12 6,3 3,15
12 6,9 3,3
24 6,2 3,2
24 7,1 3,35
24 7,3 3,55
36 6,3 3,3
36 7,6 4,05
 
c. Graficamos 
 
 
9. ANÁLISIS NO PARAMÉTRICOS (JI CUADRADO) 
 
a. Elaboramos la tabla de doble entrada con los datos (reales) y la tabla esperada (cálculo de 
proporciones) 
 
 
b. Aplicamos la función =PRUEBA.CHICUAD y completamos con los datos 
 
c. Obtenemos el valor de p: 0,0000097751 (<0,05 rechazo Ho)