Crear un listado de números aleatorios sin repetición

30 Abr Crear un listado de números aleatorios sin repetición

Funciones Utilizadas: ALEATORIO, JERARQUIA, CONTAR.SI, SI, ESERROR, BUSCARV

Suponiendo un rango de nombres en el rango A1:A20 para asignarle números aleatorios entre el 1 y el 20 sin repeticiones, vamos a realizar los siguientes pasos:

1. Copiamos la siguiente función para el rango B1:B20:

2. Copiamos la siguiente fórmula en la celda C1 y arrastramos hacia abajo hasta la celda C20:

Deberemos modificar el argumento “$B$20” por la última celda de nuestro rango de números. De esta manera tenemos en la columna C un listado de números aleatorios que probablemente no se repitan entre sí y que son aleatorios dentro del rango de números que tenga nuestro listado inicial. En otras palabras, obtenemos un listado jerarquizado no ordenado del 1 al 20 en función del resultado aleatorio de la columna B, donde el 1 es el resultado más cercano a 1 en la columna B y el 20 el resultado más cercano a 0 en la columna B.

Ahora bien, decimos probablemente porque puede existir la posibilidad de que dos números de la función ALEATORIO() sean iguales y por consecuencia la asignación aleatoria de la jerarquía quede condicionada a que sean dos valores consecuentes. En otras palabras, si por ejemplo dos números se repiten y el primero en aparecer de arriba hacia abajo en nuestro listado de la columna C es el número 3, al otro valor repetido se le asignará el número 4 y por ende la asignación no es 100% aleatoria, sino consecuente dentro de esos valores repetidos. Para resolver esto vamos a utilizar una fórmula que nos permite revisar si existen valores repetidos dentro del rango aleatorio de la columna B, y una fórmula final que revise a su vez si estos valores de revisión no devuelven repetidos, es decir buscamos una celda control de todo el rango que controlamos.

3. Copiamos la siguiente fórmula en la celda D1 y arrastramos hacia abajo hasta la celda D20:

Esta fórmula nos devuelve “OK” si no existen repetidos en nuestro rango aleatorio de la columna B, y devuelve “REPETIDOS” para los valores aleatorios que se repiten.

4. Copiamos la siguiente fórmula en la celda E1:

De esta forma podemos generar valores aleatorios en el rango B1:B20 hasta que nuestra celda E1 nos devuelva “OK” y no “REPETIDOS”, y así confirmar que tenemos un listado de números debidamente aleatorios dentro del rango C1:C20 sin repetición alguna.

Te recomiendo ver el siguiente link con más videos de Fórmulas ingeniosas de Excel AQUÍ.

Salvo aclaración, todas las fórmulas y macros de este sitio están configuradas para aplicarse sobre la celda A1. Algunas fórmulas se encuentran encerradas entre llaves {} debido a que son fórmulas matriciales. Estas llaves no deben introducirse tecleándolas, sino que se generan automáticamente al aceptar la fórmula pulsando Control+Shift+Enter al mismo tiempo. Las fórmulas de este sitio son compatibles con versiones de Microsoft Excel® 2010 o superiores.