[图文]用Excel电子表格实现学生成绩统计

火端文库

[图文]用Excel电子表格实现学生成绩统计

[转载]用excel电子表格实现学生成绩统计
[转载]用excel电子表格实现学生成绩统计

用 Excel 电子表格实现学生成绩统计统计学生成绩是教导主任和教师每学期都必须做的常规工作。

传统的做法是: 教师在成绩表上填写学生的平时成绩、考试成绩,然后用计算器按百分比计算每 个学生的总评成绩,计算科目总分和平均分,有的还需要列出名次,不但量大, 而且容易出错,反复验算是常事。

如果用电脑完成,则方便快捷准确得多,而且 无需“重算一遍”。

Excel 是美国微软公司推出的基于 Windows 操作系统的电子 表格软件,是目前被广泛使用的现代办公软件之一,本文举例使用的版本是 Microsoft Office Excel 2003。

1.如何制作成绩统计表[1.1] 输入列标题开机启动 Windows 后进入 Excel 电子表格窗口,在顶端行依次输入表格的列 标题:考号、姓名、语文、数学、英语、总分、平均分、名次等。

[1.2] 输入原始数据考号、姓名、语文、数学、英语的内容属于原始数据,也要依次输入。

考号 的输入可采用“自动填充”方法。

先在1、2单元格输入预先设计好的考号后,再 选取1、2号单元格后鼠标移动至选定区域的右下角,当出现“+”后往下拖动,所 有的考号会自动出现。

[1.3] 数据居中对齐拖动鼠标选定制表区,单击“格式”,再单击“单元格”,再单击“对齐”, 水平和垂直对齐均选“居中”,然后单击“确定”。

[1.4] 加表格框线选取需加边框的单元格,单击菜单栏中的“格式”,选择“单元格”,在对 话框中选择“边框线”标签,从边框线型栏内,选择较粗的线形加入到“外框” 标志栏内,选择较细的线型分别加入到上、下、左、右栏内,按下“确定”。

另一种方法是选择表格后,单击工具栏上“边框”按钮在弹出的选项中选择 “所有框线”则可。

至此,除需计算部分外全部输入完成。

2.如何计算总分[2.1] 计算考号1的总分表格都是由行和列组成的,行由数字表示,列用字母表示。

当活动单元格在 F4位置时键入“=”然后输入 SUM(C4:E4)(表示从 C4单元格加到 E4单元格) ,再 回车,即得总分。

另一种方法是选择 C4到 E4这三个单元格后单击工具栏上“自动 求和”按钮。

[2.2] 其他总分的得出用鼠标选取已算出的学号1的总分,当出现“+”符号后往下拖动鼠标,所有 其他的总评分会自动出现。

3.如何计算学生平均分[3.1] 学生1的平均分:活动单元格放在需要填写平均分的位置, 输入“=F4/3” (用总分除以科目3) 回车,即算出平均分。

[3.2] 其他平均分的得出:

用鼠标选取已算出的学生1的平均分,当出现“+”符号后往下拖动鼠标,所 有其他的平均分会自动出现。

4.如何排列名次[4.1] 利用菜单排序:排列名次,首先按总分排序。

从列标题那行一直选取到最后(本表是从第3行到55行) ,单击菜单“数据”, 再单击“排序”,在主要关键字下拉列表中选择“总分”,再选“降序”,按下 “确定”键,分数即由高至低排列。

这时再用“自动填充”方法将名次的号(数 字)填上。

缺点是并列的名次没有显示出来。

然后再选全表,单击“数据”再单击“排序”,在下拉列表中选主要关键字 “考号”,再选“升序”,最后“确定”,即出现按考号排列的名次格式。

[4.2] 利用 RANK 函数排序:另外一种方法是利用 RANK 函数进行排名, 解决了以上统计方法无并列名次的 缺点。

RANK(X,Y,Z)函数,其中有三个参数,第一个参数 X 为某个学生的总分所 在单元格,第二个参数 Y 为整个班级总分所在的区域,第三个参数 Z 是可选的, 表示统计方式,若省写或写0,则成绩高的名次靠前,一般都使用这种方式,如果 写1,则成绩高的名次靠后,这种情况一般较少用。

为了在保持学号顺序不变的前提下进行学生成绩名次排定, 可以在 F4单元格 中输入公式: =RANK(F4,$F$4:$F$55,0)然后,利用自动填充柄将其复制到下方的单元格,统计出所有学生的名次。

注意,这里$F$4:$F$55用的是绝对地址,是为了保证公式在复制时此处不变,因 为作为第二个参数,这里都是指整个班级总分所在的区域,这个区域是相同的。

再按名次排序,可以看出并列的名次也统计出来了。

(见下图)5.进行科目统计[5.1] 计算科目总分选取所要求和的“总分”一栏的数据,单击“Σ ”按钮,或者是在其中输入 公式“=SUM(C4:C55)”后回车即算出科目总分。

然后,利用自动填充柄将其 复制到右方的单元格,数学、英语总分即可得出。

[5.2] 计算科目平均分:方法同上,只是函数不一样 在要填入语文平均分的单元格内输入公式:“=AVERAGE(C4:C55)”即得出语 文平均分,然后,利用自动填充柄将其复制到右方的单元格,数学、英语平均分 即可得出。

[5.3] 统计科目及格人数:通常,统计不同分数段最好的方法是利用 COUNTIF(X,Y)函数。

其中有两个 参数,第一个参数 X 为统计的范围,一般最好用绝对引用;第二个参数 Y 为统计条 件,要加引号。

对于大于等于60分的人数只要用一个 COUNTIF( )函数,如在 C59单元格中输 入公式: =COUNTIF($C$4:$C$55,">=60")

回车即可得出。

其它两科复制公式后,把统计范围即其中绝对地址分别改为 $D$4:$D$55和$E$4:$E$55,数学和英语科目及格人数也统计完成。

[5.4] 计算单科及格率:单科及格率等于及格人数除以参考人数。

语文科目已在 C59单元格统计出及 格人数,参考人数在列标题上方 E2单元格内填入,因此在统计及格率 C60单元格 内写入公式:=C59/E2回车即得出。

单元格格式可设为“百分比”保留2位小数。

其它两科同样的方法可算出。

[5.5] 其它分数段的统计方法同统计及格人数, 优生人数的统计只需把统计条件改为“>=80”差生人 数的统计把统计条件改为“<40”。

如果要统计80——89、70——79与60——69 分数段的人数,需要用两个 COUNTIF( )函数相减。

如统计80——90分数段人数用 公式: =COUNTIF($C$4:$C$55,"<90")-COUNTIF($C$4:$C$55,"<80”) 即用小于90的人数减去小于80的人数。

其它分数段统计方法相同只要把统计 条件改一下。

[5.6] 统计科目最高分和最低分:利用函数 MAX、MIN 分别统计最大值和最小值。

如统计语文科目中最高分在 C65单元格内输入公式:=MAX(C4:C55)回车即得到87.00。

最低分在 C66单元格内 输入公式:=MIN(C4:C55)回车即可。

[5.7] 将百分制转换成不同的等级分:将百分制转换成不同的等级分有多种不同的划分方法, 1是将百分制的分数 转换成 A(90——100)、B(80——89)、C(70——79)、D(60——69)与 E(低于60) 五个等; 2是将百分制的分数转换成优(90——100)、 良(75——89)、 中(60——74) 与不及格(低于60)四个等级。

具体使用哪种等级划分方法可根据实际情况自己确 定。

在百分制转换成不同的等级分时,一般使用 IF(X,Y,Z)函数。

其中有 三个参数,第一个参数 X 为条件,不能加引号;第二个参数为条件成立时的结果, 如果是显示某个值,则要加引号;第三个参数为条件不成立时的结果,如果是显 示某个值,同样要加引号。

该函数可以嵌套,即在第二个或第三个参数处可以再 写一个 IF 函数。

为了得到第一种方法所要的等级结果, 如语文科目可以在 D4单元格中输入公 式: =IF(C4>=90,"A",IF(C4>=80,"B",IF(C4>=70,"C",IF(C4>=60,"D","E")))) 然后,利用自动填充柄将其复制到下方的所需单元格。

为了得到第二种方法所要的等级结果,语文科目可以在 E4单元格中输入公 式: =IF(C4>=90,"优",IF(C4>=75,"良",IF(C4>=60,"中","不及格"))) 然后,利用自动填充柄将其复制到下方的所需单元格。

[5.8] 使不及格的分数以红色显示统计学生成绩时经常需要将不及格的分数用红色显示,其结果如上图中红色 显示部分。

使不及格的分数以红色显示需要使用“格式”菜单中的“条件格式”命令。

该命令会弹出一个对话框,其中要求确认条件与相应的格式。

对于“语文”列,可先选中 C4:C55,然后使用“格式”菜单中的“条件格 式”命令, 在弹出的对话框中, 左边使用默认的“单元格数值”, 中间选“小于”, 右边填写60,然后单击右边的“格式”按钮,从中选择红色,最后单击两次“确 定”按钮。

对于“等级一”列,可先选中 D4:D55,然后使用“格式”菜单中的“条件 格式”命令,在弹出的对话框中,左边使用默认的“单元格数值”,中间选“等 于”,右边填写 E,然后单击右边的“格式”按钮,从中选择红色,最后单击两 次“确定”按钮。

“等级二”列类似。

6、结束语Excel 电子表格功能强大,界面直观易学易用,可以轻松地完成学科的总分、 平均分的计算;利用 RANK 函数排序,可以在学生顺序不变的前提下进行学生成绩 名次排定,并且可以显示出名次并列的情况,一目了然;COUNTIF(X,Y)函数是统 计不同分数段人数的一种好方法;使用 IF(X,Y,Z)函数能轻松将百分制转换成 不同的等级分;用“条件格式”命令可以用特殊的颜色显示符合条件的一组分数 段或某等级;总之,用 Excel 统计学生成绩,将会大大提高教师的工作效率和工 作质量,让我们的教学工作变得更轻松。

参考文献:[1]周天红.《如何用 Excel 电子表格统计学生成绩》[J].教师论坛, 1997,(11) :78-79 [2]王 2004-3-15 [3]姬颖玉.Excel 统计学生成绩时的四个难题及解决. [EB/OL] .兴庆区教育 局信息中心,2008-3-27 冰. 用 EXCEL 进行学生成绩管理小技巧.[EB/OL] .eNet 论坛,