Excel在學(xué)生成績(jī)管理中的應(yīng)用

黃 偉 何 蘇

（1.宜賓衛(wèi)生學(xué)校，四川 宜賓 644000；2.宜賓學(xué)院，四川 宜賓 644000）

Excel在學(xué)生成績(jī)管理中的應(yīng)用

黃 偉1何 蘇2

（1.宜賓衛(wèi)生學(xué)校，四川 宜賓 644000；2.宜賓學(xué)院，四川 宜賓 644000）

在學(xué)校日常教學(xué)管理工作中，學(xué)生成績(jī)管理當(dāng)屬其中的重要環(huán)節(jié)。文章利用Excel的數(shù)據(jù)處理功能、內(nèi)置函數(shù)以及公式，可實(shí)現(xiàn)學(xué)生成績(jī)錄入、統(tǒng)計(jì)成績(jī)分布、篩選學(xué)生數(shù)據(jù)、成績(jī)排名、畢業(yè)生成績(jī)檔案打印等一系列工作，操作簡(jiǎn)單易懂，可有效地提高工作效率。

Excel；成績(jī)管理；函數(shù)；公式

在日常教學(xué)管理中，學(xué)生成績(jī)管理當(dāng)屬比較重要的一個(gè)部分。面對(duì)全校數(shù)千名學(xué)生幾十門(mén)課程的龐大的數(shù)據(jù)量，如何能使成績(jī)數(shù)據(jù)做到規(guī)范有序地存儲(chǔ)，且能按照該校的具體管理要求快捷靈活地使用數(shù)據(jù)，看來(lái)似乎是一個(gè)讓人頭疼的難題，其實(shí)運(yùn)用我們身邊常見(jiàn)的辦公自動(dòng)化軟件Excel就可以輕松解決。

Microsoft Excel 是Microsoft office的一個(gè)重要的組成部分，它可完成表格輸入、統(tǒng)計(jì)、分析等多項(xiàng)工作，可生成精美直觀的表格、圖表。它可以進(jìn)行各種數(shù)據(jù)的處理、統(tǒng)計(jì)分析和輔助決策操作，廣泛地應(yīng)用于管理、統(tǒng)計(jì)財(cái)經(jīng)、金融等眾多領(lǐng)域。

本文應(yīng)用Excel實(shí)現(xiàn)了成績(jī)錄入、成績(jī)統(tǒng)計(jì)匯總以及畢業(yè)生成績(jī)單打印等成績(jī)管理各個(gè)重要環(huán)節(jié)的優(yōu)化設(shè)計(jì)，使成績(jī)管理更加有效、靈活，能滿(mǎn)足不同學(xué)校的特殊管理要求。

1 學(xué)生成績(jī)錄入

1.1 表格設(shè)計(jì)

在學(xué)生成績(jī)錄入前，首先根據(jù)的年級(jí)建立工作簿文件，如“2009級(jí)學(xué)生成績(jī)表”、 “2010級(jí)學(xué)生成績(jī)表”……，然后在建立好的某工作簿中根據(jù)專(zhuān)業(yè)建立對(duì)應(yīng)的工作表（sheet）（同一專(zhuān)業(yè)如眾多班級(jí)也可建立在同一張工作表中，只需要用一個(gè)數(shù)據(jù)列“班級(jí)”作為區(qū)分標(biāo)志），工作表標(biāo)簽可修改為“專(zhuān)業(yè)名”以便區(qū)分。

在單張工作表中，必有字段包括：“班級(jí)”、“學(xué)號(hào)”、“姓名”“課程名稱(chēng)”。建立后的樣式如圖1所示：

圖1

1.2 成績(jī)錄入

將紙質(zhì)成績(jī)單轉(zhuǎn)換為電子表格數(shù)據(jù)是一項(xiàng)繁重的工作，面對(duì)海量的成績(jī)數(shù)據(jù)，要求錄入者必須做到快速且仔細(xì)。為避免成績(jī)?cè)阡浫牒箅y以察覺(jué)，可在錄入前作好對(duì)電子表格的相關(guān)設(shè)置。

1.2.1 設(shè)置有效范圍

一般情況下，成績(jī)數(shù)據(jù)的有效值是在0到100之間，但是錄入難免會(huì)出現(xiàn)錄入數(shù)字大于100的失誤，對(duì)于這種情況，我們可事先設(shè)置好錄入數(shù)據(jù)的有效范圍。選定即將輸入數(shù)據(jù)的單元格區(qū)域，打開(kāi)“數(shù)據(jù)”菜單下“有效性”對(duì)話框，可在“設(shè)置”選項(xiàng)卡中將輸入數(shù)據(jù)定義為“小于等于100的小數(shù)”，然后點(diǎn)擊“確定”按鈕。這樣，如輸入數(shù)值超過(guò) 100時(shí)，系統(tǒng)將提示“輸入非法值”要求重新輸入數(shù)據(jù)。

1.2.2 設(shè)置條件格式

在成績(jī)輸入過(guò)程中，為區(qū)分該科成績(jī)確為十分以下或是誤將兩位數(shù)字輸為一位數(shù)的情況，最好是將10分以下的成績(jī)用特殊條件格式突出顯示，以便查對(duì)。具體操作：先選定單元格，打開(kāi)“格式”菜單中的“條件格式”菜單項(xiàng)，在“條件格式”對(duì)話框中設(shè)置條件為“單元格數(shù)值小于 10”，然后點(diǎn)擊右下方“格式”按鈕，將單元格字體格式設(shè)置為“加粗”、“藍(lán)色”，然后點(diǎn)擊“確定”按鈕。這樣一來(lái)，選定區(qū)域內(nèi)的凡低于十分的單元格數(shù)字將以加粗藍(lán)色字體顯示，十分醒目。同理，也可將不及格成績(jī)用紅色字體突出顯示。

1.2.3 安全設(shè)置

學(xué)生成績(jī)表作為教學(xué)管理中的重要文件，必須得有一定的保密性，即利用密碼設(shè)置打開(kāi)和修改權(quán)限。具體操作：打開(kāi)“工具”文件中的“選項(xiàng)”菜單項(xiàng)，在“選項(xiàng)”對(duì)話框中選定“安全性”選項(xiàng)卡，可在里面分別設(shè)置“打開(kāi)權(quán)限密碼”和“修改權(quán)限密碼”，然后點(diǎn)擊“確定”按鈕。

2 成績(jī)統(tǒng)計(jì)匯總

學(xué)生成績(jī)表不僅學(xué)生學(xué)習(xí)情況的體現(xiàn)，同時(shí)也是教師教學(xué)效果的一種體現(xiàn)，因而學(xué)校常利用學(xué)生成績(jī)數(shù)據(jù)為依據(jù)進(jìn)行成績(jī)分析。要進(jìn)行成績(jī)分析，就得進(jìn)行數(shù)據(jù)的統(tǒng)計(jì)匯總，這時(shí)我們就可以利用Excel強(qiáng)大的函數(shù)功能輕松實(shí)現(xiàn)。

2.1 統(tǒng)計(jì)成績(jī)分布情況

如要求統(tǒng)計(jì)“法律”課程60分以下，60到70分以下，70到80分以下，80到90分以下，以及90分以上的學(xué)生人數(shù)，即可將分段點(diǎn)設(shè)為60、69、79、89。打開(kāi)學(xué)生成績(jī)表，在A19到D19單元格中輸入成績(jī)統(tǒng)計(jì)時(shí)的分段點(diǎn)。這里將用到統(tǒng)計(jì)函數(shù)FREQUENCY，它的作用是計(jì)算一組數(shù)據(jù)的頻率分布。

選定統(tǒng)計(jì)數(shù)據(jù)要輸入到的單元格D19至D23，輸入公式“=FREQUENCY($D$2:$D$16,$A$18:$D$18)”(這里的$D$2:$D$16、$A$18:$D$18中加入絕對(duì)引用的目的是避免以后在其他列復(fù)制該公式時(shí)出現(xiàn)自動(dòng)更改單元格地址的錯(cuò)誤)。由于該公式屬于數(shù)組公式，所以錄入完公式后必須按“Ctrl+Shift+Enter”組合鍵確認(rèn)，以便為其加上數(shù)組公式標(biāo)志“｛｝”。這樣在D19至D23單元格就可以迅速得到分段統(tǒng)計(jì)的結(jié)果。2.2 篩選出符合條件的學(xué)生數(shù)據(jù)

圖2

如當(dāng)評(píng)選優(yōu)秀學(xué)生時(shí)，要求統(tǒng)計(jì)出各科成績(jī)均大于等于85分的記錄，可使用高級(jí)篩選的方法迅速得到結(jié)果。采用圖二數(shù)據(jù)，具體操作方法：在D18至L19區(qū)域內(nèi)輸入篩選條件（如下圖中條件一所示），注意大于等于條件得用符號(hào)“〉=”表示。打開(kāi)“數(shù)據(jù)”菜單“篩選”菜單項(xiàng)中的“高級(jí)篩選”，在對(duì)話框中設(shè)置相應(yīng)參數(shù)如圖3所示：

圖3

圖4

使用“自動(dòng)篩選”功能時(shí)，值得注意的是對(duì)于篩選條件的設(shè)置，如何利用各條件的位置來(lái)表示“與”、“或”關(guān)系。如圖4中條件一，當(dāng)要篩選出同時(shí)滿(mǎn)足各科成績(jī)均大于等于85分時(shí)，各條件數(shù)據(jù)存在的是“與”關(guān)系，必須將各條件數(shù)據(jù)并排在同一行當(dāng)中。如圖4中條件二所示，當(dāng)要篩選出有不及格學(xué)科的學(xué)生時(shí)，各條件數(shù)據(jù)間存在的是“或”關(guān)系，各條件數(shù)據(jù)就只能錯(cuò)行排列。

2.3 快速得到中國(guó)式總成績(jī)排名

同樣以表二中的數(shù)據(jù)為例，如要以總成績(jī)進(jìn)行排名，首先可利用求函數(shù)得出總成績(jī)，具體操作：首先利用公式得到總成績(jī)。先選中第一位同學(xué)的成績(jī)單元格區(qū)域D2至L2，另外再在右側(cè)多選一個(gè)單元格M2，用于顯示總成績(jī)。點(diǎn)擊常用工具欄中的∑按鈕，便可在多選定的空白單元格，即M2中得到總分，之后再利用Excel中的公式復(fù)制，得出所有記錄的總成績(jī)。

接下來(lái)將所有記錄按總成績(jī)降序排列：選定所有記錄區(qū)域即A2至M16，利用“數(shù)據(jù)”菜單中的“排序”菜單項(xiàng)，設(shè)置按總成績(jī)所在M列為主關(guān)健字，以降序方式排列。

在 O2中輸入數(shù)字 1，在 O3中輸入公式“=IF(M3=M2,O2,O2+1)”后回車(chē)確定，然后將公式復(fù)制到O4至 O16單元格，即可得到每條記錄相應(yīng)的名次。其中公式“=IF(M3=M2,O2,O2+1)”的含義是：如果M3單元格數(shù)據(jù)等于M2單元數(shù)據(jù)，則得到O2即上一記錄相同名次，如果M3與M2單元格數(shù)據(jù)不同，則名次是O2+1即上一條記錄的下一名。

以上操作完成后，再將O2至O16單元格復(fù)制后，選定N2至N16，用“編輯”菜單中的“選擇性粘貼”項(xiàng)中的“粘貼數(shù)值”功能將排序結(jié)果復(fù)制到N2至N16單元格，刪除O2至O16單元格。最后再按學(xué)號(hào)順序，將所有記錄按降序排列即可。以上排名方式屬中國(guó)式排名，可避免因使用RANK函數(shù)造成的美國(guó)式排名因總分相同而形成的名次間斷的問(wèn)題。

3 畢業(yè)生成績(jī)檔案打印

在成績(jī)管理中，打印畢業(yè)生成績(jī)檔案表是必不可少的一項(xiàng)工作。在成績(jī)檔案打印過(guò)程中，要求將一覽表中一條記錄顯示一行的形式改為以列的方式顯示，這需要用到Excel單元格數(shù)據(jù)引用和函數(shù)進(jìn)行科目匹配。具體做法如下：

首先，制作表頭，包括必有數(shù)據(jù)項(xiàng)：班級(jí)、學(xué)號(hào)、姓名。其中，學(xué)號(hào)可用插入控件的形式，插入一微調(diào)按鈕，并將其控件的最小值設(shè)為 1，最大值設(shè)為該班學(xué)號(hào)。這樣，就可以通過(guò)單擊微調(diào)按鈕來(lái)直接控制學(xué)號(hào)的變化了。另外，姓名數(shù)值單元格，可通過(guò)一數(shù)組公式“=INDEX([2007級(jí)學(xué)生成績(jī)表.xls]護(hù)理!$C$3:$C$17,D2)”，直接在數(shù)據(jù)源工作表中（如圖2所示），尋找出與成績(jī)檔案表中D2單元格即當(dāng)前顯示學(xué)號(hào)相匹配的學(xué)生姓名。這樣一來(lái)，就實(shí)現(xiàn)了學(xué)號(hào)與姓名的同步滾動(dòng)顯示。

填充數(shù)據(jù)。根據(jù)教學(xué)計(jì)劃，將所有課程名稱(chēng)依次填入從A4開(kāi)始的第一列單元格中，同時(shí)必須保證輸入的課程名與數(shù)據(jù)源工作表中的課程名稱(chēng)絕對(duì)一致。如圖5所示，在可以打印區(qū)域外，列出查找條件，當(dāng)前查詢(xún)學(xué)生姓名為“劉小紅”，其中姓名引用自G2單元格。

圖5

顯示第一期成績(jī)。在成績(jī)套取中將使用到一個(gè)重要的函數(shù)DGET，其作用是從列表或數(shù)據(jù)庫(kù)的列中提取符合指定條件的單個(gè)值。語(yǔ)法：DGET(database,field,criteria)，其中Database 是構(gòu)成列表或數(shù)據(jù)庫(kù)的單元格區(qū)域。Field是指定函數(shù)所使用的數(shù)據(jù)列，Criteria為一組包含給定條件的單元格區(qū)域。在B4單元格中，輸入公式“=DGET([2007級(jí)學(xué)生成績(jī)表.xls]護(hù)理!$C$2:$L$17,A4,$J$6:$J$7)”，意思是在“2007級(jí)學(xué)生成績(jī)表”工作簿的“護(hù)理”工作表中，以姓名列與第一期成績(jī)所在單元格，即C2: L17作為構(gòu)成數(shù)據(jù)庫(kù)的單元格區(qū)域。在其中的指定數(shù)據(jù)列，即要生成的成績(jī)檔案工作簿中的A4所顯示的科目所在列，查找出包含指定條件，即與同一工作簿中$J$6:$J$7單元格所顯示的條件相匹配的單元格數(shù)據(jù)。接下來(lái)，只需將B4單元格中公式的通過(guò)填充柄往下復(fù)制即可顯示出第一期所有科目的成績(jī)。但是因?yàn)樵趶?fù)制公式中，如果不屬于第一期的科目，即沒(méi)有滿(mǎn)足“field”或“criteria”的記錄，所對(duì)應(yīng)的單元格會(huì)返回錯(cuò)誤值#value!。為避免這一情況，可在B4單元格輸入一嵌套函數(shù)“=IF(ISERR(DGET([2007級(jí)學(xué)生成績(jī)表.xls]護(hù)理!$C$2:$L$17,A4,$J$6:$J$7)),"",DGET([2007級(jí)學(xué)生成績(jī)表.xls]護(hù)理!$C$2:$L$17,A4,$J$6:$J$7))”。其中應(yīng)用到了IF函數(shù)與Iserr判斷錯(cuò)誤函數(shù)，意為如果當(dāng)DGET函數(shù)反回錯(cuò)誤值#value!，則單元格顯示為空，否則，則顯示為DGET函數(shù)的返回值。

第二期及以后各期成績(jī)顯示。由于DGET函數(shù)在本例使用中，必須滿(mǎn)足指定條件，即匹配與給定姓名一致的記錄，故要求每期記錄前必須出現(xiàn)學(xué)生姓名。在套取得二期成績(jī)時(shí)，我們可在數(shù)據(jù)源工作表中的第二期成績(jī)前加上一空白列，然后將姓名列復(fù)制進(jìn)去。之后在C4單元格的函數(shù)引用中，只需要將DGET函數(shù)的database改為第二期成績(jī)所在區(qū)域另加之前加入的姓名列即可。以后各期成績(jī)的套取同理操作。

本文通過(guò)使用 Excel的部分?jǐn)?shù)據(jù)處理功能以及內(nèi)置函數(shù)，實(shí)現(xiàn)了學(xué)校在管理學(xué)生成績(jī)中必需的學(xué)生成績(jī)錄入、成績(jī)統(tǒng)計(jì)匯總、畢業(yè)生成績(jī)檔案打印等成績(jī)管理工作中的重要環(huán)節(jié)操作，本方法的優(yōu)點(diǎn)為：簡(jiǎn)明易懂，易于操作，適用性較廣，可適用于各類(lèi)專(zhuān)業(yè)與非專(zhuān)人人員使用，能滿(mǎn)足各學(xué)校對(duì)于成績(jī)管理不同要求，有效地提高了管理工作效率，并且降低了使用數(shù)據(jù)庫(kù)進(jìn)行軟件開(kāi)發(fā)的投入成本。

[1] Excel Home.Excel函數(shù)與公式實(shí)戰(zhàn)技巧精粹[M].北京:人民郵電出版社，2008.

[2] Excel Home.Excel數(shù)據(jù)處理與分析實(shí)戰(zhàn)技巧精粹[M].北京:人民郵電出版社,2008.

Excel in student achievement management application

In the management of the school day teaching, student achievement was undoubtedly one of the important aspects of management. This use of Excel's data processing capabilities, built-in functions and formulas can be input to achieve student performance, statistical performance distribution, screening student data, performance ranking, graduate performance file and print a series of work, easy to understand, can effectively improve the efficiency.

Excel; performance management; function; formula

G63

A

1008-1151(2012)03-0030-03

2012-01-13

黃偉（1980－），女，四川宜賓人，宜賓衛(wèi)生學(xué)校講師，從事計(jì)算機(jī)課程教學(xué)。