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


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.


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


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 :


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


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.


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). :


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)


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


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


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


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


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)


el resultado es


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

:


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


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.


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


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


. 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)


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

:


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

3. estadistica con excel y con wolfram alpha

Documents