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

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

《电子表格在中小学成绩管理中的应用技巧.docx》由会员分享，可在线阅读，更多相关《电子表格在中小学成绩管理中的应用技巧.docx（14页珍藏版）》请在冰豆网上搜索。

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

电子表格在中小学成绩管理中的应用

电子表格（execl）以其用途广泛、操作简单、易学易用等多种优点，成为中小学校即word和powerpoint之后使用较广泛的office工具软件，一方面它能够实现教学各项数据管理要求，另一方面它不像专业数据库软件需要专业语言的支持，在学校成绩管理中应用广泛，作为一名电子表格的使用者和初学者，在此仅就成绩管理方面，通过本文和大家共享学习和使用过程中的收获，以期共同提高，并望对各校中小学成绩管理提供一定帮助。

一、掌握基本表内的公式

单班单表成绩统计是学校成绩管理的基础，通过对单班单表成绩统计最主要的是要理解并掌握常用的公式，只有通过反复练习，熟练其操作方法，才能达到熟能生巧和举一反三的效果。

如（图一）该班有99名学生，学生成绩在第3行从C列到G列。

设定目标：

1、统计出每个学生的总分、在备注列中统计出总分排名；2、统计出学科总成绩、平均分、优秀率、及格率和最高分、最低分；

（一）总分

1、个人总分

以01号学生为例计算其总分。

一是在H3单元格内输入=SUM（C3:

G3），回车后得出数值；二是在H3单元格内输入=c3+d3+e3+f3+g3，回车后得出数值。

注1：

“=”等号可以简单地认为是输入数据与插入公式进行运算的区分符号，Excel中的公式通常以（=）开始，用于表明之后的字符为公式。

紧随等号之后的是需要进行计算的元素（操作数），各操作数之间以运算符分隔。

同时，Excel将根据公式中运算符的特定顺序从左到右计算公式。

如在其一格中输入3+5则显示为3+5，若输入=3+5则显示为8，这是在学习电子表格公式时必须要明确的问题。

注2：

（:

）冒号区域运算符，产生对包括在两个引用之间的所有单元格的引用，C3:

G3是从C3到G3整体这一区域。

若要对物理、化学成绩进行折合，则乘以相应的折合率，如物理按70%，化学按50%，公式为=c3+d3+e3+f3*60%+g3*50%，回车后得出数值。

2、学科总分

以语文成绩为例计算该科总分。

在C102格内输入＝SUM（C3:

C102），回车后得出数值，由于人数在多一般不使用=C3+C4+……C101+C102。

3、其他学生和其他学科的总分

注3：

在Excel中，将某一单元格中的数据（字符或公式）复制到同列（行）中其他大量连续的单元格中时，通过采用“填充柄”来拖拉完成。

选中需要复制的单元格，然后将鼠标移至该单元格右下角，成“细十字”细状时（这种状态我们称之为“填充柄”），按住鼠标左键向下拖拉，即可将公式复制到下面的单元格区域中。

如先单击H3单元格，再把鼠标移至在H3单元格的右下角，其变为细线“+”字状后，按下鼠标左键下拉至H101，即可得到全部学生的总分。

先单击H101单元格，再把鼠标移至在H101单元格的右下角，其变为“+”字后，按下鼠标左键下拉至I101，即可得到全部学科的总分。

在实际使用中，变通使用“拖拉法”可以使填充更加便捷。

这也是在批量处理行、列公式中最常用的一项基础操作。

当再单击被拖动过的单元格时，会发现电子表格已智能地为其插入了对应的公式。

实际操作中学生人数和学科不同时，其操作方法相同。

（二）平均分

1、个人平均分

以02号学生为例计算其平均分。

一是在I3单元格内输入=AVERAGE（C3:

G3），回车后得出数值；二是可利用总分公式，用总分除以学科总数，即在I3单元格内输入输=H3/5，回车后得出数值。

2、学科平均分

以语文成绩为例计算该科总分。

一是在C102格内输入＝AVERAGE（C3:

C101），回车后得出数值。

二是可利用总分公式，用总分除以学生总数，若C102为语文总分，即在C103单元格内输入输=C102/99，回车后得出数值。

注4：

在成绩统计过程中，为了保证统计结果的准确性，通常可采用一种公式计算出结果，并用另一种公式进行验算。

（三）某一分数段人数和及格率、优秀率统计

以语文学科为例。

大于等于60分：

=COUNTIF（C3:

C101,">=60"）

低于60分：

=COUNTIF（C3:

C101,"<60"）

大于等于80分：

=COUNTIF（C3:

C101,">=80"）

区间内学生总数：

=COUNT（C3:

C101）*100

60-69分：

=COUNTIF（C3:

C101,">=60"）-COUNTIF（C3:

C101,">=70"）

70-79分：

=COUNTIF（C3:

C101,">=70"）-COUNTIF（C3:

C101,">=80"）

80-89分：

=COUNTIF（C3:

C101,">=80"）-COUNTIF（C3:

C101,">=90"）

及格率：

=COUNTIF（C3:

C101,">=60"）/COUNT（C3:

C101）*100

优秀率：

=COUNTIF（C3:

C101,">=80"）/COUNT（C3:

C101）*100

注5：

（,）逗号联合运算符，将多个引用合并为一个引用。

通过

（1）3操作，即可得到相应学科的各项统计。

在通常情况下，≥80分为优秀，≥60分为及格，所以在优秀率和及格率公式中以80分和60分为标准。

如满分120分，通常设定为96分和72分，在计算总分的优秀率和及格率时，根据全部学科灵活设定。

（四）名次、最高分、最低分统计

以03号学生为例计算其总分在班里的排名，一是在J3单元格内输入=RANK（H5,$H$3:

$H$101），回车后得出数值。

二是在J3单元格内输入=COUNTIF（$H$3:

$H$101,">"&$H5）+1，回车后得出数值。

通过

（1）3操作，即可得到相应学生的名次。

注6：

$为绝对引用符号，$H$3为绝对列和绝对行，H$3为相对列和绝对行，H3为相对列和相对行。

如（H5,$H$3:

$H$101）和（H5,H3:

H101）的区别在于：

当进行拖动时，H5会发生变化，$H$3:

$H$101这一区域不变；而（H5,H3:

H101）进行拖动时，H5在发生变化的同时其H3:

H101区域同时发生变化。

注7：

公式中用到的符号均为英语输入法状态下的符号，不能是汉语中的符号。

如引号应为""而不能是“”。

最高分＝MAX（C3:

C101）

最低分＝MIN（C3:

C101）

如（图二）已完成了该班成绩的常规统计。

通过（表二）可以看到学生的各项成绩和统计结果都在一张表上，这仅仅是实现了对成绩统计的基本要求，但一方面不美观，另一方面在实际操作中存在着很多的缺点。

通常情况下可使用单班双表进行统计。

同时，在成绩统计过程中不直接在成绩录入表上进行统计。

可对录入的成绩进行备份，作为原始数据使用。

统计时在备份文件上操作，也可把成绩录入表和成绩统计表分开，如成绩录入表为表一名为sheet1，成绩统计为表二名为sheet2，这样还可避免因错误操作造成重新录入或进行再次录入核准。

（五）引用

如[注8]中，统计表表二的生成，往往通过表的复制粘贴或单元格复制粘贴来完成，这样如果某表中的一个数据在变更后，另一个表也要进行修改，为使表一和表二的数据始终保持一致，可通过单元格引用实现。

在表二A1中输入=Sheet1!

A1，回车后得出数值。

通过“填充柄”生成其他全部数据，生成表二。

二、学会表间公式引用

为使统计表表二的统计结果更为清晰，可在表二的相应单元格中输入公式来对表一进行计算和统计。

以表Sheet1中的01号学生总分为例，若在同一表内：

即在Sheet1H3单元格内输入=SUM（C3:

G3），若在不同表内计算如Sheet2则要插入引用，在某一单元格内输入=SUM（Sheet1!

C3:

G3）。

平均分为＝AVERAGE（Sheet1!

C3:

C101）

９0分以上人数=COUNTIF（Sheet1!

C3:

C101,">=90"）

优秀率=COUNTIF（Sheet1!

C3:

C101,">=80"）/COUNT（Sheet1!

C3:

C101）*100

及格率=COUNTIF（Sheet1!

C3:

C101,">=60"）/COUNT（Sheet1!

C3:

C101）*100

最高分＝MAX（Sheet1!

C3:

C101）

最低分＝MIN（Sheet1!

C3:

C101）

此类引用用处广泛。

三、建立常用的统计模版

通过以上内容已经可以完成对单班统计，统计出各班成绩后即可建立年级的统计。

这样就要对每个班进行分别统计后再进行汇总，工作量大且较易出错。

所谓模版就是利用电子表格中表间公式的引用，在表与表之间建立起相应关联，通过关联自动产生统计数据。

简单地说就是在录入成绩后，电子表格会自动计算统计出所需要的数据。

下面以单班双表、多班多表、多班双表为例说明模版建立方法。

单班双表模版：

以全年级班额数最大的为计算基数，如上例仍以101人为公式总人数，不足人数为空格，将不计入统计结果。

如（图一）为成绩录入表，如（图三）在sheet2中进行统计计算。

分别在相应单元格内插入引用公式，在指定域前加上表名Sheet1!

。

图一

注8：

对该文档进行保存，更名为成绩模版，把其他班级录入成绩复制到sheet1后，在sheet2中即得到相关统计数据，然后更名另存得到新文件。

多班多表模版：

这是学校进行成绩统计用到的最常用的方法，分别在电子表格中Sheet1、Sheet2……Sheetn中插入同一年级中对应各班级的成绩录，同时建立表格Sheet（n+1）（如下图）作为统计模版。

统计的各类基础公式不变，改变其选择域，如单班双表操作相同，分别在对应班级公式中插入的指定域前加上表名Sheet1!

、Sheet2!

……Sheetn!

。

例各班语文平均分

一班：

D3=AVERAGE（Sheet1!

$C$3:

$C$101）

二班：

D10=AVERAGE（Sheet2!

$C$3:

$C$101）

三班：

D17=AVERAGE（Sheet3!

$C$3:

$C$101）

其他依次类推。

当对每次Sheet1、Sheet2……Sheetn进行数据更改后，将自动在Sheet（n+1）得到更新后的统计。

多班双表

例各班语文优秀率

一班：

B4=COUNTIF（Sheet1!

$C$3:

$C$101,">=80"）/COUNT（Sheet1!

$C$3:

$C$101）*100

二班：

B5=COUNTIF（Sheet2!

$C$3:

$C$101,">=80"）/COUNT（Sheet1!

$C$3:

$C$101）*100

三班：

B6=COUNTIF（Sheet3!

$C$3:

$C$101,">=80"）/COUNT（Sheet1!

$C$3:

$C$101）*100

其他依次类推。

注9：

对该文档进行保存，更名为成绩模版，把其他班级录入成绩复制到sheet1、Sheet2……Sheetn后，在Sheet（n+1）中即得到相关统计数据，然后更名另存得到新文件。

在图与图的两种统计中插入引用公式的方法相同，当一组公式插入后，可通过“填充柄”插入其他单元格公式，然后选中相应的行或列进行替换操作。

多班单表模版：

由于考场混编，各班学生在各场分布较为混乱，当然，可以对班级进行排序后变成多班多表进行统计。

最简单的办法通过公式组合直接进行统计。

A列序号，B列班级，C列姓名，其中F列为语文成绩。

平均分=SUMPRODUCT（（Sheet1!

$B$2:

$B$1000="一班"）*

（Sheet1!

$f$2:

$f$1000）/COUNTIF（Sheet1!

$B$2:

$B$1000,"一班"））

意思为：

对表1中在B2到B1000中符合“一班”这一条件的单元格，同时，对其f列进行求平均值。

大于80分为数=SUMPRODUCT（（Sheet1!

$B$2:

$B$1000="一班"）

*（Sheet1!

$f$2:

$f$1000>=80））

意思为：

对表1中在B2到B1000中符合“一班”这一条件的单元格，同时，统计其f列符合>或=80分这一条件的个数。

及格率=SUMPRODUCT（（Sheet1!

$B$2:

$B$1000="一班"）*

（Sheet1!

$f$2:

$f$1000>=60）/COUNTIF（Sheet1!

$B$2:

$B$1000,"一班"）*100）

意思为：

对表1中在B2到B1000中符合“一班”这一条件的单元格，同时，对其f列进行求及格率。

四、其他应有

1、某一学生成绩在班级和全校的排名

首先要制作出如下图的表格模版，班级、姓名、学科、成绩等均为上下两单位格合并成的一个单元格。

单科成绩或总分为四个单元格，其中前两个合并后粘贴成绩，后两年单元格不合并，上为年级排名，下为班级排名。

年级排名

=RANK（Sheet6!

D3,Sheet6!

D3:

D22）

班级排名

=SUMPRODUCT（（Sheet6!

A3:

A22=Sheet6!

A3）*（Sheet6!

D3:

D22>Sheet6!

D3））+1

2、邮件合并批量打印统一规格报表

掌握了单班单表的统计方法，就基础上达到了学校的统计的最基本要求，但是对统计者来讲，一年N次的成绩统计这一项细心、繁重、枯燥的工作，费心、费时、费力，每次都要细心地插入公式进行计算，常常是想得头痛、累得腰痛、看得眼痛，还担心一旦出错后教师的不满和领导的批评，更主要的是内心的自责。

如何解决这一问题？

通过单班双表利用公式的表间引用轻松解决。

（4）引用

公式的表间

我设计过一张学生成绩统计表，表中除了上面这些内容外还包括分段人数、几门主课总分，总分排名等。

=AVERAGE（）平均

至于及格和优秀率

=COUNTIF（C3:

C19,">60"）可以数出大于60等的个数

及格率=COUNTIF（B1:

B19,">60"）/COUNT（B1:

B19）

优秀率把60改成优秀的分数就行

假设A1:

B25是你的数据区域，公式为

=SUMPRODUCT（（A1:

B25>=60）*（A1:

B25<=89.5））

排名次

“=RANK（E2,$E$2:

$E$76）”，

=RANK（D1,$D$1:

$D$14）

=AVERAGE（I1:

I521）

=AVERAGE（J1:

J521）

=AVERAGE（K1:

K521）

=COUNTIF（I1:

I521,">=80"）/COUNT（I1:

I521）*100

=COUNTIF（J1:

J521,">=80"）/COUNT（J1:

J521）*100

=COUNTIF（K1:

K521,">=160"）/COUNT（K1:

K521）*100

=COUNTIF（I1:

I521,">=60"）/COUNT（I1:

I521）*100

=COUNTIF（J1:

J521,">=60"）/COUNT（J1:

J521）*100

=COUNTIF（K1:

K521,">=120"）/COUNT（K1:

K521）*100

制作管理模版

实战中的技巧

1、准考证制作邮件合并

2、

3、

4、

5、

准备工作：

1、小键盘使用

2、

3、

4、

5、

6、

定期对学生进行学习质量监测是学校教学工作的个重要环节。

教师必须在考试结束后及时对学生的考试成绩进行统计分析，掌握学生学习质量现状，教务处领导要快速将全校成绩汇总，为校长提供分析学校教学质量的数据。

面对诸多的统计项目，许多教师开始在计算机中使用Excel软件进行统计。

如何利用Excel进行成绩统计，有许多介绍的文章，但是里面涉及的函数公式让很多教师茫然。

笔者结合多年的工作实践，用Excel制作了一个能够自动统计成绩的电子表格模板提供给教师。

只要在“空白”模板中填上学生成绩，各统计数据就立刻显现出来，能够让教师方便快捷、准确无误进行统计，轻松完成繁重的工作，班级的教学质量数据一目了然，彻底告别那些眼花缭乱的繁杂统计工作，提高了工作效率。

　　下面笔者将电子工作表格制作的方法作以介绍，希望能给教学一线辛勤的教师和教务工作者一点帮助。

【教学内容】Excel电子表格在学校成绩管理中应用

【课前分析】本教学过程的创新点在于教法和学法。

教法是：

教师点拔→学生操作→产生问题→解决（与辅导老师一起）→练习→掌握。

学法是：

依据已掌握的知识+操作的类同性+对话框提示。

教学过程采用任务驱动法，由老师先进行概念的解释及要点的提示，然后通过设计合理的、分层递进的练习，让学生在做练习的过程中发现问题、解决问题，带着问题去听课，寻求答案。

通过解惑、总结、反复练习，加深理解，培养学生举一反三的能力和观察、归纳总结的能力。

教学反馈表的设计是对本教学方法的有力补充。

自学过程采取分组合作的方式，较好地解决了职校学生个体差异大的问题，在分组时根据对学生的了解，进行合理的安排，使学习有困难的学生能获得较好的帮助，由学生通过互助的方式解决了传统教学中教师难以顾及所有学困生的问题。

课后的思考练习题是Excel的一个大综合题，在学习之后，有利于锻炼学生解决实际问题的能力，作为课后练习提出任务，起到承上启下的作用。

【教学目标】

1、学法目标：

理解Excel在学校成绩管理中的实际应用，学习用函数、排序和筛选、统计及分析数据表中的数据。

2、能力目标：

学会用Excel中的基本功能对学生成绩进行统计和分析。

3、创新目标：

①引导学生学会一种自主学习的方法；

②通过任务驱动的方式，培养学生勇于质疑、主动解决问题的积极性，变被动学习为主动学习。

4、情感目标：

①学会合作，向同伴学习，培养同学们的团队合作精神；

②让学生从中体验成功、乐学。

【教学重点】筛选和数据透视表的应用。

【教学难点】数据分析和图表的建立。

【教学过程】

一、课堂引入：

成绩管理是所有学校学籍管理的基本工作，特别是中小学更强调对学生的成绩管理，虽然现在能够较容易地找到许多免费版的学籍管理软件，但是每个学校都有自己的管理特点和要求，这些软件在使用中总是会存在一些不便之处。

如何使用现有的办公软件，进一步挖掘软件的功能来完成我们的工作，许多教师已经探讨了许多实用的经验和技巧，这里主要和大家一起探讨如何使用Excel电子表格中的各个功能，实现成绩管理中的基本应用。

二、新课讲授：

教学过程

I、导入新课

去年我们学习了Excel中的基本应用，今天我们结合实际，利用两节课的时间来制作一个学生的通用成绩报告单。

II、讲授新课

一、数据的引用

先认真分析工作表“课程”和“名单”

[讲解]

完成“第一期成绩”和“第二期成绩”的单元格地址的引用，注意绝对地址和相对地址引用。

1、学号：

“=名单!

B5”

2．姓名：

“=名单!

C5”

3．课程名：

“=课程!

C4”

完成相应的复制。

二、建立工作表“成绩单”，如下图：

1．选择“三好学生”名单：

利用公式计算有简明的优点，但对于单元格过多的情况在处理上较繁琐，Excel提供了函数输入的计算方法。

主要的函数有：

SUM，AVERAGE，COUNT，MAX，MIN，IF等。

2、Excel中的函数“OFFSET”的应用：

注意：

公式中如果以A1为参照单元格，则公式为

=OFFSET（A1,100，7,1,1）

函数OFFSET（）格式——

=OFFSET（引用偏移的参照单元格，参照单元格向下/上偏移的行数，偏移参照单元格所在列的列数，返回引用区域的行数，返回引用区域的列数）。

例如：

学号：

“=OFFSET（名单!

A4,B2,1,1,1）”

姓名：

“=OFFSET（名单!

B4,B2,1,1,1）”

成绩：

“=OFFSET（第1期成绩!

C4,$B$2,1,1,1）”

注意：

在引用偏移的参照单元格时，每个显示当前的单元格多不需要偏移，也就是说，输入学号为“1”的，那当前显示的应该就是本人，不要偏移“1”，而应该是偏移“0”，所以在这个地方应该有一个让学号减去“1”的算法,或者改变偏移量。

如何排除学号输入"0"时出错的问题:

=if（b2=0,99,b2-1）

或者设定该单元格数据的＂数据有效性＂为＂1-56＂学号值范围内有效。

3．测试成绩单

在输入学号后面输入学号，即出现该学生“第一期成绩”和“第二期成绩”的成绩单。

III、练习、作业、预习

完成成绩单的制作。

IV、教学小结

本节主要讲述了工作表之间的引用和函数OFFSET的使用方法，通过这几个知识点完成我们教师经常用到的学生成绩单的制作，实例不仅能将知识点讲透，而且在具体工作中也很有实际用途，希望同学们能在课后认真完成作业，起到举一反三的效果。

三、课后作业

作业提交：

将制作好的课件在“作业提交”中上传作业，作业没提交的不可记录成绩，请大家注意。

用数据透视表显示3个班男生、女生的英语成绩的平均分情况。

【教后分析】

基本教学思想正确，任务的设计也较合理，能充分调动学生的积极性，较好地完成了教学任务。

不足之处：

教师在教学设计时太过于想让学生一节课的时间通过比较掌握两种方法，体会其重要性，而少考虑了如何更好地激趣。

如果改为让学生随意选用自己想用的方法去进行操作，不必作规定和硬性安排，在设计时更多地考虑学生的主体作用，会有更为理想的效果。

此外，在筛选内容的安排上，亦可以通过向学生提供多种方案（选题）的方式，让学生选择或自己定题目，而不拘泥于死板的内容，则会更有利于调动学生学习的积极性和主动性，培养创造性和创新精神，活跃课堂气氛。

【教学评析】

本课教学内容是信息技术课程中较理性的知识，教学难度较大，教师教学目标明确，思路清晰，结构完整，层次分明，情景创设自然有趣，任务设置富于启发性，突出重点、分解难点，讲练结合，体现了信息技术课程的教学规律，有较好的创新意识。

教师教学基本功扎实，学生听课率高，教学效果理想。

希望教师在任务设计上更好地激发学生的兴趣，为学生自主学习服务的教育思想方面提供更好的帮助。

在单表多班中计算年级排名和班级排名

=SUMPRODUCT（（$A$3:

$A$23=$A3）*（$D$3:

$D$23>$D3））+1

=SUMPRODUCT（（$A$3:

$A$12=$A10）*（$G$3:

$G$12>$G10））+1

=COUNTIF（$G$2:

$G$11,">"&$G2）+1

=RANK（$C16,（$C$16:

$C$20,$F$16:

$F$20））

=SUMPRODUCT（（$d$5:

$d$23="一"）*/RANK（d5,$d$5:

$d$23,””））

=SUMPRODUCT（（Sheet1!

$B$2:

$B$2186="第一小学"）*（Sheet1!

$I$2:

$I$2186）/COUNTIF（Sheet1!

$B$2:

$B$2186,"第一小学"））

=SUMPRODUCT（（Sheet1!

$B$2:

$B$2186="第一小学"）*（Sheet1!

$I$2:

$I$2186>=80）/COUNTIF（Sheet1!

$B$2:

$B$2186,"第一小学"）*100）

=

=RANK（D3,$D$3:

$D$23）

=SUMPRODUCT（（Sheet1!

$B$2:

$B$2186="第一小学"）*（Sheet1!

$J$2:

$J$2186）/COUNTIF（Sheet1!

$B$2:

$B$2186,"第一小学"））