Cómo generar números aleatorios sin duplicados en Excel

Una hoja de cálculo de Excel en blanco — Imagen: PixieMe/Shutterstock

En los últimos meses, he escrito sobre varias de las nuevas funciones de matrices dinámicas de Excel. Son potentes y fáciles de implementar, lo que nos libera de la tediosa construcción de expresiones que antes era necesaria. En este artículo, combinaremos algunas de estas nuevas funciones de matriz dinámica para generar una matriz de valores aleatorios únicos. Es fácil generar valores aleatorios; es igual de fácil limitar los límites inferior y superior para un conjunto de valores aleatorios. Lo que no es tan fácil, como verá, es generar una matriz de valores aleatorios únicos. Afortunadamente, al usar funciones de matriz dinámica, solo tiene que pasar por algunos aros.

VER: 83 consejos de Excel que todo usuario debe dominar (TechRepublic)

Uso Microsoft 365 en un sistema Windows 10 de 64 bits. Las funciones de matriz dinámica son compatibles con 365, Excel para la Web, Excel 2021, Excel para iPad y iPhone, Excel para tabletas y teléfonos Android. No hay archivo de demostración; no necesitarás uno.

Acerca de las funciones aleatorias

Excel tiene tres funciones de valor aleatorio: RAND(), RANDBETWEEN() y RANDARRAY(). RAND() genera valores aleatorios entre 0 y 1, por lo tanto, valores decimales aleatorios. RANDBETWEEN() le permite especificar los valores inferior y superior. Por ejemplo, podrías usar
RANDBETWEEN(10,1000)
para generar números enteros aleatorios entre 10 y 1.000.

Finalmente, RANDARRAY() devuelve una matriz de valores aleatorios usando la siguiente sintaxis:
RANDARRAY([rows], [columns], [min], [max], [integer])
Tenga en cuenta que todos los argumentos son opcionales. Estos argumentos le permiten especificar el número de filas y columnas para llenar, los valores mínimo y máximo para devolver y si devolver números enteros o decimales. El argumento entero es falso por defecto, lo que devuelve valores decimales. Para devolver números enteros, debe especificar VERDADERO. Además, min y max son valores inclusivos. Por ejemplo, si estos dos argumentos son 1 y 5, respectivamente, la función devolverá 1, 2, 3, 4 y 5, no solo 2, 3 y 4.

Al considerar los argumentos, tenga en cuenta las siguientes reglas:

RANDARRAY() funciona como RAND(), devolviendo un valor entre 0 y 1 si omite todos los argumentos.
RANDARRAY() funciona como RAND(), devolviendo un valor entre 0 y 1 si omite filas y columnas.
RANDARRAY() devolverá 0 o 1 respectivamente, si omite min y max.
El argumento min debe ser menor que max.
RANDARRAY() incluirá valores decimales si no establece explícitamente entero en VERDADERO.
RANDARRAY() creará el rango de tamaño apropiado para completar los cálculos. Este rango se llama rango de derrame. Si no hay celdas adecuadas en este rango, RANDARRAY() devuelve un error de derrame. Para obtener más información sobre el rango de derrame de Excel, puede leer Cómo usar el rango de derrame en Excel
RANDARRAY() actualizará los resultados y cambiará el tamaño cuando los datos de origen cambien si los datos de origen están en un objeto Tabla.
Actualmente, si la fuente de datos está en otro libro de trabajo, ambos libros deben estar abiertos para que RANDARRAY() funcione correctamente.
Para generar una matriz aleatoria de valores no duplicados, utilizaremos tres funciones de matriz dinámica: RANDARRAY(), UNIQUE() y SEQUENCE().

Cómo generar valores aleatorios sin duplicados, más o menos

RANDARRAY() solo tiene el potencial de devolver valores duplicados, al igual que todas las funciones aleatorias. Si tiene tiempo, hay una manera simple de lograr valores aleatorios sin duplicados. Convierta los valores en el rango de derrame a valores estáticos (también reemplazando la función) y luego elimine cualquier duplicado usando la herramienta Eliminar duplicados en la pestaña Datos. Es fácil, pero no es una forma razonable de trabajar a menos que lo que esté haciendo sea un pase de una sola vez.

No puede obligar a RANDARRAY() a devolver solo valores únicos, pero puede combinarlo con UNIQUE() para obtener los resultados deseados. Por ejemplo, la siguiente función, que se muestra en Figura Adevolverá una sola columna de cinco (aparentemente) valores únicos entre 1 y 20:

=UNIQUE(RANDARRAY(5, 1, 1, 20,TRUE))

Sin embargo, pueden suceder dos cosas: 1.) Ve una lista de valores únicos aleatorios que es menor que lo que especificó; 2.) Ves un error de derrame (a la derecha en Figura A).

Cuando RANDARRAY() repite suficientes valores para que la expresión no pueda devolver cinco valores únicos del grupo de valores aleatorios de RANDARRAY(), devolverá lo que pueda, lo que podría ser uno o dos valores cortos. Las posibilidades de tener tantos valores repetidos en ese grupo de 20 (los valores mínimo y máximo de 1 y 20, respectivamente) que no puede devolver una lista de cinco son pequeñas, pero no imposibles. Presione F9 y observe cómo se actualiza la expresión; eventualmente verá una matriz que tiene menos de cinco dígitos.

El tiro por la derecha (Figura A) muestra la expresión en la barra de fórmulas donde calculé manualmente RANDARRAY() seleccionando solo esa función y presionando F9. Como puede ver, la matriz lo hace repite un valor, el valor 1. Por lo tanto, la expresión puede devolver solo cuatro valores únicos.

Figura A

Es posible que espere ver cinco valores únicos.

Figura B muestra un error de derrame, rango de derrame desconocido. Cambié el argumento de filas de 5 a 19, lo que obligó a que suceda con más frecuencia, pero si presiona F9 con la suficiente frecuencia, el argumento de filas de 5 eventualmente devolverá un error de derrame. Tiene que ver con la naturaleza volátil de estas funciones, a veces una calcula más rápido que la otra. A volátil La función vuelve a calcular cada vez que se realiza un cambio.

Figura B

Los errores de derrame son posibles con esta simple expresión.

Una forma de asegurarse de que la expresión no devuelva menos valores que los especificados es crear un gran grupo de valores aleatorios. Quizás la forma más fácil es cambiar el argumento de las filas de un valor específico a una expresión corta: norte^ 2. Por ejemplo,

=UNIQUE(RANDARRAY(5^2, 1, 1, 20,TRUE))

La expresión de fila, 5^2, devolverá 25 valores aleatorios, pero ahora la expresión devuelve hasta 25 valores (filas) y aún es susceptible a un error de rango de derrame ocasional. Sin embargo, es poco probable que devuelva menos de cinco filas.

Si puede vivir con todos estos comportamientos, podría detenerse aquí. Sin embargo, lo más probable es que desee algo más estable.

Cómo generar valores aleatorios sin duplicados

En la última sección, aprendiste mucho sobre RANDARRAY() y UNIQUE() y cómo funcionan juntos, aunque los resultados no son estables. Sus necesidades determinarán si necesita llevar este problema al siguiente nivel.

En este momento, tenemos una expresión que no siempre devuelve el número correcto de valores y tiene el potencial de devolver un error de derrame. Para evitar estos dos problemas, necesita una expresión más compleja en la forma

=INDEX(UNIQUE(RANDARRAY(rows^2, columns, min, max, TRUE)), SEQUENCE(rows))


Se ve horrible, pero no te preocupes.  Una vez que comprenda cómo funciona, tendrá sentido y no tendrá problemas para aplicarlo a su propio trabajo.
RANDARRAY() genera una matriz de valores aleatorios basados en min y max.  El argumento de las filas a la potencia de 2 genera una gran cantidad de valores aleatorios, mucho más grande que el valor de las filas en sí.  UNIQUE() elimina todos los valores duplicados y devuelve una matriz de valores únicos.  Llegamos hasta aquí en la última sección.
La diferencia esta vez es que UNIQUE() no devuelve la matriz a la hoja.  Una vez que UNIQUE() elimina los duplicados, INDEX() usa el número de filas expresado en SEQUENCE() para determinar cuántos valores aleatorios únicos devolver como una matriz a la hoja.
Figura C muestra esta expresión usando nuestros valores de argumento anteriores:
=INDEX(UNIQUE(RANDARRAY(5^2,1,1,10,TRUE)), SEQUENCE(5))
Primero, RANDARRAY() devuelve una matriz de una sola columna de 25 enteros aleatorios entre 1 y 10. UNIQUE() elimina todos los duplicados de esos resultados.  INDEX() luego devuelve los cinco valores únicos y aleatorios principales, según lo determinado por SEQUENCE(5).
Figura C
Necesitas una expresión más estable.
Tenga en cuenta que el mínimo y el máximo siguen siendo importantes.  Por ejemplo, si min y max son 1 y 5, respectivamente, no puede devolver una matriz de 10 valores; no hay tantos valores únicos entre 1 y 5. La expresión devolverá cinco valores únicos, pero las celdas restantes en el rango de derrame mostrará errores.  Eso es una simplificación excesiva, pero hace el punto.
Además, los arreglos grandes toman tiempo.  Considere un valor de filas de 2000^2.  ¡Internamente, Excel está calculando 4,000,000 de números aleatorios!  Probablemente no necesites una piscina tan grande.  En su lugar, multiplique el argumento de las filas por otro valor, como 10. Eso devuelve 20 000 valores aleatorios, lo que podría ser suficiente, dependiendo de los valores mínimo y máximo.
Tendrás que experimentar para determinar si una piscina es lo suficientemente grande.  No existe una panacea verdadera debido a la relación: la posibilidad de devolver un rango de derrame incompleto aumenta a medida que disminuye la cantidad de números aleatorios pasados a UNIQUE().
Es hora de la confesión.  Esta es una expresión creativa y poderosa que aprovecha las nuevas funciones de matrices dinámicas de Excel.  Me gustaría reclamar todo el crédito por diseñar esta expresión, pero ha estado en Internet por un tiempo.  Si pudiera dar crédito al genio creativo que se le ocurrió la primera vez, lo haría.
Para obtener más información sobre las funciones de matrices dinámicas, lea los siguientes artículos: