Excel Como Simulador - Introduccion Basica

Excel como simulador - Introducción básica

1

Excel como simulador: Introducción básica

Índice

1. Introducción

2. Generando números aleatorios con Excel

2.1 Uso de la función Aleatorio()

2.1.1 Aleatorio()

2.1.2 Aleatorio.Entre()

2.1.3 Números aleatorios con distribución no uniforme

2.2 Uso del complemento de Análisis de Datos

3. Análisis de datos

3.1 Histograma

3.2 Frecuencia

3.3 Medidas de tendencia central, variación y forma

3.3.1 Medidas de tendencia central, variación y forma usando

Estadística Descriptiva

3.3.2 Medidas de tendencia central, variación y forma usando

funciones de Excel

3.3.2.1 Funciones para el cálculo de la tendencia central

3.3.2.2 Funciones para el cálculo de la variación

3.3.2.3 Funciones para el cálculo de la forma

3.4 Medidas de asociación lineal

3.4.1 Medidas de asociación lineal usando Estadística Descriptiva

3.4.2 Medidas de asociación lineal usando funciones de Excel

3.4.2.1 Covarianza

3.4.2.2 Coeficiente de correlación


2

Excel como simulador: Introducción básica

1. INTRODUCCIÓN

Para poder trabajar con Excel como simulador es necesario conocer algunos conceptos

estadísticos y como los manipula Excel.

Las potencia de las hojas de calculo reside en su universalidad, en su facilidad de uso,

en su capacidad de recalcular valores y sobre todo en las posibilidades que ofrece con

respecto al análisis de escenarios (what – if ) las últimas versiones de Excel incorporan,

además un lenguaje de programación propio, el Visual Basic for Applications con el

cual es posible crear auténticas aplicaciones de simulación destinadas al usuario final.

Algunas funciones importante de conocer son:

Genéricas:

- Contar

- ContarA

- Normalización

- Permituciones

- Abs

- Coincidir

- Cociente

- Contar.Si

- Entero

- Fact

- Indice

- Jerarquia

- Nod

- Producto

- Redondea.par - Redondea.impar

- Redondear

- Redondear.mas

- Residuo

- Subtotales

- Suma

- Suma.cuadrados

- Signo

- Suma.si

- Sumaproducto

- Truncar

Números aleatorios

- Aleatorio ()

- Aleatorio.Entre(a;b)

Descriptiva de datos

- Coeficiente.asimetria

- Cuartil

- Curtosis

- Desvest

- Desvesta

- Desvestp

- Desvestpa

- Desvia2

- Desvprom

- Frecuencia

- Intervalo.confianza

- Jerarquia

- K.esimo.mayor

- K.esimo.menor

- Max

- Maxa

- Media.acotada

- Media.armo

- Media.geom

- Mediana

- Min

- Mina

- Moda

- Percentil

- Probabilidad

- Promedio

- PromedioA

- Rango.percentil

- Var

- Vara


3

- Varp

- VarpA

P.valores - prueba.chi.inv

- Prueba.chi

- prueba.f

- Prueba.fisher.inv

- prueba.t

- prueba.z

Variables aleatorias

- Binom.crit

- Dist.gamma.inv

- Distr.gamma

- Distr.beta.inv

- Distr.beta

- Distr.binom

- Distr.chi

- Distr.exp

- Dist.f

- Dist.hipergeom

- Distr.inv.f

- Dist.log.inv

- Dist.log.norm

- Distr.norm.estand.inv

- Distr.norm.estand

- Distr.norm.inv

- Distr.norm

- Distr.t.inv

- Distr.t

- Distr.weibull

- Negbinomdist

- Poisson

2. GENERANDO NUMEROS ALEATORIOS CON EXCEL

Utilizando la planilla de cálculo de Excel podemos generar números aleatorios de dos

maneras:

- Utilizando la función Aleatorio()

- Utilizando el complemento de Análisis de datos

2.1 Generando números aleatorios con la función Aleatorio()

2.1.1 Uso de la función Aleatorio()

Aleatorio()

Para poder desarrollar una simulación utilizando la computadora necesitamos valernos

de un generador números pseudo-aleatorio. En Excel podemos conseguimos este

número mediante el llamado a la función Aleatorio(), así presentada nos devolverá un

valor mayor o igual que 0 y menor que 1, distribuido uniformemente.

Sintaxis:

= Aleatorio()

Independencia de valores

Si realizamos el llamado a la función Aleatorio() en varias celdas, por ejemplo en B3,

C3, D3, E3 y F3 cada una de ellas devolverá números generados estadísticamente independientes unos de otros.


4

Volatilidad del valor

Cada vez que pulsamos la tecla F9 o realizamos un cambio en alguna de las entradas del

modelo, todas las celdas donde aparezca la función Aleatorio() serán recalculadas de

forma automática y nos mostrarán nuevos valores. En nuestro ejemplo se puede que

valores tomaron las celdas que tienen la función.

Casos particulares

Generar números aleatorios entre 0 y 100.

Como hemos indicado la función Aleatorio() devuelve un valor entre igual o mayor que

0 y menor que 1. Si necesitáramos generar números aleatorios entre 0 y 100 ¿Cómo

podríamos hacerlo?

Si llamamos a la función =Aleatorio()*100 obtenemos un número real producto de

multiplicar un valor igual o mayor que 0 y menor que 1 por 100. Escribiendo esta

fórmula en las celdas que venimos trabajando podemos observar:

Para convertir estos valores en números enteros podemos valernos de alguna de las

funciones específica de Excel a tal efecto, veamos algunas:

ENTERO()

La función Entero() redondea el número indicado como parámetro hasta el entero

inferior más próximo.

Aplicada en las celdas dará:


5

REDONDEAR()

La función Redondear(num;decimales) redondea un número indicado como primer

parámetro a otro con tanta cantidad de decimales como se haya especificado en el

segundo parámetro. Los dos parámetros son obligatorios de establecer. Si lo que

deseamos obtener, como en nuestro ejemplo, son números enteros, debemos indicar

como segundo parámetro el valor 0.


TRUNCAR()

La función Truncar() suprime la parte fraccionaria del número indicado como parámetro

y devuelve solamente la parte entera de dicho número. Si se utiliza con un segundo

parámetro opcional, la función Truncar(num,decimal) trunca los valores a partir del

decimal especificado como segundo parámetro.


Para analizar:

- ¿En qué casos los resultados de las funciones Entero(), Redondear() y Truncar()

coinciden y en cuales no?

- Con alguno de los ejemplos anteriores ¿podemos obtener el número 100?

- ¿Qué nos devuelve la siguiente expresión: =Aleatorio()*(b-a)+a ? Se

sugiere probarla para distintos valores de a y b.

2.1.2 Uso de la función Aleatorio.Entre(numInferior;numSuperior)

Aleatorio.Entre(numInferior;numSuperior)

Otra forma de obtener un número aleatorio entre dos valores indicados es utilizando la

función Aleatorio.Entre(numInferior;numSuperior) .

Según la versión de Excel que se utilice puede ser necesario instalar el complemento de

Herramientas para análisis para tenerla disponible. Para hacerlo elija Complementos en

le menú Herramientas (si no está visible utilice la opción más comandos) y active los

complementos que necesite.


6

Al igual que la función Aleatorio(), la función Aleatoria(nunInferior;numSuperior) es

volátil y uniforme.

Sintaxis:

= Aleatorio.Entre(numInferior;numSuperior)

Los dos argumentos son obligatorios de indicar.

numInferior es el menor número entero que la función puede devolver.

numSuperior es el mayor número entero que la función puede devolver

Observemos que con esta función podemos obtener como resultado el número indicado

como límite superior.

2.1.3 Generando números aleatorios con distribución no uniforme.

Como se indicó con anterioridad, los números generados a partir de aleatorio() y

aleatorio.entre() están distribuidos de manera uniforme. Para obtener números aleatorios

que respondan a otra distribución se puede usar estos como base y las funciones

inversas que provee Excel.

A continuación se muestra como hacerlo para las funciones más comunes.

Para discretas:

Binomial (ensayos;probExitos) = Inv.Binom(ensayos;probExitos ;Aleatorio())

Poisson(λ) = Inv.Binom(λ/0,001;0,001;Aleatorio()) Uniforme(a,b)= Aleatorio.Entre(a;b)


7

Geométrica(p)= Redondear.Menos(Ln(Aleatorio())/Ln(1-p);0) BinomNegativa(n,p)=Inv.Binomial(Inv.gamma(Aleatorio();n;(1-p)/p)/ε;ε;Aleatorio());

Para continuas:

Beta(α,β)= Inv.Beta.N(Aleatorio();α;β,[a],[b]) Los parámetros [a] y [b] son opcionales y responden a los lìmites inferior y superior del intervalo de x Exponencial(λ) = (1/ λ) * -log(Aleatorio()) Gamma(α,β)= Inv.Gamma(Aleatorio();α;β) LogNormal(Π,σ) = Distr.Log.Inv(Aleatorio(); μ; σ) Normal (Π,σ) = μ + σ * (raíz(-2*log(Aleatorio()))*seno(2*pi()*Aleatorio())) Pareto(α,β) = β*(Aleatorio()^(-1/α)) Uniforme(a,b)= α + (β- α)*Aleatorio()

2.2 Generando números aleatorios con el complemento de Análisis de Datos

El paquete de Excel proporciona como complemento una serie de herramientas

llamadas Análisis de Datos que resultan de mucha utilidad al realizar análisis

estadísticos, entre otras cosas.

Al utilizar estas herramientas se deberá proporcionar los datos y parámetros para cada

análisis; la herramienta utilizará las funciones de macros estadísticas o técnicas

correspondientes y luego mostrará los resultados en forma tabla y en algunos casos en

forma de gráficos.

Las Herramientas para análisis incluyen las herramientas que se describen a

continuación. Para tener acceso a estas herramientas, haga clic en Análisis de datos en

el grupo Análisis de la ficha Datos. Si el comando Análisis de datos no está disponible,

deberá cargar el programa de complemento Herramientas para análisis.


8

Una vez instalada, en la solapa Datos veremos:

Al acceder a Análisis de datos tenemos la opción de elegir Generación de números

aleatorios.

La pantalla de la Generación de números aleatorio nos presenta una serie de opciones

donde debemos indicar:


9

Número de variables: Se debe introducir el número de columnas de valores que desee

incluir en la tabla de resultados. En caso de no introducir ningún número, se rellenará

todas las columnas del rango de salida que se haya especificado.

Cantidad de números aleatorios: Se debe introducir el número de puntos de datos que se

desee ver. Cada punto de datos aparecerá en una fila de la tabla de resultados. En caso

de no introducir ningún valor, se rellenará todas las columnas del rango de salida que se

haya especificado.

Distribución: Se debe elegir el método de distribución que se desea utilizar para generar

los números aleatorios.

Parámetros: Se utiliza para introducir un valor o valores para caracterizar la distribución

seleccionada.

Iniciar con: Es el valor semilla a partir del cual se generarán los números aleatorios.

La herramienta de análisis Generación de números aleatorios rellena un rango con

números aleatorios independientes extraídos de una de varias distribuciones. Puede

utilizar esta herramienta para caracterizar a los sujetos de una población con una

distribución de probabilidades. Por ejemplo, puede utilizar una distribución normal para

caracterizar la población de estatura de las personas o utilizar una distribución de

Bernoulli con dos resultados posibles para caracterizar la población de resultados de un

juego de azar.

Opciones de salida: Indicamos el lugar donde queremos que se generen los números

aleatorios.

Por ejemplo, para generar 30 números aleatorios entre 0 y 100 con distribución

uniforme podemos hacerlo de la siguiente manera:

Distribución

Semilla


10

Observación: Una vez generados los números aleatorios, estos no son volátiles. Es

decir, no vuelven a generarse con una modificación en el modelo como sucede con la

función Aleatorio()

3 ANÁLISIS DE DATOS

3.1 Histograma

Desde el complemento de Análisis de Datos podemos obtener la distribución de

frecuencia de un conjunto de datos y dibujar un histograma y el diagrama de Pareto.

Se accede desde la solapa de Datos – Análisis de datos – Histograma

Allí debemos indicar:


11

- Los datos que se utilizarán como Entrada (El Rango de clases es opcional, allí se

introduce la referencia de celda para un rango que contenga un conjunto

opcional de valores límite que definan rango de clase. Estos valores deberán

estar en orden ascendente.)

- Lugar donde queremos obtener la salida

Y disponemos de tres casillas de verificación para obtener:

- Pareto o Histograma ordenado

- Porcentaje acumulado

- Crear gráfico

3.2 Frecuencia (datos,grupo)

La función Frecuencia(datos,grupo) calcula la frecuencia con que se repiten los valores

de un rango y devuelve una matriz vertical de números.

Sintaxis:

= Frecuencia(datos;grupo)

Los dos parámetros pedidos en la función son obligatorios.

- En datos debemos indicar un conjunto de valores cuya frecuencia se desea

contar.

- En grupos debemos indicar una matriz de intervalos dentro de los cuales se

desea agrupar los valores del parámetro datos.

Por ejemplo, si generamos números aleatorios comprendidos entre 0 y queremos

determinar la frecuencia con la que se generaron números de acuerdo al siguiente

intervalo:


12

a) Menores o iguales a 5

b) Entre 6 y 15

c) Entre 16 y 25

d) Iguales o mayores a 26

Para hacerlo seleccionamos:

- Un rango de celdas y generamos los números aleatorios, para el ejemplo se

utilizará la función Aleatorio.Entre(0,30) aplicada al rango de celdas b3..d17

- Un rango de celdas para indicar el intervalo, para el ejemplo se utilizará f3..f5

Ahora es necesario utilizar la función Frecuencia(datos,grupo) para responder lo

solicitado.

Para mayor legibilidad rotularemos las celdas contiguas.

Importante: Para escribir la fórmula de frecuencia, debe hacerse como fórmula de

matriz. Esto es, seleccionar el rango de celdas g8..g11 donde se escribirán los

resultados, presionar F2 para que el foco de inserción se ubique en g8, escribir la

fórmula como indica el ejemplo y presionar CTRL+MAYUS+ENTER. Caso contrario

la fórmula no se escribe como fórmula de matriz y sólo aparecerá un resultado en la

celda g8.


13

Ejercicio:

1) Generar 150 números aleatorios enteros comprendidos entre 0 y 20. Se desea

agrupar los datos para contar su frecuencia de dos en dos. Luego

a) Construir una tabla que refleje el intervalo, la frecuencia de valores dentro de

dicho intervalo, la frecuencia acumulada.

A modo se ejemplo se muestra:

b) Representar gráficamente los valores mediante un diagrama de barras y

polígonos de frecuencias acumulados y no acumulados.

A modo de ejemplo se muestra:

3.3 Medidas de tendencia central, variación y forma

Para hacer un análisis estadístico de las medidas de tendencia central, variación y forma

de una muestra o población en Excel podemos recurrir al apartado Estadística

Descriptiva del complemento de Análisis de Datos o bien utilizar y combinar algunas de

las funciones que nos provee a tal efecto Excel.


14

3.3.1 Medidas de tendencia central, variación y forma usando Estadística

Descriptiva

El complemento de análisis de datos, por medio de Estadística descriptiva, calcula los

datos estadísticos básicos de un conjunto de datos, para una o varias variables.

Se accede a él por medio de la solapa Datos -> Análisis de datos -> Estadística

descriptiva

Una vez allí debemos indicar elementos referidos a la entrada de datos que se analizarán

y cómo se mostrarán los resultados del análisis.

Activando la casilla Resumen de estadística Excel generará un campo en la tabla de

resultados por cada una de las siguientes variables estadísticas: Media, Error típico de

la media, Mediana, Moda, Desviación estándar, Varianza de la muestra, Curtosis,

Coeficiente de asimetría, Rango, Mínimo, Máximo, Suma, Cuenta. Además se podrá

activar las casillas correspondientes a Mayor, Menor y Nivel de confianza para obtener

estos valores.

3.3.2 Medidas de tendencia central, variación y forma usando funciones de Excel


15

3.3.2.1 Funciones para el cálculo de la tendencia central

Media

- Promedio: Devuelve la media aritmética de los argumentos.

Sintaxis:

= Promedio(número1, [número2], ...)

Número1 hace referencia al rango de celdas para el cual desea el promedio, [número2]

es opcional y hace referencia a celdas o rangos adicionales para los que desea el

promedio, hasta un máximo de 255.

- PromedioA: Devuelve la media incluidos textos y valores lógicos.

Sintaxis:

= Promedio(número1, [número2], ...)

- Media.Acotada: Devuelve la media de un conjunto de datos

Sintaxis:

= Media.Acotada (matriz; Porcentaje)

- Media.Armo: Devuelve la media armónica.

Sintaxis:

= Media.Armo (número1, [número2], …)

- Media.Geom: Devuelve la media geométrica.

Sintaxis:

= Media.Geom (número1, [número2], …)

Mediana

- Mediana: devuelve la mediana de los números dados.

Sintaxis:

= Mediana (número1, [número2], …)

Moda

- Moda.Uno: Devuelve el valor más frecuente en un conjunto de datos.

Sintaxis:


16

= Moda.Uno (número1, [número2], …)

3.3.2.2 Funciones para el cálculo de la variación

Rango medio

- Usar MAX y MIN

RM=Promedio(Max(datos) + Min(Datos))

Cuartiles

- Cuartil

Cuartil(matriz;cuartil)

- Percentil: Devuelve el percentil k-ésimo de los valores de un rango

Percentil(matriz;k)

- Rango.Percentil: devuelve el % de los valores que son menores que cifra dentro de

matriz

Rango.Percentil(matriz;x;cifra_significativa)

Rango intercuartílico

Se puede utilizar:

Percentil (Ri = Percentil(datos;0,75)-Percentil(datos;0,25))

O

Cuartil (Ri = Cuartil(datos;3)-Cuartil(datos;1))

Varianza y desviación típica

- Var(A): Calcula la (cuasi)varianza de una muestra

- Varp(A): Calcula la varianza de la población.

- Desvest(A): Calcula la (Cuasi) desviación estándar de una muestra.

- Desvestp(A): Calcula la desviación estándar de la población total.

Coeficiente de variación

- Usar Promedio() y Desvest()

3.3.2.3 Funciones para el cálculo de la forma

Simetría

- Coeficiente.Asimetria: Devuelve el sesgo de una distribución

Curtosis

- Curtosis: Devuelve el coeficiente de curtosis de un conjunto de datos.


17

3.4 Medidas de asociación lineal

Para hacer un análisis estadístico de medidas de asociación lineal de una muestra o

población en Excel podemos recurrir al apartado Estadística Descriptiva del

complemento de Análisis de Datos o bien utilizar y combinar algunas de las funciones

que nos provee a tal efecto Excel.

3.4.1 Medidas de asociación lineal usando Estadística Descriptiva

El complemento de análisis de datos, por medio de Covarianza, calcula la matriz de

varianzas covarianzas de un conjunto de variables

Se accede a él por medio de la solapa Datos -> Análisis de datos -> Covarianza

Una vez allí debemos indicar elementos referidos a la entrada de datos que se analizaran

y como se mostrarán los resultados del análisis.

3.4.2 Medidas de asociación lineal usando funciones de Excel

3.4.2.1 Covarianza

- Covar() Devuelve la covarianza, o promedio de los productos de las desviaciones para

cada pareja de puntos de datos.

Covar(matriz1;matriz2)

3.4.2.2 Coeficiente de correlación

- Coef.de.correl() Devuelve el coeficiente de correlación entre dos rangos de celdas

definidos por los argumentos matriz1 y matriz2. Use el coeficiente de correlación para

determinar la relación entre dos propiedades. Por ejemplo, la temperatura promedio de

una localidad y el uso de aire acondicionado.


18

Coef.de.correl(matriz1;matriz2)

Documents

Excel Como Simulador - Introduccion Basica