Hàm RANK trong Excel: xếp hạng điểm, doanh số có điều kiện
Cách dùng hàm RANK, RANK.EQ, RANK.AVG để xếp hạng điểm thi, doanh số; xếp hạng theo từng lớp bằng COUNTIFS và cách xếp hạng không nhảy số khi bằng điểm.
Hàm RANK cho biết một số đứng thứ mấy trong danh sách: học sinh nào đứng nhất lớp, nhân viên nào có doanh số cao thứ ba. Công thức thường dùng là =RANK.EQ(C2,$C$2:$C$45,0), với 0 nghĩa là số lớn nhất xếp hạng 1.
Cú pháp RANK, RANK.EQ, RANK.AVG
=RANK(number, ref, [order])
=RANK.EQ(number, ref, [order])
=RANK.AVG(number, ref, [order])| Đối số | Ý nghĩa |
|---|---|
number | Giá trị cần xếp hạng, ví dụ điểm ở C2 |
ref | Toàn bộ danh sách để so sánh; luôn cố định bằng F4: $C$2:$C$45 |
order | 0 hoặc bỏ trống: giảm dần (lớn nhất hạng 1, dùng cho điểm, doanh số). 1: tăng dần (nhỏ nhất hạng 1, dùng cho thời gian chạy, số ngày nghỉ) |
RANK và RANK.EQ cho kết quả giống nhau; RANK được giữ lại để tương thích Excel cũ. RANK.AVG khác ở cách xử lý bằng điểm. Máy đặt vùng Việt Nam dùng ;.
Bằng điểm thì xếp hạng thế nào?
Ví dụ bốn bạn có điểm 9,5; 9,0; 9,0; 8,5:
| Điểm | RANK.EQ | RANK.AVG | Không nhảy số |
|---|---|---|---|
| 9,5 | 1 | 1 | 1 |
| 9,0 | 2 | 2,5 | 2 |
| 9,0 | 2 | 2,5 | 2 |
| 8,5 | 4 | 4 | 3 |
RANK.EQ là kiểu xếp hạng thi đấu quen thuộc: hai người đồng hạng 2 thì không có hạng 3. RANK.AVG lấy trung bình các hạng bị chiếm, ít dùng trong văn phòng nhưng hay gặp trong thống kê.
Ví dụ thực tế
1. Xếp hạng điểm trung bình cả lớp
=RANK.EQ(G2, $G$2:$G$45, 0)2. Xếp hạng doanh số nhân viên tháng 10
=RANK.EQ(D2, $D$2:$D$30)Ghép thêm chữ cho đẹp: ="Hạng "&RANK.EQ(D2,$D$2:$D$30)&"/"&COUNT($D$2:$D$30) → “Hạng 3/29”.
3. Xếp hạng tốc độ (thấp nhất là nhất)
=RANK.EQ(E2, $E$2:$E$60, 1)Xếp hạng có điều kiện theo lớp, theo chi nhánh
RANK không có tham số điều kiện. Khi danh sách gồm nhiều lớp (cột A) và cần xếp hạng trong từng lớp theo điểm (cột C), dùng COUNTIFS đếm số bạn cùng lớp có điểm cao hơn rồi cộng 1:
=COUNTIFS($A$2:$A$300, A2, $C$2:$C$300, ">"&C2)+1Kết quả giống RANK.EQ nhưng chỉ so trong cùng lớp. Đổi ">" thành "<" để xếp tăng dần. Thêm cặp điều kiện nữa nếu cần xếp theo lớp và giới tính. Cách dùng COUNTIFS chi tiết xem hàm COUNTIF, COUNTIFS.
Xếp hạng không nhảy số (dense rank)
Một số trường hoặc cuộc thi muốn sau hai bạn đồng hạng 2 là hạng 3, không bỏ qua số. Công thức dùng được trên mọi phiên bản:
=SUMPRODUCT(($C$2:$C$45>C2)/COUNTIF($C$2:$C$45, $C$2:$C$45))+1Công thức đếm số mức điểm khác nhau cao hơn C2. Vùng không được có ô trống (sẽ báo #DIV/0!). Trên Microsoft 365 có thể viết dễ hiểu hơn:
=XMATCH(C2, SORT(UNIQUE($C$2:$C$45), , -1))Lỗi hay gặp
- Hạng bị trùng lung tung khi kéo xuống: quên
$ở vùngref, vùng so sánh bị trượt. #N/A: ô cần xếp hạng không phải số hoặc không nằm trong vùngref.- Hai điểm trông giống nhau nhưng khác hạng: điểm có phần lẻ ẩn (8,4999 hiển thị 8,5). Làm tròn trước bằng hàm ROUND.
- Ô điểm trống bị bỏ qua, không được xếp hạng; ô ghi 0 thì vẫn được xếp hạng cuối.
Google Sheets
Google Sheets hỗ trợ RANK, RANK.EQ, RANK.AVG và công thức COUNTIFS ở trên. Sau khi có cột hạng, bạn có thể lọc hoặc sắp xếp để in danh sách khen thưởng.