浅析用EXCEL函数制作国家体测数据的统计分析表

以下由评委填写

浅析用EXCEL函数制作国家体测数据的统计分析表

摘要：以《国家学生体质健康标准(2014年修订)》（以下简称《标准》）为基础评判依据，利用EXCEL中的函数LOOKUP、CHOOSE、MATCH、COUNTIF、IF及《标准》中的权重等制作简略数据分析表，使广大基础体育教师在有EXCEL软件的情况下可以简单快速的进行数据分析，增加工作效率。

关键字：excel，函数，国家体质健康测试数据，统计分析

国家体质健康测试是我国为建立健全学生体质健康监测评价机制，激励学生积极参加身体锻炼所制定的一项长期政策。每年全国所有学校都将对学生进行体质健康测试并将数据输入学生体质健康网（http://www.xiexiebang.com/），网站将对数据进行统一分析并反馈在网站上。在这一过程中，为了落实《国务院办公厅转发教育部等部门关于进一步加强学校体育工作若干意见的通知》(国办发〔2012〕53号)和《教育部关于印发〈学生体质健康监测评价办法〉等三个文件的通知》(教体艺〔2014〕3号)有关要求[1][2]，基层体育教师需明确各自年级、班级的四率（优秀率、良好率、及格率、不及格率）及相关的排列，由于数据多、计算量大且数据的动态变动，使没有专用工具的体育老师的工作量大大增加。办公软件office中的EXCEL是一个基础的表格软件，本文利用EXCEL中的一些函数可以实现简单的数据分析，满足广大体育教师的基础工作需求。

一、我们先了解几个需要用到的基本函数：

1.LOOKUP函数

作用是在单行或单列区域（向量）中查找某一数值然后返回某行或某列的数值。语法为lookup（查找的数值，查找对比的数据区域，返回的数据区域）[3]，需要注意的是查找对比的区域数据必须按照从小到大、A~Z、假~真进行顺序排列，否则函数将不能返回正常数值。

具体应用举例：如左图，为了在G4格显示F4格测试成绩的相应评分，则只要在G4格中输入=lookup（F4,B4:B25,A4:A25)，意义为将F4格中的数据在区域B4到B25的数据中进行查找，找到相同或区间后将区域A4到A25中相对应的数值（同列）返回到公式所在的位置。图中左侧A列B列为评分标准，如F4中的1125对照B列中1180>1125>1120,则公式会返回较小数值的A列对应值，即1120所对应的74，并返回到G4格中。

由于lookup函数的局限性，在速度类的测试中，如右图：例如10.7秒的数据查找后10.6<10.7<10.8,用lookup返回的是较小数值10.6对应的B列数据80分，但根据实际情况10.7秒是慢于10.6秒的，实际应该返回10.8秒所对应的78分。此时就需要用到函数MATCH函数与CHOOSE函数配合来解决速度类的数值分析。

2.CHOOSE函数

作用是按照参数值返回数值列表中的数值。其中参数值为1到29之间的数字，数值最多29个。语法为：CHOOSE（参数值，数值1，数值2，数值3......数值29）[3]，例如，=CHOOSE（5，21，13，5，20，38，44，87，5）显示的是38，即5后面的第五个数字。需要说明的是数值也可以是区域，这就为了综合列式打下基础。比如= CHOOSE(2,A1:A10,B1:B10,C1:C10)相当于在公式格中返回B1:B10。

3.MATCH函数

作用是返回在指定方式下与指定数组匹配的数组中元素的相应位置。

语法是：MATCH(查找值,{要查找的数组},查找类型)[3]，其中需要注意的是查找的数组排列必须与查找类型相对应。如果查找类型为 1，则函数MATCH查找小于或等于查找值的最大数值，要查找的数组必须按 升序 排列：如...、-2、-1、0、1、2、...、A-Z等； 如果查找类型为0，则函数MATCH查找等于查找值的第一个数值，查找的数组可以按任何顺序排列；如果查找类型为-1，则函数MATCH查找大于或等于查找值的最小数值，要查找的数组必须按降序 排列：如Z-A、...、2、1、0、-1、-2、...等；如果省略查找类型，则自动设为 1。

例如：=MATCH(10.7,{11,10.8,10.6,10},-1),则显示的是2，即10.7在数组中有11，10.8两个数值大于它，而10.8在这两个数中最小，则函数返回10.8在数组中处于的位置2。

MATCH函数可以返回数值在数组中的位置，而CHOOSE函数可以利用这个位置返回对应的数值，这样两个函数组合起来就可以计算出速度型的成绩所对应的分数。如下图F4内的成绩对应的G4格中分数公式为：

=CHOOSE(MATCH(F4,{30,13.6,13.4,13.2,13,12.8,12.6,12.4,12.2,12,11.8,11.6,11.4,11.2,11,10.8,10.6,10.5,10.4,10.3,10.2,4},-1),0,10,20,30,40,50,60,62,64,66,68,70,72,74,76,78,80,85,90,95,100,100)，公式中MATCH函数中的数组中的数值和CHOOSE函数中的数列是按顺序一一对应的关系，即MATCH函数返回的数值是做为CHOOSE函数的参数值来算的。所以整个计算式先计算MATCH函数，查找F4单元格内数值在MATCH数组内所处位置为数字多少，再由CHOOSE函数根据这个数字在CHOOSE函数里的数列里查找对应的位置，并返还该数值到公式单元格，这样就完美解决了速度类测试的评分问题。如下图，F4格中10.7的数值在MATCH函数中大于或等于10.7的最小值是10.8，它在MATCH函数数组中是第16个位置，所以MATCH函数返回16这个数值，而整个公式相当于=CHOOSE(11,0,10,20,30,40…….100),即CHOOSE数

组中第16个数值，G4格中最终显示是78。

4.IF函数

作用为判断条件是否为真，如果为真则返回一个值，如果为假则返回另一个值。语法为：IF(条件,结果为真时返回的值，结果为假时返回的值)[3]，条件为表达式。例如：=IF(A2>B3,C6-D6,””)意思为：如果单元格A2里面的值大于B3里的值，则显示C6中的值减去D6中的值的结果，如果单元格A2里面的值小于或等于B3里的值则公式单元格内不显示任何字符（两个双引号中间无字符，意为空）

5.COUNTIF函数

作用为计算区域中满足给定条件的单元格的个数。可用来统计等地的人数。语法为：COUNTIF(查找区域，查找条件)[3]，查找区域可以为本工作表内，如=COUNTIF(B2:B65,67)即在本工作表内B列的第2行到第65行中查找数值为67的单元格个数并返还至公式格;也可以在其他工作表中查询，如=COUNTIF(‘1年级男生’!P3:P34,”优秀”)，意为在 “1年级男生”这个工作表的P列第3行至第34行中查询为“优秀”字符的单元格的个数并返还到公式格，其中请注意引用格式：单引号+工作表名称+单引号+!+区域。查找条件可以为数字、表达式或文本，如：12、”>=21”、”优秀”等。需注意的是表达式或文本（包括数字型文本）需要写在英文下的双引号内。例如：=countif(b2:b45,”优秀”)即返回b2到b45之间所有是“优秀”的个数。

二、在掌握以上五个函数的基础上，我们可以开始构建数据分析表了。

既然我们要用函数自动判断，那么就必须先建立一个评分标准表做为基础判断依据。国家学生体质健康标准（2014年修订版）是最新的评判标准，我们只要复制其中的数据在一个表格中即可。需特别注意的是，判别低于0分与超过100分的数据（如加分项目）如何给函数制定判断依据？个人解决方案是在0分之下和100分之上再分别设立一个远远小于0分或远远大于100分的评判标准，如右图。EXCEL中各工作表间可以相互借用数据，且《标准》中评分是分年级与男女的，所以评分标准表、年级或班级最好按照男女进行分别建表。

我们以小学五年级男生为例，小学五年级需要上报的体测项目为：BMI（体重指数=体重/身高的平方）、肺活量、50米跑、坐位体前屈、跳绳、仰卧起坐、50米X8往返跑共7个上报数据，我们需要先算出每项的百分制得分，然后分别乘以它们的权重再相加，然后加上附加分，才是我们需要的学生的整体分数。其中BMI、肺活量、坐位体前屈、仰卧起坐都可以直接用LOOKUP函数解决评分;50米跑与50米X8往返跑可以用函数CHOOSE(MATCH(,{...},-1),...)嵌套解决;跳绳项目由于有加分项，不可以直接将用LOOKUP函数算的得分乘以权重，而要用分成两部分算，先用LOOKUP函数算出对应原始得分，然后用IF函数判断：一列为百分制分数，如果学生成绩对照得分表的得分>100分直接返回100，否则返回原始得分；一列为加分数，如果学生成绩对照得分表的得分>100分则返回（原始得分-100），否则返回0，这样就能解决加分项目的得分计算问题了。切记所有的评判标准是分男女的，所以每个班级必须分男女做出两个评分表。完整公式例如下图：

最后，当我们把几个班级的测试成绩分男女输入进上表时，该班级的男（女）生综合成绩与等地就实时的显示出来，下面我们还需要对这些数据进行更进一步的统计分析，利用COUNTIF函数统计出各等地的数量，就可以利用数量进行四率的计算了。公式见右图。

如上，中学和大学只需要对照《标准》中的评分标准进行相应替换即可。该函数公式不只在OFFICE的EXCEL中可以使用，在常用的WPS的表格文件中也可以使用，只要有电脑，有基础的办公软件，只需学习简单的几个表格和函数，我们基层体育教师就可以随时掌握学生的体测数据情况，并及时进行反馈或改进教学方案，能极大的提高工作效率，何乐而不为呢？

参考文献：

[1]教育部关于印发《学生体质健康监测评价办法》等三个文件的通知(教体艺〔2014〕3号)

[2]教育部关于印发《国家学生体质健康标准（2014年修订）》的通知(教体艺〔2014〕5号)

[3]office2007，WPS帮助文件