Uso de la función ALEATORIO.ENTRE() en tramos discontinuos y/o no homogéneos.
Veremos dos casos, dependiendo de que la cantidad de números en cada tramo sea la misma o distinta.
Para el primer caso, el más sencillo, vamos a suponer que deseásemos obtener uno o más valores aleatorios de entre los rangos de números siguientes:
1-20
41-60
81-100
Resulta evidente que si intentásemos utilizar
=ALEATORIO.ENTRE(1;100)
habría un 40% de posibilidades de que la función nos devolviera un número no comprendido entre los tramos deseados.
Quizás la fórmula más simple para este supuesto sea:
=ELEGIR(ALEATORIO.ENTRE(1;3);ALEATORIO.ENTRE(1;20);ALEATORIO.ENTRE(41;60);ALEATORIO.ENTRE(81;100))
El funcionamiento de la fórmula es bastante sencillo: dado que se trata de 3 tramos iguales, de 20 números cada uno, el primer ALEATORIO.ENTRE() escoge al azar uno de ellos, y la función ELEGIR() procesa entonces el ALEATORIO.ENTRE que corresponde a dicho tramo y escoge al azar uno de los números que lo componen.
Dado que ELEGIR() admite hasta 254 elementos de entre los que escoger, esta técnica debería ser suficiente para casi cualquier caso concebible, siempre (me permito insistir) que los tramos de números sean idénticos.
Pero el problema se complica bastante si los tramos de números no son iguales. Supongamos que tenemos estos 4 tramos de números, situados en el rango A1:B4 de una hoja de cálculo:
1 10
17 31
37 47
52 63
y que necesitamos escoger al azar uno o varios números de entre los comprendidos en dichos tramos, de forma aleatoria.
Dos notas previas:
-
para mayor facilidad en las fórmulas que veremos más adelante, le aplicaremos el nombre rango_desde a A1:A4, el nombre rango_hasta a B1:B4 y el nombre rango_porcentajes a C1:C4.
-
Los tramos aparecen aquí ordenados para mayor claridad, pero no habría ningún problema si no lo estuviesen.
Si utilizásemos para este supuesto la técnica que vimos antes para el caso de tramos idénticos, se producirían sesgos que lógicamente tenderían a beneficiar a los números comprendidos en los tramos con menos números y a perjudicar a los tramos con más números (al final pondré una tabla para demostrar esto, aunque es algo que debería resultar bastante obvio). En este ejemplo, la cantidad de números de cada tramo es:
10
15
11
12
así que los tramos "favorecidos" serían el 1º y el 3º, mientras que el 2º y el 4º resultarían "perjudicados".
Dado que ALEATORIO() devuelve un número >0 y <1, la solución más sencilla quizás sea calcular el porcentaje que representa cada uno de los tramos sobre el total, para luego poder efectuar una búsqueda del valor devuelto por ALEATORIO(). En este ejemplo, el total de números entre los que se necesita escoger uno es 10+15+11+12=48
En C1 pondremos la fórmula:
=(SUMA($B$1:B1)-SUMA($A$1:A1)+FILA())/(SUMA(rango_hasta)-SUMA(rango_desde)+FILAS(rango_desde))
fórmula que copiaremos y pegaremos en el rango C2:C4
Los resultados devueltos por estas fórmulas deberían ser:
0,208333333
0,520833333
0,75
1
Estos resultados muestran el porcentaje que representa sobre el total de números (48) el acumulado de los números de cada uno de los tramos + el/los que le predede/n. Así, para la fila 1 el tramo es 1-10, y estos 10 números representan el 20,8333% de 48. Para la fila 2 el tramo es 17-31, y estos 15 números + los 10 del tramo anterior totalizan 25, que representan el 52,0833% de 48; y lo mismo para las dos filas restantes.
Ahora pondremos la fórmula
=ALEATORIO()
en, por ejemplo, D1. Si se necesitase más de una selección de entre los números deseados, lo único que habría que hacer es copiar esta fórmula y pegarla a partir de D2 hasta donde haga falta.
Ahora pondremos la fórmula matricial (hay que introducirla pulsando mayúsculas control entrada al mismo tiempo):
{=ALEATORIO.ENTRE(INDICE(rango_desde;COINCIDIR(FALSO;D1>rango_porcentajes;0));INDICE(rango_hasta;COINCIDIR(FALSO;D1>rango_porcentajes;0)))}
(Nota: las llaves no hay que teclearlas en la barra de fórmulas; son la forma convenida de indicar que una fórmula es matricial, y deberían aparecer automáticamente al introducir la fórmula como tal)
en, por ejemplo, E1, copiándola y pegándola hacia abajo hasta donde sea necesario si se puso más de un ALEATORIO() en la columna D
El funcionamiento de esta fórmula no es demasiado complicado, a pesar de tratarse de una fórmula matricial: la función ALEATORIO() que hay en D1 habrá generado un número decimal que, como ya vimos antes, será siempre >0 y <1. La función COINCIDIR() busca ese número en la columna con los porcentajes, y dado que están dispuestos en orden ascendente es posible utilizar como último argumento para COINCIDIR() un cero (o el valor FALSO), de forma que esta función nos devolverá el número de fila correspondiente al primero de los tramos cuyo porcentaje es > que el número devuelto por ALEATORIO()
Finalmente, la fórmula aplica la función ALEATORIO.ENTRE sobre los números que componen el tramo elegido por ALEATORIO() para escoger uno de ellos.
Es de destacar el hecho de que esta técnica permite "favorecer" a números o tramos de números de forma muy sencilla: basta con repetirlos cuantas veces se desee. Por ejemplo, si se pretendiese que los números 3 al 5 tuviesen el doble de posibilidades de salir que el resto, lo único que habría que hacer es añadir dichos números, como un tramo más, en las columnas A y B. Si se desease que sus posibilidades fuesen el triple que las de los demás números, bastaría con añadir una nueva fila, etc.
En este libro hay un ejemplo para cada uno de los dos casos. Para el caso de tramos no homogéneos (Hoja2 en el libro) he creado rangos dinámicos en las columnas A, B y C, para que se ajusten automáticamente a las filas con datos. Por lo tanto, es necesario que en dichas celdas A:C no se ponga nada por debajo de las columnas de los tramos y la que tiene las fórmulas que calculan los porcentajes.
Para terminar, he aquí una tabla que demuestra los sesgos que se producen al intentar utilizar la técnica basada en el uso de la función ELEGIR sobre tramos no homogéneos:
| UTILIZANDO FÓRMULA MATRICIAL | UTILIZANDO FUNCIÓN ELEGIR() | ||||||||||
| TRAMOS | RECUENTO | % REAL | % ESPERADO | DIFERENCIA | RECUENTO | % REAL | % ESPERADO | DIFERENCIA | |||
| 1 | 10 | 4.263 | 21,32% | 20,83% | -0,48% | 4.968 | 24,84% | 20,83% | 4,01% | ||
| 17 | 31 | 6.196 | 30,98% | 31,25% | 0,27% | 5.020 | 25,10% | 31,25% | -6,15% | ||
| 37 | 47 | 4.515 | 22,58% | 22,92% | 0,34% | 5.047 | 25,24% | 22,92% | 2,32% | ||
| 52 | 63 | 5.026 | 25,13% | 25,00% | -0,13% | 4.965 | 24,83% | 25,00% | -0,18% | ||
| Totales: | 20.000 | 100,00% | 100,00% | 0,00% | 20.000 | 100,00% | 100,00% | 0,00% | |||
Se han usado ambas técnicas para escoger 20.000 números aleatorios de entre los tramos que se muestran en las dos primeras columnas (que son los mismos que los utilizados como ejemplo para el desarrollo de la fórmula matricial).
En las columnas %ESPERADO se muestra cuántos números aleatorios deberían estar en cada tramo, teniendo en cuenta el porcentaje que representa el número de elementos de cada tramo sobre el total.
En las columnas DIFERENCIA puede comprobarse cómo en el caso de la fórmula matricial las diferencias entre los recuentos esperados y los reales no llegan en ningún caso al +/- 0,5% (que es perfectamente atribuíble a la aleatoriedad), mientras que al usar la técnica para tramos homogéneos llega a producirse una diferencia del -6,15% (que resulta casi imposible de atribuir al puro azar)
Y, lógicamente, estos sesgos se harían con casi toda probabilidad aún más pronunciados si las diferencias de numeros de cada tramo fuesen mayores.