Fórmula genérica
=MATCH(RAND(),cumulative_probability)
Resumen
Para generar un número aleatorio, ponderado con una probabilidad determinada, puede utilizar una tabla auxiliar junto con una fórmula basada en las funciones RAND y MATCH.
En el ejemplo que se muestra, la fórmula en F5 es:
=MATCH(RAND(),D$5:D$10)
Explicación
Esta fórmula se basa en la tabla auxiliar visible en el rango B4: D10. La columna B contiene los seis números que queremos como resultado final. La columna C contiene el peso de probabilidad asignado a cada número, ingresado como porcentaje. La columna D contiene la probabilidad acumulada, creada con esta fórmula en D5, copiada:
=SUM(D4,C4)
Observe que estamos desplazando intencionalmente la probabilidad acumulada una fila hacia abajo, de modo que el valor en D5 sea cero. Esto es para asegurarse de que MATCH pueda encontrar una posición para todos los valores hasta cero, como se explica a continuación.
Para generar un valor aleatorio, utilizando la probabilidad ponderada en la tabla auxiliar, F5 contiene esta fórmula, copiada:
=MATCH(RAND(),D$5:D$10)
Dentro de MATCH, el valor de búsqueda lo proporciona la función RAND. RAND genera un valor aleatorio entre cero y 1. La matriz de búsqueda es el rango D5: D10, bloqueada para que no cambie cuando la fórmula se copia en la columna.
Se omite el tercer argumento de MATCH, tipo de coincidencia. Cuando se omite el tipo de coincidencia, COINCIDIR devolverá la posición del valor más grande menor o igual que el valor de búsqueda *. En términos prácticos, esto significa que la función COINCIDIR viaja a lo largo de los valores en D5: D10 hasta que se encuentra un valor mayor, luego "retrocede" a la posición anterior. Cuando COINCIDIR encuentra un valor mayor que el último valor más grande en D5: D10 (.7 en el ejemplo), devuelve la última posición (6 en el ejemplo). Como se mencionó anteriormente, el primer valor en D5: D10 es deliberadamente cero para garantizar que los valores por debajo de .1 sean "capturados" por la tabla de búsqueda y devuelvan una posición de 1.
* Los valores en el rango de búsqueda deben ordenarse en orden ascendente.
Valor de texto ponderado aleatorio
Para devolver un valor de texto ponderado aleatorio (es decir, un valor no numérico), puede ingresar valores de texto en el rango B5: B10, luego agregar INDICE para devolver un valor en ese rango, según la posición devuelta por MATCH:
=INDEX($B$5:$B$10,MATCH(RAND(),D$5:D$10))
Notas
- Me encontré con este enfoque en una publicación del foro en mrexcel.com
- RAND es una función volátil y se volverá a calcular con cada cambio de hoja de trabajo
- Una vez que tenga valores aleatorios, use pegar valores especiales> para reemplazar la fórmula si es necesario
