田江華

（重慶市開縣九龍山初級中學(xué) 重慶 405415）

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

田江華

（重慶市開縣九龍山初級中學(xué) 重慶 405415）

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

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

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

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

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

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

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

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

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

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

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

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

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

圖1

圖2 九龍山初中七年級2011年達(dá)標(biāo)運動會評分表

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

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

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

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

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

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

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

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

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

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

圖3

圖4

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

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

G633.96

A

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