Fórmula genérica
=INDEX(characters,RANDARRAY(n,1,1,count,TRUE))
Resumen
Para generar una lista de cadenas de texto aleatorias, puede utilizar una fórmula basada en INDEX, RANDARRAY y TEXTJOIN. En el ejemplo que se muestra, la fórmula en D5 es:
=TEXTJOIN("",1,INDEX(chars,RANDARRAY(6,1,1,26,TRUE)))
donde chars es el rango con nombre B5: B30 que contiene las letras AZ. A medida que la fórmula se copia en la columna, genera una nueva cadena de texto de 6 caracteres en cada línea.
Explicación
Las nuevas fórmulas de matriz dinámica en Excel 365 hacen que sea mucho más fácil resolver ciertos problemas complicados con fórmulas.
En este ejemplo, el objetivo es generar una lista de códigos aleatorios de 6 caracteres. La aleatoriedad es manejada por la función RANDARRAY, una nueva función en Excel 365. RANDARRAY devuelve 6 números aleatorios a INDEX, que luego recupera 6 valores aleatorios de los caracteres del rango con nombre. Los resultados de INDEX luego se concatenan junto con la función TEXTJOIN.
En el ejemplo que se muestra, la fórmula en D5 es:
=TEXTJOIN("",1,INDEX(chars,RANDARRAY(6,1,1,26,TRUE)))
Trabajando desde adentro hacia afuera, la función RANDARRAY se usa para generar una matriz que contiene seis números aleatorios entre 1-26:
RANDARRAY(6,1,1,26,TRUE) // return array like (14;5;21;7;25;3)
Tenga en cuenta que la matriz devuelta variará con cada instancia de la función RANDARRAY. Además, debido a que RANDARRAY es una función volátil, se volverá a calcular con cada cambio de hoja de trabajo.
Esta matriz de números aleatorios se devuelve directamente a la función INDICE como argumento de filas:
INDEX(chars,(14;5;21;7;25;3))
Debido a que estamos pidiendo a INDEX 6 filas, obtenemos 6 resultados en una matriz como esta:
("N","E","U","G","Y","C")
Esta matriz se devuelve a la función TEXTJOIN como argumento text1:
=TEXTJOIN("",1,("N","E","U","G","Y","C")) // returns "NEUGYC"
TEXTJOIN está configurado para usar una cadena vacía como delimitador y para ignorar los valores vacíos. Con esta configuración, TEXJOIN simplemente concatena todos los valores y devuelve una cadena de texto de 6 caracteres como "NEUGYC".
Contar caracteres mediante programación
En lugar de codificar el tamaño de los caracteres directamente en la función RANDARRAY, puede usar la función COUNTA para contar los elementos en la matriz y devolver ese recuento a RANDARRAY:
RANDARRAY(6,1,1,COUNTA(chars),TRUE)
Esto supone que los caracteres no contienen celdas vacías.
Genera caracteres mediante programación
Since the letters A-Z have underlying numeric code values, it is possible to generate the array of characters used to assemble text strings programmatically, instead of using a range. This can be done with the CHAR function and the SEQUENCE function.
To generate an array with all uppercase letters A-Z, which map to ASCII 65-90:
=CHAR(SEQUENCE(26,1,65,1)) // returns ("A","B","C",… )
To generate lowercase letters a-z, which correspond to ASCII 97-122:
=CHAR(SEQUENCE(26,1,97,1)) // returns ("a","b","c",… )
This code can be dropped into the original formula to replace "chars" like this:
=TEXTJOIN("",1,INDEX(CHAR(SEQUENCE(26,1,65,1)),RANDARRAY(6,1,1,26,TRUE)))
Other characters
The characters in the named range chars can be anything you like. If you add more than 26 characters (or fewer) adjust the number 26 as appropriate, or use COUNTA as explained above.
Without Excel 365
It is possible to generate random text strings without Excel 365, but the formula is more tedious and redundant. Since we don't have a good way to get 6 random numbers all at once, we use the RANDBETWEEN function to get one random value at a time:
=INDEX(chars,RANDBETWEEN(1,26))& INDEX(chars,RANDBETWEEN(1,26))& INDEX(chars,RANDBETWEEN(1,26))& INDEX(chars,RANDBETWEEN(1,26))& INDEX(chars,RANDBETWEEN(1,26))& INDEX(chars,RANDBETWEEN(1,26))
This formula uses the INDEX function to retrieve one random value at a time from the named range chars, and the 6 results are concatenated together into a single text string. Line breaks added for readability.
It is also possible to generate values A-Z directly with CHAR and RANDBETWEEN like this:
=CHAR(RANDBETWEEN(65,90))& CHAR(RANDBETWEEN(65,90))& CHAR(RANDBETWEEN(65,90))& CHAR(RANDBETWEEN(65,90))& CHAR(RANDBETWEEN(65,90))& CHAR(RANDBETWEEN(65,90))
En esta versión, RANDBETWEEN devuelve un valor entre 65 y 90 (inclusive) que corresponde al valor ASCII para las letras AZ (mayúsculas). La función CHAR traduce el valor numérico a una letra. Como se indicó anteriormente, todos los resultados se concatenan en una sola cadena de texto.
