以下由評(píng)委填寫

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

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

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

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

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

1.LOOKUP函數(shù)

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

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

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

2.CHOOSE函數(shù)

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

3.MATCH函數(shù)

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

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

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

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

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

4.IF函數(shù)

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

5.COUNTIF函數(shù)

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

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

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

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

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

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

參考文獻(xiàn)：

[1]教育部關(guān)于印發(fā)《學(xué)生體質(zhì)健康監(jiān)測(cè)評(píng)價(jià)辦法》等三個(gè)文件的通知(教體藝〔2014〕3號(hào))

[2]教育部關(guān)于印發(fā)《國家學(xué)生體質(zhì)健康標(biāo)準(zhǔn)（2014年修訂）》的通知(教體藝〔2014〕5號(hào))

[3]office2007，WPS幫助文件