搜档网
当前位置:搜档网 › 学生成绩统计模板

学生成绩统计模板

一劳永逸,打造学生成绩统计模板

如今之学校,月考,期中考试,期末考试,模拟考试,诊断考试……可谓名目繁多。每次考试后班主任都需填写学生成绩,还要计算及格率、优秀率、总分、均分、名次等,对于已经忙得团团转的班主任来说,真可谓雪上加霜。不如,让我们在EXCEL中打造学生成绩统计模板,让每次成绩统计不再是班主任心中永远的痛。

目的:打造成的模板,应达到如下目的,班主任在填完学生成绩后,EXCEL自动计算出各科总分、均分、及格率、优秀率,最高分、最低分;各科最高分及最低分及不及格学生成绩以不同颜色显示出来,使老师对不同学生成绩一目了然;自动求出学生总分及名次。其最终效果如图一所示:

任务助手:EXCEL函数,包括SUM(求和)、SUMIF(条件求和)、AVERAGE(求平均值)、COUNT(求值)、COUNTIF(条件求值)、MAX(求最大值)、MIN(求最小值)及RANK (求名次)等及以上函数的嵌套。

心动不如行动,且随我一起开始具体打造过程——

步骤一:输入基本信息

打开EXCEL,在表格中输入基本信息,包括学生姓名、科目、总分、名次、及格率等。设置各科目及总分、均分、最高分、最低分格式为数值,保留两位小数;设置及格人数格式为数值,不保留小数;设置及格率、优秀率格式为百分比,保留两位小数。为方便图解,假定学生数为20人,表格结构如图一所示。

步骤二:运用公式求各科目成绩统计项

1. 求语文这一科目的总分,在B22语文栏中输入“=SUM(B2:B21)”(不包括引号),回车确认。

2. 在B23栏中输入“=A VERAGE(B2:B21)”,求出学生语文平均分。

3. 求出语文及格人数。用到“COUNTIF(条件求值)函数”,在B24栏中输入“=COUNTIF(B2:B21,">=60")”,意思是求出从B2到B21单元格区域中数值大于等于60分(及格)的人数。提示:括号中的引号为英文半角状态下输入,下同。

4. 求出语文课的及格率。及格率的计算公式为“及格人数/参加考试人数*100%”,根据以上推导,在B25中输入“=(COUNTIF(B2:B21,">=60")/COUNT(B2:B21))”即可。

5. 计算语文课的优秀率。优秀率的算法和及格率相似,只需将公式中的“>=60”改为“>=80”(假定80分为优秀)即可。

6. 在B26及B27单元格中分别输入“=MAX(B2:B21)”及“=MIN(B2:B21)”就可以得出最高分及最低分,非常简单。

其它科目运用公式和语文相同,只要向右拖动复制语文中的公式即可。各科相关数据计算完成,现在让我们进入下一环节。

步骤三:计算学生总分及名次

1. 计算学生总分。在J2单元格中输入“=SUM(B2:I2)”,就可求出学生各科总分。把J2单元格向下拖动复制,使每位学生的总分都显示出来。由于学生各科成绩还没有输入,所以现在结果显示为0。

2. 根据总分,自动计算学生名次。计算学生名次我们常用的办法是,先给总分排序,然后在名次列中按序输入序列的方法来完成。但这种方法比较啰嗦,需将学生所有成绩输完以后才可排序,而且如果学生某一科成绩改变就须重新排序并输入序列。能不能用一种方法,能根据学生总分自动为学生排名呢?回答是肯定的,运用RANK函数可以实现这一功能,该函数可以返回一个数值在一组数值中的排位,数值的排位是与数据清单中其他数据的相对大小。

由于这个函数我们平常不常用,所以我在这里详细进行讲解:选择J2单元格,单击[fx]按钮,出现“插入函数”对话框。在类别中找到“全部”,然后找到我们需要的RANK函数,单击[确定]按钮,出现“函数参数”对话框,第一个参数Number为需要找到排位的数字,在这里我们依总分进行排名,所以在其中输入“J2”;第二个参数Ref为包含一组数字的数组或引用,在其中输入“$J$2:$J$21”,为了使排名随总分改变而改变,我们加上“$”,这样单元格地址就变成绝对引用了;第三个参数Order为一数字,用来指明排位的方式,如果Order为0或省略,则EXCEL按降序排列的数据清单进行排位,在这里我们输入0或省略(见图3)。单击[确定]按钮,完成的整个函数语法形式为“=RANK(J2,$J$2:$J$21)”,向下拖动复制,就可实现排名功能,而且如果我们修改某个学生的成绩,显示结果会自动变化。

步骤四:使各科最高分、最低分及不及格成绩一目了然

为了使老师快速掌握各科成绩中的最高分、最低分及不及格学生,针对不同层次学生因材施教,我们可以运用不同颜色使这些成绩区别开来。

仍以语文成绩为例来说明:

1. 选定所有的语文成绩单元格区域,单击[格式]菜单中的“条件格式”,在弹出的“条件

格式”对话框中进行相应设置。

2. 设置最高分的格式。先在“条件1(1)”列表框中选择“单元格数值”,接着在“算法”列表框中选择“等于”,在参数框中输入“=MAX($B$2:$B$21)”,然后单击[格式]按钮,在“单元格格式”中选择字体颜色为“绿色”。

3. 设置最低分的格式。单击“添加”按钮,扩展“条件格式”对话框,在“条件2(2)”中设置和1中相同,不过要修改“MAX”为“MIN”,并设置颜色为蓝色。

4. 设置不及格成绩的格式。单击“添加”按钮,扩展“条件格式”对话框,在“条件3(3)”的“算法”中选择“小于”,在“参数”中输入“60”,选择字体为红色,单击[确定]按钮.

5. 再次单击[确定]按钮,语文课的最高分自动以绿色凸显示,最低分自动以蓝色显示,不及格成绩就会亮起“红灯”,结果如图5所示。

其它科目如法炮制进行。提示:可用格式刷设置各科格式,然后修改单元格地址即可。

步骤五:单元格保护

至此,我们的表格功能基本实现,但工作还没完成。为了防止别人对你煞费心机做好的表格无意中破坏,我们还须对单元格进行保护。也就是说,在表格中我们只需让老师们输入学生各科成绩就行,其它统计工作让EXCEL自动完成。这样就要把除学生成绩以外的区域保护起来,禁止别人修改。

1. 在默认状态下,工作表的所有单元格都被锁定,在对工作表进行保护前,必须先解除锁定。选择“格式”/“单元格”菜单项,在弹出的“单元格格式”对话框中单击“保护”选项卡,如图6所示。取消选择“锁定”复选框,如果需要隐藏不想让他人查看的公式,需选中“隐藏”复选框。单击[确定]按钮返回。

2. 选定允许编辑的单元格区域,也就是此表中的学生成绩区域,然后选择“工具”/“保护”/“保护工作表”菜单项,打开“保护工作表”对话框(见图7)。

⑴在“保护工作表”对话框中选定“保护工作表及锁定的单元格内容”复选框,可以防止对工作表的更改,并且可以防止对锁定单元格的更改。

⑵在“取消工作表保护时使用的密码”文本框中输入密码,防止其他用户删除或更改单元格。

⑶在“允许此工作表的所有用户进行”列表框中选中“选定未选定的单元格”复选框和其它允许用户进行的操作。

⑷设置完毕后单击[确定]按钮,在弹出的“确认密码“对话框中重新输入刚才的密码进行确认,并单击[确定]按钮。

3.如果要撤销保护工作表,则选择“工具”/“保护”/“撤销工作表保护”选项,打开“撤销工作表

保护”对话框,输入密码后单击[确定]按钮即可。

步骤六:保存为模板,以便随时调用

单击“文件”/“保存”菜单,在“另存为”对话框“文件名”中输入“学生成绩统计表”,在“保存类型”中选择“模板”,回车确定即可。

至此,一个超强的学生成绩统计模板打造成功。应用时,打开此模板,在其中输入学生成绩,其余一切交给电脑来完成。如果学生人数、科目有所改变的话,改变其中部分内容即可使用。虽然打造模板的过程花了一些时间,但“磨刀不误砍柴工”,细细想来还是很划算的。

相关主题