Splošna formula
=INDEX(data,MATCH(ROWS(exp_range),sort_values,0))
Povzetek
Če želite naključno razvrstiti obstoječe vrednosti s formulo, lahko uporabite formulo INDEX in MATCH skupaj s pomožnimi stolpci, kot je prikazano na posnetku zaslona. V prikazanem primeru je formula v E5:
=INDEX(names,MATCH(ROWS($D$5:$D5),sort,0))
kjer je "names" imenovani obseg B5: B11, "rand" je imenovani obseg C5: C11, "sort" pa imenovani obseg D5: D11.
Pojasnilo
Ta formula je odvisna od dveh pomožnih stolpcev. Prvi pomožni stolpec vsebuje naključne vrednosti, ustvarjene s funkcijo RAND (). Kopirana formula v C5 je:
=RAND()
Funkcija RAND ustvari naključno vrednost v vsaki vrstici.
Opomba: RAND je spremenljiva funkcija in bo z vsako spremembo delovnega lista ustvarila nove vrednosti.
Drugi pomožni stolpec vsebuje številke, uporabljene za razvrščanje podatkov, ustvarjenih s formulo. Formula v D5 je:
=RANK(C5,rand)+COUNTIF($C$5:C5,C5)-1
Za razlago te formule glejte to stran.
Formula v E5 je:
=INDEX(names,MATCH(ROWS($D$5:$D5),sort,0))
Tu se funkcija INDEX uporablja za pridobivanje vrednosti v poimenovanem obsegu "imena" z uporabo vrednosti razvrščanja v imenovanem obsegu "razvrščanje". Dejansko delo pri ugotavljanju vrednosti, ki jo je treba pridobiti, je opravljeno s funkcijo MATCH v tem delčku:
MATCH(ROWS($D$5:$D5),sort,0)
Znotraj MATCH funkcija ROWS dobi razširitveno območje kot iskalno vrednost, ki se začne kot ena celica in se razširi, ko se formula kopira v stolpec. To poveča iskalno vrednost, začenši z 1 in nadaljuje na 7. MATCH nato vrne položaj iskalne vrednosti na seznamu.
Položaj se poda kot INDEX kot številka vrstice in INDEX pridobi ime na tem mestu.
