基于Excel函数实现学生成绩有效管理

更新时间:2024-02-07 作者:用户投稿原创标记本站原创 点赞:6327 浏览:21670

摘 要:本文基于Excel函数实现对学生成绩的有效管理,旨在说明Excel电子表格作为目前应用比较广泛的数据处理软件在教务管理实践的具体应用.本例主要利用VLOOKUP、INDIRECT、ALL、TEXT和COUNTIF等函数,来实现对外部数据的读取、高级筛选,数据的统计、分析和查询等功能.

关键字:Excel函数应用;教务管理;学生成绩管理;数据处理

中图分类号:TP317.3 文献标识码:A DOI:10.3969/j.issn.1003-6970.2013.06.040


0 背景

Excel电子表格是目前在学校教务管理工作中应用比较广泛的数据处理软件,它的有效应用为教务管理实现科学化、规范化、精细化开辟了一个全新的领域.为使Excel应用不仅仅停留在解决个体的问题层面上,教务管理人员应及时转变传统纸质管理的思维方式,努力提高Excel应用技能,促使Excel被深入应用到教务管理的整体体系中去,进而提高管理效率.本例利用VLOOKUP、INDIRECT和COUNTIF等函数,实现对学生成绩有效管理,操作简单,通用性强,在教务管理工作中具有一定的实用性.

1功能描述

本系统涵盖了四个主要模块,具体结构如图1所示.

1.1“读取单科成绩”模块.学校教务处统一规定《学生单科成绩表》成绩登记格式(如图2所示),任课教师按指定格式登记成绩,将成绩以电子表格的形式直接交给教务管理人员,再由教务管理人员按班级分类,根据学期教学计划制作本学期班级期评成绩汇总表(如图3所示),利用VLOOKUP、INDIRECT等函数实现对外部单科成绩的读取.

其中,VLOOKUP函数主要功能:最终返回该列所需查询列序所对应的值,是Excel中的一个纵向查找函数.INDIRECT函数主要功能:此函数立即对引用进行计算,并显示其内容.当需要更改公式中单元格的引用,而不更改公式本身,请使用函数 INDIRECT.

1.2“学生成绩查询”模块.根据《补考信息统计表》(如图4所示)提供的数据,通过在《学生成绩查询》工作表(如图5所示)F6单元格中输入学生的学号,来实现对某个学生所有成绩的查询功能.该模块主要利用INDIRECT、TEXT、ALL、ROW、COLUMN等函数来实现《补考信息统计表》中的高级筛选功能,利用If 、VLOOKUP等函数来实现《学生成绩查询》工作表中的数据查询功能.

其中,TEXT函数主要功能:将数值转换为按指定数字格式表示的文本.ALL函数主要功能:返回数据组中的第K个最小值.ROW函数主要功能:返回一个引用的行号.

1.3“考试质量分析”模块.根据《单科成绩分析》(如图6所示)提供的数据,通过在《考试质量分析》(如图7所示)表B2单元格中输入任课教师的姓名,来实现对某个教师所教课程的授课质量分析、统计、查询功能.该模块主要利用COUNTA、MAX、MIN、ERAGE、COUNTIF等函数来实现《单科成绩分析》中的数据分析、统计功能,利用If 、VLOOKUP函数来实现《考试质量分析》表中的查询功能.

其中,COUNTA函数主要功能:返回参数列表中非空值的单元格个数.利用函数 COUNTA 可以计算单元格区域或数组中包含数据的单元格个数.COUNTIF函数主要功能:对指定区域中符合指定条件的单元格计数的一个函数.

2.具体实现

2.1“读取单科成绩”模块.根据如图3所示的表结构,创建《读取单科成绩》工作表.

在《读取单科成绩》工作表C5单元格中输入以下核心代码,然后选中C5:G45单元格区域,分别向右、向下自动填充公式,结果如图3所示.C5等于VLOOKUP($A5,INDIRECT(“’[《“&C$3&”》单科成绩表.xls]12计信1’!$A:$O”),15,0);D5等于VLOOKUP($A5,INDIRECT(“’[《“&D$3&”》单科成绩表.xls]12计信1’!$A:$O”),15,0)等这里,INDIRECT函数指明是对另一个工作簿的引用,则被引用的工作簿必须被打开,因此必须将包含单科成绩数据的所有Excel表格全部打开,否则INDIRECT函数将会返回错误值#REF.

2.2“学生成绩查询”模块.根据如图4所示的表结构,创建《补考信息统计》表.

在该表相应单元格中输入以下核心代码,然后选中I3:M3单元格区域,分别向右、向下自动填充公式, 结果如图4所示.

I3等于INDIRECT(TEXT(ALL(IF($C$5:$G$45<60,ROW($5:$45),65536),ROW(1:1)),"r0c1"),)&""

J3等于INDIRECT(TEXT(ALL(IF($C$5:$G$45<60,ROW($5:$45),65536),ROW(1:1)),"r0c2"),)&""

K3等于INDIRECT("r4c"&RIGHT(ALL(IF($C$5:$G$45<60,ROW($5:$45)*1000+COLUMN($C:$G),65536255),ROW(1:1)),3),)&""

这里,在每次输入完公式时必须同时按住Ctrl、shift和enter三键加以确认,公式两端即会出现花括号“{}”,这表示数组公式引用结束.接下来,根据如图5所示的表结构,创建《学生成绩查询》工作表.在该表相应单元格中输入以下核心代码,结果如图5所示.

F8等于IF($F$6等于””,””,VLOOKUP($F$6,读取单科成绩!$A$5:$G$45,2))

F 11等于IF($F$6等于””,””,VLOOKUP($F$6,读取单科成绩!$A$5:$G$45,3)) F 13等于IF($F$6等于””,””,VLOOKUP($F$6,读取单科成绩!$A$5:$G$45,4))等

D 21等于IF(COUNTIF(读取单科成绩!I3:I12,I6)等于0,””,”该生应补考门数”&COUNTIF(读取单科成绩!I3:I12,I6) &”门”)

2.3“考试质量分析”模块.根据如图6所示的表结构,创建《考试质量分析汇总表》.

在该表相应单元格中输入以下核心代码,然后选中d5:N5单元格区域,分别向右、向下自动填充公式,结果如图6所示.

D5等于COUNTA(读取单科信息!A5:A45);E5等于COUNTA(读取单科信息!B5:B45);F5等于(E5-N5)/E5;G5等于MAX(读取单科成绩!C$5:C$45);H5等于MIN(读取单科成绩!C$5:C$45);I5等于ERAGE(读取单科成绩!C$5:C$45);J5等于COUNTIF(读取单科成绩!C$5:C$45,”>等于90”);K5等于COUNTIF(读取单科成绩!C$5:C$45,”>等于80”)-J5;L5等于COUNTIF(读取单科成绩!C$5:C$45,”>等于70”)-K5-J5;M5等于COUNTIF(读取单科成绩!C$5:C$45,”>等于60”)-L5-K5-J5;N5等于COUNTIF(读取单科成绩!C$5:C$45,”<60”)

接下来,根据如图7所示,创建《考试质量分析》工作表.在《考试质量分析》工作表相应单元格中输入以下核心代码,然后选中B5:F6元格区域,设置图表向导.结果如图7所示.

B3等于IF($B$2等于””,””,VLOOKUP($B$2,单科成绩分析!$A$4:$N$9,2,))

F3等于IF(B2等于””,””,单科成绩分析!B3)

B4等于IF($B$2等于””,””,VLOOKUP($B$2,单科成绩分析!$A$4:$N$9,3,))

E4 等于IF($B$2等于””,””,VLOOKUP($B$2,单科成绩分析!$A$4:$N$9,4,))&”人”

G4等于IF($B$2等于””,””,VLOOKUP($B$2,单科成绩分析!$A$4:$N$9,5,))&”人”等

这里,需要事先设置B2单元格的数据有效性,将任课教师的“姓名”添加到该单元格中,具体参数设置按要求来操作.

3.结语

本例使用Excel函数来完成复杂的数据处理,主要利用VLOOKUP、INDITECT和COUNTIF等函数实现了从外部读取数据、高级筛选、数据处理等功能,从整体上减少人为失误的概率.本例稍作修改,可以灵活应用到其他一些领域的数据处理中,希望本例的设计思路和具体方法能够给教务管理人员在工作中带来帮助,进而使我们的教务管理工作趋于科学化、规范化.