Khi bạn nhắc đến cách xếp hạng trong excel thì không thể nào không nhắc tới hàm Rank được. Nhưng bạn không biết cách sử dụng hàm Rank như thế nào để xếp hạng theo nhiều điều kiện. Bài viết dưới đây sẽ hướng dẫn bạn cách xếp hạng theo nhiều điều kiện trong Excel.
Để dễ hiểu hơn, chúng ta sẽ áp dụng một bài tập sau để làm ví dụ minh họa:
Ví dụ minh họa
Phân biệt các hàm RANK / RANK.AVG / RANK.EQ
Bắt đầu từ phiên bản 2010 trở lên, trong excel xuất hiện thêm 2 hàm thay thế cho hàm RANK đó là hàm RANK.AVG và hàm RANK.EQ. Các phiên bản Excel trước đó (2003, 2007) chỉ có hàm RANK mà thôi.
Về cú pháp, 3 hàm này đều có chung một cú pháp giống nhau
CÚ PHÁP:
=RANK( number, ref, [order])
=RANK.AVG( number, ref, [order])
=RANK.EQ( number, ref, [order])
Trong đó:
– Number: Có nghĩa là đối số mà bạn muốn thực hiện tìm thứ hạng cho nó.
– Ref: Có nghĩa là một mảng hoặc tham chiếu đến danh sách các số cần xếp hạng.
– Order: Có nghĩa là số chỉ rõ cách xếp hạng. (Order có 2 giá trị là giá trị 0 và giá trị 1).
*Lưu ý: Nếu Order=0 thì xếp hạng sẽ sắp xếp theo thứ tự giảm dần, order =1 thì xếp hạng sẽ sắp xếp theo thứ tự tăng dần.
Bạn xét việc sử dụng 3 hàm này vào bài tập đã nói ở trên và xem kết quả thu được thế nào nhé: (Xếp hạng theo thứ tự giảm dần)
Xếp hạng theo thứ tự giảm dần
Bạn sẽ thu được kết quả hàm RANK và RANK.AVG cho kết quả giống nhau. Ở đây hàm RANK.AVG có nghĩa là thứ hạng trung bình, tức là nếu có sự đồng hạng (thứ hạng ngang nhau) thì sẽ được xếp hạng trung bình cho số hạng đó (2 người đồng hạng thì thứ hạng trung bình sẽ là + 0,5 ; 10 người đồng hạng thì thứ hạng trung bình sẽ là + 0,1) (AVG là chữ viết tắt của Average)
Hàm RANK.EQ sẽ được xếp đồng hạng mà không phải tính trung bình hạng, tức là nếu 2 người cùng đồng hạng thì sẽ xếp cùng vào 1 thứ hạng được làm tròn, chứ không phải thứ hạng lẻ. (EQ là chữ viết tắt của Equal)
Kết quả xếp hạng
Hướng dẫn cách xếp hạng theo nhiều điều kiện trong Excel
Dù đã có nhiều phần mềm thực hiện việc xếp hạng này, nhưng những kỹ năng Excel vẫn cực kỳ quan trọng với nhân viên kế toán
- Tiêu chí 1: Thứ tự được xếp hạng ưu tiên của các điều kiện: điều kiện nào sẽ được xét trước, điều kiện nào sẽ được xét sau
- Tiêu chí 2: Công thức xếp hạng của các điều kiện tiếp theo sẽ phụ thuộc vào thứ hạng của điều kiện trước đó.
Yêu cầu của bài tập ví dụ cho phần xếp hạng theo nhiều điều kiện trong Excel như sau:
Bạn hãy xếp hạng theo điểm trung bình tổng các môn, nếu đồng hạng thì bạn ưu tiên xét theo thứ tự các môn: Toán > Lý > Hóa
Xét ví dụ minh họa
Bạn hoàn toàn có thể thấy với trường hợp dòng 7 (Dương Quốc Đạt) và dòng 10 (Lê Bá Long) khi xét theo kiểu xếp hạng 1 điều kiện thì vẫn đồng xếp hạng 6. Do đó bạn cần phải xét thêm điều kiện ưu tiên khác.
Bạn cùng phân tích cụ thể cách làm như sau nhé:
Bước 1: Bạn xét ưu tiên môn toán khi điểm tổng trung bình bằng nhau (giống ở Phần 2)
Xét ưu tiên môn toán
Sử dụng công thức:
=IF(COUNTIF($E$4:$E$13,E4)>1,RANK.EQ(B4,$B$4:$B$13,1),0)
Bước 2: Bạn xét ưu tiên môn Lý khi xếp hạng điểm môn Toán đã bằng nhau (Đồng điểm môn Toán =>theo thứ tự ưu tiên, có tính phụ thuộc vào thứ tự trước đó)
Xét ưu tiên môn Lý
Sử dụng công thức sau:
=IF(AND(L4>0,COUNTIF(L$4:L$13,L4)>1),RANK.EQ(C4,C$4:C$13,1),0)
Phân tích công thức:
- L4>0: Chỉ xét khi điều kiện xếp hạng ưu tiên môn Toán khác 0 (tức là điểm trung bình ban đầu phải có giá trị trùng nhau)
- COUNTIF(L$4:L$13,L4)>1 : Chỉ xét với trường hợp xếp hạng ưu tiên môn Toán đồng hạng (đếm kết quả xếp hạng ưu tiên môn Toán nếu xuất hiện nhiều hơn 1 thì tức là có sự đồng hạng)
- AND(L4>0,COUNTIF(L$4:L$13,L4)>1): Xét xếp hạng thỏa mãn đồng thời cả 2 điều kiện trên thì mới được xét trường hợp ưu tiên môn Lý
- RANK.EQ(C4,C$4:C$13,1): Xếp hạng điểm môn Lý theo thứ tự tăng dần để bạn tính điểm ưu tiên
Bước 3: Trường hợp xét ưu tiên môn Hóa khi xếp hạng điểm môn Toán và môn Lý bằng nhau (đồng điểm cả môn Toán và môn Lý)
Xét ưu tiên môn Hóa
Sử dụng công thức sau:
=IF(AND(M4>0,COUNTIF(M$4:M$13,M4)>1),RANK.EQ(D4,D$4:D$13,1),0)
Phân tích công thức:
- M4>0: Chỉ xét với trường hợp xếp hạng ưu tiên môn Lý khác 0 (tức là bạn đã bao gồm trùng của môn Toán rồi, giờ xét thêm môn Lý có thứ hạng nào trùng)
- COUNTIF(M$4:M$13,M4)>1: Chỉ xét với trường hợp xếp hạng ưu tiên môn Lý đồng hạng (đếm kết quả xếp hạng ưu tiên môn Lý nếu xuất hiện nhiều hơn 1 thì tức là có sự đồng hạng)
- AND(M4>0,COUNTIF(M$4:M$13,M4)>1): Lúc này là xét thỏa mãn đồng thời cả 2 điều kiện trên thì mới được tính ưu tiên môn Hóa
- RANK.EQ(D4,D$4:D$13,1),0): Xét xếp hạng điểm môn Hóa theo thứ tự tăng dần để tính điểm ưu tiên
Bước 4: Lúc này, bạn tính điểm trung bình mới dựa trên điểm ưu tiên của các điều kiện
Tính điểm trung bình
Ở bước này, các bạn lưu ý là thứ tự ưu tiên điểm của các môn học sẽ được cộng vào tổng điểm trung bình, do đó mỗi cấp bậc bạn ưu tiên cũng đồng thời phải xét giảm đi 10 lần để nhìn vào kết quả bạn hoàn toàn có thể phân biệt ngay bằng mắt thường được => Khi đó bạn kiểm tra lại cũng dễ hơn.
Bước 5: Bạn xếp hạng dựa trên điểm trung bình mới
Xếp hạng theo thứ tự giảm dần
Ở bước này, bạn xếp hạng theo thứ tự giảm dần ( Order = 0)
Như vậy bằng với việc sử dụng các cột phụ và xếp hạng theo thứ tự ưu tiên, chúng ta hoàn toàn có thể hoàn thành được việc xếp hạng theo nhiều điều kiện một cách dễ dàng và dễ hiểu.
