Generisk formel
=INDEX(characters,RANDARRAY(n,1,1,count,TRUE))
Sammendrag
For å generere en liste over tilfeldige tekststrenger, kan du bruke en formel basert på INDEX, RANDARRAY og TEXTJOIN. I eksemplet vist er formelen i D5:
=TEXTJOIN("",1,INDEX(chars,RANDARRAY(6,1,1,26,TRUE)))
hvor tegn er det navngitte området B5: B30 som inneholder bokstavene AZ. Når formelen kopieres nedover i kolonnen, genererer den en ny tekststreng på 6 tegn på hver linje.
Forklaring
De nye dynamiske matriseformlene i Excel 365 gjør det mye enklere å løse visse vanskelige problemer med formler.
I dette eksemplet er målet å generere en liste over tilfeldige koder med 6 tegn. Tilfeldigheten håndteres av RANDARRAY-funksjonen, en ny funksjon i Excel 365. RANDARRAY returnerer 6 tilfeldige tall til INDEX, som deretter henter 6 tilfeldige verdier fra de nevnte områdetegnene. Resultatene fra INDEX blir deretter sammenkoblet med TEXTJOIN-funksjonen.
I eksemplet vist er formelen i D5:
=TEXTJOIN("",1,INDEX(chars,RANDARRAY(6,1,1,26,TRUE)))
RANDARRAY-funksjonen fungerer fra innsiden og ut og brukes til å generere en matrise som inneholder seks tilfeldige tall mellom 1-26:
RANDARRAY(6,1,1,26,TRUE) // return array like (14;5;21;7;25;3)
Merk at matrisen som returneres vil variere med hver forekomst av RANDARRAY-funksjonen. Også fordi RANDARRAY er en ustabil funksjon, vil den beregne på nytt med hvert regnearksendring.
Denne matrisen med tilfeldige tall returneres direkte til INDEX-funksjonen som radargumentet:
INDEX(chars,(14;5;21;7;25;3))
Fordi vi spør INDEX om 6 rader, får vi tilbake 6 resultater i en matrise som dette:
("N","E","U","G","Y","C")
Denne matrisen returneres til TEXTJOIN-funksjonen som text1-argumentet:
=TEXTJOIN("",1,("N","E","U","G","Y","C")) // returns "NEUGYC"
TEXTJOIN er satt til å bruke en tom streng som skilletegn og å ignorere tomme verdier. Med denne konfigurasjonen sammenkobler TEXJOIN ganske enkelt alle verdiene sammen og returnerer en tekststreng med 6 tegn som "NEUGYC".
Telle tegn programmatisk
I stedet for å hardkode størrelsen på tegn direkte til RANDARRAY-funksjonen, kan du bruke COUNTA-funksjonen til å telle elementene i matrisen og returnere den som teller til RANDARRAY:
RANDARRAY(6,1,1,COUNTA(chars),TRUE)
Dette antar at tegn ikke inneholder tomme celler.
Generer tegn programmatisk
Siden bokstavene AZ har underliggende numeriske kodeverdier, er det mulig å generere en rekke tegn som brukes til å sette sammen tekststrenger programmatisk, i stedet for å bruke et område. Dette kan gjøres med CHAR-funksjonen og SEQUENCE-funksjonen.
For å generere en matrise med alle store bokstaver AZ, som tilordnes til ASCII 65-90:
=CHAR(SEQUENCE(26,1,65,1)) // returns ("A","B","C",… )
For å generere små bokstaver az, som tilsvarer ASCII 97-122:
=CHAR(SEQUENCE(26,1,97,1)) // returns ("a","b","c",… )
Denne koden kan slippes inn i den opprinnelige formelen for å erstatte "tegn" slik:
=TEXTJOIN("",1,INDEX(CHAR(SEQUENCE(26,1,65,1)),RANDARRAY(6,1,1,26,TRUE)))
Andre tegn
Tegnene i navngitt område tegn kan være noe du liker. Hvis du legger til mer enn 26 tegn (eller færre), justerer du nummeret 26 etter behov, eller bruker COUNTA som forklart ovenfor.
Uten 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 versjonen returnerer RANDBETWEEN en verdi mellom 65 og 90 (inkludert) som tilsvarer ASCII-verdien for bokstavene AZ (store bokstaver). CHAR-funksjonen oversetter den numeriske verdien til en bokstav. Som ovenfor er alle resultatene sammenkoblet i en enkelt tekststreng.
