以下由評委填寫

淺析用EXCEL函數制作國家體測數據的統計分析表

摘要：以《國家學生體質健康標準(2014年修訂)》（以下簡稱《標準》）為基礎評判依據，利用EXCEL中的函數LOOKUP、CHOOSE、MATCH、COUNTIF、IF及《標準》中的權重等制作簡略數據分析表，使廣大基礎體育教師在有EXCEL軟件的情況下可以簡單快速的進行數據分析，增加工作效率。

關鍵字：excel，函數，國家體質健康測試數據，統計分析

國家體質健康測試是我國為建立健全學生體質健康監測評價機制，激勵學生積極參加身體鍛煉所制定的一項長期政策。每年全國所有學校都將對學生進行體質健康測試并將數據輸入學生體質健康網（http://www.tmdps.cn/），網站將對數據進行統一分析并反饋在網站上。在這一過程中，為了落實《國務院辦公廳轉發教育部等部門關于進一步加強學校體育工作若干意見的通知》(國辦發〔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幫助文件