如何运用VLOOKUP等函数制作体育达标成绩册

田江华

（重庆市开县九龙山初级中学 重庆 405415）

如何运用VLOOKUP等函数制作体育达标成绩册

田江华

（重庆市开县九龙山初级中学 重庆 405415）

体育达标运动会是所有学生都要参加的一年一度的《国家体育锻炼标准》的测验，达标运动会组织工作任务繁重，根据我多年的体育教学和组织运动会的经验来看，工作量最大的不是报名工作，也不是秩序册的编排工作，而是达标运动会的成绩统计工作，厚厚的几本达标运动会成绩册实在让人头疼。传统的查分方法工作量巨大，工作效率很低，统计容易出错。

VLOOKUP 函数制作 电子表格

我在工作中经常使用电子表格，电子表格的强大统计功能使我萌生制作一个能自动生成《国家体育锻炼标准》成绩的体育达标成绩册的想法，只需录入学生姓名、测试成绩，就能自动生成以下数据：得分、总分、达标等级、各等级人数、达标率、优秀率等需要的统计指标，从而达到减轻体育教师的查分、统计的工作量，提高工作效率，同时保证成绩和统计的准确性。

首先简单介绍一下Microsoft Excel工作簿,它是计算和储存数据的文件，每一个工作簿都可以包含多张工作表，因此可在单个文件中管理各种类型的相关数据。工作表是Microsoft Excel用来存储和处理数据的最主要的文档，其中包括排列成行和列的单元格，工作表是工作簿的一部分，也称作电子表格。

下面谈谈我是如何运用Microsoft Excel中的VLOOKUP等函数制作体育达标成绩册的（以七年级为例）。

第一步：新建一个工作簿。命名为“七年级体育达标成绩查询表”，工作簿内新建四张工作表，分别命名为“七年级达标标准”、“七年级男生成绩表”、“七年级女生成绩表”、“七年级成绩统计表”（如图1）。

第二步：查分表的制作。在电子表格中查分方法可以采用精确查找法，也可以采取近似查找法。《国家体育锻炼标准评分表的使用方法》中的规定：如果某项成绩低于某分数线的成绩但高于下面分数线的成绩时，应按下面分数线记分；对于高于最高分数线的成绩，按最高成绩计算；低于最低成绩的，按0分计算。通过统计发现，虽然达标测试各项目的成绩有很多种可能，但都能在电子表格单元格中反映出来，也就是说我们能通过电子表格列出所有可能出现的成绩，并且能根据《国家体育锻炼标准》及其评分规定列出所有可能出现的测试成绩的对应得分，所以制作查分表就是列出不同性别不同项目不同成绩的不同得分，查分表就是一本“数据字典”(如图2)。

为了真实记录学生测试成绩，各项目100分线以上成绩可以列举到一个一般不容易达到的成绩，除径赛项目外其他项目最低成绩可列至零(如图2）。

第三步：成绩表的设计。成绩表内测试项目一定在查分表中所列项目之中，通常情况下，我们设计达标成绩查询表时，就要考虑所测项目与查分表对应，当然也可以在查分表中列出所有可能测试项目，成绩表中也可根据查分表列出所有可能测试项目。在此我以选定的测试项目设计表格作为例子(如图3)。

第四步：运用VLOOKUP函数自动查询生成得分（以七年级男生成绩表为例，女生成绩表公式相同）。我们录入各项目测试成绩，由于对应栏目设置了查找函数、求和函数、条件函数等，录入测试成绩后，各单项得分、总分和等级将自动生成。

先要利用Mmicrosoft Excel的查找函数VLOOKUP来实现测试项目得分的自动查找，例如50m跑项目，先在测试成绩相邻的得分栏E4列输入：E4=VLOOKUP（D4，七年级达标标准，A：B，2，FALSE）。

图3函数的语意是将表“七年级男生成绩表”中所在行D4的值与表“七年级达标标准”中A列的值进行精确匹配，返回表“七年级达标标准”命中行中B列的值。当在表“七年级男生成绩表”中D4成绩单元格录入测试成绩后，对应得分栏即时自动生成得分。如上图G4、I4、K4列函数设置与D4单元格相同，其他相应单元格可类推，可以用电子表格自动填充功能实现L、G、I、K列其他单元格的公式输入，但要注意与表“七年级达标标准”中取值区间对应。

第五步：求总分和自动生成等级。总分栏L列是求E、G、I、K列的和，其公式为：L4=E4+G4+I4+K4，其他行类推，也可以用电子表格自动填充功能实现L列其他单元格的公式输入。

图1

图2 九龙山初中七年级2011年达标运动会评分表

等级栏M列是根据E、G、I、K列的单项得分和L列的总分判断学生达标等级，其公式为：M4=IF(OR(E4<30,G4<30,I4<30,K4<30),“不及格”,IF(L4>=360,“优秀”,IF(L4>=300,“良好”,IF(L4>=240,“及格”,“不及格”))))。

上面函数的语意是任何一项成绩＜30分，则不及格；总分≥360分，则优秀；总分≥300＜360分，则良好；总分≥240分＜300分，则及格；否则不及格。

第六步：制作成绩统计表。在第一步我们已经建好“七年级成绩统计表”（如图4）。

从图4我们可以看出参测年级各项等级统计指标，男生统计以“七年级男生成绩表”为数据源，女生以“七年级女生成绩表”为数据源。在此我们以男生测试统计指标为例对各统计指标单元格内公式进行说明。

（1）参测人数：B3=COUNTIF(七年级男生成绩表!$L$4:$L$1003,“>0”)，求“七年级男生成绩表”中参加测试人数。

（2）优生人数（单项>30,总分≥360）和优秀率：C4=COUNTIF(七年级男生成绩表!$L$4:$M$1003,“优秀”)，根据“七年级男生成绩表”中等级栏结果统计出“优秀”人数。D4=C4/B4,即优秀人数队以参测人数得出优秀率。

（3）良好人数（单项>30,总分≥300）和良好率、及格人数（单项>30,总分≥240）和及格率公式设置和计算方法同优秀人数和优秀率的公式，但要注意修改公式相关参数。

（4）不及格人数（单项<30,总分<240）和不及格率：不及格人数等于参测人数减去优秀、良好、及格人数；不及格率方法相同。

女生成绩统计同男生统计方法，但其统计数据源为“七年级女生成绩表”。

上文所述只是一个简明的运用VLOOKUP等函数制作达标成绩统计表的操作步骤，是针对达标测试项目制作的专门表格，没有考虑《国家体育锻炼标准》中所有测试项目，当然也可以采用相同方法将所有测试项目得分标准全部列出，对应设计制作成绩表和成绩统计分析表，当年不测试的项目可隐藏其对应列。当然，这只是一个可以根据学校每年不同测试项目定制的一个简易统计表格，作为体育教育工作者，我更期待相关部门或软件开发者为《国家体育锻炼标准》配套开发一个科学易学的查询系统。

图3

图4

[1]钭志斌.Excel在财务中的应用实训[M].高等教育出版社，2011.

[2]李印利.浅谈电子表格教学[J].黑龙江科技信息,2009(2).

G633.96

A

2095-2813(2013)09(b)-0152-02