Cách Tính Điểm Trung Bình Môn Và Xếp Loại Học Lực Bằng Excel

Trả lời nhanh: Tính điểm trung bình môn bằng công thức có hệ số, ví dụ =ROUND((SUM(C2:F2)+G2*2+H2*3)/(COUNT(C2:F2)+5),1), với hệ số giả định là TX hệ số 1, giữa kỳ hệ số 2, cuối kỳ hệ số 3. Điểm trung bình năm là (HK1 + HK2×2)/3. Xếp loại bằng IF lồng, xếp hạng bằng RANK.

Cuối học kỳ, cô giáo chủ nhiệm (một tình huống minh họa) ngồi trước bảng điểm của 40 em. Điểm miệng, điểm 15 phút, điểm giữa kỳ, điểm cuối kỳ nằm rải trong ba cuốn sổ và một tờ giấy nháp. Cô bấm máy tính từng em, ghi tay vào sổ, rồi sang em tiếp theo. Đến em thứ 25 thì nhận ra mình đã cộng nhầm một bài kiểm tra ở em thứ 9. Cô phải làm lại từ đầu, và đó mới chỉ là một môn trong chín môn.

Bảng tính sinh ra để làm đúng việc này. Bài viết hướng dẫn dựng bảng điểm từng bước: công thức điểm trung bình môn có hệ số, điểm trung bình năm, xếp loại, xếp hạng và cảnh báo môn thấp nhất. Công thức chạy được trên cả Excel lẫn Google Sheets.

Dựng bảng điểm đúng ngay từ đầu

Chuyện sai số hầu như bắt nguồn từ cách chia cột, chứ ít khi do công thức. Nếu mỗi môn nằm trên một sheet riêng, hãy giữ cùng một bố cục cho tất cả. Ví dụ dưới đây dùng bố cục sau cho một môn, dữ liệu bắt đầu từ dòng 2 và dòng 1 là tiêu đề:

• Cột A: số thứ tự
• Cột B: họ và tên học sinh
• Cột C đến F: bốn điểm thường xuyên (TX1, TX2, TX3, 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ông thức tự tính)

Mỗi học sinh một dòng, mỗi loại điểm một cột. Đừng gộp ô, đừng chèn dòng trống giữa danh sách, vì RANK và các hàm thống kê sau này đều cần vùng dữ liệu liền mạch. Bảng cuộn xuống vài chục dòng là bạn sẽ mất dòng tiêu đề khỏi tầm mắt. Cách cố định hàng tiêu đề nằm trong bài đóng băng hàng và cột trong Excel, làm một lần là xong.

Một thói quen đáng tập: ô điểm chưa nhập thì để trống hẳn, đừng gõ số 0. Số 0 là một điểm thật và sẽ kéo trung bình xuống. Ô trống thì hàm đếm bỏ qua được. Lát nữa công thức sẽ dựa vào chính điều này.

Công thức điểm trung bình môn có hệ số

Hệ số do trường hoặc giáo viên bộ môn quy định, nên bài này không khẳng định đó là quy chế chung. Để có ví dụ cụ thể, giả sử trường dùng hệ số như sau: bốn điểm thường xuyên mỗi điểm hệ số 1, giữa kỳ hệ số 2, cuối kỳ hệ số 3. Tổng hệ số khi nhập đủ là 4 + 2 + 3 = 9. Nếu trường của bạn dùng hệ số khác, chỉ cần sửa các con số 2, 3 và 5 trong công thức bên dưới.

Tại ô I2, gõ:

=ROUND((SUM(C2:F2)+G2*2+H2*3)/(COUNT(C2:F2)+5),1)

Phần SUM(C2:F2) cộng các điểm thường xuyên. G2*2 và H2*3 nhân điểm giữa kỳ và cuối kỳ với hệ số. Mẫu số là COUNT(C2:F2)+5: COUNT đếm số ô thường xuyên đã có điểm (mỗi ô hệ số 1), cộng 5 là tổng hệ số của giữa kỳ và cuối kỳ (2 + 3). Nhờ vậy, em nào mới có 3 điểm thường xuyên vẫn được tính đúng thay vì bị chia cho 9 rồi hụt điểm.

Thử với số liệu minh họa

Giả sử một học sinh (hoàn toàn là ví dụ) có bốn điểm thường xuyên 8, 7, 9, 8, giữa kỳ 7 và cuối kỳ 8. Cộng thường xuyên được 32. Giữa kỳ 7 × 2 = 14. Cuối kỳ 8 × 3 = 24. Tổng là 32 + 14 + 24 = 70, chia cho 4 + 5 = 9 được 7,78. Hàm ROUND làm tròn một chữ số thập phân thành 7,8.

Còn nếu em này mới có hai điểm thường xuyên, 8 và 7, chưa thi giữa kỳ và cuối kỳ thì sao? Công thức trên vẫn cho ra một con số, nhưng con số đó sai, vì ô trống G2 và H2 bị coi như 0 trong khi mẫu số vẫn cộng 5. Bạn nên bọc thêm điều kiện để chỉ tính khi đã đủ hai cột thi:

=IF(OR(G2="",H2=""),"",ROUND((SUM(C2:F2)+G2*2+H2*3)/(COUNT(C2:F2)+5),1))

Ô sẽ để trống cho đến khi nhập xong cả hai điểm thi. Cách này có lợi hơn một con số tạm tính: cuối kỳ nhìn cột I thấy ô trống là biết ngay còn thiếu điểm, không phải dò từng dòng.

Điểm trung bình năm từ hai học kỳ

Với cách tính phổ biến trong file mẫu của bài này, điểm trung bình năm bằng (HK1 + HK2×2)/3, tức học kỳ 2 nặng gấp đôi học kỳ 1. Đây cũng là quy ước do bạn đặt trong bảng tính, không phải điều bài này khẳng định là quy định chung. Dựng một sheet tổng hợp riêng: cột C là điểm trung bình môn học kỳ 1, cột D là điểm trung bình môn học kỳ 2, cột E là cả năm. Tại E2:

=ROUND((C2+D2*2)/3,1)

Ví dụ em học sinh ở trên có HK1 là 7,8 và HK2 là 8,4. Tính ra (7,8 + 8,4 × 2) / 3 = 24,6 / 3 = 8,2. Nếu em tiến bộ ở học kỳ 2, điểm năm sẽ nghiêng về phía học kỳ 2, và đó chính là mục đích của hệ số kép.

Muốn cột này tự trống khi học kỳ 2 chưa có điểm, dùng =IF(D2="","",ROUND((C2+D2*2)/3,1)). Đỡ cảnh giữa học kỳ 2 mà cột cả năm đã hiện ra một con số ngớ ngẩn.

Một chi tiết hay bị bỏ sót: cột E có thể dùng để phát hiện em nào bị tụt so với học kỳ trước. Công thức =IF(D2<C2-1,"Giảm điểm","") đánh dấu những em học kỳ 2 thấp hơn học kỳ 1 quá 1 điểm. Ngưỡng 1 điểm ở đây là giả định, bạn đặt bao nhiêu tùy lớp.

Xếp loại học lực bằng IF lồng

Ngưỡng xếp loại cũng do trường quy định nên cần để ra một chỗ riêng, đừng gõ cứng vào công thức rồi quên. Giả sử lớp của bạn dùng các mốc sau để minh họa: từ 8,0 trở lên là Giỏi, từ 6,5 là Khá, từ 5,0 là Trung bình, từ 3,5 là Yếu, dưới 3,5 là Kém. Với điểm trung bình năm ở ô E2, công thức IF lồng như sau:

=IF(E2="","",IF(E2>=8,"Giỏi",IF(E2>=6.5,"Khá",IF(E2>=5,"Trung bình",IF(E2>=3.5,"Yếu","Kém")))))

Công thức đọc từ trong ra ngoài như một chuỗi câu hỏi: có phải trống không, có từ 8 trở lên không, nếu chưa thì có từ 6,5 trở lên không, và cứ thế đến hết. Thứ tự các điều kiện rất quan trọng. Phải đi từ mốc cao xuống mốc thấp, vì nếu đặt mốc 5 lên trước thì em 9 điểm cũng bị xếp Trung bình.

Excel đời mới và Google Sheets có thêm IFS, đọc gọn hơn:

=IFS(E2="","",E2>=8,"Giỏi",E2>=6.5,"Khá",E2>=5,"Trung bình",E2>=3.5,"Yếu",TRUE,"Kém")

Dòng cuối TRUE,"Kém" là nhánh mặc định: mọi trường hợp còn lại đều rơi vào đây. Nếu máy của bạn là bản Excel cũ chưa có IFS thì dùng IF lồng ở trên, kết quả y hệt.

Điều đáng cân nhắc là trường nào cũng có thêm điều kiện phụ (ví dụ một môn dưới mốc nào đó thì không được xếp Giỏi). Công thức một cột như trên không bao quát nổi những điều kiện đó. Nếu trường bạn có quy tắc như vậy, hãy thêm cột kiểm tra riêng bằng MIN trên các môn rồi đối chiếu bằng mắt, đừng cố nhồi mọi thứ vào một công thức dài.

Xếp hạng lớp và tìm môn thấp nhất

Giả sử sheet tổng hợp có tên học sinh ở cột B, điểm trung bình cả năm của chín môn nằm ở cột C đến K (tiêu đề dòng 1 là tên môn), cột L là điểm trung bình các môn. Tại L2:

=IF(COUNT(C2:K2)=0,"",ROUND(AVERAGE(C2:K2),1))

Ở đây mọi môn tính cùng trọng số, vì đó là giả định cho ví dụ. Nếu trường bạn nhân hệ số cho Toán, Văn thì thay AVERAGE bằng SUMPRODUCT với một dòng hệ số riêng.

Xếp hạng bằng RANK

Tại M2, xếp hạng trong 40 dòng dữ liệu (dòng 2 đến 41):

=IF(L2="","",RANK(L2,$L$2:$L$41))

Dấu $ khóa vùng so sánh để khi kéo công thức xuống, vùng không bị trượt theo. Mặc định RANK xếp từ cao xuống thấp, điểm cao nhất được hạng 1. Hai em bằng điểm sẽ cùng hạng và hạng kế tiếp sẽ bị nhảy cách ra: hai em đồng hạng 3 thì em sau là hạng 5, không có hạng 4. Đó là hành vi bình thường chứ không phải lỗi. Nếu lớp muốn tách thứ hạng, hãy thêm một tiêu chí phụ như điểm Toán rồi so lại, còn chỉ dựa vào công thức thì không phân định được.

Môn thấp nhất của từng em

Cảnh báo môn thấp nhất hữu ích nhất khi gặp phụ huynh: thay vì nói chung chung "con còn yếu", bạn chỉ ngay một môn cụ thể. Tại N2:

=IF(COUNT(C2:K2)=0,"",INDEX($C$1:$K$1,MATCH(MIN(C2:K2),C2:K2,0)))

MIN(C2:K2) tìm điểm thấp nhất trong chín môn, MATCH(...,0) tìm vị trí của điểm đó trong dòng, INDEX lấy tên môn từ dòng tiêu đề tương ứng. Em có Toán 5,2 và các môn còn lại từ 6,5 trở lên (số giả định) thì ô N2 hiện ra "Toán". Nếu hai môn cùng thấp nhất, công thức chỉ trả về môn đứng trước trong dòng, nên đây là gợi ý để bạn xem lại chứ không thay được việc đọc bảng điểm.

Bạn có thể tô màu ô L bằng định dạng có điều kiện: nhỏ hơn 5 thì đỏ. Cả cột sáng lên một lượt, em nào cần để ý nhìn là thấy.

Những chỗ bảng điểm hay hỏng khi lớp đông lên

Công thức đúng chưa đủ. Bảng điểm thường hỏng ở ba chỗ, và cả ba đều tránh được.

Chỗ thứ nhất là nhập điểm bằng dấu phẩy hay dấu chấm. Máy cài định dạng Việt Nam hiểu "7,5" là số, nhưng máy cài định dạng Mỹ lại coi đó là chữ và công thức báo lỗi hoặc bỏ qua. Nếu hai giáo viên cùng nhập trên hai máy, hãy thống nhất một kiểu và kiểm tra bằng =ISNUMBER(C2) khi nghi ngờ.

Chỗ thứ hai là sửa tên học sinh giữa chừng. Nếu mỗi sheet môn gõ tên một lần, sau này một em đổi cách viết, bảng tổng hợp sẽ không khớp. Cách an toàn là giữ một danh sách gốc, các sheet khác lấy tên từ đó bằng tham chiếu ô. Cách đánh dấu danh sách và theo dõi chuyên cần có thể xem trong bài điểm danh học sinh bằng Excel.

Chỗ thứ ba là sao chép bảng cho lớp khác mà quên xóa điểm cũ. Với gia sư dạy nhiều lớp nhỏ thì lịch học cũng dễ chồng chéo như bảng điểm, và bài về xếp lịch dạy thêm không trùng giờ giải quyết đúng phần đó.

Khi nào nên dùng file dựng sẵn thay vì tự làm

Tự dựng theo hướng dẫn trên mất khoảng nửa ngày cho một lớp, nếu chỉ cần điểm số. Khó là phần còn lại: điểm danh theo buổi, hạnh kiểm, học phí, nhận xét gửi phụ huynh, in phiếu. Khi cần cả những thứ đó, mỗi tab thêm vào lại nối chéo công thức với tab khác và dễ gãy.

Nếu bạn không muốn tự nối từng tab, SheetStore có File Excel Quản Lý Học Sinh - Điểm Danh Và Kết Quả, giá 39.000đ, mua một lần dùng vĩnh viễn, không phí hằng tháng. Đây là file Excel 19 tab liên kết công thức, mở bằng Excel là dùng, không cần cài đặt, không cần tài khoản Google, và không cần Internet cho việc dùng hằng ngày. Bạn nhập họ tên một lần ở tab Danh Sách Lớp, mã học sinh tự sinh, các tab khác chạy theo mã đó. File tự tính điểm trung bình môn, điểm trung bình năm, xếp loại học lực và xếp hạng lớp, đồng thời cảnh báo học lực Yếu/Kém, vượt ngưỡng vắng, giảm điểm so với học kỳ 1 và cần phụ đạo.

Hai điều cần nói rõ trước khi mua: mỗi file dành cho một lớp, tối đa 40 học sinh, và sản phẩm này không có bản dùng thử. File có gợi ý nhận xét tự động theo học lực, xếp hạng, tiến bộ, chuyên cần, cùng chức năng in hàng loạt 40 phiếu báo cáo phụ huynh chỉ một lần bấm in. Hệ số và ngưỡng xếp loại chỉnh ở tab Cấu Hình, nên bạn đặt theo quy ước của trường mình.

Câu hỏi thường gặp

Vì sao ô điểm chưa nhập nên để trống thay vì gõ số 0?

Số 0 là một điểm thật nên sẽ kéo điểm trung bình xuống, còn ô trống thì hàm COUNT bỏ qua. Công thức điểm trung bình môn dựa vào điều này: COUNT(C2:F2)+5 chỉ đếm những ô thường xuyên đã có điểm. Nhờ vậy em mới có 3 điểm thường xuyên vẫn được tính đúng, không bị chia cho 9 rồi hụt điểm.

Nếu trường dùng hệ số khác thì phải sửa công thức điểm trung bình môn ở đâu?

Bạn sửa các con số 2, 3 và 5 trong công thức. Số 2 và 3 là hệ số của điểm giữa kỳ và cuối kỳ, số 5 là tổng hai hệ số đó ở mẫu số. Ví dụ trong bài giả định bốn điểm thường xuyên hệ số 1, giữa kỳ hệ số 2, cuối kỳ hệ số 3, tổng 9 khi nhập đủ.

Vì sao hai học sinh bằng điểm lại có hạng 3 và hạng 5, không có hạng 4?

Đó là cách hàm RANK hoạt động bình thường, không phải lỗi. Hai em bằng điểm cùng hạng, hạng kế tiếp bị nhảy cách ra. Muốn tách thứ hạng, bạn thêm một tiêu chí phụ như điểm Toán rồi so lại, vì chỉ dựa vào công thức RANK thì không phân định được.

Công thức xếp loại IF lồng cần chú ý điều gì về thứ tự điều kiện?

Phải đi từ mốc cao xuống mốc thấp. Nếu đặt mốc 5 lên trước thì em 9 điểm cũng bị xếp Trung bình. Với mốc minh họa trong bài, từ 8,0 là Giỏi, từ 6,5 là Khá, từ 5,0 là Trung bình, từ 3,5 là Yếu, dưới 3,5 là Kém. Excel đời mới và Google Sheets có thể dùng IFS cho gọn hơn.