注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

零售创新,创新那些事儿,SPSS,VBA

零售创新

 
 
 

日志

 
 
关于我

新浪微博,零售创新 研究经理,数据分析师 希望和市场研究和零售业的同事共同进步! 本博客发表的都是免费或试用的资料,如果有版权问题请发邮件wangli12a@163.com联系删除。 spss excel vba blog

网易考拉推荐

Excel 进行学生成绩统计分析(转载)  

2012-03-31 16:44:52|  分类: VB和VBA知识 |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |

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.排名次:Microsoft Excel中用RANK函数求名次(排序),它的语法为:

RANK(Number,Ref,Order)   返回一个数字在数字列表中的排位。
        Number 为需要找到排位的数字。
        Ref 为数字列表数组或对数字列表的引用。Ref 中的非数值型参数将被忽略。如果使用公式复制,最好使用绝对地址。如

=RANK(E2,$E$2:$E$50)。注意,其中的标点符号用英文半角。
        Order 为一数字,指明排位的方式。如果order为0(零)或省略,Microsoft Excel 对数字的排位是基于ref为按照降序排列的列表。如果order不为零,Microsoft Excel对数字的排位是基于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区域内数字的平均值。默认情况下,Excel 2003会忽略掉空白的单元格,但是它不忽略数值为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”, )”。以后要用的时候,直接双击打开就可以了。如果经常要用,把模板存放在桌面上比较方便。

  评论这张
 
阅读(649)| 评论(0)
推荐 转载

历史上的今天

在LOFTER的更多文章

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2017