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

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

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