運用電子表格實現(xiàn)體育測試項目成績自動評分研究

董東風(fēng)

（湖南郵電職業(yè)技術(shù)學(xué)院，湖南長沙410015）

運用電子表格實現(xiàn)體育測試項目成績自動評分研究

董東風(fēng)

（湖南郵電職業(yè)技術(shù)學(xué)院，湖南長沙410015）

為了滿足高校信息化建設(shè)需要，方便體育教師快速、準(zhǔn)確對學(xué)生體育測試項目成績進(jìn)行評分，文章通過查詢知網(wǎng)文獻(xiàn)資料，運用電子表格技術(shù)，采用INDEX與MATCH兩函數(shù)組合查找，并采用單項分?jǐn)?shù)成升序排列的評分表的辦法，研發(fā)出了"體育成績管理系統(tǒng)"，實現(xiàn)了對學(xué)生體育測試項目成績自動評分功能。該系統(tǒng)可以滿足體育教師個人對學(xué)生體育成績管理的需求。但是，該系統(tǒng)安全性低，自動化程度低，處理數(shù)據(jù)量小，且尚未在實際中運用。

電子表格；體育成績；自動評分；計算公式

1　研究背景

對學(xué)生體育成績評定是體育教學(xué)的重要環(huán)節(jié)之一。其評定內(nèi)容通常包括多個方面，例如，體育理論知識，體育測試項目，體育課考勤，參與體育競賽，參與體育社團等，各高校沒有統(tǒng)一標(biāo)準(zhǔn)，依據(jù)教育部《全國普通高等高校體育課程教學(xué)指導(dǎo)綱要》教體藝［2002］13號文件自行制定，各方面內(nèi)容分別占體育總成績的一定比重（百分比）（參見表1）。

在體育成績評定內(nèi)容中，體育測試項目是必不可少的一項內(nèi)容，通常每個學(xué)期會對學(xué)生進(jìn)行兩到三個體育項目測試，并對各項實際測試成績依據(jù)相關(guān)評分標(biāo)準(zhǔn)進(jìn)行評分，例如表2，就是大學(xué)男生體育測試項目評分標(biāo)準(zhǔn)表，這是一個百分制評分表，表2只是列出了部分測試項目，實際有13項之多（沒有包括各高校自主制定的體育測試項目評分標(biāo)準(zhǔn)）。評分后，依據(jù)各測試項目得分（百分制）占體育總成績比重?fù)Q算成總成績的一項分?jǐn)?shù)，合計各項內(nèi)容考核分?jǐn)?shù)就可以得到體育總成績分?jǐn)?shù)（百分制），然后，把總分換算成等級（小于60分不及格，大于等于60分且小于75分及格，大于等于75分且小于85分良好，大于等于85優(yōu)秀）就是學(xué)生學(xué)期最終成績。

表1　體育成績評定內(nèi)容和比重表

目前，很多高校都已實現(xiàn)了體育成績計算機管理。由于體育成績評定內(nèi)容和比重各校沒有統(tǒng)一標(biāo)準(zhǔn)，且各校體育測試項目成績評分標(biāo)準(zhǔn)也不統(tǒng)一，也沒有統(tǒng)一的體育成績管理標(biāo)準(zhǔn)；且購買相關(guān)軟件成本很高，而技術(shù)含量很低，通用性差。所以，各高?；旧隙际亲灾餮邪l(fā)體育成績管理系統(tǒng)。有的高?；ù蟪杀鹃_發(fā)數(shù)據(jù)庫管理軟件，數(shù)據(jù)安全性高，自動化程度高，實現(xiàn)了網(wǎng)絡(luò)化管理。有的高校采用電子表格來管理，數(shù)據(jù)安全性低，自動化程度低，處理數(shù)據(jù)量小，但開發(fā)成本低，經(jīng)濟實用。

我院對學(xué)生體育成績計算機管理，只包含了學(xué)生學(xué)期最終成績管理，通過任課教師上報學(xué)生學(xué)期最終成績到教務(wù)處，然后，由教務(wù)處把成績輸入到學(xué)籍管理系統(tǒng)中（該系統(tǒng)包含有學(xué)生學(xué)期成績管理）。而對學(xué)生體育考核資料還是采用手工管理，對學(xué)生體育測試項目成績評分還是采用人工查表評分，尚未實現(xiàn)計算機自動評分。自動評分是實現(xiàn)體育成績計算機管理的難點。

表2　大學(xué)男生體育測試項目評分標(biāo)準(zhǔn)表（部分項目）

2　研究目的和意義

對每一位學(xué)生體育測試項目的每一項實際測試成績進(jìn)行人工評分是一個簡單重復(fù)的工作，工作量大，效率低，且枯燥無味，難免不出現(xiàn)錯誤。所以，這個工作特別適合用計算機來完成。一是可以對輸入的實際測試數(shù)據(jù)進(jìn)行有效性檢驗，避免異常數(shù)據(jù)輸入；二是可以格式化實際測試數(shù)據(jù)輸入，減少字符輸入量，例如，3分30秒，只需要輸入330；三是可以對輸入的實際測試數(shù)據(jù)進(jìn)行自動評分，避免人為錯誤評分，且減少了分?jǐn)?shù)的輸入；四是考核資料便于存檔、查詢、統(tǒng)計分析。

為了減輕體育教師工作負(fù)擔(dān)，提高工作效率，提高體育成績管理自動化水平，滿足高校信息化建設(shè)需要。本文針對體育教師個人對學(xué)生體育成績管理的需求，以經(jīng)濟實用為原則，以實現(xiàn)對學(xué)生體育測試項目成績自動評分為主要目標(biāo)，進(jìn)而實現(xiàn)“體育成績管理系統(tǒng)”目標(biāo)。系統(tǒng)實際運行后，將會取代體育教師現(xiàn)行手工體育成績管理，方便體育教師快速、準(zhǔn)確對學(xué)生體育測試項目成績進(jìn)行評分，對學(xué)生體育成績進(jìn)行評定，方便體育教師對原始數(shù)據(jù)存檔、查詢、統(tǒng)計分析，并可用于指導(dǎo)教學(xué)。努力實現(xiàn)體育成績精準(zhǔn)化、標(biāo)準(zhǔn)化、規(guī)范化、科學(xué)化、現(xiàn)代化管理，提高體育教學(xué)管理水平。同時，能更好的為學(xué)生服務(wù)，方便學(xué)生查詢成績，及時掌握成績動態(tài)，幫助學(xué)生有針對性的鍛煉。

3　研究方法

3.1文獻(xiàn)研究法

通過知網(wǎng)查詢體育成績管理及自動評分相關(guān)研究成果，通過對現(xiàn)有“電子表格體育成績管理系統(tǒng)”進(jìn)行研究發(fā)現(xiàn)，部分系統(tǒng)存在評分不準(zhǔn)確問題，并找到解決問題的辦法。

3.2經(jīng)驗總結(jié)法

在實際運用電子表格技術(shù)研發(fā)“我院體育成績管理系統(tǒng)”中，運用以往對數(shù)據(jù)庫精確查詢經(jīng)驗，選擇正確的查找函數(shù)是實現(xiàn)自動評分的關(guān)鍵，對于不能滿足評分表查找要求的函數(shù)，不能輕易改變原始評分表來滿足函數(shù)的要求，這樣只能是把問題復(fù)雜化，應(yīng)該選擇能滿足評分表查找要求的函數(shù)。

4　研究結(jié)果

4.1現(xiàn)有電子表格自動評分存在的問題

通過知網(wǎng)查詢可以發(fā)現(xiàn)，現(xiàn)有電子表格自動評分還存在問題，雖然實現(xiàn)了自動評分，但是，把評分表改變得面目全非，還存在對成績數(shù)值成升序排列項目（數(shù)值越小得分越高，數(shù)值越大得分越低）評分不準(zhǔn)確問題。主要原因：一是對電子表格查找函數(shù)認(rèn)識不夠，沒有正確選擇查找函數(shù)，錯誤選擇VLOOKUP或LOOKUP函數(shù)來實現(xiàn)自動評分，由于，這兩個函數(shù)在模糊查找評分表時，都對評分表中數(shù)值排列要求升序排列（數(shù)值從小到大排列），然而，評分表中數(shù)值排列存在升序和降序兩種排列形式，所以，想通過改變降序排列項目（數(shù)值越大得分越高，數(shù)值越小得分越低）成升序排列來滿足查找函數(shù)的要求，由此造成評分表面目全非；二是對評分表中數(shù)值“區(qū)間”（在升序排列項目中，數(shù)值區(qū)間是從“大于上一行數(shù)值”到“小于等于本行數(shù)值”之間）與查找函數(shù)模糊查找“方向”（在升序排列項目中，是查找小于實測值的最大值）兩者之間的關(guān)系認(rèn)識不夠，忽略了評分表中數(shù)值通常是不連續(xù)排列問題，造成對原本升序排列項目評分不準(zhǔn)確，反而對原本降序排列項目通過倒置成升序排列項目的評分準(zhǔn)確。正是因為上述兩個原因，反而把簡單的自動評分問題復(fù)雜化，而且還犯了顧此失彼的錯誤，忽略了評分表的整體性。

4.2正確選擇查找函數(shù)

由于評分表中項目成績數(shù)值排列存在升序和降序兩種形式，所以，在實現(xiàn)自動評分問題上，要求必須選擇查找函數(shù)，既能實現(xiàn)查找升序項目，又能實現(xiàn)查找降序項目。而VLOOKUP和LOOKUP兩函數(shù)都不能滿足這一要求。其實，不難發(fā)現(xiàn)MATCH函數(shù)才是實現(xiàn)自動評分首選函數(shù)，既能實現(xiàn)升序項目查找，又能實現(xiàn)降序項目查找。但是，MATCH函數(shù)只能返回指定值在指定區(qū)域中的位置，卻不能返回指定值所處位置對應(yīng)的分?jǐn)?shù)。而VLOOKUP和LOOKUP兩函數(shù)正是因為具備這種查找功能，所以才被很多人首選。然而，INDEX函數(shù)可以實現(xiàn)返回所處位置對應(yīng)的分?jǐn)?shù)，所以，采用INDEX和MATCH兩函數(shù)組合查找，可以達(dá)到VLOOKUP或LOOKUP函數(shù)查找作用，同時又能滿足升序和降序查找要求。

4.3認(rèn)識評分表

在表2中，最左列是單項得分（百分制），分?jǐn)?shù)從高到低降序排列，其余各列是各測試項目成績，這里只列出了部分有代表性的測試項目。不管測試項目有多少，體育測試項目數(shù)值只有兩種排列形式：一種是數(shù)值越小得分越高，數(shù)值越大得分越低，當(dāng)分?jǐn)?shù)成降序排列時，這種項目成績成升序排列，例如表2中的1000米跑、50米跑、籃球運球。另一種是數(shù)值越大得分越高，數(shù)值越小得分越低，當(dāng)分?jǐn)?shù)成降序排列時，這種項目成績也成降序排列，例如表2中的立定跳遠(yuǎn)、排球墊球。兩種形式排序相反，并存于評分表中。

但是，兩種排列形式有一個共同點，就是數(shù)值排列不是連續(xù)的，換句話說，實際測試項目成績在表中會找不到（不包括超出最大值到最小值范圍的值），例如，實測成績1000跑3分30秒、排球墊球47次，在表2中，如果采用查找函數(shù)精確查找，這兩個成績都是在98分與96分之間，沒有對應(yīng)的分?jǐn)?shù)，系統(tǒng)報錯。如果采用查找函數(shù)模糊查找，這兩個成績得到的分?jǐn)?shù)都是98分，這是因為，查找升序排列數(shù)值時，如果查找函數(shù)找不到，就找小于實測值的最大值，例子中實測值是1000米跑3分30秒，小于它的有3分28秒和3分27秒，小于它的最大值是3分28秒，對應(yīng)的分?jǐn)?shù)是98分。查找降序排列數(shù)值時，如果查找函數(shù)找不到，就找大于實測值的最小值，例子中實測值是排球墊球47次，大于它的有49次和50次，大于它的最小值是49次，對應(yīng)的分?jǐn)?shù)是98分。很明顯這個評分是錯誤的。如果采用人工查找，很容易得出這兩個成績都是得96分。這是因為，我們知道所有類似的評分表測試項目成績是一個數(shù)值區(qū)間（多個數(shù)值）對應(yīng)一個分?jǐn)?shù)（最小區(qū)間就一個數(shù)值）。也就是說，在表2中隱含了數(shù)值區(qū)間的另一端值。

實際上表2中項目成績給出的數(shù)值，如果是升序排列項目（1000米跑、50米跑、籃球運球）給出的是數(shù)值區(qū)間的高限數(shù)值，如果是降序排列項目（立定跳遠(yuǎn)、排球墊球）給出的是數(shù)值區(qū)間的低限數(shù)值。而查找函數(shù)模糊查找數(shù)值方向：升序排列時是找數(shù)值區(qū)間的低限數(shù)值（小于查找值的最大值）；降序排列時是找數(shù)值區(qū)間的高限數(shù)值（大于查找值的最小值）。這與表2給出的區(qū)間數(shù)值正好相反。所以，表2不適合查找函數(shù)模糊查找。有兩種辦法可以解決這個問題：

一種辦法是，讓表2列出隱含的另一端值。依據(jù)表2給出的數(shù)值，運用電子表格可以很容易得到數(shù)值區(qū)間的另一端值（參見表3）。

表3　大學(xué)男生體育測試項目評分標(biāo)準(zhǔn)表（隱含端值）

表3中的各項目數(shù)值，除第一行（100分）數(shù)值與表2對應(yīng)行數(shù)值相等外，其余各行數(shù)值都是依據(jù)表2對應(yīng)行的上一行數(shù)值增或減一個數(shù)得到。把表2和表3合并起來看，就可以看到各項目成績的數(shù)值區(qū)間，也就形成了數(shù)值區(qū)間排列的連續(xù)性。上述查找函數(shù)模糊查找的例子，如果是查找表3，就會得到與人工查找一樣的結(jié)果。這是因為表3給出的區(qū)間高低限值與查找函數(shù)模糊查找數(shù)值的方向正好相同。所以，表3才是自動評分所需要的評分表。但是，這一辦法不可取，原因是表3與表2的數(shù)值不相等，容易造成混淆。另一種辦法是，把表2完整倒置，單項得分成升序排列，原升序項目成降序項目，原降序項目成升序項目（參見表4）。

表4　大學(xué)男生體育測試項目評分標(biāo)準(zhǔn)表（倒置）

表4與表2比較，數(shù)值沒有改變，但由于表4改變了表2的方向，所以，表2原來數(shù)值區(qū)間的端值到表4，由不適合變?yōu)檫m合，上述查找函數(shù)模糊查找的例子，如果是查找表4，只需要改變查找方向，原來升序查找變?yōu)榻敌虿檎遥瓉斫敌虿檎易優(yōu)樯虿檎?，改變后，查找結(jié)果與人工查找一樣。所以，表4更適合自動評分采用。

4.4自動評分公式

通過實踐，確定了適合自動評分的函數(shù)和評分表，所以，我們采用INDEX與MATCH兩函數(shù)組合查找和倒置評分表（參見表4）辦法來實現(xiàn)精準(zhǔn)的自動評分。表5是體育成績登記表，是根據(jù)上述體育成績評定內(nèi)容和比重進(jìn)行設(shè)計的，可以實現(xiàn)對學(xué)生體育測試項目成績自動評分，進(jìn)而實現(xiàn)對學(xué)生學(xué)期體育成績評定管理。

在表5中，測試項目分?jǐn)?shù)欄中的記錄是帶有計算公式的，為了方便理解計算公式，我們用偽代碼給出：800/1000米跑分?jǐn)?shù)欄自動評分公式（偽代碼）：

=（

IF（輸入成績=""，0，

IF（性別="男"，

IF（ISNA（MATCH（輸入成績，大學(xué)男生體育測試項目評分標(biāo)準(zhǔn)！1000米跑，-1））=TRUE，

MIN（單項得分），

INDEX（大學(xué)男生體育測試項目評分標(biāo)準(zhǔn)！單項得分，

MATCH（輸入成績，大學(xué)男生體育測試項目評分標(biāo)準(zhǔn)！1000米跑，-1））），

IF（性別="女"，?

表5　體育成績登記表

IF（ISNA（MATCH（輸入成績，大學(xué)女生體育測試項目評分標(biāo)準(zhǔn)！800米跑，-1））=TRUE，

MIN（單項得分），

INDEX（大學(xué)女生體育測試項目評分標(biāo)準(zhǔn)！單項得分，

MATCH（輸入成績，大學(xué)女生體育測試項目評分標(biāo)準(zhǔn)！800米跑，-1））），0）））

）*0.2

立定跳遠(yuǎn)分?jǐn)?shù)欄自動評分公式（偽代碼）：

=（

IF（輸入成績=""，0，

IF（性別="男"，

IF（ISNA（MATCH（輸入成績，大學(xué)男生體育測試項目評分標(biāo)準(zhǔn)！立定跳遠(yuǎn)，1））=TRUE，

MIN（單項得分），

INDEX（大學(xué)男生體育測試項目評分標(biāo)準(zhǔn)！單項得分，

MATCH（輸入成績，大學(xué)男生體育測試項目評分標(biāo)準(zhǔn)！立定跳遠(yuǎn)，1））），

IF（性別="女"，

IF（ISNA（MATCH（輸入成績，大學(xué)女生體育測試項目評分標(biāo)準(zhǔn)！立定跳遠(yuǎn)，1））=TRUE，

MIN（單項得分），

INDEX（大學(xué)女生體育測試項目評分標(biāo)準(zhǔn)！單項得分，

MATCH（輸入成績，大學(xué)女生體育測試項目評分標(biāo)準(zhǔn)！立定跳遠(yuǎn)，1））），0）））

）*0.2

上述公式需要說明的是，對于超范圍值（超出最大值到最小值范圍之間的值），無需改變端值，可以通過ISNA函數(shù)判斷，再結(jié)合對輸入值的限制來解決。

MATCH函數(shù)如果是查找表2評分表時，輸入成績，如果超出最高分限值，查找函數(shù)會報錯，ISNA函數(shù)顯示為真，判給最高分；如果超出最低分限值，查找函數(shù)將不會報錯，因為，查找函數(shù)總能找到最低分值。

MATCH函數(shù)如果是查找表4評分表時，輸入成績，如果超出最低分限值，查找函數(shù)會報錯，ISNA函數(shù)顯示為真，判給最低分。如果超出最高分限值，查找函數(shù)將不會報錯，因為，查找函數(shù)總能找到最高分值。

800/1000米跑成績欄限制：

自定義輸入格式：#：##。

有效性檢查的有效條件：允許整數(shù)，數(shù)據(jù)介于最小值1與最大值999之間。

立定跳遠(yuǎn)成績欄限制：

輸入格式：整數(shù)。

有效性檢查的有效條件：允許整數(shù)，數(shù)據(jù)介于最小值1與最大值999之間。

5　結(jié)論

1）本文通過查詢知網(wǎng)文獻(xiàn)資料研究發(fā)現(xiàn)，現(xiàn)有體育成績電子表格自動評分系統(tǒng)還存在不足，主要原因：一是錯誤選擇VLOOKUP或LOOKUP函數(shù)來實現(xiàn)自動評分；二是存在對升序排列項目評分不準(zhǔn)確問題。對評分表數(shù)值區(qū)間與查找函數(shù)模糊查找“方向”之間的關(guān)系認(rèn)識不夠，忽略了評分表中數(shù)值排列的連續(xù)性問題。把自動評分問題復(fù)雜化，犯了顧此失彼的錯誤，忽略了評分表的整體性。

2）采用INDEX與MATCH兩個函數(shù)組合查詢是實現(xiàn)自動評分首選。

3）采用單項分?jǐn)?shù)成升序排列的評分表更符合電子表格查找函數(shù)模糊查找要求。

4）本文運用電子表格技術(shù)，研發(fā)出了“體育成績管理系統(tǒng)”，實現(xiàn)了對學(xué)生體育測試項目成績自動評分功能，并用偽代碼形式給出了自動評分計算公式。

5）該系統(tǒng)小巧、開放，開發(fā)成本低，經(jīng)濟實用，可以作為體育成績管理工具，滿足體育教師個人對學(xué)生體育成績管理的需求。但是，該系統(tǒng)安全性低，自動化程度低，處理數(shù)據(jù)量小，且尚未在實際中運用?？梢宰鳛椤绑w育成績數(shù)據(jù)庫管理系統(tǒng)”雛形，為最終實現(xiàn)“體育成績數(shù)據(jù)庫管理系統(tǒng)”提供理論依據(jù)。

［1］王勝，張偉業(yè).體育成績管理系統(tǒng)設(shè)計與實現(xiàn)［J］.攀枝花學(xué)院學(xué)報，2011（6）：42-46.

［2］楊丹亞，黃顯忠.體育成績計算機評分系統(tǒng)的開發(fā)與應(yīng)用［J］.商洛學(xué)院學(xué)報，2009（6）：88-91.

［3］陳寧，羅玲，王有智.運用Excel函數(shù)快速計算高校公體成績探析［J］.黔南民族師范學(xué)院學(xué)報，2013（6）：93-96.

［4］宋強.使用Excel自動生成體育成績的設(shè)計［J］.山東體育學(xué)院學(xué)報，2002（4）：89-91.

［5］原儒建.基于EXCEL函數(shù)的學(xué)生體質(zhì)健康評價［J］.河北北方學(xué)院學(xué)報（社會科學(xué)版），2009（5）：90-92.

［6］施海波.運用ExcelVBA實現(xiàn)體育運動成績的評分與統(tǒng)計［C］.第二十一屆全國高校田徑科研論文報告會論文專輯，2011.

［7］陳邦權(quán).淺談Excel函數(shù)在體育成績評定中的應(yīng)用［J］.科技信息（學(xué)術(shù)研究），2007（36）：562-565.

［8］黃敏.運用VLOOKUP函數(shù)處理體育成績的查詢與生成［J］.新課程（上），2013（6）：102-103.

［9］李娜.基于數(shù)據(jù)挖掘的高職體育成績管理系統(tǒng)的設(shè)計與實現(xiàn)［D］.成都：電子科技大學(xué)，2012.

［10］董東風(fēng).學(xué)生三自主體育選項課管理系統(tǒng)數(shù)據(jù)結(jié)構(gòu)研究［J］.湖南郵電職業(yè)技術(shù)學(xué)院學(xué)報，2015（2）：100-104.

Research on using spreadsheet to realize the automatic scoring of physical testing

DONG Dong-feng
（Hunan Postand Telecommunication College，Changsha，Hunan，China410015）

In order tomeet the needsof the information construction of collegesand universitiesand facilitate PE teachers to quickly and accurately givemarks to the students，the author inquired the CNKI literature and developed the Sports Performance Management System which uses the spreadsheet technology，INDEX-MATCH combination search and the individualscores sheetin ascending order，which achieves automatic scoring.The system canmeet theneedsof PE teachers for themanagementof students'physicalperformance. However，the system has low security，low degree ofautomation，smalldataprocessing size，and hasnotbeen used in practice.

spreadsheet；PE score；automatic scoring；calculating formula

10.3969/j.issn.2095-7661.2016.03.005】

TP311.52

A

2095-7661（2016）03-0018-05

2016-06-23

董東風(fēng)（1958-），男，湖南長沙人，湖南郵電職業(yè)技術(shù)學(xué)院經(jīng)濟管理系教授，學(xué)士，研究方向：體育教學(xué)管理、體育競賽編排。

2013年度湖南郵電職業(yè)技術(shù)學(xué)院資助科研項目“我院體育課自選系統(tǒng)開發(fā)與研究”（課題編號：2012BZ09）。