Cách xếp hạng không ngắt quãng bằng hàm RANK và COUNTIF trong Excel

Thông thường, khi muốn xếp hạng trong Excel, chúng ta sẽ sử dụng hàm RANK. Tuy nhiên, nhược điểm của hàm RANK là những giá trị giống nhau sẽ được sắp xếp đồng hạng với nhau, làm cho việc sắp xếp diễn ra không liên tục, bị ngắt quãng. Thật tốt vì chúng ta hoàn toàn có thể khắc phục tình trạng này bằng cách kết hợp hàm RANK với hàm COUNTIF. Hãy cùng Gitiho tìm hiểu cách làm trong bài viết này nhé!

Cách xếp hạng bằng hàm RANK trong Excel

Để xếp hạng trong Excel, chúng ta sử dụng hàm RANK với cấu trúc:

RANK(number,ref,[order])

Trong đó: 

number: Giá trị mà bạn muốn xếp hạng. Đây là thành phần bắt buộc trong hàm RANK

ref:  Mảng hoặc tham chiếu tới danh sách các số để xếp hạng giá trị đã chọn. Những giá trị không phải giá trị số trong tham chiếu sẽ được bỏ qua. Đây là thành phần bắt buộc trong hàm RANK. Lưu ý: Cần khóa mảng được chọn lại bằng phím F4

order: Cách xếp hạng số. Đây là thành phần không bắt buộc trong hàm RANK. Có 2 lựa chọn cách xếp hạ;ng:

• Nếu nhập số 0 hoặc không nhập gì: Xếp hạng thành dang sách theo thứ tự tăng dần
• Nếu nhấp số 1 hoặc bất cứ giá trị nào khác 0: Xếp hạng thành danh sách theo thứ tự giảm dần.

Tuy nhiên, bạn có thể thấy, khi xếp hạng theo cách này thì cách giá trị giống nhau sẽ được sắp xếp đồng hạng và bị sắp xếp ngắt quãng, không liên tục. Ví dụ: Điểm trung bình là 8.5 được xuất hiện 2 lần, và đều được xếp hạng ở vị trí thứ 2. Tuy nhiên, ta thấy trong cột xếp hạng chỉ có các vị trí 1-2-4-5, không có vị trí số 3. Đây chính là bị xếp hạng ngắt quãng. Để khắc phục tình trạng này, ta cần kết hợp hàm RANK với hàm COUNTIF.

Cách xếp hạng bằng hàm RANK và hàm COUNTIF trong Excel

Cách xếp hạng theo thứ tự tăng dần bằng hàm RANK và hàm COUNTIF

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

Áp dụng vào ví dụ, ta có:

Lưu ý khi đếm bằng hàm COUNTIF: Vùng dữ liệu đếm là C3:C3 chỉ cần cố định điểm đầu, không cần cố định điểm cuối

Nhìn vào kết quả trả về của hàm COUNTIF, ta có thể nhận biết:

• Kết quả trả về là 1: Là lần xuất hiện đầu tiên
• Kết quả trả về lớn hơn 1: Là giá trị đã xuất hiện trước đó, và tại vị trí > 1 sẽ xuất hiện tới lần thứ mấy

Kết hợp hàm RANK và hàm COUNTIF

Để xếp hạng liên tục không ngắt quãng theo thứ tự tăng dần trong Excel, ta sẽ kết hợp hàm RANK và hàm COUNTIF thành công thức:

RANK(number,ref,[order])+COUNTIF(range,criteria)-1

Xem thêm: Hướng dẫn cách sử dụng hàm COUNTIF để đếm theo điều kiện trong Excel

Cách xếp hạng theo thứ tự giảm dần bằng hàm RANK và hàm COUNTIF

Cách làm tương tự với xếp hạng theo thứ tự tăng dần, tuy nhiên có một chút thay đổi. Chúng ta thực hiện theo các bước sau:

Sử dụng hàm COUNT để đếm tổng số hạng cần xếp

Sử dụng hàm RANK để xếp hạng theo thứ tự tăng dần

Sử dụng hàm COUNTIF để tìm các giá trị trùng

Ta có công thức:

Kết quả cuối cùng = COUNT - (RANK + COUNTIF) + 2

Áp dụng vào ví dụ, ta có kết quả như sau:

Tổng kết

Như vậy. bằng việc kết hợp thêm hàm COUNT và COUNTIF vào công thức đã giúp tối ưu hóa việc xếp hạng trong Excel bằng hàm RANK, loại bỏ được lỗi xếp hạng ngắt quãng, không liên tục. Nếu bạn chưa quen viết các công thức kết hợp nhau thì bạn có thể tách làm riêng các hàm, kiểm tra đúng rồi mới kết hợp các hàm với nhau. Chúc bạn áp dụng thành công!