Excel处理《国家学生体质健康标准》数据的应用研究

刘月红

摘要：Excel是大家非常熟悉的应用软件，它强大的函数功能，可以帮助我们完成许多复杂的数据统计和操作。本文主要利用Excel 函数及邮件合并功能，对《国家学生体质健康标准》的数据进行自动计算、统计及《〈国家学生体质健康标准〉登记卡》的输出，实现了《国家学生体质健康标准》计算及统计，自动输出了《〈国家学生体质健康标准〉登记卡》，以方便学生体测数据管理，减轻体育老师的工作量。

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

一、前言

《国家学生体质健康标准》（以下简称《标准》）是对学生体质健康状态和锻炼效果进行评估的一种标准。

目前，国内学校每年将体测资料上传到“国家学生体质健康标准数据管理系统”软件（以下简称《体测软件》）。但是体育教师在软件的实际软件操作中，软件操作和易用性等方面存在着诸多问题。如果可以通过一种常用的软件，也可以实现《标准》数据的全部功能，且生成《〈国家学生体质健康标准〉登记卡》（以下简称《登记卡》），以方便体育教师上传，从而大大减轻体育老师的工作量。

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

二、目标设计

首要任务是自动计算和评价，即体育教师将数据输入电脑之后，马上能对数据进行相应分数的计算，并进行相应的评价;其次，实现《登记卡》一键自动生成的功能，方便毕业班级打印登记卡;再次，对所得的数据实现自动统计，自动统计出不及格率、及格率、良好率、优秀率、平均分等;最后，生成与《体测软件》对应的数据，方便体育教师将本校体测数据上传。

三、Excel自动计算、评价、统计、输出设计

（一）前期数据准备

如表1、表2、表3所示：

以中学为例，将《标准》评分表录入到一张工作表，将其命名为“标准”，按上表所示，分别将各项评分标准录入在一张工作表中，以备后续函数运用时的参数调用。

其中“50米”和“1000/800米”项目的评分表，需要在评分表中的评分值加“0.001”，如表3所示。

（二）基本函数运用详解

为实现自动评分和自动评价，我们用LOOKUP函数。

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

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

（三）各数据自动评分、评价的运用

如表6所示：

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

以初一年级为例，新建工作表，命名为“初一”（初二、初三以此类推），当教师将学生各项数据输入时，函数会自动计算出其对应的成绩和评价。

其公式如下：

体重指数对应的公式：= 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"））））;

肺活量成绩对应的公式：

=LOOKUP（G2，标准！$B$23：$B$42，标准！$A$23：$A$42）;

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

（四）数据优化

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

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

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

（3）《标准》中耐久跑和引体向上/仰卧起坐附加分，如何将这个分数自动加入。

通过对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所示：

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

=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））。

（五）《登记卡》自动生成

《登记卡》是每学年毕业班级每个学生通过三年的体测数据自动生成的，其数据来源就是每学年的体测数据。当一个学生三年当中每年的体测数据都输入完成，其对应的成绩、评价都可以通过上述方法自动生成，并用邮件合并。

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

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

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

=IF（初一！E2=""，""，初一！E2）

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

用Word调用“登记卡数据”工作本中的数据，《登记卡》就自动生成完成，生成后的登记卡如表8所示。

（六）数据结果统计与分析

新建一张统计表，如表9所示。

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

不及格人数：=COUNTIF（初一！AF：

AF，“不及格”），自動统计“初一”工作表中“不及格”的学生人数;

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

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

其他年级的统计公式依此类推。如果需要其他统计信息，也可以在此工作表中调用前面的所有数据。

（七）上报数据的生成

各个学校每年将体测数据上传至《体测软件》，在完成数据录入之后，如果数据不能与《体测软件》匹配，上传会出现错误，需要体育教师在软件上重复输入。但是通过下面的操作，可以自动完成数据的转化。《体测软件》中最主要的数据变化是1000米由“#.##”分转化成“###”秒，如1000米成绩是“3.55”，则在软件中需要输入“235”，可以通过下面的方法转化。

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

表10 分钟与秒的对应表

新建一个工作表，将其命名为“初一数据”（初二、初三以此类推），用公式“=IF（初一！A2=""，""，初一！A2）”

调用“初一”工作表中除“1000/800米”外的所有数据，调用“1000/800米”数据时用公式“=IF（初一！L2=""，""，（LOOKUP（初一！L2，标准！$A$366：

$A$726，标准！$B$366：$B$726）））”，

这样初一年级的所有上传数据自动调用完成，只需要将此工作表上传到《体测软件》，即可完成数据的上传，不需再重复输入。

四、总结

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

参考文献

[1]中华人民共和国教育部.教育部关于印发《国家学生体质健康标准（2014年修订）》的通知：教体艺〔2014〕5号[EB/OL].

[2014–07–07].http：//old.moe.gov.cn//publicfiles/business/htmlfiles/moe/s3273/201407/171692.html

[2]徐德宝.刍议Lookup函数在体育成绩评分上的应用[J].运动，2013（4）：121–122.