=RANK.EQ(B2,$B$2:$B$7) trả về thứ hạng của B2 trong các giá trị ở B2:B7, giá trị lớn nhất được hạng 1. Để xếp giá trị nhỏ nhất lên đầu, thêm 1 làm đối số thứ ba: =RANK.EQ(B2,$B$2:$B$7,1).
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Name | Sales | Rank (high = 1) | Rank (low = 1) |
| 2 | Ana | 4200 | 3 | 4 |
| 3 | Ben | 3100 | 5 | 2 |
| 4 | Cleo | 5600 | 1 | 6 |
| 5 | Dan | 2800 | 6 | 1 |
| 6 | Eve | 4900 | 2 | 5 |
| 7 | Finn | 3700 | 4 | 3 |
Cleo có doanh số cao nhất, nên được hạng 1 ở cột C và hạng 6 ở cột D. Đổi doanh số của Dan thành 6000 và mọi thứ hạng đều cập nhật.
Cú pháp RANK.EQ
=RANK.EQ(number, ref, [order])
number: giá trị cần xếp hạng, thường là ô trên cùng hàng.ref: danh sách dùng để xếp hạng. Hãy dùng tham chiếu tuyệt đối ($B$2:$B$7) để vùng này giữ nguyên ở mọi hàng khi bạn kéo công thức xuống.order: bỏ trống hoặc dùng0để xếp giảm dần (lớn nhất là 1), dùng1để xếp tăng dần (nhỏ nhất là 1).
Văn bản và ô trống trong ref bị bỏ qua. Nếu number hoàn toàn không có trong ref, RANK.EQ trả về #N/A.
RANK.EQ và RANK.AVG có từ Excel 2010. Hàm RANK cũ vẫn dùng được và cho kết quả giống RANK.EQ; Google Sheets có cả ba hàm.
Giá trị bằng nhau: RANK.EQ và RANK.AVG
Khi các giá trị bằng nhau, RANK.EQ cho chúng cùng một hạng và bỏ qua các hạng mà chúng chiếm chỗ, giống kết quả thi đấu thể thao: hai người đồng hạng nhì, rồi đến hạng tư. RANK.AVG cho mỗi giá trị bằng nhau hạng là trung bình của các vị trí chúng cùng chiếm.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Name | Score | RANK.EQ | RANK.AVG | Unique rank |
| 2 | Ana | 95 | 1 | 1 | 1 |
| 3 | Ben | 88 | 2 | 3 | 2 |
| 4 | Cleo | 88 | 2 | 3 | 3 |
| 5 | Dan | 76 | 5 | 5 | 5 |
| 6 | Eve | 88 | 2 | 3 | 4 |
| 7 | Finn | 64 | 6 | 6 | 6 |
Ben, Cleo và Eve cùng được 88 điểm. RANK.EQ cho cả ba hạng 2 và Dan hạng 5. RANK.AVG cho họ hạng 3, là trung bình của các vị trí 2, 3 và 4.
Cột E phân định các giá trị bằng nhau theo thứ tự trong danh sách: COUNTIF($B$2:B2,B2) đếm số lần điểm này đã xuất hiện, từ trên cùng xuống đến hàng hiện tại (khi kéo, chỉ đầu cuối của vùng dịch chuyển). Điểm 88 đầu tiên cộng 0, lần thứ hai cộng 1, lần thứ ba cộng 2, nên mỗi hạng từ 1 đến 6 xuất hiện đúng một lần. Đây là cách nên dùng khi thứ hạng được đưa vào một phép tra cứu như "ai đứng thứ 3", vì tra cứu cần mỗi hạng là duy nhất.
Xếp hạng trong từng nhóm
Excel không có RANKIF. Để xếp hạng mỗi nhân viên bán hàng chỉ so với những người cùng khu vực, hãy đếm xem có bao nhiêu người trong khu vực đó bán nhiều hơn rồi cộng 1:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Name | Region | Sales | Rank in region |
| 2 | Ana | North | 4200 | 3 |
| 3 | Ben | South | 3100 | 3 |
| 4 | Cleo | North | 5600 | 1 |
| 5 | Dan | South | 2800 | 4 |
| 6 | Eve | North | 4900 | 2 |
| 7 | Finn | South | 3700 | 1 |
| 8 | Gia | North | 3900 | 4 |
| 9 | Hal | South | 3300 | 2 |
Cleo dẫn đầu North và Finn dẫn đầu South. Đổi khu vực của Ana thành South: các hạng của North dồn lên lấp chỗ trống và South có thêm người thứ năm. Để xếp hạng tăng dần trong nhóm, dùng "<"&C2 thay thế. Giá trị bằng nhau được xử lý như RANK.EQ: chúng có chung hạng. Xem thêm về cách đếm với nhiều điều kiện ở trang COUNTIFS.
Hiện top 3 theo thứ hạng
Cột thứ hạng trả lời câu hỏi "người này đứng thứ mấy"; để trả lời "ai đứng thứ nhất", hãy tra cứu theo thứ hạng. Với các hạng không trùng ở cột C, =XLOOKUP(1,C2:C7,A2:A7) trả về tên người được hạng 1 (trong Excel 2019 trở về trước, dùng =INDEX(A2:A7,MATCH(1,C2:C7,0))). Không cần cột thứ hạng, =LARGE(B2:B7,2) trả về trực tiếp giá trị lớn thứ hai, còn =SORTBY(A2:B7,B2:B7,-1) liệt kê cả bảng từ cao xuống thấp (Excel 2021 hoặc Microsoft 365; xem SORT và SORTBY).
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Name | Sales | Rank | Place | Name | |
| 2 | Ana | 4200 | 3 | 1 | Cleo | |
| 3 | Ben | 3100 | 5 | 2 | Eve | |
| 4 | Cleo | 5600 | 1 | 3 | Ana | |
| 5 | Dan | 2800 | 6 | |||
| 6 | Eve | 4900 | 2 | |||
| 7 | Finn | 3700 | 4 |
Cột C là hạng không trùng từ phần giá trị bằng nhau, nên giá trị bằng nhau không bao giờ làm trống một vị trí. F2 đến F4 liệt kê Cleo, Eve và Ana. Đổi doanh số của Ana thành 6000, cô ấy sẽ lên đầu.
Thử ngay: người chạy nhanh nhất đứng đầu
| A | B | C | |
|---|---|---|---|
| 1 | Runner | Time | Rank |
| 2 | Ana | 58.4 | |
| 3 | Ben | 55.1 | |
| 4 | Cleo | 61.9 | |
| 5 | Dan | 53.7 | |
| 6 | Eve | 57.3 | |
| 7 | Finn | 60.2 |
Đến lượt bạn: Ở C2, xếp hạng thời gian của Ana ở B2 trong mọi thời gian ở B2:B7, sao cho thời gian nhanh nhất (thấp nhất) được hạng 1. Công thức được kéo xuống đến C7, nên hãy dùng tham chiếu tuyệt đối cho danh sách.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Student | Class | Score | Rank in class |
| 2 | Ana | Red | 81 | |
| 3 | Ben | Red | 92 | |
| 4 | Cleo | Blue | 77 | |
| 5 | Dan | Red | 69 | |
| 6 | Eve | Blue | 95 | |
| 7 | Finn | Red | 85 | |
| 8 | Gia | Blue | 88 |
Đến lượt bạn: Ở D2, xếp hạng điểm của Ana chỉ so với các học sinh cùng lớp (cột B), điểm cao nhất được hạng 1. Công thức được kéo xuống đến D8.
Lỗi thường gặp: vùng tham chiếu tương đối
Không có dấu $, vùng tham chiếu dịch xuống một hàng mỗi khi kéo công thức, nên mỗi hàng được xếp hạng trong một danh sách ngày càng ngắn, bắt đầu từ chính hàng đó. Các thứ hạng trông có vẻ hợp lý nhưng lại sai:
| A | B | C | |
|---|---|---|---|
| 1 | Name | Sales | Rank |
| 2 | Ana | 4200 | 3 |
| 3 | Ben | 3100 | 4 |
| 4 | Cleo | 5600 | 1 |
| 5 | Dan | 2800 | 3 |
| 6 | Eve | 4900 | 1 |
| 7 | Finn | 3700 | 1 |
Bấm vào C4: vùng của nó là B4:B9, nên Cleo chỉ được so với những người từ hàng của cô ấy trở xuống. Eve hiện hạng 1 dù Cleo bán nhiều hơn, còn Finn hiện hạng 1 vì anh ấy chỉ được so với chính mình. Đổi C2 thành =RANK.EQ(B2,$B$2:$B$7) và cả cột tự sửa lại. Nhấn F4 khi con trỏ đang ở trong vùng để thêm dấu $ (trên Mac, Cmd+T hoặc Fn+F4); trang tham chiếu tuyệt đối giải thích lý do.
Câu hỏi thường gặp
Làm sao để xếp hạng các số trong Excel?
Dùng =RANK.EQ(B2,$B$2:$B$7) rồi kéo công thức xuống. Giá trị lớn nhất được hạng 1. Giữ dấu $ trên vùng để vùng không bị dịch khi kéo.
Làm sao để xếp hạng từ nhỏ đến lớn trong Excel?
Thêm 1 làm đối số thứ ba: =RANK.EQ(B2,$B$2:$B$7,1). Dùng cách này cho thời gian chạy, giá bán hay bất cứ thứ gì mà giá trị thấp nhất phải đứng đầu.
RANK.EQ và RANK.AVG khác nhau thế nào?
Chúng chỉ khác nhau khi có giá trị bằng nhau. Nếu hai giá trị cùng đứng thứ hai, RANK.EQ cho cả hai hạng 2 và giá trị kế tiếp hạng 4. RANK.AVG cho cả hai hạng 2,5, là trung bình của vị trí 2 và 3.
Làm sao để xếp hạng không trùng trong Excel?
Cộng thêm số lần giá trị đó đã xuất hiện: =RANK.EQ(B2,$B$2:$B$7)+COUNTIF($B$2:B2,B2)-1. Giá trị đầu tiên trong hai giá trị bằng nhau giữ nguyên hạng, giá trị thứ hai nhận hạng kế tiếp.
Làm sao để xếp hạng trong từng nhóm trong Excel?
Đếm số giá trị lớn hơn trong cùng nhóm rồi cộng 1: =COUNTIFS($A$2:$A$9,A2,$C$2:$C$9,">"&C2)+1. Excel không có hàm RANKIF.
