邱陽

（西安音樂學(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)體比賽的成績等?？记诔煽冎饕ㄆ綍r成績以及由單項較突出的某個人參加班級以外的比賽獲獎?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ù)?？偟梅至蠥A僅用到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é)科的成績，本文提供的方法具有一定的普適性。