Trong quá trình lập bảng số liệu như thống kê điểm cho học sinh để xếp loại học lực, xếp loại chất lượng doanh số, sản phẩm…bạn cần phải biết cách xếp loại trong excel. Bài viết dưới đây sẽ hỗ trợ bạn các thủ thuật, thao tác cài đặt hàm số để xếp loại nhanh chóng, linh hoạt trong excel.
Giới thiệu hàm số xếp loại trên excel
Để xếp loại học lực trên excel, người ta thường sử dụng hàm IF. Hàm số này có ý nghĩa là điều kiện tương ứng với ý nghĩa ‘Nếu – thì’.
Vận dụng hàm số IF linh hoạt, kết hợp với các hàm điều kiện logic như AND, OR, MIN, MAX… Bạn có thể xếp loại học lực của học sinh, sinh viên một cách dễ dàng, chính xác, thuận tiện cho phương án lên kế hoạch giảng dạy những học sinh yếu kém.
Cú pháp của hàm IF được viết như sau: If (Logic_test, value_if_true, value_if_false). Trong đó, các thành phần được định nghĩa là:
Logic_test: giá trị hoặc biểu thức logic
Value_if_false: giá trị của hàm nếu biểu thức logic nhận giá trị false
Value_if_true: giá trị của hàm nếu biểu thức logic nhận giá trị true
Xác định tiêu chí hàm số xếp loại trong excel
Nếu bạn là một giáo viên thì công việc nhập điểm của học sinh, phân loại học lực trung bình, khá, giỏi theo điểm trung bình sẽ phải thực hiện thường xuyên.
Với một danh sách học sinh dài, bao gồm nhiều lớp, ở nhiều bộ môn học… bạn sẽ phải cài đặt cách xếp loại trong excel như thế nào để nhanh chóng, không bị sai sót ảnh hưởng đến kết quả học tập của học sinh.
Chúng tôi sẽ hướng dẫn và phân tích cho các bạn nắm được cách quản lý điểm, xếp loại học sinh với các hàm số trong excel như thế nào qua những ví dụ thực tế, trực quan nhất.
Nếu bạn có một bảng quản lý sinh viên bao gồm họ đệm, tên, các bộ môn học Toán, Lý, Hóa, Tin, tổng điểm các bộ môn, điểm trung bình. Bạn cần dựa vào các dữ liệu cơ bản này để đề ra cách tính xếp loại trong excel phù hợp.
Muốn cài đặt hàm số để xếp loại cho sinh viên, bạn cần xác định các tiêu chí điều kiện xếp loại như sau:
Loại Giỏi nếu điểm trung bình trên 8
Loại Khá nếu điểm trung bình trong khoảng 6,5-8 điểm
Loại Trung Bình nếu điểm trung bình trong khoảng từ 5 đến dưới 6,5
Loại Yếu là điểm số còn lại không trong các khoảng trên.
Dựa trên cách xếp loại trong excel, chúng ta sẽ cài đặt hàm số mang ý nghĩa ‘nếu – thì’ là hàm IF, hàm số mang ý nghĩa ‘và’ là hàm AND.
Chúng ta nhập hàm số vào ô excel của cột ‘xếp loại’ có cú pháp như sau: =IF(I3>8,”Giỏi”,IF(AND(I3>=6.5,I3<=8),”Khá”,IF(AND(I3>=5,I3<6.5),”Trung bình”,”Yếu”)))
Các thành phần trong cú pháp hàm số xếp loại
Muốn hiểu Cách tính điểm trung bình và xếp loại trong excel dựa trên cú pháp của ví dụ ở trên, bạn cần hiểu rõ về các thành phần và ý nghĩa của hàm số:
IF(I3>8;”Giỏi”: Sinh viên được xếp loại Giỏi nếu giá trị số điểm ở cột điểm trung bình trên 8
IF(AND(I3>=5,I3<6.5),”Trung bình”,”Yếu”))): Sinh viên được xếp loại yếu nếu giá trị số điểm ở cột điểm trung bình lớn hơn hoặc bằng 5 và nhỏ hơn 6,5
IF(AND(I3>=6.5,I3<=8),”Khá”: Sinh viên xếp loại Khá nếu giá trị điểm số ở cột điểm trung bình lớn hơn hoặc bằng 6,5 và nhỏ hơn hoặc bằng 8.
Khi nắm được cách xếp loại trong excel với hàm số IF, AND, nhập điều kiện hàm số thành công, bạn sẽ thu được kết quả bảng danh sách sinh viên đã được xếp loại:
Bạn cũng có thể viết công thức xếp loại sinh viên ở ví dụ trên theo một công thức khác ngắn gọn hơn:
=IF(I3>8,”Giỏi”,IF(I3>=6.5,”Khá”,IF(I3>=5,”Trung bình”,”Yếu”)))
Điểm trung bình được xét điều kiện theo thứ tự giảm dần cấp độ, nên bạn có thể giảm bớt các điều kiện. Ở công thức rụt gọn với hàm IF, chúng ta hiểu như sau:
I3: Ô excel điểm trung bình làm điều kiện xét xếp loại, ý nghĩa của 3 thông số hàm IF theo thứ tự được hiểu như sau:
Hàm IF xét I3 lớn hơn 8
Hàm IF xét I3 lớn hơn hoặc bằng 6,5, tương ứng với I3 nhỏ hơn hoặc bằng 8
Hàm IF xét I3 lớn hơn hoặc bằng 5, tương ứng với I3 nhỏ hơn 6,5
Cách sử dụng kết hợp hàm AND, OR trong hàm IF để xếp loại
Cách xếp loại trong excel đối với ví dụ bảng thống kê danh sách sinh viên và điểm số ở trên có kèm theo xếp loại và học bổng. Bạn cần xác định điều kiện sinh viên được học bổng như sau:
Sinh viên không có điểm môn nào dưới 7 và xếp loại Giỏi thì được trị giá học bổng là 240000.
Sinh viên có một môn điểm dưới 7 và xếp loại Giỏi hoặc Khá thì được học bổng 180000.
Sinh viên xếp loại Trung bình không được hưởng học bổng.
Sinh viên xếp loại Yếu phải nộp vào học bổng là 180000 để học lại.
Ở ví dụ này, bạn sẽ phải thiết lập hàm IF kết hợp với các hàm MIN, AND,OR trong excel. Nối các hàm lại với nhau, bạn sẽ được cách xếp loại trong excel với cú pháp như sau:
IF(AND(J3=”Giỏi”,MIN(D3:G3)>=7),240000,IF(OR(AND(J3=”Giỏi”,MIN(D3:G3)<7),J3=”Khá”),180000,IF(J3=”Trung bình”,0,-180000)))
Ý nghĩa các thành phần được lý giải dưới đây:
J3: vị trí ô excel thuộc cột xếp loại cài đặt công thức hàm số xếp loại.
Hàm IF xét học bổng loại Giỏi là: IF(AND(J3=”Giỏi”,MIN(D3:G3)>=7),240000, hàm if thứ 2): Nếu sinh viên xếp loại giỏi và điểm các môn lớn hơn hoặc bằng 7 thì được học bổng 240000
Hàm IF xét học bổng loại Khá là: IF(OR(AND(J3=”Giỏi”,MIN(D3:G3)<7),J3=”Khá”),180000, hàm if thứ 3)
Ở đây, chúng ta ngầm hiểu theo hai tình huống xảy ra và đặt hàm OR. Đặt hàm AND với trường hợp sinh viên xếp loại giỏi và điểm nhỏ hơn 7, cụ thể như sau:
Sinh viên xếp loại Giỏi và điểm dưới 7: chỉ được học bổng loại Khá
Sinh viên xếp loại Khá
Hàm IF xét học bổng Trung Bình và Yếu là: IF(J3=”Trung bình”,0,-180000)
Sau khi nhập hàm số xếp loại, bạn sẽ thu về giá trị của cột học bổng như sau:
Trên đây là cách xếp loại trong excel với hàm số IF và các hàm số kết hợp AND, OR, MIN… Việc sử dụng thành thạo các hàm số xếp loại này sẽ giúp bạn xử lý công việc nhanh chóng, thuận tiện. Hãy ghi nhớ và thao tác thật chuẩn xác!
