Rumus Rangking Dengan Banyak Kriteria - Sebagaimana janji saya sebelumnya, kali ini saya akan menulis turorial belajar excel tentang cara ketiga mencegah duplikat ranking atau peringkat ganda. tutorial kali ini sekaligus membahas tentang cara membuat ranking Excel dengan multiple criteria atau ranking excel dengan banyak kriteria.
Mencari Ranking di Excel
Contoh kasus menentukan ranking dengan banyak kriteria ini biasanya terjadi pada guru atau wali kelas lembaga pendidikan. Dimana dalam menetukan ranking siswa biasanya didasarkan pada jumlah nilai-nilai banyak mata pelajaran. Persoalan muncul tatkala jumlah nilai-nilai tersebut sama. Ketika terjadi jumlah nilai sama maka ranking didasarkan pada urutan nilai mata pelajaran tertentu.
Perhatikan contoh gambar berikut ini:
Ranking Excel pada contoh diatas didasarkan pada jumlah nilai. Karena ada beberapa nilai yang sama maka muncul beberapa ranking ganda. Rumus excel pada cell H2 contoh tersebut adalah:
=RANK.EQ(G2;$G$2:$G$11;0)
Copy-Pastekan rumus diatas ke cel-cel bawahnya jika anda mencoba berlatih sesuai gambar diatas.
Jika pada kasus diatas anda hanya ingin membuat ranking yang unik atau tidak ganda berdasarkan jumlah nilai saja maka silahkan baca artikel belajar excel tentang Rumus Excel untuk Mencegah Duplicate Ranking yang sudah saya bahas sebelumnya.
Menentukan Kriteria Ranking Nilai
Jika terjadi duplikat ranking seperti contoh diatas dan anda ingin menentukan ranking berdasarkan kriteria pada nilai-nilai yang diperoleh siswa, maka bacalah tutorial ini sampai selesai. :D
Pada kasus penetuan ranking excel ini misal kita ingin membuat kriteria bahwa ranking disusun berdasarkan "Jumlah Nilai". Jika "Jumlah Nilai" sama maka siswa atau nama yang memperoleh "Nilai 1" lebih tinggi maka akan mendapat ranking yang lebih sedikit. Jika "Jumlah Nilai" dan "Nilai 1" sama maka akan di prioritaskan untuk yang mendapat "Nilai 2" lebih tinggi begitu seterusnya dengan urutan kriteria "Nilai 3", "Nilai 4", dan "Nilai 5". Jika "Nilai 5" juga sama maka "Nama" yang mendapat "No" lebih kecil atau yang namanya ditulis lebih dahulu akan diprioritaskan.
Jadi urutan kriteria penentuan Ranking pada contoh ini adalah:
- Jumlah Nilai
- Nilai 1
- Nilai 2
- Nilai 3
- Nilai 4
- No
Membuat Composite Key
Untuk membuat Ranking Excel berdasarkan banyak kriteria kita membutuhkan Composite Key. Composite Key adalah kombinasi dari sel-sel kriteria sehingga membentuk sebuah key unik baru yang akan kita jadikan rujukan perankingan atau pemeringkatan.
Cara membuat Composit Key dalam kasus pemeringkatan multi kriteria kali ini adalah dengan membuat penjumlahan hasil perankingan pada masing-masing baris kolom kriteria yang sudah kita tetapkan. Agar lebih mudah difahami kita akan membuat kolom dummy atau kolom bantu.
Masing-masing pada cell J2, K2, L2, M2, N2, dan O2 tuliskan rumus excel berikut:
Pada sel J2 :
=RANK.EQ(G2;$G$2:$G$11;1)*10^10
Pada sel K2 :
=RANK.EQ(C2;$C$2:$C$11;1)*10^8
Pada sel L2:
=RANK.EQ(D2;$D$2:$D$11;1)*10^6
Pada sel M2:
=RANK.EQ(E2;$E$2:$E$11;1)*10^4
Pada sel N2:
=RANK.EQ(F2;$F$2:$F$11;1)*10^2
Pada sel O2:
=RANK.EQ(A2;$A$2:$A$11;0)
Rumus Excel pada masing-masing kolom tersebut bermaksud membuat ranking untuk masing-masing kriteria sesuai urutan kriteria yang sudah kita tetapkan diawal.
Untuk kolom J sampai N pemeringkatan atau perangkingan kita buat secara order ascending atau urutan naik. Agar jumlah atau nilai yang tinggi mendapat angka rangking yang tinggi pula.
Sedangkan pada kolom O atau sel O2 order pemeringkatan kita buat descending atau menurun. Tujuannya agar no yang lebih awal atau ada pada baris (row) atas mendapat Ranking yang lebih banyak secara angka sesuai kriteria yang kita tetapkan sebelumnya.
Adapun tujuan *10^10 (Kali 10 Pangkat 10), *10^8 (Kali sepuluh pangkat 8) dan seterusnya adalah agar hasil penjumlahan nanti bisa kita pakai sebagai composit key sebagai acuan pemeringkatan.
Rumus excel perkalian diatas tidak mutlak seperti itu. Angka dibelakang pangkat (^) tergantung/dipengaruhi dari jumlah siswa atau jumlah record yang akan kita ranking serta jumlah kriteria yang kita tetapkan.
Intinya kita buat composite key yang bersifat unik (tidak ganda) berdasarkan kolom-kolom kriteria yang hasil akhirnya bisa kita Ranking lagi untuk mendapatkan ranking yang unik dan sesuai yang kita harapkan.
Jika rumus-rumus excel tadi sudah anda tuliskan berikutnya buat satu kolom lagi sebagai komposit key pada cel P2 dengan rumus excel:
=J2+K2+L2+M2+N2+O2
Jika sudah copy pastekan semua rumus pada masing-masing cel tadi kebawah sehingga hasil akhirnya kita akan mendapatkan nilai seperti gambar berikut :
Menyusun Ranking Excel Berdasarkan Composite Key
Setelah berhasil membuat kolom composite key, selanjutnya kita akan membuat rangking multi kriterianya berdarsarkan kolom composite key yang telah dibuat tadi.
Langkah berikutnya buat sebuah kolom baru lagi pada kolom Q dan tuliskan rumus excel ini pada sel Q2:
=RANK.EQ(P2;$P$2:$P$11;0)
Copy-pastekan rumus tersebut ke bawah sampai pada sel Q11 sehingga menjadi seperti ini:
Nah rumus pada kolom Q2 sampai Q11 ini merupakan ranking yang sudah memenuhi kriteria yang kita tetapkan di awal tadi.
Rumus Excel penyusunan Composit Key (Sel P2 Gambar dibawah) tadi jika kita gabung akan menjadi:
=RANK.EQ(G2;$G$2:$G$11;1)*10^10
+RANK.EQ(C2;$C$2:$C$11;1)*10^8
+RANK.EQ(D2;$D$2:$D$11;1)*10^6
+RANK.EQ(E2;$E$2:$E$11;1)*10^4
+RANK.EQ(F2;$F$2:$F$11;1)*10^2
+RANK.EQ(A2;$A$2:$A$11;0)
Dan rumus Excel untuk membuat ranking dengan Banyak Kriteria (Sel H2 Gambar dibawah) menjadi:
=RANK.EQ(P2;$P$2:$P$11;0)
Hasil Akhirnya akan seperti gambar dibawah ini:
Download File Latihan
Sebagai pelengkap silahkan download file contoh dibawah ini:
Download File *Jika link mati / tidak dapat diakses silahkan lapor via kontak yang tersedia
LINK DOWNLOAD TERKUNCI.
Silahkan SHARE untuk download file!
Jika masih belum bisa memahami silahkan dibaca lagi dari awal. Kalau berulangkali masih belum faham juga silahkan sampaikan dibagian komentar.