Aleatorios sin repetición en Excel, sin macros.

Dos días atrás me llegó una consulta sobre la manera de conseguir ciertos números de manera aleatoria entre valores de un rango, pero sin repetir. Como es una pregunta que se me hace con cierta frecuencia, por fin me he decidido a publicar algunas soluciones al tema.
En esta primera ocasión veremos cómo hacerlo sin emplear macros.

...Quisiera hacerte esta consulta: deseo aplicar la función Aleatorio (no dispongo de la versión de excel Aleatorio.entre) por ejemplo para que me dé números enteros del 1 al 100. Sería: =REDONDEAR.MAS(ALEATORIO()*100;) Mi intención es dar con 30 números, por lo que aplico la fórmula en 30 celdas (salen repes)... Empero: tendrían que ser 30 números diferentes. Ese es mi dilema...

Para dar solución a esta cuestión deberemos recordar un post publicado en este blog hace tres años (ver), es esa entrada se explicaba el uso de la función JERARQUIA, combinada a la función CONTAR.SI para conseguir una ordenación de ciertos valores; una ordenación única, esto es, sin repeticiones de posición. Esta es la esencia de la explicación siguiente.

Lo primero que tenemos que hacer es dotar de 'aleatoriedad' a nuestro modelo, por lo que comenzamos formulando en el rango A2:A21 veinte fórmulas =ALEATORIO(), que recuerdo que devuelve un número entre 0 y 1 (para el ejemplo que plantea arrastraríamos desde A2 hasta A101):

Las posibilidades de obtener dos valores iguales en el rango deben ser bastante próximas a cero, pero lo tendremos en cuenta para nuestra formulación...
El siguiente paso es fórmular, sobre el rango anterior A2:A21, la fórmula que devolverá números aleatorios sin repetir entre 1 y 20 (para mi ejemplo), a partir de la celda C2:
=JERARQUIA(A2;$A$2:$A$21)+CONTAR.SI($A$2:A2;A2)-1
fórmula idéntica a la vista en ver.
Esta fórmula JERARQUIA junto al CONTAR.SI nos asegura que los valores devueltos nunca serán iguales, es decir, que el orden dado no repetiría posiciones.

Dependiendo de cuantos números aleatorios sin repetición necesitemos arrastraremos el rango C2:C21. En mi caso, para demostrar que ciertamente nuestra formulación devuelve todos los valores sin repetir, he generado 20 aleatorios. Si en lugar de 20 fueran 100 ó 1000 arrastraríamos los rangos de nuestras fórmulas hasta tal número de filas.
En resumen, respondiendo la cuestión planteada, para conseguir 30 números aleatorios que vayan del 1 al 100, arrastraremos nuestra formulación =ALEATORIO() en A2:A101, y la fórmula =JERARQUIA(A2;$A$2:$A$21)+CONTAR.SI($A$2:A2;A2)-1 en C2:C31.
Es decir, el intervalo de valores nos lo da el número de ALEATORIOS y los valores sin repetición la fórmula JERARQUIA + CONTAR.SI.

Para comprobar que es cierto que no se repiten, en el rango E2:E21 he añadido una fórmula de conteo CONTAR.SI que evalúa cuantas veces aparece cada valor en el rango C2:C21: