Cara Membuat Ranking Ganda Tanpa Loncat Di Excel

Cara Membuat Ranking Ganda Tanpa Loncat Di Excel

Rumus excel untuk membuat ranking di excel tanpa loncat/lompat dan tetap urut meskipun terdapat data ganda atau duplikat
Cara Membuat Ranking Ganda Tanpa Loncat Di Excel

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

Coba perhatikan contoh hasil merangking dengan rumus Rank Excel berikut:

Rangking Ganda Tidak Urut

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

Selain ada peringkat nilai yang duplikat urutan rangkingnya juga melompat dari 1 ke 3, dari 4 ke 7, dan dari 8 ke 10. Hal ini disebabkan karena nilai pada rangking ke 1, 4 dan 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 dan 6?

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

Pada tutorial kali ini ada dua cara yang bisa Anda lakukan. Pertama dengan memanfaatkan beberapa kolom dummy untuk menyusun perangkingan nilai duplikat tanpa loncat dan yang 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 dan mempraktikkannya langsung.

Rumus Ranking Ganda Di Excel Tanpa Lompat

Langkah-Langkah yang perlu Anda lakukan untuk membuat rangking tanpa loncat adalah sebagai berikut:

  • 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 yang sudah ada di kolom A.

  • Menyusun Rangking Awal/Asli
    Langkah selanjutnya adalah menggunakan fungsi RANK excel pada kolom G untuk membuat peringkat nilai awal yang nantinya akan kita sesuaikan kembali pada kolom-kolom berikutnya.

    Pada cell G2 masukkan rumus excel berikut:

    =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 berikut: 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 berikut pada Cell H2 kemudian copy paste ke cell lain dibawahnya.

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

    Dari rumus di atas akan bisa Anda 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, sedangkan jika nilainya bukan 0 maka kita kurangi 1.

    Masukkan rumus If excel berikut pada Cell I2, kemudian copy paste ke bawahnya:

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

    Kenapa perlu kita kurangi 1? sebab yang akan kita sesuaikan pada dasarnya adalah dupliat rangkingnya saja. Jika sebuah rangking memiliki 3 duplikat maka hanya 2 saja yang perlu kita sesuaikan.

  • Membuat Penyesuaian Rangking ke-2
    Pada kolom J kita gunakan jumlah masing-masing rangking dan penyesuaiannya untuk membuat nilai penyesuaian baru. Masukkan rumus excel berikut pada cell J2.

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

    Jika peringkat tidak ada nilainya (H2=0 dan I2=0) maka rumus akan menghasilkan nilai penyesuaian sebelummya (diatasnya), dan 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 berikut:

    =INDEX($J$2:$J$11;G2)
  • Membuat Rangking Akhir
    Selanjutnya tinggal kita sesuaikan Rangking Awal dengan mengurangi nilainya dengan angka penyesuaian yang telah kita buat.

    Rumus pada L2 adalah:

    =G2-K2

    Copy paste rumus tersebut pada cell lain dibawahnya

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

Langkah terakhir adalah mengambil Rangking yang telah disesuaikan pada kolom L ke kolom D dengan memasukkan rumus berikut pada cell D2 kemudian copy paste ke cell lain dibawahnya:

=L2

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

Mudah bukan? jika masih bingung jangan ragu untuk membaca dan 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 dan tinggalkan dulu, kemudian beralih ke cara berikutnya di bawah ini.

Rumus Rangking Ganda Tanpa Loncat Dengan Rumus Array (SUM-IF-COUNTIF)

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

Rumus umum yang digunakan adalah:

=SUM(IF([Nilai]<=[RangeNilai];1/COUNTIF([RangeNilai];[RangeNilai])))
Cara membuat rangking tanpa lompat di Excel

Tulis rumus excel berikut sebagai 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)))

Setelah 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 sudah tidak asing lagi. Sebab pada dasarnya sama.

Hanya saja dalam kasus ini nilai unik ini di beri syarat kurang dari nilai yang akan kita rangking.

Pada rumus di atas fungsi IF akan mengecek setiap nilai apakah kurang dari nilai yang 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:

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

Sedangkan untuk baris kedua rumus"C3<=$C$2:$C$11" Akan menghasilkan nilai:

{FALSE;TRUE;TRUE;TRUE;TRUE;FALSE;TRUE;TRUE;FALSE;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 berikut:

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

Maksudnya nilai 60 ada 1, nilai 80 ada 1, nilai 85 ada 3, dan 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 yang bersesuaian dengan TRUE dari logika IF akan digunakan sedangkan yang FALSE tidak digunakan atau bernilai 0. Untuk baris pertama (D2) akan menghasikan nilai seperti ini:

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

Sedangkan untuk baris kedua (D3) seperti ini:

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

Kemudian fungsi SUM akan menjumlahkan nilai tersebut, sehingga didapatkan hasil 6 untuk baris pertama

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

Untuk baris kedua nilai hasil fungsi SUM adalah 4.

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

Nah, cukup mudah bukan?

Kalau mau yang lebih mudah rumus array di atas bisa anda gantikan dengan fungsi SUMPRODUCT yang tidak perlu CSE.

Rumus Rangking Ganda Tanpa Loncat Dengan Fungsi SUMPRODUCT + COUNTIF

Jika rumus array diatas masih terlalu sulit digunakan Anda juga bisa menggunakan fungsi SUMPRODUCT yang dalam eksekusinya tidak perlu menggunakan tombol Ctrl + Shift + Enter.

Rumus umum yang digunakan adalah sebagai berikut:

=SUMPRODUCT(([Nilai]<=[RangeNilai])/COUNTIF([RangeNilai];[RangeNilai]))
Cara membuat rangking tanpa lompat di Excel

Dengan data awal yang sama Anda dapat memasukkan rumus berikut di cell D2, keudian cukup di copy paste ke cell lain dibawahnya.

=SUMPRODUCT((C2<=$C$2:$C$11)/COUNTIF($C$2:$C$11;$C$2:$C$11))

Pada dasarnya cara kerja rumus di atas sama dengan gabungan fungsi SUM-IF-COUNTIF seeperti sebelumnya. Hanya saja di sini sumproduct dapat bekerja dengan Array dalam rumus.

Untuk baris pertama, bagian (C2<=$C$2:$C$11) hasilnya adalah:

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

Sebab kebetulan, baris pertama disini nilainya terkecil sehingga semua kurang dari atau sama dengan setiap nilai yang ada.

Sedangkan untuk baris ke-2, bagian (C3<=$C$2:$C$11) hasilnya adalah:

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

Dimana nilai false dihasilkan dari perbandingan pada nilai ke 1 (60), 6 (70) dan 9 (70)

Masing-masing bagian ini kemudian akan membagi nilai yang dihasilkan oleh bagian kedua rumus COUNTIF($C$2:$C$11;$C$2:$C$11)

Pada setiap barisnya bagian rumus Countif hasilnya akan sama yakni:

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

Sebab, argumen-argumen rangenya sudah kita kunci dengan simbol $ yang digunakan untuk membuat referensi absolut.

Jika digabungkan dengan bagian sebelumnya, akan seperti ini untuk baris pertama:

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

Untuk baris kedua:

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

Nilai TRUE oleh excel kemudian dikonversi menjadi angka 1 dan untuk nilai False diubah menjadi angka 0

SUMPRODUCT({1/1;1/1;1/3;1/1;1/2;1/2;1/3;1/2;1/2;1/3})

Untuk baris kedua:

SUMPRODUCT({0/1;1/1;1/3;1/1;1/2;0/2;1/3;1/2;0/2;1/3})

Hasil pembagian masing-masing nilai array kemudian dijumlahkan oleh fungsi SUMPRODUCT, sehingga mengghasilkan nilai 6 untuk baris pertama dan 4 untuk baris kedua.

Untuk baris-baris nilai lainnya menyesuaikan cara kerja diatas

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

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

LANGGANAN GRATIS!

Dapatkan update tutorial excel gratis langsung ke email anda.