基于VLOOKUP的大學(xué)生體育綜合成績自動化統(tǒng)計與實現(xiàn)

邱陽

（西安音樂學(xué)院，西安 710061）

0 引言

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

1 大學(xué)生體育成績綜合評定表的主要構(gòu)成

大學(xué)生體育總成績一般由三部分構(gòu)成：素質(zhì)成績、專項成績和考勤成績。素質(zhì)成績方面，男、女生的體育項目存在差異：在高校體育課中男女生均需測試體重指數(shù)（Body Mass Index，BMI）、肺活量、50 米跑、坐位體前屈、立定跳遠(yuǎn)等項目；除此之外，大學(xué)男生體育成績中的素質(zhì)成績可能包含1000米跑、引體向上等，大學(xué)女生體育成績中的素質(zhì)成績可能包含800米跑、仰臥起坐等。專項成績方面，各高校也有較大差異，一般是指高校開設(shè)的特色課程成績，如西安音樂學(xué)院開設(shè)的健美操、藝術(shù)體操等體育教學(xué)項目的單項考試成績；此外還包含個人比賽、團(tuán)體比賽的成績等。考勤成績主要包括平時成績以及由單項較突出的某個人參加班級以外的比賽獲獎?wù)酆系母郊臃值取?/p>

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

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

（1）基本框架

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

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

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

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

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

圖1 體育成績綜合評定表的列標(biāo)題行

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

（2）制作難點

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

在設(shè)計大學(xué)生體育成績表時，每單項成績共需要4列，分別為某單項標(biāo)準(zhǔn)、某單項標(biāo)準(zhǔn)得分、某單項、某單項得分。其中前兩列根據(jù)國家標(biāo)準(zhǔn)應(yīng)提前錄入數(shù)據(jù)。第3列，錄入學(xué)生測評的真實成績，如跑步時間、跳遠(yuǎn)距離等。第4列應(yīng)提前錄入公式或函數(shù)。

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

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

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

（3）評定表中的函數(shù)設(shè)計

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

①得分列函數(shù)設(shè)計

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

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

②VLOOKUP函數(shù)的特殊要求

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

圖2 大學(xué)生體育成績綜合評定表標(biāo)準(zhǔn)列

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

圖3 編輯欄顯示的數(shù)據(jù)真實大小

3 結(jié)語

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