如何運(yùn)用VLOOKUP等函數(shù)制作體育達(dá)標(biāo)成績(jī)冊(cè)

田江華

（重慶市開(kāi)縣九龍山初級(jí)中學(xué) 重慶 405415）

如何運(yùn)用VLOOKUP等函數(shù)制作體育達(dá)標(biāo)成績(jī)冊(cè)

田江華

（重慶市開(kāi)縣九龍山初級(jí)中學(xué) 重慶 405415）

體育達(dá)標(biāo)運(yùn)動(dòng)會(huì)是所有學(xué)生都要參加的一年一度的《國(guó)家體育鍛煉標(biāo)準(zhǔn)》的測(cè)驗(yàn)，達(dá)標(biāo)運(yùn)動(dòng)會(huì)組織工作任務(wù)繁重，根據(jù)我多年的體育教學(xué)和組織運(yùn)動(dòng)會(huì)的經(jīng)驗(yàn)來(lái)看，工作量最大的不是報(bào)名工作，也不是秩序冊(cè)的編排工作，而是達(dá)標(biāo)運(yùn)動(dòng)會(huì)的成績(jī)統(tǒng)計(jì)工作，厚厚的幾本達(dá)標(biāo)運(yùn)動(dòng)會(huì)成績(jī)冊(cè)實(shí)在讓人頭疼。傳統(tǒng)的查分方法工作量巨大，工作效率很低，統(tǒng)計(jì)容易出錯(cuò)。

VLOOKUP 函數(shù)制作 電子表格

我在工作中經(jīng)常使用電子表格，電子表格的強(qiáng)大統(tǒng)計(jì)功能使我萌生制作一個(gè)能自動(dòng)生成《國(guó)家體育鍛煉標(biāo)準(zhǔn)》成績(jī)的體育達(dá)標(biāo)成績(jī)冊(cè)的想法，只需錄入學(xué)生姓名、測(cè)試成績(jī)，就能自動(dòng)生成以下數(shù)據(jù)：得分、總分、達(dá)標(biāo)等級(jí)、各等級(jí)人數(shù)、達(dá)標(biāo)率、優(yōu)秀率等需要的統(tǒng)計(jì)指標(biāo)，從而達(dá)到減輕體育教師的查分、統(tǒng)計(jì)的工作量，提高工作效率，同時(shí)保證成績(jī)和統(tǒng)計(jì)的準(zhǔn)確性。

首先簡(jiǎn)單介紹一下Microsoft Excel工作簿,它是計(jì)算和儲(chǔ)存數(shù)據(jù)的文件，每一個(gè)工作簿都可以包含多張工作表，因此可在單個(gè)文件中管理各種類型的相關(guān)數(shù)據(jù)。工作表是Microsoft Excel用來(lái)存儲(chǔ)和處理數(shù)據(jù)的最主要的文檔，其中包括排列成行和列的單元格，工作表是工作簿的一部分，也稱作電子表格。

下面談?wù)勎沂侨绾芜\(yùn)用Microsoft Excel中的VLOOKUP等函數(shù)制作體育達(dá)標(biāo)成績(jī)冊(cè)的（以七年級(jí)為例）。

第一步：新建一個(gè)工作簿。命名為“七年級(jí)體育達(dá)標(biāo)成績(jī)查詢表”，工作簿內(nèi)新建四張工作表，分別命名為“七年級(jí)達(dá)標(biāo)標(biāo)準(zhǔn)”、“七年級(jí)男生成績(jī)表”、“七年級(jí)女生成績(jī)表”、“七年級(jí)成績(jī)統(tǒng)計(jì)表”（如圖1）。

第二步：查分表的制作。在電子表格中查分方法可以采用精確查找法，也可以采取近似查找法。《國(guó)家體育鍛煉標(biāo)準(zhǔn)評(píng)分表的使用方法》中的規(guī)定：如果某項(xiàng)成績(jī)低于某分?jǐn)?shù)線的成績(jī)但高于下面分?jǐn)?shù)線的成績(jī)時(shí)，應(yīng)按下面分?jǐn)?shù)線記分；對(duì)于高于最高分?jǐn)?shù)線的成績(jī)，按最高成績(jī)計(jì)算；低于最低成績(jī)的，按0分計(jì)算。通過(guò)統(tǒng)計(jì)發(fā)現(xiàn)，雖然達(dá)標(biāo)測(cè)試各項(xiàng)目的成績(jī)有很多種可能，但都能在電子表格單元格中反映出來(lái)，也就是說(shuō)我們能通過(guò)電子表格列出所有可能出現(xiàn)的成績(jī)，并且能根據(jù)《國(guó)家體育鍛煉標(biāo)準(zhǔn)》及其評(píng)分規(guī)定列出所有可能出現(xiàn)的測(cè)試成績(jī)的對(duì)應(yīng)得分，所以制作查分表就是列出不同性別不同項(xiàng)目不同成績(jī)的不同得分，查分表就是一本“數(shù)據(jù)字典”(如圖2)。

為了真實(shí)記錄學(xué)生測(cè)試成績(jī)，各項(xiàng)目100分線以上成績(jī)可以列舉到一個(gè)一般不容易達(dá)到的成績(jī)，除徑賽項(xiàng)目外其他項(xiàng)目最低成績(jī)可列至零(如圖2）。

第三步：成績(jī)表的設(shè)計(jì)。成績(jī)表內(nèi)測(cè)試項(xiàng)目一定在查分表中所列項(xiàng)目之中，通常情況下，我們?cè)O(shè)計(jì)達(dá)標(biāo)成績(jī)查詢表時(shí)，就要考慮所測(cè)項(xiàng)目與查分表對(duì)應(yīng)，當(dāng)然也可以在查分表中列出所有可能測(cè)試項(xiàng)目，成績(jī)表中也可根據(jù)查分表列出所有可能測(cè)試項(xiàng)目。在此我以選定的測(cè)試項(xiàng)目設(shè)計(jì)表格作為例子(如圖3)。

第四步：運(yùn)用VLOOKUP函數(shù)自動(dòng)查詢生成得分（以七年級(jí)男生成績(jī)表為例，女生成績(jī)表公式相同）。我們錄入各項(xiàng)目測(cè)試成績(jī)，由于對(duì)應(yīng)欄目設(shè)置了查找函數(shù)、求和函數(shù)、條件函數(shù)等，錄入測(cè)試成績(jī)后，各單項(xiàng)得分、總分和等級(jí)將自動(dòng)生成。

先要利用Mmicrosoft Excel的查找函數(shù)VLOOKUP來(lái)實(shí)現(xiàn)測(cè)試項(xiàng)目得分的自動(dòng)查找，例如50m跑項(xiàng)目，先在測(cè)試成績(jī)相鄰的得分欄E4列輸入：E4=VLOOKUP（D4，七年級(jí)達(dá)標(biāo)標(biāo)準(zhǔn)，A：B，2，F(xiàn)ALSE）。

圖3函數(shù)的語(yǔ)意是將表“七年級(jí)男生成績(jī)表”中所在行D4的值與表“七年級(jí)達(dá)標(biāo)標(biāo)準(zhǔn)”中A列的值進(jìn)行精確匹配，返回表“七年級(jí)達(dá)標(biāo)標(biāo)準(zhǔn)”命中行中B列的值。當(dāng)在表“七年級(jí)男生成績(jī)表”中D4成績(jī)單元格錄入測(cè)試成績(jī)后，對(duì)應(yīng)得分欄即時(shí)自動(dòng)生成得分。如上圖G4、I4、K4列函數(shù)設(shè)置與D4單元格相同，其他相應(yīng)單元格可類推，可以用電子表格自動(dòng)填充功能實(shí)現(xiàn)L、G、I、K列其他單元格的公式輸入，但要注意與表“七年級(jí)達(dá)標(biāo)標(biāo)準(zhǔn)”中取值區(qū)間對(duì)應(yīng)。

第五步：求總分和自動(dòng)生成等級(jí)。總分欄L列是求E、G、I、K列的和，其公式為：L4=E4+G4+I4+K4，其他行類推，也可以用電子表格自動(dòng)填充功能實(shí)現(xiàn)L列其他單元格的公式輸入。

圖1

圖2 九龍山初中七年級(jí)2011年達(dá)標(biāo)運(yùn)動(dòng)會(huì)評(píng)分表

等級(jí)欄M列是根據(jù)E、G、I、K列的單項(xiàng)得分和L列的總分判斷學(xué)生達(dá)標(biāo)等級(jí)，其公式為：M4=IF(OR(E4<30,G4<30,I4<30,K4<30),“不及格”,IF(L4>=360,“優(yōu)秀”,IF(L4>=300,“良好”,IF(L4>=240,“及格”,“不及格”))))。

上面函數(shù)的語(yǔ)意是任何一項(xiàng)成績(jī)＜30分，則不及格；總分≥360分，則優(yōu)秀；總分≥300＜360分，則良好；總分≥240分＜300分，則及格；否則不及格。

第六步：制作成績(jī)統(tǒng)計(jì)表。在第一步我們已經(jīng)建好“七年級(jí)成績(jī)統(tǒng)計(jì)表”（如圖4）。

從圖4我們可以看出參測(cè)年級(jí)各項(xiàng)等級(jí)統(tǒng)計(jì)指標(biāo)，男生統(tǒng)計(jì)以“七年級(jí)男生成績(jī)表”為數(shù)據(jù)源，女生以“七年級(jí)女生成績(jī)表”為數(shù)據(jù)源。在此我們以男生測(cè)試統(tǒng)計(jì)指標(biāo)為例對(duì)各統(tǒng)計(jì)指標(biāo)單元格內(nèi)公式進(jìn)行說(shuō)明。

（1）參測(cè)人數(shù)：B3=COUNTIF(七年級(jí)男生成績(jī)表!$L$4:$L$1003,“>0”)，求“七年級(jí)男生成績(jī)表”中參加測(cè)試人數(shù)。

（2）優(yōu)生人數(shù)（單項(xiàng)>30,總分≥360）和優(yōu)秀率：C4=COUNTIF(七年級(jí)男生成績(jī)表!$L$4:$M$1003,“優(yōu)秀”)，根據(jù)“七年級(jí)男生成績(jī)表”中等級(jí)欄結(jié)果統(tǒng)計(jì)出“優(yōu)秀”人數(shù)。D4=C4/B4,即優(yōu)秀人數(shù)隊(duì)以參測(cè)人數(shù)得出優(yōu)秀率。

（3）良好人數(shù)（單項(xiàng)>30,總分≥300）和良好率、及格人數(shù)（單項(xiàng)>30,總分≥240）和及格率公式設(shè)置和計(jì)算方法同優(yōu)秀人數(shù)和優(yōu)秀率的公式，但要注意修改公式相關(guān)參數(shù)。

（4）不及格人數(shù)（單項(xiàng)<30,總分<240）和不及格率：不及格人數(shù)等于參測(cè)人數(shù)減去優(yōu)秀、良好、及格人數(shù)；不及格率方法相同。

女生成績(jī)統(tǒng)計(jì)同男生統(tǒng)計(jì)方法，但其統(tǒng)計(jì)數(shù)據(jù)源為“七年級(jí)女生成績(jī)表”。

上文所述只是一個(gè)簡(jiǎn)明的運(yùn)用VLOOKUP等函數(shù)制作達(dá)標(biāo)成績(jī)統(tǒng)計(jì)表的操作步驟，是針對(duì)達(dá)標(biāo)測(cè)試項(xiàng)目制作的專門(mén)表格，沒(méi)有考慮《國(guó)家體育鍛煉標(biāo)準(zhǔn)》中所有測(cè)試項(xiàng)目，當(dāng)然也可以采用相同方法將所有測(cè)試項(xiàng)目得分標(biāo)準(zhǔn)全部列出，對(duì)應(yīng)設(shè)計(jì)制作成績(jī)表和成績(jī)統(tǒng)計(jì)分析表，當(dāng)年不測(cè)試的項(xiàng)目可隱藏其對(duì)應(yīng)列。當(dāng)然，這只是一個(gè)可以根據(jù)學(xué)校每年不同測(cè)試項(xiàng)目定制的一個(gè)簡(jiǎn)易統(tǒng)計(jì)表格，作為體育教育工作者，我更期待相關(guān)部門(mén)或軟件開(kāi)發(fā)者為《國(guó)家體育鍛煉標(biāo)準(zhǔn)》配套開(kāi)發(fā)一個(gè)科學(xué)易學(xué)的查詢系統(tǒng)。

圖3

圖4

[1]鈄志斌.Excel在財(cái)務(wù)中的應(yīng)用實(shí)訓(xùn)[M].高等教育出版社，2011.

[2]李印利.淺談電子表格教學(xué)[J].黑龍江科技信息,2009(2).

G633.96

A

2095-2813(2013)09(b)-0152-02