Bài viết này, chúng ta sẽ cùng nhau tìm hiểu chi tiết về hàm RANK có điều kiện trong Excel, cùng theo dõi nhé!
Để dễ hình dung, chúng ta sẽ cùng nhau áp dụng bài tập sau để làm ví dụ minh họa:
Phần 1: Phân biệt các hàm RANK / RANK.AVG / RANK.EQ
Kể từ phiên bản 2010, Excel xuất hiện 2 hàm thay thế cho hàm RANK, đó là hàm RANK.AVG và hàm RANK.EQ. Những phiên bản trước đó (2003, 2007) thì chỉ có hàm RANK mà thôi.
Về cú pháp, 3 hàm này đều có chung 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: là ối số bạn muốn tìm thứ hạng cho nó.
– Ref: là một mảng hay tham chiếu đến danh sách các số.
– Order: là số chỉ rõ cách xếp hạng. (Order có 2 giá trị là 0 và 1).
*Lưu ý: Nếu như Order=0 thì xếp hạng theo thứ tự giảm dần, còn order =1 thì xếp hàng theo thứ tự tăng dần.
Cùng xét việc dùng 3 hàm này vào bài tập và hãy xem kết quả thu được sẽ thế nào nhé
Hàm RANK và RANK.AVG đã cho ra kết quả giống nhau. Tại đây, hàm RANK.AVG nghĩa là thứ hạng trung bình, hay là nếu có sự đồng hạng (thứ hạng ngang nhau) thì sẽ thực hiện xếp trung bình cho số hạng đó (2 người đồng hạng thì thứ hạng trung bình là + 0,5 ; trong khi đó 10 người đồng hạng thì thứ hạng trung bình là + 0,1) (AVG là viết tắt của Average)
Hàm RANK.EQ có chức năng xếp đồng hạng mà không tính trung bình hạng, có nghĩa là nếu 2 người đồng hạng thì sẽ xếp cùng vào 1 thứ hạng được làm tròn, chứ không kể thứ hạng lẻ. (EQ là viết tắt của Equal)
Để có thể ứng dụng được việc xếp hạng của những hàm này vào việc xếp hạng theo một hay nhiều điều kiện, mời bạn đón xem ở các phần sau của bài viết này. Mục tiêu của chúng ta sẽ là bảng kết quả bên dưới:
Phần 2: Cách phân biệt những người đồng hạng theo 1 tiêu chuẩn ưu tiên (xếp hạng theo một điều kiện)
Dựa vào bài tập ví dụ ở trên, bạn có thể xác định yêu cầu ở đây là:
Xếp hạng theo điểm trung bình, nếu như đồng hạng thì ưu tiên cho ai có điểm toán cao hơn thì sẽ xếp hạng cao hơn.
Hãy cùng phân tích đề bài để hiểu cách làm nhé:
Bước 1: Tiến hành xét ưu tiên môn toán khi điểm trung bình bằng nhau.
Công thức áp dụng là:
=IF(COUNTIF($E$4:$E$13,E4)>1,RANK.EQ(B4,$B$4:$B$13,1),0)
Phân tích công thức:
COUNTIF($E$4:$E$13,E4)>1 thực hiện đếm trong vùng E4:E13 theo từng giá trị điểm, rồi xét điểm nào có kết quả đếm lớn hơn 1 (tức là có điểm trùng)
RANK.EQ(B4,$B$4:$B$13,1) Khi điểm trung bình bị trùng, bạn sẽ xếp hạng theo điểm toán, thứ tự tăng dần (trong đó, ai điểm thấp nhất sẽ xếp trước)
=> Công thức nay sẽ được hiểu là: nếu như điểm trung bình mà trùng, thì tại những người bị trùng sẽ xếp hạng họ theo điểm môn toán, và theo thứ tự tăng dần.
* Lưu ý ở đây: Bạn có thắc mắc tại sao thứ tự ưu tiên lại xếp tăng dần mà không phải giảm dần? Câu trả lời là bởi vì như vậy ai điểm càng cao thì thứ tự càng là số lớn => Số thứ hạng này sẽ cũng chính là số sẽ được cộng thêm vào điểm gốc để làm căn cứ xếp hạng với điều kiện ưu tiên => Số điểm càng cao thì điểm cộng càng nhiều
Bước 2: Thực hiện tính lại điểm trung bình khi có điểm ưu tiên
Vì điểm trung bình có 2 số nằm sau dấu phẩy nên điểm ưu tiên sẽ tính phía sau dấu phẩy 3 chữ số (chia thứ hạng ở cột ưu tiên cho 1000)
Khi đó, bạna sẽ được điểm trung bình mới bao gồm điểm trung bình cũ + điểm xếp hạng với các điểm trùng mà xét ưu tiên với điểm toán
Điểm xếp hạng ưu tiên này nên đặt ở phía sau điểm gốc để việc sắp xếp thứ hạng được tốt hơn và dễ phân biệt hơn.
Bước 3: Tiến hành xếp hạng dựa trên điểm trung bình mới
Hàm xếp hạng ở đây ta sẽ dùng hàm RANK.EQ để xếp hạng cho điểm trung bình mới.
Xem thêm: Hướng dẫn cách đánh số la mã trong Excel
Phần 3. Cách xây dựng công thức xếp hạng theo một điều kiện, hay theo nhiều điều kiện (theo thứ tự ưu tiên)
Sau khi đã hiểu được cách xếp hạng theo một điều kiện, bạn có thể áp dụng cách đó cho nhiều điều kiện.
Đối với trường hợp nhiều điều kiện, bạn cần quan tâm tới 2 tiêu chí:
- Tiêu chí 1: là thứ tự ưu tiên của những đ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 những đ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:
Thực hiện xếp hạng theo điểm trung bình, nếu như đồng hạng thì ưu tiên xét theo thứ tự những môn: Toán > Lý > Hóa
Lúc này, bạn có thể thấy đối 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 đang đồng xếp hạng 6. Do vậy, bạn cần xét thêm điều kiện ưu tiên khác.
Hãy cùng phân tích cụ thể cách làm này nhé:
Bước 1: Đầu tiên, 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 bài viết đã phân tích trong phần 2, mời bạn xem lại để hiểu hơn.
Bước 2: Tiến hành 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 -> xét 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: là chỉ xét khi xếp hạng ưu tiên môn Toán khác 0 (có nghĩa là điểm trung bình ban đầu phải trùng nhau)
- COUNTIF(L$4:L$13,L4)>1 : là chỉ tiến hành 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 như xuất hiện nhiều hơn 1 thì có nghĩa là có đồng hạng)
- AND(L4>0,COUNTIF(L$4:L$13,L4)>1): tiến hành 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): là xếp hạng điểm môn Lý theo thứ tự tăng dần để tính điểm ưu tiên
Bước 3: Xét ưu tiên môn Hóa trong trường hợp 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: là 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: là chỉ xét khi xếp hạng ưu tiên môn Lý đồng hạng (thực hiện đế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): 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): là 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: Tiến hành tính điểm trung bình mới dựa vào điểm ưu tiên của những điều kiện
Trong bước này, bạn lưu ý rằng thứ tự ưu tiên của những môn này sẽ được cộng vào điểm trung bình, vì thế mỗi cấp bậc ưu tiên cũng đồng thời phải xét giảm đi 10 lần để khi nhìn vào kết quả bạn có thể phân biệt ngay bằng mắt thường được => Khi đó kiểm tra cũng sẽ dễ hơn.
Bước 5: Xếp hạng dựa vào điểm trung bình mới
Trong bước này, bạn xếp hạng theo thứ tự giảm dần ( Order = 0)
Vậy là với việc dùng những cột phụ và xếp hạng thứ tự ưu tiên, bạ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. Chúc bạn thực hiện thành công!
Xem thêm: Hướng dẫn cách dùng hàm trả về thứ trong tuần
