Rumus Rank di Excel: Panduan Komprehensif Disertai Contoh

Peringkat membantu Anda membandingkan nilai dan mengidentifikasi pola dalam himpunan data. Excel menyediakan alat yang andal seperti RANK(), RANK.EQ(), dan RANK.AVG() untuk memudahkan proses peringkat. Fungsi-fungsi ini berguna di berbagai situasi, mulai dari memberi peringkat peserta turnamen olahraga berdasarkan poin hingga menganalisis tren pasar berdasarkan data penjualan yang diberi peringkat.

Pada artikel ini, kita akan membahas ketiga fungsi peringkat di Excel dan menjelaskan cara menggunakannya dengan contoh langsung. Kita juga akan membahas penggunaan fungsi peringkat bersama fungsi Excel lainnya serta menghitung peringkat persentil. 

Jawaban Singkat: Cara Menghitung Peringkat di Excel 

Untuk menghitung peringkat dalam himpunan data yang Anda inginkan, gunakan rumus berikut:

=RANK(number, ref, [order])

Memahami Fungsi RANK() di Excel

Misalkan Anda menyelenggarakan kompetisi antar siswa sekolah dan kini ingin memberi peringkat ribuan siswa berdasarkan performa mereka. 

Apakah Anda akan menghabiskan berjam-jam memilah data? Tidak, karena Excel bisa membantu. Di antara banyak fungsi andalnya terdapat fungsi RANK(), yang memungkinkan Anda menentukan posisi seseorang hanya dengan sekali klik. 

Untuk menghitung peringkat suatu nilai dalam sebuah rentang nilai, masukkan rumus:

RANK(number, ref, [order])

Pada rumus di atas:

• number: adalah angka yang ingin Anda beri peringkat.
• ref: adalah rentang angka yang diperhitungkan untuk peringkat.
• order: menentukan urutan peringkat. Untuk menaik, gunakan nilai "1"; untuk menurun, gunakan "0" (default).

Cara Menggunakan Rumus RANK() di Excel

Mari terapkan fungsi RANK() pada contoh nyata:

Memberi peringkat nilai siswa

Menghitung nilai akan menantang dan memakan waktu jika Anda memiliki ribuan siswa. Misalkan Anda sedang menyiapkan hasil akhir dan ingin memberi peringkat siswa berdasarkan skor. 

Untuk melakukannya, siapkan terlebih dahulu himpunan data Anda.

Contoh dataset siswa di Excel. Sumber: Gambar oleh penulis.

Masukkan rumus =RANK(B3,($B$3:$B$7)) pada satu sel dan seret hingga sel terakhir yang terisi untuk mendapatkan peringkat.

Menggunakan fungsi RANK() untuk menghitung peringkat siswa. Sumber: Gambar oleh penulis.

Kini Anda mendapatkan peringkat siswa hanya dengan sekali klik.

Namun, apakah Anda melihat dua siswa dengan nilai sama memiliki peringkat 4? Siswa berikutnya, dengan 23 poin, langsung ke peringkat 6, melewati peringkat 5. Artinya, rumus memberikan peringkat yang sama untuk skor duplikat. 

Hal ini bisa mengacaukan urutan untuk skor yang berdekatan. Tetapi Anda bisa memutus seri ini menggunakan fungsi COUNTIF() . Masukkan rumus berikut: 

=RANK(B3,($B$3:$B$8)) + COUNTIF($B$3:B3, B3)-1

Fungsi COUNTIF() menyesuaikan peringkat untuk nilai duplikat. Fungsi ini menghitung berapa kali nilai siswa saat ini muncul di atasnya dalam daftar.

Memutus seri menggunakan fungsi COUNTIF() di Excel. Sumber: Gambar oleh penulis.

Nilai 28 muncul dua kali, dan COUNTIF() melacak jumlah kemunculannya. Jumlah kemunculan ini digunakan untuk menyesuaikan peringkat. Rumus menambahkan jumlah kemunculan dikurangi satu ke peringkat asli. Ini memastikan setiap nilai duplikat memperoleh peringkat unik.

Memberi peringkat angka penjualan dalam dataset

Mari lihat contoh lain untuk menunjukkan cara menggunakan fungsi Excel RANK() dalam urutan menaik atau menurun. 

Jika Anda ingin menilai kinerja tim penjualan, Anda dapat memberi peringkat angka penjualan bulanan mereka menggunakan fungsi RANK() di Excel.

Di sini, kita memiliki dataset dengan angka penjualan bulanan untuk setiap tenaga penjualan. Kita akan menggunakan fungsi RANK() untuk memberi peringkat berdasarkan siapa yang menghasilkan penjualan terbanyak (tertinggi ke terendah).

Daftar angka penjualan untuk diberi peringkat. Sumber: Gambar oleh penulis.

Untuk memberi peringkat penjualan dalam urutan menaik, kami menerapkan rumus =RANK(B2, ($B$2:$B$6), 1). Di sini, nilai "1" untuk parameter order menentukan urutan menaik untuk peringkat. 

Memberi peringkat nilai dalam urutan menaik. Sumber: Gambar oleh penulis.

Setelah Anda memasukkan rumus, cukup tahan kursor, seret sudut sel ke bawah, dan terapkan ke seluruh data penjualan Anda.

Demikian pula, jika Anda ingin memberi peringkat nilai dalam urutan menurun, tuliskan "0" sebagai nilai parameter order dalam rumus seperti ini: =RANK(B2, ($B$2:$B$6), 0)

Anda bisa melihat perbedaan hasilnya:

Memberi peringkat nilai dalam urutan menurun. Sumber: Gambar oleh penulis.

Anda mungkin memperhatikan tanda dolar pada referensi ($B$2:$B$6). Ini disebut referensi absolut. Referensi ini memastikan rumus selalu merujuk ke rentang data penjualan yang sama, bahkan saat Anda menyalinnya ke bawah, untuk mencegah kesalahan.

Jika Anda tidak menggunakan referensi absolut, seperti =RANK(A2, A2:A10), rentang akan menyesuaikan saat Anda menyeret ke bawah:

• Rumus di G3: =RANK(G3,(G3:G7), 1)
• Rumus di G4: =RANK(G4,(G4:G8), 1)

Untuk mencegah masalah ini, gunakan referensi absolut dengan menekan "F4". Kini rumusnya menjadi: =RANK(G3,($G$3:$G$7), 1)

Saat Anda menyeret rumus di atas hingga sel terakhir yang terisi, referensi relatif akan berubah berdasarkan posisi setiap sel. Anda bisa melihat perbedaan hasil saat menggunakan referensi absolut:

Perbedaan peringkat dengan dan tanpa referensi absolut. Sumber: Gambar oleh penulis.

Fungsi Peringkat Lanjutan di Excel: RANK.EQ() dan RANK.AVG()

Saat Anda menemui nilai duplikat di Excel, situasinya bisa jadi rumit. Di sinilah saya lebih suka menggunakan fungsi RANK.EQ() dan RANK.AVG(). Keduanya sedikit berbeda dalam penanganannya. Mari lihat cara kerjanya.

Fungsi Excel RANK.EQ()

RANK.EQ() bekerja sama seperti fungsi RANK() biasa tetapi dengan nama yang lebih presisi. Saat ada duplikat, RANK.EQ() memberikan peringkat yang sama kepada semuanya dan melewati peringkat berikutnya.

Sintaks: =RANK.EQ(number, ref, [order])

Pada rumus di atas:

• number: adalah angka yang ingin Anda beri peringkat.
• ref: adalah rentang angka yang diperhitungkan untuk peringkat.
• order: menentukan urutan peringkat. Untuk menaik, gunakan nilai "1"; untuk menurun, gunakan "0" (default).

Mari pahami rumus ini dengan contoh: Saya memiliki dataset populasi beberapa negara, dan saya ingin memberi peringkat negara-negara ini berdasarkan populasinya.

Begini cara saya melakukannya dengan fungsi RANK.EQ():

Menggunakan fungsi RANK.EQ(). Sumber: Gambar oleh penulis.

Pada gambar di atas, negara dengan populasi tertinggi berada di peringkat 1, dan seterusnya. Kini Anda melihat bagaimana RANK.EQ() menangani seri. 

Karena AS dan Indonesia memiliki populasi yang identik, keduanya berbagi peringkat 2. Ini menunjukkan dengan jelas bagaimana RANK.EQ() memberikan peringkat yang sama pada duplikat dan melewati peringkat berikutnya dalam urutan.

Fungsi Excel RANK.AVG()

Fungsi RANK.AVG() mengambil pendekatan berbeda untuk menangani seri dalam peringkat. Alih-alih memberikan peringkat yang sama untuk semua duplikat, fungsi ini menghitung rata-rata peringkat yang akan mereka terima secara individual.

Sintaks: RANK.AVG(number, ref, [order])

Pada rumus di atas:

• number: adalah angka yang ingin Anda beri peringkat.
• ref: adalah rentang angka yang diperhitungkan untuk peringkat.
• order: menentukan urutan peringkat. Untuk menaik, gunakan nilai "1"; untuk menurun, gunakan "0" (default).

Mari pahami dengan contoh: Saya memiliki dataset skor, jadi saya menerapkan fungsi RANK.AVG() untuk melihat bagaimana fungsi ini menangani skor seri.

Menggunakan fungsi RANK.AVG(). Sumber: Gambar oleh penulis.

Dengan menggunakan =RANK.AVG(D3,$D$3:$D$6), skor 88 memperoleh peringkat 2,5. Artinya fungsi menghitung rata-rata peringkat yang akan diberikan secara individual. 

Pendekatan ini menjaga distribusi peringkat yang lebih seimbang, terutama pada dataset dengan seri yang sering terjadi.

Mengombinasikan RANK() dengan Fungsi Excel Lain

Menggunakan fungsi RANK() bersama fungsi Excel lainnya berguna dalam beberapa skenario. Mari tinjau beberapa kasus penggunaan umum.

Mengombinasikan RANK() dan ROUNDUP() untuk mengategorikan data

Berikut cara Anda dapat mengombinasikan fungsi ROUNDUP() dengan fungsi RANK():

Mengombinasikan fungsi RANK() dan ROUNDUP() di Excel. Sumber. Gambar oleh penulis.

Saya menggunakan rumus =ROUNDUP(RANK(B2, $B$2:$B$7, 1)/3, 0), yang menghitung peringkat nilai pada sel B2 dalam rentang B2 secara menaik. Lalu membagi peringkat ini dengan tiga dan membulatkan ke atas hasilnya ke bilangan bulat terdekat.

Dengan rumus di atas, Anda secara efektif mengelompokkan data ke dalam kelompok atau tingkatan yang berbeda, yang dapat membantu mengidentifikasi dan menganalisis berbagai level kinerja atau mengategorikan data untuk analisis lanjutan.

Mengombinasikan RANK.EQ() dan COUNTIFS() untuk memutus seri

Kita telah membahas beberapa cara menangani seri menggunakan fungsi RANK() di Excel. Namun, misalkan Anda ingin memberi peringkat produk hanya berdasarkan harga. Ini mungkin terasa sewenang-wenang saat beberapa item memiliki harga yang sama. 

Jadi, mari perkenalkan kriteria sekunder untuk memutus seri dan menciptakan sistem peringkat yang lebih objektif.

Pertimbangkan daftar pelanggan yang membeli headphone. Kita ingin memberi peringkat berdasarkan harganya (kriteria utama) dan kemudian menggunakan penilaian pelanggan (kriteria sekunder) untuk membedakan jika terjadi seri.

Contoh daftar pelanggan. Sumber: Gambar oleh penulis 

Untuk mencapainya, kita akan mengombinasikan fungsi RANK.EQ() dengan fungsi COUNTIFS(). Begini caranya:

=RANK.EQ($B2,$B$2:$B$9) + COUNTIFS($B$2:$B$9, $B2, $C$2:$C$9, ">&"& $C2)

Menghitung peringkat menggunakan RANK.EQ() dan COUNTIFS(). Sumber: Gambar oleh penulis.

Bagian COUNTIFS($B$2:$B$9,$B2) menghitung seberapa sering harga saat ini muncul dalam daftar. Kita menggunakan referensi absolut untuk rentang $B$2:$B$9 demi konsistensi, tetapi mempertahankan sel kriteria $B2 tetap relatif agar rumus memeriksa setiap baris secara individual.

Bagian kedua, $C$2:$C$9,">"&$C2, mencari jumlah penilaian pelanggan yang lebih tinggi daripada penilaian headphone saat ini, C2.

COUNTIFS() mengikuti logika "AND": Hanya menghitung sel yang memenuhi semua kondisi. Jika tidak ada headphone lain pada harga yang sama memiliki penilaian lebih tinggi, COUNTIFS() menghasilkan 0. Itulah sebabnya peringkat yang ditentukan oleh RANK.EQ() tetap tidak berubah.

Menghitung Peringkat Persentil di Excel

Karena fungsi RANK() memberi peringkat nilai numerik, Anda dapat menggunakan rumus peringkat persentil Excel untuk menghitung peringkat nilai dalam dataset sebagai persentase dari keseluruhan dataset. 

Excel menyediakan dua fungsi untuk menghitung peringkat persentil. Mari kita jelajahi keduanya. 

Fungsi Excel PERCENTRANK.INC()

PERCENTRANK.INC() menghitung peringkat persentil suatu nilai, termasuk nilai terkecil dan terbesar dalam dataset.

Mari pahami fungsi ini dengan contoh: Saya memiliki dataset yang merepresentasikan nilai ujian 10 siswa, dan saya ingin menghitung peringkat persentil setiap nilai, termasuk nilai terkecil dan terbesar. 

Jadi, saya menggunakan rumus =PERCENTRANK.INC($A$2:$A$11, A2):

Menghitung peringkat menggunakan fungsi PERCENTRANK.INC(). Sumber: Gambar oleh penulis

Pada contoh di atas, Anda dapat melihat fungsi PERCENTRANK.INC() menyertakan nilai terkecil dan terbesar dalam dataset. Skor 45 (nilai terkecil) memiliki peringkat persentil 0, dan skor 90 (nilai terbesar) memiliki peringkat persentil 1.

Fungsi Excel PERCENTRANK.EXC()

PERCENTRANK.EXC() menghitung peringkat persentil suatu nilai, dengan mengecualikan nilai terkecil dan terbesar dalam dataset.

Mari pahami fungsi ini dengan contoh:  Saya menerapkan fungsi PERCENTRANK.EXC() pada dataset siswa yang sama dari bagian sebelumnya:

Menghitung peringkat menggunakan fungsi PERCENTRANK.EXC(). Sumber: Gambar oleh penulis

Pada contoh di atas, fungsi PERCENTRANK.EXC() mengecualikan nilai terkecil dan terbesar dalam dataset. Artinya skor 45 (nilai terkecil) tidak memiliki peringkat persentil 0, dan skor 90 (nilai terbesar) tidak memiliki peringkat persentil 1.

Kesalahan Umum pada Fungsi RANK() di Excel dan Cara Menghindarinya

Fungsi Excel praktis dan mudah digunakan untuk semua pekerjaan bisnis. Namun kadang, bahkan pengguna andal pun bisa membuat beberapa kesalahan umum. Jadi, saya membagikan beberapa kesalahan yang paling sering terjadi dan cara Anda dapat menghindarinya:

Salin-tempel rumus

Saat Anda menyalin rumus peringkat ke beberapa sel, pastikan Anda tidak secara tidak sengaja menggunakan referensi relatif. Setiap sel bisa merujuk ke bagian data yang berbeda dan mengacaukan peringkat Anda. 

Untuk menghindarinya, gunakan referensi absolut, seperti $A$1:$A$10, saat menyiapkan rumus Anda untuk mengunci rentang yang benar di seluruh peringkat.

Tidak menangani duplikat

Misalkan Anda memiliki dua tenaga penjualan yang seri untuk penjualan terbanyak. Cara Anda menangani seri dalam peringkat bisa berdampak besar: fungsi RANK() dan RANK.EQ() memperlakukan seri dengan cara yang serupa.

Jadi, Anda harus memutuskan apakah nilai yang seri pantas mendapatkan peringkat yang sama atau berbeda, atau apakah peringkatnya harus dirata-ratakan menggunakan RANK.AVG(). Lalu, gunakan fungsi yang sesuai. 

Urutan yang salah 

Terlihat sederhana, tetapi lupa mengatur argumen order dalam fungsi Anda dapat membuat peringkat tersortir ke arah yang salah (menaik atau menurun). 

Karena itu Anda harus selalu memeriksa kembali argumen ini: Gunakan "0" (atau biarkan kosong) untuk urutan menurun dan "1" untuk urutan menaik. Pastikan urutan sesuai dengan cara Anda ingin data diberi peringkat.

Keliru memilih fungsi

Saat bekerja dengan berbagai dataset, sangat penting memilih fungsi peringkat yang benar karena menggunakan fungsi yang salah akan memberi hasil yang tidak diinginkan.

Untuk menghindari masalah ini, gunakan RANK.EQ() untuk peringkat standar dan RANK.AVG() untuk skenario ketika penanganan seri memengaruhi hasil, seperti analisis statistik. 

Menggunakan rumus yang kedaluwarsa 

Seiring pertumbuhan data Anda, Anda harus memperbarui referensi rentang dalam rumus. Jika tidak, titik data baru tidak akan termasuk dalam peringkat Anda, dan Anda mungkin frustrasi karena tidak mendapatkan jawaban yang benar.

Karena itu, perbarui dan periksa rentang dalam rumus Anda secara berkala. Ini memastikan semua data berharga Anda dipertimbangkan untuk peringkat.

Penutup

Kini Anda tahu cara menggunakan fungsi seperti RANK(), RANK.EQ(), dan RANK.AVG() untuk menangani seri dan menyelesaikan berbagai masalah peringkat. Fungsi-fungsi ini membantu dalam berbagai skenario, seperti menganalisis nilai siswa atau mengurutkan angka penjualan.

Jika Anda tertarik untuk memperluas keterampilan Excel lebih jauh, saya sangat merekomendasikan melihat beberapa kursus dan sumber daya hebat di Datacamp: 

• Mulailah dengan kursus Introduction to Excel untuk membangun dasar, lalu lanjut ke Data Analysis in Excel dan Excel Fundamentals.  
• Pertimbangkan kursus Data Visualization in Excel untuk meningkatkan kemampuan memvisualisasikan data. 
• Kursus Introduction to PowerQuery in Excel sangat bermanfaat untuk manipulasi data tingkat lanjut. 
• Namun, untuk memformalkan keahlian Excel Anda, lihat panduan komprehensif ini tentang How to Earn a Microsoft Excel Certification.