如何用Excel电子表格统计学生成绩

如何用Excｅl电子表格统计学生成绩

【摘要】使用Excel电子表格统计学生成绩不但方便而且快捷，利用它能自动完成成绩各项统计工作如计算总分、排名次、统计各分数段学生人数和所占比例、最高分、最低分等,还能把分数转换成等级分.用Excｅl电子表格统计学生成绩，将会大大提高教师的工作效率和工作质量。

【关键词】Exｃel;电子表格;统计;成绩；名次

统计学生成绩是教导主任和教师每学期都必须做的常规工作。传统的做法是：教师在成绩表上填写学生的平时成绩、考试成绩，然后用计算器按百分比计算每个学生的总评成绩，计算科目总分和平均分，有的还需要列出名次,不但量大，而且容易出错,反复验算是常事.如果用电脑完成，则方便快捷准确得多，而且无需“重算一遍”。Excel是美国微软公司推出的基于Windows操作系统的电子表格软件，是目前被广泛使用的现代办公软件之一,本文举例使用的版本是Mｉｃｒoｓoft Oｆfｉce Exceｌ 2003。１如何制作成绩统计表

［1．1] 输入列标题

开机启动Ｗindｏws后进入Exceｌ电子表格窗口，在顶端行依次输入表格的列标题：考号、姓名、语文、数学、英语、总分、平均分、名次等。

［１.2] 输入原始数据

考号、姓名、语文、数学、英语的内容属于原始数据,也要依次输入。考号的输入可采用“自动填充”方法.先在1、２单元格输入预先设计好的考号后，再选取1、２号单元格后鼠标移动至选定区域的右下角,当出现“+”后往下拖动，所有的考号会自动出现.(如图1）

图1

[1.3］数据居中对齐

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

［1．4］加表格框线

选取需加边框的单元格,单击菜单栏中的“格式",选择“单元格”，在对话框中选择“边框线”标签，从边框线型栏内，选择较粗的线形加入到“外框”标志栏内,选择较细的线型分别加入到上、下、左、右栏内,按下“确定”.（如图2）

第二种方法是选择表格后，单击工具栏上“边框”按钮在弹出的选项中选择“所有框线”则可（如图2）。至此,除需计算部分外全部输入完成。

图 2

2.如何计算总分

[2.１] 计算考号１的总分

表格都是由行和列组成的，行由数字表示，列用字母表示.当活动单元

格在Ｆ4位置时键入“＝”然后输入ＳUＭ（Ｃ4:E4）(表示从C4单元格加到E4单元格),再回车，即得总分（如图3）.另一种方法是选择C4到E4这三个单元格后单击工具栏上“自动求和"按钮(如图3右侧图标).

图3

［2．2] 其他总分的得出

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

3。如何计算学生平均分

[3。１］学生1的平均分:

活动单元格放在需要填写平均分的位置，输入“＝F4／３”（用总分除以科目３）回车，即算出平均分.(如图4）

图４

［3。２］其他平均分的得出:

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

4．如何排列名次

［4．1］利用菜单排序：

排列名次,首先按总分排序。

从列标题那行一直选取到最后（本表是从第3行到55行），单击菜单“数据”，再单击“排序”，在主要关键字下拉列表中选择“总分",再选“降序”,按下“确定”键，分数即由高至低排列。这时再用“自动填充"方法将名次的号（数字)填上。缺点是并列的名次没有显示出来。(如图5)

图5

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

[4．2] 利用RANK函数排序:

另外一种方法是利用RANＫ函数进行排名，解决了以上统计方法无并列名次的缺点。ＲANK（X，Y,Ｚ)函数，其中有三个参数,第一个参数Ｘ为某个学生的总分所在单元格，第二个参数Ｙ为整个班级总分所在的区域，第三个参数Ｚ是可选的，表示统计方式，若省写或写0，则成绩高的名次靠前,一般都使用这种方式,如果写1，则成绩高的名次靠后,这种情况一般较少用。

为了在保持学号顺序不变的前提下进行学生成绩名次排定，可以在Ｆ4单元格中输入公式: =RANK（Ｆ4，＄Ｆ＄4:＄F$55，0）（如图6）

图 6

然后,利用自动填充柄将其复制到下方的单元格，统计出所有学生的名次。注意，这里＄F＄4：＄Ｆ$５5用的是绝对地址，是为了保证公式在复制时此处不变，因为作为第二个参数，这里都是指整个班级总分所在的区

域,这个区域是相同的。

再按名次排序，可以看出并列的名次也统计出来了.（如图7）

图 7

5.进行科目统计

[5.1］计算科目总分

选取所要求和的“总分”一栏的数据，单击“Σ”按钮,或者是在其中输入公式“=SUM(C4:C５5）”后回车即算出科目总分。然后,利用自动填充柄将其复制到右方的单元格，数学、英语总分即可得出.(如图８)

图８

［5。２］计算科目平均分：

方法同上，只是函数不一样

在要填入语文平均分的单元格内输入公式:“=AVＥRAGE（C４：C55)”即得出语文平均分,然后，利用自动填充柄将其复制到右方的单元格，数学、英语平均分即可得出。

［5．3］统计科目及格人数：

通常，统计不同分数段最好的方法是利用ＣOUNTIF（X,Y）函数.其中有两个参数,第一个参数X为统计的范围，一般最好用绝对引用;第二个参

数Y为统计条件，要加引号。

对于大于等于60分的人数只要用一个CＯUNＴＩF( )函数,如在C5９单元格中输入公式：

=CＯUNTＩF(＄C＄4：$C＄５5,">=60"）（如图9）

图９

回车即可得出.其它两科复制公式后,把统计范围即其中绝对地址分别改为$D＄４：$D$55和$E$4:$Ｅ$5５,数学和英语科目及格人数也统计完成。

[5。4] 计算单科及格率:

图 10

单科及格率等于及格人数除以参考人数。语文科目已在C59单元格统计出及格人数，参考人数在列标题上方E２单元格内填入，因此在统计及格率C６０单元格内写入公式：=Ｃ59/E2回车即得出(如图10)。单元格格式可设为“百分比”保留２位小数.其它两科同样的方法可算出.

[5．5］其它分数段的统计

方法同统计及格人数，优生人数的统计只需把统计条件改为“>=８0”差生人数的统计把统计条件改为“〈４0"。如果要统计80-—８9、70——７9与6０—-69分数段的人数，需要用两个COUNTIＦ( )函数相减。如统计80-—9０分数段人数用公式: =COUNTIＦ（＄C＄4:＄C＄55，"〈90")—ＣＯUNＴIF(＄C$4：$C$55,”<８0”）

即用小于90的人数减去小于８0的人数.其它分数段统计方法相同只要把统计条件改一下。

[5。6] 统计科目最高分和最低分：

利用函数MAX、MIN分别统计最大值和最小值。如统计语文科目中最高分在Ｃ65单元格内输入公式:=ＭＡX（C4：Ｃ5５）回车即得到87．0０.最低分在Ｃ66单元格内输入公式：=MＩＮ（C4：Ｃ55）回车即可。（如图11）

图 11

[５。７] 将百分制转换成不同的等级分：

将百分制转换成不同的等级分有多种不同的划分方法, １是将百分制的分数转换成A（9０——100）、B(８0-—８9)、C(70—-79）、D（６0—-69）与E（低于60）五个等;２是将百分制的分数转换成优(90——10０）、良(75--89）、中（６0——74）与不及格（低于60）四个等级。具体使用哪种等级划分方法可根据实际情况自己确定。

在百分制转换成不同的等级分时,一般使用IＦ（Ｘ,Y，Z）函数.其中有三个参数，第一个参数X为条件,不能加引号；第二个参数为条件成立时的结果，如果是显示某个值,则要加引号；第三个参数为条件不成立时的结

果，如果是显示某个值，同样要加引号。该函数可以嵌套,即在第二个或第三个参数处可以再写一个IF函数.

为了得到第一种方法所要的等级结果，如语文科目可以在D４单元格中输入公式： =IF（C４>=９0，”A",IF（C４〉=80,＂B”，ＩF（C４＞＝7０,"Ｃ＂，ＩF(C4〉=60,”D"，＂E"））)）然后,利用自动填充柄将其复制到下方的所需单元格。(如图12）

为了得到第二种方法所要的等级结果，语文科目可以在Ｅ4单元格中输入公式：

＝IF（Ｃ４>=90，＂优”，IＦ（C４＞=7５，＂良"，IＦ（Ｃ４＞=

６0，＂中＂，”不及格＂)）)

然后，利用自动填充柄将其复制到下方的所需单元格。

图12

[5．8］使不及格的分数以红色显示

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

使不及格的分数以红色显示需要使用“格式"菜单中的“条件格式”命令。该命令会弹出一个对话框，其中要求确认条件与相应的格式。

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

图１３

对于“等级一"列,可先选中D4:D５5，然后使用“格式”菜单中的“条件格式”命令,在弹出的对话框中，左边使用默认的“单元格数值"，中间选“等于",右边填写E，然后单击右边的“格式”按钮,从中选择红色，最后单击两次“确定”按钮。“等级二”列类似。6、结束语Ｅxcｅｌ电子表格功能强大,界面直观易学易用，可以轻松地完成学科的总分、平均分的计算；利用ＲANK函数排序,可以在学生顺序不变的前提下进行学生成绩名次排定，并且可以显示出名次并列的情况，一目了然；ＣOUNTIＦ（X，Y)函数是统计不同分数段人数的一种好方法；使用IF（X,Ｙ,Z）函数能轻松将百分制转换成不同的等级分；用“条件格式”命令可以用特殊的颜色显示符合条件的分数段或某等级；总之,用Excｅl统计学生成绩，将会大大提高教师的工作效率和工作质量，让我们的教学工作变得更轻松。