Formula generik
=COUNTIFS(criteria_range,criteria,values,">"&value)+1
Ringkasan
Untuk meletakkan item dalam senarai menggunakan satu atau lebih kriteria, anda boleh menggunakan fungsi COUNTIFS. Dalam contoh yang ditunjukkan, formula di E5 adalah:
=COUNTIFS(groups,C5,scores,">"&D5)+1
di mana "kumpulan" adalah julat bernama C5: C14, dan "skor" adalah julat bernama D5: D14. Hasilnya adalah peringkat untuk setiap orang dalam kumpulan mereka sendiri.
Catatan: walaupun data disusun mengikut kumpulan dalam tangkapan skrin, formula akan berfungsi dengan baik dengan data yang tidak disusun.
Penjelasan
Walaupun Excel mempunyai fungsi RANK, tidak ada fungsi RANKIF untuk melakukan peringkat bersyarat. Walau bagaimanapun, anda boleh membuat RANK bersyarat dengan mudah dengan fungsi COUNTIFS.
Fungsi COUNTIFS dapat melakukan kiraan bersyarat menggunakan dua atau lebih kriteria. Kriteria dimasukkan dalam pasangan julat / kriteria. Dalam kes ini, kriteria pertama mengehadkan kiraan kepada kumpulan yang sama, menggunakan julat "kumpulan" yang dinamakan (C5: C14):
=COUNTIFS(groups,C5) // returns 5
Dengan sendirinya, ini akan mengembalikan jumlah ahli kumpulan dalam kumpulan "A", iaitu 5 orang.
Kriteria kedua mengehadkan jumlah hanya skor lebih besar daripada "skor semasa" dari D5:
=COUNTIFS(groups,C5,scores,">"&D5) // returns zero
Kedua-dua kriteria berfungsi bersama untuk mengira baris di mana kumpulan A dan skornya lebih tinggi. Untuk nama pertama dalam senarai (Hannah), tidak ada skor yang lebih tinggi dalam kumpulan A, jadi COUNTIFS mengembalikan sifar. Di baris seterusnya (Edward), terdapat tiga markah dalam kumpulan A yang lebih tinggi daripada 79, jadi COUNTIFS mengembalikan 3. Dan seterusnya.
Untuk mendapatkan pangkat yang betul, kita hanya menambah 1 pada nombor yang dikembalikan oleh COUNTIFS.
Membalikkan susunan pangkat
Untuk membalikkan susunan kedudukan dan kedudukan mengikut urutan (iaitu nilai terkecil berada di kedudukan # 1) hanya gunakan operator yang kurang dari ():
=COUNTIFS(groups,C5,scores,"<"&D5)+1
Daripada menghitung skor lebih besar daripada D5, versi ini akan menghitung skor kurang daripada nilai di D5, dengan berkesan membalikkan susunan kedudukan.
Pendua
Seperti fungsi RANK, formula di halaman ini akan memberikan nilai pendua pada peringkat yang sama. Sebagai contoh, jika nilai tertentu diberikan peringkat 3, dan ada dua contoh nilai dalam data yang diperingkat, kedua-dua contoh akan menerima peringkat 3, dan peringkat berikutnya yang diberikan adalah 5. Untuk meniru tingkah laku dari fungsi RANK.AVG, yang akan memberikan peringkat purata 3.5 dalam kes sedemikian, anda boleh mengira "faktor pembetulan" dengan formula seperti ini:
=(COUNTIFS(groups,C5)+1-(COUNTIFS(group,C5,scores,">"&D5)+1)-(COUNTIFS(groups,C5,scores,"<"&D5)+1))/2
Hasil dari formula di atas dapat ditambahkan ke peringkat asal untuk mendapatkan peringkat rata-rata. Apabila nilai tidak mempunyai pendua, kod di atas mengembalikan sifar dan tidak memberi kesan.
