Excel在學籍信息管理中的妙用

Excel在學籍信息管理中的妙用(2009-03-15 16:06:17)

標籤：電腦單元格數據透視excel教育

分類：美文採擷

本文來源《現代教育導報》綜合版

　　近年來，隨著各級各類學校辦學規模的不斷擴大，學生數量急劇增加，有關學生的各種信息量也成倍增長。為了做到學生信息管理規範化，實行學生學籍信息的計算機管理是可行而且必要的。但在現實生活中，很多學校並未配備專門的學籍管理軟體。購進一套專門的管理軟體，費用較昂貴。雖然現在能夠較容易地找到許多免費版的學籍管理軟體，但是，由於每個學校都有自己的管理特點和要求，因此這些軟體在使用中總會存在一些不便之處。自己動手，用Excel建立一套適於學校具體需求的學籍信息管理系統是一個不錯的選擇。Excel功能強大，操作又很方便。它比用資料庫系統開發的軟體，在使用上更習慣，修改起來也更方便。　　一、建立學籍信息庫框架　　首先，我們打開一個新的Excel表，建立一個學籍信息庫框架。信息項目的設置，您可根據本校實際需要而定。利用單元格格式設置字體、圖案。　　二、妙用「有效數據」　　在默認情況下，Excel對單元格的輸入是不加任何限制的。但為了保證輸入數據的正確性，可以為單元格組或單元格區域指定輸入的有效範圍。例如：為了保證身份證號是18位，可以為身份證號所在一列的單元格區域指定有效範圍。為了用下拉列錶快速輸入「民族」、「政治面貌」，可以為「民族」、「政治面貌」所在列的單元格區域設置有效數據。　　（一）設置身份證號的有效條件　　1.設置有效條件　　選定單元格區域。選取「數據」菜單的「有效數據」命令，打開「有效數據」對話框，單擊「有效數據」對話框中的「設置」標籤。在「允許」下拉列框中，選擇允許輸入的內容類型為「文本長度」；在「數據」下拉列框中，選擇「等於」，「長度」為18。　　2.顯示警告信息　　當身份證號輸入不是18位時，顯示「錯誤警告」。在「有效數據」對話框的「錯誤警告」標籤里選中「輸入無效數據時，顯示警告信息」複選框，並輸入要顯示的錯誤提示信息。　　（二）利用下拉列錶快速輸入「民族」、「政治面貌」　　方法同上。區別是：在「允許」下拉列框中，選擇允許輸入的內容類型為「序列」；在「來源」文本框中輸入「漢族，蒙古族，回族，藏族，維吾爾族等」。注意：在對話框中選擇「提供下拉箭頭」複選框，最後單擊「確定」。　 　三、妙用身份證號碼提取個人信息　　（一）提取「性別」　　例如，在I3單元格內輸入身份證號碼，在C3單元格輸入函數：=IF(MOD(MID(I3，17，1)，2)=1，「男」，「女」)。MID(I3，17，1)，表示在I3中從第17位開始提取1位字元。MOD(MID(I3，17，1)，2)=1，表示提取的字元除以2餘數為1。IF(MOD(MID(I3，17，1)，2)=1，「男」，「女」)，表示所取字元除以2，如果餘數為1，顯示男，否則顯示女。　　（二）提取「出生日期」　　在D3單元格輸入函數=MID(I3，7，4)&「-」&MID(I3，11，2)&「-」&MID(I3，13，2)，MID(I3，7，4)表示，在I3中從第七位開始提取4位字元&為文本連接符。　　（三）自動生成「年齡」　　在E3單元格中輸入函數：=DATEDIF(D3，TODAY()，「Y」)。TODAY()，表示當前日期。DATEDIF(D3，TODAY()，「Y」)，表示計算當前日期與出生日期的年差。　　（四）提取「生源地代碼」　　在J3單元格輸入函數：=LEFT(I3，6)。LEFT(I3，6)，表示在I3中從左邊開始提取6位字元。 （五）自動生成「生源所在地」　　建立「代碼庫」工作表，輸入代碼及代碼名稱。並按「代碼」排序。在K3單元格中輸入函數：=LOOKUP(J3，代碼庫！$A$1：$A$192，代碼庫！$B$1：$B$192)。「代碼庫！$A$1：$A$192」表示絕對引用工作表「代碼庫」中單元格區域A1：A192 LOOKUP(J3，代碼庫！$A$1：$A$192，代碼庫！$B$1：$B$192)，表示在「代碼庫」工作表中單元格區域A1：A192的數據中查找「J3」單元格指定的數值，然後返回工作表「代碼庫」中單元格區域B1：B192中相同位置的數值。　　四、自動生成「班號」 在G3單元格中輸入公式：=MID(A3，1，2)&「級」&MID(A3，3，2)&「班」。　　五、妙用「條件函數」輸出帶有公式的空白表　　當「身份證號」為空時，用身份證號碼提取個人信息，返回「#VALUE」或「#N/A」，不美觀。I5為空時，E5、K5顯示效果。可用條件檢測函數IF將公式進行修改，在E6單元格中輸入公式：=IF(I6=「」，「」，DATEDIF(D6，TODAY()，「Y」))，表示I6單元格為空時，結果是顯示效果為空。在I6單元格中輸入身份證號時，輸出結果為函數DATEDIF(D6，TODAY()，「Y」)的值。　　六、快速錄入信息　　單元格格式及函數設置完成後，可進行信息的錄入。為提高錄入速度，可利用以下技巧：　　（一）用「自動填充柄」，錄入相同數據或具有增減可能的數據序列，複製公式 滑鼠對準E6單元格右下角的填充柄，向下拖動到E100單元格，即將公式=IF(I6=「」，「」，DATEDIF(D6，TODAY()，「Y」))複製到E7-E100單元格。　　（二）「自定義數據格式」讓系統自動添加數據共同的部分　　Excel支持自動填充功能。如果數據是不連續、無規則但具有共同部分的，自動填充功能就不能完成任務了。這時，可用「自定義數據格式」。例如：欄位「證書編號」為十位數「2007063***」，共同部分「2007063」可以選定要輸入「證書編號」的單元格區域。選擇「格式」→「單元格」，打開「單元格格式」對話框，單擊「數字」標籤，選中「分類」下面的「自定義」選項，再在「類型」下面的方框中輸入「200706300#」，按下「確定」按鈕，「00#」是數字的預留位置。　　以後，在上述單元格中，輸入數值「1」則顯示「2007063001」，輸入「147」顯示「2007063147」。　　（三）自動更正法輸入特殊文字　　選擇「工具」→「自動更正」，打開「自動更正」對話框，在「替換」下面填入「SJ」，在「替換為」下面填入「××省××市」(不含引號)，然後按「確定」按鈕。以後，只要在單元格中輸入SJ及後續文本(或按「Enter」鍵)後，系統會自動將其更正為「××省××市」。　 （四）查找替換法快速輸入符號　 有時要多次輸入一些字或特殊符號(如山東省濟南市)，非常麻煩，這時可先在需要輸入這些字元的位置上輸入一個代替的字母(如S），等表格製作完成後，執行「編輯」→「替換」，打開「替換」對話框，在「查找內容」下面的方框中輸入代替的字母「S」，在「替換值」下面的方框中輸入「山東省濟南市」，然後按「全部替換」按鈕。　　七、用「自動篩選」進行信息查詢　　我們經常在信息庫中查詢滿足一定條件的記錄，如查詢「張鵬雨」的信息。可執行「數據——篩選——自動篩選」，單擊「姓名」欄位右下角的下拉箭頭，單擊「自定義」，篩選條件為「姓名=張鵬雨」，單擊「確定」，則顯示張鵬雨的信息。 八、用「數據透視表」匯總數據　　我們經常進行大量的信息統計，如各班年齡分布情況、男女生情況、各民族情況、各類數據構成比例等。這項工作是一項非常繁重的工作。我們可以利用Excel自帶的「數據透視表」功能為我們排憂解難。例如：我們對各班各年齡段人數進行匯總。執行「數據」——「數據透視表和數據透視圖」——「數據透視表」——「下一步」。當出現「數據透視表和數據透視圖嚮導3」時，我們可以點擊「布局」按鈕，接下來會出現「數據透視表和數據透視圖嚮導-布局」，分別將「班號」、「年齡」、「姓名」欄位按鈕拖到「行」、「列」、「數據」區域上，按一下「確定」就完成了。　　以上以學籍管理為例講述的是一些常用功能的設置，您還可以舉一反三，運用到其他信息管理中。
推薦閱讀：