学生体质健康数据上报步骤（EXCEL软件处理国家学生体质健康标准数据的应用研究）

通过此方法，已经制作好初中和高中评价表，能自动评价和生成登记卡，有需要的体育老师，请在下面留言，注明“邮箱和学校名称”，我会发到你邮箱。

摘要：EXCEL是我们非常熟悉的应用软件，其强大的函数功能，可以让我们完成很多复杂的数据统计和运算。本文主要通过运用EXCEL的函数和邮件合并功能对《国家学生体质健康标准》数据的自动计算、统计和《〈国家学生体质健康标准〉登记卡》的输出，实现对《国家学生体质健康标准》的计算和统计，自动输出《〈国家学生体质健康标准〉登记卡》，方便学生体测数据管理，减轻广大体育教师的工作量。

关键字：学生体质健康标准；函数；邮件合并

1 前言

《国家学生体质健康标准》的内涵是测量学生体质健康状况和锻炼效果的评价标准，是国家对不同年龄段学生体质健康方面的基本要求。每个学生每学年评定一次，记入《〈国家学生体质健康标准〉登记卡》。

目前全国所有的学校每年都将体测数据通过“中国学生体质健康网”上传至“国家学生体质健康标准数据管理系统”软件。但是体育教师在实际软件操作过程中，软件的操作性和易用性方面，存在诸多问题。如果能够通过一种常用的软件，也能实现《国家学生体质健康标准》体测数据的自动计算、评价、统计功能，并且能自动生成《〈国家学生体质健康标准〉登记卡》，还能够将这些数据转换成与“国家学生体质健康标准数据管理系统”软件相对应的数据方便体育教师上传，将极大的减轻体育教师的工作量。

我们在教学中也会遇到许多报表的制作，其本质主要是对原始数据进行符合多个条件的统计。本文根据我校近年来的实际使用经验，阐述利用Excel公式进行自动计算、评价、统计、输出，实现“国家学生体质健康标准数据管理系统”软件的所有功能，并加以扩展，解决实际工作中的问题。

2 目标设计

首要的任务是自动计算和评价，即体育教师将数据输入电脑之后，马上能对数据进行计算相应的分数，并进行相应的评价；其次，实现《〈国家学生体质健康标准〉登记卡》一键自动生成的功能，方便毕业班级打印登记卡；第三，对所得的数据实现自动统计，自动统计出不及格率、及格率、良好率、优秀率、平均分等；第四，生成与“国家学生体质健康标准数据管理系统”软件对应的数据，方便体育教师将本校体测数据上传。

3 EXCEL自动计算、评价、统计、输出设计

3.1 前期数据准备

图1：身高体重指数评分表

图2：肺活量评分表

图3：1000米评分表

以中学为例，将《国家学生体质健康标准》评分表制作成一张工作表，命名为“标准”，按上图所示，分别将“体重指数（BMI）单项评分表”、“肺活量单项评分表”、“50米跑单项评分表”、“坐位体前屈单项评分表”、 “立定跳远单项评分表”、“一分钟仰卧起坐、引体向上单项评分”、“耐力跑单项评分表”、“男生引体向上、女生一分钟仰卧起坐附加分评分表”、“耐久跑附加分评分表”的评分标准录入在一张工作表中，以备后续函数运用时的参数调用。

其中“50米”和“1000/800米”项目的评分表，需要在评分表中的评分值后面另加一个“0.001”（后续LOOKUP函数会调用），如图3。

3.2 基本函数运用详解

为实现自动评分和自动评价，主要使用IF函数和LOOKUP函数。

3.2.1 IF函数

IF函数的涵义：如果（内容为 True，则执行某些操作，否则就执行其他操作）。

例如在进行评分时，如果性别为“男”，则后续自动匹配男生的评分标准，否则就匹配女生的评分标准。

3.2.2 LOOKUP函数

LOOKUP函数的涵义：LOOKUP (lookup_value, lookup_vector, [result_vector])，LOOKUP函数先找到lookup_value，然后匹配lookup_vector相同位置的值。如果 LOOKUP 函数找不到 lookup_value，则该函数会与 lookup_vector 中小于或等于 lookup_value 的最大值进行匹配。

例如在进行肺活量（表2）评分，当学生的肺活量数据是“2300”时，函数会在评分表自动找到“2300”对应的分数是“70”分，从而返回值“70”，当实际数据小于“2300”，如“2290”时，此时函数找不到“2290”对应的分数值，函数会将小于 “2290”对应的值“2180”自动匹配“68”的分数，从而返回值“68”（见图4）。

图4：肺活量评分示例表

但是50米和1000/800米评分时，成绩越小，分数越高，如果按照上述“如果 LOOKUP 函数找不到 lookup_value，则该函数会与 lookup_vector 中小于或等于 lookup_value 的最大值进行匹配”的规则则会出现问题。如图5，左图中当学生成绩在表中有对应的值时如“7.8”，则返回值是“100”，当学生成绩在表中没有对应的值如“8.3”，返回的值是“80”，而实际分数应该是“78”。为解决这个问题对评分表做适当调整，如图5右图中，将标准值后面加上0.01，这样当学生成绩没有对应的值的时候如“8.3”，函数会将小于“8.3”对应的值“8.21”自动匹配“78”，这样就完美解决“成绩越小，分数越好“的自动评分的问题。

图5：调整前（左图）后（右图）评分示例表

3.3 各数据自动评分、评价的运用

图6：学生成绩自动计算及评价表

清楚了LOOKUP函数的涵义，下一步目标是对学生的所有体测数据进行自动计算和自动评价。

以初一年级为例，新建工作表，命名为“初一”（初二、初三以此类推），当教师将学生学号、姓名、性别、身高、体重、肺活量、50米、立定跳、坐位体前屈、引体向上/仰卧起坐、1000/800米的数据输入时，函数会自动计算出其对应的成绩和评价。

其公式如下：

体重指数对应的公式：=F2/(E2/100*E2/100)；

体重指数成绩对应的公式：

=IF(M2>=25,"60",IF(AND(M2>=22.2,M2<25),"80",IF(AND(M2<22.2,M2>15.4),"100",IF(M2<=15.4,"80"))))；

体重指数评价对应的公式：

=IF(M2>=25,"肥胖",IF(AND(M2>=22.2,M2<25),"超重",IF(AND(M2<22.2,M2>15.4),"正常",IF(M2<=15.4,"低体重"))))；

肺活量成绩对应的公式：

=LOOKUP(G2,标准!$B$23:$B$42,标准!$A$23:$A$42)；

肺活量评价对应的公式：

=IF(P2>=90,"优秀",IF(AND(P2>=80,P2<90),"良好",IF(AND(P2>=60,P2<80),"及格",IF(P2<60,"不及格"))))；

以此类推，设置相应的公式，就可以对其它各项体测数据进行自动计算和评价。

3.4 数据优化

在实际成绩输入数据过程中会出现几个问题：

（1）男生体测和女生体测的标准不一致，项目不一致，如何对输入的成绩自动判断并根据判断选择男或女生的项目和评价标准；

（2）如果学生出现某个项目缺考，如何让这个单项成绩为0分，其它项目都正常显示，以利最后的总评；

（3）《国家学生体质健康标准》对引体向上/仰卧起坐和1000/800米有一个附加分，如何将这个分数自动加入。

通过对IF函数和LOOKUP函数的深度尝试，我们发现可以完美解决以上出现的问题。

以50米评分为例（图6），公式如下：

=IF(D2="男",(IF(C2="","",IF(OR(H2="",H2=0),0,IF(H2>标准!$B$91,0,(IF(H2<=标准!$B$72,100,(LOOKUP(H2,标准!$B$71:$B$91,标准!$A$71:$A$91)))))))),(IF(C2="","",IF(OR(H2="",H2=0),0,IF(H2>标准!$B$115,0,(IF(H2<=标准!$B$96,100,(LOOKUP(H2,标准!$B$95:$B$115,标准!$A$95:$A$115)))))))))。

此公式的涵义为：首先判断这名学生是否是“男”，如果是“男”，则执行后面“男生评价标准”的函数，否则执行“女生评价标准”的函数；再次判断学生姓名有没有输入，如果没有姓名，则50米成绩显示为空白，否则继续执行后面的函数；最后还要判断，50米成绩是“空白（无成绩）”的还是“0”，当50米成绩是“空白（无成绩）”或者是“0”时，其成绩自动判断为“0”分，否则执行对应的评分标准，并自动计算成绩。这是一个多重嵌套的IF函数，完美解决第（1）和（2）的问题。

对于第（3）个问题，在引体向上/仰卧起坐和1000/800米评分时，可以对评分表格做出适当调整，见图7。通过对附加分评分标准的调整，可以完美解决附加分自动评分的问题，其公式与上述“50米评分”类似。

图7：1000米项目附加分评分标准表

最后“总评”成绩，可以通过如下公式自动算出：

=IF(C2="","",(N2*0.15 P2*0.15 R2*0.2 T2*0.1 V2*0.1 X2*0.1 Z2*0.2 AB2 AC2))。

3.5 《〈国家学生体质健康标准〉登记卡》的自动生成

《〈国家学生体质健康标准〉登记卡》是每学年毕业班级每个学生通过三年的体测数据自动生成的，其数据来源就是每学年的体测数据。当一个学生三年当中每年的体测数据都输入完成，其对应的成绩、评价都可以通过上述方法自动生成，当有了这些数据，就可以通过邮件合并功能，将这些数据自动填充到WORD模板当中。

作为邮件合并的数据源，其数据需要做适当处理。根据EXCEL邮件合并的规则，邮件合并的数据源的所有数据必须在一个工作表，需要把所有数据合并到一个工作表，作为邮件合并的数据源。

新建一个工作表，命名为“初三毕业登记卡数据”（如图6）。

用公式调用“初一”、“初二”、“初三”中的所有数据到“初三毕业登记卡数据”工作表，实现“初三毕业登记卡数据”的自动填充，公式如下：

=IF(初一!E2="","",初一!E2)

此公式涵义是：“初一”工作表“E2”为空时，“初三毕业登记卡数据”对应的“E2”也为空，如果有数据，就调用“初一”工作表“E2”的数据，以此类推，可以将“初一”、“初二”、“初三”所有数据调用到一张工作表，这样邮件合并的数据源就完成。

打开WORD模板，通过邮件合并功能，用WORD调用“初三毕业登记卡数据”工作本中的所有数据，《〈国家学生体质健康标准〉登记卡》就自动生成完成，生成后的登记卡如图8。

图8：通过邮件合并功能自动生成的〈国家学生体质健康标准〉登记卡》

3.6 数据结果的统计与分析

新建工作表，命名为“数据统计”，制作一张统计表，如图9。

图9：学生统计表

在对应的表格中输入如下公式：

不及格人数：=COUNTIF(初一!AF:AF,"不及格")，自动统计“初一”工作表中“不及格”的学生人数；

不及格比率：=B4/K4，自动统计“初一”工作表中“不及格”学生占总人数的比率百分比；

平均分：=AVERAGE(初一!AE:AE)，自动统计“初一”工作表中所有学生的总评平均分。

其它年级的统计公式依此类推，当“初一”、“初二”、“初三”三个工作表学生成绩输入之后，此统计信息自动完成并生成，方便学校及体育教师对本校的学生体质健康状况有一个初步的了解。如果需要其它统计信息，也可以在此工作表中调用前面的所有数据。

3.7 上报数据的生成

每年各个学校将本校体测数据通过“中国学生体质健康网”上传至“国家学生体质健康标准数据管理系统”软件，在完成上述数据输入之后，如果数据不能与“国家学生体质健康标准数据管理系统”相匹配，上传会出现错误，需要体育教师在软件上重复输入。但是通过下面的操作，可以自动完成数据的转化，方便上传。“国家学生体质健康标准数据管理系统”软件中最主要的数据变化是1000米由“#.##”分转化成“###”秒，如1000米成绩是“3.55”，则在软件中需要输入“235”，可以通过下面的方法转化。

在“标准”工作表中建立一个“分钟”与“秒”对应的一个标准，做为函数调用的参数（如图10）。

图9：分钟与秒的对应表

新建一个工作表，命名为“初一数据上报”（初二、初三以此类推），用公式“=IF(初一!A2="","",初一!A2)”调用“初一”工作表中除“1000/800米”外的所有数据，调用“1000/800米”数据时用公式“=IF(初一!L2="","",(LOOKUP(初一!L2,标准!$A$366:$A$726,标准!$B$366:$B$726)))”，这样初一年级的所有上传数据自动调用完成，只需要将此工作表上传到“国家学生体质健康标准数据管理系统”，即可完成数据的上传，不需再重复输入。

4 总结

通过EXCEL函数和邮件合并功能的实际运用，EXCEL完全可以实现《国家学生体质健康标准》的数据自动计算、评价、统计及输出《〈国家学生体质健康标准〉登记卡》的功能。在开始制作工作表的时候，看起来公式运用比较繁琐和复杂，但是理解LOOKUP和IF函数的具体涵义之后，操作起来就非常简便，一个项目公式完成以后，其它项目只需要修改个别参数就可以完成，包括其它年级，也包括大学的体测数据，都可以全部参照此公式。当几个工作表都完成之后，体育教师只需要每年按体测的各项数据输入，学生的成绩、评价、各项统计、登记表、数据上报等所有工作全部自动完成，将大大减轻体育教师的工作量。

参考文献：

[1] 中华人民共和国教育部.教育部关于印发《国家学生体质健康标准（2014年修订）》的通知:教体艺[2014]5号[A/OL]].[2014-07-07].http://old.moe.gov.cn//publicfiles/business/htmlfiles/moe/s3273/201407/

171692.html

[2] 徐德宝.刍议Lookup函数在体育成绩评分上的应用[J].运动,2013 (4) :121-122.

- - - - - - - - - - END - - - - - - - - -

为了大家更好 的查找本微信公众号发布的信息，我们对信息进行了整理：

回复数字【1】：可以查看所有体育教学视频课堂实录；

回复数字【2】：可以查看三大球（足、篮、排）体育教学系列片；

回复数字【3】：可以查看三小球（羽、网、乒）体育教学系列片；

回复数字【4】：可以查看田径类体育教学系列片；

回复数字【5】：可以查看体育教案设计汇总；

回复数字【6】：体育教学资料干货汇总；

回复数字【7】：可以对本微信号的历史记录进行关键字搜索。

回复数字【8】：可查看体育教师招聘、体育老师理论考试、入编考试、教学基本功比赛、职称评定理论考试相关资料；

回复数字【9】：可查看体育游戏资料汇总。

,