Cách xếp hạng trong Excel bằng hàm RANK và biểu diễn bằng màu

Trong Excel, bạn có thể xé hạng các giá trị trong 1 cột, một hàng theo thứ tự tăng dần hoặc giảm dần bằng cách sử dụng hàm RANK. Bài viết ngày hôm nay, Tin Học Thành Luân sẽ hướng dẫn các bạn cách xếp hạng trong Excel bằng hàm RANK và tô màu tự động phần xếp hạng đó. Ngoài ra, mình sẽ bonus thêm đoạn mã VBA tự động xếp hạng giá trị theo hàng trong bảng tính.

Hàm RANK trong Excel là gì?

Hàm Rank hay hàm xếp hạng trong Excel là hàm trả về thứ hạng của một số trong một dãy các giá trị khác, thường là trong cột hoặc hàng. Nếu bạn cần sắp sếp danh sách, thì thứ hạng của số chính là vị trí của nó.

Thông tin thêm: Hàm RANK trong Excel đã được thay thế bằng một hoặc nhiều hàm mới với độ chính xác cao hơn và có tên gọi thể hiện rõ chức năng của nó. Đối với các phiên bản Excel vẫn có hàm RANK nhưng có thể trong tương lai hàm RANK sẽ không còn nữa. Nên bạn có thể làm quen với 2 hàm thay thế là hàm RANK.AVG và hàm RANK.EQ.

Cú pháp hàm RANK: =RANK(number, ref, [order])

Trong đó:

• number: Số mà bạn muốn tìm vị trí của nó
• ref: Một mảng hoặc tham chiếu tới một danh sách các số ( cột hoặc hàng), các giá trị không phải là số trong dãy sẽ được bỏ qua.
• [order]: có 2 tùy chọn, nếu để trống hoặc chọn 0 – Descending  là sắp xếp theo thứ tự giảm dần, ngược lại nếu chọn 1 – Ascending là sắp xếp theo thứ tự tăng dần.

Chú thích:

Các số trùng lặp trong một dãy hàm RANK sẽ sắp xếp cùng một thứ hạng. Tuy nhiên, các số trùng lặp này sẽ ảnh hưởng đến thứ hạng của các số sau đó. Ví dụ, trong một danh sách số nguyên sắp xếp theo thứ tự tăng dần, nếu số 10 xuất hiện hai lần và có thứ hạng là 5, thì số 11 sẽ có thứ hạng 7 (không có số nào có thứ hạng 6).

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

Chúng ta có 1 ví dụ đơn giản sau:

Yêu cầu xếp hạng các thí sinh dựa vào Điểm Trung Bình theo thứ tự giảm dần, dựa vào lý thuyết ở trên chúng ta sẽ có công thức như sau:

Công thức: =RANK(G7,$G$7:$G$11)

Giải thích:

• G7: ô chứa giá trị số cần mang đi xếp hạng
• $G$7:$G$11: Dãy số cần xếp hạng

Thành phần thứ 3 bỏ trống hoặc bạn có thể điền số 0 vào, Excel sẽ ngầm hiểu là bạn muốn sắp xếp theo tự tự giảm dần. Và kết quả sẽ như sau:

Để sắp xếp các số này theo thứ tự các bạn sẽ làm như sau:

Bước 1: Bôi đen vùng bảng cần sắp xếp theo thứ tự

Bước 2: Bấm chọn Sort & Filter ở góc phải trên cùng cửa sổ làm việc

Bước 3: Chọn FIlter

Bước 4: Bấm vào mũi tên quay xuống tại cột muốn sắp xếp chọn A-Z Sort Smallest to Largest (từ bé tới lớn) hoặc Z-A Sort Largest to Smallest (từ lớn tới bé).

Kết quả sau khi làm các bước như trên:

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

Đối với các số bị trùng lặp trong dãy mà bạn vẫn muốn xếp hạng không để số thứ tự bị trùng thì các bạn sẽ làm theo các bước như dưới đây. Lưu ý cách này chỉ áp dụng với cách xếp hạng theo thứ tự giảm dần.

Bước 1: Đếm tổng số trong dãy cần sắp xếp

Bước 2: Sử dụng hàm RANK để sắp xếp theo thứ tự giảm dần

Bước 3: Đếm số trùng lặp trong dãy

Công thức: =COUNT($G$7:$G$11)-(RANK(G7,$G$7:$G$11)+COUNTIF($G$7:G7,G7)) + 2

Giải thích:

• COUNT($G$7:$G$11): Đếm tổng số hạng trong dãy
• RANK(G7,$G$7:$G$11): Xếp hạng giá trị số đó trong dãy
• COUNTIF($G$7:G7,G7): Đếm số bị trùng lặp trong dãy
• 2: Số cộng vào khi bù trừ phần trùng lặp trên

Cách biểu diễn màu tự động bằng Conditional formating

Khi mà bạn nhìn những con số nhỏ rất khó nhìn thì biểu diễn xếp hạng bằng định dạng có điều kiện là một điều rất thú vị. Khi nhìn vào bạn sẽ thấy ngay được số nào là lớn nhất hoặc nhỏ nhất, biểu diễn bằng màu theo xếp hạng ví dụ như màu xanh sẽ từ màu nhạt (màu trắng) tới màu xanh tùy thuộc vào dữ liệu trong dãy có bao nhiêu.

Chúng ta sẽ đi tìm hiểu các bước bằng cách sử dụng lại ví dụ phía trên sau khi đã sắp xếp thứ tự từ bé tới lớn. Đầu tiên, các bạn hãy bôi đen chọn dãy số thứ tự xếp hạng từ 1 tới 5 kia sau đó làm các bước như sau:

Bước 1: Bấm chọn Conditional Formatting

Bước 2: Chọn Color Scales

Bước 3: Chọn 1 trong số những mẫu sẵn hoặc chọn More Rules để cấu hình nâng cao

Phần định dạng có điều kiện nâng cao này, bạn chỉ cần chọn color ở mục Minimum là màu đại diện cho số thứ tự đầu và ở mục Maximum màu đại diện cho số thứ tự cao nhất.

Viết mã VBA tự động xếp hạng trong Excel

Ngoài những cách tạo xếp hạng trong Excel như trên thì mình sẽ thêm một cách viết code bằng VBA Excel rất hay. Việc của bạn là tải file Excel mẫu tham khảo cách viết code. Phần này dành cho các bạn biết một chút về mã VBA để có thể chỉnh sửa được theo file Excel của các bạn đang làm. Đối với các bạn không biết về code có thể để lại bình luận ở trên bài viết này, mình sẽ hỗ trợ bạn viết code.

Đoạn code như sau:

Sub ranking()
Dim sh As Worksheet
Set sh = Sheet1
Dim lr_DTB As Long
lr_DTB = sh.Range(“G1000”).End(xlUp).Row
sh.AutoFilter.Sort.SortFields.Clear
sh.AutoFilter.Sort.SortFields.Add2 Key:=Range _
(“G5:G6”), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With sh.AutoFilter.Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

i = 0
For i = 7 To lr_DTB
sh.Cells(i, 8) = i – 6
Next i
End Sub

Link: Tải file xếp hạng bằng mã VBA Excel

Như vậy, với những cách trên và phần mở rộng mình tin chắc rằng các bạn đã có thể hiểu hơn về cách xếp hạng trong Excel rồi. Các bạn nên chia nhỏ công thức ra rồi mới ghép vào bởi công thức ghép vào khá phức tạp nếu bạn không nắm được tổng quát hết. Do vậy hãy đi từng bước trước khi hoàn thành công thức cuối. Bạn có thể học thêm nhiều hàm hơn tại mục Excel cơ bản trên trang web của mình và đừng quên để lại một bình luận hoặc chia sẻ bài viết cho các bạn của bạn có thể đọc được.