楊守民，于晴茹

?

基于函數(shù)嵌套技術(shù)學生體質(zhì)健康評價模板程序的設(shè)計

楊守民1，于晴茹2

1.河南科技學院新科學院，河南 新鄉(xiāng)，453003；2.長垣烹飪職業(yè)技術(shù)學院，河南 長垣，453400。

為提高學生體質(zhì)健康測試與評價的工作效率，減少人工查表評價所帶來的誤差，確保學生體質(zhì)健康評價的準確性。運用文獻法、系統(tǒng)分析法、函數(shù)編程法、實驗法，以函數(shù)嵌套技術(shù)理論為基礎(chǔ)，研究并設(shè)計出了大學生體質(zhì)健康評價模板程序。該模板程序整合了教育部學生體質(zhì)健康測試所需上報數(shù)據(jù)，實現(xiàn)了大學生不同年級、不同性別體質(zhì)健康單項評價、綜合評價以及評價結(jié)果統(tǒng)計的智能化。該程序具有操作簡便、批量處理、性能可靠等優(yōu)點，有效降低了學生體質(zhì)健康評價的工作強度，提高了工作效率，降低了工作誤差，保證了學生體質(zhì)健康評價數(shù)據(jù)的準確性，為高校學生體質(zhì)健康測試與評價等基層工作提供了幫助。

函數(shù)嵌套；體質(zhì)健康評價；程序設(shè)計

2014年教育部根據(jù)我國學生體質(zhì)健康狀況，對《國家學生體質(zhì)健康標準》進行了修訂。高等學校學生體質(zhì)健康標準測試項目依然保留了原來的身高、體重、肺活量、800/1000m、50m、仰臥起坐/引體向上、坐位體前屈、立定跳遠等項目[1]。但是，評分標準有所調(diào)整，由原來的高校分性別不分年級變成了分性別分年級，即分性別1、2年級一個標準，3、4年級一個標準。同時，為鼓勵學生發(fā)展力量和耐力素質(zhì)，實行了力量和耐力項目加分制。這看似簡單的改革，在測試學生數(shù)量龐大的實況下，使原本單調(diào)、繁瑣、工作量大、容易出錯的學生體質(zhì)健康查表評定工作難上加難。

Microsoft Office Excel是教師們常用的辦公軟件，其功能強大，使用方便。Microsoft Office Excel自帶一些功能函數(shù)，通過多函數(shù)的嵌套使用，設(shè)計學生體質(zhì)健康評價模板程序，實現(xiàn)大批量學生體質(zhì)健康自動評價，不僅能夠降低教師們的工作強度，提高工作效率，還能降低人工查表評價所帶來的誤差，提高學生體質(zhì)健康評價的準確性。

1 理論基礎(chǔ)

1.1 多函數(shù)嵌套

多函數(shù)嵌套是指在處理某種復(fù)雜問題時，一個函數(shù)的單獨使用無法有效解決問題，用戶需要將某個函數(shù)作為其他函數(shù)的參數(shù)使用從而形成一個“復(fù)合”函數(shù)，這樣的技術(shù)稱為多函數(shù)嵌套[2]。多個函數(shù)嵌套后形成的“復(fù)合”函數(shù)稱為嵌套函數(shù)。

1.2 VLOOKUP函數(shù)

Excel工作表中的基本函數(shù)有11類，在這11類函數(shù)中，其中有1類函數(shù)為“查找與引用”函數(shù)，常用的查找函數(shù)就是VLOOKUP函數(shù)[3]。其功能是在表格數(shù)組的首列中查找指定值，并返回指定值當前行其他列的值；其語法結(jié)構(gòu)是VLOOKUP（look_value,table_array,col_Index _num, range_ lookup）。其中參數(shù)look_value表示在表格數(shù)組首列中所需要查找的數(shù)值，若look_value小于table_array首列中最小值，VLOOKUP返回錯誤值#N/A；table_array表示兩列或多列數(shù)據(jù)。其首列值應(yīng)是look_value所查找的文本、數(shù)字或邏輯值；col_index_num表示table_array中待返回值的序列號，col_index_num為多少就返回第多少列的數(shù)值；range_ lookup為邏輯值，用來設(shè)定VLOOKUP是近似匹配查找還是精確匹配查找，設(shè)置為TRUE時，返回精確匹配或次值匹配的對應(yīng)值，設(shè)置為FALSE時返回精確匹配的對應(yīng)值。

在運用該函數(shù)時，應(yīng)該注意的是：（1）文本查找的一致性。查找table_array中首列值為文本時，要確保table_array首列數(shù)據(jù)沒有空格、引號和其他不一致或非打印字符等。否則，VLOOKUP將可能返回錯值；（2）數(shù)值、日期格式的對應(yīng)性。當look_value為數(shù)字或日期格式時，table_array的首列數(shù)據(jù)也要確保是數(shù)字或日期格式。否則，VLOOKUP將可能返回錯值；（3）查找的概括性。當range_lookup的邏輯值為FALSE，且look_value為文本數(shù)值時，可以在查找中使用通配符、問號和星號，問號代表任意單個字符，星號代表任意序列字符。

2 設(shè)計思路與技術(shù)分析

2.1 設(shè)計思路

日常我們進行學生體質(zhì)健康評價，其步驟是首先根據(jù)學生某項運動指標的最初成績，依照其性別和所在的年級，在《國家學生體質(zhì)健康標準》中查找其最初運動成績所對應(yīng)的標準成績和等級，每單項運動指標的最初成績所對應(yīng)的標準成績和等級全部查出來后，再根據(jù)每項運動指標在總成績中所占的比重權(quán)重后相加，得出該學生的最終評價成績和等級。

根據(jù)人工評價的操作方法步驟，運用計算機函數(shù)，實現(xiàn)由學生單項指標最初運動成績對其標準成績和等級的自動查找，并在各單項運動指標標準成績和等級查找完畢后，實現(xiàn)學生最終評價成績和等級的自動計算和評定，以及總體樣本的最終評價等級結(jié)果的自動統(tǒng)計。

2.2 VLOOKUP函數(shù)應(yīng)用分析

在模板程序設(shè)計中，我們要實現(xiàn)的是通過學生單項運動的原始成績在《國家學生體質(zhì)健康標準》中查找其對應(yīng)的標準成績和等級，而VLOOKUP函數(shù)的功能是在表格數(shù)組的首列中查找指定值，并返回指定值當前行其他列的值，此功能正好與研究的目的相契合[4]。那么，VLOOKUP函數(shù)中參數(shù)look_value就是學生單行運動的原始成績，而參數(shù)table_array就是《國家學生體質(zhì)健康標準》數(shù)據(jù)表，所“返回指定值當前行其他列的值”就是學生單項運動原始成績所對應(yīng)的標準成績和等級。實現(xiàn)查找、賦值的過程也就是按照《國家學生體質(zhì)健康標準》給學生運動成績評分、評價的過程。

3 模板程序設(shè)計

3.1 評價模板程序設(shè)計

根據(jù)需要，整個評價模板程序分為后臺評價標準表和前臺評價模板兩部分。前臺評價模板部分又分為單項評價模塊、總評模塊和統(tǒng)計模塊。

3.1.1 后臺評價標準表設(shè)計 根據(jù)《國家學生體質(zhì)健康標準》內(nèi)容，高等學校學生體質(zhì)健康評價標準共有8個方面的內(nèi)容：男生BMI標準、女生BMI標準、大1-2男生標準、大1-2女生標準、大3-4男生標準、大3-4女生標準、男生加分標準和女生加分標準。根據(jù)需要，分別將這8個評價標準用8個Excel表來設(shè)計。其中，男生BMI標準表和女生BMI標準表表頭設(shè)計相同，詳見表1；大1-2男生標準表、大1-2女生標準表、大3-4男生標準表和大3-4女生標準表的表頭設(shè)計相同，詳見表2；男生加分標準表和女生加分標準表的表頭設(shè)計相同，詳見表3。

表1 男（女）生BMI標準表表頭設(shè)計

表2 不同年級男（女）生單項標準表表頭設(shè)計

表3 男（女）加分項標準表表頭設(shè)計

3.1.2 前臺評價模板設(shè)計 根據(jù)教育部學生體質(zhì)健康數(shù)據(jù)上報模板要求和獲得評價數(shù)據(jù)信息的需要，可將前臺評價模板設(shè)計成四個模塊，即學生基本信息模塊、單項評價模塊、總評及統(tǒng)計模塊，其表頭設(shè)計分別詳見表4、表5和表6。

表4 學生基本信息模塊表頭設(shè)計

表5 單項評價模塊表頭設(shè)計

表6 總評及統(tǒng)計模塊表頭設(shè)計

（1）單項評價模塊函數(shù)設(shè)計

根據(jù)后臺評價標準表設(shè)計的種類差異，需要采用多函數(shù)嵌套來設(shè)計各單項評價得分和等級。

第一類：“BMI得分”和“BMI等級”。因為其標準表只分性別不分年級，故其函數(shù)只需要判斷性別，不需要判斷年級，函數(shù)設(shè)計也相對簡單。設(shè)計嵌套函數(shù)所涉及到的函數(shù)有IF函數(shù)、VLOOKUP函數(shù)和ROUND函數(shù)[5]?！癇MI得分”函數(shù)設(shè)計為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單元格中返回相對應(yīng)的第2列的數(shù)值；否則在“女生BMI標準表”中查找BMI為ROUND（K2/J2/ J2*10000,1）的值，并在T2單元格中返回相對應(yīng)的第2列的數(shù)值。“BMI等級”的函數(shù)設(shè)計與“BMI得分”函數(shù)設(shè)計相同，只是把對應(yīng)值的返回列數(shù)設(shè)定為3就可以了。

第二類：“肺活量得分”、“肺活量等級”、“50m得分”、“50m等級”、“立定跳遠得分”、“立定跳遠等級”、“坐位體前屈得分”、“坐位體前屈等級”、“800m或1000m得分”、“800m或1000m等級”、“1min仰臥起坐或引體向上得分”和“1min仰臥起坐或引體向上等級”。由于此類標準表不僅分性別還分年級，所以其函數(shù)既需要判斷性別又需要判斷年級，函數(shù)設(shè)計相對繁雜。以“1min仰臥起坐或引體向上得分”函數(shù)為例設(shè)計此類嵌套函數(shù)。這里同樣運用了IF函數(shù)和VLOOKUP函數(shù)的嵌套。函數(shù)設(shè)計為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）））。此函數(shù)表示當A2單元格的數(shù)值小于42.5時（1、2年級的代碼是41和42），G2單元格的值為1時（男生代碼為1），在“大1-2男生標準表”中查找“引體向上”個數(shù)為S2的值，并在AF2單元格中返回相對應(yīng)的第2列的數(shù)值；否則在“大1-2女生標準”中查找“1min仰臥起坐”數(shù)量為R2的值，并在AF2單元格中返回相對應(yīng)的第2列的數(shù)值；當A2單元格的數(shù)值大于等于42.5時（3、4年級的代碼是43和44），G2單元格的值為１時（男生代碼為1），在“大3-4男生標準表”中查找“引體向上”個數(shù)為S2的值，并在AF2單元格中返回相對應(yīng)的第２列的數(shù)值；否則在“大3-4女生標準”中查找“1min仰臥起坐”數(shù)量為R2的值，并在AF2單元格中返回相對應(yīng)的第2列的數(shù)值。同樣，“1min仰臥起坐或引體向上等級”函數(shù)的設(shè)計與“1min仰臥起坐或引體向上得分”函數(shù)設(shè)計上只有返回相對應(yīng)的列數(shù)不同，即設(shè)定為第3列就行了。

第二類中其他單項得分和等級函數(shù)的設(shè)計句法、結(jié)構(gòu)與“1min仰臥起坐或引體向上”得分和等級函數(shù)相同，不再贅述。

（2）總評模塊函數(shù)設(shè)計

此模塊函數(shù)，語法、結(jié)構(gòu)相對簡單。以“1min仰臥起坐加分”函數(shù)為例說明其函數(shù)設(shè)計。該函數(shù)設(shè)計為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）。該函數(shù)同樣是VLOOKUP函數(shù)和IF函數(shù)的嵌套。它表示A2單元格的數(shù)值小于42.5時（1、2年級的代碼是41和42），如果R2單元格的數(shù)值大于1、2年級1min仰臥起坐的最高標準56，就在“女生加分”標準表中查找R2-56的數(shù)值并返回其相對應(yīng)的第3列的數(shù)值，如果A2單元格的數(shù)值大于等于42.5時（3、4年級的代碼是43和44），如果R2單元格的數(shù)值大于3、4年級1min仰臥起坐的最高標準57，就在“女生加分”標準表中查找R2-57的數(shù)值并返回其相對應(yīng)的第3列的數(shù)值。

其他“引體向上加分”、“800m加分”、“1000m加分”函數(shù)與“1min仰臥起坐加分”函數(shù)設(shè)計相同，不在贅述。

“總得分”用SUM函數(shù)，設(shè)計為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）；

“等級”函數(shù)仍用VLOOKUP函數(shù)，設(shè)計為AM2=VLOOKUP（AL2,總得分等級!A$1:B$9,2, TRUE）。

（3）統(tǒng)計模塊函數(shù)設(shè)計

評價完畢后，要進行一些常規(guī)性統(tǒng)計，來掌握參加測試學生的整體評價情況。如總?cè)藬?shù)和不同評價格次的人數(shù)及比例。參加測試的總?cè)藬?shù)和不同格次的人數(shù)用統(tǒng)計函數(shù)用COUNTIF?！翱?cè)藬?shù)”函數(shù)設(shè)計為AN2=COUNTA（AM2:AM20000）；“不及格人數(shù)”函數(shù)設(shè)計為AO2=COUNTIF（AM2:AM20000,“不及格”；“及格以上人數(shù)”函數(shù)設(shè)計為AQ2=COUNTIF（AM2:AM20000,“及格”）+COUNTIF（AM2:AM20000,“良好”）+COUNTIF（AM2: AM20000,“優(yōu)秀”）等；比率函數(shù)用相除公式就可以了如“不及格率”用公式AP2=AO2/AN2；“及格率”用公式AR2=AQ2/AN2等。

4 結(jié)論與建議

2014年教育部對《國家學生體質(zhì)健康標準》進行了修訂，將原來高校學生體質(zhì)健康標準分成了兩個年齡段，同時增加了加分項目標準，反映到實際工作中，教師查表評價學生體質(zhì)健康的工作難度和錯誤率都將增加。鑒于此，分析了人工查表評價的工作程序和EXCEL中查找函數(shù)VLOOKUP的功能，發(fā)現(xiàn)運用VLOOKUP和IF等函數(shù)嵌套設(shè)計學生體質(zhì)健康評價模板程序，實現(xiàn)學生體質(zhì)健康自動評價的可行性。通過對國家學生體質(zhì)健康各項目標準的分析，設(shè)計了后臺各項目評價標準表；同時根據(jù)教育部學生體質(zhì)健康數(shù)據(jù)上報模板設(shè)計了前臺學生體質(zhì)健康評價模板；并通過各種函數(shù)的嵌套運用，建立了前臺學生體質(zhì)健康評價模板與后臺各項目評價標準表的函數(shù)關(guān)聯(lián)，實現(xiàn)了學生體質(zhì)健康評價模板程序的整體設(shè)計。學生體質(zhì)健康評價模板程序的研制，對降低教師在學生體質(zhì)健康評價時的工作強度，提高工作效率，減少工作誤差，開展學生體質(zhì)健康測試評價工作具有重要的現(xiàn)實意義。

[1] 學生體質(zhì)健康網(wǎng).教育部關(guān)于印發(fā)《國家學生體質(zhì)健康標準（2014年修訂）》[R/OL].http://www.csh.edu.cn，2014-07-28.

[2] EXCEL HOME.EXCEL 2013函數(shù)與公式應(yīng)用大全[M].北京：北京大學出版社，2016.04：50.

[3] 賽貝爾資訊.Excel函數(shù)與公式速查手冊[M].北京：清華大學出版社，2015：346.

[4] 楊守民，紀銘霞.VLOOKUP函數(shù)在學生體質(zhì)健康測試數(shù)據(jù)整合中的應(yīng)用研究[J].體育科技文獻通報，2014，22（05）：23~24.

[5] 李子明.運用EXCEL實現(xiàn)對《國家學生體質(zhì)健康標準》測試樣本數(shù)據(jù)的統(tǒng)計分析與查詢[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年河南省社科聯(lián)、經(jīng)團聯(lián)調(diào)研項目：基于函數(shù)嵌套技術(shù)大學生體質(zhì)健康評價模板程序的設(shè)計與實現(xiàn)（項目編號：SKL—2018—2822）；2019年度河南省高等學校重點科研項目：基于函數(shù)嵌套技術(shù)大學生體質(zhì)健康評價模板程序的設(shè)計與實現(xiàn)（項目編號：19B890003）。