2021年教育科研小學(xué)體育參評(píng)論文840 |
淺析用EXCEL函數(shù)制作國家體測(cè)數(shù)據(jù)的統(tǒng)計(jì)分析表 |
[選取申報(bào)日期] |
★請(qǐng)注意:本文檔中不允許出現(xiàn)姓名、單位等真實(shí)信息并按要求正確修訂文檔名! 參評(píng)論文正文前要有300字以內(nèi)的摘要和3-5個(gè)關(guān)鍵詞。引文要準(zhǔn)確無誤,注釋及參考文獻(xiàn)要按通用學(xué)術(shù)規(guī)范格式編寫,其中,注釋統(tǒng)一用腳注。文章格式設(shè)置為:標(biāo)題三號(hào)宋體加粗,一級(jí)標(biāo)題四號(hào)宋體加粗,正文五號(hào)宋體,行距固定值20磅。論文篇幅在2500字以上,6000字以內(nèi)。 |
以下由評(píng)委填寫
評(píng)委 | 評(píng)委一 | 評(píng)委二 | 評(píng)委三 | 評(píng)委四 | 評(píng)委五 |
等第 | 選擇一項(xiàng)。 | 選擇一項(xiàng)。 | 選擇一項(xiàng)。 | 選擇一項(xiàng)。 | 選擇一項(xià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幫助文件