淮海工學(xué)院 曹忠東

一、Excel在成績管理中的運(yùn)用

以下是從方法技巧方面對(duì)Excel在實(shí)際教學(xué)中運(yùn)用中實(shí)際操作的解析：

(一)創(chuàng)建的表格以及文本輸入

Excel的基本功能就是新建一個(gè)Excel工作簿，并在工作表sheet1中輸入相應(yīng)文本，創(chuàng)建表格(見圖1)。

1.輸入標(biāo)題及日期。首先在單元格A1中輸入相關(guān)標(biāo)題。

2.輸入相應(yīng)項(xiàng)目名稱。在第二行分別輸入學(xué)號(hào)、姓名、高數(shù)、等第、名次、各分?jǐn)?shù)段人數(shù)分布、有關(guān)指標(biāo)等項(xiàng)目名稱。

3.輸入固定數(shù)據(jù)。(1)首先學(xué)號(hào)輸入。這里可以使用Excel的自動(dòng)填充功能，輸入首個(gè)數(shù)據(jù)后，點(diǎn)擊ctrl按住鼠標(biāo)左鍵下拉，不需一個(gè)一個(gè)地輸。(2)其次，姓名輸入。根據(jù)學(xué)號(hào)輸入相應(yīng)的學(xué)生姓名。(3)分?jǐn)?shù)段設(shè)立。在G3單元格輸入“95—”在H3單元格輸入“100”，這樣一組一組地輸入。(4)指標(biāo)輸入。在J3至J7分別輸入“總分”、“平均分”、“標(biāo)準(zhǔn)差”、“及格率”、“優(yōu)秀率”等項(xiàng)目名稱。

4.設(shè)置形成表格。(1)選中整張表格，合并單元格，通過表格屬性設(shè)置好字體、字號(hào)等。(2)用“合并居中”功能分別將F2至F15、G2至H2、J2、K2、G10至K10、G11至K15單元格合并居中。(3)給表格框上合適的邊框。

(二)引用函數(shù)，創(chuàng)設(shè)公式

1.IF函數(shù)的使用。IF函數(shù)的功能之一就是能根據(jù)邏輯測(cè)試的真假值返回不同的結(jié)果，利用它可以達(dá)到輸入不同等級(jí)的分?jǐn)?shù)而并得到與指令相對(duì)應(yīng)的結(jié)果。假定85分以上為優(yōu)秀，60至84分為合格，低于60分為不及格。先用鼠標(biāo)選定區(qū)域D3，再在數(shù)據(jù)編輯欄中輸入相關(guān)函數(shù)“=IF(C3<60,”不及格”,IF(C3>=85,”優(yōu)秀”,”合格”))”然后回車或點(diǎn)擊編輯欄上的對(duì)勾即完成設(shè)置，最后用自動(dòng)填充功能輕松完成該列的所有設(shè)置。(見圖2)。

2.用RANK函數(shù)設(shè)定名次。RANK函數(shù)能返回指定數(shù)字在一列數(shù)字中的排位，利用它可以知道某一學(xué)生本次成績?cè)谌嗨械拿?。同樣先用鼠?biāo)選定區(qū)域E3，再輸入相關(guān)公式“=RANK(C3,$C$3:$C$27)”。這里的“C3”指全班第一個(gè)學(xué)生的成績所在的單元格，而“C27”指到最后一個(gè)學(xué)生所在的單元格。最后用自動(dòng)填充功能完成該列的所有設(shè)置。

3.用FREQUENCY函數(shù)統(tǒng)計(jì)各分?jǐn)?shù)段人數(shù)分布情況。FREQUENCY函數(shù)可以計(jì)算某個(gè)區(qū)域中數(shù)據(jù)的頻率分布情況。由于FREQUENCY函數(shù)返回一個(gè)數(shù)組，必須以數(shù)組公式的形式輸入。先選定區(qū)域I3：I9為輸出區(qū)域，輸入相關(guān)公式“=FREQUENCY(C3:C27,H3:H9)”再按Ctrl+Shift+Enter鍵即可得出在0-39、40-59、60-79、80～84、85-89、90-94、95-100各區(qū)間中的數(shù)據(jù)個(gè)數(shù)。

4.用SUM函數(shù)統(tǒng)計(jì)總分。先選中K3，輸入公式“=SUM(C3:C27)”后回車即可。

5.用AVERAGE函數(shù)統(tǒng)計(jì)平均分。選中K4，輸入公式“AVERAGE=(C3:C27)”后回車即可。

6.用STDEVP函數(shù)統(tǒng)計(jì)標(biāo)準(zhǔn)差。選中K5，輸入公式“STDEVP=(C3:C27)”后回車即可。

7.創(chuàng)設(shè)及格率計(jì)算公式。在K6中輸入=SUM(I3:I7)/SUM(I3:I9)即可算得及格率。

8.創(chuàng)設(shè)優(yōu)秀率計(jì)算公式。在K7中輸入=SUM(I3:I5)/SUM(I8:I9)即可算得優(yōu)秀率(假定優(yōu)秀臨界線為85分)。

至此，表格的所有公式都已設(shè)置完成。只要輸入單元測(cè)試的原始成績即可自動(dòng)生成學(xué)生的成績等第、所處的名次、各分?jǐn)?shù)段人數(shù)分布情況、全班總分、平均分、標(biāo)準(zhǔn)差、及格率、優(yōu)秀率。

(三)美化表格，形成網(wǎng)絡(luò)

以上介紹了表格的制作全過程，為使表格更加好用，還要進(jìn)行如下完善工作。

圖1

圖2

圖3

圖4

圖5

1.讓數(shù)據(jù)更準(zhǔn)確醒目。通過設(shè)置數(shù)據(jù)有效性及條件格式，來避免數(shù)據(jù)輸入時(shí)出錯(cuò)，通過給各個(gè)等級(jí)的分?jǐn)?shù)設(shè)置不同顏色，使數(shù)據(jù)更為醒目。點(diǎn)擊列編號(hào)C，選中整列。(1)設(shè)置數(shù)據(jù)有效性：在菜單欄上點(diǎn)擊“數(shù)據(jù)”下的“有效性”，在設(shè)置選項(xiàng)中選擇和填寫所需要的值，再按確定完成設(shè)置。這樣，在輸入原始成績時(shí)如輸入的數(shù)據(jù)不在有效值范圍，系統(tǒng)就會(huì)拒絕輸入，從而減少手誤。(2)設(shè)置條件格式：在菜單欄上點(diǎn)擊“格式”下的“條件格式”，在彈出的選項(xiàng)中選擇和填寫所需要的值(如圖3)，再按確定完成設(shè)置。

2.讓統(tǒng)計(jì)更直觀明了。利用“圖表向?qū)А惫δ?，在表中“分析”欄?nèi)創(chuàng)建自己喜歡的統(tǒng)計(jì)圖，使數(shù)據(jù)圖化，更為直觀明了。在工具欄上點(diǎn)擊“圖表向?qū)А卑此奶崾局鸩皆O(shè)置完成。

3.讓管理更方便實(shí)用。雙擊sheet1重命名為“單元一”，然后在用鼠標(biāo)左鍵點(diǎn)擊“單元一”的同時(shí)按Ctrl鍵并向右拖拽的方法復(fù)制其他單元測(cè)試所需的統(tǒng)計(jì)分析表，再重命名。完成這些之后，再在“單元一”這張工作表前插入一張新的工作表。把學(xué)生的學(xué)號(hào)、姓名復(fù)制到新工作表中，然后把各單元的成績欄鏈接到表中形成一張總表。就可以看到每位學(xué)生的各個(gè)單元成績情況了，既方便又實(shí)用。

二、Excel在學(xué)籍管理中的運(yùn)用

由于每個(gè)學(xué)校都有自己的管理特點(diǎn)和要求，因此這些軟件在使用中總會(huì)存在一些不便之處。自己動(dòng)手，用Excel建立一套適于學(xué)校具體需求的學(xué)籍信息管理系統(tǒng)是一個(gè)不錯(cuò)的選擇。Excel功能強(qiáng)大，操作又很方便。它比用數(shù)據(jù)庫系統(tǒng)開發(fā)的軟件，在使用上更習(xí)慣，修改起來也更方便。

(一)建立學(xué)籍信息庫框架

首先，我們打開一個(gè)新的Excel表，建立一個(gè)學(xué)籍信息庫框架—如圖4。信息項(xiàng)目的設(shè)置，您可根據(jù)本校實(shí)際需要而定。利用單元格格式設(shè)置字體、圖案。

(二)妙用“有效數(shù)據(jù)”

在默認(rèn)情況下，Excel對(duì)單元格的輸入是不加任何限制的。但為了保證輸入數(shù)據(jù)的正確性，可以為單元格組或單元格區(qū)域指定輸入的有效范圍，如圖5。

1.設(shè)置身份證號(hào)的有效條件

(1)設(shè)置有效條件

選定單元格區(qū)域。選取“數(shù)據(jù)”

菜單的“有效數(shù)據(jù)”命令，打開“有效數(shù)據(jù)”對(duì)話框，單擊“有效數(shù)據(jù)”對(duì)話框中的“設(shè)置”標(biāo)簽。在“允許”下拉列框中，選擇允許輸入的內(nèi)容類型為“文本長度”；在“數(shù)據(jù)”下拉列框中，選擇“等于”，“長度”為18。

(2)顯示警告信息

當(dāng)身份證號(hào)輸入不是18位時(shí)，顯示“錯(cuò)誤警告”。在“有效數(shù)據(jù)”對(duì)話框的“錯(cuò)誤警告”標(biāo)簽里選中“輸入無效數(shù)據(jù)時(shí)，顯示警告信息”復(fù)選框，并輸入要顯示的錯(cuò)誤提示信息。

2.利用下拉列表快速輸入“民族”、“政治面貌”

具體操作同上。區(qū)別是：在“允許”下拉列框中，選擇允許輸入的內(nèi)容類型為“序列”；在“來源”文本框中輸入“漢族，蒙古族，回族，藏族，維吾爾族等相關(guān)民族”。注意：在對(duì)話框中選擇“提供下拉箭頭”復(fù)選框，最后單擊“確定”。

3.妙用身份證號(hào)碼提取個(gè)人信息

提取“性別”、提取“出生日期”、自動(dòng)生成“年齡”、提取“生源地代碼”、自動(dòng)生成“生源所在地”。

4.自動(dòng)生成“班號(hào)”

在G3單元格中輸入公式：=MID(A3，1，2)&“級(jí)”&MID(A3，3，2)&“班”。

5.妙用“條件函數(shù)”輸出帶有公式的空白表

當(dāng)“身份證號(hào)”為空時(shí)，用身份證號(hào)碼提取個(gè)人信息，返回“#VALUE”或“#N/A”，不美觀?？捎脳l件檢測(cè)函數(shù)IF將公式進(jìn)行修改。

6.快速錄入信息

單元格格式及函數(shù)設(shè)置完成后，可進(jìn)行信息的錄入。為提高錄入速度，可利用以下技巧：

(1)用“自動(dòng)填充柄”，錄入相同數(shù)據(jù)或具有增減可能的數(shù)據(jù)序列，復(fù)制公式鼠標(biāo)對(duì)準(zhǔn)E6單元格右下角的填充柄，向下拖動(dòng)到E100單元格，即將公式=IF(I6=“”，“”，DATEDIF(D6，TODAY()，“Y”))復(fù)制到E7-E100單元格。

(2)“自定義數(shù)據(jù)格式”讓系統(tǒng)自動(dòng)添加數(shù)據(jù)共同的部分

Excel支持自動(dòng)填充功能。如果數(shù)據(jù)是不連續(xù)、無規(guī)則但具有共同部分的。這時(shí)，可用“自定義數(shù)據(jù)格式”。

7.用“自動(dòng)篩選”進(jìn)行信息查詢

我們經(jīng)常在信息庫中查詢滿足一定條件的記錄，可執(zhí)行“數(shù)據(jù)——篩選——自動(dòng)篩選”，單擊“姓名”字段右下角的下拉箭頭，單擊“自定義”，篩選條件為“姓名=張鵬雨”，單擊“確定”，則顯示張鵬雨的信息。

三、結(jié)束語

以上就是Excel在教學(xué)成績和學(xué)籍管理中的實(shí)際運(yùn)用操作，作為功能強(qiáng)大的辦公軟件，這些只是其操作的一小部分內(nèi)容，有更多的技術(shù)工作等著我們?cè)趯?shí)踐中去運(yùn)用。

[1]謝柏青,張健清,劉新元.Excel教程[M].北京:電子工業(yè)出版社,2003.

[2]何杰.Excel在教學(xué)管理工作中的運(yùn)用[J].大連水產(chǎn)學(xué)院報(bào),2005(07).

[3]申兆光.Excel在統(tǒng)計(jì)學(xué)教學(xué)中的運(yùn)用探討.現(xiàn)代商貿(mào)工業(yè),2008,10.

[4]楊金勝.探析網(wǎng)絡(luò)環(huán)境下計(jì)算機(jī)輔助教學(xué)[J].華商,2002,10.