Formula generică
=INDEX(data,MATCH(ROWS(exp_range),sort_values,0))
rezumat
Pentru a sorta aleatoriu valorile existente cu o formulă, puteți utiliza o formulă INDEX și MATCH împreună cu coloanele de ajutor, așa cum se arată în captura de ecran. În exemplul prezentat, formula din E5 este:
=INDEX(names,MATCH(ROWS($D$5:$D5),sort,0))
unde „nume” este intervalul numit B5: B11, „rand” este intervalul numit C5: C11, iar „sortare” este intervalul numit D5: D11.
Explicaţie
Această formulă depinde de două coloane de ajutor. Prima coloană helper conține valori aleatorii create cu funcția RAND (). Formula din C5, copiată este:
=RAND()
Funcția RAND generează o valoare aleatorie la fiecare rând.
Notă: RAND este o funcție volatilă și va genera noi valori la fiecare modificare a foii de lucru.
Cea de-a doua coloană de ajutor conține numerele utilizate pentru sortarea datelor, generate cu o formulă. Formula din D5 este:
=RANK(C5,rand)+COUNTIF($C$5:C5,C5)-1
Consultați această pagină pentru o explicație a acestei formule.
Formula din E5 este:
=INDEX(names,MATCH(ROWS($D$5:$D5),sort,0))
Aici, funcția INDEX este utilizată pentru a prelua valorile din intervalul denumit „nume”, utilizând valorile de sortare din intervalul denumit „sortare”. Lucrarea reală de a afla ce valoare trebuie preluată este realizată de funcția MATCH din acest fragment:
MATCH(ROWS($D$5:$D5),sort,0)
În interiorul MATCH, funcției ROWS i se oferă un interval de expansiune ca valoare de căutare, care începe ca o celulă și se extinde pe măsură ce formula este copiată în coloană. Aceasta crește valoarea de căutare, începând de la 1 și continuând la 7. MATCH apoi returnează poziția valorii de căutare în listă.
Poziția este alimentată la INDEX ca număr de rând, iar INDEX recuperează numele la acea poziție.
