Excel在體育中考成績統(tǒng)計中的應用

筆者多次參加體育中考的監(jiān)考及成績統(tǒng)計，深感體育中考成績統(tǒng)計方法的繁瑣，整個工作非常繁重和枯燥，要消耗大量的時間和精力，而且在評定過程和謄寫過程中，極容易出現(xiàn)錯誤。EXCEL提供了大量的函數(shù)和圖標，操作簡便，可以輕松完成繁復的體育成績統(tǒng)計，還可以完成表格制作，清晰反映出各項成績狀況。本文以2013年河源市東源縣區(qū)體育中考成績統(tǒng)計為例進行說明。

一、制定成績評分表

1. 在制作此表時，以市教育局下發(fā)的評分標準為藍本，進行成績轉換，并且在工作表中新建一工作簿名為“評分標準”。（見圖1、圖2）

2. 轉換后的評分標準說明。為了防止因缺考或者其他原因沒有成績，在成績登記0，評分登記為0；由于我們后面將用VLOOKUP函數(shù)采用數(shù)組的方式進行自動算分，而體育競賽項目是數(shù)值越大、成績越差，所以在評分標準上加0.001，并進行倒序排序；由于我縣跑道均為200M，故只對200M進行轉換，對400M的轉換相同，在此不作介紹。

二、制定成績統(tǒng)計表

1. 按實際情況在表格中輸入準考證、姓名、性別、各個項目及成績等項目。此表由河源市教育局下發(fā)，不需要進行修改。命名為XX中學成績匯總表。（見圖3）

2. 此表制定后在自動算分中，會出現(xiàn)男、女同學的評分標準相同，問題出現(xiàn)在性別前后有空格。處理辦法有兩個，一是重新輸入，二是用替換，選定“性別”欄，選擇“編輯”——“替換”，在“查找內容”輸入“ ”（為了替換完整，請在英文狀態(tài)下按空格鍵），在“替換為”處則不需要輸入。該操作刪除了性別欄中的空格。

三、實現(xiàn)自動算分

1. VLOOKUP 解釋。

“VLOOKUP”函數(shù)的語法結構及參數(shù)作用如下： VLOOKUP （lookup_value， table_array， col_index_num， range_lookup）. Lookup_value為“需在數(shù)據(jù)表第一列中查找的數(shù)據(jù)”，可以是數(shù)值、文本字符串或引用。Table_array 為“需要在其中查找數(shù)據(jù)的數(shù)據(jù)表”，可以使用單元格區(qū)域或區(qū)域名稱等。

2. 根據(jù)VLOOKUP函數(shù)完成成績統(tǒng)計表自動算分。

（1）200米、立定跳遠、一分鐘跳繩自動成績生成。在已輸入了原始考核成績的工作表“XX中學成績統(tǒng)計表”中，自動算古*娥同學200米得分的步驟如下：選中要算分的表格F4，首先實現(xiàn)有判斷性別，采用IF函數(shù)對D4的性別進行判斷，然后與VLOOKUPH函數(shù)結合，自動算出E4在“評分標準”中的得分情況，并自動填到F4。函數(shù)為=IF（D4=“男”，VLOOKUP（E4，評分標準！$A$34：$B$28，2，1），VLOOKUP（E4，評分標準！$C$34：$D$28，2，1））；解釋為：如果（IF函數(shù)）D4是“男”，則用VLOOKUP函數(shù)在評分標準這各文件中的A3： A28區(qū)域選擇與E4相符的檔次，并將B3：B28中相對應的說值反饋到F4，要是D4是“女”，則用VLOOKUP函數(shù)在評分標準這各文件中的C3： C28區(qū)域選擇與E4相符的檔次，并將D3：D28中相對應的數(shù)值反饋到F4.由于我們在制定評分表的時候200M在運用VLOOKUP需要進行些調整，故評分標準加了5分，現(xiàn)需減掉。其他函數(shù)則不需要。

（2）考試成績（兩項平均分）自動成績生成。以往的體育中考統(tǒng)一選定2項，但2013年采用必考200米，選考立定跳遠、一分鐘跳繩中的一項，而VLOOKUP函數(shù)如果在成績?yōu)榭盏臅r候則返回錯誤值#N/A，無法用函數(shù)統(tǒng)計。筆者發(fā)現(xiàn)，需要再次用IF函數(shù)對成績欄進行判定，如果成績?yōu)榭?，則在分數(shù)欄返回0，如果成績非空，則用上面的函數(shù)，這樣就解決了三項考兩項而無法用函數(shù)統(tǒng)計成績的問題，所以200米、立定跳遠、一分鐘跳繩自動成績生成函數(shù)則需修改為=IF（E4=“”，0，IF（D4=“男”，VLOOKUP（E4，評分標準！$A$4：$B$28，2，1），VLOOKUP（E4，評分標準！$C$4：$D$28，2，1））.當然200M的函數(shù)還是需要減5的。那么考試成績（兩項平均分） 自動成績生成的函數(shù)=（F4+H4+J4）*0.5則可以完成了。

（3）體育成績自動生成。根據(jù)河源市相關文件，體育成績最后得分不得高于50分，那我們需要對考試成績（兩項平均分） 進行一個函數(shù)判定，高于50分按50分計算，不足50分，按實際成績計算。那么體育成績自動生成的函數(shù)=IF（K4>=100，50，K4/2）則可以完成了。解讀：對K4 （兩項平均分）進行判定，大于等于100則按50計算，小于100則直接按K4的一半（考試成績兩項平均分計算）。

（4）總分成績自動生成?？偡殖煽冏詣由傻暮瘮?shù)=L4+M4則可以完成了。

四、其他輔助函數(shù)技巧

1. 分割Excel工作表。垂直、水平分割窗口。在垂直滾動條的頂端或水平滾動條的右端，指向分割框。當鼠標變?yōu)榉指钪羔樅?，將分割框向下或向左拖至所需位置即可。凍結窗口功能，它會自動凍結當前鼠標選擇窗格線的上方和左側部分。在滾動工作表時，可使行列標題保持在視線之內。

2. 利用函數(shù)統(tǒng)計應考人數(shù)、缺考人數(shù)、統(tǒng)計各分數(shù)段的人數(shù)。計算總人數(shù)選擇函數(shù)為“COUNTA”；計算實考數(shù)選擇函數(shù)為“COUNT”；計算缺考數(shù)選擇函數(shù)為“COUNTBLANK”（本次成績統(tǒng)計因沒有要求，故不作詳細介紹）。分數(shù)段統(tǒng)計：單擊存放此統(tǒng)計人數(shù)的單元格，然后選取函數(shù)COUNTIF，選取統(tǒng)計區(qū)域后，回到函數(shù)向導對話框中，輸入統(tǒng)計條件：“>=90”，單擊確定就可以統(tǒng)計90分以上（包括90分）的人數(shù)（根據(jù)要求可以改變）。統(tǒng)計80-89分的人數(shù)：單擊存放此統(tǒng)計人數(shù)的單元格，輸入統(tǒng)計條件：“=COUNTIF（統(tǒng)計區(qū)域，“>=80”）-COUNTIF（統(tǒng)計區(qū)域，“>=90”）”，單擊確定就可以計算出結果了。其他的分段統(tǒng)計相同。

責任編輯 魏文琦