Rumus Excel untuk Membuat Ranking dengan Banyak Kriteria pada Microsoft Excel

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.

Contoh kasus menentukan ranking dengan banyak kriteria ini biasanya terjadi pada guru atau wali kelas lembaga pendidikan. Dimana dlm 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.

File Latihan pembahasan rangking multi kriteria ini bisa anda download pada bagian akhir tutorial ini.

Perhatikan contoh gambar brkut ini:



Ranking Excel pada contoh diatas didasarkan pada jumlah nilai. Karena ada beberapa nilai yg 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.

Penentuan Ranking pada tutorial kali ini menggunakan Fungsi RANK.EQ. Jika anda menggunakan microsoft office 2007 atau kebawah gunakan Fungsi RANK. Kedua fungsi Excel ini menghasilkan nilai ranking yg sama.

Mulai Excel 2010 Microsoft Excel mulai memperkenalkan fungsi RANK.EQ & RANK.AVG sbgai pengganti Fungsi RANK.

Jika pada kasus diatas anda hanya ingin membuat ranking yg unik atau tidak ganda berdasarkan jumlah nilai saja maka slahkan baca artikel belajar excel tentang Rumus Excel untuk Mencegah Duplicate Ranking yg sdh saya bahas sebelumnya.

Jika terjadi duplikat ranking sprti contoh diatas & anda ingin menentukan ranking berdasarkan kriteria pada nilai-nilai yg 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 yg memperoleh "Nilai 1" lebih tinggi maka akan mendapat ranking yg lebih sedikit. Jika "Jumlah Nilai" & "Nilai 1" sama maka akan di prioritaskan untuk yg mendapat "Nilai 2" lebih tinggi begitu seterusnya dengan urutan kriteria "Nilai 3", "Nilai 4", & "Nilai 5". Jika "Nilai 5" juga sama maka "Nama" yg mendapat "No" lebih kecil atau yg namanya ditulis lebih dahulu akan diprioritaskan.

Biasanya ranking disusun secara descending atau menurun. Dari nilai terbesar ke yg terkecil. Nilai terbesar mendapat ranking yg kecil. Misal ranking urutan 100,90,80 adalah 1,2,3.

Jadi urutan kriteria penentuan Ranking pada contoh ini adalah:

1. Jumlah Nilai
2. Nilai 1
3. Nilai 2
4. Nilai 3
5. Nilai 4
6. No

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 yg akan kita jadikan rujukan perankingan atau pemeringkatan.

Cara membuat Composit Key dlm kasus pemeringkatan multi kriteria kali ini adalah dengan membuat penjumlahan hasil perankingan pada masing-masing baris kolom kriteria yg sdh kita tetapkan. Agar lebih mudah difahami kita akan membuat kolom dummy atau kolom bantu.

Masing-masing pada cell J2, K2, L2, M2, N2, & O2 tuliskan rumus excel brkut:

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 yg sdh kita tetapkan diawal.

Untuk kolom J sampai N pemeringkatan atau perangkingan kita buat secara order ascending atau urutan naik. Agar jumlah atau nilai yg tinggi mendapat angka rangking yg tinggi pula.

Se&gkan pada kolom O atau sel O2 order pemeringkatan kita buat descending atau menurun. Tujuannya agar no yg lebih awal atau ada pada baris (row) atas mendapat Ranking yg lebih banyak secara angka sesuai kriteria yg kita tetapkan sebelumnya.

Adapun tujuan *10^10 (Kali 10 Pangkat 10), *10^8 (Kali sepuluh pangkat 8) & seterusnya adalah agar hasil penjumlahan nanti bisa kita pakai sbgai composit key sbgai acuan pemeringkatan.

Rumus excel perkalian diatas tidak mutlak sprti itu. Angka dibelakang pangkat (^) tergantung/dipengaruhi dari jumlah siswa atau jumlah record yg akan kita ranking dan jumlah kriteria yg kita tetapkan.

Sebagai catatan juga bahwa dlm satu cell excel hanya menerima 15 digit angka. Maka jika composite key yg anda buat melebihi 15 digit pisahkan dulu menjadi dua atau tiga composite key & buatlah composite key baru dari pecahan composite key tadi.

Intinya kita buat composite key yg bersifat unik (tidak ganda) berdasarkan kolom-kolom kriteria yg hasil akhirnya bisa kita Ranking lagi untuk mendapatkan ranking yg unik & sesuai yg kita harapkan.

Jika rumus-rumus excel tadi sdh anda tuliskan brkutnya buat satu kolom lagi sbgai komposit key pada cel P2 dengan rumus excel:

=J2+K2+L2+M2+N2+O2

Jika sdh copy pastekan semua rumus pada masing-masing cel tadi kebawah sehingga hasil akhirnya kita akan mendapatkan nilai sprti gambar brkut :



Langkah brkutnya buat sebuah kolom baru lagi pada kolom Q & 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 sprti ini:



Nah rumus pada kolom Q2 sampai Q11 ini adalah ranking yg sdh memenuhi kriteria yg 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 sprti gambar dibawah ini:



Sebagai pelengkap slahkan download file contoh dibawah ini:

Dropbox
GDrive

*Jika link mati / tidak dapat diakses slahkan lapor via kontak yg tersedia

FacebookTwitterWhatsapp

LINK DOWNLOAD TERKUNCI.
Silahkan SHARE untuk membuka kunci!

Jika masih belum bisa memahami slahkan dibaca lagi dari awal. Kalau berulangkali masih belum faham juga slahkan sampaikan dibagian komentar.

Seri tutorial membuat ranking di Excel lainnya:

• Cara membuat ranking di excel
• Cara mencegah peringkat ganda pada excel
• Rumus ranking ganda tanpa loncat
• Cara membuat ranking dengan banyak kriteria di excel
• Cara mengurutkan nama sesuai ranking secara otomatis

Sumber https://www.kelasexcel.id

Bagikan Artikel ini

Berlangganan update artikel terbaru via email: