Formule générique
=INDEX(characters,RANDARRAY(n,1,1,count,TRUE))
Sommaire
Pour générer une liste de chaînes de texte aléatoires, vous pouvez utiliser une formule basée sur INDEX, RANDARRAY et TEXTJOIN. Dans l'exemple illustré, la formule en D5 est:
=TEXTJOIN("",1,INDEX(chars,RANDARRAY(6,1,1,26,TRUE)))
où chars est la plage nommée B5: B30 contenant les lettres AZ. Lorsque la formule est copiée dans la colonne, elle génère une nouvelle chaîne de texte de 6 caractères sur chaque ligne.
Explication
Les nouvelles formules de tableau dynamique d'Excel 365 facilitent la résolution de certains problèmes délicats avec les formules.
Dans cet exemple, l'objectif est de générer une liste de codes aléatoires à 6 caractères. Le caractère aléatoire est géré par la fonction RANDARRAY, une nouvelle fonction dans Excel 365. RANDARRAY renvoie 6 nombres aléatoires à INDEX, qui récupère ensuite 6 valeurs aléatoires à partir des caractères de plage nommés. Les résultats de INDEX sont ensuite concaténés avec la fonction TEXTJOIN.
Dans l'exemple illustré, la formule en D5 est:
=TEXTJOIN("",1,INDEX(chars,RANDARRAY(6,1,1,26,TRUE)))
En travaillant de l'intérieur vers l'extérieur, la fonction RANDARRAY est utilisée pour générer un tableau contenant six nombres aléatoires entre 1 et 26:
RANDARRAY(6,1,1,26,TRUE) // return array like (14;5;21;7;25;3)
Notez que le tableau retourné variera avec chaque instance de la fonction RANDARRAY. En outre, étant donné que RANDARRAY est une fonction volatile, il recalculera à chaque changement de feuille de calcul.
Ce tableau de nombres aléatoires est renvoyé directement à la fonction INDEX comme argument de lignes:
INDEX(chars,(14;5;21;7;25;3))
Parce que nous demandons INDEX pour 6 lignes, nous obtenons 6 résultats dans un tableau comme celui-ci:
("N","E","U","G","Y","C")
Ce tableau est renvoyé à la fonction TEXTJOIN en tant qu'argument text1:
=TEXTJOIN("",1,("N","E","U","G","Y","C")) // returns "NEUGYC"
TEXTJOIN est configuré pour utiliser une chaîne vide comme délimiteur et pour ignorer les valeurs vides. Avec cette configuration, TEXJOIN concatène simplement toutes les valeurs ensemble et renvoie une chaîne de texte de 6 caractères comme "NEUGYC".
Compter les caractères par programmation
Au lieu de coder en dur la taille des caractères directement dans la fonction RANDARRAY, vous pouvez utiliser la fonction COUNTA pour compter les éléments du tableau et renvoyer ce nombre à RANDARRAY:
RANDARRAY(6,1,1,COUNTA(chars),TRUE)
Cela suppose que chars ne contient aucune cellule vide.
Générer des caractères par programmation
Étant donné que les lettres AZ ont des valeurs de code numérique sous-jacentes, il est possible de générer le tableau de caractères utilisé pour assembler des chaînes de texte par programme, au lieu d'utiliser une plage. Cela peut être fait avec la fonction CHAR et la fonction SEQUENCE.
Pour générer un tableau avec toutes les lettres majuscules AZ, qui correspondent à ASCII 65-90:
=CHAR(SEQUENCE(26,1,65,1)) // returns ("A","B","C",… )
Pour générer des lettres minuscules az, qui correspondent à ASCII 97-122:
=CHAR(SEQUENCE(26,1,97,1)) // returns ("a","b","c",… )
Ce code peut être déposé dans la formule d'origine pour remplacer "chars" comme ceci:
=TEXTJOIN("",1,INDEX(CHAR(SEQUENCE(26,1,65,1)),RANDARRAY(6,1,1,26,TRUE)))
Autres personnages
Les caractères dans la plage nommée chars peuvent être tout ce que vous voulez. Si vous ajoutez plus de 26 caractères (ou moins), ajustez le nombre 26 selon le cas, ou utilisez COUNTA comme expliqué ci-dessus.
Sans 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))
Dans cette version, RANDBETWEEN renvoie une valeur comprise entre 65 et 90 (inclus) qui correspond à la valeur ASCII pour les lettres AZ (majuscules). La fonction CHAR traduit la valeur numérique en lettre. Comme ci-dessus, tous les résultats sont concaténés dans une seule chaîne de texte.
