Cara Membuat Ranking Ganda Tanpa Loncat Di Excel

Rumus Ranking Ganda Tanpa Loncat - Jika Anda mengikuti tutorial kmi, tentu sdh maklum bahwa fungsi Rank yg disediakan excel untuk membuat peringkat nilai ternyata akan menghasilkan nilai rangking yg meloncat atau melompat jika ada nilai yg memiliki rangking ganda/duplikat.

Coba perhatikan contoh hasil merangking dengan rumus Rank Excel brkut:



Jika Anda perhatikan ternyata hasil peringkat nilai yg dihasilkan rumus Rank excel pada contoh daftar nilai tersebut menghasilkan nilai ranking 1, 3, 4, 7, 8 & 10 & tidak ada nilai ranking 2, 5, 6 & 9.

Selain ada peringkat nilai yg duplikan urutan rangkingnya juga melompat dari 1 ke 3, dari 4 ke 7, & dari 8 ke 10. Hal ini disebabkan karena nilai pada rangking ke 1, 4 & 8 ada duplikatnya.

Lalu bagaimana cara membuat ranking di excel tanpa melewatkan urutan rangking meskipun terdapat nilai ganda atau duplikat sehinga menghasilkan nilai rangking 1, 2, 3, 4, 5 & 6?

Untuk tutorial membuat rangking tanpa peringkat ganda bisa Anda lihat pada halaman brkut: Cara mencegah peringkat ganda pada excel

• Rumus Rangking Ganda Tanpa Loncat Dengan Kolom Bantuan (Dummy)
• Rumus Rangking Ganda Tanpa Loncat Dengan Rumus Array

Pada tutorial kali ini ada dua cara yg bisa Anda lakukan. Pertama dengan memanfaatkan beberapa kolom dummy untuk menyusun perangkingan nilai duplikat tanpa loncat & yg kedua dengan menggunakan rumus Array.

Rumus Rangking Ganda Tanpa Loncat Dengan Kolom Dummy (Bantuan)

Sebelum membaca tutorial ini, ada baiknya Anda membuat contoh file sendiri kemudian pelan-pelan mambaca & mempraktikkannya langsung.



Langkah-Langkah yg perlu Anda lakukan untuk membuat rangking tanpa loncat adalah sbgai brkut:

• Buatlah nomor urut
  Langkah pertama adalah membuat nomor urut baru pada kolom F.
  
  Nomor urut ini boleh Anda abaikan jika Anda lebih memilih untuk menggunakan nomor urut yg sdh ada di kolom A.
  
• Menyusun Rangking Awal/Asli
  Langkah selanjutnya adalah menggunakan fungsi RANK excel pada kolom G untuk membuat peringkat nilai awal yg nantinya akan kita sesuaikan kembali pada kolom-kolom brkutnya.
  
  Pada cell G2 masukkan rumus excel brkut:
  

  =RANK(C2;$C$2:$C$11)

  
  Copy-paste rumus pada cell G2 tersebut pada semua cell lain di bawahnya.
  
  Mengenai cara kerja fungsi Rank ini bisa Anda baca lebih lanjut pada halaman brkut: Fungsi RANK Excel.
  
• Menghitung jumlah masing-masing peringkat/ranking
  Pada kolom H, hitung jumlah masing-masing peringkat atau Ranking nilai sesuai nomor urutnya menggunakan fungsi Countif dengan memasukkan rumus excel brkut pada Cell H2 kemudian copy paste ke cell lain dibawahnya.
  

  =COUNTIF($G$2:$G$11;F2)

  
  Dari rumus di atas akan bisa Nada ketahui jumlah masing-masing rangking 1-10:
  
  1. Rangking 1 ada 2
  2. Rangking 2 ada 0
  3. Rangking 3 ada 1
  4. Rangking 4 ada 3
  5. Dan seterusnya
• Membuat Penyesuaian Rangking ke-1
  Selanjutnya Pada kolom I kita buat dummy penyesuaian pertama, Yakni jika Jumlah Rangking pada kolom H adalah 0 maka tidak perlu disesuaikan, se&gkan jika nilainya bukan 0 maka kita kurangi 1.
  
  Masukkan rumus If excel brkut pada Cell I2, kemudian copy paste ke bawahnya:
  

  =IF(H2=0;0;H2-1)

  
  Kenapa perlu kita kurangi 1? sebab yg akan kita sesuaikan pada dasarnya adalah dupliat rangkingnya saja. Jika sebuah rangking memiliki 3 duplikat maka hanya 2 saja yg perlu kita sesuaikan.
  
• Membuat Penyesuaian Rangking ke-2
  Pada kolom J kita gunakan jumlah masing-masing rangking & penyesuaiannya untuk membuat nilai penyesuaian baru. Masukkan rumus excel brkut pada cell J2.
  

  =IF(AND(H2=0;I2=0);J1;SUM($I$1:I1))

  
  Jika peringkat tidak ada nilainya (H2=0 & I2=0) maka rumus akan menghasilkan nilai penyesuaian sebelummya (diatasnya), & sebaliknya akan menghasilkan nilai penjumlahan angka penyesuaian (Kolom I) sampai baris terkait.
  
  Perhatikan juga penggunaan tanda $ pada rumus excel tersebut.
  
• Mengurutkan nilai Penyesuaian Masing-masing Rangking
  Selanjutnya Pada kolom K, kita sejajarkan masing-masing penyesuaian rangking dengan Rangking Awal menggunakan fungsi INDEX. Pada K2 masukkan rumus excel brkut:
  

  =INDEX($J$2:$J$11;G2)

• Membuat Rangking Akhir
  Selanjutnya tinggal kita sesuaikan Rangking Awal dengan mengurangi nilainya dengan angka penyesuaian yg tlah kita buat.
  
  Rumus pada L2 adalah:
  

  =G2-K2

  
  Copy paste rumus tersebut pada cell lain dibawahnya

Setlah melakukan rangkainya penyesuaian diatas Anda akan mendapatkan hasil akhir Rangking tanpa loncat meskipun terdapat nilai ganda atau duplikat.

Langkah terakhir adalah mengambil Rangking yg tlah disesuaikan pada kolom L ke kolom D dengan memasukkan rumus brkut pada cell D2 kemudian copy paste ke cell lain dibawahnya:

=L2

Dan akhirnya akan kita dapati rangking peringkat nilai yg tetap urut tanpa loncat.

Mudah bukan? jika masih bingung jangan ragu untuk membaca & praktik langkah-langkah di atas 1-10x lagi. Barangkali pada urutan ke 11 Anda akan mendapatkan pencerahan.

Jika pada urutan ke 11 masih bingung juga, sebaiknya ambil nafas & tinggalkan dulu, kemudian beralih ke cara brkutnya di bawah ini.

Rumus Rangking Ganda Tanpa Loncat Dengan Rumus Array

Untuk rumus kedua ini, kita tidak perlu membuat beberapa kolom bantuan & cukup menggunakan 1 cell saja untuk membuat rangking tanpa loncat. Yakni dengan menggunakan Rumus Array Excel.



Tulis rumus excel brkut sbgai rumus CSE (Array) pada Cell D2 tanpa tanda {...}.

=SUM(IF(C2<$C$2:$C$11;1/COUNTIF($C$2:$C$11;$C$2:$C$11)))+1

Setlah menuliskan rumus di atas akhiri dengan menekan tombol Ctrl + Shift + Enter untuk memunculkan tanda {...}

Jika Anda pernah membaca cara menghitung nilai unik pada blog tutorial excel ini, tentunya rumus di atas sdh tidak asing lagi. Sebab pada dasarnya sama.

Hanya saja dlm kasus ini nilai unik ini di beri syarat kurang dari nilai yg akan kita rangking.

Pada rumus di atas fungsi IF akan mengecek setiap nilai apakah kurang dari nilai yg akan di rangking sehingga rumus:

IF(C2<$C$2:$C$11;1/COUNTIF($C$2:$C$11;$C$2:$C$11))

Bagian logika "C2<$C$2:$C$11" Akan menghasilkan nilai:

{FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE}

Untuk setiap nilai True Fungsi IF akan menghasilkan nilai rumus:

1/COUNTIF($C$2:$C$11;$C$2:$C$11)

Fungsi Countif di atas digunakan untuk menghitung jumlah masing-masing nilai sehingga di dapatkan nilai brkut:

1/{1;1;3;1;2;2;3;2;2;3}

Maksudnya nilai 60 ada 1, nilai 80 ada 1, nilai 85 ada 3, & seterusnya untuk masing-masing nilai.

Masing-masing nilai tersebut kemudian digunakan untuk membagi angka 1, sehingga didapatkan nilai:

{1/1;1/1;1/3;1/1;1/2;1/2;1/3;1/2;1/2;1/3}

Nilai diatas yg bersesuaian dengan TRUE dari logika IF akan digunakan se&gkan yg FALSE tidak digunakan atau bernilai 0:

{FALSE;1/1;1/3;1/1;1/2;1/2;1/3;1/2;1/2;1/3}

Kemudian fungsi SUM akan menjumlahkan nilai tersebut kemudian ditambah angka 1, sehingga didapatkan hasil 6

SUM({1/1;1/1;1/3;1/1;1/2;1/2;1/3;1/2;1/2;1/3})+1

Nah, cukup mudah bukan?

Jangan ragu untuk meninggalkan komentar jika masih ada yg kurang jelas.

Jika Anda mendapatkan manfaat dari tutorial ini, jangan lupa juga untuk klik share, supaya lebih banyak teman-teman Anda yg juga ikut mendapatkan manfaatkanya.

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: