Công thức Excel để tính điểm trung bình môn theo Thông tư 22/2021/TT-BGDĐT chỉ có một dòng: =ROUND(SUMPRODUCT(dãy điểm;dãy hệ số)/SUM(dãy hệ số);1). Phần còn lại là dựng bảng sao cho kéo được xuống cả lớp mà không sai.
Tôi viết bài này cho hai nhóm người. Giáo viên chủ nhiệm phải đối chiếu bảng điểm của cả lớp trước khi nộp, và phụ huynh muốn tự dựng một bảng theo dõi cho con qua từng học kỳ. Cả hai đều không cần biết Excel nâng cao, chỉ cần ba hàm.
Nếu chỉ tính một môn cho một người, mở công cụ tính điểm trung bình môn nhanh hơn dựng bảng.
Bố cục bảng nên đặt thế nào
Đặt mỗi học sinh một dòng, mỗi cột điểm một cột, và một dòng hệ số ở ngay dưới hàng tiêu đề. Cách này cho phép viết công thức một lần rồi kéo xuống toàn bộ danh sách lớp.
| Cột | Nội dung | Ví dụ |
|---|---|---|
| A | Họ tên | Danh sách lớp |
| B, C, D | Ba cột đánh giá thường xuyên | 8 · 6,5 · 7,5 |
| E | Điểm giữa kỳ | 8,25 |
| F | Điểm cuối kỳ | 7,5 |
| Dòng 2 | Hệ số tương ứng | 1 · 1 · 1 · 2 · 3 |
Dòng hệ số nằm cố định, nên khi viết công thức phải khóa nó lại bằng dấu đô la. Đây là chi tiết quyết định bảng của bạn đúng hay sai.
Công thức đầy đủ cho một học sinh
Với dữ liệu ở dòng 3, công thức trong ô G3 là =ROUND(SUMPRODUCT(B3:F3;$B$2:$F$2)/SUM($B$2:$F$2);1). Hàm SUMPRODUCT nhân từng cặp điểm với hệ số rồi cộng lại; SUM cho tổng hệ số; ROUND làm tròn một chữ số như quy chế yêu cầu.
Kiểm tra bằng bộ số ở bảng trên: 8 + 6,5 + 7,5 + 2 × 8,25 + 3 × 7,5 = 61, chia cho tổng hệ số 8 được 7,625, làm tròn thành 7,6. Nếu ô của bạn hiện 7,6 thì công thức đúng.
Kéo G3 xuống hết danh sách. Nhờ dấu đô la, vùng hệ số không trôi theo.
Vì sao không dùng AVERAGE
Hàm AVERAGE coi mọi ô như nhau, nên nó cho 7,55 với bộ số trên thay vì 7,625. Sai lệch nhỏ nhưng đủ để đổi con số cuối cùng sau khi làm tròn, và với môn đang ở ranh giới 6,5 hay 8,0 thì đó là ranh giới thật.
Chỉ dùng AVERAGE khi mọi hệ số bằng nhau — chẳng hạn khi tính điểm trung bình các môn trong một học kỳ, nơi các môn ngang nhau.
Thêm cột điểm cả năm
Điểm cả năm cần hai ô đầu vào là điểm trung bình môn của hai học kỳ. Công thức: =ROUND((H3+2*I3)/3;1) với H là học kỳ 1 và I là học kỳ 2.
Đừng tính điểm cả năm trực tiếp từ các cột điểm gốc của hai kỳ. Quy chế lấy hai con số đã làm tròn của hai học kỳ, nên tính tắt sẽ ra kết quả lệch 0,1 ở một số trường hợp — và học bạ mới là con số đúng về mặt hành chính.
Ba lỗi hay gặp khi kéo công thức
- Quên khóa vùng hệ số: các dòng dưới nhân với ô trống, kết quả bằng 0 hoặc báo lỗi chia cho 0.
- Ô trống bị coi là 0: học sinh chưa có cột điểm nào đó sẽ bị tính như được 0 điểm. Cách xử lý là để trống ô hệ số tương ứng hoặc dùng bảng riêng cho học sinh thiếu cột.
- Dấu phân cách sai: một số máy dùng dấu phẩy thay vì chấm phẩy giữa các đối số. Nếu Excel báo lỗi cú pháp, đổi dấu là chạy.
Lỗi thứ hai là lỗi tôi thấy gây tranh cãi nhiều nhất trong nhóm lớp, vì nó im lặng — bảng vẫn cho ra một con số trông hợp lý, chỉ là thấp hơn thực tế.
Định dạng cho đúng kiểu Việt Nam
Excel mặc định hiển thị dấu chấm thập phân theo cấu hình máy. Muốn ra 7,6 thay vì 7.6, đổi trong phần cài đặt vùng của hệ điều hành, đừng thay bằng cách gõ tay — gõ tay biến số thành chữ và mọi công thức phía sau ngừng hoạt động.
Điều bài này không nói hết
Lời khuyên thường gặp là “dùng Excel cho nhanh”. Sau khi dựng bảng vài lần, tôi khuyên cụ thể hơn: dùng Excel khi bạn có từ mười dòng trở lên và cần lặp lại nhiều kỳ; còn với một hai môn thì bảng tính lại chậm hơn và dễ sai hơn tính trực tiếp.
Một điều bảng tính không làm thay bạn: kiểm tra số cột thường xuyên của môn có đúng theo số tiết hay không, và cột nào thực sự là cột giữa kỳ. Đó là phần phải đọc từ bảng điểm của trường. Sau khi có bảng, tôi vẫn khuyên kiểm tra chéo một dòng bằng công cụ tính điểm theo Thông tư 22 — nếu hai kết quả khớp, cả bảng khớp.
Hỏi đáp về Excel và điểm số
Có mẫu file Excel sẵn không?
Trang chưa có file mẫu để tải. Bảng ở trên dựng lại trong ba phút, và tự dựng thì bạn hiểu công thức của mình, đó là điều quan trọng hơn với một bảng điểm.
Google Sheets có dùng được công thức này không?
Có, cả ba hàm SUMPRODUCT, SUM và ROUND đều giống nhau. Chỉ khác dấu phân cách đối số, thường là dấu phẩy trong Google Sheets.
Làm sao tính điểm trung bình các môn trong một học kỳ?
Dùng AVERAGE trên dãy điểm trung bình môn, vì các môn không có hệ số. Cách hiểu con số này ở bài tính điểm trung bình học kỳ.
Muốn tính điểm cần đạt ở bài cuối kỳ thì viết công thức thế nào?
Giải ngược: =(mục_tiêu*tổng_hệ_số − tổng_điểm_đã_có)/3. Nhưng phải nhớ mục tiêu là ngưỡng trước làm tròn, ví dụ 7,95 cho mục tiêu 8,0 — công cụ tính điểm cần đạt đã xử lý sẵn phần đó.