Generisk formel
=INDEX(characters,RANDARRAY(n,1,1,count,TRUE))
Sammanfattning
För att skapa en lista med slumpmässiga textsträngar kan du använda en formel baserad på INDEX, RANDARRAY och TEXTJOIN. I exemplet som visas är formeln i D5:
=TEXTJOIN("",1,INDEX(chars,RANDARRAY(6,1,1,26,TRUE)))
där tecken är det namngivna området B5: B30 som innehåller bokstäverna AZ. Eftersom formeln kopieras ner i kolumnen genererar den en ny 6-tecken textsträng på varje rad.
Förklaring
De nya dynamiska matrisformlerna i Excel 365 gör det mycket lättare att lösa vissa knepiga problem med formler.
I det här exemplet är målet att generera en lista med slumpmässiga 6-teckenkoder. Slumpmässigheten hanteras av RANDARRAY-funktionen, en ny funktion i Excel 365. RANDARRAY returnerar 6 slumpmässiga siffror till INDEX, som sedan hämtar 6 slumpmässiga värden från de angivna intervalltegnen. Resultaten från INDEX sammanfogas sedan tillsammans med funktionen TEXTJOIN.
I exemplet som visas är formeln i D5:
=TEXTJOIN("",1,INDEX(chars,RANDARRAY(6,1,1,26,TRUE)))
Arbetar inifrån och ut, RANDARRAY-funktionen används för att generera en matris som innehåller sex slumptal mellan 1-26:
RANDARRAY(6,1,1,26,TRUE) // return array like (14;5;21;7;25;3)
Observera att den returnerade matrisen varierar med varje instans av RANDARRAY-funktionen. Eftersom RANDARRAY är en flyktig funktion kommer den också att räkna om med varje ändring av kalkylbladet.
Denna matris av slumpmässiga nummer returneras direkt till INDEX-funktionen som radargumentet:
INDEX(chars,(14;5;21;7;25;3))
Eftersom vi frågar INDEX om 6 rader, får vi tillbaka 6 resultat i en sådan matris:
("N","E","U","G","Y","C")
Denna matris returneras till TEXTJOIN-funktionen som text1-argument:
=TEXTJOIN("",1,("N","E","U","G","Y","C")) // returns "NEUGYC"
TEXTJOIN är inställd på att använda en tom sträng som avgränsare och att ignorera tomma värden. Med denna konfiguration sammanfogar TEXJOIN helt enkelt alla värden tillsammans och returnerar en 6-tecken textsträng som "NEUGYC".
Räkna tecken programmatiskt
Istället för att hårdkoda storleken på tecken direkt i RANDARRAY-funktionen kan du använda COUNTA-funktionen för att räkna elementen i matrisen och återföra det antalet till RANDARRAY:
RANDARRAY(6,1,1,COUNTA(chars),TRUE)
Detta förutsätter att tecken inte innehåller några tomma celler.
Skapa tecken programmatiskt
Eftersom bokstäverna AZ har underliggande numeriska kodvärden är det möjligt att generera den uppsättning tecken som används för att sätta ihop textsträngar programmatiskt istället för att använda ett intervall. Detta kan göras med CHAR-funktionen och SEQUENCE-funktionen.
För att generera en matris med alla stora bokstäver AZ, som kartläggs till ASCII 65-90:
=CHAR(SEQUENCE(26,1,65,1)) // returns ("A","B","C",… )
För att generera små bokstäver az, som motsvarar ASCII 97-122:
=CHAR(SEQUENCE(26,1,97,1)) // returns ("a","b","c",… )
Den här koden kan släppas i den ursprungliga formeln för att ersätta "tecken" så här:
=TEXTJOIN("",1,INDEX(CHAR(SEQUENCE(26,1,65,1)),RANDARRAY(6,1,1,26,TRUE)))
Andra karaktärer
Tecknen i de angivna intervallkedjorna kan vara vad du vill. Om du lägger till mer än 26 tecken (eller färre), justerar du siffran 26 efter behov eller använder COUNTA som förklarats ovan.
Utan 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 den här versionen returnerar RANDBETWEEN ett värde mellan 65 och 90 (inklusive) som motsvarar ASCII-värdet för bokstäverna AZ (versaler). CHAR-funktionen översätter det numeriska värdet till en bokstav. Som ovan sammanfogas alla resultat i en enda textsträng.
