运用电子表格实现体育测试项目成绩自动评分研究

董东风

（湖南邮电职业技术学院，湖南长沙410015）

运用电子表格实现体育测试项目成绩自动评分研究

董东风

（湖南邮电职业技术学院，湖南长沙410015）

为了满足高校信息化建设需要，方便体育教师快速、准确对学生体育测试项目成绩进行评分，文章通过查询知网文献资料，运用电子表格技术，采用INDEX与MATCH两函数组合查找，并采用单项分数成升序排列的评分表的办法，研发出了"体育成绩管理系统"，实现了对学生体育测试项目成绩自动评分功能。该系统可以满足体育教师个人对学生体育成绩管理的需求。但是，该系统安全性低，自动化程度低，处理数据量小，且尚未在实际中运用。

电子表格；体育成绩；自动评分；计算公式

1　研究背景

对学生体育成绩评定是体育教学的重要环节之一。其评定内容通常包括多个方面，例如，体育理论知识，体育测试项目，体育课考勤，参与体育竞赛，参与体育社团等，各高校没有统一标准，依据教育部《全国普通高等高校体育课程教学指导纲要》教体艺［2002］13号文件自行制定，各方面内容分别占体育总成绩的一定比重（百分比）（参见表1）。

在体育成绩评定内容中，体育测试项目是必不可少的一项内容，通常每个学期会对学生进行两到三个体育项目测试，并对各项实际测试成绩依据相关评分标准进行评分，例如表2，就是大学男生体育测试项目评分标准表，这是一个百分制评分表，表2只是列出了部分测试项目，实际有13项之多（没有包括各高校自主制定的体育测试项目评分标准）。评分后，依据各测试项目得分（百分制）占体育总成绩比重换算成总成绩的一项分数，合计各项内容考核分数就可以得到体育总成绩分数（百分制），然后，把总分换算成等级（小于60分不及格，大于等于60分且小于75分及格，大于等于75分且小于85分良好，大于等于85优秀）就是学生学期最终成绩。

表1　体育成绩评定内容和比重表

目前，很多高校都已实现了体育成绩计算机管理。由于体育成绩评定内容和比重各校没有统一标准，且各校体育测试项目成绩评分标准也不统一，也没有统一的体育成绩管理标准；且购买相关软件成本很高，而技术含量很低，通用性差。所以，各高校基本上都是自主研发体育成绩管理系统。有的高校花大成本开发数据库管理软件，数据安全性高，自动化程度高，实现了网络化管理。有的高校采用电子表格来管理，数据安全性低，自动化程度低，处理数据量小，但开发成本低，经济实用。

我院对学生体育成绩计算机管理，只包含了学生学期最终成绩管理，通过任课教师上报学生学期最终成绩到教务处，然后，由教务处把成绩输入到学籍管理系统中（该系统包含有学生学期成绩管理）。而对学生体育考核资料还是采用手工管理，对学生体育测试项目成绩评分还是采用人工查表评分，尚未实现计算机自动评分。自动评分是实现体育成绩计算机管理的难点。

表2　大学男生体育测试项目评分标准表（部分项目）

2　研究目的和意义

对每一位学生体育测试项目的每一项实际测试成绩进行人工评分是一个简单重复的工作，工作量大，效率低，且枯燥无味，难免不出现错误。所以，这个工作特别适合用计算机来完成。一是可以对输入的实际测试数据进行有效性检验，避免异常数据输入；二是可以格式化实际测试数据输入，减少字符输入量，例如，3分30秒，只需要输入330；三是可以对输入的实际测试数据进行自动评分，避免人为错误评分，且减少了分数的输入；四是考核资料便于存档、查询、统计分析。

为了减轻体育教师工作负担，提高工作效率，提高体育成绩管理自动化水平，满足高校信息化建设需要。本文针对体育教师个人对学生体育成绩管理的需求，以经济实用为原则，以实现对学生体育测试项目成绩自动评分为主要目标，进而实现“体育成绩管理系统”目标。系统实际运行后，将会取代体育教师现行手工体育成绩管理，方便体育教师快速、准确对学生体育测试项目成绩进行评分，对学生体育成绩进行评定，方便体育教师对原始数据存档、查询、统计分析，并可用于指导教学。努力实现体育成绩精准化、标准化、规范化、科学化、现代化管理，提高体育教学管理水平。同时，能更好的为学生服务，方便学生查询成绩，及时掌握成绩动态，帮助学生有针对性的锻炼。

3　研究方法

3.1文献研究法

通过知网查询体育成绩管理及自动评分相关研究成果，通过对现有“电子表格体育成绩管理系统”进行研究发现，部分系统存在评分不准确问题，并找到解决问题的办法。

3.2经验总结法

在实际运用电子表格技术研发“我院体育成绩管理系统”中，运用以往对数据库精确查询经验，选择正确的查找函数是实现自动评分的关键，对于不能满足评分表查找要求的函数，不能轻易改变原始评分表来满足函数的要求，这样只能是把问题复杂化，应该选择能满足评分表查找要求的函数。

4　研究结果

4.1现有电子表格自动评分存在的问题

通过知网查询可以发现，现有电子表格自动评分还存在问题，虽然实现了自动评分，但是，把评分表改变得面目全非，还存在对成绩数值成升序排列项目（数值越小得分越高，数值越大得分越低）评分不准确问题。主要原因：一是对电子表格查找函数认识不够，没有正确选择查找函数，错误选择VLOOKUP或LOOKUP函数来实现自动评分，由于，这两个函数在模糊查找评分表时，都对评分表中数值排列要求升序排列（数值从小到大排列），然而，评分表中数值排列存在升序和降序两种排列形式，所以，想通过改变降序排列项目（数值越大得分越高，数值越小得分越低）成升序排列来满足查找函数的要求，由此造成评分表面目全非；二是对评分表中数值“区间”（在升序排列项目中，数值区间是从“大于上一行数值”到“小于等于本行数值”之间）与查找函数模糊查找“方向”（在升序排列项目中，是查找小于实测值的最大值）两者之间的关系认识不够，忽略了评分表中数值通常是不连续排列问题，造成对原本升序排列项目评分不准确，反而对原本降序排列项目通过倒置成升序排列项目的评分准确。正是因为上述两个原因，反而把简单的自动评分问题复杂化，而且还犯了顾此失彼的错误，忽略了评分表的整体性。

4.2正确选择查找函数

由于评分表中项目成绩数值排列存在升序和降序两种形式，所以，在实现自动评分问题上，要求必须选择查找函数，既能实现查找升序项目，又能实现查找降序项目。而VLOOKUP和LOOKUP两函数都不能满足这一要求。其实，不难发现MATCH函数才是实现自动评分首选函数，既能实现升序项目查找，又能实现降序项目查找。但是，MATCH函数只能返回指定值在指定区域中的位置，却不能返回指定值所处位置对应的分数。而VLOOKUP和LOOKUP两函数正是因为具备这种查找功能，所以才被很多人首选。然而，INDEX函数可以实现返回所处位置对应的分数，所以，采用INDEX和MATCH两函数组合查找，可以达到VLOOKUP或LOOKUP函数查找作用，同时又能满足升序和降序查找要求。

4.3认识评分表

在表2中，最左列是单项得分（百分制），分数从高到低降序排列，其余各列是各测试项目成绩，这里只列出了部分有代表性的测试项目。不管测试项目有多少，体育测试项目数值只有两种排列形式：一种是数值越小得分越高，数值越大得分越低，当分数成降序排列时，这种项目成绩成升序排列，例如表2中的1000米跑、50米跑、篮球运球。另一种是数值越大得分越高，数值越小得分越低，当分数成降序排列时，这种项目成绩也成降序排列，例如表2中的立定跳远、排球垫球。两种形式排序相反，并存于评分表中。

但是，两种排列形式有一个共同点，就是数值排列不是连续的，换句话说，实际测试项目成绩在表中会找不到（不包括超出最大值到最小值范围的值），例如，实测成绩1000跑3分30秒、排球垫球47次，在表2中，如果采用查找函数精确查找，这两个成绩都是在98分与96分之间，没有对应的分数，系统报错。如果采用查找函数模糊查找，这两个成绩得到的分数都是98分，这是因为，查找升序排列数值时，如果查找函数找不到，就找小于实测值的最大值，例子中实测值是1000米跑3分30秒，小于它的有3分28秒和3分27秒，小于它的最大值是3分28秒，对应的分数是98分。查找降序排列数值时，如果查找函数找不到，就找大于实测值的最小值，例子中实测值是排球垫球47次，大于它的有49次和50次，大于它的最小值是49次，对应的分数是98分。很明显这个评分是错误的。如果采用人工查找，很容易得出这两个成绩都是得96分。这是因为，我们知道所有类似的评分表测试项目成绩是一个数值区间（多个数值）对应一个分数（最小区间就一个数值）。也就是说，在表2中隐含了数值区间的另一端值。

实际上表2中项目成绩给出的数值，如果是升序排列项目（1000米跑、50米跑、篮球运球）给出的是数值区间的高限数值，如果是降序排列项目（立定跳远、排球垫球）给出的是数值区间的低限数值。而查找函数模糊查找数值方向：升序排列时是找数值区间的低限数值（小于查找值的最大值）；降序排列时是找数值区间的高限数值（大于查找值的最小值）。这与表2给出的区间数值正好相反。所以，表2不适合查找函数模糊查找。有两种办法可以解决这个问题：

一种办法是，让表2列出隐含的另一端值。依据表2给出的数值，运用电子表格可以很容易得到数值区间的另一端值（参见表3）。

表3　大学男生体育测试项目评分标准表（隐含端值）

表3中的各项目数值，除第一行（100分）数值与表2对应行数值相等外，其余各行数值都是依据表2对应行的上一行数值增或减一个数得到。把表2和表3合并起来看，就可以看到各项目成绩的数值区间，也就形成了数值区间排列的连续性。上述查找函数模糊查找的例子，如果是查找表3，就会得到与人工查找一样的结果。这是因为表3给出的区间高低限值与查找函数模糊查找数值的方向正好相同。所以，表3才是自动评分所需要的评分表。但是，这一办法不可取，原因是表3与表2的数值不相等，容易造成混淆。另一种办法是，把表2完整倒置，单项得分成升序排列，原升序项目成降序项目，原降序项目成升序项目（参见表4）。

表4　大学男生体育测试项目评分标准表（倒置）

表4与表2比较，数值没有改变，但由于表4改变了表2的方向，所以，表2原来数值区间的端值到表4，由不适合变为适合，上述查找函数模糊查找的例子，如果是查找表4，只需要改变查找方向，原来升序查找变为降序查找，原来降序查找变为升序查找，改变后，查找结果与人工查找一样。所以，表4更适合自动评分采用。

4.4自动评分公式

通过实践，确定了适合自动评分的函数和评分表，所以，我们采用INDEX与MATCH两函数组合查找和倒置评分表（参见表4）办法来实现精准的自动评分。表5是体育成绩登记表，是根据上述体育成绩评定内容和比重进行设计的，可以实现对学生体育测试项目成绩自动评分，进而实现对学生学期体育成绩评定管理。

在表5中，测试项目分数栏中的记录是带有计算公式的，为了方便理解计算公式，我们用伪代码给出：800/1000米跑分数栏自动评分公式（伪代码）：

=（

IF（输入成绩=""，0，

IF（性别="男"，

IF（ISNA（MATCH（输入成绩，大学男生体育测试项目评分标准！1000米跑，-1））=TRUE，

MIN（单项得分），

INDEX（大学男生体育测试项目评分标准！单项得分，

MATCH（输入成绩，大学男生体育测试项目评分标准！1000米跑，-1））），

IF（性别="女"，?

表5　体育成绩登记表

IF（ISNA（MATCH（输入成绩，大学女生体育测试项目评分标准！800米跑，-1））=TRUE，

MIN（单项得分），

INDEX（大学女生体育测试项目评分标准！单项得分，

MATCH（输入成绩，大学女生体育测试项目评分标准！800米跑，-1））），0）））

）*0.2

立定跳远分数栏自动评分公式（伪代码）：

=（

IF（输入成绩=""，0，

IF（性别="男"，

IF（ISNA（MATCH（输入成绩，大学男生体育测试项目评分标准！立定跳远，1））=TRUE，

MIN（单项得分），

INDEX（大学男生体育测试项目评分标准！单项得分，

MATCH（输入成绩，大学男生体育测试项目评分标准！立定跳远，1））），

IF（性别="女"，

IF（ISNA（MATCH（输入成绩，大学女生体育测试项目评分标准！立定跳远，1））=TRUE，

MIN（单项得分），

INDEX（大学女生体育测试项目评分标准！单项得分，

MATCH（输入成绩，大学女生体育测试项目评分标准！立定跳远，1））），0）））

）*0.2

上述公式需要说明的是，对于超范围值（超出最大值到最小值范围之间的值），无需改变端值，可以通过ISNA函数判断，再结合对输入值的限制来解决。

MATCH函数如果是查找表2评分表时，输入成绩，如果超出最高分限值，查找函数会报错，ISNA函数显示为真，判给最高分；如果超出最低分限值，查找函数将不会报错，因为，查找函数总能找到最低分值。

MATCH函数如果是查找表4评分表时，输入成绩，如果超出最低分限值，查找函数会报错，ISNA函数显示为真，判给最低分。如果超出最高分限值，查找函数将不会报错，因为，查找函数总能找到最高分值。

800/1000米跑成绩栏限制：

自定义输入格式：#：##。

有效性检查的有效条件：允许整数，数据介于最小值1与最大值999之间。

立定跳远成绩栏限制：

输入格式：整数。

有效性检查的有效条件：允许整数，数据介于最小值1与最大值999之间。

5　结论

1）本文通过查询知网文献资料研究发现，现有体育成绩电子表格自动评分系统还存在不足，主要原因：一是错误选择VLOOKUP或LOOKUP函数来实现自动评分；二是存在对升序排列项目评分不准确问题。对评分表数值区间与查找函数模糊查找“方向”之间的关系认识不够，忽略了评分表中数值排列的连续性问题。把自动评分问题复杂化，犯了顾此失彼的错误，忽略了评分表的整体性。

2）采用INDEX与MATCH两个函数组合查询是实现自动评分首选。

3）采用单项分数成升序排列的评分表更符合电子表格查找函数模糊查找要求。

4）本文运用电子表格技术，研发出了“体育成绩管理系统”，实现了对学生体育测试项目成绩自动评分功能，并用伪代码形式给出了自动评分计算公式。

5）该系统小巧、开放，开发成本低，经济实用，可以作为体育成绩管理工具，满足体育教师个人对学生体育成绩管理的需求。但是，该系统安全性低，自动化程度低，处理数据量小，且尚未在实际中运用。可以作为“体育成绩数据库管理系统”雏形，为最终实现“体育成绩数据库管理系统”提供理论依据。

［1］王胜，张伟业.体育成绩管理系统设计与实现［J］.攀枝花学院学报，2011（6）：42-46.

［2］杨丹亚，黄显忠.体育成绩计算机评分系统的开发与应用［J］.商洛学院学报，2009（6）：88-91.

［3］陈宁，罗玲，王有智.运用Excel函数快速计算高校公体成绩探析［J］.黔南民族师范学院学报，2013（6）：93-96.

［4］宋强.使用Excel自动生成体育成绩的设计［J］.山东体育学院学报，2002（4）：89-91.

［5］原儒建.基于EXCEL函数的学生体质健康评价［J］.河北北方学院学报（社会科学版），2009（5）：90-92.

［6］施海波.运用ExcelVBA实现体育运动成绩的评分与统计［C］.第二十一届全国高校田径科研论文报告会论文专辑，2011.

［7］陈邦权.浅谈Excel函数在体育成绩评定中的应用［J］.科技信息（学术研究），2007（36）：562-565.

［8］黄敏.运用VLOOKUP函数处理体育成绩的查询与生成［J］.新课程（上），2013（6）：102-103.

［9］李娜.基于数据挖掘的高职体育成绩管理系统的设计与实现［D］.成都：电子科技大学，2012.

［10］董东风.学生三自主体育选项课管理系统数据结构研究［J］.湖南邮电职业技术学院学报，2015（2）：100-104.

Research on using spreadsheet to realize the automatic scoring of physical testing

DONG Dong-feng
（Hunan Postand Telecommunication College，Changsha，Hunan，China410015）

In order tomeet the needsof the information construction of collegesand universitiesand facilitate PE teachers to quickly and accurately givemarks to the students，the author inquired the CNKI literature and developed the Sports Performance Management System which uses the spreadsheet technology，INDEX-MATCH combination search and the individualscores sheetin ascending order，which achieves automatic scoring.The system canmeet theneedsof PE teachers for themanagementof students'physicalperformance. However，the system has low security，low degree ofautomation，smalldataprocessing size，and hasnotbeen used in practice.

spreadsheet；PE score；automatic scoring；calculating formula

10.3969/j.issn.2095-7661.2016.03.005】

TP311.52

A

2095-7661（2016）03-0018-05

2016-06-23

董东风（1958-），男，湖南长沙人，湖南邮电职业技术学院经济管理系教授，学士，研究方向：体育教学管理、体育竞赛编排。

2013年度湖南邮电职业技术学院资助科研项目“我院体育课自选系统开发与研究”（课题编号：2012BZ09）。