电子表格在中小学成绩管理中的应用技巧模板.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,=80)区间内学生总数: =COUNT(C3:C101)*10060-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、 Sheet2Sheetn中插入同一年级中对应各班级成绩录, 同时建立表格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、 Sheet2Sheetn进行数据更改后, 将自动在 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、 Sheet2Sheetn后, 在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:D22Sheet6!D3)+12、 邮件合并批量打印统一规格报表掌握了单班单表统计方法, 就基础上达成了学校统计最基础要求, 不过对统计者来讲, 一年N次成绩统计这一项细心、 繁重、 枯燥工作, 费心、 费时、 费力, 每次都要细心地插入公式进行计算, 常常是想得头痛、 累得腰痛、 看得眼痛, 还担心一旦犯错后老师不满和领导批评, 更关键是内心自责。怎样处理这一问题？经过单班双表利用公式表间引用轻松处理。（4）引用公式表我设计过一张学生成绩统计表, 表中除了上面这些内容外还包含分段人数、 几门主课总分, 总分排名等=AVERAGE() 平均 至于及格 和 优异 率 =COUNTIF(C3:C19,60)能够数出大于60等个数 及格率 =COUNTIF(B1:B19,60)/COUNT(B1:B19) 优异率 把60改成优异分数就行假设A1:B25是你数据区域, 公式为=SUMPRODUCT(A1:B25=60)*(A1:B25=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