基于函数嵌套技术学生体质健康评价模板程序的设计

杨守民，于晴茹



基于函数嵌套技术学生体质健康评价模板程序的设计

杨守民1，于晴茹2

1.河南科技学院新科学院，河南 新乡，453003；2.长垣烹饪职业技术学院，河南 长垣，453400。

为提高学生体质健康测试与评价的工作效率，减少人工查表评价所带来的误差，确保学生体质健康评价的准确性。运用文献法、系统分析法、函数编程法、实验法，以函数嵌套技术理论为基础，研究并设计出了大学生体质健康评价模板程序。该模板程序整合了教育部学生体质健康测试所需上报数据，实现了大学生不同年级、不同性别体质健康单项评价、综合评价以及评价结果统计的智能化。该程序具有操作简便、批量处理、性能可靠等优点，有效降低了学生体质健康评价的工作强度，提高了工作效率，降低了工作误差，保证了学生体质健康评价数据的准确性，为高校学生体质健康测试与评价等基层工作提供了帮助。

函数嵌套；体质健康评价；程序设计

2014年教育部根据我国学生体质健康状况，对《国家学生体质健康标准》进行了修订。高等学校学生体质健康标准测试项目依然保留了原来的身高、体重、肺活量、800/1000m、50m、仰卧起坐/引体向上、坐位体前屈、立定跳远等项目[1]。但是，评分标准有所调整，由原来的高校分性别不分年级变成了分性别分年级，即分性别1、2年级一个标准，3、4年级一个标准。同时，为鼓励学生发展力量和耐力素质，实行了力量和耐力项目加分制。这看似简单的改革，在测试学生数量庞大的实况下，使原本单调、繁琐、工作量大、容易出错的学生体质健康查表评定工作难上加难。

Microsoft Office Excel是教师们常用的办公软件，其功能强大，使用方便。Microsoft Office Excel自带一些功能函数，通过多函数的嵌套使用，设计学生体质健康评价模板程序，实现大批量学生体质健康自动评价，不仅能够降低教师们的工作强度，提高工作效率，还能降低人工查表评价所带来的误差，提高学生体质健康评价的准确性。

1 理论基础

1.1 多函数嵌套

多函数嵌套是指在处理某种复杂问题时，一个函数的单独使用无法有效解决问题，用户需要将某个函数作为其他函数的参数使用从而形成一个“复合”函数，这样的技术称为多函数嵌套[2]。多个函数嵌套后形成的“复合”函数称为嵌套函数。

1.2 VLOOKUP函数

Excel工作表中的基本函数有11类，在这11类函数中，其中有1类函数为“查找与引用”函数，常用的查找函数就是VLOOKUP函数[3]。其功能是在表格数组的首列中查找指定值，并返回指定值当前行其他列的值；其语法结构是VLOOKUP（look_value,table_array,col_Index _num, range_ lookup）。其中参数look_value表示在表格数组首列中所需要查找的数值，若look_value小于table_array首列中最小值，VLOOKUP返回错误值#N/A；table_array表示两列或多列数据。其首列值应是look_value所查找的文本、数字或逻辑值；col_index_num表示table_array中待返回值的序列号，col_index_num为多少就返回第多少列的数值；range_ lookup为逻辑值，用来设定VLOOKUP是近似匹配查找还是精确匹配查找，设置为TRUE时，返回精确匹配或次值匹配的对应值，设置为FALSE时返回精确匹配的对应值。

在运用该函数时，应该注意的是：（1）文本查找的一致性。查找table_array中首列值为文本时，要确保table_array首列数据没有空格、引号和其他不一致或非打印字符等。否则，VLOOKUP将可能返回错值；（2）数值、日期格式的对应性。当look_value为数字或日期格式时，table_array的首列数据也要确保是数字或日期格式。否则，VLOOKUP将可能返回错值；（3）查找的概括性。当range_lookup的逻辑值为FALSE，且look_value为文本数值时，可以在查找中使用通配符、问号和星号，问号代表任意单个字符，星号代表任意序列字符。

2 设计思路与技术分析

2.1 设计思路

日常我们进行学生体质健康评价，其步骤是首先根据学生某项运动指标的最初成绩，依照其性别和所在的年级，在《国家学生体质健康标准》中查找其最初运动成绩所对应的标准成绩和等级，每单项运动指标的最初成绩所对应的标准成绩和等级全部查出来后，再根据每项运动指标在总成绩中所占的比重权重后相加，得出该学生的最终评价成绩和等级。

根据人工评价的操作方法步骤，运用计算机函数，实现由学生单项指标最初运动成绩对其标准成绩和等级的自动查找，并在各单项运动指标标准成绩和等级查找完毕后，实现学生最终评价成绩和等级的自动计算和评定，以及总体样本的最终评价等级结果的自动统计。

2.2 VLOOKUP函数应用分析

在模板程序设计中，我们要实现的是通过学生单项运动的原始成绩在《国家学生体质健康标准》中查找其对应的标准成绩和等级，而VLOOKUP函数的功能是在表格数组的首列中查找指定值，并返回指定值当前行其他列的值，此功能正好与研究的目的相契合[4]。那么，VLOOKUP函数中参数look_value就是学生单行运动的原始成绩，而参数table_array就是《国家学生体质健康标准》数据表，所“返回指定值当前行其他列的值”就是学生单项运动原始成绩所对应的标准成绩和等级。实现查找、赋值的过程也就是按照《国家学生体质健康标准》给学生运动成绩评分、评价的过程。

3 模板程序设计

3.1 评价模板程序设计

根据需要，整个评价模板程序分为后台评价标准表和前台评价模板两部分。前台评价模板部分又分为单项评价模块、总评模块和统计模块。

3.1.1 后台评价标准表设计 根据《国家学生体质健康标准》内容，高等学校学生体质健康评价标准共有8个方面的内容：男生BMI标准、女生BMI标准、大1-2男生标准、大1-2女生标准、大3-4男生标准、大3-4女生标准、男生加分标准和女生加分标准。根据需要，分别将这8个评价标准用8个Excel表来设计。其中，男生BMI标准表和女生BMI标准表表头设计相同，详见表1；大1-2男生标准表、大1-2女生标准表、大3-4男生标准表和大3-4女生标准表的表头设计相同，详见表2；男生加分标准表和女生加分标准表的表头设计相同，详见表3。

表1 男（女）生BMI标准表表头设计

表2 不同年级男（女）生单项标准表表头设计

表3 男（女）加分项标准表表头设计

3.1.2 前台评价模板设计 根据教育部学生体质健康数据上报模板要求和获得评价数据信息的需要，可将前台评价模板设计成四个模块，即学生基本信息模块、单项评价模块、总评及统计模块，其表头设计分别详见表4、表5和表6。

表4 学生基本信息模块表头设计

表5 单项评价模块表头设计

表6 总评及统计模块表头设计

（1）单项评价模块函数设计

根据后台评价标准表设计的种类差异，需要采用多函数嵌套来设计各单项评价得分和等级。

第一类：“BMI得分”和“BMI等级”。因为其标准表只分性别不分年级，故其函数只需要判断性别，不需要判断年级，函数设计也相对简单。设计嵌套函数所涉及到的函数有IF函数、VLOOKUP函数和ROUND函数[5]。“BMI得分”函数设计为T2=IF（G2=1, VLOOKUP（ROUND（K2/J2/J2*10000,1）,男生BMI!A$2:C$8,2,TRUE）, VLOOKUP（ROUND（K2/J2/J2*10000,1）,女生BMI!A$2: C$8,2,TRUE））。表示当G2单元格的值为1时（男生代码为1），在“男生BMI标准表”中查找BMI为ROUND（K2/J2/J2*10000,1）的值，并在T2单元格中返回相对应的第2列的数值；否则在“女生BMI标准表”中查找BMI为ROUND（K2/J2/ J2*10000,1）的值，并在T2单元格中返回相对应的第2列的数值。“BMI等级”的函数设计与“BMI得分”函数设计相同，只是把对应值的返回列数设定为3就可以了。

第二类：“肺活量得分”、“肺活量等级”、“50m得分”、“50m等级”、“立定跳远得分”、“立定跳远等级”、“坐位体前屈得分”、“坐位体前屈等级”、“800m或1000m得分”、“800m或1000m等级”、“1min仰卧起坐或引体向上得分”和“1min仰卧起坐或引体向上等级”。由于此类标准表不仅分性别还分年级，所以其函数既需要判断性别又需要判断年级，函数设计相对繁杂。以“1min仰卧起坐或引体向上得分”函数为例设计此类嵌套函数。这里同样运用了IF函数和VLOOKUP函数的嵌套。函数设计为AF2=IF（A2<42.5, IF（G2=1,VLOOKUP（S2,大1-2男生标准! D$2:F$22,2, TRUE）,VLOOKUP（R2,大1-2女生标准! D$2:F$22,2, TRUE））, IF（G2=1,VLOOKUP（S2,大3-4男生标准! D$2: F$22,2, TRUE）,VLOOKUP（R2,大3-4女生标准!D$2: F$22,2,TRUE）））。此函数表示当A2单元格的数值小于42.5时（1、2年级的代码是41和42），G2单元格的值为1时（男生代码为1），在“大1-2男生标准表”中查找“引体向上”个数为S2的值，并在AF2单元格中返回相对应的第2列的数值；否则在“大1-2女生标准”中查找“1min仰卧起坐”数量为R2的值，并在AF2单元格中返回相对应的第2列的数值；当A2单元格的数值大于等于42.5时（3、4年级的代码是43和44），G2单元格的值为１时（男生代码为1），在“大3-4男生标准表”中查找“引体向上”个数为S2的值，并在AF2单元格中返回相对应的第２列的数值；否则在“大3-4女生标准”中查找“1min仰卧起坐”数量为R2的值，并在AF2单元格中返回相对应的第2列的数值。同样，“1min仰卧起坐或引体向上等级”函数的设计与“1min仰卧起坐或引体向上得分”函数设计上只有返回相对应的列数不同，即设定为第3列就行了。

第二类中其他单项得分和等级函数的设计句法、结构与“1min仰卧起坐或引体向上”得分和等级函数相同，不再赘述。

（2）总评模块函数设计

此模块函数，语法、结构相对简单。以“1min仰卧起坐加分”函数为例说明其函数设计。该函数设计为AH2=VLOOKUP（IF（A2<42.5,IF（R2-56>0,R2-56,0）,IF（R2-57>0, R2-57,0））, 女生加分!A$2:C$12,3,TRUE）。该函数同样是VLOOKUP函数和IF函数的嵌套。它表示A2单元格的数值小于42.5时（1、2年级的代码是41和42），如果R2单元格的数值大于1、2年级1min仰卧起坐的最高标准56，就在“女生加分”标准表中查找R2-56的数值并返回其相对应的第3列的数值，如果A2单元格的数值大于等于42.5时（3、4年级的代码是43和44），如果R2单元格的数值大于3、4年级1min仰卧起坐的最高标准57，就在“女生加分”标准表中查找R2-57的数值并返回其相对应的第3列的数值。

其他“引体向上加分”、“800m加分”、“1000m加分”函数与“1min仰卧起坐加分”函数设计相同，不在赘述。

“总得分”用SUM函数，设计为AL2=SUM（T2*0.15, V2*0.15,X2*0.2,Z2*0.1,AB2*0.1, AD2*0.2, AF2*0.1,AH2, AI2,AJ2,AK2）；

“等级”函数仍用VLOOKUP函数，设计为AM2=VLOOKUP（AL2,总得分等级!A$1:B$9,2, TRUE）。

（3）统计模块函数设计

评价完毕后，要进行一些常规性统计，来掌握参加测试学生的整体评价情况。如总人数和不同评价格次的人数及比例。参加测试的总人数和不同格次的人数用统计函数用COUNTIF。“总人数”函数设计为AN2=COUNTA（AM2:AM20000）；“不及格人数”函数设计为AO2=COUNTIF（AM2:AM20000,“不及格”；“及格以上人数”函数设计为AQ2=COUNTIF（AM2:AM20000,“及格”）+COUNTIF（AM2:AM20000,“良好”）+COUNTIF（AM2: AM20000,“优秀”）等；比率函数用相除公式就可以了如“不及格率”用公式AP2=AO2/AN2；“及格率”用公式AR2=AQ2/AN2等。

4 结论与建议

2014年教育部对《国家学生体质健康标准》进行了修订，将原来高校学生体质健康标准分成了两个年龄段，同时增加了加分项目标准，反映到实际工作中，教师查表评价学生体质健康的工作难度和错误率都将增加。鉴于此，分析了人工查表评价的工作程序和EXCEL中查找函数VLOOKUP的功能，发现运用VLOOKUP和IF等函数嵌套设计学生体质健康评价模板程序，实现学生体质健康自动评价的可行性。通过对国家学生体质健康各项目标准的分析，设计了后台各项目评价标准表；同时根据教育部学生体质健康数据上报模板设计了前台学生体质健康评价模板；并通过各种函数的嵌套运用，建立了前台学生体质健康评价模板与后台各项目评价标准表的函数关联，实现了学生体质健康评价模板程序的整体设计。学生体质健康评价模板程序的研制，对降低教师在学生体质健康评价时的工作强度，提高工作效率，减少工作误差，开展学生体质健康测试评价工作具有重要的现实意义。

[1] 学生体质健康网.教育部关于印发《国家学生体质健康标准（2014年修订）》[R/OL].http://www.csh.edu.cn，2014-07-28.

[2] EXCEL HOME.EXCEL 2013函数与公式应用大全[M].北京：北京大学出版社，2016.04：50.

[3] 赛贝尔资讯.Excel函数与公式速查手册[M].北京：清华大学出版社，2015：346.

[4] 杨守民，纪铭霞.VLOOKUP函数在学生体质健康测试数据整合中的应用研究[J].体育科技文献通报，2014，22（05）：23~24.

[5] 李子明.运用EXCEL实现对《国家学生体质健康标准》测试样本数据的统计分析与查询[J].首都师范大学学报（自然科学版），2011，32（01）：90~93.

The Design of the Template-Program for Students’ Physical Health Assessment Based on Function Nesting Technology

YANG Shoumin1, YU Qingru2

1.Xinke College of HIST, Xinxiang Henan, 453003, China; 2.Changyuan Vocational College of Cuisine, Changyuan Henan, 453400, China.

In order to improve the work efficiency of the students' physical health testing and assessment, reduce the error caused by the assessment of the artificial table, and ensure the accuracy of the students' physical health assessment. By using the literature method, system analysis, function programming and experimental method, the template-program of the physical health assessment of college students is designed on the basis of the theory of function nesting. The program integrates the data required for the physical health test of the students of the Ministry of Education, and realizes the intelligent assessment of the physical health of different grades, different sexes, the comprehensive assessment and the statistics of the assessment results. The program has the advantages of simple operation, batch processing and reliable performance, which can effectively reduce the work intensity of students' physical health assessment, improve the work efficiency, reduce the work error, and ensure the accuracy of the students' physical health assessment data. It provides help for the basic work such as physical health test and assessment of college students.

Function Nesting; Physical Health Assessment; Programming

G804.49

A

1007―6891（2018）06―0037―03

10.13932/j.cnki.sctykx.2018.06.10

2018-08-30

2018-09-21

2018年河南省社科联、经团联调研项目：基于函数嵌套技术大学生体质健康评价模板程序的设计与实现（项目编号：SKL—2018—2822）；2019年度河南省高等学校重点科研项目：基于函数嵌套技术大学生体质健康评价模板程序的设计与实现（项目编号：19B890003）。