Generisk formel
=INDEX(characters,RANDARRAY(n,1,1,count,TRUE))
Resumé
For at generere en liste over tilfældige tekststrenge kan du bruge en formel baseret på INDEX, RANDARRAY og TEXTJOIN. I det viste eksempel er formlen i D5:
=TEXTJOIN("",1,INDEX(chars,RANDARRAY(6,1,1,26,TRUE)))
hvor tegn er det navngivne område B5: B30, der indeholder bogstaverne AZ. Da formlen kopieres ned i kolonnen, genererer den en ny tekststreng på 6 tegn på hver linje.
Forklaring
De nye dynamiske matrixformler i Excel 365 gør det meget nemmere at løse visse vanskelige problemer med formler.
I dette eksempel er målet at generere en liste over tilfældige koder med 6 tegn. Tilfældigheden håndteres af RANDARRAY-funktionen, en ny funktion i Excel 365. RANDARRAY returnerer 6 tilfældige tal til INDEX, som derefter henter 6 tilfældige værdier fra de navngivne rækkekarakterer. Resultaterne fra INDEX sammenkædes derefter sammen med TEXTJOIN-funktionen.
I det viste eksempel er formlen i D5:
=TEXTJOIN("",1,INDEX(chars,RANDARRAY(6,1,1,26,TRUE)))
Arbejder indefra og ud, RANDARRAY-funktionen bruges til at generere en matrix, der indeholder seks tilfældige tal mellem 1-26:
RANDARRAY(6,1,1,26,TRUE) // return array like (14;5;21;7;25;3)
Bemærk, at det returnerede array varierer med hver forekomst af RANDARRAY-funktionen. Da RANDARRAY også er en ustabil funktion, genberegner den også med hver ændring af regnearket.
Denne matrix med tilfældige tal returneres direkte til INDEX-funktionen som rækkeargumentet:
INDEX(chars,(14;5;21;7;25;3))
Da vi beder INDEX om 6 rækker, får vi 6 resultater tilbage i en matrix som denne:
("N","E","U","G","Y","C")
Denne matrix returneres til TEXTJOIN-funktionen som text1-argumentet:
=TEXTJOIN("",1,("N","E","U","G","Y","C")) // returns "NEUGYC"
TEXTJOIN er indstillet til at bruge en tom streng som afgrænser og ignorere tomme værdier. Med denne konfiguration sammenkæder TEXJOIN simpelthen alle værdier sammen og returnerer en tekststreng på 6 tegn som "NEUGYC".
Tæl tegn programmatisk
I stedet for hardcoding af størrelsen på tegn direkte i RANDARRAY-funktionen kan du bruge funktionen COUNTA til at tælle elementerne i arrayet og returnere det antal til RANDARRAY:
RANDARRAY(6,1,1,COUNTA(chars),TRUE)
Dette forudsætter, at tegn ikke indeholder tomme celler.
Generer tegn programmatisk
Da bogstaverne AZ har underliggende numeriske kodeværdier, er det muligt at generere den række af tegn, der bruges til at samle tekststrenge programmatisk, i stedet for at bruge et interval. Dette kan gøres med CHAR-funktionen og SEQUENCE-funktionen.
Sådan genereres en matrix med alle store bogstaver AZ, som kortlægges til ASCII 65-90:
=CHAR(SEQUENCE(26,1,65,1)) // returns ("A","B","C",… )
Sådan genereres små bogstaver az, der svarer til ASCII 97-122:
=CHAR(SEQUENCE(26,1,97,1)) // returns ("a","b","c",… )
Denne kode kan slippes ned i den originale formel for at erstatte "tegn" som denne:
=TEXTJOIN("",1,INDEX(CHAR(SEQUENCE(26,1,65,1)),RANDARRAY(6,1,1,26,TRUE)))
Andre tegn
Tegnene i de navngivne rækkekarakterer kan være hvad du vil. Hvis du tilføjer mere end 26 tegn (eller færre), skal du justere nummeret 26 efter behov eller bruge COUNTA som forklaret ovenfor.
Uden 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))
I denne version returnerer RANDBETWEEN en værdi mellem 65 og 90 (inklusive), der svarer til ASCII-værdien for bogstaverne AZ (store bogstaver). CHAR-funktionen oversætter den numeriske værdi til et bogstav. Som ovenfor er alle resultater sammenkædet i en enkelt tekststreng.
