基于VLOOKUP的大学生体育综合成绩自动化统计与实现

邱阳

（西安音乐学院，西安 710061）

0 引言

为了促进学生全面发展，2014年教育部颁布了《国家学生体质健康标准2014修订版》（简称《新标准》），对大学生体育测试项目进行了大幅调整，如增加身体素质类测试项目权重[1]。这一举措使学生更加重视体质测试，学生的平均体质健康水平得到显著改善[2]。随着信息技术的不断发展，数据的采集和分析效率大幅度提高。而传统纸质体育成绩记录表不仅造成大量纸张资源的浪费，且后续查询难，不利于体育教学活动的开展[3]。因此，大量针对学生体育成绩预测[4-5]或统计[3,6]的信息管理系统应运而生。然而，体育成绩管理系统开发周期较长，更新较慢。基于此，本文构建了基于VLOOKUP函数的高校体育成绩综合评定表（简称体育成绩表），以期立竿见影地解决一线教师的迫切需求。

1 大学生体育成绩综合评定表的主要构成

大学生体育总成绩一般由三部分构成：素质成绩、专项成绩和考勤成绩。素质成绩方面，男、女生的体育项目存在差异：在高校体育课中男女生均需测试体重指数（Body Mass Index，BMI）、肺活量、50 米跑、坐位体前屈、立定跳远等项目；除此之外，大学男生体育成绩中的素质成绩可能包含1000米跑、引体向上等，大学女生体育成绩中的素质成绩可能包含800米跑、仰卧起坐等。专项成绩方面，各高校也有较大差异，一般是指高校开设的特色课程成绩，如西安音乐学院开设的健美操、艺术体操等体育教学项目的单项考试成绩；此外还包含个人比赛、团体比赛的成绩等。考勤成绩主要包括平时成绩以及由单项较突出的某个人参加班级以外的比赛获奖折合的附加分等。

体育课开课内容受季节、天气、场地、器材及学生的诸多限制，各个高校依据自身优势开展特色教学，开设项目差异显著。以西安音乐学院为例，学校女生人数多，且具有艺术特长，专项教学包含室内项目和室外项目，室内不定期开展健美操、艺术体操等，室外不定期开展定向、羽毛球、太极拳、气排球、柔力球等。

2 大学生体育成绩综合评定表的实现

（1）基本框架

大学生体育成绩综合评定表应在对该高校一线体育教师充分调研的基础上搭建，应包含教师最终呈交上级部门的全部成绩项，原则上包含教师已经开设或计划开设的所有单项，通过系数的变化使综合评定表可以供该校每一学期每一名体育教师评定成绩使用。本文以长安大学为例，说明成绩表基本框架如何搭建。以长安大学为例，体育成绩综合评定表（见图1）中共包含36列，其中A～F共6列为学生基本信息，G～AA共21列为学生素质成绩，AB～AJ共9列为学生专项成绩。在这一框架的基础上应将国家标准及计算公式或函数等不变的内容填写清楚，教师只需录入学生基本信息和单项成绩，即可自动得出总分，实现自动化统计的目的。

需要提前录入的数据列共15列，分别是：编号A，可根据班级人数填写，如1-30；各标准列G～J、M～N、Q～R、U～X、AG～AH应根据国家标准输入。当标准录入完成后，为保护数据使其不被轻易修改，可将其隐藏。

含有公式或函数的列共10列，分别是：总分E、各得分列 L、P、T、Z、AA、AC、AE、AF、AI。

需要体育教师自行填写的列共10列，分别是：学号B、姓名C、性别D、平时成绩F、各单项成绩K、O、S、Y、AB、AD。

附加分AJ列作为备注项，使整个表格可以灵活调整，对于个别学生的特殊情况酌情加分或减分的可以写在附加分一列。

图1 体育成绩综合评定表的列标题行

对于体育课程设置更加灵活、项目更多、开课时间及项目等变动较大的院校，在设计综合评定表时还可增加了项目、系数两列，由教师根据本学期实际情况填写，在总分中所有的项目得分乘以对应系数后计入总分，对于本学期没有开始的课程，教师只需在系数一列填写0或空白即可。

（2）制作难点

《国家学生体质健康标准（2014修订版）》中给出了100,95,90,…,20,10共20个分数档，大于等于该分数档对应成绩但不够更高档位成绩的，一律按照较低档分数执行。以往教师需要手动查阅国家标准，找出成绩对应的得分档后，写出学生该项目考试分数。这是教师统计成绩最耗时的一项工作，也是成绩统计中最容易出错的环节。如何自动对照国家标准给出得分是成绩评定表的制作难点。由于VLOOKUP函数的主要功能是在数据表的首列查找指定的值，并返回数据表当前行中指定列的值，使用VLOOKUP函数可轻松完成该工作。

在设计大学生体育成绩表时，每单项成绩共需要4列，分别为某单项标准、某单项标准得分、某单项、某单项得分。其中前两列根据国家标准应提前录入数据。第3列，录入学生测评的真实成绩，如跑步时间、跳远距离等。第4列应提前录入公式或函数。

VLOOKUP函数有两种常用模式，精确查找和模糊查找。采用精确查找，需要给出每一个测评成绩对应的得分值，若某测评成绩没有对应的得分值，则单元格显示#N/A，不显示任何成绩。本例长安大学的体育成绩表采用的精确查找，前两列为标准列、标准得分列，以男子1000米跑为例，标准数据以0.01递增依次为0.00,2.42,2.43,…,7.00，标准得分列考虑国家标准及加分标准，给出对应成绩得分依次为0,110,109,…,0。精确查找虽然前期录入的工作量稍大，但可以使用Excel快速填充命令，并且方便后期使用该成绩表的体育教师自行调整等。

若使用模糊查找，则第1、2列的标准简写即可，第3列等待体育教师测评后自行录入，第4列可写入公式，例如在Z3单元格中写入函数（省略号部分为实际标准，应填写完整）如下：

模糊查找应特别注意标准第1列的数据只能按照升序排列，降序或乱序则会出现返回值错误。另外标准的书写需要进行简单计算转换，因为VLOOKUP函数的模糊查找方式为大于等于第1列中的某个数值（但小于下一数值）时，找到数据同行其他列的相关数据。这可能与国家标准的数据顺序不一致，需要在录入Excel标准列时做出调整。如图1中800米标准一列，按照VLOOKUP函数要求，第1列应按照升序排列，即按原表的顺序录入，但如果按照表中数据不作任何改动录入则会出现错误，例如录入3.18对应100分，3.24对应95，3.30对应90分，等等，然后输入3.19-3.23的成绩均会返回为100分的值，这显然是错误的。录入时应找到得分对应的最大数值进行录入。如上述实例应录入3.18对应100分，3.19对应95分，3.25对应90分，以此类推。

（3）评定表中的函数设计

含有公式或函数的列共10列，分别是：总分E、各得分列 L、P、T、Z、AA、AC、AE、AF、AI。总分列 E 使用的公式是求和公式，为平时成绩F、（素质成绩）总得分AA、（专项成绩）总得分AI、附加分AJ的和。

①得分列函数设计

以立定跳远得分列L为例，使用的是if函数与VLOOKUP函数的嵌套，见公式（1）。

if函数用来判断性别，VLOOKUP函数根据男（或女）立定跳远标准I，返回男（或女）立定跳远标准得分J同一行的得分数值。800/1000米得分列Z的函数与L列类似。得分列P、T仅用到VLOOKUP函数。总得分列AA仅用到IF函数，根据性别判断该生素质成绩的来源项为立定跳远、引体向上、1000米跑，还是立定跳远、仰卧起坐、800米跑，同时按照学校统一要求将原项目百分制的成绩乘以一定系数，以长安大学体育成绩表为例，AA列公式：

②VLOOKUP函数的特殊要求

使用VLOOKUP函数的精确查找功能时，不能出现同一标准有不同得分的情况，但允许不同标准的得分相同。以女子800米大一大二的标准为例（见图2），4分34秒（图2中简写为4.34）对应的得分是60分，若下一行中再次出现4.34，且对应的得分不是60分，则Excel的VLOOKUP函数按照从上往下的顺序查找4.34的对应分数，最终显示得分60分。即最后一行数据4.34属于重复数据，应予以删除。另外，按照标准，只有当时间小于等于4分29秒得分62分，那么4分29秒与4分34秒之间的4分30秒、31秒、32秒、33秒的得分也应该是60分。

图2 大学生体育成绩综合评定表标准列

此外，无论成绩表空表的早期设计制作还是后期的数据录入都需要输入大量数据，对于有规律的数据可利用Excel的填充功能快速录入。需要注意的是，在制作800米和1000米标准的时候为了节省录入时间，将时间简化为*.**。小数点前表示几分，小数点后表示几秒，精确到小数点后2位，即精确到秒。并使用Ex⁃cel自动填充功能，填充增量为0.01的数列。特别说明的是，增量为0.01或增量更小的数据在使用填充柄快速填充时，应注意检查编辑栏，确认该单元格中数据的真实大小。例如：某单元格填充后显示4.54，但其编辑栏的真实数据是4.53999999999999见图3。这类错误直接导致VLOOKUP函数查找不到4.54的数据，最终跑步时间为4.54的学生将没有成绩得分。

图3 编辑栏显示的数据真实大小

3 结语

体育成绩是学生综合体育素质的体现，同时也是对大学体育课程及教学质量的有力评判，因此，对大学生体育成绩的统计应务必准确。而且大学生体育成绩的构成多样，标准不一，借助Excel软件中的VLOOK⁃UP函数统计大学生体育成绩不仅高效，更是用技术手段保证了成绩的准确性。同时，运用Excel自带函数设计成绩表，克服了开发成绩计算系统周期较长的问题，且具有易于更新、便于一线教师学习使用等优点，一定程度上替代了人工的重复性工作，为教师将精力集中在教学科研等创新环节提供了支撑。同理，利用Excel的公式函数还可自动统计其他学科的成绩，本文提供的方法具有一定的普适性。