Formula umum
=INDEX(data,MATCH(ROWS(exp_range),sort_values,0))
Ringkasan
Untuk mengurutkan nilai yang ada secara acak dengan rumus, Anda dapat menggunakan rumus INDEX dan MATCH bersama dengan kolom pembantu seperti yang ditunjukkan pada tangkapan layar. Dalam contoh yang ditampilkan, rumus dalam E5 adalah:
=INDEX(names,MATCH(ROWS($D$5:$D5),sort,0))
di mana "nama" adalah rentang bernama B5: B11, "rand" adalah rentang bernama C5: C11, dan "urutkan" adalah rentang bernama D5: D11.
Penjelasan
Rumus ini bergantung pada dua kolom pembantu. Kolom pembantu pertama menyimpan nilai acak yang dibuat dengan fungsi RAND (). Rumus di C5, disalin adalah:
=RAND()
Fungsi RAND menghasilkan nilai acak di setiap baris.
Catatan: RAND adalah fungsi yang mudah menguap dan akan menghasilkan nilai baru dengan setiap perubahan lembar kerja.
Kolom pembantu kedua berisi nomor yang digunakan untuk mengurutkan data, yang dibuat dengan rumus. Rumus di D5 adalah:
=RANK(C5,rand)+COUNTIF($C$5:C5,C5)-1
Lihat halaman ini untuk penjelasan tentang rumus ini.
Rumus dalam E5 adalah:
=INDEX(names,MATCH(ROWS($D$5:$D5),sort,0))
Di sini, fungsi INDEX digunakan untuk mengambil nilai dalam rentang bernama "nama", menggunakan nilai sortir dalam rentang bernama "sort". Pekerjaan sebenarnya untuk mencari tahu nilai apa yang akan diambil dilakukan oleh fungsi MATCH saya dalam cuplikan ini:
MATCH(ROWS($D$5:$D5),sort,0)
Di dalam MATCH, fungsi ROWS diberi rentang yang meluas sebagai nilai pencarian, yang dimulai sebagai satu sel, dan meluas saat rumus disalin ke bawah kolom. Ini menambah nilai pencarian, mulai dari 1 dan berlanjut ke 7. MATCH kemudian mengembalikan posisi nilai pencarian dalam daftar.
Posisi diumpankan ke INDEX sebagai nomor baris, dan INDEX mengambil nama pada posisi itu.
