En este artículo, discutiremos algunas fórmulas diferentes para aleatorizar en Excel sin repetir números. Además, le mostraremos un generador aleatorio universal que puede producir una lista de números aleatorios, fechas y cadenas sin repeticiones.
Como probablemente sepa, Microsoft Excel tiene varias funciones para generar números aleatorios como RAND, RANDBETWEEN y RANDARRAY. Sin embargo, no hay garantía de que el resultado de cualquier función esté libre de duplicados.
Este tutorial explica algunas fórmulas para crear una lista de números aleatorios únicos. Tenga en cuenta que algunas fórmulas solo funcionan en la última versión de Excel 365 y 2021, mientras que otras se pueden usar en cualquier versión de Excel 2019, Excel 2016, Excel 2013 y anteriores.
Obtenga una lista de números aleatorios únicos con un paso predefinido
Solo funciona en Excel 365 y Excel 2021 que admiten matrices dinámicas.
Si tiene la última versión de Excel, la forma más fácil de obtener un lista de números aleatorios únicos es combinar 3 nuevas funciones de matriz dinámica: SORTBY, SEQUENCE y RANDARRAY:
ORDENAR(SECUENCIA(n), RANDARRAY(n))
Donde n es el número de valores aleatorios que desea obtener.
Por ejemplo, para crear una lista de 5 números aleatorios, use 5 para n:
=SORTBY(SEQUENCE(5), RANDARRAY(5))
Ingrese la fórmula en la celda superior, presione la tecla Intro y los resultados se derramarán automáticamente sobre el número especificado de celdas.
Como puede ver en la captura de pantalla a continuación, esta fórmula en realidad ordena números del 1 al 5 en orden aleatorio. Si necesita un generador de números aleatorios clásico sin repeticiones, consulte otros ejemplos a continuación.
En la fórmula anterior, solo define cuántas filas llenar. Todos los demás argumentos se dejan en sus valores predeterminados, lo que significa que la lista comenzará en 1 y se incrementará en 1. Si desea un primer número e incremento diferentes, establezca sus propios valores para el 3.º (inicio) y el 4.º ( step) argumentos de la función SEQUENCE.
Por ejemplo, para comenzar en 100 e incrementar en 10, use esta fórmula:
=SORTBY(SEQUENCE(5, , 100, 10), RANDARRAY(5))
Cómo funciona esta fórmula:
Trabajando de adentro hacia afuera, esto es lo que hace la fórmula:
- La función SECUENCIA crea una matriz de números secuenciales basados en el valor de inicio predeterminado o especificado y el tamaño de paso incremental. Esta secuencia va al argumento de matriz de SORTBY.
- La función RANDARRAY crea una matriz de números aleatorios del mismo tamaño que la secuencia (5 filas, 1 columna en nuestro caso). El valor mínimo y máximo realmente no importa, por lo que podemos dejarlos en los valores predeterminados. Esta matriz va al argumento by_array de SORTBY.
- La función SORTBY ordena los números secuenciales generados por SEQUENCE usando una matriz de números aleatorios producidos por RANDARRAY.
Tenga en cuenta que esta fórmula simple crea una lista de números aleatorios que no se repiten con un paso predefinido. Para eludir esta limitación, utilice una versión avanzada de la fórmula que se describe a continuación.
Genere una lista de números aleatorios sin duplicados
Solo funciona en Excel 365 y Excel 2021 que admiten matrices dinámicas.
Para generar números aleatorios en Excel sin duplicados, use una de las fórmulas genéricas a continuación.
enteros aleatorios:
ÍNDICE (ÚNICO (RANDARRAY (n ^ 2, 1, min, max, TRUE)), SECUENCIA (n))
decimales aleatorios:
ÍNDICE (ÚNICO (RANDARRAY (n ^ 2, 1, min, max, FALSE)), SECUENCIA (n))
Dónde:
- N es el número de valores a generar.
- Min es el valor mínimo.
- Max es el valor máximo.
Por ejemplo, para crear una lista de 5 enteros aleatorios del 1 al 100 sin repeticiones, usa esta fórmula:
=INDEX(UNIQUE(RANDARRAY(5^2, 1, 1, 100, TRUE)), SEQUENCE(5))
Para generar 5 aleatorios únicos numeros decimalesponga FALSO en el último argumento de RANDARRAY u omita este argumento:
=INDEX(UNIQUE(RANDARRAY(5^2, 1, 1, 100)), SEQUENCE(5))
Cómo funciona esta fórmula:
A primera vista, la fórmula puede parecer un poco engañosa, pero al mirarla más de cerca, su lógica es muy sencilla:
- La función RANDARRAY crea una matriz de números aleatorios en función de los valores mínimo y máximo que especifique. Para determinar cuántos valores generar, eleva el número deseado de únicos a la potencia de 2. Debido a que la matriz resultante puede tener nadie sabe cuántos duplicados, debe proporcionar una matriz de valores suficiente para que ÚNICA pueda elegir. En este ejemplo, solo necesitamos 5 números aleatorios únicos, pero le indicamos a RANDARRAY que produzca 25 (5^2).
- La función ÚNICA elimina todos los duplicados y «alimenta» una matriz sin duplicados a ÍNDICE.
- De la matriz pasada por ÚNICO, la función ÍNDICE extrae los primeros n valores especificados por SECUENCIA (5 números en nuestro caso). Debido a que los valores ya están en orden aleatorio, realmente no importa cuáles sobreviven.
Nota. En arreglos muy grandes, esta fórmula puede ser un poco lenta. Por ejemplo, para obtener una lista de 1.000 números únicos como resultado final, RANDARRAY tendría que generar una matriz de 1.000.000 de números aleatorios (1000^2) internamente. En tales situaciones, en lugar de elevar a la potencia, puede multiplicar n por, digamos, 10 o 20. Solo tenga en cuenta que la matriz más pequeña se pasa a la función ÚNICA (pequeña en relación con el número deseado de valores aleatorios únicos), mayor será la posibilidad de que no todas las celdas en el rango de derrame se llenen con los resultados.
Cree un rango de números aleatorios que no se repiten en Excel
Solo funciona en Excel 365 y Excel 2021 que admiten matrices dinámicas.
Para generar un rango de números aleatorios sin repeticiones, puede usar esta fórmula:
ÍNDICE (ÚNICO (RANDARRAY (n ^ 2, 1, min, max)), SECUENCIA (filas, columnas))
Dónde:
- n es el número de celdas a llenar. Para evitar cálculos manuales, puede proporcionarlo como (cant. de filas * cant. de columnas). Por ejemplo, para llenar 10 filas y 5 columnas, use 50^2 o (10*5)^2.
- Filas es el número de filas a llenar.
- Columnas es el número de columnas a llenar.
- Min es el valor más bajo.
- Max es el valor más alto.
Como puede notar, la fórmula es básicamente la misma que en el ejemplo anterior. La única diferencia es la función SEQUENCE, que en este caso define tanto el número de filas como de columnas.
Por ejemplo, para llenar un rango de 10 filas y 3 columnas con números aleatorios únicos del 1 al 100, use esta fórmula:
=INDEX(UNIQUE(RANDARRAY(30^2, 1, 1, 100)), SEQUENCE(10, 3))
Y producirá una matriz de decimales aleatorios sin repetir números:
Si necesita números enteros, establezca el último argumento de RANDARRAY en VERDADERO:
=INDEX(UNIQUE(RANDARRAY(30^2, 1, 1, 100, TRUE)), SEQUENCE(10,3))
Cómo generar números aleatorios únicos en Excel 2019, 2016 y anteriores
Dado que ninguna versión distinta de Excel 365 y 2021 admite matrices dinámicas, ninguna de las soluciones anteriores funciona en versiones anteriores de Excel. Sin embargo, esto no significa que no haya solución en absoluto, solo tendrá que realizar algunos pasos más:
- Crea una lista de números aleatorios. Según sus necesidades, utilice:
- La función RAND para generar decimales aleatorios entre 0 y 1, o
- La función RANDBETWEEN para producir enteros aleatorios en el rango que especifique.
Asegúrese de generar más valores de los que realmente necesita porque algunos estarán duplicados y los eliminará más tarde.
Para este ejemplo, estamos creando una lista de 10 números enteros aleatorios entre 1 y 20 usando la siguiente fórmula:
=RANDBETWEEN(1,20)Para ingresar la fórmula en varias celdas a la vez, seleccione todas las celdas (A2:A15 en nuestro ejemplo), escriba la fórmula en la barra de fórmulas y presione Ctrl + Enter. O puede ingresar la fórmula en la primera celda como de costumbre y luego arrastrarla hacia abajo a tantas celdas como sea necesario.
De todos modos, el resultado será algo como esto:
Como puede notar, hemos ingresado la fórmula en 14 celdas, aunque eventualmente solo necesitamos 10 números aleatorios únicos.
- Cambiar fórmulas a valores. Como RAND y RANDBETWEEN se recalculan con cada cambio en la hoja de trabajo, su lista de números aleatorios cambiará continuamente. Para evitar que esto suceda, utilice Pegado especial > Valores para convertir fórmulas en valores como se explica en Cómo evitar que los números aleatorios se vuelvan a calcular.
Para asegurarse de que lo ha hecho bien, seleccione cualquier número y observe la barra de fórmulas. Ahora debería mostrar un valor, no una fórmula:
- Eliminar duplicados. Para hacerlo, seleccione todos los números, vaya a la pestaña Datos > grupo Herramientas de datos y haga clic en Eliminar duplicados. En el cuadro de diálogo Eliminar duplicados que aparece, simplemente haga clic en Aceptar sin cambiar nada. Para conocer los pasos detallados, consulte Cómo eliminar duplicados en Excel.
¡Hecho! Todos los duplicados se han ido, y ahora puede eliminar los números en exceso.
Cómo evitar que los números aleatorios cambien
Todas las funciones de aleatorización en Excel, incluidas RAND, RANDBETWEEN y RANDARRAY, son volátiles, lo que significa que se vuelven a calcular cada vez que se cambia la hoja de cálculo. Como resultado, se producen nuevos valores aleatorios con cada cambio. Para evitar generar nuevos números automáticamente, use la función Pegado especial > Valores para reemplazar fórmulas con valores estáticos. Así es cómo:
- Seleccione todas las celdas con su fórmula aleatoria y presione Ctrl + C para copiarlas.
- Haga clic derecho en el rango seleccionado y haga clic en Pegado especial > Valores. Alternativamente, puede presionar Shift + F10 y luego V, que es el atajo para esta opción.
Para conocer los pasos detallados, consulte Cómo cambiar fórmulas a valores en Excel.
Generador de números aleatorios para Excel sin repeticiones
Los usuarios de nuestra Ultimate Suite realmente no necesitan ninguna de las soluciones anteriores porque ya tienen un generador aleatorio universal en su Excel. Esta herramienta puede producir fácilmente una lista de números enteros que no se repiten, números decimales, fechas y contraseñas únicas. Así es cómo:
- En la pestaña Herramientas de Ablebits, haga clic en Aleatorizar > Generador aleatorio.
- Seleccione el rango para llenar con números aleatorios.
- En el panel Generador aleatorio, haga lo siguiente:
- Elija el tipo de valor deseado: entero, número real, fecha, booleano, lista personalizada o cadena (¡ideal para generar contraseñas únicas y seguras!).
- Configure los valores Desde y Hasta.
- Selecciona el Valores únicos casilla de verificación
- Haz clic en Generar.
¡Eso es todo! El rango seleccionado se llena con números aleatorios que no se repiten a la vez:
Si tiene curiosidad por probar esta herramienta y explorar otras características fascinantes incluidas en nuestra Ultimate Suite, puede descargar una versión de prueba.
Así es como aleatorizar números en Excel sin duplicados. ¡Gracias por leer y espero verte en nuestro blog la próxima semana!
Cuaderno de ejercicios para descargar
(archivo .xlsx)