Cara Membuat Daftar Berdasarkan Rangking Nilai

Cara Membuat Daftar Berdasarkan Rangking Nilai

Fasilitas Sort (pengurutan sebuah Tabel berdasarkan kriteria tertentu) yang dilakukan dengan beberapa langkah memang sangat membantu pekerjaan tugas menggunakan Excel. Namun, dalam beberapa hal misalnya dalam pembuatan Program Analisis Ulangan Harian, kita menginginkan pembuatan tabel yang sudah terurut tersebut dibuat secara otomatis tanpa perlu ceklik sana-sini. 
Tulisan kali ini membahas cara Sorting dengan Formula, sebuah alternatif untuk meng-otomasikan program Analisis Nilai Ulangan, khususnya pada tahap sebelum membuat daftar Kelompok Atas dan Kelompok Bawah. 
Misal sudah ada tabel entri nilai per item seperti berikut ini:

Dari tabel awal seperti di atas, kita inginkan secara otomatis terbuat daftar baru yang sudah tersusun berdasarkan jumlah nilai masing-masing siswa, dari yang jumlah nilainya tertinggi ke terendah seperti berikut:

Berikut langkah-langkahnya:



Langkah 1
Buatlah terlebih dahulu tabel baru untuk menampung data hasil Sorting.


Langkah 2
Buatlah kolom bantuan (dalam contoh ini di sel R11 sampai T20)
Kolom bantuan (Kolom Helper/Dummy Columns) terdiri dari 3 kolom, di mana masing-masing kolom berisi:

• Kolom Pertama (Nilai Unik masing-masing siswa)

Nilai Unik ini berasal dari Jumlah nilai masing-masing siswa dan nomor urutnya. Nilai Unik dibutuhkan untuk mengantisipasi adanya jumlah nilai yang sama. Sebab kalau jumlah nilai sama, rangkingnya menjadi bingung khan? Sehingga kita tambahkan saja Nomor Urutnya. Agar siswa yang nomor urutnya lebih kecil posisinya lebih tinggi, maka di sini dicontohkan dengan mengurangkan 99 (maksimal kemungkinan jumlah siswa) dengan nomor urut. Sehingga rumusnya menjadi: =O11&(99-B11)

Rumus di atas menghasilkan data tipe teks. Untuk itu kita harus mengubahnya menjadi data tipe numerik dengan salah satu cara berikut:

=VALUE(O11&(99-B11))

=0+(O11&(99-B11))

=1*(O11&(99-B11))

=--(O11&(99-B11))

• Kolom Kedua (Urutan Nilai Unik dari yang terbesar ke terkecil)

Nilai Unik dari kolom pertama, kita urutkan dengan memanfaatkan fungsi LARGE. Sehingga rumusnya:

=LARGE(R$11:R$20,B11)

• Kolom Ketiga (Nomor Urut Siswa dari yang Nilai Unik terbesar ke yang terkecil)

Kolom ini mencari Nomor Urut Siswa yang Nilai Unik-nya terbesar ke yang terkecil dengan rumus:

=MATCH(S11,R$11:R$20,0)

Kolom inilah yang akan dijadikan acuan untuk membuat tabel baru berdasarkan rangking.

Ketiga kolom di atas tampak seperti gambar berikut:

Ketiga kolom tersebut sebenarnya bisa diciutkan menjadi hanya satu kolom saja dengan menggunakan Array Formula Multi Hasil :

=MATCH(LARGE(1*($O$11:$O$20&99-$B$11:$B$20),ROW(1:10)),1*($O$11:$O$20&99-$B$11:$B$20),0)

Seperti gambar berikut:

Langkah 3

Dengan adanya nomor urut yang sudah tersortir, maka untuk membuat tabel baru menjadi SANGAT GAMPANG. Tinggal menggunakan rumus VLOOKUP atau INDEX atau OFFSET, urusan sudah beres.

Kalau saya, lebih suka menggunakan rumus OFFSET seperti berikut :

=OFFSET(C$10,$T11,0)

Rumus ini bisa langsung dikopi ke kanan dan bawah. Selesai