Công thức chung
=COUNTIFS(criteria_range,criteria,values,">"&value)+1
Tóm lược
Để xếp hạng các mục trong danh sách bằng một hoặc nhiều tiêu chí, bạn có thể sử dụng hàm COUNTIFS. Trong ví dụ được hiển thị, công thức trong E5 là:
=COUNTIFS(groups,C5,scores,">"&D5)+1
trong đó "nhóm" là phạm vi được đặt tên C5: C14 và "điểm" là phạm vi được đặt tên D5: D14. Kết quả là một thứ hạng cho mỗi người trong nhóm của họ.
Lưu ý: mặc dù dữ liệu được sắp xếp theo nhóm trong ảnh chụp màn hình, công thức sẽ hoạt động tốt với dữ liệu không được sắp xếp.
Giải trình
Mặc dù Excel có hàm RANK, nhưng không có hàm RANKIF để thực hiện xếp hạng có điều kiện. Tuy nhiên, bạn có thể dễ dàng tạo một RANK có điều kiện bằng hàm COUNTIFS.
Hàm COUNTIFS có thể thực hiện đếm có điều kiện bằng cách sử dụng hai hoặc nhiều tiêu chí. Tiêu chí được nhập vào các cặp phạm vi / tiêu chí. Trong trường hợp này, tiêu chí đầu tiên giới hạn số lượng cho cùng một nhóm, sử dụng phạm vi được đặt tên "nhóm" (C5: C14):
=COUNTIFS(groups,C5) // returns 5
Tự nó, điều này sẽ trả về tổng số thành viên nhóm trong nhóm "A", là 5.
Tiêu chí thứ hai giới hạn số điểm chỉ lớn hơn "điểm hiện tại" từ D5:
=COUNTIFS(groups,C5,scores,">"&D5) // returns zero
Hai tiêu chí kết hợp với nhau để đếm các hàng có nhóm là A và điểm cao hơn. Đối với tên đầu tiên trong danh sách (Hannah), không có điểm nào cao hơn trong nhóm A, vì vậy COUNTIFS trả về số không. Trong hàng tiếp theo (Edward), có ba điểm trong nhóm A cao hơn 79, vì vậy COUNTIFS trả về 3. Và cứ tiếp tục như vậy.
Để có được thứ hạng thích hợp, chúng tôi chỉ cần thêm 1 vào số được trả về bởi COUNTIFS.
Đảo ngược thứ tự xếp hạng
Để đảo ngược thứ tự xếp hạng và xếp hạng theo thứ tự (tức là giá trị nhỏ nhất được xếp hạng # 1) chỉ cần sử dụng toán tử less than ():
=COUNTIFS(groups,C5,scores,"<"&D5)+1
Thay vì đếm điểm lớn hơn D5, phiên bản này sẽ đếm điểm nhỏ hơn giá trị trong D5, đảo ngược thứ tự xếp hạng một cách hiệu quả.
Trùng lặp
Giống như hàm RANK, công thức trên trang này sẽ gán các giá trị trùng lặp có cùng thứ hạng. Ví dụ: nếu một giá trị cụ thể được chỉ định xếp hạng là 3 và có hai trường hợp của giá trị trong dữ liệu đang được xếp hạng, thì cả hai trường hợp sẽ nhận được xếp hạng là 3 và xếp hạng tiếp theo được chỉ định sẽ là 5. Để bắt chước hành vi của hàm RANK.AVG, sẽ chỉ định xếp hạng trung bình là 3,5 trong trường hợp như vậy, bạn có thể tính toán "hệ số hiệu chỉnh" với công thức như sau:
=(COUNTIFS(groups,C5)+1-(COUNTIFS(group,C5,scores,">"&D5)+1)-(COUNTIFS(groups,C5,scores,"<"&D5)+1))/2
Kết quả từ công thức trên có thể được thêm vào thứ hạng ban đầu để có được thứ hạng trung bình. Khi một giá trị không có bản sao, đoạn mã trên trả về giá trị 0 và không có hiệu lực.
