|
Excel在學(xué)籍信息管理中的妙用(2009-03-15 16:06:17)
本文來源《現(xiàn)代教育導(dǎo)報(bào)》綜合版 近年來，隨著各級(jí)各類學(xué)校辦學(xué)規(guī)模的不斷擴(kuò)大，學(xué)生數(shù)量急劇增加，有關(guān)學(xué)生的各種信息量也成倍增長(zhǎng)。為了做到學(xué)生信息管理規(guī)范化，實(shí)行學(xué)生學(xué)籍信息的計(jì)算機(jī)管理是可行而且必要的。但在現(xiàn)實(shí)生活中，很多學(xué)校并未配備專門的學(xué)籍管理軟件。購進(jìn)一套專門的管理軟件，費(fèi)用較昂貴。雖然現(xiàn)在能夠較容易地找到許多免費(fèi)版的學(xué)籍管理軟件，但是，由于每個(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é)籍信息庫框架。信息項(xiàng)目的設(shè)置，您可根據(jù)本校實(shí)際需要而定。利用單元格格式設(shè)置字體、圖案。
二、妙用“有效數(shù)據(jù)”
在默認(rèn)情況下，Excel對(duì)單元格的輸入是不加任何限制的。但為了保證輸入數(shù)據(jù)的正確性，可以為單元格組或單元格區(qū)域指定輸入的有效范圍。例如：為了保證身份證號(hào)是18位，可以為身份證號(hào)所在一列的單元格區(qū)域指定有效范圍。為了用下拉列表快速輸入“民族”、“政治面貌”，可以為“民族”、“政治面貌”所在列的單元格區(qū)域設(shè)置有效數(shù)據(jù)。
（一）設(shè)置身份證號(hào)的有效條件
1.設(shè)置有效條件
選定單元格區(qū)域。選取“數(shù)據(jù)”菜單的“有效數(shù)據(jù)”命令，打開“有效數(shù)據(jù)”對(duì)話框，單擊“有效數(shù)據(jù)”對(duì)話框中的“設(shè)置”標(biāo)簽。在“允許”下拉列框中，選擇允許輸入的內(nèi)容類型為“文本長(zhǎng)度”；在“數(shù)據(jù)”下拉列框中，選擇“等于”，“長(zhǎng)度”為18。
2.顯示警告信息
當(dāng)身份證號(hào)輸入不是18位時(shí)，顯示“錯(cuò)誤警告”。在“有效數(shù)據(jù)”對(duì)話框的“錯(cuò)誤警告”標(biāo)簽里選中“輸入無效數(shù)據(jù)時(shí)，顯示警告信息”復(fù)選框，并輸入要顯示的錯(cuò)誤提示信息。
（二）利用下拉列表快速輸入“民族”、“政治面貌”
方法同上。區(qū)別是：在“允許”下拉列框中，選擇允許輸入的內(nèi)容類型為“序列”；在“來源”文本框中輸入“漢族，蒙古族，回族，藏族，維吾爾族等”。注意：在對(duì)話框中選擇“提供下拉箭頭”復(fù)選框，最后單擊“確定”。
三、妙用身份證號(hào)碼提取個(gè)人信息
（一）提取“性別”
例如，在I3單元格內(nèi)輸入身份證號(hào)碼，在C3單元格輸入函數(shù)：=IF(MOD(MID(I3，17，1)，2)=1，“男”，“女”)。MID(I3，17，1)，表示在I3中從第17位開始提取1位字符。MOD(MID(I3，17，1)，2)=1，表示提取的字符除以2余數(shù)為1。IF(MOD(MID(I3，17，1)，2)=1，“男”，“女”)，表示所取字符除以2，如果余數(shù)為1，顯示男，否則顯示女。
（二）提取“出生日期”
在D3單元格輸入函數(shù)=MID(I3，7，4)&“-”&MID(I3，11，2)&“-”&MID(I3，13，2)，MID(I3，7，4)表示，在I3中從第七位開始提取4位字符&為文本連接符。
（三）自動(dòng)生成“年齡”
在E3單元格中輸入函數(shù)：=DATEDIF(D3，TODAY()，“Y”)。TODAY()，表示當(dāng)前日期。DATEDIF(D3，TODAY()，“Y”)，表示計(jì)算當(dāng)前日期與出生日期的年差。
（四）提取“生源地代碼”
在J3單元格輸入函數(shù)：=LEFT(I3，6)。LEFT(I3，6)，表示在I3中從左邊開始提取6位字符。
建立“代碼庫”工作表，輸入代碼及代碼名稱。并按“代碼”排序。在K3單元格中輸入函數(shù)：=LOOKUP(J3，代碼庫！$A$1：$A$192，代碼庫！$B$1：$B$192)。“代碼庫！$A$1：$A$192”表示絕對(duì)引用工作表“代碼庫”中單元格區(qū)域A1：A192 LOOKUP(J3，代碼庫！$A$1：$A$192，代碼庫！$B$1：$B$192)，表示在“代碼庫”工作表中單元格區(qū)域A1：A192的數(shù)據(jù)中查找“J3”單元格指定的數(shù)值，然后返回工作表“代碼庫”中單元格區(qū)域B1：B192中相同位置的數(shù)值。
四、自動(dòng)生成“班號(hào)”
五、妙用“條件函數(shù)”輸出帶有公式的空白表
當(dāng)“身份證號(hào)”為空時(shí)，用身份證號(hào)碼提取個(gè)人信息，返回“#VALUE”或“#N/A”，不美觀。I5為空時(shí)，E5、K5顯示效果。可用條件檢測(cè)函數(shù)IF將公式進(jìn)行修改，在E6單元格中輸入公式：=IF(I6=“”，“”，DATEDIF(D6，TODAY()，“Y”))，表示I6單元格為空時(shí)，結(jié)果是顯示效果為空。在I6單元格中輸入身份證號(hào)時(shí)，輸出結(jié)果為函數(shù)DATEDIF(D6，TODAY()，“Y”)的值。
六、快速錄入信息
單元格格式及函數(shù)設(shè)置完成后，可進(jìn)行信息的錄入。為提高錄入速度，可利用以下技巧：
（一）用“自動(dòng)填充柄”，錄入相同數(shù)據(jù)或具有增減可能的數(shù)據(jù)序列，復(fù)制公式
（二）“自定義數(shù)據(jù)格式”讓系統(tǒng)自動(dòng)添加數(shù)據(jù)共同的部分
Excel支持自動(dòng)填充功能。如果數(shù)據(jù)是不連續(xù)、無規(guī)則但具有共同部分的，自動(dòng)填充功能就不能完成任務(wù)了。這時(shí)，可用“自定義數(shù)據(jù)格式”。例如：字段“證書編號(hào)”為十位數(shù)“2007063***”，共同部分“2007063”可以選定要輸入“證書編號(hào)”的單元格區(qū)域。選擇“格式”→“單元格”，打開“單元格格式”對(duì)話框，單擊“數(shù)字”標(biāo)簽，選中“分類”下面的“自定義”選項(xiàng)，再在“類型”下面的方框中輸入“200706300#”，按下“確定”按鈕，“00#”是數(shù)字的預(yù)留位置。
以后，在上述單元格中，輸入數(shù)值“1”則顯示“2007063001”，輸入“147”顯示“2007063147”。
（三）自動(dòng)更正法輸入特殊文字
選擇“工具”→“自動(dòng)更正”，打開“自動(dòng)更正”對(duì)話框，在“替換”下面填入“SJ”，在“替換為”下面填入“××省××市”(不含引號(hào))，然后按“確定”按鈕。以后，只要在單元格中輸入SJ及后續(xù)文本(或按“Enter”鍵)后，系統(tǒng)會(huì)自動(dòng)將其更正為“××省××市”。
七、用“自動(dòng)篩選”進(jìn)行信息查詢
我們經(jīng)常在信息庫中查詢滿足一定條件的記錄，如查詢“張鵬雨”的信息。可執(zhí)行“數(shù)據(jù)——篩選——自動(dòng)篩選”，單擊“姓名”字段右下角的下拉箭頭，單擊“自定義”，篩選條件為“姓名=張鵬雨”，單擊“確定”，則顯示張鵬雨的信息。
我們經(jīng)常進(jìn)行大量的信息統(tǒng)計(jì)，如各班年齡分布情況、男女生情況、各民族情況、各類數(shù)據(jù)構(gòu)成比例等。這項(xiàng)工作是一項(xiàng)非常繁重的工作。我們可以利用Excel自帶的“數(shù)據(jù)透視表”功能為我們排憂解難。例如：我們對(duì)各班各年齡段人數(shù)進(jìn)行匯總。執(zhí)行“數(shù)據(jù)”——“數(shù)據(jù)透視表和數(shù)據(jù)透視圖”——“數(shù)據(jù)透視表”——“下一步”。當(dāng)出現(xiàn)“數(shù)據(jù)透視表和數(shù)據(jù)透視圖向?qū)?”時(shí)，我們可以點(diǎn)擊“布局”按鈕，接下來會(huì)出現(xiàn)“數(shù)據(jù)透視表和數(shù)據(jù)透視圖向?qū)?布局”，分別將“班號(hào)”、“年齡”、“姓名”字段按鈕拖到“行”、“列”、“數(shù)據(jù)”區(qū)域上，按一下“確定”就完成了。
以上以學(xué)籍管理為例講述的是一些常用功能的設(shè)置，您還可以舉一反三，運(yùn)用到其他信息管理中。
|
|
|
