Hàm xếp hạng trong Excel và những công thức xếp hạng có điều kiện

Hàm xếp hạng trong Excel là hàm gì?

Hàm xếp hạng trong Excel là hàm RANK và được dùng để xếp hạng dữ liệu theo thứ tự từ cao đến thấp hoặc từ thấp đến cao tùy theo nhu cầu của người sử dụng.

Nhờ có hàm RANK, công việc của dân công sở, đặc biệt là những người thường xuyên làm việc với bản tính hay những con số, sẽ tiết kiệm được kha khá khoảng thời gian quý báu của mình.

Người ta cũng tìm thấy những điểm tương đồng về mặt chức năng giữa hàm RANK và công cụ Sort nhưng chắc chắn là sẽ hoạt động hiệu quả cũng như tối ưu hơn.

Vậy ở trong trường hợp nào, chúng ta cần đến sự hữu dụng của hàm RANK?

• Thứ nhất, khi dữ liệu cần được xếp hạng theo thứ tự từ cao đến thấp.
• Thứ hai, khi dữ liệu cần được xếp hạng theo thứ tự từ thấp đến cao.

Đối với những bảng dữ liệu có từ 2 giá trị trở lên giống nhau thì làm sao?

Trường hợp này những tưởng sẽ làm khó hàm xếp hạng trong Excel nhưng thật ra lại vô cùng đơn giản. Hàm RANK sẽ cho chúng đồng hạng. Ví dụ trong bảng giá trị có số 20 xuất hiện 2 lần, vậy số 20 thứ 2 sẽ đồng hạng với số 20 thứ nhất.

Cú pháp hàm xếp hạng trong Excel

Mỗi hàm trong Excel có thể khác nhau về chức năng song không thể nào thiếu đi cú pháp mặc định, tất nhiên hàm xếp hạng trong Excel không phải là ngoại lệ.

Cú pháp hàm xếp hạng trong Excel hay còn gọi là hàm RANK như sau:

=RANK(number,ref, [order])

Giải thích cú pháp:

• RANK: tên hàm xếp hạng trong Excel (tiếng Anh có nghĩa là thứ hạng)
• number: dữ liệu cần được xếp hạng
• ref: khối dữ liệu chứa phần dữ liệu đang cần được xếp hạng
• order: khi trong công thức thì chỉ viết tượng trưng là “order”, nhưng khi được áp dụng trong thực tiễn, nó sẽ được thay bằng số 0 hoặc số 1. Trong đó, nếu là số 0 hoặc không có tham số order thì mặc định xếp hạng từ cao đến thấp, còn nếu là số 1 thì sẽ xếp hạng từ thấp đến cao.

Nếu bạn đang còn cảm thấy quá trừu tượng và khó hiểu, đừng lo, hãy tiếp tục theo dõi bài viết để có những ví dụ thực tiễn và hướng dẫn chi tiết cho hàm xếp hạng trong Excel.

Hàm sắp xếp từ cao đến thấp trong Excel

Trong một bản dữ liệu gồm rất nhiều giá trị lớn nhỏ khác nhau và bạn chỉ muốn nó thể hiện từ cao xuống thấp, hãy dùng hàm xếp hạng theo cú pháp sau:

=RANK(number,ref, [order]) hoặc =RANK(number,ref)

Ví dụ: Xếp hạng nhân viên theo thành tích đạt KPI từ cao đến thấp trong phạm vi tháng 7/2021 của phòng Nội dung (Content Marketing).

Bước 1: Nhập công thức vào ô đầu tiên trong cột xếp hạng với cú pháp:

=RANK(B2,$B$2:$B$5,0) hoặc =RANK(B2,$B$2:$B$5)

Giải thích công thức:

• B2: Giá trị cần được xếp hạng trong Excel
• $B$2:$B$5: Danh sách các bài viết của tất cả nhân viên phòng Nội dung. Để cố định phần này (làm xuất hiện ký tự $, bạn chọn từ B2 đến B5 rồi bấm F4 hoặc fn+F4).
• 0: Đại diện cho chữ “order” trong cú pháp tổng quát, có nghĩa là xếp hạng theo thứ tự tăng dần. Có thể không điền số 0 vẫn cho ra kết quả tương tự.

Bước 2: Nhấn Enter và bạn sẽ có kết quả như hình.

Bước 3: Để thực hiện xếp hạng những nhân viên còn lại, đừng copy công thức hay viết lại cho từng người mà hãy làm theo video hướng dẫn sau để tối ưu hóa:

Vậy theo kết quả cuối cùng thì nhân viên Đinh Hoàng Mai có thành tích cao nhất (30 bài viết) nếu xếp hạng từ cao xuống thấp. Công thức này thường được dùng để muốn tìm ra và khen thưởng nhân viên xuất sắc của nhóm.

Hàm sắp xếp từ thấp đến cao trong Excel

Giả sử nếu muốn tìm ra và phê bình hoặc đào tạo thêm cho người có thành tích tệ nhất nhóm thì làm cách nào? Chúng ta sẽ ngay lập tức làm ngược lại, áp dụng hàm xếp hạng trong Excel theo thứ tự từ thấp đến cao.

Để áp dụng hàm xếp hạng từ thấp đến cao, bạn hãy thuộc cú pháp này:

=RANK(number,ref,1)

Giải thích cú pháp:

• number: Giá trị được xếp hạng
• ref: Khối dữ liệu chứa giá trị được xếp hạng (number)
• 1: biểu thị cho chữ “order”, ý muốn cho Excel hiểu người dùng muốn sắp xếp theo thứ tự từ thấp đến cao.

Ví dụ: Xếp hạng nhân viên theo thành tích đạt KPI từ cao đến thấp trong phạm vi tháng 7/2021 của phòng Nội dung (Content Marketing).

Bước 1: Ở ô đầu tiên của cột Xếp hạng ta điền vào công thức sau:

=RANK(B2,$B$2:$B$5,1)

Giải thích công thức:

• B2: Giá trị cần được xếp hạng.
• $B$2:$B$5: Khối dữ liệu chứa giá trị B2, phải cố định bằng cách chọn từ ô B2 đến ô B5 sau đó nhấn F4 hoặc fn+F4).
• 1: Lệnh của xếp hạng theo thứ tự từ thấp đến cao.

Bước 2: Nhấn Enter và có kết quả như hình:

Bước 3: Thực hiện xếp hạng các nhân viên còn lại bằng cách di chuyển con trỏ vào bên góc phải ô đầu tiên rồi kéo xuống.

Vậy là nhờ hàm xếp hạng trong Excel theo thứ tự từ thấp đến cao, bạn dễ dàng tìm ra ai là người có thành tích tệ nhất nhóm, cụ thể là nhân viên Nguyễn Quốc Đại, từ đó có những hướng giải quyết và phân chia công việc phù hợp.

Hàm xếp hạng trong Excel theo điều kiện

Trong những trường hợp cụ thể ngoài thực tiễn, không chỉ đơn giản là dùng hàm xếp hạng trong Excel để sắp xếp lớn nhỏ, mà cũng có lúc phải thực hiện theo một số điều kiện kèm theo. Cách để bạn giải quyết vấn đề chính là áp dụng hàm xếp hạng theo điều kiện.

Có hai loại hàm xếp hạng theo điều kiện trong Excel: Xếp hạng trong Excel theo một điều kiện và Xếp hạng trong Excel theo nhiều điều kiện.

Xếp hạng trong Excel theo một điều kiện

Khi chúng ta đặt một điều kiện cho việc xếp hạng trong Excel, nếu chỉ dùng mỗi hàm RANK thì không có kết quả, phải áp dụng đồng thời nhiều hàm khác.

Việc sử dụng kèm theo một điều kiện nhất định sẽ giúp quá trình phân hạng diễn ra rõ ràng hơn, tránh sự đồng hạng xảy ra, điều này đặc biệt quan trọng nếu bạn muốn xếp loại học sinh theo bảng điểm các môn.

Ví dụ: Xếp hạng nhân viên theo thứ tự từ cao đến thấp bằng điểm trung bình của 3 tiêu chí Trang phục, Giờ giấc, Thái độ. Nếu ai có điểm Giờ giấc cao hơn thì ưu tiên đứng thứ hạng cao hơn.

Bước 1: Để tiện cho việc xếp hạng theo điều kiện điểm giờ giấc như đề yêu cầu, ta xét điểm ưu tiên Giờ giấc nếu điểm trung bình bằng nhau trước, ta có công thức sau:

=IF(COUNTIF($C$3:$C$7,C3)>1,RANK.EQ(C3,$C$3:$C$7,1),0)

Giải thích công thức:

• COUNTIF($C$3:$C$7,C3)>1: Dùng hàm COUNTIF đếm trong vùng dữ liệu từ C3 đến C7 (cột Giờ giấc) có giá trị nào trùng nhau hay không, nếu có thì tức nhiên là lớn 1.
• RANK.EQ(B4,$B$4:$B$13,1): Sử dụng hàm RANK.EQ để kiểm tra có điểm trung bình nào bị trùng không, nếu có thì nếu ai có điểm Giờ giấc cao hơn sẽ có thứ hạng cao hơn theo thứ tự từ cao đến thấp.

Hiểu nôm na, ý nghĩa của công thức này là nếu điểm trung bình bị trùng nhau thì dùng điểm giờ giấc để xem ai có vị thứ cao hơn, sắp xếp theo thứ tự tăng dần.

Tại sao xếp theo thứ tự từ cao đến thấp? Vì thứ hạng được tính theo điểm Giờ giấc này sẽ dùng để cộng vào điểm trung bình gốc (cột E), điểm càng cao thứ hạng sẽ càng tăng.

Tại sao dùng hàm RANK.EQ? Vì nó nâng cấp hơn hàm RANK, nếu có nhiều hơn 1 người có cùng số điểm Giờ giấc, vậy ai có thứ hạng cao nhất sẽ được thứ hạng lớn hơn. Ví dụ điểm Giờ giấc của Thảo và Thắng đều bằng 9.5 nhưng điểm trung bình Thảo > Thắng => Thảo xếp hạng cao hơn.

Bước 2: Tính lại điểm trung bình sau khi tính điểm ưu tiên Giờ giấc, thực hiện theo cú pháp: =E3+G3/1000

Giải thích công thức:

• Dùng điểm ưu tiên đã tính được cộng vào với điểm trung bình cộng của 3 tiêu chí.
• Chúng ta lấy kết quả với 3 chữ số thập phân nên chia cho 1000.
• Khuyến khích để cột điểm ưu tiên gần với cột điểm gốc nhằm tiện lợi hơn.

Bước 3: Xếp hạng dựa trên điểm trung bình vừa được tính lại theo cú pháp:

=RANK(H3,$H$3:$H$7,0).

Bước 4: Nhấn Enter để có kết quả. Vậy cuối cùng người có thứ hạng cao nhất là nhân viên Đoàn Văn Quyết với điểm giờ giấc là 10.

Xếp hạng trong Excel theo nhiều điều kiện

Nếu đã thuần thục cách xếp hạng trong Excel theo một điều kiện thì việc áp dụng nhiều điều kiện để xếp hạng cũng sẽ diễn ra dễ dàng hơn.

Ví dụ: Xếp hạng nhân viên theo điểm trung bình, nếu đồng hạng thì ưu tiên xét theo thứ tự các hạng mục Giờ giấc > Trang phục > Thái độ.

Bước 1: Xét ưu tiên điểm Giờ giấc khi điểm trung bình bằng nhau, công thức:

=IF(COUNTIF($C$3:$C$7,C3)>1,RANK.EQ(C3,$C$3:$C$7,1),0)

Giải thích công thức: Xem lại Bước 1 phần xếp hạng trong Excel theo một điều kiện.

Bước 2: Xét ưu tiên điểm Trang phục nếu điểm ưu tiên Giờ giấc trùng nhau, công thức:

=IF(AND(G3>0,COUNTIF(G$3:G$7,G4)>1),RANK.EQ(B3,B$3:B$7,1),0)

Giải thích công thức:

• G3>0: Chỉ thực hiện khi điểm ưu tiên giờ giấc khác 0
• COUNTIF(G$3:G$7,G4)>1: Chỉ thực hiện khi xếp hạng ưu tiên Giờ giấc xuất hiện đồng hạng
• (AND(G3>0,COUNTIF(G$3:G$7,G4)>1): Nếu cả hai điều kiện nêu trên đều thỏa mãn thì mới thực hiện tính điểm ưu tiên Trang phục.
• RANK.EQ(B3,B$3:B$7,1): Xếp hạng điểm ưu tiên Trang phục theo thứ tự tăng dần

Bước 3: Xét ưu tiên Thái độ nếu trường hợp điểm ưu tiên Giờ giấc và Thái độ bằng nhau, công thức:

=IF(AND(K3>0,COUNTIF(K$3:K$7,K3)>1),RANK.EQ(D3,D$3:D$7,1),0)

• K3>0: Chỉ thực hiện nếu xếp hạng ưu tiên Trang phục khác 0
• COUNTIF(K$3:K$7,K3)>1: Chỉ thực hiện nếu xếp hạng ưu tiên Trang phục đồng hạng
• (AND(K3>0,COUNTIF(K$3:K$7,K3)>1): Nếu cả hai điều nêu trên đều đáp ứng thì mới xét điểm ưu tiên thái độ
• RANK.EQ(D3,D$3:D$7,1),0): Xếp hạng điểm ưu tiên Thái độ theo thứ tự từ thấp đến cao

Bước 4: Tính điểm trung bình lại dựa trên 3 điểm ưu tiên mà ta đã tính, công thức: =H3+J3/1000+K3/10000+L3/100000

Sở dĩ phải chia cho 1000, 10000, 100000 là vì mỗi cấp bậc ưu tiên phải xét giảm 10 lần để cho ra kết quả không làm tròn nhờ vậy mà dễ dàng phân loại ngay cả bằng mắt thường.

Bước 5: Tiến hành xếp loại nhân viên theo điểm trung bình mới tính được theo thứ tự từ cao đến thấp:

=RANK(M3,$M$3:$M$7,0)

Bước 6: Nhấn Enter và có kết quả sau:

Như vậy, thông qua bài viết này, Phanmemgoc.vn đã giúp bạn hiểu rõ từng chi tiết và cách áp dụng hàm xếp hạng trong Excel, hay còn gọi là hàm RANK.

Trên thực tế, hàm RANK khi đi cùng với những điều kiện sẽ khá phức tạp và cần bạn vận dụng vốn kiến thức đã được học trong Excel để kết hợp chúng một cách logic. Có thể bạn sẽ nhớ, nhưng nếu bạn quên, đừng ngần ngại tìm đến những bài viết hướng dẫn trên Phanmemgoc.vn nhé. Chúc bạn thành công.