Excel 进行学生成绩统计分析.docx
- 文档编号:733499
- 上传时间:2023-04-29
- 格式:DOCX
- 页数:19
- 大小:861.81KB
Excel 进行学生成绩统计分析.docx
《Excel 进行学生成绩统计分析.docx》由会员分享,可在线阅读,更多相关《Excel 进行学生成绩统计分析.docx(19页珍藏版)》请在冰点文库上搜索。
Excel进行学生成绩统计分析
Excel进行学生成绩统计分析
处理学生成绩时所分析的项目包括参考班级,参考人数、平均分,及格人数、及格率、优秀人数、优秀率,最高分、最低分以及各分数段所占的人数:
600分以上人数、500~599分人数、400~499人数、300~399人数、300分以下人数。
这个统计比较复杂,且工作量大,如果用人工计算,不但效率低,且出错机率大,而用EXCEL函数可以很好解决这个问题。
新建空白Excel工作簿,将自动建立的Sheet1、Sheet2、Sheet3工作表重命名为成绩表、分析表、分数段
统计表。
其中各科考试成绩表用于输入各科成绩。
(一)设计各科成绩表
在各科考试成绩表第一行中输入分析名称,如“**中学2007~2008学年度第一学期高一月考成绩表”,在第二行从A1单元格开始顺序输入记录字段名:
考号、姓名、班级、语文、数学、英语、物理、化学等。
假设全级学生有674人,10个班,所以从第3行到第676行用来输入所有学生各科的成绩。
1. 求总分:
主要用SUM函数,其语法格式为SUM(Ref),此处Ref为参与计算的单元格区域。
例如:
SUM(B2:
E2)是表示求B2、C2、
D2、E2四个单元格内数字的和。
另外还用到SUMIF函数,语法格式为SUMIF(Range,Criteria,Sum_range),其功能是根据指定条件对若干单元格求和,参数Range表示引用,用于条件判断的单元格区域。
Criteria表示数字、表达式或文本,指出哪些单元格符合被相加求和的条件。
Sum_range表示引用,需要求和的实际单元格。
注意:
Criteria如果是文本,那么引号应该是半角的,而不是全角的,否则会出错!
在O3单元格中输入=sum(F3:
N3),便可求出第一个同学韩威的总分,然后选中O3单元格,当右下角出现小”+”字图标时时使用拖拉大法往下自动填充,或左键双击即可.
2.排名次:
MicrosoftExcel中用RANK函数求名次(排序),它的语法为:
RANK(Number,Ref,Order) 返回一个数字在数字列表中的排位。
Number为需要找到排位的数字。
Ref为数字列表数组或对数字列表的引用。
Ref中的非数值型参数将被忽略。
如果使用公式复制,最好使用绝对地址。
如
=RANK(E2,$E$2:
$E$50)。
注意,其中的标点符号用英文半角。
Order为一数字,指明排位的方式。
如果order为0(零)或省略,MicrosoftExcel对数字的排位是基于ref为按照降序排列的列表。
如果order不为零,MicrosoftExcel对数字的排位是基于ref为按照升序排列的列表。
在计算的过程中我们需要注意两点:
首先当RANK函数中的Number不是一个数时,其返回值为“#VALUE!
”,影响美观。
另外,Excel有时将空白单元格当成是数值“0”处理,造成所有成绩空缺者都是最后一名,看上去也很不舒服。
此时,可将上面的公式
“=RANK(E2,$E$2:
$E$50)”改为“=IF(ISNUMBER(E2),RANK(E2,$e$2:
$E$50),"")”。
其含义是先判断E2单元格里面有没有数值,如果有则计算名次,没有则空白。
其次当使用RANK函数计算名次时,相同分数算出的名次也相同,使用RANK()函数排名的两种情况:
(1)同分同名次,但后续名次空缺
假定E列为成绩,F列为名次,F2单元格公式如下:
=RANK(E2,E:
E)或=rank(e2,$E$2:
$E$10000)
这种方法,分数相同时名次相同,随后的名次将空缺。
例如:
两个人99分,并列第2名,则第3名空缺,接下来是第4名。
(2)同分同名次,但后续名次不空缺
假定成绩在E列,则在F2单元格中输入公式:
=SUMPRODUCT((INDIRECT("E$2:
E$"&MAX(IF(E$2:
E$10000<>"",ROW(E$2:
E$10000))))>E2)*(1/COUNTIF(INDIRECT("E$2:
E$"&MAX(IF
(E$2:
E$10000<>"",ROW(E$2:
E$10000)))),INDIRECT("E$2:
E$"&MAX(IF(E$2:
E$10000<>"",ROW(E$2:
E$10000)))))))+1
公式以Ctrl+Shift+Enter三键结束。
通常我们在排名次时大多采用的是同分同名次,允许后续名次空缺.
(1)排总名次:
在P3单元格输入=RANK(O3,$O$3:
$O$676),回车,便得到韩威同学在全年级的总分名次,然后选中P3单元格,当右下
角出现小“+”字图标时时使用拖拉大法往下自动填充,或左键双击即可.
(2)排班名次:
在Q3单元格中输入=SUMPRODUCT(($C$3:
$C$376=$C3)*($O3<$O$3:
$O$676))+1,然后按Ctrl+Shift+Enter,选中Q3单元格,当右下角出现小“+”字图标时时使用拖拉大法往下自动填充,或左键双击即可.
(二)设计成绩分析表
下面就以语文科分析表设计为例进行描述,其他科的分析相似操作。
1.设计分析表如图2所示.
将A1:
R1合并并居中,输入“**中学2007~2008学年度第一学期高一月考成绩分析表”。
用同样的方法将A2:
I2合并并居
中,输入“科目:
语文”其他文字按表格内容输入即可。
然后从A5至A14依次输入1,2,3……10班级名.
2.求语文科各班参考人数
我们在这里可采用COUNTIF函数,COUNTIF函数用于计算给定区域内满足特定条件的单元格的数目,所以在B5单元格中输入“=COUNTIF(成绩表!
$C$3:
成绩表!
$C$676,A5)”,即可求出1班的参考人数。
然后选中B5单元格,拖动B5单元格右下角的小“+”字图标至B14单元格(复制公式),这样,就能自动得到其他各班的参考人数。
最后在B15单元格中输入“=SUM(B5:
B14)”,用于分析全年级参考总人数。
3.求各班及格人数
我们在这里可采用数组公式进行多重标准单元计算。
首先在D5单元格中输入“=SUM(if((成绩表!
$C$3:
成绩表!
$C$676=A5)*(成绩!
$F$3:
成绩表!
$F$676,>=90),1,0))”,按下SHIFT+CTRL键敲回车,Excel会自动在公式两侧加上大括号,生成数组公式。
然后对D5至D14单元格进行向下自动填充操作。
最后在D15中输入“=SUM(D5:
D14)”,求出全级及格人数。
公式中的1表示当条件为真时返回结果计为1个,即及格1人,否则为0;
4.求各班及格率
我们可在E5单元格中输入“=IF(B5=0,0,D5/B54)*100”,为避免分母为0的错误,用了IF函数。
公式的作用为:
若1班参考人数不为0,则及格率为及格人数除以参考人数后乘以100。
然后对E5至E14单元格进行向下自动填充操作。
然后在E15中输入Average(E5:
E14),算出全年级的平均合格率。
5.按及格率班级排名次
在F5单元格中输入=RANK(E5,$E$5:
$E$14),然后往下自动填充.得到各班的及格率名次.
6.求各班优秀人数
首先在I5单元格中输入“=SUM(IF((成绩表!
$C$3:
成绩表!
$C$676=A5)*(成绩表!
$F$3:
成绩表!
$F$676,>=100),1,0))”然后对I5至I14单元格进行向下自动填充操作。
最后在I15单元格中输入“=SUM(I5:
I14)”,以求得全年级优秀人数。
如果规定全年级20%以上的学生为优秀人数,可以这样算:
=SUM(IF((成绩表!
$C$3:
成绩表!
$C$676=A5)*(成绩表!
$F$3:
成绩表!
$F$676>=LARGE(成绩表!
$F$3:
$F$676,INT(0.2*COUNT(成绩表!
$F$3:
$F$676)))),1,0))
其中LARGE(成绩表!
$F$3:
$F$676,INT(0.2*COUNT(成绩表!
$F$3:
$F$676)))所求的是全年级语文分数前20%中最低的一个同学的分数,SUM(IF((成绩表!
$C$3:
成绩表!
$C$676=A5)*(成绩表!
$F$3:
成绩表!
$F$676,”>=”LARGE(成绩表!
$F$3:
$F$676,INT(0.2*COUNT(成绩表!
$F$3:
$F$676))))则是求出1班语文学科高于或等于这个同学分数的人数.
8.求各班优秀率
跟求合格率类似,首先在J5单元格中输入“=IF(B5=0,0,I5/B5)*100”,为避免分母为0的错误,用了IF函数。
公式的作用为:
若107班参考人数不为0,则优生率为优生人数除以参考人数后乘以100。
然后对J5至J14单元格进行向下自动填充操作。
最后在J15中输入“=average(J5:
J14)”算出全年级的平均优秀率。
9.求各班平均分
求平均分用AVERAGE函数,其语法格式为AVERAGE(Ref),此处Ref为参与计算的单元格区域。
例如AVERAGE(F2:
F50)是求F2:
F50区域内数字的平均值。
默认情况下,Excel2003会忽略掉空白的单元格,但是它不忽略数值为0的单元格,要想忽略数值为0的单元格需要用到COUNTIF函数,其语法为COUNTIF(Range,Criteria),其含义是计算某个区域中满足给定条件的单元格数目。
本例求F2:
F50的平均分,如果忽略数值为0的单元格可以这样计算:
SUM(F2:
F50)/COUNTIF(F2:
F50,"<>0")。
另外如果要求去掉几个最高分和几个最低分然后取平均分的话,用到LARGE和SMALL函数,其语法格式为LARGE(array,k),含义是返回数组中第k个最大值,SMALL(array,k)
的含义是返回数组中第k个最小值。
如果我们求F2:
F50中去掉两个最高分和两个最低分之后的平均分可以这样计算:
“SUM(F2:
F50)-LARGE(F2:
F50,1)-LARGE(F2:
F50,2)-SMALL(F2:
F50,1)-SMALL(F2:
F50,2))/COUNTIF(F2:
F50-4)”。
在N5单元格中输入=SUMIF(成绩表!
$C$3:
成绩表!
$C$676,A5,成绩表!
$F$3:
成绩表!
$F$676)/B5,然后向下自动填充,便可得到各班的语文平均分,最后在N15单元格中输入=AVERAGE(N5:
N14),得到全年级语文平均分.
10.求各班最高分
求最高分可用MAX函数,这个函数的作用是求出参数中的最大值。
所以在R5单元格中输入“=MAX(IF(成绩表!
$C$3:
成绩表!
$C$676=A5,成绩表!
$F$3:
成绩表!
$F$676))”按下SHIFT+CTRL键敲回车,生成数组公式。
即可算出1班语文科中的最高分。
然后向下自动填充操作。
最后在R15单元格中输入“=MAX(R5:
R14)”,以求得全年级的最高分。
(三)设计分数段统计表
将A1:
F1合并并居中,输入“**中学2007~2008学年度第一学期高一月考分数段统计表”。
其他文字按表格内容输入即可。
然后从A3至A12依次输入1,2,3……10班级名.
1.求总分在600分以上的人数
计算分数段人数仍可用数组的方法,可在C3单元格中输入“=SUM(IF(成绩表!
$C$3:
成绩表!
$C$676=A3)*(成绩表!
$O$3:
成绩表!
$O$676>=600),1,0)”,按下SHIFT+CTRL键敲回车,然后进行向下自动填充操作。
然后在C13单元格中输入“=SUM(C3:
C12)”,以求得全年级600分以上的人数。
2.求500~599分数段人数
求各班总分在500~599分数段人数,将用到三个条件进行标准单元计算。
首先在D3中输入=SUM(IF((成绩表!
$C$3:
成绩表!
$C$676=A3)*(成绩表!
$O$3:
成绩表!
$O$676<600)*(成绩表!
$O$3:
成绩表!
$O$676>=500),1,0))按下SHIFT+CTRL键敲回车,生成数组公式。
然后进行向下自动填充操作。
最后在D13单元格中输入“=SUM(D3:
D12)”,求出全年级总分在500~599分数段人数。
3.求400~499分数段人数
求各班总分在400~499分数段人数,类似上述方法,只改变条件即可,首先在E3中输入=SUM(IF((成绩表!
$C$3:
成绩表!
$C$676=A3)*(成绩表!
$O$3:
成绩表!
$O$676<500)*(成绩表!
$O$3:
成绩表!
$O$676>=400),1,0)),按下SHIFT+CTRL键敲回车,生成数组公式。
然后进行向下自动填充操作。
最后在E13单元格中输入“=SUM(E3:
E12)”,求出全年级400~499分数段人数。
同样的操作,可以分别分析各班总分各分数段人数。
进行分数段统计的另外两种方法:
用Excel怎样统计出学生成绩各分数段内的人数分布时,很多文章都推荐使用CountIF函数,可是每统计一个分数段都要写一条函数,十分麻烦。
例如,要统计高一1班的语文成绩的分数段人数分布情况,可在在T76:
T80内统计显示F3:
F72内小于60分、60至70之间、70至80之间、80至90之间、90至100之间的分数段内人数分布情况,要输入以下5条公式:
1.在C58内输入公式统计少于60分的人数:
=CountIF(F3:
F74,"<60")
2.在C59内输入公式统计90分至100之间的人数:
=CountIF(F3:
F74,">=90")
3.在C60内输入公式统计80至90之间的人数:
=CountIF(F3:
F74,">=80")-CountIF(F3:
F74,">=90"),
4.在C61内输入公式统计70到80之间的人数:
=CountIF(F3:
F74,">=70")-CountIF(F3:
F74,">=80"),
5.在C62内输入公式统计60到70之间的人数:
=CountIF(F3:
F74,">=60")-CountIF(F3:
F74,">=70")。
如果要把0至10之间、10至20之间、20至30……90至100、100至110、110至120、120至130、130至140、140至150分之间这么多个分数段都统计出来,就要写上十几条公式了。
其实,Excel已经为我们提供了一个进行频度分析的FreQuency数组函数,它能让我们用一条数组公式就轻松地统计出各分数段的人数分布。
例如,我们要统计出总分在O3:
O676区域内高一1班至高一10班10个班级,300分以下,300至400,400至500,500至600,600分以上每个分数段内的人数分布:
如图4
1.在A4:
A8内输入:
0、299、399、499、599。
2.用鼠标选择区域C4至C9,在编辑栏内输入“=FREQUENCY(IF(成绩表!
$C$3:
$C$676=C2,成绩表!
$O$3:
$O$676,""),$A$4:
$A$8)”。
3.按“Crtl+Shift+Enter”组合键产生数组公式“={=FREQUENCY(IF(成绩表!
$C$3:
$C$676=C2,成绩表!
$O$3:
$O$676,""),$A$4:
$A$8)}”,这里要注意“{}”不能手工键入,必须按下“Crtl+Shift+Enter”组合键由系统自动产生。
完成后C4:
C9将显示如图所示的分数分布情况。
然后向右自动填充,得到其它各个班的分数段人数分布情况。
用CountIF函数统计分数段的方法流传很广,但效率并不高,而用FREQUENCY()函数就非常方便且高效
函数FREQUENCY主要功能是以一列垂直数组返回某个区域中数据的频率分布。
其语法格式:
FREQUENCY(data_array,bins_array)
参数说明:
Data_array表示用来计算频率的一组数据或单元格区域;Bins_array表示为前面数组进行分隔一列数值。
4.创建图表:
按以上方法创建好工作表,设置好打开权限密码和修改密码,保护好里面的数据,这样就可以快速准确地对学生成绩进行管理了。
既减轻了教师的工作负担,又提高了准确性和工作效率。
(四)创建模板
打开文件菜单,选择”另存为”,在”保存类型”中选择模板(*.XLT),文件名为”成绩统计分析.XLT”,)”。
以后要用的时候,直接双击打开就可以了。
如果经常要用,把模板存放在桌面上比较方便。
EXCEL计算名次的问题(两种方法if和rank函数)
在学校工作,要用EXCEL对学生的成绩进行名次计算。
但是出现了一些问题,最后使用了比较麻烦的方法才把名次算出来。
下面讲一下过程。
首先成绩表是这样的:
一、rank函数
第一个想到的方法是使用EXCEL中自带的rank函数。
rank函数可以计算出单元格在某数列中的排名。
函数编辑好之后对名次列(G列)进行公式填充。
使用rank函数后的效果,红框中代表的是函数:
但是我们如果对名次进行一个排序。
就发现结果很奇怪,如图所示:
再仔细看编辑栏中的函数,就发现问题所在。
原来在使用rank函数进行填充的时候函数中的数据列也跟着改变。
这个问题比较好解决,将函数中的相对引用转化成绝对引用就可以了(更改方法即是在行列中间插入一个"$"符号,例如A3更改为A$3),更改好了以后的图:
到这里笔者认为已经达成目标了,但是对名次列进行排序后又出现问题了:
注意红框中的内容,有两个同学并列第八名,但是往下就没有了第九名,直接到了第十名,并且只要有成绩并列的名次都出现了相同的问题。
可能rank函数不适用多个数值的排名。
于是放弃rank函数,重新思考。
二、if函数+选择性粘贴
既然rank函数失败了,那么就转向新的思考方向。
新办法的思路是这样:
1.对总成绩进行降序排序,并且确定第一名。
2.使用if函数,以总成绩第二名的同学为例。
⑴如果第二名同学的成绩比第一名同学的成绩小,那么第二名同学的名次就等于第一名同学的名次再加一。
⑵如果第二名同学的成绩不比第一名同学的成绩小,那么第二名同学的名次就等于第一名同学的名次。
3.以学号列为主要关键字进行排序,恢复表格的原始顺序。
1.对总成绩进行降序排列,在成绩最高的同学名次单元格上输入1:
2.在第二名同学的名次单元格中输入if函数,if(F3 该函数的目的在于,如果第二名同学的总成绩(F3)小于第一名同学的总成绩(F2),即(F3 那么第二名同学的名次等于第一名同学的名次加一(G2+1);如果不小于,那么就与第一名同学的名次相等(G2)。 随后填充公式,发现刚才rank函数的问题已经解决,并列第八名的两位同学的下一个同学就为正常的第九名。 3.最后一步即是要将表格按照学号进行排序,恢复表格的原始数据排列。 可是排序时又出现了问题: 问题在于笔者计算排名的时候使用的是函数,重新进行排序以后顺序变动,无法找准if函数处理后得到的值造成的。 解决这个问题的办法是将函数得到的值直接转化为数字,再进行排列。 先选中名次列->复制->右键->选择性粘贴,在对话框中选择数值 右键->选择性粘贴 选择数值 选择数值以后名次列的函数全部转化为数值 编辑栏中的函数变为数字 最后将学号列作为关键字进行升序排列,表格恢复到原始状态,并且排名列也填好,问题得以解决。 总结: 由于笔者对于EXCEL了解不是很多,对函数部分了解得也太少。 所以暂时只想到这个办法。 但是这个方法始终过于繁琐,可能使用某个函数或者某几个函数嵌套的方法或者在EXCEL中嵌入VB语言可以实现一步到位的计算名次。 希望知道更简单方法的朋友可以告之,也欢迎大家和我讨论好的方法。
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- Excel 进行学生成绩统计分析 进行 学生 成绩 统计分析