Excel在體育中考成績統計中的應用

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

一、制定成績評分表

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

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

二、制定成績統計表

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

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

三、實現自動算分

1. VLOOKUP 解釋。

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

2. 根據VLOOKUP函數完成成績統計表自動算分。

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

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

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

（4）總分成績自動生成。總分成績自動生成的函數=L4+M4則可以完成了。

四、其他輔助函數技巧

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

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

責任編輯 魏文琦