3. Estadistica Con Excel y Con Wolfram Alpha
-
Upload
robinson-moreno -
Category
Documents
-
view
65 -
download
4
description
Transcript of 3. Estadistica Con Excel y Con Wolfram Alpha
-
Apuntes de clase MANEJO DE EXCEL PEDRO SUAREZ SANCHEZ
ESTADISTICA CON EXCEL1
CONTAR.SI Cuenta las celdas, dentro del rango, que no estn en blanco y que cumplen los Criterios especificados .Sintaxis
CONTAR.SI(rango; criterio)
Rango es la celda o las celdas que se van a contar; deben contener nmeros, o
nombres, matrices o referencias que contengan nmeros. Los valores en blanco y los de
texto no se tienen en cuenta.
Criterio es el criterio en forma de nmero, expresin, referencia a celda o texto, que
determina las celdas que se van a contar. Por ejemplo, los criterios pueden expresarse
como 32, "32", ">32", "manzanas" o B4.
EJEMPLO
cuenta cuantos nmeros son mayores a 100
MAXIMO Y MINIMO Devuelve el valor mximo O mnimo de un conjunto de valores segn el caso
1 Las definiciones de las funciones de Excel, la sintaxis y las formulas se tomaron de la ayuda
de Excel
-
Apuntes de clase MANEJO DE EXCEL PEDRO SUAREZ SANCHEZ
Sintaxis
MAX(nmero1;nmero2; ...)
Nmero1, nmero2... son de 1 a 255 nmeros de los que desea encontrar el valor
mximo.
hallar el mximo y el mnimo de una serie de valores:
K ESIMO MAYOR Y K ESIMO MENOR
Devuelve el k-simo mayor valor de un conjunto de datos. Esta funcin puede usarse
para seleccionar un valor basndose en su posicin relativa. Por ejemplo, se puede
utilizar K.ESIMO.MAYOR para devolver el mayor valor de un resultado, el segundo
resultado o el tercero.
-
Apuntes de clase MANEJO DE EXCEL PEDRO SUAREZ SANCHEZ
Matriz es una matriz o un rango de datos numricos cuyo k-simo mayor valor desea
determinar.
K es la posicin, dentro de la matriz o del rango de datos, de los datos que se van a
devolver, determinada a partir del mayor de los valores
Devuelve el k-simo menor valor de un conjunto de datos. Utilice esta funcin para
devolver valores con una posicin relativa especfica dentro de un conjunto de datos.
Sintaxis
K.ESIMO.MENOR (matriz)
Matriz es una matriz o un rango de datos numricos cuyo k-simo menor valor desea
determinar.
K es la posicin, dentro de la matriz o del rango de datos, de los datos que se van a
devolver, determinada a partir del menor de los valores.
caso: Hallar el cuarto ms grande valor y el tercero ms pequeo
-
Apuntes de clase MANEJO DE EXCEL PEDRO SUAREZ SANCHEZ
MEDIDAS DE TENDENCIA CENTRAL
Promedio Es la media aritmtica y se calcula sumando un grupo de nmeros y
dividiendo a continuacin por el recuento de dichos nmeros. Por ejemplo, el
promedio de:
.
Mediana Es el nmero intermedio de un grupo de nmeros; es decir, la mitad de
los nmeros son superiores a la mediana y la mitad de los nmeros tienen valores
menores que la mediana. Por ejemplo, la mediana de :
-
Apuntes de clase MANEJO DE EXCEL PEDRO SUAREZ SANCHEZ
Moda Es el nmero que aparece ms frecuentemente en un grupo de nmeros (ES EL QUE MAS SE REPITE). Por ejemplo:
Media Geomtrica Devuelve la media geomtrica de una matriz o de un rango de datos positivos. Por ejemplo, es posible utilizar la funcin MEDIA.GEOM para calcular la tasa de crecimiento promedio, dado un inters compuesto por tasas variables
-
Apuntes de clase MANEJO DE EXCEL PEDRO SUAREZ SANCHEZ
MEDIA ARMNICA Devuelve la media armnica de un conjunto de datos. La media armnica es la inversa de la media aritmtica de los valores recprocos.
PARAMETROS VAR: Calcula la varianza de una muestra.
-
Apuntes de clase MANEJO DE EXCEL PEDRO SUAREZ SANCHEZ
VARP: Calcula la varianza en funcin de toda la poblacin.
DESVEST: Calcula la desviacin estndar de una muestra. La desviacin
estndar es la medida de la dispersin de los valores respecto a la media (valor
promedio). :
-
Apuntes de clase MANEJO DE EXCEL PEDRO SUAREZ SANCHEZ
DESVESTP: Calcula la desviacin estndar de la poblacin total determinada por los argumentos. La desviacin estndar es la medida de la dispersin de los valores respecto a la media (valor promedio).
CUARTIL Devuelve el cuartil de un conjunto de datos. Los cuartiles se usan con frecuencia en los datos de ventas y encuestas para dividir las poblaciones en grupos. Por ejemplo, puede utilizar la funcin CUARTIL para determinar el 25 por ciento de ingresos ms altos en una poblacin.
Sintaxis
CUARTIL(matriz; cuartil)
Matriz es la matriz o rango de celdas de valores numricos cuyo cuartil desea obtener.
Cuartil indica el valor que se devolver.
Si cuartil es igual a
La funcin CUARTIL devuelve
0 Valor mnimo
1 El primer cuartil (percentil 25)
2 El valor de la mediana (percentil 50)
3 El tercer cuartil (percentil 75)
-
Apuntes de clase MANEJO DE EXCEL PEDRO SUAREZ SANCHEZ
4 Valor mximo
NUMEROS ALEATORIOS
ALEATORIO Devuelve un nmero real aleatorio mayor o igual a 0 y menor que 1,
distribuido uniformemente. Cada vez que se calcula la hoja de clculo, se devuelve un
nmero real aleatorio nuevo.
Si desea usar ALEATORIO para generar un nmero aleatorio pero no desea que
los nmeros cambien cada vez que se calcule la celda, puede escribir
=ALEATORIO () en la barra de frmulas y despus presionar la tecla F9 para
cambiar la frmula a un nmero aleatorio.
ALEATORIO.ENTRE. Devuelve un nmero entero aleatorio entre los nmeros que
especifique. Devuelve un nuevo nmero entero aleatorio cada vez que se calcula la hoja
de clculo
-
Apuntes de clase MANEJO DE EXCEL PEDRO SUAREZ SANCHEZ
REGRESION CORRELACION Y PROYECCIONES
La regresin (o tendencia) es una de las herramientas estadsticas utilizadas para
efectuar las estimaciones o proyecciones de las ventas, igualmente se utiliza en la
determinacin de los costos fijos y los costos variables
Existen varias opciones de lnea de tendencia o regresin, entre otras el programa Excel
nos presenta las siguientes:
Cuando el Excel trata la estimacin lineal2
Calcula las estadsticas de una lnea utilizando el mtodo de "mnimos cuadrados" para
calcular la lnea recta que mejor se ajuste a los datos y, a continuacin, devuelve una
matriz que describe la lnea. Tambin puede combinar ESTIMACION.LINEAL con otras
funciones para calcular las estadsticas para otros tipos de modelos que son lineales en
los parmetros desconocidos, incluidas series polinmicas, logartmicas, exponenciales y
2 Ver ayudas en Excel
-
Apuntes de clase MANEJO DE EXCEL PEDRO SUAREZ SANCHEZ
series de potencias. Debido a que esta funcin devuelve una matriz de valores, debe ser
especificada como una frmula de matrices.
La ecuacin para la lnea recta es:
y = mx + b o
y = m1x1 + m2x2 + ... + b (si hay varios rangos de valores X)
Donde el valor Y dependiente es funcin de los valores X independientes. Los valores m
son coeficientes que corresponden a cada valor X, y b es un valor constante. Observe
que Y, X y m pueden ser vectores. La matriz que devuelve
La exactitud de la lnea calculada por ESTIMACION.LINEAL depende del grado de
dispersin de los datos. Cuanto ms lineales sean los datos, ms exacto ser el modelo
de ESTIMACION.LINEAL. ESTIMACION.LINEAL utiliza el mtodo de mnimos cuadrados
para determinar el mejor ajuste para los datos. Si slo tiene una variable X dependiente,
los clculos para m y b se basan en las frmulas siguientes:
Donde x e y son medias de muestras, es decir, x = PROMEDIO (conocido) e y =
PROMEDIO (conocido).
Ejemplo de estimacin de ventas:
Se tiene las siguientes ventas y se quiere estimar las ventas para el periodo 7
PROMEDIO X 3,5
PROMEDIO Y 2350
Se elabora el siguiente cuadro:
X=PERIODO Y=VENTAS EN MILES DE PESOS
1 1170 2 1800 3 2500 4 2550 5 2600 6 3480
-
Apuntes de clase MANEJO DE EXCEL PEDRO SUAREZ SANCHEZ
X menos promedio X
Y menos promedio Y
(X menos promedio X) por (y menos promedio y)
cuadrado de(x menos promedio x)
-2,5 -1180 2950 6,25
-1,5 -550 825 2,25
-0,5 150 -75 0,25
0,5 200 100 0,25
1,5 250 375 2,25
2,5 1130 2825 6,25
Sumas 7000 17,5
Utilizando la formula
Obtenemos:
m = 400
Y remplazando en
; Obtenemos:
b= 950
Y con
B 950
M 400 X 7
Obtenemos: y = 3750
utilizando Excel se procede a calcular la nube de puntos
-
Apuntes de clase MANEJO DE EXCEL PEDRO SUAREZ SANCHEZ
luego se pide agregar lnea de tendencia
en este caso se escogi la opcin lineal, se pidi presentar en el greafico y calcular el R
cuadrado ( ndice de determinacin)
-
Apuntes de clase MANEJO DE EXCEL PEDRO SUAREZ SANCHEZ
el resultado es
-
Apuntes de clase MANEJO DE EXCEL PEDRO SUAREZ SANCHEZ
PENDIENTE: Devuelve la pendiente de una lnea de regresin lineal creada con los datos
de los argumentos conocido y conocido. La pendiente es la distancia vertical dividida por
la distancia horizontal entre dos puntos cualquiera de la recta, lo que corresponde a la
tasa de cambio a lo largo de la lnea de regresin
:
-
Apuntes de clase MANEJO DE EXCEL PEDRO SUAREZ SANCHEZ
INTERSECCION EJE
Calcula el punto en el que una lnea intersecar el eje y utilizando los valores X e Y
existentes. El punto de interseccin se basa en el mejor ajuste de la lnea de regresin
trazado con los valores X y los valores Y. Utilice la funcin INTERSECCION.EJE para
determinar el valor de la variable dependiente cuando la variable independiente es igual a
0 (cero). Por ejemplo, puede emplear la funcin INTERSECCION.EJE para predecir la
resistencia elctrica de un metal a 0 C si los puntos de datos se han tomado a
temperatura ambiente o superior.
La ecuacin que representa la interseccin de la lnea de regresin, a, es:
para el caso del ejercicio anterior tenemos:
PEARSON ( COEFICIENTE DE CORRELACION) Devuelve el coeficiente de correlacin producto o momento r de Pearson, r, un ndice adimensional acotado entre -1,0 y 1,0 que refleja el grado de dependencia lineal entre dos conjuntos de datos
-
Apuntes de clase MANEJO DE EXCEL PEDRO SUAREZ SANCHEZ
COEFICIENTE.R2 ( COEFICIENTE DE DETERMINACIN)
Devuelve el cuadrado del coeficiente de correlacin de momento del producto Pearson mediante los puntos de datos de conocido y conocido. Para obtener ms informacin, vea PEARSON. El valor R cuadrado puede interpretarse como la proporcin de la varianza de y que puede atribuirse a la varianza de x.
-
Apuntes de clase MANEJO DE EXCEL PEDRO SUAREZ SANCHEZ
DETERMINACION DE LOS COSTOS FIJOS TOTALES Y VARIABLES LOS VARIABLES
UNITARIOS
Ejemplo
X=HORAS MAQUINA Y =COSTOS TOTALES EN MILES DE PESOS
100 1450 200 1630 300 1830 400 1900 500 2500 600 3014
Calcular los costos fijos totales (m) y el costo unitario variable (b)
PROMEDIO X 350
PROMEDIO Y 2054
X menos promedio X
Y menos promedio Y
(X menos promedio X) por (y menos promedio y)
cuadrado de(x menos promedio x)
-250,00000 -604,00000 151000 62500
-150,00000 -424,00000 63600 22500
-50,00000 -224,00000 11200 2500
50,00000 -154,00000 -7700 2500
150,00000 446,00000 66900 22500
250,00000 960,00000 240000 62500
Sumas 525000 175000
Utilizando
Se obtiene
-
Apuntes de clase MANEJO DE EXCEL PEDRO SUAREZ SANCHEZ
m = 3 costos variables unitarios
b= 1004 costos fijos totales
cuando la tendencia es exponencial se utilizas la funcin crecimiento CRECIMIENTO: Calcula el pronstico de crecimiento exponencial a travs de los datos existentes. CRECIMIENTO devuelve los valores y de una serie de valores x especificados utilizando valores x y valores y existentes CASO: Se quieren pronosticar las ventas para el periodo 7, utilizando una funcin exponencial
-
Apuntes de clase MANEJO DE EXCEL PEDRO SUAREZ SANCHEZ
. Tambin puede utilizar la funcin de hoja de clculo CRECIMIENTO para ajustar una curva exponencial a los valores x y valores y existentes (se hace matricialmente) ver el siguiente ejemplo:
COMBINAT (COMBINACIONES) Devuelve el nmero de combinaciones para un nmero
determinado de elementos. Use COMBINAT para determinar el nmero total de grupos
posibles para un nmero determinado de elementos.
Sintaxis
COMBINAT(nmero; tamao)
-
Apuntes de clase MANEJO DE EXCEL PEDRO SUAREZ SANCHEZ
Nmero es el nmero de elementos.
Tamao es el nmero de elementos de cada combinacin.
El nmero de combinaciones es el siguiente, donde el argumento nmero = n y el
argumento tamao = k:
PERMUTACIONES: Devuelve el nmero de permutaciones para un nmero determinado de objetos que pueden seleccionarse a partir de un nmero de objetos determinado por el argumento nmero. Una permutacin es un conjunto o subconjunto de objetos o de eventos en el que el orden de los objetos es importante. Difiere en esto de las combinaciones en las que el orden de los elementos no es significativo. Utilice esta funcin para los clculos de probabilidad tipo sorteos
:
-
Apuntes de clase MANEJO DE EXCEL PEDRO SUAREZ SANCHEZ
-
Apuntes de clase MANEJO DE EXCEL PEDRO SUAREZ SANCHEZ
Ejercicio; graficar (Plot), calcular el nmero de tem, total, promedio (mean),
primer cuartil (first quartile),mediana (median),tercer cuartil (third quartile) ,
moda (mode), desviacin estndar (sample estndar deviation ) , varianza
(sample variance), rango intercuartil (interquartile range) coeficiente de
variacin (coefficient o variation) , coeficiente de asimetra (skewness), curtosis
(kurtosis), grafico circular con participacin porcentual, tasa de crecimiento
sucesivas (successive ratios)
Para los siguientes datos {45, 30, 30, 25,28, 30,44, 29
-
Apuntes de clase MANEJO DE EXCEL PEDRO SUAREZ SANCHEZ
-
Apuntes de clase MANEJO DE EXCEL PEDRO SUAREZ SANCHEZ
-
Apuntes de clase MANEJO DE EXCEL PEDRO SUAREZ SANCHEZ