Tài liệu Skkn tin học thiết kế mẫu bảng tổng hợp điểm cho giáo viên chủ nhiệm bằng excel và vba

SỞ GIÁO DỤC VÀ ĐÀO TẠO GIA LAI TRƯỜNG THPT NGUYỄN BỈNH KHIÊM TỔ: TIN HỌC  THIẾT KẾ MẪU BẢNG TỔNG HỢP ĐIỂM CHO GIÁO VIÊN CHỦ NHIỆM BẰNG EXCEL VÀ VBA Tên tác giả: ĐÀO ĐÌNH NGỌC Chức vụ: Tổ trưởng Năm thực hiện: 2013-2014 0 PHẦN. ĐẶT VẤN ĐỂ Việc ứng dụng Công nghệ thông tin (CNTT) trong trường học đang được các cấp khuyến khích, từ việc quản lý học sinh, giáo viên, cơ sở vật chất đến công việc văn phòng. Sử dụng CNTT trong quản lý và giảng dạy ngày càng được chú trọng, góp phần giúp cho công việc quản lý và giảng dạy đạt hiệu quả cao hơn. Việc tổng hợp điểm bộ môn của giáo viên chủ nhiệm gặp nhiều khó khăn vì trình độ CNTT của một số giáo viên còn hạn chế nên thông tin về kết quả học tập của học sinh thường xuyên bị sai sót dẫn đến việc tổng hợp kết quả toàn trường bị sai lệch. Năm học 2010 - 2011 và học kỳ 1 năm học 2011 - 2012 việc đánh giá xếp loại học sinh vẫn thực hiện theo Quyết định số 40/2006/QĐBGDĐT và thông tư số 51/2008/QĐ-BGDĐT. Thời gian này tôi đã thiết kế mẫu Bảng tổng hợp điểm cho Giáo viên chủ nhiệm để tính toán, thống kê, tổng hợp kết quả học lực và hạnh kiểm của học sinh. Vì vậy đã hạn chế được các sai sót trong việc tính điểm trung bình học kỳ, năm học, việc xếp loại học lực và thống kê thông tin liên quan cũng rất chính xác. Mẫu bảng tổng hợp điểm đã được giáo viên đánh giá rất tốt. Từ học kỳ 2, năm học 2011 – 2012 đến nay, việc tính điểm trung bình các môn học và đánh giá xếp loại học sinh thực hiện theo thông tư số 58/2011/TT-BGDĐT. Việc thay đổi thường xuyên cách đánh giá, xếp loại học sinh cũng làm cho giáo viên gặp khó khăn, nhiều giáo 1 viên vẫn bị nhầm lẫn cách tính điểm trung bình các môn học, xếp loại học lực của học sinh theo thông tư cũ. Để giúp cho giáo viên chủ nhiệm lớp thuận tiện trong việc tính toán, xếp loại, tổng hợp kết quả học tập của học sinh của lớp mình, tôi đã cập nhật lại mẫu Bảng tổng hợp điểm theo những thay đổi mới nhất trong trong thông tư 58 và bổ sung thêm chức năng lọc danh sách khen thưởng được viết bằng ngôn ngữ lập trình VBA. Sử dụng mẫu Bảng tổng hợp điểm này đã khắc phục được những sai sót trong việc tính điểm và xếp loại học lực của học sinh, thao tác thực hiện rất đơn giản, việc thông kê, tổng hợp rất nhanh và chính xác. Bên cạnh việc thiết kế mẫu Bảng tổng hợp cho giáo viên chủ nhiệm, để thuận tiện cho Ban giám hiệu trong việc tổng hợp kết quả toàn trường về: Thông tin học sinh, kết quả học lực, hạnh kiểm, danh sách lên lớp, thi lại, ở lại, rèn luyện hạnh kiểm trong hè… tôi cũng đã thiết kế phần mềm có chức năng tổng hợp các thông tin từ mẫu Bảng tổng hợp của giáo viên chủ nhiệm nộp về. Sử dụng mẫu Bảng tổng hợp điểm tuyệt đối chính xác theo thông tư 58 và hướng dẫn thực hiện thông tư 58 (Công văn số: 2642/BGDĐTGDTrH) kết quả đã được giáo viên chủ nhiệm, Ban giám hiệu kiểm chứng, mọi sai sót đã được cập nhật kịp thời. Mẫu Bảng tổng hợp đã được các trường THPT trong huyện Chư Sê sử dụng và kết quả phản hồi là khá tốt. PHẦN II. NỘI DUNG 1. Chuẩn bị thiết kế - Cơ bản sử dụng phần mềm MicroSoft Excel và một Modul VBA để lọc danh sách khen thưởng từ kết quả đã có. - Mẫu BTH được thiết kế bao gồm 02 sheet được đặt tên là: Mat_truoc và Mat_sau, hình H1, H2 (phụ lục) 2 - Sheet Mat_truoc chứa toàn bộ thông tin của học sinh như: Tên lớp, giáo viên chủ nhiệm, họ tên, ngày sinh, giới tính, dân tộc, điểm các môn học theo HK1, HK2, cả năm, kết quả học lực, hạnh kiểm và kết quả thống kê chi tiết theo từng môn học. - Sheet Mat_sau thống kê sĩ số, nam, nữ, dân tộc, nữ dân tộc, số lượng, tỉ lệ học sinh Giỏi, Khá, TB, Yếu, Kém và danh sách khen thưởng theo học kỳ, cả năm. - Các hàm và Modul sử dụng trong đề tài bao gồm: + Count, Counta, Countif, Sum, Round, If, And, Or, Sumproduct. + Form frmhl và Modul lọc danh sách khen(itnbkcs) - Để hạn chế việc nhập dữ liệu ngoại lai ảnh hưởng tới việc thống kê, tôi đã thiết kế dạng Combo box (chức năng Data Validation...) để chọn các thông tin như giới tính, dân tộc, tên lớp, học kỳ, năm học. 3 - Việc tính điểm trung bình các môn học theo học kỳ, năm học tương đối đơn giản nhưng xây dựng hàm xếp loại học lực khá phức tạp, tôi đã phải sử dụng kỹ thuật gộp dữ liệu từ nhiều cột. 2. Thiết kế chi tiết - Xếp loại học lực của học sinh trong thông tư 58 chia làm hai trường hợp: + Trường hợp 1: Xếp loại theo đúng kết quả ĐTB học kỳ và ĐTB môn học. + Trường hợp 2: Điều chỉnh những trường hợp đặc biệt (bị khống chế) - Khi xây dựng công thức xếp loại học lực phải sử dụng rất nhiều hàm: If, And, Or, các hàm này đã vượt quá số lượng cho phép của Excel trong một ô (Cell). Lúc này thật sự rất khó để tiếp tục xây dựng công thức chung và cuối cùng tôi đã quyết định tách công thức chung thành hai công thức độc lập ứng với hai trường hợp đặt ở hai ô (Cell) khác nhau. Kết quả xếp loại học lực của học sinh chính là sự kết hợp kết quả ở hai ô. - Trường hợp 1 ta có công thức: Xếp loại chung theo thông tư 58/2011/TT-BGDĐT =IF(OR($B$3="",AS8="",AV8="",AV8="Ban"),"",IF(AND(AV8 >=8,OR(I8>=8,X8>=8),I8>=6.5,L8>=6.5,O8>=6.5,R8>=6.5,U 8>=6.5,X8>=6.5,AA8>=6.5,AD8>=6.5,AG8>=6.5,AJ8>=6.5,AM 8>=6.5,AP8>=6.5,AS8="Đ"),"Giỏi",IF(AND(AV8>=6.5,OR(I8 >=6.5,X8>=6.5),I8>=5,L8>=5,O8>=5,R8>=5,U8>=5,X8>=5,AA 8>=5,AD8>=5,AG8>=5,AJ8>=5,AM8>=5,AP8>=5,AS8="Đ"),"Khá ",IF(AND(AV8>=5,OR(I8>=5,X8>=5),I8>=3.5,L8>=3.5,O8>=3 .5,R8>=3.5,U8>=3.5,X8>=3.5,AA8>=3.5,AD8>=3.5,AG8>=3.5 4 ,AJ8>=3.5,AM8>=3.5,AP8>=3.5,AS8="Đ"),"TB",IF(AND(AV8> =3.5,AND(I8>=2,L8>=2,O8>=2,R8>=2,U8>=2,X8>=2,AA8>=2,A D8>=2,AG8>=2,AJ8>=2,AM8>=2,AP8>=2)),"Yếu","Kém"))))) 5 - Trường hợp 2 ta sử dụng công thức: Điều chỉnh theo thông tư 58/2011/TT-BGDĐT (khống chế) Đạt mức Giỏi =IF(OR($B$3="",AS8="",AV8=""),"",IF(AND(AV8>=8,OR(I8 >=8,X8>=8),AS8="Đ",SUM(I8>=6.5,L8>=6.5,O8>=6.5,R8>=6 .5,U8>=6.5,X8>=6.5,AA8>=6.5,AD8>=6.5,AG8>=6.5,AJ8>=6 .5,AM8>=6.5,AP8>=6.5)=11,SUM(AND(I8>=3.5,I8<5),AND(L 8>=3.5,L8<5),AND(O8>=3.5,O8<5),AND(R8>=3.5,R8<5),AND (U8>=3.5,U8<5),AND(X8>=3.5,X8<5),AND(AA8>=3.5,AA8<5) ,AND(AD8>=3.5,AD8<5),AND(AG8>=3.5,AG8<5),AND(AJ8>=3. 5,AJ8<5),AND(AM8>=3.5,AM8<5),AND(AP8>=3.5,AP8<5))=1) ,"Khá",IF(AND(AV8>=8,OR(I8>=8,X8>=8),AS8="CĐ",SUM(I8 >=6.5,L8>=6.5,O8>=6.5,R8>=6.5,U8>=6.5,X8>=6.5,AA8>=6 .5,AD8>=6.5,AG8>=6.5,AJ8>=6.5,AM8>=6.5,AP8>=6.5)=12) ,"TB",IF(AND(AV8>=8,OR(I8>=8,X8>=8),AS8="Đ",SUM(I8>= 6.5,L8>=6.5,O8>=6.5,R8>=6.5,U8>=6.5,X8>=6.5,AA8>=6.5 ,AD8>=6.5,AG8>=6.5,AJ8>=6.5,AM8>=6.5,AP8>=6.5)=11,SU M(AND(I8>=2,I8<3.5),AND(L8>=2,L8<3.5),AND(O8>=2,O8<3 .5),AND(R8>=2,R8<3.5),AND(U8>=2,U8<3.5),AND(X8>=2,X8 <3.5),AND(AA8>=2,AA8<3.5),AND(AD8>=2,AD8<3.5),AND(AG 8>=2,AG8<3.5),AND(AJ8>=2,AJ8<3.5),AND(AM8>=2,AM8<3.5 ),AND(AP8>=2,AP8<3.5))=1),"TB","")))) Đạt mức Khá =IF(OR($B$3="",AS8="",AV8=""),"",IF(AND(AV8>=6.5,OR( I8>=6.5,X8>=6.5),AS8="CĐ",SUM(I8>=5,L8>=5,O8>=5,R8>= 5,U8>=5,X8>=5,AA8>=5,AD8>=5,AG8>=5,AJ8>=5,AM8>=5,AP8 >=5)=12),"TB",IF(AND(AV8>=6.5,OR(I8>=6.5,X8>=6.5),AS 8="Đ",SUM(I8>=5,L8>=5,O8>=5,R8>=5,U8>=5,X8>=5,AA8>=5 ,AD8>=5,AG8>=5,AJ8>=5,AM8>=5,AP8>=5)=11,SUM(AND(I8>= 2,I8<3.5),AND(L8>=2,L8<3.5),AND(O8>=2,O8<3.5),AND(R8 >=2,R8<3.5),AND(U8>=2,U8<3.5),AND(X8>=2,X8<3.5),AND( AA8>=2,AA8<3.5),AND(AD8>=2,AD8<3.5),AND(AG8>=2,AG8<3 .5),AND(AJ8>=2,AJ8<3.5),AND(AM8>=2,AM8<3.5),AND(AP8> =2,AP8<3.5))=1),"TB",IF(AND(AV8>=6.5,OR(I8>=6.5,X8>= 6.5),AS8="Đ",SUM(I8>=5,L8>=5,O8>=5,R8>=5,U8>=5,X8>=5 ,AA8>=5,AD8>=5,AG8>=5,AJ8>=5,AM8>=5,AP8>=5)=11,SUM(I 8<2,L8<2,O8<2,R8<2,U8<2,X8<2,AA8<2,AD8<2,AG8<2,AJ8<2 ,AM8<2,AP8<2)=1),"Yếu","")))) Các công thức trên minh họa cho cột xếp loại học lực Cả năm học, công thức xếp loại học lực cho từng học kỳ đơn giản ta chỉ cần sao chép công thức sang các cột tương ứng (địa chỉ tương đối sử dụng trong công thức tương ứng với các cột điểm các môn học...). Ngoài ra còn sử dụng một số công thức để gộp kết quả xếp loại tương đối đơn giản quý thầy cô có thể theo dõi trực tiếp trong các ô của Mẫu bảng tổng hợp. 6 Trong quá trình thống kê thông tin về kết quả học tập của học chẳng hạn như: Đếm số lượng học sinh nữ dân tộc đạt kết quả học lực trung bình, khi đó ta sử dụng hàm SUMPRODUCT để thống kê rất phù hợp. Cấu trúc hàm SUMPRODUCT(array1,array2,array3,...) Array - Mảng dữ liệu là một tập hợp dãy giá trị liên tiếp trong một khoảng nào đó. VD A1:G1 hoặ A1:A9,... Để thực hiện thống kê số liệu gồm nhiều nhiều điều kiện, nếu ta sử dụng hàm SUM(IF(array1,array2,…)) thì khi thực hiện công thức ta phải nhấn tổ hợp phím Ctrl+Shift+Enter, khi cập nhật công thức có thể gây ra nhiều phiền toái. Nhưng khi thay thế bằng hàm SUMPRODUCT thì việc thực hiện chỉ cần nhấn Enter như các công thức thông thường mặc dù đối số sử dụng trong hàm vẫn là các mảng (array). Ví dụ: Thống kê số lượng học sinh nữ dân tộc trong danh sách sau: Trong hàm SUMPRODUCT vùng điều kiện E3:D14 bằng giá trị “Nữ”, vùng điều kiện F3:F14 giá trị khác “Kinh” (nếu cần có thể thêm một mảng F3:F14<>””). Để kết hợp các điều kiện này, thay vì dùng phép và (And) 7 thì ta sử dụng dấu “*”. Với các thống kê có thêm nhiều điều kiện ta hoàn toàn có thể sử dụng tương tự như trên. - Modul lọc danh sách khen(itnbkcs) Option Explicit Dim ws1, ws2 As Worksheet Dim vdckhen, stt, hodem, ten, cdh, dh As String Dim gioi, kha, tientien, tb, hl, chk As String Dim c As Range Dim dem As Byte Sub TaoMenu() Dim cb As CommandBar Dim cpop As CommandBarPopup Dim cbtn As CommandBarButton 'Lay tham chieu den thanh trinh don Set cb = Application.CommandBars("Worksheet Menu Bar") 'Tao Menu: "DDN_KHEN" (CommandBarPopup) Set cpop = cb.Controls.Add(msoControlPopup, , , , True) cpop.Caption = "DDN_KHEN" 'Them Loc danh sach khen vao Menu:"DDN_KHEN" Set cbtn = cpop.Controls.Add(msoControlButton, , , , True) 'Gan thuoc tinh cho Loc danh sach khen cbtn.Caption = "Loc danh sach khen" 'Gan tieu de cbtn.OnAction = "nobisu" 'Gan ma lenh End Sub Sub auto_open() TaoMenu End Sub Sub khoitao()’Chuẩn bị các nhãn, gán tên và kích hoạt Shee. gioi = frmhl.lbgioi kha = frmhl.lbkha tientien = frmhl.lbtt Set ws1 = Sheets("Mat_truoc") ws1.Activate Set ws2 = Sheets("Mat_sau") dem = 0 End Sub Sub dchk1()‘ Quy định địa chỉ của HK1 hl = "AW8:AW62" chk = "AZ" vdckhen = "A22:G71" stt = "A" hodem = "B" ten = "F" cdh = "G" End Sub Sub dchk2()‘ Quy định địa chỉ của HK2 hl = "AX8:AX62" 8 chk = "BA" vdckhen = "J22:P71" stt = "J" hodem = "K" ten = "O" cdh = "P" End Sub Sub dccn() ‘ Quy định địa chỉ của cả năm hl = "AY8:AY62" chk = "BB" vdckhen = "S22:Y71" stt = "S" hodem = "T" ten = "X" cdh = "Y" End Sub Sub capnhat()’ lọc danh sách 1 học sinh và danh hiệu tương ứng ws2.Activate ws2.Cells(dem + 21, stt) = dem ws2.Cells(dem + 21, hodem) = ws1.Cells(c.Row, "B") ws2.Cells(dem + 21, ten) = ws1.Cells(c.Row, "C") ws2.Cells(dem + 21, cdh) = dh End Sub Sub locdskhen() ws2.Range(vdckhen).ClearContents ’Xóa nội dung cũ For Each c In ws1.Range(hl) If (c.Value <> "") And (Trim(c.Value) = gioi) And (Trim(ws1.Cells(c.Row, chk)) = "T") Then dem = dem + 1 dh = gioi capnhat Else If (c.Value <> "") And ((Trim(c.Value) = gioi) Or (Trim(c.Value) = kha)) And ((Trim(ws1.Cells(c.Row, chk) = "T")) Or (Trim(ws1.Cells(c.Row, chk) = "K"))) Then dem = dem + 1 dh = tientien capnhat End If End If Next End Sub Sub nobisu() tb=InputBox("Ban hay nhap [HK1, HK2 hoac CN]", "Thong bao") ‘ Hiển thị hộp thoại cho phép người dùng nhập học kỳ cần lọc If UCase(tb) = "HK1" Then khoitao dchk1 locdskhen Else If UCase(tb) = "HK2" Then 9 khoitao dchk2 locdskhen Else If UCase(tb) = "CN" Then khoitao dccn locdskhen Else MsgBox ("Ban nhap Hoc ky chua dung!") End If End If End If End Sub 3. Kết quả đạt được, rút kinh nghiệm 2.1. Kết quả - Sử dụng mẫu bảng tổng hợp giúp giáo viên chủ nhiệm nắm được tình hình học tập của lớp mình nhanh chóng. - Hạn chế đến mức thấp nhất các sai sót (nếu tính toán, tổng hợp thủ công sẽ phát sinh nhiều sai sót) - Thao tác tác đơn giản, độ chính xác cao. - Việc tổng hợp thông tin của toàn trường nhanh và chính xác. 2.2. Rút kinh nghiệm - Ban đầu khi xây dựng công thức xếp loại học lực trong 1 ô, tôi đã làm nhiều lần mà vẫn bị lỗi vì trong Excel hạn chế số lượng các hàm IF, AND, OR... nhưng khi tách nhỏ công thức trong một ô ra nhiều ô rồi kết hợp lại thì đã giải quyết được lỗi này. - Ngoài ra tôi còn sử dụng chức năng định dạng theo điều kiện, đối với các điểm số dưới 3,5 thì giá trị tự động đổi thành màu đỏ, việc này thuận tiện cho giáo viên trong việc hướng dẫn học sinh chọn, đăng ký môn thi lại cuối năm. 10 Định dạng theo điều kiện B1. Chọn điều kiện B2. Định dạng font style, color... 4. Triển khai ứng dụng trong thực tế * Yêu cầu Máy tính đã cài đặt phần mềm Microsoft Excel từ 2003 trở lên, đã kích hoạt chức năng Macro. * Cập nhật mẫu bảng tổng hợp Từ mẫu bảng tổng hợp đã có danh sách học sinh của lớp mình, giáo viên chủ nhiệm kiểm dò lại các thông tin, điều chỉnh cho chính xác rồi tiến hành nhập hoặc sao chép điểm trung bình các môn học vào mẫu này, các thông tin về học sinh sẽ tự động được tính toán, tổng hợp sau cùng giáo viên chủ nhiệm xếp hạnh kiểm (hoặc dùng mẫu trống để cập nhật thông tin) * Thao tác lọc danh sách khen thưởng Với Office Xp/2003 thì thực hiện chức năng Lọc danh sách khen thưởng: Nháy chọn DDN_KHEN→Loc danh sach khen Với Office 2007/2010/2013 thì thực hiện chức năng Lọc danh sách khen thưởng: Nháy chọn Add-Ins→DDN_KHEN→Loc danh sach khen 11 2 1 3 Hộp thoại nhập yêu cầu nhập học kỳ cần lọc danh sách khen thưởng 2 1 PHẦN III. KẾT LUẬN Việc sử dụng Mẫu bảng tổng hợp điểm giúp giáo viên chủ nhiệm hoàn thành chính xác việc tính điểm trung bình, xếp loại học lực, thống kê rất nhanh tình hình học tập, rèn luyện của lớp mình. Sự thống nhất trong toàn trường về một Mẫu bảng tổng hợp điểm nên việc thống kê kết quả học tập, rèn luyện của học sinh toàn trường rất nhanh và chính xác. Ban giám hiệu có thể lưu trữ các bảng tổng hợp đã cập nhật đầy đủ dữ liệu, thuận lợi cho việc tra cứu về sau, mặt khác còn sử dụng phần mềm tổng hợp số liệu từ bảng tổng hợp để: + Lọc thông tin về học sinh, học lực, hạnh kiểm toàn trường. + Lọc danh sách lên lớp thẳng toàn trường. + Lọc danh sách học sinh thi lại, ở lại, rèn luyện hè... toàn trường. + Lọc kết quả bộ môn toàn theo khối lớp. + Lọc danh sách khen thưởng toàn trường. 12 Bảng tổng hợp điểm đã được giáo viên trong trường và một số giáo viên trường bạn sử dụng khá tốt, nhờ những góp ý chân thành nên chương trình đã khắc phục được các lỗi chủ quan. Mặc dù đã rất cố gắng nhưng do trình độ còn hạn chế nên trong quá trình sử dụng nếu có ý kiến phản hồi tôi sẽ tiếp tục hoàn thiện mẫu sao cho tuyệt đối chính xác và thân thiện. Rất mong được sự đóng góp ý kiến của đồng nghiệp để đề tài hoàn thiện hơn. Tôi xin chân thành cảm ơn quý thầy cô giáo trong tổ chuyên môn đã góp ý kiến để tôi hoàn thành đề tài này. Họ tên người viết Đào Đình Ngọc 13 TÀI LIỆU THAM KHẢO Trong quá trình viết đề tài này tôi có sử dụng tài liệu tham khảo tại: Website: http://www.giaiphapexcel.com Quý thầy cô có thể tải Mẫu BTH điểm về tham khảo tại trang: http://www.mediafire.com/download/15p3w487mn429p4/BTH_Mau.xls 14 PHỤ LỤC H1 (Mat_truoc) 0 H2 (Mat_sau) 0 H3 (Mat_truoc) đầy đủ thông tin 0 H4 (Mat_sau) đầy đủ thông tin 1 MỤC LỤC PHẦN I. ĐẶT VẤN ĐỀ..........................................................................1 PHẦN II. NỘI DUNG.............................................................................2 1. Chuẩn bị thiết kế.................................................................................3 2. Thiết kế chi tiết....................................................................................4 3. Kết quả đạt được, rút kinh nghiệm:.....................................................9 3. Triển khai ứng dụng trong thực thế:...................................................10 PHẦN III. KẾT LUẬN...........................................................................11 TÀI LIỆU THAM KHẢO.......................................................................13 PHỤ LỤC................................................................................................14 0