Cómo crear un conjunto normalmente distribuido de números aleatorios en Excel

Contenidos
  1. ¿Qué es una distribución normal?
  2. Función de densidad de probabilidad de distribución normal en Excel
  3. Representación gráfica de la función de densidad de probabilidad normal
  4. Cree un conjunto normalmente distribuido de números aleatorios en Excel
  5. Método de Box Muller para generar valores normales aleatorios

¿Qué es una distribución normal?

Desde un punto de vista puramente matemático, una distribución Normal (también conocida como distribución Gaussiana) es cualquier distribución con la siguiente función de densidad de probabilidad.

Descargar archivo de ejemplo

Donde μ (mu) es la media y σ (sigma) es la desviación estándar.

Función de densidad de probabilidad de distribución normal en Excel

También se conoce como curva de campana porque esta función de distribución de probabilidad parece una campana si la representamos gráficamente. Es una propiedad bien conocida de la distribución normal que el 99,7% del área bajo la curva de densidad de probabilidad normal se encuentra dentro de las 3 desviaciones estándar de la media. Entonces, para graficar esta función en Excel, necesitaremos una serie de valores de x que cubran (μ-3σ,μ+3σ) .

Esta es la función de densidad de probabilidad para la distribución normal en Excel.

=(1/SQRT(2*PI()*StdDev^2))*EXP(-1*(X-Mean)^2/(2*StdDev^2))

  • X – Este es cualquier número real.
  • Media : esta es la media de la variable aleatoria normalmente distribuida.
  • StdDev : esta es la desviación estándar de la variable aleatoria normalmente distribuida.

Representación gráfica de la función de densidad de probabilidad normal

Podemos graficar la función de densidad de probabilidad normal en Excel configurando una tabla con dos columnas de valores.

  • X : esta es una serie de números reales que aparecerán en nuestro eje X en el que evaluaremos nuestra función de densidad normal.
  • f(X) – Este es el resultado de nuestra función de densidad normal evaluada en X.

Si seleccionamos nuestra tabla, vaya a la pestaña Insertar y seleccione un Gráfico de líneas de la sección Gráficos . Podemos ver que el resultado es una bonita curva en forma de campana centrada alrededor del valor medio.

Cree un conjunto normalmente distribuido de números aleatorios en Excel

¿Es posible crear un conjunto de valores normalmente distribuidos en Excel? Sí, lo es, pero necesitaremos ver la función de distribución acumulativa F(x)=P(X=x) y su función inversa. Esta es la probabilidad de que un valor aleatorio de la distribución sea menor que un valor dado x.

La fórmula implica cálculo, pero afortunadamente la función DISTR.NORM.DIST de Excel hará este cálculo por nosotros.

=NORM.DIST(X,Mean,StdDev,TRUE)

  • X : este es cualquier número de la distribución.
  • Media : esta es la media de la distribución normal.
  • StdDev : esta es la desviación estándar de la distribución normal.
  • VERDADERO : esta es una entrada predefinida para la función NORM.DIST que le dice a Excel que calcule la distribución acumulativa.

También podemos graficar esto de manera similar a la función de densidad de probabilidad y crear un gráfico de líneas desde la sección Gráficos de la pestaña Insertar .

Tenga en cuenta que el eje Y de este gráfico va de 0 a 1 . Este es un valor de probabilidad y representa la probabilidad de que un valor aleatorio de nuestra distribución normal sea menor o igual a un valor dado.

Como ejemplo, F (0) = 50%, por lo que hay un 50% de posibilidades de que un valor aleatorio de nuestra distribución normal esté por debajo de 0.

A partir de este gráfico, también podemos comenzar con una probabilidad en el eje Y y obtener un valor de nuestra distribución normal en el eje X. Esto se llama el inverso de una función.

Si empezamos en 0,8 en el eje Y y seguimos horizontalmente hasta llegar a la gráfica, luego nos movemos verticalmente hacia abajo , llegaremos a 0,788 en el eje X. Esto significa que 0,788 es el inverso de 0,8.

Usando la función inversa es como obtendremos nuestro conjunto de valores aleatorios normalmente distribuidos. Usaremos la función RAND() para generar un valor aleatorio entre 0 y 1 en nuestro eje Y y luego obtendremos el inverso con la función NORM.INV que dará como resultado nuestro valor normal aleatorio en el eje X.

=NORM.INV(RAND(),Mean,StdDev)

  • Media : esta es la media de la distribución normal.
  • StdDev : esta es la desviación estándar de la distribución normal.

Si hacemos este cálculo 1,000 veces, podemos graficarlo con un gráfico de Histograma y comenzamos a ver emerger una curva en forma de campana.

Con 10.000 valores, la distribución se vuelve más clara.

De hecho, debido a la ley de los grandes números , cuantos más valores normales generados aleatoriamente creemos, más cerca aparecerá nuestra gráfica en forma de campana.

Método de Box Muller para generar valores normales aleatorios

El método de Box-Muller se basa en el teorema de que si U1 y U2 son variables aleatorias independientes distribuidas uniformemente en el intervalo (0, 1), entonces Z1 y Z2 serán variables aleatorias independientes con una distribución normal estándar (media = 0 y desviación estándar = 1).

Fácilmente podremos crear estas fórmulas en Excel con U1=RAND() y U2=RAND() . Sin embargo, para nuestros propósitos, solo necesitaremos calcular Z1 .

Dado que Z1 tendrá una media de 0 y una desviación estándar de 1 , podemos transformar Z1 en una nueva variable aleatoria X=Z1*σ+μ para obtener una distribución normal con media μ y desviación estándar σ.

=SQRT(-2*LN(RAND()))*COS(2*PI()*RAND())*StdDev+Mean

  • Media : esta es la media de la distribución normal.
  • StdDev : esta es la desviación estándar de la distribución normal.

También podemos graficar esto con un histograma para una gran cantidad de cálculos y ver una bonita curva en forma de campana bien definida.

Deja una respuesta

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *

Subir

Utilizamos cookies para asegurar que damos la mejor experiencia al usuario en nuestra web. Si sigues utilizando este sitio asumiremos que estás de acuerdo.