Bendroji formulė
=INDEX(data,MATCH(ROWS(exp_range),sort_values,0))
Santrauka
Norėdami atsitiktinai surūšiuoti esamas reikšmes pagal formulę, galite naudoti INDEX ir MATCH formules kartu su pagalbiniais stulpeliais, kaip parodyta ekrano kopijoje. Parodytame pavyzdyje formulė E5 yra:
=INDEX(names,MATCH(ROWS($D$5:$D5),sort,0))
kur „vardai“ yra pavadintas diapazonas B5: B11, „randas“ - pavadintas diapazonas C5: C11, o „rūšiuoti“ - pavadintas diapazonas D5: D11.
Paaiškinimas
Ši formulė priklauso nuo dviejų pagalbinių stulpelių. Pirmajame pagalbiniame stulpelyje yra atsitiktinės vertės, sukurtos naudojant funkciją RAND (). C5 formulė, nukopijuota žemyn, yra:
=RAND()
Funkcija RAND kiekvienoje eilutėje sukuria atsitiktinę vertę.
Pastaba: RAND yra nepastovi funkcija, kuri, keisdama kiekvieną darbalapį, sugeneruos naujas reikšmes.
Antrajame pagalbininkų stulpelyje yra skaičiai, naudojami duomenims rūšiuoti, sugeneruoti pagal formulę. D5 formulė yra:
=RANK(C5,rand)+COUNTIF($C$5:C5,C5)-1
Žr. Šiame puslapyje paaiškinimą apie šią formulę.
E5 formulė yra:
=INDEX(names,MATCH(ROWS($D$5:$D5),sort,0))
Čia funkcija INDEX naudojama norint gauti reikšmes pavadintame diapazone „vardai“, naudojant rūšiavimo reikšmes pavadintame diapazone „rūšiavimas“. Faktinis darbas, siekiant išsiaiškinti, kokią vertę atgauti, šiame fragmente atliekamas su funkcija MATCH:
MATCH(ROWS($D$5:$D5),sort,0)
Vykdant MATCH, funkcijai ROWS suteikiamas besiplečiantis diapazonas kaip paieškos vertė, kuri prasideda kaip viena ląstelė ir plečiasi, kai formulė nukopijuojama žemyn stulpelyje. Tai padidina paieškos vertę, pradedant nuo 1 ir tęsiant iki 7. MATCH grąžina paieškos vertės padėtį sąraše.
Pozicija tiekiama į INDEX kaip eilutės numerį, o INDEX gauna pavadinimą toje pozicijoje.
