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

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

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

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

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:

Xem thêm :   Cách sử dụng hàm Choose trong Excel

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

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

Xét ưu tiên môn toán

Sử dụng công thức:

Xem thêm :   【Hướng Dẫn】Cách Kiểm Tra Độ Chai Pin Laptop Win 10

=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ý

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

Xét ưu tiên môn Hóa

Sử dụng công thức sau:

Xem thêm :   Tự học excel cơ bản cấp tốc qua 15 chuyên đề

=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

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

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.

 

We will be happy to hear your thoughts

Leave a reply

Wiki19.com
Logo
Enable registration in settings - general