Generisk formel
=INDEX(data,MATCH(ROWS(exp_range),sort_values,0))
Sammendrag
For å tilfeldig sortere eksisterende verdier med en formel, kan du bruke en INDEX- og MATCH-formel sammen med hjelpekolonner som vist på skjermbildet. I eksemplet vist er formelen i E5:
=INDEX(names,MATCH(ROWS($D$5:$D5),sort,0))
der "navn" er det navngitte området B5: B11, "rand" er det navngitte området C5: C11, og "sorter" er det navngitte området D5: D11.
Forklaring
Denne formelen avhenger av to hjelpekolonner. Den første hjelpekolonnen inneholder tilfeldige verdier opprettet med RAND () -funksjonen. Formelen i C5, kopiert ned er:
=RAND()
RAND-funksjonen genererer en tilfeldig verdi på hver rad.
Merk: RAND er en flyktig funksjon og vil generere nye verdier for hver regnearkendring.
Den andre hjelpekolonnen inneholder tallene som brukes til å sortere data, generert med en formel. Formelen i D5 er:
=RANK(C5,rand)+COUNTIF($C$5:C5,C5)-1
Se denne siden for en forklaring på denne formelen.
Formelen i E5 er:
=INDEX(names,MATCH(ROWS($D$5:$D5),sort,0))
Her brukes INDEX-funksjonen til å hente verdier i det navngitte området "navn", ved å bruke sorteringsverdiene i det navngitte området "sorter". Det faktiske arbeidet med å finne ut hvilken verdi du skal hente, gjøres i MATCH-funksjonen i dette utdraget:
MATCH(ROWS($D$5:$D5),sort,0)
Inne i MATCH får ROWS-funksjonen et utvidende område som oppslagsverdi, som begynner som en celle, og utvides når formelen kopieres nedover i kolonnen. Dette øker oppslagsverdien, starter ved 1 og fortsetter til 7. MATCH returnerer deretter posisjonen til oppslagsverdien i listen.
Posisjonen blir matet til INDEX som radnummer, og INDEX henter navnet på den posisjonen.
