Formula generik
=INDEX(data,MATCH(ROWS(exp_range),sort_values,0))
Ringkasan
Untuk mengurutkan nilai yang ada dengan formula secara rawak, anda boleh menggunakan formula INDEX dan MATCH bersama dengan kolum pembantu seperti yang ditunjukkan dalam tangkapan skrin. Dalam contoh yang ditunjukkan, formula di E5 adalah:
=INDEX(names,MATCH(ROWS($D$5:$D5),sort,0))
di mana "names" adalah range bernama B5: B11, "rand" adalah range bernama C5: C11, dan "sort" adalah range bernama D5: D11.
Penjelasan
Formula ini bergantung pada dua tiang penolong. Lajur pembantu pertama menyimpan nilai rawak yang dibuat dengan fungsi RAND (). Rumus dalam C5, disalin adalah:
=RAND()
Fungsi RAND menghasilkan nilai rawak pada setiap baris.
Catatan: RAND adalah fungsi yang tidak menentu dan akan menghasilkan nilai baru dengan setiap perubahan lembaran kerja.
Lajur penolong kedua memegang nombor yang digunakan untuk menyusun data, dihasilkan dengan formula. Formula dalam D5 adalah:
=RANK(C5,rand)+COUNTIF($C$5:C5,C5)-1
Lihat halaman ini untuk penjelasan mengenai formula ini.
Formula dalam E5 adalah:
=INDEX(names,MATCH(ROWS($D$5:$D5),sort,0))
Di sini, fungsi INDEX digunakan untuk mengambil nilai dalam julat "nama" yang dinamakan, menggunakan nilai urutan dalam julat bernama "sort". Kerja sebenar untuk mengetahui apa nilai untuk diambil dilakukan fungsi MATCH saya dalam coretan ini:
MATCH(ROWS($D$5:$D5),sort,0)
Di dalam MATCH, fungsi ROWS diberi julat yang berkembang sebagai nilai pencarian, yang dimulai sebagai satu sel, dan berkembang ketika formula disalin ke bawah lajur. Ini meningkatkan nilai carian, bermula pada 1 dan terus menjadi 7. PERTANDINGAN kemudian mengembalikan kedudukan nilai carian dalam senarai.
Kedudukan diberi makan kepada INDEX sebagai nombor baris, dan INDEX mengambil nama pada kedudukan tersebut.
