基于函數嵌套技術學生體質健康評價模板程序的設計

楊守民，于晴茹

?

基于函數嵌套技術學生體質健康評價模板程序的設計

楊守民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）。