邱陽

（西安音樂學院，西安 710061）

0 引言

為了促進學生全面發(fā)展，2014年教育部頒布了《國家學生體質健康標準2014修訂版》（簡稱《新標準》），對大學生體育測試項目進行了大幅調整，如增加身體素質類測試項目權重[1]。這一舉措使學生更加重視體質測試，學生的平均體質健康水平得到顯著改善[2]。隨著信息技術的不斷發(fā)展，數據的采集和分析效率大幅度提高。而傳統(tǒng)紙質體育成績記錄表不僅造成大量紙張資源的浪費，且后續(xù)查詢難，不利于體育教學活動的開展[3]。因此，大量針對學生體育成績預測[4-5]或統(tǒng)計[3,6]的信息管理系統(tǒng)應運而生。然而，體育成績管理系統(tǒng)開發(fā)周期較長，更新較慢?；诖?，本文構建了基于VLOOKUP函數的高校體育成績綜合評定表（簡稱體育成績表），以期立竿見影地解決一線教師的迫切需求。

1 大學生體育成績綜合評定表的主要構成

大學生體育總成績一般由三部分構成：素質成績、專項成績和考勤成績。素質成績方面，男、女生的體育項目存在差異：在高校體育課中男女生均需測試體重指數（Body Mass Index，BMI）、肺活量、50 米跑、坐位體前屈、立定跳遠等項目；除此之外，大學男生體育成績中的素質成績可能包含1000米跑、引體向上等，大學女生體育成績中的素質成績可能包含800米跑、仰臥起坐等。專項成績方面，各高校也有較大差異，一般是指高校開設的特色課程成績，如西安音樂學院開設的健美操、藝術體操等體育教學項目的單項考試成績；此外還包含個人比賽、團體比賽的成績等?？记诔煽冎饕ㄆ綍r成績以及由單項較突出的某個人參加班級以外的比賽獲獎折合的附加分等。

體育課開課內容受季節(jié)、天氣、場地、器材及學生的諸多限制，各個高校依據自身優(yōu)勢開展特色教學，開設項目差異顯著。以西安音樂學院為例，學校女生人數多，且具有藝術特長，專項教學包含室內項目和室外項目，室內不定期開展健美操、藝術體操等，室外不定期開展定向、羽毛球、太極拳、氣排球、柔力球等。

2 大學生體育成績綜合評定表的實現(xiàn)

（1）基本框架

大學生體育成績綜合評定表應在對該高校一線體育教師充分調研的基礎上搭建，應包含教師最終呈交上級部門的全部成績項，原則上包含教師已經開設或計劃開設的所有單項，通過系數的變化使綜合評定表可以供該校每一學期每一名體育教師評定成績使用。本文以長安大學為例，說明成績表基本框架如何搭建。以長安大學為例，體育成績綜合評定表（見圖1）中共包含36列，其中A～F共6列為學生基本信息，G～AA共21列為學生素質成績，AB～AJ共9列為學生專項成績。在這一框架的基礎上應將國家標準及計算公式或函數等不變的內容填寫清楚，教師只需錄入學生基本信息和單項成績，即可自動得出總分，實現(xiàn)自動化統(tǒng)計的目的。

需要提前錄入的數據列共15列，分別是：編號A，可根據班級人數填寫，如1-30；各標準列G～J、M～N、Q～R、U～X、AG～AH應根據國家標準輸入。當標準錄入完成后，為保護數據使其不被輕易修改，可將其隱藏。

含有公式或函數的列共10列，分別是：總分E、各得分列 L、P、T、Z、AA、AC、AE、AF、AI。

需要體育教師自行填寫的列共10列，分別是：學號B、姓名C、性別D、平時成績F、各單項成績K、O、S、Y、AB、AD。

附加分AJ列作為備注項，使整個表格可以靈活調整，對于個別學生的特殊情況酌情加分或減分的可以寫在附加分一列。

圖1 體育成績綜合評定表的列標題行

對于體育課程設置更加靈活、項目更多、開課時間及項目等變動較大的院校，在設計綜合評定表時還可增加了項目、系數兩列，由教師根據本學期實際情況填寫，在總分中所有的項目得分乘以對應系數后計入總分，對于本學期沒有開始的課程，教師只需在系數一列填寫0或空白即可。

（2）制作難點

《國家學生體質健康標準（2014修訂版）》中給出了100,95,90,…,20,10共20個分數檔，大于等于該分數檔對應成績但不夠更高檔位成績的，一律按照較低檔分數執(zhí)行。以往教師需要手動查閱國家標準，找出成績對應的得分檔后，寫出學生該項目考試分數。這是教師統(tǒng)計成績最耗時的一項工作，也是成績統(tǒng)計中最容易出錯的環(huán)節(jié)。如何自動對照國家標準給出得分是成績評定表的制作難點。由于VLOOKUP函數的主要功能是在數據表的首列查找指定的值，并返回數據表當前行中指定列的值，使用VLOOKUP函數可輕松完成該工作。

在設計大學生體育成績表時，每單項成績共需要4列，分別為某單項標準、某單項標準得分、某單項、某單項得分。其中前兩列根據國家標準應提前錄入數據。第3列，錄入學生測評的真實成績，如跑步時間、跳遠距離等。第4列應提前錄入公式或函數。

VLOOKUP函數有兩種常用模式，精確查找和模糊查找。采用精確查找，需要給出每一個測評成績對應的得分值，若某測評成績沒有對應的得分值，則單元格顯示#N/A，不顯示任何成績。本例長安大學的體育成績表采用的精確查找，前兩列為標準列、標準得分列，以男子1000米跑為例，標準數據以0.01遞增依次為0.00,2.42,2.43,…,7.00，標準得分列考慮國家標準及加分標準，給出對應成績得分依次為0,110,109,…,0。精確查找雖然前期錄入的工作量稍大，但可以使用Excel快速填充命令，并且方便后期使用該成績表的體育教師自行調整等。

若使用模糊查找，則第1、2列的標準簡寫即可，第3列等待體育教師測評后自行錄入，第4列可寫入公式，例如在Z3單元格中寫入函數（省略號部分為實際標準，應填寫完整）如下：

模糊查找應特別注意標準第1列的數據只能按照升序排列，降序或亂序則會出現(xiàn)返回值錯誤。另外標準的書寫需要進行簡單計算轉換，因為VLOOKUP函數的模糊查找方式為大于等于第1列中的某個數值（但小于下一數值）時，找到數據同行其他列的相關數據。這可能與國家標準的數據順序不一致，需要在錄入Excel標準列時做出調整。如圖1中800米標準一列，按照VLOOKUP函數要求，第1列應按照升序排列，即按原表的順序錄入，但如果按照表中數據不作任何改動錄入則會出現(xiàn)錯誤，例如錄入3.18對應100分，3.24對應95，3.30對應90分，等等，然后輸入3.19-3.23的成績均會返回為100分的值，這顯然是錯誤的。錄入時應找到得分對應的最大數值進行錄入。如上述實例應錄入3.18對應100分，3.19對應95分，3.25對應90分，以此類推。

（3）評定表中的函數設計

含有公式或函數的列共10列，分別是：總分E、各得分列 L、P、T、Z、AA、AC、AE、AF、AI?？偡至?E 使用的公式是求和公式，為平時成績F、（素質成績）總得分AA、（專項成績）總得分AI、附加分AJ的和。

①得分列函數設計

以立定跳遠得分列L為例，使用的是if函數與VLOOKUP函數的嵌套，見公式（1）。

if函數用來判斷性別，VLOOKUP函數根據男（或女）立定跳遠標準I，返回男（或女）立定跳遠標準得分J同一行的得分數值。800/1000米得分列Z的函數與L列類似。得分列P、T僅用到VLOOKUP函數?？偟梅至蠥A僅用到IF函數，根據性別判斷該生素質成績的來源項為立定跳遠、引體向上、1000米跑，還是立定跳遠、仰臥起坐、800米跑，同時按照學校統(tǒng)一要求將原項目百分制的成績乘以一定系數，以長安大學體育成績表為例，AA列公式：

②VLOOKUP函數的特殊要求

使用VLOOKUP函數的精確查找功能時，不能出現(xiàn)同一標準有不同得分的情況，但允許不同標準的得分相同。以女子800米大一大二的標準為例（見圖2），4分34秒（圖2中簡寫為4.34）對應的得分是60分，若下一行中再次出現(xiàn)4.34，且對應的得分不是60分，則Excel的VLOOKUP函數按照從上往下的順序查找4.34的對應分數，最終顯示得分60分。即最后一行數據4.34屬于重復數據，應予以刪除。另外，按照標準，只有當時間小于等于4分29秒得分62分，那么4分29秒與4分34秒之間的4分30秒、31秒、32秒、33秒的得分也應該是60分。

圖2 大學生體育成績綜合評定表標準列

此外，無論成績表空表的早期設計制作還是后期的數據錄入都需要輸入大量數據，對于有規(guī)律的數據可利用Excel的填充功能快速錄入。需要注意的是，在制作800米和1000米標準的時候為了節(jié)省錄入時間，將時間簡化為*.**。小數點前表示幾分，小數點后表示幾秒，精確到小數點后2位，即精確到秒。并使用Ex?cel自動填充功能，填充增量為0.01的數列。特別說明的是，增量為0.01或增量更小的數據在使用填充柄快速填充時，應注意檢查編輯欄，確認該單元格中數據的真實大小。例如：某單元格填充后顯示4.54，但其編輯欄的真實數據是4.53999999999999見圖3。這類錯誤直接導致VLOOKUP函數查找不到4.54的數據，最終跑步時間為4.54的學生將沒有成績得分。

圖3 編輯欄顯示的數據真實大小

3 結語

體育成績是學生綜合體育素質的體現(xiàn)，同時也是對大學體育課程及教學質量的有力評判，因此，對大學生體育成績的統(tǒng)計應務必準確。而且大學生體育成績的構成多樣，標準不一，借助Excel軟件中的VLOOK?UP函數統(tǒng)計大學生體育成績不僅高效，更是用技術手段保證了成績的準確性。同時，運用Excel自帶函數設計成績表，克服了開發(fā)成績計算系統(tǒng)周期較長的問題，且具有易于更新、便于一線教師學習使用等優(yōu)點，一定程度上替代了人工的重復性工作，為教師將精力集中在教學科研等創(chuàng)新環(huán)節(jié)提供了支撐。同理，利用Excel的公式函數還可自動統(tǒng)計其他學科的成績，本文提供的方法具有一定的普適性。