基于Excel的体育成绩统计分析模型设计

张波

摘 要：体育成绩的计算和其他学科不同，如果按照传统方法，费时费力还有可能出错。如何根据学生的测试成绩快速准确地计算出分数并进行分析，关乎体育教师的办公效率甚至影响教学质量。本文以体育中考项目为例，利用办公软件Excel中的函数，图表等工具，建立统计分析模型。通过这个模型，体育教师只需要输入测试成绩就可以得到分数，还可以对每个班级的得分情况进行分析，从而减少教师的工作量，提高工作效率。

关键词：Excel 体育成绩 统计分析

中图分类号：G80 文献标识码：A 文章编号：2095-2813（2018）03（c）-0246-02

在体育教学过程中，体育教师经常对学生的身体素质进行测试，测试后得到的是数据而不是分数，需要体育教师对比评分标准查每一项的得分，这就给体育教师增加了很大的工作量。除了成绩的统计，对学生成绩的分析也是必不可少的工作，包括总分、及格率、优秀率和平均分等。如果按照以前的方法统计和分析，必定费时费力，效率低。本文以体育中考成绩的统计分析为例，建立体育成绩统计分析模型，模型建立后，只需输入学生成绩，就可以得出每一项的得分以及总分、平均分、优秀率及格率等。

1 相关函数介绍

1.1 LOOKUP函数

函数LOOKUP语法：LOOKUP（lookup value，lookup vector，result vector）。Lookup value要查找的学生单项原始成绩。Lookup vector是评分表中的达标标准，输入数据时必须按照升序排列。result vector是评分的得分值。

1.2 IF函数

IF函数是使用函数IF对数值或公式进行条件检测，进行真假值判断，根据逻辑计算的真假值，返回不同结果。语法为：IF（logical_test， value_if_true，value_if_false）。Logical test是逻辑判断，表示计算结果为TRUE或FALSE的表达式。本例中由于男女生的评价标准不同，所以要先进行男女性别判断。例如，“D5="男"”就是一个逻辑表达式，如果单元格D5中的值等于男，表达式即为LOOKUP（成绩统计！E5，评分表调整！$B$5：$B$23，评分表调整！$A$5：$A$23），否则为LOOKUP（成绩统计！E5，评分表调整！$C$5：$C$23，评分表调整！$A$5：$A$23）。其中“评分表调整！$B$5：$B$23”代表男生评分表，“评分表调整！$A$5：$A$23”为指定区域的评分分值。另一个表示女生的评分表。

2 体育成绩统计分析模型设计

2.1 制作评分标准

在体育成绩评分过程中，每一项测试都有相应的成绩评定标准，为了让Excel能够自动查表评分，我们得把这个标准录入到Excel工作表中去，让它自动根据这个标准来分析评分。

首先，新建Excel，重新命名3个工作表，分别命名为评分表、评分表调整、成绩统计和成绩分析，在评分标准处粘贴《2015年杭州市区各类高中招生体育考试各项目评分表》，见图1。在录入评分标准时要把成绩和得分成反比的项目的计时项目与成绩和得分成正比的项目分开进行升序录入，见图2。

然后，对评分表进行调整。每一个项目原始成绩按照升序排列，对应的分值排列顺序也要变化。由于如果LOOKUP函数查找不到与成绩相对应的“lookup value”参数，则会查找“lookup vector”参数中小于或等于“lookup value”参数的最大数值；如果“lookup value”参数小于“lookup vector”参数中的最小值，函数Lookup会返回错误信息。所以在录入评分标准时，成绩与得分成反比的项目（800m跑、1000m跑和100m游泳），应该在每个分数对应的成绩下限值最后一位数的后面加“1”，使成绩提高一個分数段，满分段成绩则输入“1”。例如：男生1000m跑，3′40″及以下为10分，3′45″为9.5分，所以就应该在10分对应的成绩处输入1，在9.5分对应得成绩里输入3.41（意思是1′～3′40″都是满分）。成绩与得分成正比的项目（立定跳远、跳绳、实心球、仰卧起坐和引体向上），在满分段成绩输入“1”，其他不变。调整后的评分标准见图2。

2.2 利用函数创建评分公式

在工作表成绩统计中创建一个如图3所示的体育成绩统计表。在表中输入班级、姓名、性别和各项目成绩得分。下面以立定跳远为例，利用if函数和lookup函数创建评分公式。

点击F4单元格，在编辑栏中输入“=IF（D4="男"，LOOKUP（成绩统计！E4，评分表调整！B5：B23，评分表调整！A5：A23），LOOKUP（成绩统计！E4，评分表调整！C5：C23，评分表调整！A5：A23））”输入方法是：所有标点都是在英文输入状态下输入。Lookup后面括号里的输入方法是，点击“成绩统计”工作表中的E4，加逗号，选择“评分表调整”中的B5～B23列，加逗号，选择“评分表调整”中的A5～A23列。后面的lookup输入方式相同，只是要按照女生的标准输入。该函数的含义是：如果是男生，则按照“评分表调整”中的男生立定跳远的标准评价得分。如果是女生，否则按照女生标准评价得分。公式输入好后就可以使用填充柄下拉，但是下拉过程中lookup函数查找范围会出现变化，这里需要使用绝对引用（符号$），方法是：鼠标选择公式中的“B5：B23”，快捷键fn+f4或者f4键，变为$B$5：$B$23。最后公式变为“=IF（D5="男"，LOOKUP（成绩统计！E5，评分表调整！$B$5：$B$23，评分表调整！$A$5：$A$23），LOOKUP（成绩统计！E5，评分表调整！$C$5：$C$23，评分表调整！$A$5：$A$23））”然后在使用填充柄下拉。再使用相同的方法给其他项目创建评分公式。

2.3 计算总分

根据以上的公式算出每一项的得分后，可以用求和函数得出每个学生的总成绩。例如：图4中，张1的体育总成绩计算方法如下：在单元格Q4中输入函数“=SUM（=F4+H4+J4+L4+N4+P4）”按下回车键就可以自动算出总成绩，再利用填充柄下拉，可以得到每位学生的体育总成绩。由于杭州体育中考要求学生从3个大项中选择3项进行考试，所以需要对没有选择的项目做零分处理，得到的总分才是最终的成绩。如果想换算成百分制，可以在R4单元格输入函数“=Q4 *100/30”。

3 结语

（1）利用Excel设计出来体育成绩统计分析模型后，只需输入学生测试成绩就可以直接得出分数，快速有准确。

（2）如果需要分析学生成绩，只需利用Excel中的其他函数（如AVERAGE、SUM等）就可以很快速的算出结果。

（3）本文以中考体育为例进行统计分析模型设计，其他的体育成绩（如体质测试、体育期末成绩等）都可以模仿此模型进行设计。

参考文献

[1] 杨培杰.简论体育成绩登记与统计的几个小窍门[J].运动，2014（4）：108-109.

[2] 刘冉.基于Excel电子表格的体育成绩统计软件设计[J].电子设计工程，2017（20）：17-20.

[3] 金洁.对Excel实现体育成绩自动化统计的分析[J].自动化与仪器仪表，2016（2）：161-162.

[4] 宋志超，姜伯乐.Excel在体育成绩计算中的应用研究[J].体育科技文献通报，2014（9）：133-135.