资源描述
设计成绩自动统计Excel模板在教学管理工作中,学期期末成绩统计是学校重要且枯燥烦琐的工作,设计成绩自动统计Excel模板成为实际需要。本人经过摸索,利用Excel函数功能,能够实现成绩自动统计要求。接下来本人就将自动统计功能的实现过程进行简单的陈述,以期望抛砖引玉,与众多朋友一起学习和探讨。一、学校教学成绩统计的需求分析在进行成绩自动统计模板设计前,正确进行学校教学成绩统计的需求分析是必要的,就以我校情况简述如下:每年级现有教学班10个,考虑到学校的发展,因此设模板每年级上限班级为20个。现每个教学班具有50至60人,因此设模板每个教学班上限人数为75人,每级学生总人数上限为1500人。根据学校教学成绩评估的需求,成绩统计表格如下:表1-1二、成绩自动统计模板的总体设计新建空白Excel工作簿,将自动建立的Sheet1和Sheet2工作表重命名为Tstat和Tscore,并将Sheet3工作表删除。如图 ,其中Tscore工作表用于记录成绩数据,Tstat工作表用于记录成绩统计数据。1、定制Tscore工作表在Tscore工作表第一行中从A1单元格开始顺序输入记录字段名:学号、姓名、班级、语文、数学、外语、物理、化学、生物、政治、历史和地理。2、定制Tstat工作表对Tstat工作表的定制多而复杂,下面就以语文科统计表定制为例进行陈述,其它科的统计类比操作。(一)定制语文科统计表基本框架将单元格A1:T1合并。并在合并单元格中输入“=IF(MONTH(TODAY()6,第 & (YEAR(TODAY()-1) & - & YEAR(TODAY() &学年度第二学期期末,第 & (YEAR(TODAY()-2) & - & (YEAR(TODAY()-1)&学年度第一学期期末) &TScore!D1 &科统计表一览 ”,其中MONTH(TODAY()得到统计时的月份数,YEAR(TODAY()得到统计时的年份数,TScore!D1得到统计科目,即“语文”。通过函数IF对数值和公式进行条件检测,使得单元格能够自动获得学年度信息和学期信息。参照前面表1-1,定制单元格区域A2:S3,并从A4单元格至A23单元格中顺序输入1至20,A24输入“级”。 (二)求语文科各班参考人数首先在B4单元格中输入“=COUNTIF(TScore!$C$2:TScore!$C$1501,= & A4)”,其中COUNTIF函数用于计算给定区域内满足特定条件的单元格的数目,通过COUNTIF函数求出1班的参考人数。然后选中B4单元格,拖动B4单元格右下角的小“十”字图标至B23单元格(这种操作本文统称为向下自动填充操作),这样,就能自动得到B5至B23单元格的公式。最后在B24单元格中输入“=SUM(B4:B23)”(三) 求语文科各班平均分首先在C4单元格中输入“=IF(B4=0,0,SUMIF(TScore!$C$2:TScore!$C$1501,= & A4,TScore!$D$2:TScore!$D$1501)/B4) ”,其中SUMIF函数功能是根据指定条件对若干单元格求和。为了避免分母为0的错误产生,单元格中用到了IF函数。该单元格公式可理解为:若1班参考人数不为0,则把1班语文科总分除以参考人员得到平均分。然后对C4至C23单元格进行向下自动填充操作,就可以自动得到C5至C23单元格的公式。最后在C25单元格中输入“=IF(B24=0,0,SUM(TScore!$D$2:TScore!$D$1501)/B24)”,求出全级平均分。(四)求语文科各班合格人数在求各班合格人数时,利用COUNTIF函数功能比较难满足设计要求,为了解决这种多条件的计算功能,本人使用了数组公式进行多重标准单元计算。首先在D4单元格中输入“=SUM(TScore!$C$2:TScore!$C$1501=A4)*(TScore!$D$2:TScore!$D$1501=60)”,按下SHIFTCTRL键敲回车(这是生成数组公式的关键环节),Excel会自动在公式两侧加上大括号,生成数组公式。然后对D4至D23单元格进行向下自动填充操作。最后在D25中输入“=SUM(D4:D23)”,求出全级及格人数。(五)求语文科各班合格率首先在E4单元格中输入“=IF(B4=0,0,D4/B4)*100”,为避免分母为O的错误,用了IF函数。公式可以理解为:若1班参考人数不为0,则合格率为合格人数除以参考人数后乘以100。然后对E4至E24单元格进行向下自动填充操作。(六) 求语文科各班差生人数我校对分数低于30分的学生称为差生。首先在F4单元格中输入“=SUM(TScore!$C$2:TScore!$C$1501=A4)*(TScore!$D$2:TScore!$D$1501=80)”,按下SHIFTCTRL键敲回车(这是生成数组公式的关键环节),Excel会自动在公式两侧加上大括号,生成数组公式。然后对H4至H23单元格进行向下自动填充操作。最后在H24单元格中输入“=SUM(H4:H23)”,以求得全级优生人数。(九) 求语文科各班优生率首先在I4单元格中输入“=IF(B4=0,0,H4/B4)*100”,为避免分母为O的错误,用了IF函数。公式可以理解为:若1班参考人数不为0,则优生率为优生人数除以参考人数后乘以100。然后对I4至I24单元格进行向下自动填充操作。(十) 求语文科各班0-10分数段人数0-10分数段即可理解为小于10分的分数段。首先在J4单元格中输入“=SUM(TScore!$C$2:TScore!$C$1501=A4)*(TScore!$D$2:TScore!$D$150110)”,按下SHIFTCTRL键敲回车(这是生成数组公式的关键环节),Excel会自动在公式两侧加上大括号,生成数组公式。然后对J4至J23单元格进行向下自动填充操作。,然后在J24单元格中输入“=SUM(J4:J23)”,以求得全级0-10分数段人数。(十一)求语文科各班10-20分数段人数求语文科各班10-20分数段人数,将用到三个条件进行标准单元计算。首先在K4单元格中输入“=SUM(TScore!$C$2:TScore!$C$1501=A4)*(TScore!$D$2:TScore!$D$1501=10)”,按下SHIFTCTRL键敲回车,生成数组公式。然后对K4至K23单元格进行向下自动填充操作。最后在K25单元格中输入“=SUM(K4:K23)”,求出全级10-20分数段人数。同样的道理,可以分别统计各班语文科共它各分数段人数,在此不再多述。(十二)求语文科各班最高分在T4单元格中输入“=MAX(IF(TScore!$C$2:TScore!$C$1501=A4,TScore!$D$2:TScore!$D$1501)” 按下SHIFTCTRL键敲回车,生成数组公式。然后对T4至T23单元格进行向下自动填充操作。最后在T25单元格中输入“=MAX(T4:T23)”,求出全级最高分。三、成绩自动统计Excel模板的密码加强为了维护数据的安全性,防止非法用户善自有意或无意更改成绩数据,加强成绩自动统计Excel模板的密码功能成为实际需要。虽然Excel应用软件提供了加密功能,但针对Excel的加密而开发的解密软件已经出现,因此,本人利用VBA接口,加强了学校成绩统计Excel模板打开要求。同时按住Alt键和F11键,激活VBE(即VB编辑器),在“工程”窗口中双击“ThisWorkbook”后,在代码窗口中输入下列语句:Private Sub Workbook_Open()Dim PasswordPassword = InputBox(请输入合法密码:, 提示)If Password 111 Then MsgBox 密码错误,你是非法用户, vbOKOnly, 提示 Me.Close (1)End IfEnd Sub上述代码可理解为:将输入密码内容赋值给Password,若Password内容不为验证密码“111”,则提示用户为非法用户,然后文档自动关闭。当然,可以根据自己的爱好,将验证密码进行更改。四、保存学校成绩统计Excel模板单击菜单“文件保存(或另存为)”,出现“另存为”的对话框,选择“保存类型”中的“模板”,在文件名输入栏中输入“成绩自动统计Excel模板”。通过上述操作,当我们要进行学校成绩统计时,只要打开“成绩自动统计Excel模板.XLT”,然后在TScore工作表中输入成绩,而成绩对应的各种统计则默默地自动执行,不需要人的参与。
展开阅读全文