Manual y Aplicaciones de Funciones Estadísticas y Análisis de Datos en Microsoft Excel 2007
Por:
Jhorland José Ayala García1
Richard Henry Deán Arnedo2
José Antonio Mola Ávila3
Universidad Tecnología de Bolívar
Facultad de Ciencias Económicas y Administrativas
Programa de Finanzas y Negocios Internacionales
Modelaje en Excel
Cartagena de Indias, Noviembre de 2009
1 Estudiante de Economía.
TABLA DE CONTENIDO
1. FUNCIONES ESTADÍSTICAS ... 8
1.1 Explicación de las Funciones Estadística ... 9
1.1.1 BINOM.CRIT: ... 10
1.1.2 COEF.DE.CORREL: ... 10
1.1.3 COEFICIENTE.ASIMETRIA: ... 11
1.1.4 COEFICIENTE.R2: ... 12
1.1.5 CONTAR: ... 13
1.1.6 CONTAR.BLANCO: ... 14
1.1.7 CONTAR.SI: ... 14
1.1.8 CONTARA: ... 15
1.1.9 CONTAR.Sl.CONJUNTO: ... 16
1.1.10 COVAR: ... 16
1.1.11 CRECIMIENTO: ... 17
1.1.12 CUARTIL: ... 18
1.1.13 CURTOSIS: ... 18
1.1.14 DESVEST: ... 19
1.1.15 DESVESTA: ... 20
1.1.16 DESVESTP: ... 20
1.1.17 DESVESTPA: ... 21
1.1.18 DESVIA2: ... 22
1.1.19 DESVPROM: ... 23
1.1.20 DISTR.WEIBULL: ... 24
1.1.21 DISTR.BETA: ... 25
1.1.22 DISTR.BETA.INV: ... 25
1.1.23 DISTR.BINOM: ... 26
1.1.24 DISTR.CHI: ... 27
1.1.25 DISTR.EXP: ... 27
1.1.26 DISTR.F: ... 28
1.1.27 DISTR.F.INV: ... 29
1.1.28 DISTR.GAMMA: ... 30
1.1.29 DISTR.GAMMA.INV: ... 30
1.1.30 DISTR.HIPERGEOM: ... 31
1.1.31 DISTR.LOG.INV: ... 32
1.1.32 DISTR.LOG.NORM: ... 32
1.1.33 DISTR.NORM: ... 33
1.1.34 DISTR.NORM.ESTAND: ... 34
1.1.35 DISTR.NORM.ESTAND.INV: ... 34
1.1.36 DISTR.NORM.INV: ... 35
1.1.37 DISTR.T: ... 35
1.1.38 DISTR.T.INV: ... 36
1.1.39 ERROR.TIPICO.XY: ... 36
1.1.41 ESTIMACION.LOGARITMICA: ... 38
1.1.42 FISHER ... 39
1.1.43 FRECUENCIA ... 41
1.1.44 GAMMA.LN ... 42
1.1.45 INTERSECCION.EJE ... 43
1.1.46 INTERVALO.CONFIANZA ... 44
1.1.47 JERARQUÍA ... 45
1.1.48 K.ESTIMO.MAYOR ... 46
1.1.49 K.ESTIMO.MENOR ... 47
1.1.50 MAX ... 47
1.1.51 MAXA ... 48
1.1.52 MEDIA.ACOTADA ... 49
1.1.53 MEDIA.ARMO ... 50
1.1.54 MEDIA.GEOM ... 51
1.1.55 MEDIANA ... 52
1.1.56 MIN ... 53
1.1.57 MINA ... 54
1.1.58 MODA ... 55
1.1.59 NEGBINOMDIST ... 55
1.1.60 NORMALIZACION ... 56
1.1.61 PEARSON... 57
1.1.62 PENDIENTE ... 58
1.1.63 PERCENTIL ... 59
1.1.64 PERMUTACIONES ... 59
1.1.65 POISSON ... 60
1.1.66 PROBABILIDAD ... 61
1.1.67 PROMEDIO ... 62
1.1.68 PROMEDIO.SI ... 63
1.1.69 PROMEDIO.SI.CONJUNTO ... 64
1.1.70 PROMEDIOA ... 65
1.1.71 PRONOSTICO ... 65
1.1.72 PRUEBA.CHI ... 66
1.1.73 PRUEBA.CHI.INV ... 67
1.1.74 PRUEBA.F ... 68
1.1.75 PRUEBA.FISCHER.INV ... 68
1.1.76 PRUEBA.T ... 69
1.1.77 PRUEBA.Z ... 70
1.1.78 RANGO.PERCENTIL ... 71
1.1.79 TENDENCIA ... 71
1.1.80 VAR ... 72
1.1.81 VARA ... 73
1.1.82 VARP ... 74
1.1.83 VARPA ... 74
2. ANÁLISIS DE DATOS ... 75
2.1 Análisis de varianza de un factor: ... 76
2.2 Análisis de varianza de dos factores con varias muestras por grupo: ... 77
2.3 Análisis de varianza de dos factores con una sola muestra por grupo: ... 79
2.4 Coeficiente de correlación: ... 80
2.5 Covarianza: ... 81
2.6 Estadística descriptiva: ... 82
2.7 Suavización exponencial: ... 83
2.8 Prueba F para varianza de dos muestras: ... 84
2.9 Análisis de Fourier: ... 85
2.10 Histograma: ... 86
2.11 Media móvil: ... 87
2.12 Generación de números aleatorios: ... 88
2.13 Jerarquía y percentil: ... 89
2.14 Regresión: ... 91
2.15 Muestra: ... 92
2.16 Prueba t para medias de dos muestras emparejadas: ... 93
2.17 Prueba t para dos muestras suponiendo varianzas iguales: ... 94
2.18 Prueba t para dos muestras suponiendo varianzas desiguales: ... 95
2.19 Prueba z para medias de dos muestras: ... 97
TABLA DE IMÁGENES
Imagen 1: Insertar función ... 8
Imagen 2: Insertar función 1 ... 8
Imagen 3: Cuadro de dialogo Inserta Función. ... 9
Imagen 4: Cuadro de dialogo argumentos función BINOM.CRIT ... 10
Imagen 5: Cuadro de dialogo argumentos función COEF.DE.CORREL ... 11
Imagen 6: Cuadro de dialogo argumentos función COEFICIENTE.ASIMETRIA ... 12
Imagen 7: Cuadro de dialogo argumentos función COEFICIENTE.R2 ... 13
Imagen 8: Cuadro de dialogo argumentos función CONTAR ... 13
Imagen 9: Cuadro de dialogo argumentos función CONTAR.BLANCO ... 14
Imagen 10: Cuadro de dialogo argumentos función CONTAR.SI ... 15
Imagen 11: Cuadro de dialogo argumentos función CONTARA ... 15
Imagen 12: Cuadro de dialogo argumentos función CONTAR.SI.CONJUNTO ... 16
Imagen 13: Cuadro de dialogo argumentos función COVAR ... 16
Imagen 14: Cuadro de dialogo argumentos función CRECIMIENTO ... 17
Imagen 15: Cuadro de dialogo argumentos función CUARTIL ... 18
Imagen 16: Cuadro de dialogo argumentos función CURTOSIS ... 19
Imagen 17: Cuadro de dialogo argumentos función DESVEST ... 19
Imagen 18: Cuadro de dialogo argumentos función DESVESTA ... 20
Imagen 19: Cuadro de dialogo argumentos función DESVESTP ... 21
Imagen 20: Cuadro de dialogo argumentos función DESVESTPA ... 21
Imagen 21: Cuadro de dialogo argumentos función DESVIA2 ... 22
Imagen 22: Cuadro de dialogo argumentos función DESVPROM ... 23
Imagen 23: Cuadro de dialogo argumentos función DISTR.WEIBULL ... 24
Imagen 24: Cuadro de dialogo argumentos función DISTR.BETA ... 25
Imagen 25: Cuadro de dialogo argumentos función DISTR.BETA.INV ... 26
Imagen 26: Cuadro de dialogo argumentos función DISTR.BINOM ... 26
Imagen 27: Cuadro de dialogo argumentos función DISTR.CHI ... 27
Imagen 28: Cuadro de dialogo argumentos función DISTR.EXP ... 28
Imagen 29: Cuadro de dialogo argumentos función DISTR.F ... 28
Imagen 30: Cuadro de dialogo argumentos función DISTR.F.INV ... 29
Imagen 31: Cuadro de dialogo argumentos función DISTR.GAMMA ... 30
Imagen 32: Cuadro de dialogo argumentos función DISTR.GAMMA.INV ... 31
Imagen 33: Cuadro de dialogo argumentos función DISTR.HIPERGEOM ... 31
Imagen 34: Cuadro de dialogo argumentos función DISTR.LOG.INV ... 32
Imagen 35: Cuadro de dialogo argumentos función DISTR.LOG.NORM ... 33
Imagen 36: Cuadro de dialogo argumentos función DISTR.NORM ... 33
Imagen 37: Cuadro de dialogo argumentos función DISTR.NORM.ESTAND ... 34
Imagen 38: Cuadro de dialogo argumentos función DISTR.NORM.ESTAND.INV ... 34
Imagen 40: Cuadro de dialogo argumentos función DISTR.T ... 35
Imagen 41: Cuadro de dialogo argumentos función DISTR.T.INV ... 36
Imagen 42: Cuadro de dialogo argumentos función ERROR.TIPICO.XY ... 37
Imagen 43: Cuadro de dialogo argumentos función ESTIMACION.LINEAL ... 37
Imagen 44: Cuadro de dialogo argumentos función ESTIMACION.LOGARITMICA ... 38
Imagen 45: Función FISHER ... 39
Imagen 46: Cuadro de dialogo argumentos función FISHER. ... 40
Imagen 47: Cuadro de dialogo argumentos función FISHER 1 ... 40
Imagen 48: Cuadro de dialogo argumentos función FRECUENCIA ... 41
Imagen 49: Cuadro de dialogo argumentos función GAMMA.LN ... 43
Imagen 50: Cuadro de dialogo argumentos función INTERSECCION.EJE ... 43
Imagen 51: Cuadro de dialogo argumentos función INTERVALO.CONFIANZA ... 44
Imagen 52: Cuadro de dialogo argumentos función JERARQUIA ... 45
Imagen 53: Cuadro de dialogo argumentos función K.ESTIMO.MAYOR ... 46
Imagen 54: Cuadro de dialogo argumentos función K.ESTIMO.MENOR ... 47
Imagen 55: Cuadro de dialogo argumentos función MAX ... 48
Imagen 56: Cuadro de dialogo argumentos función MAXA ... 49
Imagen 57: Cuadro de dialogo argumentos función MEDIA.ACOTADA ... 50
Imagen 58: Cuadro de dialogo argumentos función MEDIA.ARMO ... 51
Imagen 59: Cuadro de dialogo argumentos función MEDIA.GEOM ... 52
Imagen 60: Cuadro de dialogo argumentos función MEDIANA ... 53
Imagen 61: Cuadro de dialogo argumentos función MIN ... 53
Imagen 62: Cuadro de dialogo argumentos función MINA ... 54
Imagen 63: Cuadro de dialogo argumentos función MODA ... 55
Imagen 64: Cuadro de dialogo argumentos función NEGBINOMDIST ... 56
Imagen 65: Cuadro de dialogo argumentos función NORMALIZACION ... 57
Imagen 66: Cuadro de dialogo argumentos función PEARSON ... 58
Imagen 67: Cuadro de dialogo argumentos función PENDIENTE ... 58
Imagen 68: Cuadro de dialogo argumentos función PERCENTIL ... 59
Imagen 69: Cuadro de dialogo argumentos función PERMUTACIONES ... 60
Imagen 70: Cuadro de dialogo argumentos función POISSON ... 60
Imagen 71: Cuadro de dialogo argumentos función PROBABILIDAD ... 61
Imagen 72: Cuadro de dialogo argumentos función PROMEDIO ... 62
Imagen 73: Cuadro de dialogo argumentos función PROMEDIO.SI ... 63
Imagen 74: Cuadro de dialogo argumentos función PROMEDIO.SI.CONJUNTO ... 64
Imagen 75: Cuadro de dialogo argumentos función PROMEDIOA ... 65
Imagen 76: Cuadro de dialogo argumentos función PRONOSTICO ... 66
Imagen 77: Cuadro de dialogo argumentos función PRUEBA.CHI ... 67
Imagen 78: Cuadro de dialogo argumentos función PRUEBA.CHI.INV ... 67
Imagen 79: Cuadro de dialogo argumentos función PRUEBA.F ... 68
Imagen 80: Cuadro de dialogo argumentos función PRUEBA.FISHER.INV ... 69
Imagen 82: Cuadro de dialogo argumentos función PRUEBA.Z ... 70
Imagen 83: Cuadro de dialogo argumentos función RANGO.PERCENTIL ... 71
Imagen 84: Cuadro de dialogo argumentos función TENDENCIA ... 72
Imagen 85: Cuadro de dialogo argumentos función VAR ... 73
Imagen 86: Cuadro de dialogo argumentos función VARA ... 74
Imagen 87: Cuadro de dialogo argumentos función VARP ... 74
Imagen 88: Cuadro de dialogo argumentos función VARPA ... 75
1. Funciones Estadísticas
Para utilizar cualquier formula estadística que trae Microsoft Excel 2007, u otra versión, se de hacer clic en insertar función ( ) como se muestra en la siguiente imagen:
Imagen1: Insertar función
Otra forma de hacer lo mismo es dar clic, en la cinta de opciones de Excel, en
Fórmulas, en la parte izquierda, como muestra la imagen 2.
Se ve que hay otra manera de insertar una función. Ésta es Mayús+F3, es decir, la que comúnmente se conoce como Shift + F3.
Después de hacer esto les aparece el siguiente cuadro de dialogo (Imagen 3), del cual deben seleccionar Estadísticas donde dice Oseleccionar una categoría:
Imagen 3: Cuadro de dialogo Inserta Función.
Ahora después de haber explicado como insertar una función comencemos a explicar cada
1.1 Explicación de las Funciones Estadística
Funciones Estadísticas
1.1.1 BINOM.CRIT:
Esta función permite calcular el menor valor cuya probabilidad binomial acumulada es igual a una probabilidad tomada como valor crítico, dadas una cantidad de ensayos (tamaño de muestra) y la probabilidad de éxito en cada ensayo. Al insertarla función:
Imagen 4: Cuadro de dialogo argumentos función BINOM.CRIT
En Ensayos se introduce el valor del tamaño de muestra o de la cantidad de
resultados que pueden ser éxito o fracaso, Prob_éxito es la probabilidad de los
resultados favorables y Alfa es el valor crítico. El resultado es el valor que tiene
una probabilidad acumulada igual o mayor al valor Alfa.
1.1.2 COEF.DE.CORREL:
están relacionados (correlación cero) o si la relación entre estos es negativa (correlación negativa).
Al insertar esta función, aparecerá la siguiente ventana
Imagen 5: Cuadro de dialogo argumentos función COEF.DE.CORREL
En Matriz1 y Matriz2 se deben incluir los datos correspondientes a los rangos de
valores 1 y 2, respectivamente. El resultado arrojado es el coeficiente de correlación de tales conjuntos de datos.
1.1.3 COEFICIENTE.ASIMETRIA:
Imagen 6: Cuadro de dialogo argumentos función COEFICIENTE.ASIMETRIA
En la casilla Número1 se incluyen los datos a los que se les quiere calcular la
asimetría (deben estar en forma de matrices) y el resultado es el grado de asimetría de los datos con respecto a la media.
1.1.4 COEFICIENTE.R2:
Esta función muestra como resultado la proporción de la varianza de una variable dependiente que es explicada por la varianza de una variable independiente, es decir, cuánto explica la variable independiente a la variable dependiente.
Imagen 7: Cuadro de dialogo argumentos función COEFICIENTE.R2
En la casilla Conocido_y se introducen los valores de la variable explicada o
dependiente y en Conocido_x los valores de la variable explicativa o
independiente. El resultado obtenido es el Coeficiente R2.
1.1.5 CONTAR:
Esta función cuenta el número de celdas que contienen números en un rango específico. Omite las celdas que contengan texto, así como las que estén vacías. Al insertar la función se muestra el siguiente cuadro:
En Valor1 se introduce la matriz a la que se le quiere contar el número de celdas
que contienen números, y el resultado será el número de celdas del rango que contienen números.
1.1.6 CONTAR.BLANCO:
Cuenta el número de celdas vacías dentro de un rango específico. Luego de insertar la función:
Imagen 9: Cuadro de dialogo argumentos función CONTAR.BLANCO
En la casilla Rango se incluye la matriz de referencia a la que se le desea calcular
el número de celdas vacías que contiene. El resultado es el número de celdas vacías del rango especificado.
1.1.7 CONTAR.SI:
Imagen 10: Cuadro de dialogo argumentos función CONTAR.SI
Al igual que en la función CONTAR, se introduce en Rango la matriz de la que se
quiere extraer el número de celdas que cumplen con una característica o criterio específico; en Criterio, se escribe la característica de las celdas que se desea
sean contadas y como resultado se muestra el número de celdas que cumplen con esta condición.
1.1.8 CONTARA:
Esta función cuenta el número de celdas que no están vacías en un rango específico. Después de insertar la función:
En Valor1 se introduce la matriz objeto de estudio y como resultado CONTARA
cuenta el número de celdas no vacías que esta matriz tiene.
1.1.9 CONTAR.Sl.CONJUNTO:
Imagen 12: Cuadro de dialogo argumentos función CONTAR.SI.CONJUNTO
1.1.10 COVAR:
En Matriz1 se introduce el primer conjunto de datos y en Matriz2, el segundo.
Como resultado, COVAR devuelve el valor de la covarianza entre los datos
seleccionados.
1.1.11 CRECIMIENTO:
Esta función da como resultado, a partir de valores X e Y conocidos, los valores de Y correspondientes a valores específicos de X aplicando una tasa de crecimiento exponencial.
Imagen 14: Cuadro de dialogo argumentos función CRECIMIENTO
En Conocido_y se introducen los valores de la serie dependiente, en
Conocido_x los valores de la serie independiente, en Nueva_matriz_x, los
valores de X para los cuales se desea calcular los valores de Y, y Constante, es
1.1.12 CUARTIL:
Esta función devuelve el cuartil o percentil de un conjunto de datos. Imagen 15: Cuadro de dialogo argumentos función CUARTIL
En Matriz se introduce el rango de datos y en Cuartil, dependiendo de lo que se
quiera obtener:
Valor a introducir en Cuartil Para obtener
0 El valor mínimo
1 El primer cuartil (percentil 25)
2 El valor de la mediana (percentil 50) 3 El tercer cuartil (percentil 75)
4 El valor máximo
1.1.13 CURTOSIS:
Calcula la curtosis correspondiente a un conjunto de datos. Al introducir la función:
Imagen 16: Cuadro de dialogo argumentos función CURTOSIS
En Número1 se introducen los datos de referencia y como resultado se obtiene la
curtosis de los mismos.
1.1.14 DESVEST:
Calcula la desviación estándar de una muestra sin tener en cuenta los valores lógicos y celdas que contengan texto.
En Número1 se introduce la muestra y como resultado DESVEST arroja el valor
de la desviación estándar de la misma.
1.1.15 DESVESTA:
Esta función también calcula la desviación estándar de una muestra, pero a diferencia de DESVEST, DESVESTA si tiene en cuenta los valores lógicos como VERDADERO (a los que les asigna valores de 1) y FALSO (a los que les asigna valores de 0). Al insertar la función:
Imagen 18: Cuadro de dialogo argumentos función DESVESTA
En Valor1 se introducen los datos de la muestra y como resultado DESVESTA
muestra el valor de la desviación estándar muestral.
1.1.16 DESVESTP:
Imagen 19: Cuadro de dialogo argumentos función DESVESTP
En Número1 se introduce la población objeto de estudio y como resultado
DESVESTP arroja el valor de la desviación estándar de la misma.
1.1.17 DESVESTPA:
Calcula la desviación estándar de la población total teniendo en cuenta valores lógicos y el texto como VERDADERO a los que se les asigna valor de 1 y FALSO a los que se les asigna 0.
En Valor1 se introducen los datos de la población y como resultado DESVESTPA
muestra el valor de la desviación estándar poblacional.
1.1.18 DESVIA2:
Esta función devuelve la sumatoria de los cuadrados de las desviaciones de los valores dados a partir de la media muestral.
Imagen 21: Cuadro de dialogo argumentos función DESVIA2
En Número1 se introduce la muestra objeto de estudio y como resultado
DESVIA2 arroja el valor de la sumatoria de los cuadrados de las desviaciones
muestrales a partir de la media.
1.1.19 DESVPROM:
Calcula el promedio de las desviaciones absolutas de los datos de una muestra a partir de la media.
Imagen 22: Cuadro de dialogo argumentos función DESVPROM
En Número1 se introducen los valores de la muestra y como resultado se obtiene
el promedio de las desviaciones absolutas de los datos de la misma.
1.1.20 DISTR.WEIBULL:
Esta función arroja como resultado la probabilidad de una variable aleatoria que sigue una distribución de WEIBULL.
Imagen 23: Cuadro de dialogo argumentos función DISTR.WEIBULL
Donde X es el valor al que se le desea calcular la probabilidad, Alfa y Beta son
parámetros de la distribución y Acumulado define la forma de la misma,
VERDADERO para que devuelva la probabilidad acumulada (Función de probabilidad acumulada) y FALSO para que devuelva el valor de la probabilidad de acuerdo a la función de densidad.
1.1.21 DISTR.BETA:
Devuelve la probabilidad acumulada de una variable X comprendida en un intervalo [A, B] y que sigue una distribución BETA. Al insertar la función:
Imagen 24: Cuadro de dialogo argumentos función DISTR.BETA
Donde X es el valor de la variable comprendida en el intervalo [A, B], Alfa y Beta
son parámetros de la distribución, A y B son los límites inferior y superior,
respectivamente.
1.1.22 DISTR.BETA.INV:
Esta es la función inversa de la función DISTR.BETA. Calcula el valor de una variable X comprendida en un intervalo [A, B] correspondiente a una probabilidad dada y que sigue una distribución BETA.
Imagen 25: Cuadro de dialogo argumentos función DISTR.BETA.INV
Probabilidad representa la probabilidad asociada a la variable de la distribución
(se tiene la probabilidad y se desea hallar el valor de X asociado a la misma), Alfa
y Beta son parámetros de la distribución, A y B son los límites inferior y superior,
respectivamente.
1.1.23 DISTR.BINOM:
Esta función da como resultado la probabilidad de una variable aleatoria discreta que sigue una distribución de probabilidad binomial.
Donde Núm_éxito representa la cantidad de resultados favorables en o exitosos
del experimento, Ensayos es el tamaño de la muestra o la cantidad de resultados
que pueden resultar como éxitos o fracasos, Prob_éxito es la probabilidad de
que el resultado del experimento resulte favorable, y Acumulado representa la
forma de la distribución, VERDADERO para función de distribución acumulada o FALSO para función de masa de probabilidad.
1.1.24 DISTR.CHI:
Calcula la probabilidad de ocurrencia de una variable aleatoria continua que sigue una distribución de probabilidad Chi cuadrado de una sola cola. Al insertar la función:
Imagen 27: Cuadro de dialogo argumentos función DISTR.CHI
X representa el valor de la variable a la que se le quiere calcular la probabilidad de
ocurrencia, Grados_de_libertad son los grados de libertad de la distribución. El
resultado es la probabilidad asociada a la variable aleatoria X.
1.1.25 DISTR.EXP:
Imagen 28: Cuadro de dialogo argumentos función DISTR.EXP
X es el valor al que se le quiere calcular la probabilidad de ocurrencia, Lambda es
el parámetro de la función de distribución y Acum representa la forma de la
distribución (Verdadero si es acumulada o Falso si es función de densidad)
1.1.26 DISTR.F:
Esta función da como resultado el valor de la probabilidad de una variable aleatoria que sigue una distribución F. Al insertar la función:
X es el valor de la variable aleatoria que sigue la distribución F, a la que se le
quiere averiguar la probabilidad. Grados_de_libertad son los grados de libertad
del numerador y Grados_de_libertad2 son los grados de libertad del
denominador. El resultado es la probabilidad asociada a la variable X.
1.1.27 DISTR.F.INV:
Esta función muestra el valor de una variable aleatoria que sigue una distribución F, dada una probabilidad. Para calcular la probabilidad asociada a una variable aleatoria que sigue una distribución F se usa DISTR.F y para calcular el valor de una variable aleatoria correspondiente a una probabilidad dada se usa DISTR.F.INV.
Imagen 30: Cuadro de dialogo argumentos función DISTR.F.INV
En Probabilidad se incluye el valor de la probabilidad dada para la cual se desea
conocer el valor de la variable asociada a la misma. Grados_de_libertad1 son los
grados de libertad del denominador y Grados_de_libertad2 son lo grados de
libertad del denominador.
1.1.28 DISTR.GAMMA:
Esta función se utiliza para calcular la probabilidad de una variable aleatoria que sigue una distribución Gamma. Al insertar la función:
Imagen 31: Cuadro de dialogo argumentos función DISTR.GAMMA
Donde X es el valor al que se le desea calcular la probabilidad, Alfa y Beta son
parámetros de la distribución y Acumulado define la forma de la misma,
VERDADERO para que devuelva la probabilidad acumulada y FALSO para que devuelva el valor de la probabilidad de acuerdo a la función de densidad.
1.1.29 DISTR.GAMMA.INV:
Calcula el valor de una variable aleatoria discreta que sigue una distribución Gamma referente a una probabilidad específica. Para calcular la probabilidad de una variable aleatoria se usa DISTR.GAMMA y para calcular el valor de la variable aleatoria correspondiente a una probabilidad dada se usa DISTR.GAMMA.INV.
Imagen 32: Cuadro de dialogo argumentos función DISTR.GAMMA.INV
Prob, es el valor de la probabilidad asociada a la variable aleatoria que se quiere
averiguar y Alfa y Beta son los parámetros de la distribución Gamma. El resultado
es el valor de la variable correspondiente a la probabilidad señalada
1.1.30 DISTR.HIPERGEOM:
Esta función da como resultado la probabilidad de ocurrencia de una variable aleatoria discreta cuando se conoce el tamaño de la población a la que pertenece dicha variable.
Donde Muestra_éxito son los resultados favorables que se pueden obtener de la
muestra, Núm_de_muestra es el tamaño de la nuestra seleccionada,
Población_éxito es el número de resultados favorables que se pueden obtener
de la población y Núm_de_población es el tamaño de la población del estudio.
1.1.31 DISTR.LOG.INV:
Devuelve el valor de la variable aleatoria que se distribuye logarítmico-normal,
asociada a una probabilidad acumulada, es decir, calcula el valor correspondiente a una probabilidad dada.
Imagen 34: Cuadro de dialogo argumentos función DISTR.LOG.INV
Probabilidad hace referencia a la probabilidad para la que se desea calcular el
valor de la variable aleatoria asociada, Media es el promedio de la distribución,
Desv_estándar es la desviación estándar de la distribución.
1.1.32 DISTR.LOG.NORM:
Esta función calcula la probabilidad de una variable aleatoria que se distribuye logarítmico-normal, dadas la media y la desviación estándar de la distribución.
Imagen 35: Cuadro de dialogo argumentos función DISTR.LOG.NORM
X es el valor al que se le desea calcular la probabilidad, Media es el promedio de
la distribución y Desv_estándar es la desviación estándar de la misma.
1.1.33 DISTR.NORM:
Con esta función se calcula la probabilidad asociada a una variable aleatoria que se distribuye Normal, dadas la media y la desviación estándar de la población.
X es el valor al que se desea calcular la probabilidad, Media es la media de la
distribución, Desv_estándar es la desviación estándar y Acum es un valor lógico
que define la forma de la distribución (VERDADERO para probabilidad acumulada y FALSO para la Función de distribución de probabilidad).
1.1.34 DISTR.NORM.ESTAND:
Calcula el valor de la probabilidad correspondiente a un valor z específico.
Imagen 37: Cuadro de dialogo argumentos función DISTR.NORM.ESTAND
1.1.35 DISTR.NORM.ESTAND.INV: Calcula el valor z correspondiente a una probabilidad dada
Imagen 38: Cuadro de dialogo argumentos función
1.1.36 DISTR.NORM.INV:
Devuelve el valor de la variable aleatoria que se distribuye normal, a partir una probabilidad, media y desviación estándar específicas.
Imagen 39: Cuadro de dialogo argumentos función DISTR.NORM.INV
Probabilidad es el valor de la probabilidad para la que se quiere calcular el valor
de la variable asociada. Media y Desv_estándar son los parámetros media y
desviación estándar de la distribución, respectivamente.
1.1.37 DISTR.T:
Devuelve la probabilidad de una variable aleatoria que se distribuye de acuerdo a la distribución T de student, dados los grados de libertad.
X es el valor de la variable a la que se le quiere calcular la probabilidad,
Grados_de_libertad son lo grados de libertad de la distribución (n-1) y Colas
representa el numero decolas de la distribución (1 si es de una cola y 2 si es de 2 colas).
1.1.38 DISTR.T.INV:
A partir de una probabilidad y unos grados de libertad dados, muestra el valor T correspondiente.
Imagen 41: Cuadro de dialogo argumentos función DISTR.T.INV
Probabilidad es la probabilidad asociada a la variable que se quiere calcular y
Grados_de_libertad son los grados de libertad de la distribución.
1.1.39 ERROR.TIPICO.XY:
Devuelve el valor del error típico correspondiente a cada valor de Y en una regresión, es decir, la diferencia entre el valor estimado en la regresión y el observado en los puntos dados
Imagen 42: Cuadro de dialogo argumentos función ERROR.TIPICO.XY
En Conocido_y y Conocido_x se introducen las matrices de los valores Y y X,
respectivamente.
1.1.40 ESTIMACIÓN.LINEAL:
Es una función que se utiliza para estimar los principales estadísticos de una regresión lineal, pendientes, coeficiente de determinación, estadístico F, etc. Al insertar la función:
En Conocido_y y Conocido_x se introducen las matrices de los valores Y y X,
respectivamente. Constante es un valor lógico (VERDADERO para estimar la
constante normalmente y FALSO para la que sea igual a cero). Estadística es un
valor lógico, VERDADERO si se desea estimar los estadísticos de la regresión adicionales, y FALSO si sólo se desea el valor de la pendiente y de la constante.
1.1.41 ESTIMACION.LOGARITMICA:
Es una función que se utiliza para estimar los principales estadísticos de una regresión exponencial, pendientes, coeficiente de determinación, estadístico F, etc. Al insertar la función
Imagen 44: Cuadro de dialogo argumentos función ESTIMACION.LOGARITMICA
En Conocido_y y Conocido_x se introducen las matrices de los valores Y y X
conocidos, respectivamente. Constante es un valor lógico (VERDADERO para
estimar la constante normalmente y FALSO para la que sea igual a 1). Estadística
1.1.42 FISHER
Esta Función hace una transformación Fisher, o coeficiente z, de cualquier valor de x (numérico) entre -1 y 1, excluyendo los límites, es decir, -1 y 1. Esta transformación la hace Excel utilizando la fórmula de la transformación de Fisher que es la siguiente:
1
2ln
1 1
Donde z’ es la transformación de x, x es el valor numérico que se desea hallar a transformación de Fisher.
Este valor obtenido (transformación de Fisher), tiene la característica de distribuirse normalmente. Esta transformación se utilizar para hacer pruebas de hipótesis sobre el coeficiente de correlación (Cuya función en Excel es COEF.DE.CORREL).
Para hacer la transformación en Excel primero se hace el procedimiento anteriormente indicado que consiste en insertar una función. Luego se busca en la categoría de estadísticas esta función (Fisher) como se muestra en la siguiente imagen, aunque antes de lo anterior deben ubicarse en la celda que desean que salga la transformación (Ejemplo: A1):
Luego da clic en Aceptar, posteriormente se sale el siguiente cuadro de dialogo: Imagen 46: Cuadro de dialogo argumentos función FISHER.
Como vemos se pide la ubicación del valor de x que se le va a calcular la
trasformación de Fisher, por lo que se escribe la ubicación o da clic en botón que señala, con una flecha roja, la esquina superior izquierda y posteriormente da clic en la celda (en este ejemplo B1) en la que se encuentra x tal como se muestra en
la siguiente imagen:
Imagen 47: Cuadro de dialogo argumentos función FISHER 1
Después de hacer esto presiona Enter dos veces y así en la celda B2 obtienes la transformación de Fisher del valor x que se encuentra en B1.
1.1.43 FRECUENCIA
Esta función calcula la frecuencia, o número de veces, que unos valores se repiten en un intervalo determinado. Estos valores pertenecen a un rango de datos. Como resultado Excel le devuelve una matriz (más bien un vector columna) donde se encuentra la distribución de frecuencia de los datos dadas unas especificaciones de los intervalos.
Como en la función anterior debemos ubicarnos en una celda donde se va a mostrar los resultados4. Luego insertar función, estadísticas y posteriormente,
FRECUENCIA y así Excel muestra el siguiente cuadro de dialogo:
Imagen 48: Cuadro de dialogo argumentos función FRECUENCIA
Como se ve Excel pide dos cosas: Datos y Grupos. En Datos va el rango de
datos al cual se le va a calcular la frecuencia. Ejemplo: A1:A20, esto quiere decir que los datos se encuentran en un vector que va de la celda A1 hasta la celda A20. En Grupos va el rango de los valores que van a formar los intervalos.
El primer intervalo va mostrar la frecuencia de todos los valores menores o iguales al primer valor del rango de Grupos; el segundo los mayores al primer valor del
4 Para esta función, así como para otras, es necesarios ubicarse en una celda que tenga espacio
rango de Grupos y menores o iguales al segundo, así sucesivamente hasta el
penúltimo intervalo; el último intervalo mostrará la frecuencia de aquellos valores, que pertenecen al intervalo ingresado en Datos, que son mayores al último valor
del rango Grupos.
Después de ingresar5 el rango Datos y Grupos se da clic en Aceptar. Luego se ubica sobre la celda que inicialmente se había ubicado para insertar la función. Selecciona n+1 celdas hacia abajo (esto incluyendo donde se escribió la formula
inicialmente que debe ser la primera), donde n es el número de valores que
forman los intervalos. Ejemplo: el rango de Grupos está formado por los
siguientes valores {10, 20, 30, 40} entonces n es 4 por lo que se deben
seleccionar 5 celdas.
Al seleccionar las celdas presione F2; posteriormente Mayús + Enter para mostrar la matriz completa (vector columna), es decir, la distribución de frecuencia.
1.1.44 GAMMA.LN
Esta función calcula el logaritmo natural de la función gamma Γ . En formulación matemática sería:
. ln Γ x
Donde Γ ;
Para que Excel haga este cálculo se da clic en la celda donde se quiere colocar el logaritmo (resultado). Luego Insertar función, estadística, GAMMA.LN y aceptar. De esta forma Excel muestra el siguiente cuadro de dialogo:
5
Se recuerda al lector que puede utilizar la forma que más le agrade para agregar estos rangos.
Imagen 49: Cuadro de dialogo argumentos función GAMMA.LN
Como vemos hay que darle la ubicación del valor x que es una función gamma.
1.1.45 INTERSECCION.EJE
Esta función calcula el punto de intersección con el eje Y de una función de X
óptima a partir de unos valores de Y (variable dependiente) conocidos y unos
valores de X (variable independiente). Esta función óptima la calcula Excel
utilizando el método de Estimación Lineal (mínimos cuadrados).
Para que Excel haga este cálculo, se ubica el cursor en la celda que se quiere que se muestre el intercepto, luego insertar función, estadísticas, INTERSECCION.EJE y aceptar. Haciendo esto Excel muestra el siguiente cuadro de dialogo:
Como se ve en la imagen 9 Excel pide el rango de valores de Y conocidos
(Conocido_y) y el rango de valores de X conocidos (Conocido_x), luego de
haber ingresado estos rango se da aceptar y de esta forma se obtiene el valor del intercepto. Cabe aclarar que el tamaño del rango de Y debe ser el mismo que el
de X.
1.1.46 INTERVALO.CONFIANZA
Esta función sirve para calcular el intervalo de confianza para la media poblacional a partir de cualquier media muestral , un nivel de significancia dado (que está entre 0 y 1) , una desviación estándar conocida así como el tamaño de la muestra (n) la cual es decir:
√
Z es el valor de la distribución normal que corresponde a una probabilidad
mayor o igual a 1- . El lector recordará de su curso de Estadística Inferencial que √ corresponde a la desviación de las medias muéstrales para poblaciones infinitas. Excel lo único que calcula es la parte derecha de la ecuación de forma positiva.
Para que Excel calcule la parte derecha de la ecuación, se ubica el cursor en la celda en la que se quiere salga el resultado, luego insertar función, estadísticas, INTERVALO.CONFIANZA y aceptar; se obtiene el siguiente cuadro de dialogo:
Como se ve en el cuadro de dialogo hay que indicarle la ubicación donde se encuentra el nivel de significancia en Alfa así como la de la desviación estándar
en Desv_estándar y el tamaño de la muestra en Tamaño. Luego se da Enter y
así se obtiene la parte derecha de la ecuación anteriormente mostrada.
Después de tener esto, se puede plantear el intervalo de confianza para la media poblacional dado el nivel de significancia; sumándole a la media muestral el valor calculado con esta fórmula para así obtener el límite superior del intervalo de confianza y restándole el mismo valor calculado so obtiene el límite inferior, es decir, aplicando la ecuación.
1.1.47 JERARQUÍA
Esta función muestra la jerarquía o posición de un valor en un conjunto de valores que pueden estar ordenados de forma ascendente o descendente.
Para ver la posición o puesto de un valor en un conjunto de valores se da clic en la celda donde se desea que se coloque el puesto del número, luego insertar fórmula, estadísticas, JERARQUIA y aceptar y se obtiene el siguiente cuadro de dialogo:
Como vemos Excel pide la ubicación del número que se le va a calcular su posición en un conjunto de datos en Número, el rango del conjunto de datos en
Referencia y la forma como están ordenados los valor en Orden (0 si están
ordenado en forma descendente y un número diferente de 0 si están en forma ascendente). Después de ingresar los respectivos valores se da aceptar y Excel te muestra la ubicación.
Esta función tiene algunos problemas porque al repetirse algún valor y a su vez se busca su posición, Excel te mostrará la ubicación del primero.
1.1.48 K.ESTIMO.MAYOR
Esta función se muestra el valor del valor k-ésimo valor mayor de una matriz de valores, sin importar sus dimensiones.
Para saber cuál es el valor que ubica la posición k mayor se debe dar clic en la celda que desee que lo muestre, luego inserta fórmula, estadística, K.ESTIMO.MAYOR y aceptar. Excel muestra el siguiente cuadro de dialogo:
Imagen 53: Cuadro de dialogo argumentos función K.ESTIMO.MAYOR
valor de k en k. Si k =1, Excel mostrará el valor mayor de la matriz indicada; si es
igual a dos te mostrará el segundo mayor valor de la matriz y así sucesivamente.
1.1.49 K.ESTIMO.MENOR
Esta función es vez de mostrar el mayor valor de una matriz determinada muestra el menor. El cuadro de dialogo correspondiente a esta función es:
Imagen 54: Cuadro de dialogo argumentos función K.ESTIMO.MENOR
Tanto en Matriz como en k se hace lo mismo que en la función anterior. En este
caso si k=1, Excel mostrará el valor menor de la matriz: si es igual a dos mostrará el segundo menor de la matriz y así sucesivamente.
1.1.50 MAX
Esta función muestra el valor máximo de una o varias matrices (Excel tiene capacidad de hasta 255 matrices). En caso de que la matriz sea de 1x1 ésta será un número tal como lo pide Excel.
Imagen 55: Cuadro de dialogo argumentos función MAX
Vemos que Excel pide la ubicación de las matrices en Número1, Número2. Se
había dicho que se podía incluir hasta 255 matrices pero en el cuadro de dialogo sólo aparecen dos. Para que salga la posibilidad de ingresar la tercera matriz hay que dar clic en Número2, de esta forma sale Número3; si se da clic en este último
sale Número4, es decir, Excel te va ofreciendo una cada vez sólo hasta la 255.
Cabe resaltar que estas matrices pueden ser de distintas dimensiones.
1.1.51 MAXA
Esta función devuelve el mayor valor de una matriz o varias. Esta función se diferencia de la anterior (MAX) en que estas matrices pueden incluir valores lógico como VERDADERO y FALSO. Para Excel la palabra VERDADERO tiene una equivalencia de 1 y FALSO 0.
(equivalencia de FALSO). Si se presenta el caso anterior y se le suma la presencia de VERDADERO el resultado será 1.
Imagen 56: Cuadro de dialogo argumentos función MAXA
Para que Excel haga el respectivo cálculo se siguen los mismos pasos de la función anterior. Esta función también soporta hasta 255 matrices.
1.1.52 MEDIA.ACOTADA
Imagen 57: Cuadro de dialogo argumentos función MEDIA.ACOTADA
Como se ve Excel pide la ubicación de la matriz a la cual se desea calcula la media acotada en Matriz, además pide la ubicación donde se encuentra el
porcentaje de valores6(que está entre 0 y 1) que se desean eliminar en
Porcentaje.
Si el porcentaje que usted indica es 100% (1), Excel mostrará un error. Esto porque ha eliminado todos los valores y por lo tanto estará calculando la media de nada.
1.1.53 MEDIA.ARMO
Por medio de esta función Excel calcula la media armónica de una o varias matrices (como máximo 255) cuyos elementos sean mayores a cero. Excel emplea la fórmula de la media armónica que es:
1
Donde n es el número de elementos de las matrices.
es el valor de cada
elemento de las distintas matrices.En la siguiente imagen vemos el cuadro de dialogo correspondiente a esta función:
Imagen 58: Cuadro de dialogo argumentos función MEDIA.ARMO
Como vemos hay que indicarle la ubicación de cada una de las matrices. No es necesario que las matrices tengan las mismas dimensiones.
1.1.54 MEDIA.GEOM
Esta función calcula la media geométrica de una o varias matrices (hasta de 255). La media geométrica Excel la calcula con la siguiente fórmula:
…
Al igual que en la función anterior se ingresa la ubicación de las distintas matrices a las que se le va a calcula la media geométrica. Para este cálculo no necesariamente las matrices deben tener la misma dimensión. En la imagen 18 se muestra el cuadro de dialogo de esta función:
Imagen 59: Cuadro de dialogo argumentos función MEDIA.GEOM
1.1.55 MEDIANA
Imagen 60: Cuadro de dialogo argumentos función MEDIANA
En la imagen 19 se muestra el cuadro de dialogo para esta función. Las matrices se ingresan como en la función anterior. No es necesario que las matrices sean de la misma dimensión.
1.1.56 MIN
Esta función calcula el valor mínimo de una o varias matrices (máximo de 255). El cuadro de dialogo de esta función es el siguiente:
La matriz, o las matrices, ingresa de la misma forma como se ha venido explicando. Tampoco es necesario que las matrices tengan la misma dimensión.
1.1.57 MINA
Esta función devuelve el menor valor de una matriz o varias (hasta 255). Esta función se diferencia a la anterior en que estas matrices pueden incluir valores lógico como VERDADERO y FALSO. Para Excel la palabra VERDADERO tiene una equivalencia de 1 y FALSO 0.
En caso de que en los argumento (matrices) hayan valores mayores a cero y a la vez la palabra FALSO Excel devolverá como valor mínimo 0 (la equivalencia de FALSO).
Imagen 62: Cuadro de dialogo argumentos función MINA
La ubicación de la matriz, o las matrices, se ingresa como se ha explicado en las funciones anteriores. Esta función también se puede aplicar a matrices de distintas dimensiones.
1.1.58 MODA
Esta función calcula la moda (valor que se repite con más frecuencia o número de veces) de una o varias matrices (máximo 255). A partir de este criterio Excel calcula moda de un conjunto de datos formado por una o más matrices.
En la imagen 22 se muestra el cuadro de dialogo correspondiente a esta función. Este al igual que muchas de las funciones ya explicadas pide la ubicación de las distintas matrices a la cual se le va a calcular la moda.
Imagen 63: Cuadro de dialogo argumentos función MODA
1.1.59 NEGBINOMDIST
Es función devuelve la probabilidad de obtener una cantidad determinada de fracasos antes que una de éxitos, dada la probabilidad de éxito. Esto equivale a la distribución binomial negativa.
, , 11 1
Donde x es el número de fracasos. r el número de éxitos
p es la probabilidad de éxito.
El primer término de la parte derecha representa combinación.
En la siguiente imagen se muestra el cuadro de dialogo de esta función: Imagen 64: Cuadro de dialogo argumentos función NEGBINOMDIST
Excel pide la ubicación del número de fracasos en Núm_fracasos, el de número
de éxitos en Núm_éxtios y la probabilidad de éxito en Prob_éxito. Tanto el
número de fracasos como el de éxitos deben ser enteros positivos y la probabilidad debe estar entre 0 y 1.
1.1.60 NORMALIZACION
Imagen 65: Cuadro de dialogo argumentos función NORMALIZACION
La ecuación para esta normalización es:
En la imagen 24 se muestra el cuadro de dialogo para esta función. Se pide la ubicación de la celda donde se encuentra la media en Media, la desviación
estándar en Desv_estándar y el valor de x en X.
1.1.61 PEARSON
Esta función calcula el coeficiente de correlación productor entre una matriz independiente y una dependiente, las cuales tienen la misma dimensión excepto matrices de 1x1, porque no se podría calcular una correlación entre dos número que están en el mismo momento.
Imagen 66: Cuadro de dialogo argumentos función PEARSON
Excel pide la ubicación de ambas matrices: la independiente en Matriz1 y la
dependiente en Matriz2.
1.1.62 PENDIENTE
Anteriormente se explicó una formula que calcula el intercepto en Y de una función
estimada con el método de estimación lineal (mínimos cuadrados) a partir de valores de Y conocidos así como de X donde Y depende de X. Esta función
muestra la pendiente de la misma estimación.
Imagen 67: Cuadro de dialogo argumentos función PENDIENTE
Como se ve en la imagen 26 se debe indicar la ubicación de los valores de Y en
Conocido_y y los de X en Conocido_x7.
1.1.63 PERCENTIL
Esta función de Excel permite calcular el k-ésimo percentil de un conjunto de valores a partir del valor del percentil (%). El cuadro de dialogo para esta función es el que se presenta a continuación:
Imagen 68: Cuadro de dialogo argumentos función PERCENTIL
Se ve que Excel pide la ubicación de la matriz a la cual se le va a calcular el percentil k-ésimo en Matriz, además hay que indicar dónde se encuentra el valor
del percentil el cual está entre 0 y 1.
1.1.64 PERMUTACIONES
Esta función permite calcular el número de permutaciones que se pueden hacer a partir de un número determinado de objetos indicando el número de estos que son incluidos en cada permutación.
La siguiente imagen muestra el cuadro de dialogo para esta función:
Imagen 69: Cuadro de dialogo argumentos función PERMUTACIONES
Como se observa, Excel pide la ubicación del número total de objetos con los que se va a realizar el cálculo de las permutaciones, en Número, así como el número
de estos incluidos en cada permutación en Tamaño. 1.1.65 POISSON
Esta función calcula la distribución de Poisson o probabilidad de la distribución Poisson. Esta distribución sirve para estimar la probabilidad de que un número de eventos en un tiempo determinado a partir de una media. El cuadro de dialogo de esta función es el siguiente:
Imagen 70: Cuadro de dialogo argumentos función POISSON
En x es el de eventos que se desea evaluar su probabilidad de ocurrencia; en
Media es el número de eventos que se espera que se presenten (esto es
determinado a partir de datos históricos) y Acumulado significa que si se quiere la
probabilidad acumulada (esta es la probabilidad de que ocurra desde 0 eventos a
x) o especifica (probabilidad de x). Para que calcule la probabilidad acumulada se
escribe VERDADERO; si es específica FALSO.
1.1.66 PROBABILIDAD
Esta función calcula la probabilidad de que unos valores de un determinado rango se encuentren en un intervalo.
Imagen 71: Cuadro de dialogo argumentos función PROBABILIDAD
En la imagen anterior se ve los argumentos que pide Excel para el cálculo de esta función. En Rango_x va la ubicación de los valores que se desea calcular la
probabilidad; en Rango_probabilidad va un rango que corresponde a una
inferior del intervalo con el que se va a evaluar el Rango_x y en Límite_sup va el
límite superior de este mismo intervalo.
En caso de que sólo ingrese el límite inferior Excel te dará la probabilidad de que los valores del rango sean iguales a éste (límite inferior), es decir, la probabilidad asociada a él si está en Rango_x sino será 0. Si ingresa los dos sí le calculará la
proposición inicial8.
1.1.67 PROMEDIO
Con esta función Excel calcula el promedio o media aritmética de los elementos de una o varias matrices (hasta 255), sin importar que las dimensiones de éstas sea diferente. Como se observa en la siguiente imagen Excel pide la ubicación de cada una de las matrices en Número1 hasta Número255.
Imagen 72: Cuadro de dialogo argumentos función PROMEDIO
1.1.68 PROMEDIO.SI
Esta calcula lo mismo que la anterior a diferencia que ésta sólo calcula el promedio de una única matriz a partir de una condición (criterio). Este criterio también está en una matriz que debe tener las mismas dimensiones que la matriz a la que se le piensa calcular el promedio.
La siguiente imagen muestra el cuadro de dialogo que corresponde a esta función: Imagen 73: Cuadro de dialogo argumentos función PROMEDIO.SI
En Rango va la ubicación del matriz de criterios, estos criterios pueden ser
numéricos (igualdad o desigualdad; ejemplo: >4, =2, >=0, etc.) o texto; en Criterio
va el criterio con el que se va a calcular el promedio y en Rango_promedio va el
rango al que se le calcula el promedio. Cabe aclarar que Excel evaluará de la siguiente forma:
Si el elemento del rango de criterio cumple con el criterio, valga la redundancia, Excel incluirá el elemento que está en la misma ubicación en el
1.1.69 PROMEDIO.SI.CONJUNTO
Esta función hace lo mismo que la anterior a diferencia que ésta permite tener en cuenta más de un criterio (hasta 127) para calcular el promedio. En la anterior se había dicho que el criterio podía ser numérico o texto; para esta fórmula cambia dado que se pueden incluir tanto criterios numéricos como de texto. En caso de que se haga hagan varios criterios Excel tomará los valores que cumplan todos los criterios que se realicen9.
Imagen 74: Cuadro de dialogo argumentos función
PROMEDIO.SI.CONJUNTO
La anterior imagen corresponde al cuadro de dialogo de esta función. En
Rango_promedio va la matriz a la que se le va a calcular el promedio, en
Rango_criterios1 va la matriz de criterio, en Criterio1 va el criterio y así
sucesivamente.
1.1.70 PROMEDIOA
Esta función calcula el promedio de una matriz o varias (hasta 255) que contienen tanto valores numéricos como texto y valores lógicos (VERDADERO FALSO). Excel reconoce el texto como si figuraran cero en la celda así como la palabra FALSO y Verdadero como uno.
A esta se le ingresan los argumentos como se explico en la fórmula PROMEDIO. El cuadro de dialogo de ésta es el que se presenta a continuación:
Imagen 75: Cuadro de dialogo argumentos función PROMEDIOA
1.1.71 PRONOSTICO
Imagen 76: Cuadro de dialogo argumentos función PRONOSTICO
En la anterior imagen representa el cuadro de dialogo de esta función. En x hay
que incluir el valor con el cual va a predecir uno de y (variable dependiente de x);
en Conocido_y va los valores de y o variable dependiente y en Conocido_x los
valores de x conocido. Tanto el rango, o número de valores, de x como el de y
conocido debe ser igual.
En el caso de que no quiera predecir un solo valor de y sino varios, puede escribir
todos los valores de x (rango), ingresar tanto y como x conocido y dar Enter. Le va
a resultar algo así #¡VALOR!, esto es porque el resultado es una lista de
pronósticos (uno para cada valor de x) y por lo tanto debe mostrarla10.
1.1.72 PRUEBA.CHI
Esta función calcula el grado de independencia de unos datos reales y unos esperados (que están en matrices de igual dimensión) con unos grados de libertad ajustados por Excel. El valor de esta prueba se encuentra entre 0 y 1; en el caso de que los valores reales tiendan a ser iguales a las esperadas el valor se
acercara a 1 y si es lo contrario el valor seria 0. El cuadro de de dialogo de esta función es el siguiente:
Imagen 77: Cuadro de dialogo argumentos función PRUEBA.CHI
En Rango_actual va la matriz de datos observados o reales y en
Rango_esperado la matriz de datos esperados.
1.1.73 PRUEBA.CHI.INV
Esta función devuelve el valor inverso a una probabilidad dada para una distribución chi cuadrado ( ) para un número determinado de grados de libertad, con una cola. En la siguiente imagen se muestra el cuadro de dialogo de esta función:
En Probabilidad va la probabilidad que se quiere hallar el valor inverso y en
Grados_de_libertad, como su nombre lo dice, los grados de libertad.
1.1.74 PRUEBA.F
Esta función calcula la prueba F, es decir, calcula el doble de la probabilidad de que la varianza de los elementos de dos matrices sea, estadísticamente, la misma. A continuación se muestra el cuadro de dialogo de esta función:
Imagen 79: Cuadro de dialogo argumentos función PRUEBA.F
1.1.75 PRUEBA.FISCHER.INV
Esta función devuelve la inversa de la transformación de Fisher (la que explicamos anteriormente). Para esto lo único que hay que indicarle a Excel el valor de la transformación y él nos devuelve el de x11
Imagen 80: Cuadro de dialogo argumentos función PRUEBA.FISHER.INV
1.1.76 PRUEBA.T
Esta función calcula la probabilidad a partir de la distribución T de Student. Esta función calcula la probabilidad de que dos muestras procedan de dos poblaciones subyacentes con igual media.
Imagen 81: Cuadro de dialogo argumentos función PRUEBA.T
En la imagen anterior se muestra el cuadro de dialogo de esta función. En Matriz1
van los datos correspondientes a una muestra; en Matrioz2 los de la muestra dos;
que quieran utilizar una cola iría 1 si quieren dos seria 2) y en Tipo hace referencia al tipo de prueba t: pareado = 1, 2 si las muestras tienen igual varianza y 3 si tienen distinta varianza.
1.1.77 PRUEBA.Z
Esta función calcula la probabilidad de una cola a partir de la distribución normal. Sirve para calcula la probabilidad de que un valor cualquiera sea mayor que la media poblacional.
En la siguiente imagen se muestra el cuadro de dialogo de esta función: Imagen 82: Cuadro de dialogo argumentos función PRUEBA.Z
En Matriz van los datos con los que se van a comparar x; en x el valor que se
desea evaluar y en Sigma va la desviación estándar poblacional.
Para hacer la misma prueba pero con dos colas apliquen la siguiente formula: =2 * MIN(PRUEBA.Z(matriz,x,sigma), 1 - PRUEBA.Z(matriz,x,sigma))12.
1.1.78 RANGO.PERCENTIL
Esta función calcula el rango percentil de un valor determinado. Excel lo hace de la siguiente forma:
Sea a el valor al cual se le va a calcular el rango percentil. Ésta será el número de
valores del rango que son menores a a entre el número total de valores menos
(esto porque se toma el número de valores mayores y menores a a).
El cuadro de dialogo de esta función es:
Imagen 83: Cuadro de dialogo argumentos función RANGO.PERCENTIL
En Matriz va el rango de valores del cual se va a estimar el rango percentil de x,
en x el valor al cual se le va a estimar el rango percentil y en Cifra_significativa
va el número de decimales del rango; en caso de que no se coloque nada Excel por default coloca tres decimales.
1.1.79 TENDENCIA
importar de qué). El cuadro de dialogo para esta función se presenta a continuación:
Imagen 84: Cuadro de dialogo argumentos función TENDENCIA
En Conocido_y va los datos conocidos de las variable dependiente (ésta es la
variable que se desea ver la tendencia); en Conocido_x datos variable
independiente; en Nueva_matriz_x van los nuevos valores que va a tomar la
variable independiente y constante hace referencia a si se quiere aplicar el método de mínimos cuadrados con o sin constante: con VERDADERO hará la estimación con constante; con FALSO lo hará sin constante.
1.1.80 VAR
Así como se a explica la inclusión de distintas matrices para el cálculo de promedio u otra cosa así mismo se incluyen matrices para obtener la varianza de una o un conjunto de matrices.
En la siguiente imagen se muestra el cuadro de dialogo de esta función de Excel:
Imagen 85: Cuadro de dialogo argumentos función VAR
1.1.81 VARA
Imagen 86: Cuadro de dialogo argumentos función VARA
1.1.82 VARP
Esta función calcula la varianza de la población a partir de de valores muéstrales que se pueden registrar en una o varias matrices (máximo 255) que no necesitan tener la misma dimensión. Esta función omite valores lógicos y textos al igual que celdas vacías (del cualquiera matriz), pero no todas.
Para ingresar las matrices, para hacer el cálculo, se hace como sea hecha anteriormente. En la siguiente imagen se muestra su cuadro de dialogo:
Imagen 87: Cuadro de dialogo argumentos función VARP
1.1.83 VARPA
Imagen 88: Cuadro de dialogo argumentos función VARPA
1.2 Aplicación de la Funciones Estadísticas
2. Análisis de Datos
Cuando trabajamos con EXEL también poseemos la opción de analizar datos estadísticos lo cual nos puede ayudar a determinar una conclusión de una Muestra ó Población
Existen varias formas de analizar datos y nosotros la utilizaremos dependiendo nuestra situación
¾ En caso de que no tengamos ésta aplicación podemos buscarla en opciones de Excel, complementos, ir.
¾ Luego escogemos las opciones herramientas para análisis y herramientas para análisis –VBA.
2.1 Análisis de varianza de un factor:
Esta herramienta realiza un análisis simple de varianza en los datos de dos o más muestras. Si queremos comparar la relación entre las varianzas de dos ó más factores entonces podemos hacer por ejemplo:
N° de fallas de maquinas N° de retrasos empleados
5 4
3 5
6 4
9 8
3 3
4 3
Queremos analizar la relación de la varianza entre el número de fallas de la maquinaria de una fábrica y el número de retrasos de los empleados.
Procedimiento: ya teniendo la opción de análisis de datos, nos vamos en la barra de herramientas y buscamos datos y picamos en análisis de datos y escogemos la opción Análisis de varianza de un factor que es la de nuestro ejemplo. Luego nos saldrá un cuadro de dialogo así:
Imagen 89: Cuadro de dialogo Análisis de Varianza
Y expresamos las especificaciones deseadas.
Luego damos aceptar y dependiendo donde queremos que EXEL nos muestre el análisis, él arrojará los datos con el análisis deseado.
Algo como esto: Análisis de varianza de un factor
RESUMEN
Grupos Cuenta Suma Promedio Varianza
N° de fallas de maquinas 6 30 5 5.2
N° de retrasos empleados 6 27 4.5 3.5
ANÁLISIS DE VARIANZA
Origen de las variaciones Suma de cuadrados
Grados de libertad
Promedio de los
cuadrados F
Entre grupos 0.75 1 0.75 0.17241379
Dentro de los grupos 43.5 10 4.35
Total 44.25 11
2.2 Análisis de varianza de dos factores con varias muestras por
grupo:
Excel también nos ofrece la posibilidad de analizar datos cuando se pueden clasificar de acuerdo con dos dimensiones diferentes.
Por ejemplo:
Niños Niñas
Clima frio 2 2
1 3
0 4
Clima
Cálido 3 1
1 3
2 2
Nota: A diferencia del análisis de varianza de un factor, debemos tener en cuenta de que en el análisis de varianza de dos factores con varias muestras por grupo nos pedirán en el cuadro de dialogo nos solicitarán el numero de filas por cada muestra, debemos tener en cuenta de que el numero de filas sea el mismo por cada prueba que hagamos como se ilustra en el ejemplo anterior.
A continuación veremos el análisis que hace Excel estando en esta situación: ANÁLISIS DE VARIANZA
Origen de las variaciones
Suma de cuadrados
Grados de libertad
Promedio de los
cuadrados F
Muestra 0 1 0 0
Columnas 3 1 3 3
Interacción 3 1 3 3
Dentro del grupo 8 8 1
Total 14 11
2.3 Análisis de varianza de dos factores con una sola muestra por
grupo:
Utilizamos este tipo de análisis cuando los datos se clasifican en dos dimensiones diferentes. Pero se supone que existe una única observación para cada par.
Al igual que el caso anterior podemos tener el mismo ejemplo pero con una sola muestra por grupo.
Análisis de varianza de dos factores con una sola muestra por grupo
RESUMEN Cuenta Suma Promedio Varianza
Clima frio 2 4 2 0
2 4 2 2
2 4 2 8
Niños 3 3 1 1
Niñas 3 9 3 1
ANÁLISIS DE VARIANZA
Origen de las
variaciones
Suma de
cuadrados
Grados de
libertad
Promedio de los
cuadrados F Probabilidad
Filas 0 2 0 0 1
Columnas 6 1 6 3 0.22540333
Error 4 2 2
Total 10 5
2.4 Coeficiente de correlación:
Es un índice estadístico que mide la relación lineal entre dos variables cuantitativas. A diferencia de la covarianza, la correlación de Pearson es independiente de la escala de medida de las variables
• Si r = 0, no existe relación lineal. Pero esto no necesariamente implica una independencia total entre las dos variables, es decir, que la variación de una de ellas puede influir en el valor que pueda tomar la otra. Pudiendo haber relaciones no lineales entre las dos variables. Estas pueden calcularse con la razón de correlación.
• Si r = 1, existe una correlación positiva perfecta. El índice indica una dependencia total entre las dos variables denominada relación directa:
cuando una de ellas aumenta, la otra también lo hace en idéntica proporción.
• Si 0 < r < 1, existe una correlación positiva.
• Si r = -1, existe una correlación negativa perfecta. El índice indica una dependencia total entre las dos variables llamada relación inversa: cuando
una de ellas aumenta, la otra disminuye en idéntica proporción. • Si -1 < r < 0, existe una correlación negativa.
Ejemplo: Observaremos las notas de 5 estudiantes de cierto colegio en dos
