运用Vlookup函数处理体育成绩的查询与生成

摘 要：巧妙利用办公软件Excel中的Vlookup函数,能够高效处理体育成绩的查找与自动生成.此外,由于体育项目的田、径赛在判分标准上存在不同,还需要在前期制作评分标准表.

关 键 词：评分表；查找区域；Vlookup

信息老师们对于Excel常用函数如求和、平均数等非常熟悉.事实上,除了在教学中涉及的函数,Excel里臧龙卧虎、高效解决实际问题的函数比比皆是.近期,笔者利用Excel的Vlookup函数解决了令我校体育老师头晕眼花的麻烦事：查找并且自动生成学生的各项体育成绩.

一、初步解读Vlookup函数

Vlookup又称垂直查询函数,属于查找与引用函数类.表达式为Vlookup（查找值,查找的区域,返回位置<列>,查找方式）.

第一个参数F3代表查找值,第二个参数A3：B23代表查找的区域（具有标准单项得分的区域）,第三个参数2对应要返回查找区域A3：B23中的哪一列记录.这里单项得分属于第2列；第四个参数为1,为近似值.

读者可以发现,Vlookup函数的运用是否得当,关键在于查找的区域的选定.由于该函数的查找方式为近似匹配,其特点是查找区域的数据都要为升序,否则出错.而本案例中的数据查找区域是存在于评分标准中.因此,两种赛事的评分标准表的建立是关系到能都正确运用Vlookup函数的关键.

二、评分标准表的建立

根据体育学科的相关评分标准,在Excel中建立评分标准.目的是为将进行选用的函数进行自动查分提供标准.由于体育项目分田赛与径赛.以高度和距离长度计算成绩的跳跃、投掷项目称为田赛.田赛项目的测试结果与成绩是正比例对应；至于径赛项目,是以时间计算成绩的竞走和跑,它的测试结果与成绩恰恰是反比例关系.下面分别说明它们评分标准的建立.

1.田赛查分表

在上文中说明过,评分标准表中的数据需要统一为升序.以立定跳远为例,评分标准表的建立就很简单,对于田赛项目,评分规则就是数值越大,得分越高,两列单元格的数值自可统一为升序.如上图1所示.数据A列与B列同为升序排列.

2.径赛查分表

径赛评分标准表中首先要解决格式的问题.即要以分秒形式显示时间数值,如3分45秒表示成3―45.若使用键盘或特殊符号输入会很麻烦,这里可以采用设置单元格格式的方法,将A列的单元格选中,鼠标右键点击选择设置单元格格式―数值―自定义―0―00―00.

接着就要解决评分标准中数据区域同为升序排列.

正常的评分标准表只有两列,时间值与单项得分.在原始评分表中增加一个过渡处理列.将“径赛评分表”中的标准成绩进行“负数”处理.如图3所示.只需在B3单元格输入“等于A3*（-0.01）”.需要说明,虽然在A3单元格显示的是5'12,但是公式A3*（-0.01）应用时,自动识别的是最初输入的512.因此,B3单元格结果为负数.B3以下单元格做相同处理.

此时,评分标准中A列的时间显示值对应正确的C列得分.同时B列与C列同时升序排列.读者可以想到,在后面运用Vlookup时,表达式中“查找的区域”就只是B3：C23.

以上就成功地建立了体育项目的各评分标准区域.这里,还需要考虑到部分学生的成绩太低,其测试结果不在评分表中.因此有必要对标准分的下限加以设置.

三、0分值的设置

在径赛类项目的评分表的增加设置“零分行”,如图4.在田赛类项目的评分表的“零分行”设置为“零分行”设置为-100,图5所示.

至于上限100分,根据函数近似匹配的特点,会自动取得满分.因此上限不需再进行设置.

四、“自动填充”的批处理

最后利用Excel的“自动填充”功能来实现所有学生的同一项体育成绩的批量操作.为避免由于“自动填充”带来的单元格区域的改变,要在查询区域涉及的单元格行标前添加绝对引用符“$”.例在“1000米”中,H3“等于VLOOKUP（G3,B$3：C$23,2,1）”.图6所示.

当然,根据老师个人需要,可以再运用Excel的其他函数公式就能计算出总分、合格率、名次等等,将统计结果不断扩充完善.

使用Vlookup函数,非常方便快捷地完成了学生体育成绩的查找与统计.从中,我们也可以感受到Excel办公软件的实用性非常广泛.这一方法也适用于其他学科的类似统计汇总工作,提高工作效率.学会巧妙运用办公自动化工具,将我们从繁琐的重复性劳动中解脱,便能有更多的时间和精力投身到教学和科研中去.