EXCEL在成绩统计中的应用

2022-09-17

每学期结束时,教师经常要对各门课程的成绩进行统计和分析,以综合评价学生对课程的掌握情况,发现教学中存在的问题,以利于教师改进教学工作,提高教学质量。由于每学期每门课程都要重复做这项工作,给教师带来很大的工作量,而且容易造成统计错误。EXCEL强大的数据处理能力完全可以胜任成绩统计工作,且其丰富的图表功能还能使统计数据更加直观。借助EXCEL的模板功能,可以使设计好的成绩统计功能重复应用于每门课程,这不仅大大地减少了教师的工作量,而且避免了错误统计数据的产生。下面就如何使用EXCEL制作成绩统计模板进行介绍。

1 成绩的录入

如图1所示,成绩表包括学生姓名,学号,平时作业成绩,实验成绩,期中考试成绩,期末考试成绩和综合成绩几项内容。期中,综合成绩=15%平时作业成绩+15%实验成绩+30%期中考试成绩+40%期末考试成绩。根据综合成绩的构成,可在综合成绩栏输入公式直接计算,如何珮瑄的综合成绩计算机公式为:ROUND(C2*0.15+D2*0.15+E2*0.3+F2*0.4,0)。为了减少录入成绩的错误,还须对C,D,E,F四列设置录入错误提示,当输入的成绩小0或大于100时,提示用户输入错误,并清除输入的值。同时为了突出显示不及格的成绩,需将不及成绩显示为红色。

1.1 录入成绩错误提示

学生成绩的范围在0~100之间,如果错误输入其他数值,EXCEL将提示用户输入错误,设置错误提示功能的操作步骤为[1]:(1)选定需要进行成绩检查的单元格区域C2:F17;(2)单击“数据”菜单的“有效性”菜单项,在“数据有效性”对话框中的“设置”标签,进行成绩数值范围的设置,如图2所示;(3)在“数据有效性”对话框中的“出错警告”标签中,设置出错后的提示信息,如图3所示;(4)单击“确定”按钮退出。进行以上设置后,只要在C2:F17单元格中输入了小于0或大于100的数值,则会出现提示信息“成绩栏只能输入0~100之间的数字”从而提高了输入成绩的准确性。

1.2 不及格成绩显示为红色

为了突出显示不及格的成绩,将其显示为红色。其操作为:(1)选中单元格区域C2:G17;(2)单击“格式”菜单中的“条件格式”菜单项;(3)在“条件格式”对话框中,设置“单元格数据”介于0与59之间,字体的颜色设置为红色,如图4所示;(4)单击“确定”按钮退出。

2 成绩统计

如图5所示,成绩统计主要包括:(1)统计各分数段人数;(2)统计各分数段人数占总人数的比例,并用图表显示出来;(3)统计最高分,最低分和平均分。由于“成绩表分析”工作表中的数据都是通过“成绩表”工作表中的数据统计出来的,所以不需要做任何修改。为了防止使用者无意中修改其中的数据,必须将“成绩表分析”工作表中的所有单元格保护起来,不允许使用者修改。

2.1 各分数段人数

统计各分数段人数主要由COUNTIF函数实现[2],其统计表达式如下。

90分以上:COUNTIF(成绩表!G 2:G17,“>=90”

80~90分:COUNTIF(成绩表!G2:G17,“>=80”)-COUNTIF(成绩表!G2:G17,“>=90”)

70~80分:COUNTIF(成绩表!G2:G17,“>=70”)-COUNTIF(成绩表!G2:G17,“>=80”)

60~70分:COUNTIF(成绩表!G2:G17,“>=60”)-COUNTIF(成绩表!G2:G17,“>=70”)

60分以下:COUNTIF(成绩表!G 6:G17,“<60”)

2.2 各分数段人数比例

先按照以下公式统计各分数人数占总人数的比例,然后选定单元格C2:C6,将其设置为百分比显示。

90分以上:ROUND(B2/SUM(B2:B6),2)

80~90分:ROUND(B3/SUM(B2:B6),2)

70~80分:ROUND(B4/SUM(B2:B6),2)

60~70分:ROUND(B5/SUM(B2:B6),2)

60分以下:ROUND(B6/SUM(B2:B6),2)

计算各分数段人数占总人数的比例后,可通过插入图表的方式,直接绘制出其分布的三维饼图,使成绩的分布更直观地显示出来。

2.3 最高分,最低分,平均分

统计最高分,最低分,平均分的表达式如下。

最高分:MAX(成绩表!G2:G17)

最低分:MIN(成绩表!G3:G17)

平均分:ROUND(AVERAGE(成绩表!G4:G17),1)

2.4 保护“成绩表分析”工作表

为了让统计数据只能自动生成,不能人为改动,必须将“成绩表分析”工作表保护起来,其步骤为:(1)选中该工作表中的所有单元格;(2)在选中的单元格上右击,在出现的快捷菜单中选择“设置单元格格式”菜单项;(3)在“单元格格式”对话框中单击“保护”,钩先“锁定”复选框,单击“确定”按钮;(4)单击“工具”→“保护”→“保护工作表”,选中要保护的选项,然后输入保护密码,单击“确认”按钮。通过这些设置,只有知道保护密码才能对这些单元格进行修改,从而确保了统计数据的安全和准确性。

3 保存和调用模板

为了让设置好的工作薄在其他课程中使用,可以将“成绩表”工作表中的数据清空,然后将其保存为模板,就可以分发给其他教师使用这个成绩统计模板了。

3.1 将工作薄保存为模板

将工作薄“成绩表”工作表中的数据清空,单击“文件”菜单中的“另存为”菜单项在弹出的“另存为”对话框的“文件名”列表框中输入“成绩统计分析模板”,在“保存类型”下拉菜单中选择“模板”,单击“保存”按钮完成模板的保存。

3.2 调用模板

调用模板的步骤为:打开EXCEL程序,点击“文件”菜单中的“新建”菜单项,单击“新建”对话框右边的“本机上的模板”,弹出“模板”对话框,双击“成绩统计分析模板”图标,即可打开该模板并使用。

4 结语

该模板已经在很多教师中进行了推广,完成了大量的课程成绩统计工作,取得了很好的效果。教师在使用模板时,只需要在“成绩表”工作表输入学生的成绩数据,EXCEL就会在“成绩表分析”工作表中自动生成相关的统计数据,并用饼图显示出统计的结果。通过该模板的使用,不仅提高了教师成绩统计工作的效率,减少了统计数据的错误,而且图形显示使统计的结果更加直观。

摘要：Microsoft Excel是微软公司的办公软件Microsoft office的组件之一,由于具有强大的数据处理能力,被广泛地应用于数据处理、统计分析和辅助决策等领域。在成绩统计中,运用EXCEL的函数、统计和模板功能,可在很大程度上提高统计的效率和准确性。

关键词：成绩分析,EXCEL模板