Generieke formule
=INDEX(characters,RANDARRAY(n,1,1,count,TRUE))
Samenvatting
Om een lijst met willekeurige tekstreeksen te genereren, kunt u een formule gebruiken die is gebaseerd op INDEX, RANDARRAY en TEXTJOIN. In het getoonde voorbeeld is de formule in D5:
=TEXTJOIN("",1,INDEX(chars,RANDARRAY(6,1,1,26,TRUE)))
waarbij chars het benoemde bereik is B5: B30 met de letters AZ. Terwijl de formule door de kolom wordt gekopieerd, genereert deze op elke regel een nieuwe tekenreeks van 6 tekens.
Uitleg
De nieuwe dynamische matrixformules in Excel 365 maken het veel gemakkelijker om bepaalde lastige problemen met formules op te lossen.
In dit voorbeeld is het doel om een lijst met willekeurige codes van 6 tekens te genereren. De willekeurigheid wordt afgehandeld door de functie RANDARRAY, een nieuwe functie in Excel 365. RANDARRAY retourneert 6 willekeurige getallen naar INDEX, die vervolgens 6 willekeurige waarden ophaalt uit de benoemde bereikkarakters. De resultaten van INDEX worden vervolgens samengevoegd met de TEXTJOIN-functie.
In het getoonde voorbeeld is de formule in D5:
=TEXTJOIN("",1,INDEX(chars,RANDARRAY(6,1,1,26,TRUE)))
Werkend van binnen naar buiten, wordt de RANDARRAY-functie gebruikt om een array te genereren met zes willekeurige getallen tussen 1-26:
RANDARRAY(6,1,1,26,TRUE) // return array like (14;5;21;7;25;3)
Merk op dat de geretourneerde array varieert met elk exemplaar van de functie RANDARRAY. Omdat RANDARRAY een vluchtige functie is, wordt deze bovendien bij elke wijziging in het werkblad opnieuw berekend.
Deze array van willekeurige getallen wordt direct geretourneerd naar de INDEX-functie als het rijen-argument:
INDEX(chars,(14;5;21;7;25;3))
Omdat we INDEX voor 6 rijen vragen, krijgen we 6 resultaten terug in een array als deze:
("N","E","U","G","Y","C")
Deze array wordt teruggestuurd naar de TEXTJOIN-functie als het text1-argument:
=TEXTJOIN("",1,("N","E","U","G","Y","C")) // returns "NEUGYC"
TEXTJOIN is ingesteld om een lege tekenreeks als scheidingsteken te gebruiken en om lege waarden te negeren. Met deze configuratie voegt TEXJOIN eenvoudig alle waarden samen en retourneert een tekenreeks van 6 tekens zoals "NEUGYC".
Tel tekens programmatisch
In plaats van de grootte van tekens rechtstreeks in de RANDARRAY-functie te coderen, kunt u de COUNTA-functie gebruiken om de elementen in de array te tellen en dat aantal terug te sturen naar RANDARRAY:
RANDARRAY(6,1,1,COUNTA(chars),TRUE)
Dit veronderstelt dat tekens geen lege cellen bevatten.
Genereer tekens programmatisch
Omdat de letters AZ onderliggende numerieke codewaarden hebben, is het mogelijk om de reeks tekens te genereren die worden gebruikt om tekstreeksen programmatisch samen te stellen, in plaats van een bereik te gebruiken. Dit kan gedaan worden met de CHAR-functie en de SEQUENCE-functie.
Om een array te genereren met allemaal hoofdletters AZ, die verwijzen naar ASCII 65-90:
=CHAR(SEQUENCE(26,1,65,1)) // returns ("A","B","C",… )
Om kleine letters az te genereren, die overeenkomen met ASCII 97-122:
=CHAR(SEQUENCE(26,1,97,1)) // returns ("a","b","c",… )
Deze code kan in de originele formule worden neergezet om "tekens" als volgt te vervangen:
=TEXTJOIN("",1,INDEX(CHAR(SEQUENCE(26,1,65,1)),RANDARRAY(6,1,1,26,TRUE)))
Andere karakters
De karakters in de benoemde reeks karakters kunnen alles zijn wat u maar wilt. Als u meer dan 26 tekens (of minder) toevoegt, past u het getal 26 naar wens aan, of gebruikt u COUNTA zoals hierboven uitgelegd.
Zonder 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))
In deze versie retourneert RANDBETWEEN een waarde tussen 65 en 90 (inclusief) die overeenkomt met de ASCII-waarde voor de letters AZ (hoofdletters). De CHAR-functie vertaalt de numerieke waarde naar een letter. Net als hierboven worden alle resultaten samengevoegd in een enkele tekstreeks.
