Xếp hạng dữ liệu trong Excel là một thao tác phổ biến, thường sử dụng hàm RANK. Bài viết này sẽ hướng dẫn bạn tìm hiểu sâu về hàm RANK trong các phiên bản Excel từ 2010 trở lên, bao gồm cách xếp hạng theo một hoặc nhiều điều kiện.
Nội dung chính:
- Phân biệt các hàm RANK, RANK.AVG và RANK.EQ.
- Cách xếp hạng theo một điều kiện.
- Cách xây dựng công thức xếp hạng theo nhiều điều kiện (theo thứ tự ưu tiên).
Bài viết sẽ sử dụng ví dụ minh họa sau:
Bảng điểm học sinh
Phần 1: Phân Biệt Các Hàm RANK, RANK.AVG và RANK.EQ
Từ phiên bản Excel 2010, xuất hiện hai hàm RANK.AVG và RANK.EQ thay thế cho hàm RANK truyền thống.
Cú pháp của cả ba hàm đều tương tự:
=RANK(number, ref, [order])
=RANK.AVG(number, ref, [order])
=RANK.EQ(number, ref, [order])Trong đó:
number: Giá trị cần xếp hạng.ref: Vùng dữ liệu chứa các giá trị để so sánh.order: Thứ tự xếp hạng (0: giảm dần, 1: tăng dần).
Áp dụng vào ví dụ (xếp hạng giảm dần):
So sánh kết quả xếp hạng
- RANK và RANK.AVG cho kết quả giống nhau khi không có giá trị trùng lặp. RANK.AVG sẽ tính thứ hạng trung bình nếu có đồng hạng.
- RANK.EQ xếp hạng đồng hạng mà không tính trung bình, làm tròn thứ hạng.
Mục tiêu cuối cùng của chúng ta là bảng kết quả sau:
Kết quả xếp hạng theo nhiều điều kiện
Phần 2: Xếp Hạng Theo Một Điều Kiện
Yêu cầu: Xếp hạng theo điểm trung bình, nếu đồng hạng thì ưu tiên điểm Toán cao hơn.
Ví dụ xếp hạng theo điều kiện
Các bước thực hiện:
Bước 1: Xét ưu tiên môn Toán:
Xét ưu tiên môn Toán
Công thức: =IF(COUNTIF($E$4:$E$13,E4)>1,RANK.EQ(B4,$B$4:$B$13,1),0)
Công thức này kiểm tra xem có điểm trung bình trùng lặp không. Nếu có, nó sẽ xếp hạng dựa trên điểm Toán (tăng dần).
Bước 2: Tính điểm trung bình mới:
Tính điểm trung bình mới
Điểm trung bình mới được tính bằng cách cộng điểm trung bình ban đầu với điểm ưu tiên (thứ hạng Toán/1000).
Bước 3: Xếp hạng dựa trên điểm trung bình mới:
Xếp hạng cuối cùng
Sử dụng hàm RANK.EQ để xếp hạng điểm trung bình mới.
Phần 3: Xếp Hạng Theo Nhiều Điều Kiện
Yêu cầu: Xếp hạng theo điểm trung bình, nếu đồng hạng thì ưu tiên Toán > Lý > Hóa.
Ví dụ xếp hạng theo nhiều điều kiện
Các bước thực hiện:
Bước 1: Xét ưu tiên Toán (tương tự Phần 2):
Ưu tiên Toán
Bước 2: Xét ưu tiên Lý (nếu đồng hạng Toán):
Ưu tiên Lý
Công thức: =IF(AND(L4>0,COUNTIF(L$4:L$13,L4)>1),RANK.EQ(C4,C$4:C$13,1),0)
Bước 3: Xét ưu tiên Hóa (nếu đồng hạng Toán và Lý):
Ưu tiên Hóa
Công thức: =IF(AND(M4>0,COUNTIF(M$4:M$13,M4)>1),RANK.EQ(D4,D$4:D$13,1),0)
Bước 4: Tính điểm trung bình mới (cộng dồn điểm ưu tiên):
Điểm trung bình mới
Bước 5: Xếp hạng dựa trên điểm trung bình mới (giảm dần):
Xếp hạng cuối cùng
Qua bài viết, bạn đã nắm được cách sử dụng hàm RANK và các biến thể của nó, cũng như cách xếp hạng theo điều kiện trong Excel. Việc thành thạo các kỹ thuật này sẽ giúp bạn xử lý dữ liệu hiệu quả hơn.
Discussion about this post