Extraer elementos aleatoriamente sin repetición

Puede descargar el archivo extrae_aleatoriamente.xlsm

Disponemos de una columna con datos y queremos extraer en otra columna una serie de datos elegidos de forma aleatoria.

Pasos a seguir

1. En la celda amarilla (E4) escribimos cuantos datos queremos extraer. El número puede variar entre 1 y 20. No olvidar pulsar ENTER después de introducir el número.
2. Pulsamos sobre el botón que lanza la macro: donde pone Extraer en rojo.
3. Con esto ya tendremos una extracción. Pulsando el botón de la macro podremos extraer otra nueva muestra aleatoria.

Código

El código contiene dos partes.

• Para manejar la celda amarilla hemos creado un código que maneja un evento. Además la celda amarilla está tratada con Datos/Validación para que únicamente admita números enteros entre 1 y 20.

 Private Sub Worksheet_Change(ByVal Target As Range)  
 Worksheets("Hoja1").Activate  
 If Not Intersect(Target, Range("E4")) Is Nothing Then  
   Target.Interior.ColorIndex = 45  
   Range("E7:G26").ClearContents  
   For i = 1 To Target  
     Cells(i + 6, 5) = i  
   Next i  
 End If  
 End Sub  

• Para la macro que se lanza con el botón hemos creado un procedimiento en VBA que se basa en la idea siguiente. Metemos la columna con los datos de entrada en la matriz A(). Creamos la matriz B() con un listado de los números desde el 1 hasta el 20, ordenados. Nos metemos en un bucle For que recorre la matriz B() comenzando por el final, desde la posición 20 hasta la posición 2. Por cada ciclo del bucle va permutando el valor de esa posición, inicialmente B(20), luego B(19), y así hasta llegar a la última con la que se trabaja que es B(2), con alguno de los valores previos elegidos aleatoriamente. Por ejemplo, el valor de B(20) se permuta con B(7), luego B(19) se permutará con B(15), etc., hasta llegar a B(2) cuyo valor se permutará con B(1). Para hacer estas permutaciones necesitaremos la variable auxiliar aux.

Es una forma curiosa de barajar las cartas de una baraja, y nos ha permitido crear un algoritmo eficiente. Al final del bucle For obtendremos una matriz B() perfectamente aleatorizada. En el último bucle For lo que hacemos es mostrar en la columna G los valores extraidos simplemente consultando las posiciones correspondientes de la matriz A().

 Option Explicit  
 Option Base 1  
 Sub extrae()  
 Dim num_datos As Long  
 Dim num_extraidos As Long  
 Dim rango_origen As Range  
 Dim A()  
 Dim B() As Long          'contiene los números del 1 hasta num_datos, inicialmente ordenados  
 Dim i As Long, r As Long, aux  
 num_extraidos = [E4]  
 Set rango_origen = Range("C7:C26") '<-- El usuario debe cambiar este valor por el de su caso  
 num_datos = rango_origen.Count  
 ReDim B(num_datos)  
 For i = 1 To num_datos  
   B(i) = i            'asignamos a B() los números del 1 hasta num_datos  
 Next i  
 A = rango_origen  
 Randomize  
 'vamos a desordenar los valores de la matriz B()  
 For i = num_datos To 2 Step -1   'i varia disminuye desde n hasta 2  
   aux = B(i)           'la variable auxiliar captura el valor último, el i-ésimo  
   r = Int(RND() * i) + 1     'r es un aleatorio entero entre 1 e i  
   B(i) = B(r)          'el valor i-ésimo será el que tenía A(r,1) que es previo  
   B(r) = aux           'para finalizar la permuta, el valor A(r,1) toma el valor que teníamos guardado en la variable auxiliar  
 Next i  
 For i = 1 To num_extraidos  
   Cells(i + 6, 6) = B(i)  
   Cells(i + 6, 7) = A(B(i), 1)  
 Next i  
 Range("E4").Interior.ColorIndex = 36  
 End Sub  

Elegir elementos aleatorios de una lista

Descargar el archivo busca4aleatorios.xlsm

Dada una lista de 10 elementos deseamos elegir cuatro de ellos de forma aleatoria y sin repetición.

La lista puede estás formada por los elementos que sea. En este caso la lista está formada por los números del 1 al 10.

Esto se puede hacer programando en VBA una macro, pero deseamos hacerlo con la grabadora de macros para no tener que meternos a programar con código.

Hemos creado cinco macros con grabadora cuyas acciones son las siguientes.

1. Macro1. Borra el rango D5:F14
2. Macro2. Copia el rango C5:C14 que es donde se generan los aleatorios, en el rango D5:D14, y lo hace con pegado especial valores.
3. Macro3. Copia el rango D5:D14 en el rango E5:E14, y lo hace con pegado especial valores.
4. Macro4. Eliminamos duplicados del rango E5:E14
5. Macro5. Tomamos únicamente los cuatro primeros valores de la columna E. Justamente tomamos el rango E5:E8 y lo pegamos en el rango F5:F8 que es el resultado final que buscábamos.

Creamos una macro denominada Obten que llama a las otras cinco macros en el orden correcto.

Sub Obten()
Macro1
Macro2
Macro3
Macro4
Macro5
End Sub

Creamos un botón que lance la macro Obten.

Un posible problema

Este sistema tiene la ventaja de que no ha sido necesario programar una macro compleja con VBA y lo hemos hecho con la ayuda de la grabadora de macros. Existe un inconveniente que pudiera darse y es que si el número de valores de la lista que se repiten es muy alto en algún caso podríamos tener menos de 4 valores diferentes, por lo que se tendría que volver a generar el proceso ya que con tres valores, por ejemplo, no nos valdría.

Para detectar si este fenómeno raro se da con cierta frecuencia hemos creado una macro que al ser lanzada busca incansablemente hasta que encuentra menos de cuatro valores distintos. La macro es la siguiente.

Sub busca()
Dim n As Byte
Do
  Obten
  n = Application.WorksheetFunction.Count(Range("E5:E14"))
  If n < 4 Then
    End
  End If
Loop
End Sub

He tenido corriendo esta macro unos minutos y no se ha parado, lo cual indica que no es fácil encontrar un caso donde el número de repetidos sea tan alto que nuestro procedimientos resuelto con grabadora de macros no sea efectivo.

Por cierto, para parar una macro de forma manual se han de pulsar las teclas Contro+Pausa La tecla pausa habitualmente se encuentra en el teclado en la parte superior derecha. En algunos ordenadores portátiles se ha de pulsar alguna tecla de función que suele ir marcada con algún color. Esto para cualquier macro que esté funcionando y deseemos detener. Es un STOP manual.

Generar columnas de aleatorios

Descargue el fichero columnas_de_aleatorios.xlsm

Deseamos generar columnas con números aleatorios. Tantos números aleatorios como se indique.

En la zona amarilla (B2:K2) disponemos de 10 números  con valores que van entre 1 y 20. La idea es generar debajo de cada celda amarilla tantos números aleatorios, con la función =ALEATORIO(), como indique el número. Así por ejemplo, si en la celda amarilla figura un 4 debajo queremos que se generen 4 números aleatorios.

El la celda B3 se ha de escribir la siguiente fórmula.

=SI($A2<=B$1;ALEATORIO();"")

Esta fórmula escribe un aleatorio en la celda si el valor de su fila (celda azul) es menor o igual a valor indicado en la celda amarilla correspondiente. En caso contrario escribe "" que son dos dobles comillas seguidas, con nada dentro. Esto equivale a celda vacía.

Con esto generaríamos la tabla de la izquierda. Luego hemos creado una macro denominada "Copia Valores" con un botón para lanzarla que lo que hace es copiar y pegar valores en la tabla de la derecha con el objeto de que queden números (sin fórmulas) ya que previsiblemente esto es lo que deseamos.

Distribución Uniforme

Puede descargar el archivo: uniforme.xlsm

En estadística disponemos de la distribución Uniforme. Es una distribución de carácter continuo que asigna la misma probabilidad en cualquier parte del intervalo en el que está definida.

ALEATORIO

En Excel se puede conseguir con la función:

=ALEATORIO()

que nos proporciona un número aleatorio entre 0 y 1, sin llegar al valor 1. Esto se expresa diciendo que la función genera números aleatorios en el intervalo [0;1).

En realidad, lo que se genera es un número pseudoaleatorio ya que se obtiene con ciertos algoritmos internos, y no sacando por ejemplo, bolas de un bombo de la lotería, o tirando monedas al aire a cara o cruz, lo cual generaría valores realmente aleatorios.

Podemos escribir la función ALEATORIO() en Excel y al pulsar luego la tecla de función F9, de recálculo manual, observaremos que el valor aleatorio que se obtiene cambia. Es un número entre 0 y 1 con muchos decimales. En la siguiente imagen se muestran diez números aleatorios obtenidos con esta función.

Los valores aleatorios también cambian siempre que se recalculen los valores de la hoja, cosa que sucede cada vez que se introduce un nuevo valor o se edita uno existente.

Ejemplo

Escriba en una celda un número aleatorio con la función ALEATORIO() y en otra celda escriba la palabra Hola. Al pulsar Enter observe como cambia el valor aleatorio ya que se recalcula toda la hoja.

Aleatorios en un intervalo

Vamos a ver las transformación que podemos realizar partiendo de un aleatorio creado con la función ALEATORIO(), al multiplicar por un número y al sumar cierta cantidad. También veremos cómo podemos quitar la parte fraccionaria y quedarnos únicamente con la parte entera.

Factor de escala

Si a la función ALEATORIO() la multiplicamos por un número la estamos afectando de un factor de escala. Por ejemplo, al multiplicar por 200, el número aleatorio que se obtiene ya no se mueve en el intervalo [0;1) sino en el intervalo [0;200).

=ALEATORIO()*200

Esto supone amplificar 200 veces el resultado obtenido con el número aleatorio.

Traslación

Si al número aleatorio anterior se le suma una cierta cifra lo que estamos haciendo es un cambio de escala. Por ejemplo, después de haber multiplicado por 200, lo que haremos es sumar 5.000 con lo que el número obtenido ya no se moverá en el intervalo [0;200) sino en el intervalo [5000;5200).

=5000+ALEATORIO()*200

Será un número con decimales como el del siguiente ejemplo.

Parte entera

Si deseamos quitar los decimales, podemos utilizar la función =ENTERO. Es una función que no redondea, lo que hace es truncar la parte fraccionaria y quedarse únicamente con la parte entera.

=ENTERO(5000+ALEATORIO()*200)

En este ejemplo, lo que conseguimos es un número entero en el intervalo [5000;5199]. No se llega a alcanzar nunca el valor 5200, ya que nunca se llegaba a alcanzar el valor 1 con la función ALEATORIO, y la función ENTERO lo que hace es quitar la parte decimal.

Si queremos llegar hasta 5.200 hemos de sumar 1.

=ENTERO(5000+ALEATORIO()*200)+1

En este caso el aleatorio obtenido será un número entero que se mueve en el intervalo [5001;5200]

ALEATORIO.ENTRE

Existe una función en Excel que pertenece a las complementarias y que se denomina

=ALEATORIO.ENTRE(mínimo;máximo)

Esta función genera números aleatorios enteros entre un valor mínimo y un valor máximo que proporcionemos.

Para conseguir el mismo intervalo que el que hemos creado en el apartado anterior escribiríamos la siguiente expresión.

=ALEATORIO.ENTRE(5001;5200)

>

En VBA

En programación tambíen podemos crear números aleatorios. En realidad son número pseudoaleatorios ya que se generan con algoritmos que imitan a los verdaderos números aleatorios.

La función que genera los números aleatorios es

Rnd

Genera un número aleatorio entre 0 y 1, excluido el 1. Es similar a la función ALEATORIO.

Barajar

Los números aleatorios que genera VBA en realidad no son aleatorios y se pueden repetir según una cierta secuencia. Para evitar que se repitan lo que hacemos es utilizar el comando

RANDOMIZE

que equivale a barajar las cartas de una baraja. Es lo que se denomina cambiar la semilla de generación de los números aleatorios.

RANDOMIZE lo utilizaremos en nuestro programa, normalmente una sola vez, al principio. Por ejemplo, después de los DIM. En caso de usar bucles se debe procurar no introducirlo dentro, ya que sino estaría barajando continuamente a cada iteración del bucle.


Método 1

El siguiente programa genera 10 números aleatorios y los escribe en la columna A.

Aleatorios en un intervalo

Si deseamos generar aleatorios en un cierto intervalo entre un valor mínimo (min) y un cierto valor máximo (max), sin decimales, utilizaremos la siguiente expresión:

Int((max - min + 1) * Rnd + min)

Por ejemplo, podemos utilizar la 'ventana Inmediato' para generar números aleatorios enteros entre 80 y 100.

Método 2

Para generar números aleatorios entre 5.000 y 5.200 utilizaremos la siguiente expresión.

Int((5200 - 5000 + 1) * Rnd + 5000)

En el siguiente programa generamos 10.000 números aleatorios entre 5.000 y 5.200.

Método 3

Otro sistema que me gusta emplear para conseguir lo anterior es el siguiente.

Se multiplica por 201 ya que realmente entre 5.000 y 5.200, incluidos ambos hay 201 números.


Consulte el siguiente post para ver cómo se trabaja con matrices y cómo se ahorra tiempo en los cálculos.


Listado de valores con Macro

Cuando se trabajan con muchos valores es aconsejable utilizar matrices.

Método 4

También podemos llamar a una función de Excel desde el código de VBA. Las funciones a las que llamemos  han de estar en inglés, y el separador de argumentos es la coma, ya que todo va en inglés.

Para ver un listado de funciones en inglés y en español consulte el siguiente post:

Glosario: Traducción de funciones

La función ALEATORIO.ENTRE en inglés es RandBetween.

También se pude utilizar así:

Método 5

Si lo que deseamos es dejar la fórmula en las celdas de la hoja en lugar de depositar el valor podemos utilizar la siguiente macro.

Método 6

Si lo que deseamos es utilizar nuestro idioma local depositando la fórmula en las celdas la macro será la siguiente.

Así podremos utilizar las fórmulas en español.

Creación de una campana de Gauss con Excel

Descargar el fichero: campana.xlsm

Veamo cómo crear una campana de Gauss. Trataremos de ver gráficamente el Teorema Central del Límite. Partimos de una serie de números aleatorios que se distribuyen según una distribución uniforme entre 0 y 1. U[0,1]

Los aleatorios se obtienen con la función de Excel:

=ALEATORIO()

El teorema central del límite nos dice que necesitaríamos infinitos valores, pero lo haremos con únicamente 12 valores.

Calculamos la media de estos valores con la función:

=PROMEDIO(datos)

Esta media se encuentra en la celda C1, y cada vez que escribimos algo en una celda, o cada vez que pulsamos la tecla F9, se recalculan los valores, ya que se basan en números aleatorios.

Queremos que los valores de la celda C1 (el promedio) se copien secuencialmente en la columna E, y de forma automática. Para conseguir nuestro objetivo utilizaremos una macro.

La macro anota permite escribir los valores de los promedios en la columna E, poniendo tantos como necesitemos. En la imagen el bucle FOR...NEXT llega hasta 10.000 valores.


Histograma

Para crear el histograma de frecuencias definimos los intervalos. Elegimos 20 intervalos y para obtenerlos en la columna G hacemos una serie que comienza en 0 y finaliza en 1, con un intervalo de 0,05.

A su derecha dejamos preparada una zona donde mediante la función FRACUENCIA determinaremos cuantos datos, de entre las 10.000 medias generadas, se encuentran dentro de cada intervalo.

La función Frecuencia tiene la siguiente sintaxis:

=FRECUENCIA(datos;grupos)

• donde los datos son lo valores de la columna E, que es donde se encuentran las 10.000 anotaciones de los promedios generados
• donde grupos es el rango G1:G21 que es done se encuntran los intervalos que hemos definido

Es una función matricial que requiere tres pasos:

1. Seleccionar con el ratón (o con el teclado) la zona donde la función dejará sus resultados. En este caso abarca más de una celda, concretamente es la zona amarilla
2. Se escribe la función matricial propiamente dicha. En este caso, se escribe la función Frecuencia
3. No se valida pulsando INTRO. Se han de pulsar simultáneamente las tres teclas siguientes: CONTROL+MAYÚSCULAS+INTRO

Para saber más sobre funciones matriciales consulte el siguiente post:

Funciones matriciales en Excel

Si ponemos en horizontal los valores obtenidos con la función Frecuencia, y con un poco de imaginación se puede ver ya la campana de Gauss.

Realicemos el gráfico.

Media Móvil

Si pulsamos sobre el gráfico con el botón derecho del ratón, podemos elegir "Agregar línea de tendencia", y de todas ellas elegir la Media Móvil. Así obtendremos el siguiente gráfico.

Vídeo

Lotería Primitiva

Dispone de un archivo de Excel que puede descargar: loteria.xlsm

Veamos un caso de simulación del juego de la Lotería Primitiva. Se trata de simular un gran número de jugadas y comprobar cuantos aciertos de 3, 4, 5, y 6 se producen. La macro no pretende ayudar a ganar dinero con este juego de azar. Sus pretensiones son formativas, ya que aprenderemos a programar macros con matrices y funciones. A la vista de los pobres resultados alcanzados, posiblemente la consecuencia sea que usted no vuelva a jugar a la lotería primitiva.

Reglas

Disponemos de 49 números, entre 1 y 49. Se deben elegir 6 de ellos, sin repetición. La jugada ganadora esta compuesta por otros 6 números, igualmente elegidos entre los 49 disponibles. Se compara nuestra jugada con la ganadora. Reciben premio los jugadores que tienen una combinación de números que coincide con la ganadora en 3 o 4 o 5 o 6. El premio mayor se obtiene si tienes 6 coincidencias, y el menor si tienes 3 coincidencias. La cuantía del premio varia en función del número de acertantes existentes en cada categoría.

Inicialmente estas fueron las reglas. Posteriormente añadieron la existencia de un número complementario, que da lugar a un premio intermedio entre el de acertar 5 y 6. Se trata de acertar 5 más el número complementario. La existencia de esta nueva regla no la vamos a considerar en nuestra simulación para simplificar el caso.




Función Genera

Hemos creado una función que genera 6 números aleatorios sin repetición entre 1 y 49.


Observar que se incluye la instrucción

Randomize

que en Visual Basic permite inicializar la semilla de los números aleatorios. Equivale a barajar las cartas de una baraja para sacar nuevas cartas al azar.

Código:

Private Function Genera() As Byte()
Dim A(6) As Byte
Dim i As Long
Dim j As Byte
Dim U As Byte
Dim repe As Boolean
Randomize
A(1) = Int(49 * Rnd + 1)
For i = 2 To 6
Do
U = Int(49 * Rnd) + 1
repe = False
For j = 1 To i - 1
If U = A(j) Then repe = True: Exit For
Next j
Loop While repe
A(i) = U
Next i
Genera = A
End Function

Macro

Disponemos de una macro que es la que dirige el flujo del programa. Llama a la función Genera inicialmente para solicitarle la combinación ganadora, y posteriormente cada vez que se realiza una jugada.

Código:

Sub primitiva_loto()
Dim i As Byte
Dim j As Double
Dim k As Byte
Dim s As Byte 'acumula las acertadas
Dim n As Double
Dim B(6) As Long 'B(3), B(4), B(5), B(6)
'acumulan las jugadas ganadas de 3,4,5,6
Dim G() As Byte 'combinación ganadora
Dim Z() As Byte
G = Genera()
n = InputBox("¿Núm. jugadas?", , 1000000)
For j = 1 To n 'j es la jugada
Z = Genera()
s = 0
For i = 1 To 6
  For k = 1 To 6
      If G(i) = Z(k) Then s = s + 1: Exit For
  Next k
Next i
For i = 3 To 6
  If s = i Then B(i) = B(i) + 1
Next i
Next j
For i = 1 To 6 'Imprimimos la Ganadora
Cells(i + 4, "B") = G(i)
Next i
For i = 3 To 6 'Imprimimos los resultados
Cells(i + 2, "E") = B(i)
Next i
Range("E10") = n
End Sub

Se comparan todos los elementos de la jugada con la ganadora, y se determina el número de aciertos. Si los aciertos son 3, o 4 o 5 o 6, se llevan a una matriz B() que acumula el número de aciertos de cada categoría.

• B(3) acumula los acierto de 3
• B(4) acumula los acierto de 4
• B(5) acumula los acierto de 5
• B(6) acumula los acierto de 6

B(1) y B(2) no se utilizan para nada.

Continua

Una variante sobre la macro anterior es otra macro que trabaja de forma continua y que no para hasta que el usuario la detiene, por ejemplo pulsando la tecla Esc. Despues de pulsar Esc sale una pantalla avisando de la interrupción de la macro, y en ella debe pulsas sobre 'Finalizar'.

Esta macro cada 100.000 jugadas realizadas muestra en pantalla los resultados alcanzados. Esto nos permite ver la evolución de los acierto.

Código:

Option Explicit
Private Function Genera() As Byte()
Dim A(6) As Byte
Dim i As Long
Dim j As Byte
Dim U As Byte
Dim repe As Boolean
Randomize
A(1) = Int(49 * Rnd + 1)
For i = 2 To 6
Do
   U = Int(49 * Rnd) + 1
   repe = False
   For j = 1 To i - 1
       If U = A(j) Then repe = True: Exit For
   Next j
Loop While repe
A(i) = U
Next i
Genera = A
End Function
Sub primitiva_continua()
Dim i As Byte
Dim j As Double
Dim k As Byte
Dim s As Byte 'acumula las acertadas
Dim B(6) As Long 'B(3), B(4), B(5), B(6) acumulan las jugadas ganadas de 3,4,5,6
Dim G() As Byte 'combinación ganadora
Dim Z() As Byte
G = Genera()
For i = 1 To 6 'Imprimimos la Ganadora
Cells(i + 4, 2) = G(i)
Next i
Do
j = j + 1 'j es la jugada
Z = Genera()
s = 0
For i = 1 To 6
   For k = 1 To 6
       If G(i) = Z(k) Then s = s + 1: Exit For
   Next k
Next i
For i = 3 To 6
   If s = i Then B(i) = B(i) + 1
Next i
If Int(j / 100000) - (j / 100000) = 0 Then
   For i = 3 To 6 'Imprimimos los resultados
       Cells(i + 2, 5) = B(i)
   Next i
   Cells(10, 5) = j
End If
Loop
End Sub

Tras dejar funcionando mi portatil durante ocho horas y media, conseguí algo más de cuatro mil millones de jugadas (4.126.700.000) siendo los aciertos obtenidos los siguientes.

De 6 aciertos no se consiguió ningún acierto. Esto probablemente no sea muy representativo debido a que estamos manejando números pseudoaleatorios, que no son verdaderamente aleatorios. Es posible que se mejoraran los resultados obtenidos utilizando no una única jugada ganadora, sino una ganadora por cada apuesta realizada. Pero, independientemente de este caso, lo que se ve con claridad es que no es muy rentable, en términos medios, jugar a la lotería. Aunque siempre nos quedará el consuelo de pensar que ... "a alguien le toca".

Generación de números aleatorios únicos (sin repetición)

Descargar el fichero RNDunicos.xlsm

Generar números aleatorios en Excel es tarea sencilla. Con la función =ALEATORIO(), con la función =ALEATORIO.ENTRE, y en lenguaje de Visual Basic con la función RND, que es la abreviatura de random. Por el contrario, si lo que deseamos es generar una serie de números aleatorios sin repetición la tarea se complica un poco, ya que debemos ir comprobando por cada número nuevo, si se encuentra entre los anterioremente generados y en caso afirmativo desecharle y generar otro.

La siguiente macro nos permite realizar la tarea descrita.

Código:

Sub unicos1()
Dim i As Integer, j As Integer
Dim A() As Long
Dim esta As Boolean
Dim x As Long, y As Long, z As Long, num As Long
x = Application.InputBox(prompt:="Entre el número aleatorio inicial" _
        , Title:="Generador de Números Aleatorios", Default:=1, Type:=1)
y = Application.InputBox(prompt:="Entre el número aleatorio final" _
        , Title:="Generador de Números Aleatorios", Default:=1000, Type:=1)
z = Application.InputBox(prompt:="¿Cuantos números aleatorios desea generar?" _
        & " (<15000 _="" default:="100," exit="" if="" sub="" then="" title:="Generador de Números Aleatorios" type:="1)" z=""> 15000 Then z = 15000
If z > y - x + 1 Then
    MsgBox "!Ha especificado más números " _
    & "de los que son posibles en el rango!"
    Exit Sub
End If
ReDim A(z)
Randomize
A(1) = Int((y - x + 1) * Rnd + x)
For i = 2 To z
    Do
        num = Int((y - x + 1) * Rnd + x)
        esta = False
        For j = 1 To i - 1
           If num = A(j) Then esta = True: Exit For
        Next j
    Loop While esta
    A(i) = num
Next i
For i = 1 To z
    Cells(i, 2) = A(i)
Next i
End Sub

Existe otra variante de esta macro que utiliza el comando Find para buscar el nuevo número aleatorio generado entre los anteriores. Si le encuentra entre los ya generados en la columna B buscará un nuevo número aleatorio. Al comando Find es necesario añadirle la propiedad LookAt:=xlWhole para que localice exactamente el número buscado. Si esto no se hiciera así, sucede que busca por las cifras, y si esta buscando el número 1, piensa que se encuentra, por ejemplo, en la cifra 107, o en la 819, esto es, siempre que aparezca un 1.

Código:

Sub unicos2()
    Dim x As Long, y As Long, z As Long, num As Long
    Dim control As Boolean
    Dim i As Long
    Dim celda As Range
    x = Application.InputBox("Entre el número aleatorio inicial" _
        , "Generador de Números Aleatorios", 1, , , , , 1)
    y = Application.InputBox("Entre el número aleatorio final" _
        , "Generador de Números Aleatorios", 1000, , , , , 1)
    z = Application.InputBox("¿Cuantos números aleatorios desea generar?" _
        & " (<15000 100="" 1="" _="" aleatorios="" de="" enerador="" exit="" if="" meros="" n="" sub="" then="" z=""> 15000 Then z = 15000
    If z > y - x + 1 Then
        MsgBox "!Ha especificado más números " _
            & "de los que son posibles en el rango!"
        Exit Sub
    End If
    Randomize
    Cells(1, 2) = Int((y - x + 1) * Rnd + x)
    For i = 2 To z
        Do
            control = False
            Randomize
            num = Int((y - x + 1) * Rnd + x)
            Set celda = Range("b1", Range("b1").End(xlDown).Address). _
            Find(num, LookIn:=xlValues, LookAt:=xlWhole)
            If Not (celda Is Nothing) Then
                control = True
            End If
        Loop Until Not control
        Cells(i, 2) = num
    Next
End Sub

Simulación de Montecarlo: aplicación financiera

Descargar el fichero: simulabono.xls

En este fichero de Excel realizamos un caso de simulación de Montecarlo aplicado a Renta Fija. Más concretamente, lo que hacemos es trabajar con bonos en los que el cupón anual no es de cuantía cierta sino aleatoria. El cupón se ajusta a una distribución normal de media 500 y desviación 50. N(500;50).

=DISTR.NORM.INV(ALEATORIO();500;50)

Conocida la ETTI (Estructura Temporal de los Tipos de Interes) o Curva de Tipos podemos calcular el precio de un bono. Para el bono A (celda amarilla) el precio se calcula con la siguiente expresión:

{=-SUMA((Flujosa/(1+ETTI)^Tiempo))}

Los corchetes indican que se trata de una fórmula matricial que se introduce no pulsando INTRO, sino pulsando Control+Mayúsculas+INTRO.

En la celda F5 (color azul) indicamos en cuantos años madura el Bono B, utilizamondo la fórmula:

=ALEATORIO.ENTRE(8;9)

Esta fórmula genera números aleatorios enteros entre un mínimo y un máximo. En este caso hemos escrito entre 8 y 9. Se utiliza para indicar la duración variable en años del bono B.

Si la fórmula fuera =ALEATORIO.ENTRE(9;9) no supondría variación aleatoria, ya que el número de años de duración sería siempre 9.

Al pulsar la tecla F9 (recálculo manual) los valores aleatorios se recalculan. Esto supone que cambien el importe de los cupones de los bonos e incluso la duración del Bono B. Lo que implica que la TIR (Tasa Interna de Rentabilidad) que expresa la rentabilidad del bono sea una variable aleatoria.

Vamos a estudiar la TIR del Bono B. Al tratarse de una variable aleatoria la identificaremos estudiando su distribución de probabilidad. Por el Teorema Central del Límite sabemos que al trabajar con múltiples funciones de distribución, en el caso extremo (cuando n tiende a infinito, o en este caso cuando el número de iteraciones es suficientemente grande) casi siempre la distribución que se obtienen en una Normal o Campana de Gauss.

Para generar las iteraciones, recalculamos la TIR del Bono B un elevado número de veces, por ejemplo 10.000 y anotamos en al columna H las concrecciones de la TIR así obtenida. Con esos 10.000 valores de TIR efectuamos un análisis de frecuencias, creando un histograma de frecuencias tal y como se vió en un post anterior. El análisis del histograma nos permite ajustar la distribución a una Normal, cuya media y varianza podemos calcular.

En el caso de que la duración del Bono B sea aleatoria entre 8 y 9, obtenemos dos Campanas de Gauss.

Utilizamos una macro para ir anotando en la columna H las diferentes TIR que se obtienen en las iteraciones.

Sub montecarlo()
Dim n 'número de interaciones
Dim i
Application.ScreenUpdating = False
n = InputBox("Introduzca el número de iteraciones", _
"Entrada de datos", 10000)
For i = 1 To n
Cells(i + 5, 8) = Range("F19")
Next i
Application.ScreenUpdating = True
End Sub

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.