Để xếp hạng trong Excel chúng ta thường sử dụng hàm RANK. Nhưng nhược điểm của hàm RANK là những giá trị giống nhau sẽ được xếp đồng hạng, gây ra hiện tượng xếp hạng không liên tục, bị nhảy bậc, ngắt quãng. Vậy làm thế nào để khắc phục điều này? Hãy cùng Học Excel Online tìm hiểu cách dùng hàm RANK và hàm COUNTIF để xếp hạng liên tục không nhảy bậc trong Excel.

Cách xếp hạng theo thứ tự tăng dần liên tục không ngắt quãng

Trong ví dụ về xếp hạng theo thứ tự tăng dần với hàm RANK, chúng ta thấy:

Trường hợp những người có cùng điểm TB là 8.1 đều xếp thứ 2. Tiếp theo là thứ 4, bị ngắt quãng ở thứ 3. Lý do là có 2 người xếp thứ 2, sẽ không còn ai xếp vị trí thứ 3 nữa.

Vậy làm thế nào để trong bảng xếp hạng trên không bị ngắt quãng bởi các điểm giống nhau?

Nếu như không quy định tiêu chí cụ thể nào cho việc này, chúng ta có thể quy ước thứ hạng được sắp xếp theo thứ tự từ trên xuống, tức là ai có điểm giống mà xếp ở trên thì sẽ được thứ hạng nhỏ hơn.

Sử dụng hàm COUNTIF để đếm số lần xuất hiện của các điểm số:

Khi đếm bằng hàm COUNTIF cần chú ý:

• Vùng cần đếm: $B$2:B2 tức là đếm bắt đầu từ vùng B2, chỉ cố định điểm đầu, không cố định điểm cuối
• Giá trị cần đếm: tại chính ô B2

Với cách đếm này, chúng ta sẽ có:

• Giá trị = 1 tức là xuất hiện lần đầu tiên
• Giá trị > 1 tức là đã xuất hiện trước đó rồi, và tại vị trí >1 sẽ là xuất hiện tới lần thứ mấy

Như vậy nếu lấy giá trị Xếp hạng bởi hàm RANK + COUNTIF – 1 chúng ta sẽ có những thứ hạng liên tục không bị ngắt quãng như sau:

Cách viết trực tiếp hàm RANK và hàm COUNTIF trong cùng 1 công thức:

=RANK(B2,$B$2:$B$8)+COUNTIF($B$2:B2,B2)-1

Lưu ý:

• Cách này chỉ áp dụng cho xếp hạng theo thứ tự tăng dần.
• Khi có cùng thứ hạng thì ai viết trước sẽ được ưu tiên xếp thứ tự nhỏ hơn.

Cách xếp hạng theo thứ tự giảm dần liên tục không ngắt quãng

Tương tự với cách xếp hạng theo thứ tự giảm dần, chúng ta cũng có thể gặp phải trường hợp hàm RANK xếp đồng hạng với những giá trị bằng nhau. Nhưng trong cách làm này, logic cần thay đổi như sau:

• Bước 1: Đếm tổng số hạng cần xếp với hàm COUNT (đếm giá trị dạng số)
• Bước 2: Sử dụng hàm Rank để xếp hạng theo thứ tự tăng dần
• Bước 3: Sử dụng hàm COUNTIF giống như ở cách trên để phát hiện các vị trí có giá trị trùng
• Bước 4: Kết quả xếp hạng = Bước 1 – (Bước 2 + Bước 3) + 2

Kết quả như sau:

Công thức sử dụng là:

Bước 1: D2=COUNT($B$2:$B$8)

Bước 2 và 3: E2=RANK(B2,$B$2:$B$8)+COUNTIF($B$2:B2,B2)

Bước 4: F2=D2-E2+2

Nếu lồng các hàm vào nhau chúng ta có thể sử dụng 1 công thức duy nhất như sau:

Kết quả xếp hạng  =COUNT($B$2:$B$8) – (RANK(B2,$B$2:$B$8)+COUNTIF($B$2:B2,B2)) + 2

Lưu ý:

• Cách này chỉ áp dụng cho xếp hạng theo thứ tự giảm dần.
• Khi có cùng thứ hạng thì ai viết sau sẽ được ưu tiên xếp thứ tự nhỏ hơn.

Kết luận

Như vậy với cách kết hợp linh hoạt giữa hàm Rank và hàm COUNT, COUNTIF chúng ta đã có thể thay đổi cách xếp hạng cho hàm RANK trở lên linh hoạt hơn, liên tục không bị ngắt quãng, nhảy bậc.

Để viết công thức lồng ghép phức tạp trong Excel, chúng ta nên chia nhỏ vấn đề ra thành nhiều bước. Mỗi bước là 1 hàm riêng. Sau khi ra kết quả đúng, chúng ta mới thay các tham chiếu bằng các hàm đã sử dụng trong 1 công thức duy nhất. Không nên viết ngay 1 công thức lồng ghép phức tạp sẽ khó viết và dễ sai sót.

Ngoài ra các bạn có thể tham khảo thêm về cách xếp hạng theo điều kiện qua các bài viết sau:

Xếp hạng theo điều kiện trong excel – Phần 1

Xếp hạng theo điều kiện trong excel – Phần 2

Xếp hạng theo điều kiện trong excel – Phần 3