Bảng điểm học sinh tự tính trung bình, xếp loại, tô điểm dưới 5

Tuần cuối học kỳ I, cô giáo dạy Toán của lớp 10A phải nộp bảng tổng hợp điểm môn của 40 học sinh cho văn phòng trường trước chiều thứ Sáu. Mỗi em có bốn điểm đánh giá thường xuyên, một điểm giữa kỳ và một điểm cuối kỳ. Lập bảng điểm học sinh trong MORA Sheet chỉ cần ba công thức và một lần đặt màu, thời gian còn lại dành cho việc soát điểm.

Sắp cột trước khi gõ công thức

Dòng 4 là tiêu đề cột. Cột A ghi STT, cột B họ và tên, bốn cột C, D, E, F là TX1 tới TX4, cột G điểm giữa kỳ, cột H điểm cuối kỳ, cột I điểm trung bình môn, cột J xếp loại. Bốn mươi học sinh nằm từ dòng 5 tới dòng 44.

Bài nào chưa kiểm tra thì để ô trống, đừng gõ số 0, vì số 0 sẽ bị tính vào trung bình. MORA Sheet dùng quy ước Việt Nam nên điểm gõ 7,5 với dấu phẩy, còn trong công thức các đối số ngăn nhau bằng dấu chấm phẩy.

Tính điểm trung bình môn có hệ số

1. Chọn I5:I44, tức cả cột điểm trung bình của lớp.
2. Gõ =ROUND((SUM(C5:F5)+2*G5+3*H5)/(COUNT(C5:F5)+5);1).
3. Nhấn Ctrl+Enter. Công thức được điền vào mọi ô đang chọn, hàng nào tự tính theo điểm của hàng đó.

Hàm COUNT chỉ đếm những ô TX có điểm, nên em có ba điểm thường xuyên vẫn được chia đúng cho 3 + 5 = 8. Hàm ROUND làm tròn một chữ số sau dấu phẩy, 7,46 thành 7,5. Em nào chưa có điểm giữa kỳ hay cuối kỳ thì điểm trung bình sẽ thấp bất thường; ghi chú lại để tính sau khi có điểm.

Xếp loại bằng hàm IF lồng nhau

Giả sử trường xếp loại theo ngưỡng từ 8,0 là Tốt, từ 6,5 là Khá, từ 5,0 là Đạt, dưới 5,0 là Chưa đạt. Chọn J5:J44, gõ =IF(I5>=8;"Tốt";IF(I5>=6,5;"Khá";IF(I5>=5;"Đạt";"Chưa đạt"))) rồi nhấn Ctrl+Enter.

Hàm IF xét lần lượt từ ngưỡng cao xuống thấp và dừng ở điều kiện đúng đầu tiên. Viết ngược thứ tự, xét ngưỡng 5 trước, thì em được 9,0 cũng bị xếp Đạt. Gõ tới =IF là gợi ý hàm hiện cú pháp và mô tả của hàm, đỡ nhầm thứ tự đối số. Đặt ngưỡng ra ô riêng để năm sau đổi quy chế chỉ sửa vài ô, hay dùng hàm IFS cho gọn, có trong bài xếp loại thi đua bằng hàm IF lồng nhau.

Báo cáo của tổ thường hỏi lớp có bao nhiêu em mỗi loại. Gõ =COUNTIF($J$5:$J$44;"Tốt") vào một ô bên dưới bảng, chép sang ba ô cạnh rồi đổi chữ trong ngoặc kép thành Khá, Đạt, Chưa đạt. Dấu $ giữ vùng J5:J44 đứng yên khi chép công thức.

Tô đỏ điểm trung bình dưới 5

1. Chọn I5:I44.
2. Mở thẻ Trang chủ, ở nhóm Số bấm Có điều kiện › Điều kiện › Nhỏ hơn....
3. Gõ 5 vào ô giá trị, chọn kiểu ô Xấu, bấm OK.

Ô dưới 5 chuyển nền đỏ nhạt, chữ đỏ. Sửa một điểm thành phần làm điểm trung bình lên từ 5 trở lên thì màu tự mất. Nên đặt màu cho cột trung bình thay vì các cột điểm thành phần, vì cột này luôn có số do công thức tính. Đặt nhiều mức màu trong một lần có trong bài tô màu chỉ tiêu đạt và chưa đạt.

Khoá cột công thức trước khi nộp

Bảng chuyển qua nhiều người, chỉ một lần gõ đè vào cột trung bình là sai cả hàng. Cách làm là mở khoá các cột điểm để vẫn nhập được, rồi khoá cả trang tính.

1. Chọn C5:H44, bấm chuột phải, chọn Định dạng ô..., mở thẻ Bảo vệ ô và bỏ chọn ô Bảo vệ. Bấm OK.
2. Mở thẻ Xem xét, ở nhóm Bảo vệ bấm Bảo vệ trang tính.
3. Gõ mật khẩu vào ô Mật khẩu, gõ lại ở ô Xác nhận, bấm OK.

Từ giờ người mở tệp vẫn nhập, sửa điểm thành phần được, còn cột trung bình và xếp loại không gõ đè được. Ghi mật khẩu vào sổ của tổ, vì quên là chính bạn cũng không sửa được công thức. Tệp lưu .xlsx như mặc định, văn phòng trường mở bằng Excel vẫn thấy đủ công thức và màu. Muốn chặn cả việc xoá hay đổi tên trang tính thì khoá thêm phần cấu trúc bảng tính.