tales




martes, 18 de septiembre de 2012
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ómohacerlo 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 esmi 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 tenemosque 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 nuestraformulació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 ordendado 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, respondiendola 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, enel 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:




Publicado por Excelforo Ismael Romero 
TEMAS: Funciones
Valoracion: 


Enviar por correo electrónicoEscribe un blogCompartir con TwitterCompartir con Facebook
36 comentarios:
1.
Antonioseptiembre 18, 2012
FENOMENAL.
Responder
2.
Anónimonoviembre 05,2012
Estimado, siguiendo tu ejemplo, deseo volver a listar 20 números aleatorios sin repeterir en la columna"D", distinto al orden que tiene la columna "C" y asi repetir la misma acción en la columna "E" y "F", de manera que un número no se repita en la misma fila, obteniendo una tabla de 20 filas *4 columnas con números sin repetir del 1 al 20.
Como lo haría?
Responder
Respuestas
1.ExcelForonoviembre 05, 2012
Hola, 
si lo he entendido bien, necesitas copiar un =ALEATORIO() en un rango similar, siguiendo tu ejemplo en el rango A1:D20. 
Luego, para generar los aleatorios en otro rango similar de 20x4, por ejemplo F1:I20, empezando desde F1 copiarías:
=JERARQUIA(A1;$A$1:$D$20)+CONTAR.SI($A$1:A1;A1)-1
arrastrando por el rango...
Espero haberte comprendido bien.
Slds
Responder...