運(yùn)用Vlookup函數(shù)處理體育成績的查詢與生成

黃敏

摘 要：巧妙利用辦公軟件Excel中的Vlookup函數(shù)，能夠高效處理體育成績的查找與自動生成。此外，由于體育項(xiàng)目的田、徑賽在判分標(biāo)準(zhǔn)上存在不同，還需要在前期制作評分標(biāo)準(zhǔn)表。

關(guān)鍵詞：評分表；查找區(qū)域；Vlookup

信息老師們對于Excel常用函數(shù)如求和、平均數(shù)等非常熟悉。事實(shí)上，除了在教學(xué)中涉及的函數(shù)，Excel里臧龍臥虎、高效解決實(shí)際問題的函數(shù)比比皆是。近期，筆者利用Excel的Vlookup函數(shù)解決了令我校體育老師頭暈眼花的麻煩事：查找并且自動生成學(xué)生的各項(xiàng)體育成績。

一、初步解讀Vlookup函數(shù)

Vlookup又稱垂直查詢函數(shù)，屬于查找與引用函數(shù)類。表達(dá)式為Vlookup（查找值，查找的區(qū)域，返回位置<列>，查找方式）。

第一個(gè)參數(shù)F3代表查找值，第二個(gè)參數(shù)A3：B23代表查找的區(qū)域（具有標(biāo)準(zhǔn)單項(xiàng)得分的區(qū)域），第三個(gè)參數(shù)2對應(yīng)要返回查找區(qū)域A3：B23中的哪一列記錄。這里單項(xiàng)得分屬于第2列；第四個(gè)參數(shù)為1，為近似值。

讀者可以發(fā)現(xiàn)，Vlookup函數(shù)的運(yùn)用是否得當(dāng)，關(guān)鍵在于查找的區(qū)域的選定。由于該函數(shù)的查找方式為近似匹配，其特點(diǎn)是查找區(qū)域的數(shù)據(jù)都要為升序，否則出錯(cuò)。而本案例中的數(shù)據(jù)查找區(qū)域是存在于評分標(biāo)準(zhǔn)中。因此，兩種賽事的評分標(biāo)準(zhǔn)表的建立是關(guān)系到能都正確運(yùn)用Vlookup函數(shù)的關(guān)鍵。

二、評分標(biāo)準(zhǔn)表的建立

根據(jù)體育學(xué)科的相關(guān)評分標(biāo)準(zhǔn)，在Excel中建立評分標(biāo)準(zhǔn)。目的是為將進(jìn)行選用的函數(shù)進(jìn)行自動查分提供標(biāo)準(zhǔn)。由于體育項(xiàng)目分田賽與徑賽。以高度和距離長度計(jì)算成績的跳躍、投擲項(xiàng)目稱為田賽。田賽項(xiàng)目的測試結(jié)果與成績是正比例對應(yīng)；至于徑賽項(xiàng)目，是以時(shí)間計(jì)算成績的競走和跑，它的測試結(jié)果與成績恰恰是反比例關(guān)系。下面分別說明它們評分標(biāo)準(zhǔn)的建立。

1.田賽查分表

在上文中說明過，評分標(biāo)準(zhǔn)表中的數(shù)據(jù)需要統(tǒng)一為升序。以立定跳遠(yuǎn)為例，評分標(biāo)準(zhǔn)表的建立就很簡單，對于田賽項(xiàng)目，評分規(guī)則就是數(shù)值越大，得分越高，兩列單元格的數(shù)值自可統(tǒng)一為升序。如上圖1所示。數(shù)據(jù)A列與B列同為升序排列。

2.徑賽查分表

徑賽評分標(biāo)準(zhǔn)表中首先要解決格式的問題。即要以分秒形式顯示時(shí)間數(shù)值，如3分45秒表示成3—45。若使用鍵盤或特殊符號輸入會很麻煩，這里可以采用設(shè)置單元格格式的方法，將A列的單元格選中，鼠標(biāo)右鍵點(diǎn)擊選擇設(shè)置單元格格式—數(shù)值—自定義—0—00—00。

接著就要解決評分標(biāo)準(zhǔn)中數(shù)據(jù)區(qū)域同為升序排列。

正常的評分標(biāo)準(zhǔn)表只有兩列，時(shí)間值與單項(xiàng)得分。在原始評分表中增加一個(gè)過渡處理列。將“徑賽評分表”中的標(biāo)準(zhǔn)成績進(jìn)行 “負(fù)數(shù)”處理。如圖3所示。只需在B3單元格輸入“=A3*（-0.01）”。需要說明，雖然在A3單元格顯示的是5'12，但是公式A3*（-0.01）應(yīng)用時(shí)，自動識別的是最初輸入的512。因此，B3單元格結(jié)果為負(fù)數(shù)。B3以下單元格做相同處理。

此時(shí)，評分標(biāo)準(zhǔn)中A列的時(shí)間顯示值對應(yīng)正確的C列得分。同時(shí)B列與C列同時(shí)升序排列。讀者可以想到，在后面運(yùn)用Vlookup時(shí)，表達(dá)式中“查找的區(qū)域”就只是B3：C23。

以上就成功地建立了體育項(xiàng)目的各評分標(biāo)準(zhǔn)區(qū)域。這里，還需要考慮到部分學(xué)生的成績太低，其測試結(jié)果不在評分表中。因此有必要對標(biāo)準(zhǔn)分的下限加以設(shè)置。

三、0分值的設(shè)置

在徑賽類項(xiàng)目的評分表的增加設(shè)置“零分行”，如圖4。在田賽類項(xiàng)目的評分表的“零分行”設(shè)置為“零分行”設(shè)置為-100，圖5所示。

至于上限100分，根據(jù)函數(shù)近似匹配的特點(diǎn)，會自動取得滿分。因此上限不需再進(jìn)行設(shè)置。

四、“自動填充”的批處理

最后利用Excel的“自動填充”功能來實(shí)現(xiàn)所有學(xué)生的同一項(xiàng)體育成績的批量操作。為避免由于“自動填充”帶來的單元格區(qū)域的改變，要在查詢區(qū)域涉及的單元格行標(biāo)前添加絕對引用符“$”。例在“1000米”中，H3“=VLOOKUP（G3，B$3：C$23，2，1）”。圖6所示。

當(dāng)然，根據(jù)老師個(gè)人需要，可以再運(yùn)用Excel的其他函數(shù)公式就能計(jì)算出總分、合格率、名次等等，將統(tǒng)計(jì)結(jié)果不斷擴(kuò)充完善。

使用Vlookup函數(shù)，非常方便快捷地完成了學(xué)生體育成績的查找與統(tǒng)計(jì)。從中，我們也可以感受到Excel辦公軟件的實(shí)用性非常廣泛。這一方法也適用于其他學(xué)科的類似統(tǒng)計(jì)匯總工作，提高工作效率。學(xué)會巧妙運(yùn)用辦公自動化工具，將我們從繁瑣的重復(fù)性勞動中解脫，便能有更多的時(shí)間和精力投身到教學(xué)和科研中去。

參考文獻(xiàn)：

王紅衛(wèi)，白力軍.Excel函數(shù)經(jīng)典應(yīng)用實(shí)例[M].清華大學(xué)出版社，2008-07-01.

（作者單位 江蘇省南京市寧海中學(xué)）