Minggu, 18 Maret 2012

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

Oke, sekian dulu tips Sorting pake Formula.

Pada file contoh yang bisa di-download di sini atau di Boxnet pada sidebar, juga disertai cara Sorting dengan menggunakan Makro.




Artikel Terkait:

8 komentar:

Abd Rahman Palallo mengatakan...

yang saya bisa pahami pada penjelasan diatas hanya cara menyatukan antara satu kolom dengan kolom yang lainnya, tolong kalau bisa jelaskan cara menyambungkan sheet 1, sheet 2 dan sheet 3. Mohon petunjuk kawan.....

Funny mengatakan...

Sebenarnya mau satu sheet atau diletakkan di sheet yang lain, tidak ada bedanya. Hanya cara merujuk selnya yang berbeda. Misalnya, kalau dalam satu sheet kita rujuk sel B11, maka kalau dirujukkan di sheet lain, tinggal ditambah nama sheet di depannya, =Sheet4!B11
Demikianlah

mazguru mengatakan...

Mohon ijin artikel dari bapak saya copy-paste untuk saya jadikan e-book excel.

Semoga berkah dan manfaat

excell mengatakan...

Diijinkan. Silahkan.

Anonim mengatakan...

good luck pak...

Anonim mengatakan...

aduh aku gak ngerti T.T

ryanie_kusma mengatakan...

Alhamdulillah.. akhirnya stlh bbrp bulan ketemu jg yg sy cari.. Makasih artikelnya Pak.. sdh sy praktekkn cara yg pakai formula.. yg pakai makro kurang paham :) & Alhamdulillah berhasil.. tapi sy kesulitan untuk membuat angka unik pada data berupa ANGKA DESIMAL.. Mohon pencerahannya.. Bagaimana cara membuat angka desimal menjadi angka unik.. saat ini sy sdg mmbuat aplikasi sederhana utk laporan petugas gizi puskesmas.. Makasih byk sebelumnya

Unknown mengatakan...

Pak, Update lagi dong artikelnya. Terima kasih