Formula generică
=INDEX(characters,RANDARRAY(n,1,1,count,TRUE))
rezumat
Pentru a genera o listă de șiruri de text aleatorii, puteți utiliza o formulă bazată pe INDEX, RANDARRAY și TEXTJOIN. În exemplul prezentat, formula din D5 este:
=TEXTJOIN("",1,INDEX(chars,RANDARRAY(6,1,1,26,TRUE)))
unde caractere este intervalul numit B5: B30 conținând literele AZ. Pe măsură ce formula este copiată în coloană, aceasta generează un nou șir de text cu 6 caractere pe fiecare linie.
Explicaţie
Noile formule matrice dinamice din Excel 365 facilitează rezolvarea anumitor probleme dificile cu formule.
În acest exemplu, scopul este de a genera o listă de coduri aleatorii cu 6 caractere. Aleatoritatea este gestionată de funcția RANDARRAY, o nouă funcție în Excel 365. RANDARRAY returnează 6 numere aleatorii la INDEX, care apoi preia 6 valori aleatorii din intervalele de caractere numite. Rezultatele din INDEX sunt apoi concatenate împreună cu funcția TEXTJOIN.
În exemplul prezentat, formula din D5 este:
=TEXTJOIN("",1,INDEX(chars,RANDARRAY(6,1,1,26,TRUE)))
Funcționând din interior spre exterior, funcția RANDARRAY este utilizată pentru a genera o matrice care conține șase numere aleatorii între 1-26:
RANDARRAY(6,1,1,26,TRUE) // return array like (14;5;21;7;25;3)
Rețineți că matricea returnată va varia în funcție de fiecare instanță a funcției RANDARRAY. De asemenea, deoarece RANDARRAY este o funcție volatilă, se va recalcula la fiecare modificare a foii de lucru.
Această matrice de numere aleatoare este returnată direct funcției INDEX ca argument rânduri:
INDEX(chars,(14;5;21;7;25;3))
Deoarece solicităm INDEX 6 rânduri, obținem înapoi 6 rezultate într-o matrice ca aceasta:
("N","E","U","G","Y","C")
Această matrice este returnată funcției TEXTJOIN ca argument text1:
=TEXTJOIN("",1,("N","E","U","G","Y","C")) // returns "NEUGYC"
TEXTJOIN este setat să utilizeze un șir gol ca delimitator și să ignore valorile goale. Cu această configurație, TEXJOIN pur și simplu concatenează toate valorile împreună și returnează un șir de text cu 6 caractere precum „NEUGYC”.
Numărați caractere programat
În loc să codificați dimensiunea caracterelor direct în funcția RANDARRAY, puteți utiliza funcția COUNTA pentru a număra elementele din matrice și a le returna la RANDARRAY:
RANDARRAY(6,1,1,COUNTA(chars),TRUE)
Aceasta presupune că caracterele nu conțin celule goale.
Generați caractere programat
Deoarece literele AZ au valori de cod numerice subiacente, este posibil să se genereze matricea de caractere utilizate pentru asamblarea șirurilor de text programatic, în loc să se utilizeze un interval. Acest lucru se poate face cu funcția CHAR și funcția SEQUENCE.
Pentru a genera o matrice cu toate literele majuscule AZ, care se mapează la ASCII 65-90:
=CHAR(SEQUENCE(26,1,65,1)) // returns ("A","B","C",… )
Pentru a genera litere mici az, care corespund ASCII 97-122:
=CHAR(SEQUENCE(26,1,97,1)) // returns ("a","b","c",… )
Acest cod poate fi introdus în formula originală pentru a înlocui „caractere” astfel:
=TEXTJOIN("",1,INDEX(CHAR(SEQUENCE(26,1,65,1)),RANDARRAY(6,1,1,26,TRUE)))
Alte personaje
Caracterele în intervalul numit Chars poate fi orice doriți. Dacă adăugați mai mult de 26 de caractere (sau mai puține), ajustați numărul 26 după caz sau utilizați COUNTA așa cum s-a explicat mai sus.
Fără 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))
În această versiune, RANDBETWEEN returnează o valoare între 65 și 90 (inclusiv) care corespunde valorii ASCII pentru literele AZ (majuscule). Funcția CHAR traduce valoarea numerică într-o literă. Ca mai sus, toate rezultatele sunt concatenate împreună într-un singur șir de text.
