Công thức Excel xếp hạng, công thức xếp hạng có điều kiện

Để dễ dàng hơn trong việc tìm ra đối tượng nhất, nhì,…, chót, người ta áp dụng công thức Excel xếp hạng – hàm RANK. Rất nhiều người nghĩ đây là một hàm dễ vận dụng, nhưng có thật là như vậy? Hãy cùng EDUSA tìm hiểu ngay câu trả lời bạn nhé.

1. Công thức Excel xếp hạng là gì?

Công thức Excel xếp hạng 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.

Xem thêm: Các phím tắt copy công thức đơn giản nhất

2. Cú pháp của công thức Excel xếp hạng

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ố
• 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.

Xem thêm: Cách làm trang tính Excel online trên máy tính

3. 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.

Xem thêm: Cách dùng công thức Excel lọc dữ liệu đơn giản và dễ hiểu  

4. 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.

Xem thêm: Công thức tính số ngày trong Excel nhanh nhất

5. Công thức Excel xếp hạng 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 công thức Excel xếp hạng theo điều kiện: 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.

5.1 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

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.

Xem thêm: Top 5 sách học Excel tốt nhất hiện nay

5.2 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)

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ả.

Xem thêm: Cách dùng công thức Excel hàm ROUND dễ hiểu nhất

6. Trung tâm dạy Excel uy tín – EDUSA

Hiện nay, có rất nhiều trung tâm tin học được mở ra nhằm phục vụ những nhu cầu của các bạn. Tuy nhiên, mỗi trung tâm lại có chất lượng giảng dạy khác nhau, và theo sau đó là những khóa học với chi phí và ưu đãi khác nhau.

Đến với trung tâm EDUSA, bạn sẽ được tư vấn kỹ lưỡng về những khoá học phù hợp với bạn nhất. Bên cạnh đó, những mức phí của khóa học đều nằm ở mức trung bình giúp bạn có thể chi trả một cách dễ dàng. Mặc dù mức phí khá thấp nhưng chất lượng giảng dạy tại EDUSA vô cùng tuyệt vời, đội ngũ giáo viên có chuyên môn cao, nhiệt tình giảng dạy các bạn học viên. 

Và đặc biệt hơn, EDUSA sẽ cung cấp một vài buổi học và thi lại miễn phí cho các bạn nếu các bạn thi không đạt chứng chỉ ở lần thi đầu tiên. Thật tuyệt vời phải không nào? Hãy đến trung tâm EDUSA để được tư vấn kỹ hơn nhé.

Xem thêm: Khóa học Excel offline chất lượng tại EDUSA 

Xem thêm: Khóa học Excel Online chất lượng tại EDUSA 

7. Câu hỏi thường gặp

7.1 Thời gian học Excel tại EDUSA có linh động không?

100% thời gian học dù là online hay offline đều linh động theo lịch rảnh cá nhân của học viên, đảm bảo được sự thoải mái xuyên suốt quá trình học.

7.2 Khóa học Excel tại EDUSA có bao đậu không?

Khóa học của EDUSA đều sát với đề thi thật, bài tập và đề thi thử được cập nhật thường xuyên. Với chính sách cam kết đầu ra 100%  bằng văn bản cụ thể sẽ giúp học viên yên tâm trong suốt quá trình học tại đây.

7.3 EDUSA có gì khác biệt so với các trung tâm luyện thi Excel khác hay không?

Học viên có thể hoàn toàn yên tâm và tin tưởng khi lựa chọn luyện thi Excel tại lớp. Tất cả đều được chúng tôi thể hiện qua văn bản rõ ràng khi thực hiện cam kết với học viên.

8. Kết luận

Như vậy, thông qua bài viết này, EDUSA đã giúp bạn hiểu rõ từng chi tiết và cách áp dụng công thức Excel xếp hạng 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. Chúc bạn thành công.