浅谈考务管理系统中Excel函数嵌套

杨旭东

摘 要：Excel在工作应用中，许多时候仅仅使用了它的简单功能，如求和、求平均值、计数等。实际上，Excel数据处理功能很强大，经过缜密设计，灵活应用其函数功能，可以完成复杂统计，或达成类似数据库的功能。文章以应用Excel内置功能制作考务管理系统为例，讨论应用函数嵌套，实现学生成绩自动化统计分析，具有操作简单、上手容易、免除培训环节、准确高效地完成工作任务等软件特征。在函数代码开放环境中，修改代码，重构统计项目的方法，对考务管理软件《学生成绩统计分析》设计、开发的主导思想和函数嵌套技巧进行总结。研讨如何拓展Excel函数功能，更好地为学校教务工作服务，使之成为教学管理的好助手。

关键词：考务管理;Excel函数嵌套;软件开发

中图分类号：G434             文献标志码：A           文章编号：1673-8454（2015）04-0089-03

在平时教学工作中，经常会用到Excel软件记录、计算和统计数据，许多时候仅使用了Excel的简单功能，如求和、求平均值、计数等。据相关统计，80%使用Office Excel的人其实只是用了它不到20%的功能。实际上，Excel数据处理功能很强大，灵活应用其函数功能，可以完成复杂统计，甚至以它为平台开发小型数据库。本文以应用Excel内置功能制作考务管理系统为例，讨论如何应用函数嵌套，实现学生成绩自动化统计分析。考务管理系统数据流向见图1。

数据处理中为了保证数据的准确，使用全国中小学学籍管理系统中的学生基础数据就成为必然。首先，这些数据经过校对和学期初学生变动情况的更新，可以提供最新的、准确的学生基础信息。其次，不用再另起炉灶，减少数据维护的工作量，只需要设计好“班内学号”列的数据构成方式。“班内学号”应包括入学时间、班别、序号三个信息，如班内学号“2009215”，2009表示入学的年份，中间的2表示2班，最后两个数字15，表示第15个序号。这种设计有几项用处，一是班内学号是数字，在数据录入中，从本班第1位到最末位的班内学号，都可以使用数据序列填充的方式完成，减少操作量，避免错误，提高工作效率。二是通过班内学号升序排列，为整个数据建立索引，方便数据的引入和查询，数据维护简单方便。三是学号本身包含信息，可以根据需要调取，完成一些功能设计。四是开放性的设计思想为日后增加信息量预留接口且不影响其原有功能。如学校规模大，每个年级的班级数达到两位数，就把表示班别的数字定义为两位数。如要在班内学号中需要增加学生性别信息，只需在数字中增加一个数位，用0和1分别代表男女生便可实现。

“成绩册”工作表，用来记录每名学生的各门功课的考试成绩，进行初步的统计工作，提供各班成绩单报表。在实际应用中，学生姓名通过函数命令“=IF（COUNTIF（学生基础信息！$AN：$AN，A2）=0，""，LOOKUP（A2，学生基础信息！$AN：$AN，学生基础信息！$B：$B））”的方法引用。先用IF函数执行真假值判断，表达式COUNTIF（学生基础信息！$AN：$AN，A2）返回值如果为0，表示当前行中班内学号值在学生基础信息表中不存在，结果将返回空值，否则返回表达式LOOKUP（A2，学生基础信息！$AN：$AN，学生基础信息！$B：$B）的值，即使用LOOKUP 函数的向量形式，在学生基础信息AN列中查找A2（当前行中的班内学号）数值，然后返回学生基础信息B列（姓名）中相同位置的数值，从而得到学生姓名。学生的性别信息处理方法和姓名信息处理方法类似，区别是IF返回表达式指定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函数执行真假值判断，如果当前月份大于9月份（即新学年），则用当前年份减去班内学号中的入学年份再加1得到年级数，用MID函数引用出班内学号中的班别数，再用年级数乘10加班别数得到班级名称。否则，用当前年份减去班内学号中的入学年份得到年级数（不用再加1），用MID函数引用出班内学号中的班别数，再用年级数乘10加班别数得到班级名称。

提起排名，大家首先就会想到RANK函数，但RANK函数使用时局限较大，排名时班级、年级不同，对数字列表的引用范围就会有变化。如果手动给RANK函数指定引用范围，统计数据就得固定下来，不允许修改数据，容错率很低。此外，如果学校规模大，手动指定引用范围本身的工作量也是很大的，失去了编写公式的意义。因此，学生成绩班级排名和年级排名时，使用了“SUM+if”的数组函数组合。班级排名方法：“=IF（OR（COUNTIF（学生基础信息！$AN$2：$AN$1000，A2）=0，SUM（I2：K2）=0），""，IF（$H2>0，SUM（IF（bj=$D2，IF（zf>H2，1，0）））+1，""））”，先对表达式bj=$D2进行条件检测，bj是班级标题下的数据，是提前定义的名称，通过检测，筛选出同一个班级的总分数据进一步处理。再嵌套第二层函数IF，对表达式zf>H2进行条件检测，zf代表总分标题下的数据。当表达式zf>H2为TRUE时返回1，为FALSE时返回0。年级排名方法：“=IF（OR（COUNTIF（学生基础信息！$AN$2：$AN$1000，A2）=0，SUM（I2：K2）=0），""，IF（$H2>0，SUM（IF（LEFT（bj，1）=LEFT（D2，1），IF（zf>H2，1，0）））+1，""））”。

备注一栏系统设计了数据自动校对功能，可对录入的考试成绩进行各类错误的检查与提示，对学号错误、学号重复、成绩不全、语文成绩异常、数学成绩异常、英语成绩异常、重名等数据问题进行提示。应用逻辑函数指令拓展、IF函数多层嵌套、文字提示智能化等技术来实现，方法为“=IF（COUNTIF（学生基础信息！$AN：$AN，A2）=0，"此为空号"，IF（COUNTIF（A：A，A2）>1，"学号重复"，IF（OR（AND（$D2>30，COUNTBLANK（I2：K2）>0），COUNTBLANK（I2：J2）>0），"成绩不全"，CONCATENATE（IF（OR（$I2>100，$I2<0），"语文"，""），IF（OR（$J2>100，$J2<0），"数学"，""），IF（OR（$K2>100，$K2<0），"英语"，""），IF（OR（$I2>100，$I2<0，$J2>100，$J2<0，$K2>100，$K2<0），"成绩异常"，IF（AND（$D2>10，$D2<30，COUNTBLANK（K2）=0），"低年级不考英语"，IF（COUNTIF（B：B，B2）>1，"重名"，""）））））））”。IF函数可以嵌套七层，用 value_if_false 及 value_if_true 参数与逻辑函数套用，可以构造复杂的检测条件，满足备注栏的数据校对、错误提示等需求。

汇总表，对全校各班各门功课的考试成绩汇总成绩进行统计，形成各班教学质量报表。任课教师引用方法是“=HLOOKUP（B2，任课教师！$B$1：$K$13，MATCH（AW2，任课教师！$A$1：$A$13，0），FALSE）”。在“任课教师！表中$B$1：$K$13”数据区域查找B2单元格（课程科目）数值，并由此返回表达式MATCH（AW2，任课教师！$A$1：$A$13，0）指定行处的数值。实考数统计方法是数组函数“=INDEX（FREQUENCY（IF（bj=$AW2，CHOOSE（HLOOKUP（$B2，{"语文"，"数学"，"英语";1，2，3}，2，FALSE），yw，sx，yy）），{0.1，100.1}），2）”，这段数组函数也是多个函数嵌套形成的，其中yw、sx、yy是提前定义的数据区域名称，yw是语文成绩数据区域，sx是数学成绩数据区域，yy是英语成绩数据区域。B列单元格是考试科目，科目各不相同，属于变量，因此，用HLOOKUP（$B2，{"语文"，"数学"，"英语";1，2，3}，2，FALSE）语句，把科目转换为序号，用CHOOSE函数选择科目所在的定义名称数据区域，再用IF函数对指定班级的数据进行逻辑筛选，用FREQUENCY函数返回筛选出的符合要求数据的频率分布，最后用INDEX函数调用所需数据，统计出各科目的实际参加考试的人数。最高分统计方法是数组函数“=MAX（IF（bj=$AW2，CHOOSE（HLOOKUP（$B2，{"语文"，"数学"，"英语";1，2，3}，2，FALSE），yw，sx，yy）））”。用IF函数对指定班级的数据进行逻辑筛选，再用MAX函数返回其中的最大值，也就是指定班级、指定科目的最高分。最低分统计方法只需要把MAX函数换为MIN 函数返回其中的最小值便可。年级前10名（人）统计方法是数组函数“=INDEX（FREQUENCY（IF（bj=$AW2，njpm），{0.1，10.1}），2）”。其中njpm是定义的年级排名标题数据区域名称，先用IF函数筛选出指定班级的年级排名数据，再用FREQUENCY返回10以内数值的频率分布，最后用INDEX函数调用其所需数据，统计出指定班级的年级排名前10的具体人数。平均分统计方法是数组函数“=ROUND（AVERAGE（IF（bj=$AW2，CHOOSE（HLOOKUP（$B2，{"语文"，"数学"，"英语";1，2，3}，2，FALSE），yw，sx，yy））），2）”。对筛选出的指定班级、指定科目的考试成绩计算平均分，四舍五入保留2位小数。标准偏差统计方法是数组函数“=ROUND（STDEV（IF（bj=$AW2，CHOOSE（HLOOKUP（$B2，{"语文"，"数学"，"英语";1，2，3}，2，FALSE），yw，sx，yy））），2）”，标准偏差反映相对于平均分班级整体成绩的离散程度。90至100分（人）统计方法是数组函数“=INDEX（FREQUENCY（IF（bj=$AW2，CHOOSE（HLOOKUP（$B2，{"语文"，"数学"，"英语";1，2，3}，2，FALSE），yw，sx，yy）），{0，9.9，19.9，29.9，39.9，49.9，59.9，69.9，79.9，89.9，100.1}），11）”。用FREQUENCY对指定班级、科目的整体成绩按照每10分一个分段进行频率分布计算，最后用INDEX函数返回所需分数段的具体人数。各科90至100分（人）统计方法是数组函数“=IF（$AW2<30，SUM（IF（bj=$AW2，IF（yw>=90，IF（sx>=90，1，0）））），SUM（IF（bj=$AW2，IF（yw>=90，IF（sx>=90，IF（yy>=90，1，0））））））”。各科的含义对每个年级不尽相同，一、二年级是指语文和数学两个科目，三至六年级是语文、数学、英语三个科目，因此要用IF函数进行分类，再使用不同的统计方法。

成绩查询工作表，可以按照学号和姓名两种方式查询学生考试成绩，并且可以跟踪反馈历次考试成绩。班内学号的查询方法是“=IF（$L2&$M2=""，""，IF（$L2=""，IF（MAX（IF（成绩册！$A$1：$B$1000=$M2，ROW（$1：$1000）））=0，""，INDEX（成绩册！$A$1：$L$1000，MAX（IF（成绩册！$A$1：$B$1000=$M2，ROW（$1：$1000））），MAX（IF（成绩册！$A$1：$K$1000=$M2，COLUMN（$A$1：$I$1）））-1）），IF（MAX（IF（成绩册！$A$1：$B$1000=$L2，ROW（$1：$1000）））=0，""，VLOOKUP（$L2，成绩册！$A$2：$K$1000，1，FALSE））））”。该组函数首先判断查询方式，L2单元格接收学号信息，M2单元格接收姓名信息。如果L2单元格为空值，M2单元格不为空值，说明使用者是按照姓名方式查询的，即使用MAX（IF（成绩册！$A$1：$B$1000=$M2，ROW（$1：$1000）））和MAX（IF（成绩册！$A$1：$K$1000=$M2，COLUMN（$A$1：$I$1）））-1查询该生对应的班内学号在数据区域内的行号、列号，再用INDEX函数引用该生班内学号，显示在单元格。否则，进一步判断L2单元格有无学号信息，如果使用者已经在L2单元格填入了班内学号，则使用VLOOKUP（$L2，成绩册！$A$2：$K$1000，1，FALSE）语句查证成绩册工作表的班内学号数值，若学号正确则显示于单元格，否则显示空值，并在备注栏显示文字“这是空号”。因为成绩查询工作表与成绩册工作表结构相同，所以姓名、性别、班级、班级排名、年级排名、总分、平均分、语文、数学、英语的数据查询使用同一方法“=IF（$L2&$M2=""，""，IF（$A2=""，""，VLOOKUP（$A2，成绩册！$A$2：$K$1000，COLUMN（），FALSE）））”。表结构相同，每个查询项目处于同一位置，因此，以函数COLUMN返回所在列号为变量，引导VLOOKUP函数在数值数组中查找指定的数值，并由此返回数组当前行中指定列处的数值，在单元格中显示所需要的信息。

在处理变量复杂的任务时，单个函数的功能显得苍白无力，通过函数嵌套的灵活运用往往可以解决问题。嵌套函数是拓展函数功能的主要方式，将函数返回值作为另一函数的参数使用，层级叠加，性能提升[1]。合理运用Excel函数嵌套，使电子表格具备数据处理的五大要素，即数据录入、校对、查询、统计和分析，不需要专业编程软件技术支持，仅采用Excel内置的函数库，经过严密的设计，也可以形成类似数据库的简单功能，实现学校教学质量统计分析的自动化。基于统计方便、准确、全面的目标，界面设计和系统操作采用Excel工作表默认模式，运行环境为Excel，为大家所熟知，操作简单，上手容易，只要有一点计算机基础的教师都可以熟练掌握，免除培训环节。学生考试成绩录入完成之后，使用预设的公式进行数据填充，双击鼠标在几秒钟时间内，就可以对46个统计项目、几千个单元格数据进行统计，准确高效的完成工作任务。由于函数代码开放，使用者可以根据实际需要修改相关代码，重构统计项目，只需变更单位信息就可为学校教务工作服务，成为教学管理的好助手。

参考文献：

[1]钱秀峰.Excel中函数嵌套功能的实际应用[J].人力资源管理，2010（4）.

（编辑：鲁利瑞）