Bạn cần biết nhân viên nào đứng thứ mấy về doanh số, nhưng không muốn sắp xếp lại bảng. Hàm RANK trong Excel cho biết một số đứng thứ mấy trong danh sách, và kết quả tự đổi khi số liệu thay đổi. Bài này đi từ cách xếp hạng cơ bản bằng hàm RANK đến các trường hợp cần công thức khác, như đồng hạng và xếp hạng trong từng phòng.

Ảnh trong bài được xuất từ Excel 365. Dòng chữ xanh ở cuối các ảnh có công thức ghi công thức của một ô để bạn đối chiếu, nên bạn không cần gõ dòng này. Các bảng trong ảnh được tô màu cho dễ đọc, và Excel của bạn có thể hiện 4,5 hoặc 4.5 tùy cài đặt vùng. Nếu Excel của bạn báo lỗi khi gõ công thức có dấu phẩy, bạn thay dấu phẩy ngăn tham số bằng dấu chấm phẩy.

Hàm RANK có cú pháp như thế nào?

=RANK(số cần xếp hạng, danh sách, [thứ tự])

Tham số

Tham số này là gì

Số cần xếp hạng

Đây là ô chứa số bạn muốn biết thứ hạng.

Danh sách

Đây là vùng chứa tất cả các số để so sánh. Bạn nên khóa vùng này bằng dấu $.

Thứ tự

Bạn bỏ trống hoặc gõ 0 để số lớn nhất đứng thứ 1. Bạn gõ một số khác 0, thường là 1, để số nhỏ nhất đứng thứ 1.

Microsoft giữ hàm RANK để tương thích với các file cũ, cảnh báo hàm có thể không còn ở các phiên bản sau, và khuyến nghị dùng RANK.EQ, RANK.AVG. Hàm RANK.EQ có cùng cú pháp và cho cùng kết quả với hàm RANK, nên bạn có thể thay tên hàm nếu muốn.

Bạn chuẩn bị dữ liệu như thế nào?

Bạn gõ bảng sau vào một sheet mới, với ô A1 là tiêu đề Nhân viên:

Nhân viên

Phòng

Doanh số

Số đơn

An

Kinh doanh

120

8

Bình

Kinh doanh

150

11

Cường

Tiếp thị

90

5

Dung

Kinh doanh

120

10

Hùng

Tiếp thị

200

14

Lan

Tiếp thị

90

7

Minh

Kinh doanh

75

4

Nga

Tiếp thị

130

9

Từ ô E1 đến ô J1, bạn gõ các tiêu đề: Hạng từ cao xuống, Hạng từ thấp lên, RANK.AVG, Hạng không trùng, Hạng không nhảy số và Hạng trong phòng.

Bảng 8 nhân viên với phòng, doanh số, số đơn và 6 cột hạng còn trống
Bảng có hai cặp nhân viên trùng doanh số là An với Dung, Cường với Lan

Ví dụ 1: Bạn xếp hạng từ cao xuống thấp

Bạn gõ công thức sau vào ô E2 rồi nhấn Enter. Tiếp theo, bạn chọn lại ô E2, nhấn giữ chấm vuông ở góc dưới bên phải ô và kéo xuống dòng 9:

=RANK(C2,$C$2:$C$9)

Cột E hiện 4, 2, 6, 4, 1, 6, 8 và 3
Hùng có doanh số 200 nên đứng thứ 1

Dấu $ rất quan trọng trong công thức này. Nếu bạn viết C2:C9 rồi kéo xuống, vùng so sánh sẽ trượt theo thành C3:C10, C4:C11, và thứ hạng sẽ sai mà Excel không báo lỗi.

Ví dụ 2: Bạn xếp hạng từ thấp lên cao

Cách này hợp với các chỉ số càng nhỏ càng tốt, như số lỗi hoặc thời gian giao hàng. Bạn gõ công thức sau vào ô F2, rồi kéo xuống dòng 9:

=RANK(C2,$C$2:$C$9,1)

Cột F hiện 4, 7, 2, 4, 8, 2, 1 và 6
Minh có doanh số nhỏ nhất nên đứng thứ 1 khi xếp từ thấp lên

Ví dụ 3: Bạn lấy hạng trung bình khi đồng hạng

Ở ví dụ 1, An và Dung cùng đứng thứ 4, và Excel bỏ qua hạng 5 nên người kế tiếp đứng thứ 6. Hàm RANK.AVG xử lý theo cách khác: hai người này chiếm hạng 4 và hạng 5, nên mỗi người nhận hạng trung bình là 4,5.

Bạn gõ công thức sau vào ô G2, rồi kéo xuống dòng 9:

=RANK.AVG(C2,$C$2:$C$9)

Cột G hiện 4.5, 2, 6.5, 4.5, 1, 6.5, 8 và 3
Hai cặp trùng doanh số nhận hạng 4,5 và 6,5

Ví dụ 4: Bạn tạo thứ hạng không trùng nhau

Khi xét thưởng, bạn có thể cần mỗi người một thứ hạng riêng. Bài này dùng cột Số đơn để phân định: ai trùng doanh số mà có nhiều đơn hơn thì đứng trên. Bạn gõ công thức sau vào ô H2, rồi kéo xuống dòng 9:

=RANK(C2,$C$2:$C$9)+COUNTIFS($C$2:$C$9,C2,$D$2:$D$9,">"&D2)

Cột H hiện 5, 2, 7, 4, 1, 6, 8 và 3
Dung có 10 đơn nên đứng thứ 4, còn An có 8 đơn nên đứng thứ 5

Công thức đếm số người có cùng doanh số nhưng nhiều đơn hơn, rồi cộng số đó vào thứ hạng. Với An, có 1 người như vậy là Dung, nên hạng của An là 4 + 1 = 5.

Nếu hai người trùng cả doanh số lẫn số đơn, họ vẫn đồng hạng. Khi đó, bạn cần thêm một tiêu chí phân định khác.

Ví dụ 5: Bạn xếp hạng mà không nhảy số

Nếu bạn muốn sau hạng 4 là hạng 5 thay vì hạng 6, bạn đếm số mức doanh số khác nhau đang lớn hơn số của dòng đó. Bạn gõ công thức sau vào ô I2, rồi kéo xuống dòng 9:

=SUMPRODUCT(($C$2:$C$9>C2)/COUNTIF($C$2:$C$9,$C$2:$C$9))+1

Cột I hiện 4, 2, 5, 4, 1, 5, 6 và 3
Thứ hạng chạy liền từ 1 đến 6, dù có hai cặp đồng hạng

Phần công thức

Phần này làm gì

$C$2:$C$9>C2

Phần này đánh dấu các số lớn hơn số của dòng đang xét.

COUNTIF($C$2:$C$9,$C$2:$C$9)

Phần này đếm mỗi số xuất hiện mấy lần.

Phép chia

Phép chia khiến các số trùng nhau chỉ được đếm là 1 mức.

+1

Phần này đổi số mức đứng trên thành thứ hạng.

Công thức này cần cột doanh số toàn số. Khi cột có ô trống, cả cột báo #DIV/0!. Khi cột có ô chữ, Excel coi chữ lớn hơn mọi số nên xếp ô đó đứng đầu mà không báo lỗi. Nếu bảng có thể chưa đủ số liệu, bạn dùng công thức sau, và dòng chưa có số sẽ hiện trống:

=IF(ISNUMBER(C2),SUMPRODUCT(ISNUMBER($C$2:$C$9)*($C$2:$C$9>C2)/COUNTIF($C$2:$C$9,$C$2:$C$9&""))+1,"")

Công thức dùng hàm SUMPRODUCT, và bài hàm SUMPRODUCT trong Excel giải thích kỹ hơn về hàm này.

Ví dụ 6: Bạn xếp hạng trong từng phòng

Hàm RANK không có tham số điều kiện, nên bạn dùng hàm COUNTIFS. Bạn gõ công thức sau vào ô J2, rồi kéo xuống dòng 9:

=COUNTIFS($B$2:$B$9,B2,$C$2:$C$9,">"&C2)+1

Cột J hiện 2, 1, 3, 2, 1, 3, 4 và 2
Bình đứng thứ 1 phòng Kinh doanh, còn Hùng đứng thứ 1 phòng Tiếp thị

Công thức đếm số người cùng phòng có doanh số cao hơn, rồi cộng thêm 1. Khi ô doanh số trống hoặc chứa chữ, công thức vẫn trả về một thứ hạng, nên bạn bọc thêm hàm IF: =IF(ISNUMBER(C2),COUNTIFS($B$2:$B$9,B2,$C$2:$C$9,">"&C2)+1,""). Khi đổi dấu ">" thành "<", bạn có thể xếp hạng từ thấp lên trong từng phòng. Bài hàm đếm số lượng trong Excel hướng dẫn kỹ hơn về hàm COUNTIFS.

Những lỗi nào hay gặp với hàm RANK?

Bạn có thể thử hai lỗi đầu trên một sheet mới. Bạn gõ tiêu đề Nhân viên, Doanh số, Hạng vào A1:C1, gõ An, Bình, Cường, Dung vào A2:A5, gõ 120, chữ chưa có, để trống, rồi gõ 90 vào B2:B5. Tiếp theo, bạn gõ =RANK(B2,$B$2:$B$5) vào ô C2 và kéo xuống dòng 5.

Ô C3 hiện #VALUE!, ô C4 hiện #N/A, hai dòng còn lại xếp hạng 1 và 2
Hàm RANK vẫn xếp hạng các số còn lại, và bỏ qua ô chữ, ô trống trong danh sách

Lỗi bạn thấy

Nguyên nhân

Cách xử lý

Công thức báo #VALUE!

Số cần xếp hạng là chữ.

Bạn sửa ô đó về số, hoặc bọc công thức bằng hàm IF.

Công thức báo #N/A

Số cần xếp hạng là ô trống và danh sách không có số 0, hoặc số đó không nằm trong danh sách. Nếu danh sách có số 0, ô trống vẫn nhận hạng mà không báo lỗi.

Bạn dùng công thức có hàm IF ở dưới bảng, và kiểm tra vùng danh sách.

Công thức báo #N/A dù ô đang hiện số

Số đó đang lưu dạng chữ, nên hàm bỏ qua ô này trong danh sách.

Bạn đổi ô đó về số.

Thứ hạng sai nhưng không báo lỗi

Vùng danh sách trượt vì thiếu dấu $.

Bạn khóa vùng thành $C$2:$C$9.

Số nhìn giống nhau nhưng hạng khác nhau

Các số khác nhau ở phần lẻ đang bị ẩn, hoặc có sai số nhỏ của phép tính.

Bạn làm tròn cột số bằng hàm ROUND trước khi xếp hạng.

Để các dòng chưa có số liệu hiện trống, bạn gõ công thức sau vào ô C2 rồi kéo xuống dòng 5:

=IF(ISNUMBER(B2),RANK(B2,$B$2:$B$5),"")

Người đọc thường hỏi gì về hàm RANK trong Excel?

Tôi muốn đưa người đứng đầu lên trên cùng thì làm sao?

Hàm RANK không đổi thứ tự dòng. Bạn cần sắp xếp bảng theo cột hạng, và bài 5 cách sắp xếp trong Excel có hướng dẫn việc này.

RANK, RANK.EQ và RANK.AVG khác nhau như thế nào?

Hàm

Khi đồng hạng, hàm trả về gì?

Ví dụ với 2 người đồng hạng 4

RANK

Hàm trả về số hạng nhỏ nhất của nhóm.

4 và 4

RANK.EQ

Hàm trả về số hạng nhỏ nhất của nhóm.

4 và 4

RANK.AVG

Hàm trả về hạng trung bình của nhóm.

4,5 và 4,5

Tôi thêm nhân viên mới thì có phải sửa công thức không?

Nếu thêm ở dòng 10, bạn sửa vùng ở ô E2 thành $C$2:$C$10, rồi kéo lại công thức từ dòng 2 đến dòng 10. Nếu chèn dòng mới từ dòng 3 đến dòng 9, Excel tự mở rộng vùng này, và bạn kéo công thức xuống dòng mới. Với công thức ở ví dụ 5, dòng mới chưa có doanh số sẽ làm cả cột báo #DIV/0! cho tới khi bạn gõ số.

Bạn muốn học Excel bài bản hơn?

Nếu muốn học bài bản cùng người hướng dẫn, bạn có thể tham khảo khóa 30 ngày học và thực hành Excel của Nghề Excel, với 30 buổi luyện tập, hơn 500 bài tập thực tế lấy từ doanh nghiệp và coach đồng hành cùng bạn trong từng buổi.

Ảnh chụp màn hình sản phẩm Microsoft: Used with permission from Microsoft.