电子表格在中小学成绩管理中的应用技巧1.docx

Excel在中小学成绩分析统计中的应用技巧学校在质量监测后对学生成绩进行统计分析是教育教学管理工作的重要环节。及时准确地对每次成绩统计分析，为校领导提供科学决策依据，这是各校一项繁琐的工作。面对诸多的统计项目，本文就如何使用Excel方便快捷、准确无误地进行成绩统计分析提供一定参考。一、掌握基本公式如某班有99名学生，学生成绩在第3行从C列到G列。如图（一）总分（求和）1、在H3=SUM(C3:G3)，回车后得出数值；2、在H3单元格内输入=c3+d3+e3+f3+g3，回车后得出数值。（二）平均分个人平均分：1、在I3单元格内输入=AVERAGE(C3:G3) ，回车后得出数值；2、可利用总分公式，用总分除以学科总数，在I3单元格内输入输=H3/5，回车后得出数值；学科平均分：1、在C103单元格内输入AVERAGE (C3:C101) ，回车后得出数值；2、可利用总分公式，用总分除以学生总数，在C103单元格内输入输= C102/99，回车后得出数值。（三）某一分数段人数和及格率、优秀率统计1、大于等于60分：=COUNTIF(C3:C101,=60)2、低于60分：=COUNTIF(C3:C101,=80)4、区间内学生总数：=COUNT(C3:C101)*1005、60-69分：=COUNTIF(C3:C101,=60)-COUNTIF(C3:C101,=70)6、70-79分：=COUNTIF(C3:C101,=70)-COUNTIF(C3:C101,=80)7、80-89分：=COUNTIF(C3:C101,=80) -COUNTIF(C3:C101,=90)8、及格率：=COUNTIF(C3:C101,=60)/ COUNT(C3:C101)*1009、优秀率：=COUNTIF(C3:C101,=80)/ COUNT(C3:C101)*100（四）名次、最高分、最低分统计以03号学生为例计算其总分在班里的排名，1、在J5单元格内输入=RANK(H5, $H$3: $H$101) ，回车后得出数值2、在J5单元格内输入=COUNTIF($H$3:$H$101, &$H5)+1 ，回车后得出数值。3、最高分MAX(C3:C101)4、最低分MIN(C3:C101)二、学会表间引用在实践中通常把表1作为成绩登录表，用表2进行成绩统计，在表2的相应单元格中输入公式来对表1进行计算和统计。以表1中的01号学生和全班语文成绩为例。1、总分= SUM(Sheet1!C3:G3)。2、平均分为AVERAGE (Sheet1!C3:C101)3、90分以上人数 = COUNTIF(Sheet1!C3:C101,=90)4、优秀率=COUNTIF(Sheet1!C3:C101,=80)/ COUNT(Sheet1!C3:C101)*1005、及格率=COUNTIF(Sheet1!C3:C101,=60)/ COUNT(Sheet1!C3:C101)*1006、最高分MAX(Sheet1!C3:C101)7、最低分MIN(Sheet1!C3:C101)三、建立统计模版每次质量监测后都重复上述操作，工作量大且易出现错误，这样就需要建立统计模版。模版就是在表与表之间建立起相应关联，利用电子表格中表间公式的引用，当更新成绩登录表后，通过表的关联在成绩统计表自动进行分析统计。简单地说就是在录入成绩后，电子表格会自动计算统计出所需要的数据。也就是说，在以后的成绩分析统计中，通过建立的模版，当完成成绩的登录即可马上得到成绩分析统计表。单班双表模版在问题二表间公式引用中，既是把表1成绩登录表和表2 成绩分析统计表进行了关联，当对表1进行更新时，表2将随之变化。多班多表模版这是学校进行成绩统计用到的最常用的方法，分别在电子表格中Sheet1、Sheet2Sheetn中粘贴上或插入同一年级中对应各班级的成绩登录表，同时建立表格Sheet（n+1）作为年级登录汇总表，Sheet（n+2）作为年级分析统计模版。统计的各类基础公式不变，改变其选择域，如单班双表操作相同，分别在对应班级公式中插入的指定域前加上表名Sheet1!、Sheet2! Sheetn!。例各班语文平均分一班： =AVERAGE (Sheet1! $C$3:$C$101)二班： =AVERAGE (Sheet2! $C$3:$C$101)三班： =AVERAGE (Sheet3! $C$3:$C$101)各班语文优秀率一班： =COUNTIF(Sheet1!$C$3:$C$101,=80)/ COUNT(Sheet1!$C$3:$C$101)*100二班： =COUNTIF(Sheet2!$C$3:$C$101,=80)/ COUNT(Sheet1!$C$3:$C$101)*100三班： =COUNTIF(Sheet3!$C$3:$C$101,=80)/ COUNT(Sheet1!$C$3:$C$101)*100其他依次类推，当对每次Sheet1、Sheet2Sheetn进行数据更改后，将自动在 Sheet（n+2）得到更新后的统计。全年级的某学科总平均分、优秀率、及格率、某成绩段人数统计可通过Sheet（n+1）进行分析统计；若用分表则过于复杂,如平均分=AVERAGE (Sheet1! $C$3:$C$101,Sheet2! $C$3:$C$101, Sheetn! $C$3:$C$101)，较为不便，各类公式的组合不再介绍。多班双表模版由于考场混编，各班学生在各场分布较为混乱，当然，可以对班级进行排序后变成多班多表进行统计。最简单的办法通过公式组合直接进行统计。如每班55人全年级共10个班级，即全年级共550人。表1为全年级成绩登录汇总表，其中表1A列为学生班级名称，其他不变，则语文成绩应在D列；表2为成绩分析统计表。1、平均分一班的语文平均分=SUMPRODUCT(Sheet1!$A$3:$A$553=一班)*(Sheet1!$D$3:$D$553)/COUNTIF(Sheet1!$A$3:$A$553,一班)意思为：对表1中从A3到A553行中符合“一班”这一条件的单元格，同时，对其D列进行求平均值。二班的语文平均分=SUMPRODUCT(Sheet1!$A$3:$A$553=二班)*(Sheet1!$D$3:$D$553)/COUNTIF(Sheet1!$A$3:$A$553,二班)意思为：对表1中从A3到A553行中符合“二班”这一条件的单元格，同时，对其D列进行求平均值。2、成绩段人数统计一班数学成绩80分以上的人数为=SUMPRODUCT(Sheet1!$A$3:$A$553=一班)*( Sheet1!$E$3:$E$553=80)意思为：对表1中从A3到A553行中符合“一班”这一条件的单元格，同时，统计其E列符合 或=80分这一条件的个数。十班数学成绩80分以上的人数为=SUMPRODUCT(Sheet1!$A$3:$A$553=十班)*( Sheet1!$E$3:$E$553=80)3、及格率一班英语成绩及格率为=SUMPRODUCT(Sheet1!$A$3:$A$553=一班)*(Sheet1!$F$2:$F$553=60)/COUNTIF(Sheet1!$A$3:$A$553,一班)*100)意思为：对表1中从A3到A553行中符合“一班”这一条件的单元格，同时，对其F列进行求及格率。五班英语成绩及格率为=SUMPRODUCT(Sheet1!$A$3:$A$553=五班)*(Sheet1!$F$2:$F$1000=60)/COUNTIF(Sheet1!$A$3:$A$553,五班)*100)四、班级成绩表在对全校成绩进行分析统计后，还要为每个班生成本班成绩表，在一单元格内同时出现某一学生成绩在班级和全校的排名。首先要制作出如下图的表格模版，班级、姓名、学科、成绩等均为上下两单位格合并成的一个单元格。单科成绩或总分为四个单元格，其中前两个合并后粘贴成绩，后两年单元格不合并，上为年级排名，下为班级排名。01学生语文成绩的年级排名=RANK(Sheet1!D3,Sheet1!D3:D990)01学生语文成绩的班级排名=SUMPRODUCT(Sheet1!A3:A990=“一班”)*(Sheet1!D3:D990Sheet1!D3)+1需要说明的是由于使用了单元格合并，本表和各类数据使用粘贴或引用命令较易出现错误，通常使用公式引入，即：=OFFSET(Sheet1!A$3,INT(ROW(Sheet1!A2)-1)/2),)五、操作中应注意的问题1、“=” 等号可以简单地认为是输入数据与插入公式进行运算的区分符号，Excel中的公式通常以（=）开始，用于表明之后的字符为公式。紧随等号之后的是需要进行计算的元素（操作数），各操作数之间以运算符分隔。同时，Excel 将根据公式中运算符的特定顺序从左到右计算公式。如在其一格中输入3+5则显示为3+5，若输入=3+5则显示为8，这是在学习电子表格公式时必须要明确的问题。2、（:）冒号区域运算符，产生对包括在两个引用之间的所有单元格的引用，C3:G3是从C3到G3整体这一区域。3、在Excel中，将某一单元格中的数据（字符或公式）复制到同列（行）中其他大量连续的单元格中时，通过采用“填充柄”来拖拉完成。选中需要复制的单元格，然后将鼠标移至该单元格右下角，成“细十字”细状时（这种状态我们称之为“填充柄”），按住鼠标左键向下拖拉，即可将公式复制到下面的单元格区域中。 4、在成绩统计过程中，为了保证统计结果的准确性，通常可采用一种公式计算出结果，并用另一种公式进行验算。5、（,）逗号联合运算符，将多个引用合并为一个引用 。6、$为绝对引用符号， $H$3 为绝对列和绝对行，H$3 为相对列和绝对行，H3 为相对列和相对行。如(H5, $H$3: $H$101)和(H5, H3