以下由評委填寫

淺析用EXCEL函數(shù)制作國家體測數(shù)據(jù)的統(tǒng)計分析表

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

關鍵字：excel，函數(shù)，國家體質健康測試數(shù)據(jù)，統(tǒng)計分析

國家體質健康測試是我國為建立健全學生體質健康監(jiān)測評價機制，激勵學生積極參加身體鍛煉所制定的一項長期政策。每年全國所有學校都將對學生進行體質健康測試并將數(shù)據(jù)輸入學生體質健康網(wǎng)（http://004km.cn/），網(wǎng)站將對數(shù)據(jù)進行統(tǒng)一分析并反饋在網(wǎng)站上。在這一過程中，為了落實《國務院辦公廳轉發(fā)教育部等部門關于進一步加強學校體育工作若干意見的通知》(國辦發(fā)〔2012〕53號)和《教育部關于印發(fā)〈學生體質健康監(jiān)測評價辦法〉等三個文件的通知》(教體藝〔2014〕3號)有關要求[1][2]，基層體育教師需明確各自年級、班級的四率（優(yōu)秀率、良好率、及格率、不及格率）及相關的排列，由于數(shù)據(jù)多、計算量大且數(shù)據(jù)的動態(tài)變動，使沒有專用工具的體育老師的工作量大大增加。辦公軟件office中的EXCEL是一個基礎的表格軟件，本文利用EXCEL中的一些函數(shù)可以實現(xiàn)簡單的數(shù)據(jù)分析，滿足廣大體育教師的基礎工作需求。

一、我們先了解幾個需要用到的基本函數(shù)：

1.LOOKUP函數(shù)

作用是在單行或單列區(qū)域（向量）中查找某一數(shù)值然后返回某行或某列的數(shù)值。語法為lookup（查找的數(shù)值，查找對比的數(shù)據(jù)區(qū)域，返回的數(shù)據(jù)區(qū)域）[3]，需要注意的是查找對比的區(qū)域數(shù)據(jù)必須按照從小到大、A~Z、假~真進行順序排列，否則函數(shù)將不能返回正常數(shù)值。

具體應用舉例：如左圖，為了在G4格顯示F4格測試成績的相應評分，則只要在G4格中輸入=lookup（F4,B4:B25,A4:A25)，意義為將F4格中的數(shù)據(jù)在區(qū)域B4到B25的數(shù)據(jù)中進行查找，找到相同或區(qū)間后將區(qū)域A4到A25中相對應的數(shù)值（同列）返回到公式所在的位置。圖中左側A列B列為評分標準，如F4中的1125對照B列中1180>1125>1120,則公式會返回較小數(shù)值的A列對應值，即1120所對應的74，并返回到G4格中。

由于lookup函數(shù)的局限性，在速度類的測試中，如右圖：例如10.7秒的數(shù)據(jù)查找后10.6<10.7<10.8,用lookup返回的是較小數(shù)值10.6對應的B列數(shù)據(jù)80分，但根據(jù)實際情況10.7秒是慢于10.6秒的，實際應該返回10.8秒所對應的78分。此時就需要用到函數(shù)MATCH函數(shù)與CHOOSE函數(shù)配合來解決速度類的數(shù)值分析。

2.CHOOSE函數(shù)

作用是按照參數(shù)值返回數(shù)值列表中的數(shù)值。其中參數(shù)值為1到29之間的數(shù)字，數(shù)值最多29個。語法為：CHOOSE（參數(shù)值，數(shù)值1，數(shù)值2，數(shù)值3......數(shù)值29）[3]，例如，=CHOOSE（5，21，13，5，20，38，44，87，5）顯示的是38，即5后面的第五個數(shù)字。需要說明的是數(shù)值也可以是區(qū)域，這就為了綜合列式打下基礎。比如= CHOOSE(2,A1:A10,B1:B10,C1:C10)相當于在公式格中返回B1:B10。

3.MATCH函數(shù)

作用是返回在指定方式下與指定數(shù)組匹配的數(shù)組中元素的相應位置。

語法是：MATCH(查找值,{要查找的數(shù)組},查找類型)[3]，其中需要注意的是查找的數(shù)組排列必須與查找類型相對應。如果查找類型為 1，則函數(shù)MATCH查找小于或等于查找值的最大數(shù)值，要查找的數(shù)組必須按 升序 排列：如...、-2、-1、0、1、2、...、A-Z等； 如果查找類型為0，則函數(shù)MATCH查找等于查找值的第一個數(shù)值，查找的數(shù)組可以按任何順序排列；如果查找類型為-1，則函數(shù)MATCH查找大于或等于查找值的最小數(shù)值，要查找的數(shù)組必須按降序 排列：如Z-A、...、2、1、0、-1、-2、...等；如果省略查找類型，則自動設為 1。

例如：=MATCH(10.7,{11,10.8,10.6,10},-1),則顯示的是2，即10.7在數(shù)組中有11，10.8兩個數(shù)值大于它，而10.8在這兩個數(shù)中最小，則函數(shù)返回10.8在數(shù)組中處于的位置2。

MATCH函數(shù)可以返回數(shù)值在數(shù)組中的位置，而CHOOSE函數(shù)可以利用這個位置返回對應的數(shù)值，這樣兩個函數(shù)組合起來就可以計算出速度型的成績所對應的分數(shù)。如下圖F4內的成績對應的G4格中分數(shù)公式為：

=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函數(shù)中的數(shù)組中的數(shù)值和CHOOSE函數(shù)中的數(shù)列是按順序一一對應的關系，即MATCH函數(shù)返回的數(shù)值是做為CHOOSE函數(shù)的參數(shù)值來算的。所以整個計算式先計算MATCH函數(shù)，查找F4單元格內數(shù)值在MATCH數(shù)組內所處位置為數(shù)字多少，再由CHOOSE函數(shù)根據(jù)這個數(shù)字在CHOOSE函數(shù)里的數(shù)列里查找對應的位置，并返還該數(shù)值到公式單元格，這樣就完美解決了速度類測試的評分問題。如下圖，F(xiàn)4格中10.7的數(shù)值在MATCH函數(shù)中大于或等于10.7的最小值是10.8，它在MATCH函數(shù)數(shù)組中是第16個位置，所以MATCH函數(shù)返回16這個數(shù)值，而整個公式相當于=CHOOSE(11,0,10,20,30,40…….100),即CHOOSE數(shù)

組中第16個數(shù)值，G4格中最終顯示是78。

4.IF函數(shù)

作用為判斷條件是否為真，如果為真則返回一個值，如果為假則返回另一個值。語法為：IF(條件,結果為真時返回的值，結果為假時返回的值)[3]，條件為表達式。例如：=IF(A2>B3,C6-D6,””)意思為：如果單元格A2里面的值大于B3里的值，則顯示C6中的值減去D6中的值的結果，如果單元格A2里面的值小于或等于B3里的值則公式單元格內不顯示任何字符（兩個雙引號中間無字符，意為空）

5.COUNTIF函數(shù)

作用為計算區(qū)域中滿足給定條件的單元格的個數(shù)?？捎脕斫y(tǒng)計等地的人數(shù)。語法為：COUNTIF(查找區(qū)域，查找條件)[3]，查找區(qū)域可以為本工作表內，如=COUNTIF(B2:B65,67)即在本工作表內B列的第2行到第65行中查找數(shù)值為67的單元格個數(shù)并返還至公式格;也可以在其他工作表中查詢，如=COUNTIF(‘1年級男生’!P3:P34,”優(yōu)秀”)，意為在 “1年級男生”這個工作表的P列第3行至第34行中查詢?yōu)椤皟?yōu)秀”字符的單元格的個數(shù)并返還到公式格，其中請注意引用格式：單引號+工作表名稱+單引號+!+區(qū)域。查找條件可以為數(shù)字、表達式或文本，如：12、”>=21”、”優(yōu)秀”等。需注意的是表達式或文本（包括數(shù)字型文本）需要寫在英文下的雙引號內。例如：=countif(b2:b45,”優(yōu)秀”)即返回b2到b45之間所有是“優(yōu)秀”的個數(shù)。

二、在掌握以上五個函數(shù)的基礎上，我們可以開始構建數(shù)據(jù)分析表了。

既然我們要用函數(shù)自動判斷，那么就必須先建立一個評分標準表做為基礎判斷依據(jù)。國家學生體質健康標準（2014年修訂版）是最新的評判標準，我們只要復制其中的數(shù)據(jù)在一個表格中即可。需特別注意的是，判別低于0分與超過100分的數(shù)據(jù)（如加分項目）如何給函數(shù)制定判斷依據(jù)？個人解決方案是在0分之下和100分之上再分別設立一個遠遠小于0分或遠遠大于100分的評判標準，如右圖。EXCEL中各工作表間可以相互借用數(shù)據(jù)，且《標準》中評分是分年級與男女的，所以評分標準表、年級或班級最好按照男女進行分別建表。

我們以小學五年級男生為例，小學五年級需要上報的體測項目為：BMI（體重指數(shù)=體重/身高的平方）、肺活量、50米跑、坐位體前屈、跳繩、仰臥起坐、50米X8往返跑共7個上報數(shù)據(jù)，我們需要先算出每項的百分制得分，然后分別乘以它們的權重再相加，然后加上附加分，才是我們需要的學生的整體分數(shù)。其中BMI、肺活量、坐位體前屈、仰臥起坐都可以直接用LOOKUP函數(shù)解決評分;50米跑與50米X8往返跑可以用函數(shù)CHOOSE(MATCH(,{...},-1),...)嵌套解決;跳繩項目由于有加分項，不可以直接將用LOOKUP函數(shù)算的得分乘以權重，而要用分成兩部分算，先用LOOKUP函數(shù)算出對應原始得分，然后用IF函數(shù)判斷：一列為百分制分數(shù)，如果學生成績對照得分表的得分>100分直接返回100，否則返回原始得分；一列為加分數(shù)，如果學生成績對照得分表的得分>100分則返回（原始得分-100），否則返回0，這樣就能解決加分項目的得分計算問題了。切記所有的評判標準是分男女的，所以每個班級必須分男女做出兩個評分表。完整公式例如下圖：

最后，當我們把幾個班級的測試成績分男女輸入進上表時，該班級的男（女）生綜合成績與等地就實時的顯示出來，下面我們還需要對這些數(shù)據(jù)進行更進一步的統(tǒng)計分析，利用COUNTIF函數(shù)統(tǒng)計出各等地的數(shù)量，就可以利用數(shù)量進行四率的計算了。公式見右圖。

如上，中學和大學只需要對照《標準》中的評分標準進行相應替換即可。該函數(shù)公式不只在OFFICE的EXCEL中可以使用，在常用的WPS的表格文件中也可以使用，只要有電腦，有基礎的辦公軟件，只需學習簡單的幾個表格和函數(shù)，我們基層體育教師就可以隨時掌握學生的體測數(shù)據(jù)情況，并及時進行反饋或改進教學方案，能極大的提高工作效率，何樂而不為呢？

參考文獻：

[1]教育部關于印發(fā)《學生體質健康監(jiān)測評價辦法》等三個文件的通知(教體藝〔2014〕3號)

[2]教育部關于印發(fā)《國家學生體質健康標準（2014年修訂）》的通知(教體藝〔2014〕5號)

[3]office2007，WPS幫助文件