Xếp hạng theo điều kiện trong excel – Phần 3

Phần 1: Phân biệt các hàm xếp hạng RANK / RANK.AVG / RANK.EQ

Phần 2: xếp hạng theo một điều kiện

Sau khi hiểu cách xếp hạng theo một điều kiện, chúng ta có thể áp dụng cách đó cho nhiều điều kiện.

Với trường hợp nhiều điều kiện, chúng ta cần quan tâm tới 2 tiêu chí:

• Tiêu chí 1: Thứ tự ưu tiên của các điều kiện: điều kiện nào xét trước, điều kiện nào xét sau
• Tiêu chí 2: Công thức xét 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 như sau:

Xếp hạng theo điểm trung bình, nếu đồng hạng thì ưu tiên xét theo thứ tự các môn: Toán > Lý > Hóa

Hướng dẫn học Excel cơ bản

Chúng ta 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 1 điều kiện thì vẫn đồng xếp hạng 6. Do đó cần xét thêm điều kiện ưu tiên khác.

Chúng ta cùng phân tích cụ thể cách làm nhé:

Bước 1: Xét ưu tiên môn toán khi điểm trung bình bằng nhau (giống ở Phần 2)

Công thức sử dụng:

=IF(COUNTIF($E$4:$E$13,E4)>1,RANK.EQ(B4,$B$4:$B$13,1),0)

Nội dung công thức này đã phân tích trong phần 2, mời các bạn xem lại để hiểu.

Bước 2: 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 đó)

Công thức sử dụng:

=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 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 trùng nhau)
• COUNTIF(L$4:L$13,L4)>1 : Chỉ xét khi 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ó đồng hạng)
• AND(L4>0,COUNTIF(L$4:L$13,L4)>1): 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 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 để tính điểm ưu tiên

Xem ngay: Học Excel nâng cao ở đâu?

Bước 3: 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ý)

Công thức sử dụng:

=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 khi xếp hạng ưu tiên môn Lý khác 0 (tức là ãã 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 trùng)
• COUNTIF(M$4:M$13,M4)>1: Chỉ xét khi 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ó đồng hạng)
• AND(M4>0,COUNTIF(M$4:M$13,M4)>1): 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ế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: 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

Ở bước này, các bạn lưu ý là thứ tự ưu tiên của các môn sẽ được cộng vào điểm trung bình, do đó mỗi cấp bậc ư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ả chúng ta có thể phân biệt ngay bằng mắt thường được => Khi đó kiểm tra lại cũng dễ hơn.

Khóa học excel kế toán

Bước 5: Xếp hạng dựa trên điểm trung bình mới

Ở bước này, chúng ta xếp hạng theo thứ tự giảm dần ( Order = 0)

Như vậy bằng việc sử dụng các cột phụ và xếp hạng thứ tự ưu tiên, chúng ta 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.

Cảm ơn các bạn đã chú ý theo dõi bài viết.