jueves, 14 de agosto de 2008

Simulación de una Distribución Normal

Puede descargar el archivo: normalBoxMuller.xlsm

En Excel disponemos de una función de distribución que genera directamente valores ajustados a una distribución normal, pero en esta ocasión vamos a generarlos sin recurrir a las funciones de Excel. La denominada transformada de Box-Müller permite generar una distribución Normal. Para ello se utilizan dos distribuciones Uniformes [0;1], que en Excel se obtienen con la función:
=ALEATORIO()


La función es la siguiente:

=+RAIZ((-2*LN(ALEATORIO())))*SENO(2*PI()*ALEATORIO())

Enlace a la Wikipedia.

Podríamos programar una función que nos de una Normal de media mu y desviación típica sigma.



Código:

Function xNORMAL(mu, sigma)
Dim NORMAL01
Const Pi As Double = 3.14159265358979
Randomize
NORMAL01 = Sqr((-2 * LN(Rnd))) * Sin(2 * Pi * Rnd)
xNORMAL = mu + sigma * NORMAL01
End Function

Function LN(x)
LN = Log(x) / Log(Exp(1))
End Function

Por ejemplo, la función =xNORMAL(100;2) nos proporcionará un número aleatorio que se ajusta a una distribución normal de media 100 y desviación típica 2.

17 comentarios:

  1. Interesante forma... pero hay una forma un poco mas simple:

    =DISTR.NORM.INV(ALEATORIO();media;desviacion)

    y lo arrastras ;)

    ResponderEliminar
  2. La forma que indicas es el método clásico.

    La novedad de la transformada de Box-Müller es que desde una macro, simplemente utilizando código VBA no es necesario recurrir a la hoja de cálculo, ni a sus funciones predefinidas para conseguir la Normal Inversa.

    ResponderEliminar
    Respuestas
    1. Excelente Profesor, muchas gracias. Yo lo necesitaba manual porque tengo un programa que no dispone de la Normal Inversa y ahora la puedo construir de forma manual en el programa no es para excel.

      Eliminar
  3. Muchas gracias por tu publicación! ya no tengo q usar el objeto excel para sacar la normal inversa. Creo q hay unas librerias para .NET que incluyen una función NormalInverse(), pero no las encontré por ningún sitio, seguramente no sean gratuitas..

    Un saludo.

    ResponderEliminar
  4. Adolfo Aparicio: Por favor me interesa mucho usar tu algoritmo. Requiero también por favor si me puedes facilitar tu mail para hacerte unas preguntitas sobre este método que estas usando. Es el metodo de la transformada inversa o de box muller? Necesito un poco mas de informacion para poder documentar el uso de este algoritmo. Te lo agradecería infinitamente
    !!!
    Mil gracias de antemanos
    Saludos desde Chile

    ResponderEliminar
  5. Hola Magdalena.

    La transformada de Box-Muller la tienes en la wikipedia:

    http://en.wikipedia.org/wiki/Box%E2%80%93Muller_transform

    Mi correo es: financieras@gmail.com

    Un saludo.

    ResponderEliminar
  6. Si quisiera hacer una NORMAL Truncada???

    ResponderEliminar
  7. Hola Jorge.
    Para hacer una normal truncada puedes utilizar un condicional IF para truncarla.
    Un saludo.

    ResponderEliminar
  8. Respuestas
    1. Hola David.
      mu es la media, es como se designa habitualmente la media en estadística, y sigma es la desviación típica.

      Eliminar
  9. para que excel esta diseñado el codigo?

    ResponderEliminar
  10. Hola.
    El código supongo que se realizó para la versión de Excel 2003. Ahora, lo he probado en Excel 2010, y el código continúa funcionando estupendamente.
    Es necesario programar la función LN mediante código ya que en VBA no existe, pero si existe LOG. Aunque ya sabemos que en Excel existen ambas, pero en VBA solo existe LOG que es el logaritmo en base decimal.
    Otra opción que hubiera evitado tener que programar LN hubiera sido llamar desde el código a la función LN de la siguiente forma:

    NORMAL01 = Sqr((-2 * Application.WorksheetFunction.LN(Rnd))) * Sin(2 * Pi * Rnd)

    Un saludo.

    ResponderEliminar
  11. Saludos, primeramente espero que este bien, solo quisiera saber si puedo hacerle llegar algunos correos electronicos para realizarle algunas preguntas mas concretas sobre mi caso, ya que se parece un poco a lo que expone en este tema.
    De antemano muchas gracias por su invaluable ayuda.

    ResponderEliminar
  12. Es posible crear una serie de datos aleatorios entre dos valores que se rijan a una distribución normal. Ejemplo. En una casilla introduces el número de datos "200" y automáticamente ( macro) excel genera 200 números aleatorios entre tu rango con una distribución normal.?.

    ResponderEliminar
  13. como generar, numeros aleatorios con una media y desviación standard definida ??

    ResponderEliminar
  14. Hola, estoy aprendiendo VBA, y no consigo calcular una función, la cual tiene una normal de otra función que creé con anterioridad.
    Muchas gracias

    ResponderEliminar
  15. no s epuede descargar para probarla?

    ResponderEliminar