浅谈考务管理系统中xcel函数嵌套.docVIP

浅谈考务管理系统中Excel函数嵌套 摘要：工作中大部分时候仅仅使用了它的简单功能，如求和、求平均值、计数等应用。实际上，Excel数据处理功能很强大，灵活应用其函数功能，可以完成复杂统计，经过严密的设计，也可以形成类似数据库的简单功能。系统开发要基于统计方便、准确、全面的目标，要能够涵盖学校需要的各项统计项目，要操作简单，上手容易，免除培训环节，要准确高效的完成工作任务。函数代码应当开放，可修改代码，重构统计项目，更好的为学校教务工作服务，成为教学管理的好助手。本文以应用Excel内置功能制作考务管理系统为例，简要讨论如何有效应用函数嵌套，实现学生成绩自动化统计分析。 关键词：考务管理；Excel；函数嵌套 在平时教学工作中，经常会用到Excel软件，记录各种数据，进行计算和统计，大部分时候仅仅使用了Excel的简单功能，如求和、求平均值、计数等应用。据相关统计，80%使用Office的人其实只是用了它不到20%的功能。实际上，Excel数据处理功能很强大，灵活应用其函数功能，可以完成复杂统计，甚至以它为平台开发小型数据库。本文以应用Excel内置功能制作考务管理系统为例，简要讨论如何有效应用函数嵌套，实现学生成绩自动化统计分析。系统数据流向见下图： 进行数据处理，首先要保证数据的准确，这是前提和基础，所以，应当使用全国中小学学籍管理系统中的学生基础数据，一方面，这些数据经过反复的校对和每个学期初学生变动情况的数据更新，可以提供最新的、准确的学生基础信息，其次，不用再另起炉灶，另搞一套，减少数据维护的工作量。只需要在原有数据结构中，设计好“班内学号”这列数据的构成方式。数据至少应包括三个信息，即入学时间、班别、序号。如班内学号：“2009101”，2009表示入学的年份、中间的1表示1班、最后两个数字01，表示第1个序号。这样的设计有几项用处，一是班内学号是数字，在数据录入中，从本班第1位到最末位的学号，可以非常方便地使用数据序列填充的方式完成，减少操作量，避免错误，提高工作效率。二是通过班内学号升序排列，为整个数据建立索引，方便数据的引入和查询，数据维护简单方便。三是学号本身包含三个信息，可以根据需要调取信息，完成一些功能设计。四是开放性的设计思想为日后增加信息量预留接口且不影响其原有功能。如果学校规模大，每个年级的班级数达到两位数，就要把表示班别的数字定义为两位数，再比如要在班内学号中需要增加学生性别信息，只需在数字中间增加一个数位，用0和1分别代表男女生便可实现。 建立“成绩册”工作表，用来记录每名学生的各门功课的考试成绩，进行初步的统计工作，为各班提供成绩单报表。工作表标题从左到右依次为：班内学号，姓名，性别，班级，班级排名，年级排名，总分，平均分，语文，数学，英语，备注。各个标题项完成后，表格结构如下图所示： 班内学号 姓名 性别 班级 班级排名 年级排名 总分 平均分 语文 数学 英语 备注 　 　 　 　 　 　 　 　 　 　 　 执行真假值判断，根据””，即空值。否则，返回表达式LOOKUP(A2,学生基础信息!$AN:$AN,学生基础信息!$B:$B)的值， 学生的性别信息处理方法和姓名信息处理方法类似，方法为“=IF(COUNTIF(学生基础信息!$AN:$AN,A2)=0,,LOOKUP(A2,学生基础信息!$AN:$AN,学生基础信息!$C:$C))”。区别是在IF返回表达式LOOKUP(A2,学生基础信息!$AN:$AN,学生基础信息!$C:$C)中指定C列（性别）数据便可。 由于班内学号的数据构成已经涵盖了学生所在班级的信息，因此“班级”，就可以通过班内学号自动计算出来，方法是“=IF(COUNTIF(学生基础信息!$AN$2:$AN$1000,A2)=0,,IF(MONTH(TODAY())9,(YEAR(TODAY())-LEFT($A2,4)+1)*10+MID($A2,5,1),(YEAR(TODAY())-LEFT($A2,4))*10+MID($A2,5,1)))”。其中核心算法的表达式IF(MONTH(TODAY())9,(YEAR(TODAY())-LEFT($A2,4)+1)*10+MID($A2,5,1),(YEAR(TODAY())-LEFT($A2,4))*10+MID($A2,5,1))意思为先用IF函数执行真假值判断对数字列表的引用。对表达式进行条件检测进行条件检测TRUE时返回FALSE时返回0。以班级总分第一的数值为例，函数在筛选出同一个班级的总分数据后，重新组成数组，数组中的每个数据与当前最高总分进行大小比较，结果全部为FALSE，SUM函数的返回值为0，意味着数组中没有数据大于当前总分，因此当前总分排名为第“0+1＝1”名。 年级排名的核心语句是SUM(