Excel nằm trong bộ công cụ văn phòng của Microsoft Office được sử dụng rộng rãi ở rất nhiều đối tượng trong các ngành nghề khác nhau. Một trong những kỹ năng quan trọng khi sử dụng phần mềm là hàm RANK có điều kiện. Bài viết dưới đây sẽ cung cấp các vấn đề liên quan tới hàm RANK có điều kiện trong Excel để bạn đọc hiểu rõ hơn nhé.
Mục Lục
1. Hàm RANK có điều kiện trong Excel là gì
Hàm RANK là một trong những hàm xếp hạng cơ bản trong Excel để xếp hạng một số trong danh sách nhiều số.
Cũng có thể hiểu hàm RANK là hàm sắp xếp thứ tự từ cao đến thấp hoặc từ thấp lên cao, là hàm để liệt kê danh sách tăng dần hoặc giảm dần.
Hàm RANK có điều kiện là hàm xếp hạng theo một hoặc nhiều điều kiện( theo thứ tự ưu tiên)
2. Phân biệt ba loại hàm RANK, RANK.AVG, RANK.EQ
Hai biến thể RANK.AVG và RANK.EQ là hai hàm thay thế cho RANK xuất hiện từ bản Excel 2010. Trong đó AVG là viết tắt của chữ Average( trung bình) và EQ là viết tắt của chữ Equal( ngang bằng)
Ba hàm đều có chung cú pháp:
=RANK(number, ref, [order])
=RANK.AVG( number, ref, [order])
=RANK.EQ( number, ref, [order])
Trong đó:
+ number: đối số mà người dùng cần tìm thứ hạng
+ ref: một mảng hoặc tham chiếu dẫn đến danh sách các số
+ order: cách xếp hạng, gồm hai giá trị 0 và 1
( Nếu order = 0 thì Excel sẽ xếp hạng số giống với khi tham chiếu là danh sách theo thứ tự giảm dần
Nếu order = 1 thì Excel sẽ xếp hạng số giống với khi tham chiếu là danh sách theo thứ tự tăng dần)
Dưới đây là ví dụ khi sử dụng 3 hàm để xếp hạng theo thứ tự giảm dần
Nhìn vào kết quả thu được, hàm RANK.AVG và hàm RANK.EQ cho ra kết quả giống nhau.
Hàm RANK.AVG lúc này là thứ hạng trung bình. Nghĩa là nếu có thứ tự ngang nhau thì sẽ xếp trung bình cho số hạng đó( ví dụ: khi hai người đồng hạng => thứ hạng trung bình là + 0,5, mười người đồng hạng => thứ hạng trung bình là + 0,1)
Hàm RANK.EQ sẽ được xếp đồng hạng mà không cần tính trung bình hạng. Nghĩa là nếu hai người cùng đồng hạng thì sẽ xếp cùng 1 thứ hạng được làm tròn, chứ không phải thứ hạng lẻ.
3. Cách sử dụng hàm RANK có điều kiện trong Excel
3.1. Sử dụng hàm RANK theo 1 điều kiện
Nhìn vào bài tập dưới đây để phân tích các bước:
Bước 1: Xét ưu tiên môn toán khi điểm trung bình bằng nhau( những ai có điểm toán cao hơn thì được xếp hạng cao hơn)
Công thức:
=IF(COUNTIF($E$4:$E$13,E4)>1,RANK.EQ(B4,$B$4:$B$13,1),0)
Trong đó:
+ COUNTIF($E$4:$E$13,E4)>1 : đếm trong vùng từ E4:E13 theo từng giá trị đếm, xét điểm nào có kết quả đếm >1, tức là có điểm trùng
+ RANK.EQ(B4,$B$4:$B$13,1) : nếu trùng điểm trung bình, người dùng sẽ xếp hạng theo điểm toán thứ tự tăng dần, ai điểm thấp nhất sẽ xếp đầu
=> Nếu điểm trung bình trùng nhau thì tại những người bị trùng sẽ xếp hạng tăng dần, ưu tiên theo điểm môn toán
Khi người có điểm càng cao thì STT càng lớn, số thứ hạng này là số được cộng thêm vào điểm gốc để căn cứ xếp hạng với điều kiện ưu tiên. Số điểm càng lớn thì điểm cộng càng nhiều nên thứ tự ưu tiên phải xếp theo tăng tăng dần.
Bước 2: Tính lại điểm trung bình khi có điểm ưu tiên
Vì điểm trung bình gồm 2 số sau dấu phẩy nên điểm ưu tiên sẽ tính 3 chữ số sau dấu phẩy( lấy thứ hạng cột ưu tiên : 1000)
Khi đó, kết quả cho ra là điểm trung bình mới bao gồm điểm trung bình cũ + điểm xếp hạng với những điểm trùng nhưng xét ưu tiên điểm toán.
Đặt điểm xếp hạng ưu tiên phía sau điểm gốc để việc sắp xếp thứ hạng dễ phân biệt hơn.
Bước 3: Xếp hạng theo điểm trung bình mới
Sử dụng hàm RANK.EQ để xếp hạng trong bước này.
3.2. Sử dụng hàm RANK theo nhiều điều kiện
Trong trường hợp này, người dùng cần chú ý hai tiêu chí:
+ Thứ tự ưu tiên của các điều kiện, điều kiện nào cần xét trước, điều kiện nào cần xét sau
+ 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 nó.
Nhìn vào ví dụ dưới đây để phân tích các bước:Chú ý: STT 7 – Dương Quốc Đạt và STT 10 – Lê Bá Long vẫn đồng hạng 6 dù đã xét hạng theo điều kiện 1 nên cần xét thêm các điều kiện khác
Bước 1: Khi điểm trung bình bằng nhau cần xét ưu tiên điểm toán( giống bước 1 phần 3.1)
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
Công thức: =IF(AND(L4>0,COUNTIF(L$4:L$13,L4)>1,RANK.EQ(C4,C$4:C$13,1),0)
Trong đó:
+ L4>0 : chỉ xét khi xếp hạng ưu tiên môn Toán khác 0( đ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ó 2 đồng hạng)
+ AND(L4>0,COUNTIF(L$4:L$13,L4)>1) : xét thỏa mãn 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
Bước 3: Xét ưu tiên môn Hóa khi xếp hạng điểm Toán và điểm Lý bằng nhau
Công thức: =IF(AND(M4>0,COUNTIF(M$4:M$13,M4)>1),RANK.EQ(D4,D$4:D$13,1),0)
Trong đó:
+ M4>0 : chỏ xét khi xếp hạng ưu tiên môn Lý khác 0
+ COUNTIF(M$4:M$13,M4)>1) : chỉ xét khi xếp hạng ưu tiên môn Lý đồng hạng
+ AND(M4>0,COUNTIF(M$4:M$13,M4)>1): xét thỏa mãn 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 theo điểm ưu tiên của các điều kiện
Thứ tự ưu tiên của các môn sẽ được cộng vào điểm trung bình. Vì vậy mỗi cấp bậc ưu tiên đồng thời phải giảm đi 10 lần để nhìn vào kết quả có thể dễ dàng phân biệt được
Bước 5: Xếp hạng dựa theo điểm trung bình mới
Sử dụng hàm RANK.EQ xếp hạng theo thứ tự giảm dần( order = 0)
Kết luận
Bài viết đã hướng dẫn bạn cách sử dụng hàm RANK có điều kiện trong Excel. Bạn có thể thể lưu lại để dễ dàng tra cứu khi cần. Chúc các bạn học tập tốt!