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

邱陽

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

0 引言

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

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

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

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

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

（1）基本框架

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

需要提前錄入的數(shù)據(jù)列共15列，分別是：編號(hào)A，可根據(jù)班級(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é)號(hào)B、姓名C、性別D、平時(shí)成績F、各單項(xiàng)成績K、O、S、Y、AB、AD。

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

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

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

（2）制作難點(diǎn)

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

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

VLOOKUP函數(shù)有兩種常用模式，精確查找和模糊查找。采用精確查找，需要給出每一個(gè)測(cè)評(píng)成績對(duì)應(yīng)的得分值，若某測(cè)評(píng)成績沒有對(duì)應(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)，給出對(duì)應(yīng)成績得分依次為0,110,109,…,0。精確查找雖然前期錄入的工作量稍大，但可以使用Excel快速填充命令，并且方便后期使用該成績表的體育教師自行調(diào)整等。

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

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

（3）評(píng)定表中的函數(shù)設(shè)計(jì)

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

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

以立定跳遠(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ì)成績的來源項(xiàng)為立定跳遠(yuǎn)、引體向上、1000米跑，還是立定跳遠(yuǎn)、仰臥起坐、800米跑，同時(shí)按照學(xué)校統(tǒng)一要求將原項(xiàng)目百分制的成績乘以一定系數(shù)，以長安大學(xué)體育成績表為例，AA列公式：

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

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

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

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

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

3 結(jié)語

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