Formula umum
=COUNTIFS(criteria_range,criteria,values,">"&value)+1
Ringkasan
Untuk memberi peringkat item dalam daftar menggunakan satu atau beberapa kriteria, Anda dapat menggunakan fungsi COUNTIFS. Dalam contoh yang ditampilkan, rumus dalam E5 adalah:
=COUNTIFS(groups,C5,scores,">"&D5)+1
di mana "grup" adalah rentang bernama C5: C14, dan "skor" adalah rentang bernama D5: D14. Hasilnya adalah peringkat untuk setiap orang dalam kelompoknya sendiri.
Catatan: meskipun data diurutkan berdasarkan grup di tangkapan layar, rumus akan berfungsi dengan baik dengan data yang tidak diurutkan.
Penjelasan
Meskipun Excel memiliki fungsi RANK, tidak ada fungsi RANKIF untuk melakukan peringkat bersyarat. Namun, Anda dapat dengan mudah membuat RANK bersyarat dengan fungsi COUNTIFS.
Fungsi COUNTIFS dapat melakukan penghitungan bersyarat menggunakan dua kriteria atau lebih. Kriteria dimasukkan dalam pasangan rentang / kriteria. Dalam kasus ini, kriteria pertama membatasi hitungan ke grup yang sama, menggunakan rentang bernama "grup" (C5: C14):
=COUNTIFS(groups,C5) // returns 5
Dengan sendirinya, ini akan mengembalikan total anggota grup di grup "A", yaitu 5.
Kriteria kedua membatasi penghitungan menjadi hanya skor yang lebih besar dari "skor saat ini" dari D5:
=COUNTIFS(groups,C5,scores,">"&D5) // returns zero
Kedua kriteria bekerja sama untuk menghitung baris yang kelompoknya A dan skornya lebih tinggi. Untuk nama depan dalam daftar (Hannah), tidak ada skor yang lebih tinggi di grup A, jadi COUNTIFS mengembalikan nol. Di baris berikutnya (Edward), ada tiga skor di grup A lebih tinggi dari 79, jadi COUNTIFS mengembalikan 3. Dan seterusnya.
Untuk mendapatkan peringkat yang tepat, kita cukup menambahkan 1 ke angka yang dikembalikan oleh COUNTIFS.
Membalik urutan peringkat
Untuk membalik urutan peringkat dan peringkat dalam urutan (yaitu nilai terkecil peringkat # 1) cukup gunakan kurang dari operator ():
=COUNTIFS(groups,C5,scores,"<"&D5)+1
Alih-alih menghitung skor yang lebih besar dari D5, versi ini akan menghitung skor yang lebih kecil dari nilai di D5, secara efektif membalik urutan peringkat.
Duplikat
Seperti fungsi RANK, rumus di halaman ini akan menetapkan nilai duplikat dengan peringkat yang sama. Misalnya, jika nilai tertentu diberi peringkat 3, dan ada dua contoh nilai dalam data yang diberi peringkat, kedua contoh akan menerima peringkat 3, dan peringkat berikutnya yang ditetapkan adalah 5. Untuk meniru perilaku dari fungsi RANK.AVG, yang akan menetapkan peringkat rata-rata 3,5 dalam kasus seperti itu, Anda dapat menghitung "faktor koreksi" dengan rumus seperti ini:
=(COUNTIFS(groups,C5)+1-(COUNTIFS(group,C5,scores,">"&D5)+1)-(COUNTIFS(groups,C5,scores,"<"&D5)+1))/2
Hasil dari rumus diatas dapat ditambahkan ke rank asli untuk mendapatkan ranking rata-rata. Jika suatu nilai tidak memiliki duplikat, kode di atas mengembalikan nol dan tidak berpengaruh.
