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

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

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

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

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

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

电子表格（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