Excel在高校學(xué)生檔案管理中的應(yīng)用

摘 要：介紹Excel在高校學(xué)生檔案管理中快速準(zhǔn)確處理數(shù)據(jù)的方法與技巧。如怎樣制作下拉列表框選擇所需數(shù)據(jù)；通過函數(shù)的使用保證輸入的身份證號(hào)是唯一的；通過身份證號(hào)自動(dòng)得出性別、出生日期、戶口所在地等。

關(guān)鍵詞：Eexcel；處理數(shù)據(jù)；函數(shù)

中圖分類號(hào)：TP3

Excel是微軟公司出品的Office系列辦公軟件中的一個(gè)組件，可以用來制作電子表格，完成數(shù)據(jù)計(jì)算，進(jìn)行數(shù)據(jù)的分析和預(yù)測等。在高校的學(xué)生檔案管理中應(yīng)用Excel，不僅可以提高錄入的速度，還可以保證錄入數(shù)據(jù)的準(zhǔn)確性，達(dá)到事半功倍的效果。

1 制作下拉列表框選擇所需數(shù)據(jù)

在學(xué)生檔案表中，有很多字段所需錄入的數(shù)據(jù)在某一范圍之內(nèi)，不可能有其它答案。比如“學(xué)習(xí)形式”只有“全日制”與“非全日制”兩種選擇；“戶口性質(zhì)”只有“農(nóng)村”、“縣鎮(zhèn)”、“城市”三種選擇。像這類數(shù)據(jù)的輸入，可以通過數(shù)據(jù)有效性來得到一個(gè)下拉列表，從列表中選擇正確的選項(xiàng)，從而避免了重復(fù)輸入浪費(fèi)大量時(shí)間。

現(xiàn)以“戶口性質(zhì)”為例，假設(shè)A1單元格為“戶口性質(zhì)”字段名，將光標(biāo)定位到A2單元格，單擊“數(shù)據(jù)”菜單，選擇“有效性”，在彈出的“數(shù)據(jù)有效性”對(duì)話框中，“允許”項(xiàng)選擇“序列”?！皝碓础表?xiàng)輸入“農(nóng)村，縣鎮(zhèn)，城市”，這里需要強(qiáng)調(diào)的是，每兩項(xiàng)之間的逗號(hào)應(yīng)用英文輸入法狀態(tài)下的逗號(hào)。單擊“確定”按鈕完成設(shè)置。然后使用自動(dòng)填充功能完成其它單元格的設(shè)置。

2 確保身份證號(hào)碼唯一性

人工錄入最大的缺點(diǎn)是不能保證數(shù)據(jù)準(zhǔn)確性。因?yàn)樯矸葑C號(hào)可以唯一標(biāo)識(shí)一個(gè)人，所以它具有唯一性，在輸入時(shí)不可能出現(xiàn)兩個(gè)一樣的身份證號(hào)。Excel提供了工具，可以提示人工錄入時(shí)出現(xiàn)的錯(cuò)誤，效果如圖1所示。

具體做法為：將光標(biāo)定位至C2單元格，單擊“數(shù)據(jù)”菜單，選擇“有效性”。在彈出的“數(shù)據(jù)有效性”對(duì)話框中，“樣式”項(xiàng)選擇“停止”；“允許”項(xiàng)選擇“自定義”；“公式”項(xiàng)輸入“=COUNTIF（C：C，C2）=1”。單擊“出錯(cuò)警告”標(biāo)簽卡，“標(biāo)題”項(xiàng)輸入“出錯(cuò)啦”；“錯(cuò)誤信息”項(xiàng)輸入“輸入號(hào)碼重復(fù)，請(qǐng)重新輸入！”。單擊“確定”按鈕完成設(shè)置。

3 根據(jù)身份證號(hào)自動(dòng)得出所在地、出生日期、年齡、性別等信息

居民18位身份證號(hào)碼各位的含義為：1-2位為省、自治區(qū)、直轄市代碼；3-4位為地級(jí)市、盟、自治州代碼；5-6位為縣、縣級(jí)市、區(qū)代碼；7-14位為出生年月日；15-17位為順序號(hào)，其中17位男為單數(shù)，女為雙數(shù)；18位為校驗(yàn)碼，0-9和X，由公式隨機(jī)產(chǎn)生。

使用Excel提供的函數(shù)可以自動(dòng)提取所在地、出生日期、年齡、性別等信息。當(dāng)我們?cè)凇吧矸葑C號(hào)碼”欄中輸入身份證時(shí)，后面所在地、出生日期、年齡、性別等信息就自動(dòng)生成了。

3.1 根據(jù)身份證號(hào)提取“所在地”信息

身份證號(hào)碼的前6位可以確定一個(gè)人的戶口所在地。所以我們可以使用excel提供的函數(shù)來提取前6位數(shù)據(jù)，然后再通過前6位數(shù)字具體對(duì)應(yīng)的地區(qū)表來查找戶口所在地。

首選我們應(yīng)該有一張表，這張表是身份證前6位數(shù)字所對(duì)應(yīng)的地區(qū)。這張表我們可以在互聯(lián)網(wǎng)上搜索到并轉(zhuǎn)化成excel表格。比如我們把這張表轉(zhuǎn)化成了excel表格，并給工作表取名叫“戶口所在地”。

在同一工作薄中，我們新建一個(gè)工作表叫“檔案表”，檔案表的A1、B1、C1、D1、E1分別是身份證號(hào)碼、所在地、出生年月、年齡、性別幾個(gè)字段。

光標(biāo)定位在“所在地”下面的B2單元格，在單元格中輸入公式：“=VLOOKUP（MID（A2，1，6），a，2，F(xiàn)ALSE）”。其中a是定義的一個(gè)名稱，具體做法為單擊“插入”菜單，選擇“名稱”下的“定義”，在彈出的“定義名稱”對(duì)話框中名稱輸入“a”，引用位置選擇“戶口所在地”工作表中的所有數(shù)據(jù)。

3.2 根據(jù)身份證號(hào)提取“出生日期”信息

身份證號(hào)碼中的7至14位是出生年月日，在C2單元格中輸入公式：“=TEXT（RIGHT（19MID（A2，7，LEN（A2）/2-1），8），\"#-##-##\"）”即可自動(dòng)求出出生日期。

3.3 根據(jù)身份證號(hào)提取“年齡”信息

3.4 根據(jù)身份證號(hào)提取“性別”信息

身份證號(hào)碼的第17位如果是奇數(shù)則性別為男，若是偶數(shù)，則性別為女。所以我們?cè)贓2單元格輸入公式“=IF（A2<>\"\"，IF（MOD（RIGHT（LEFT（A2，17）），2），\"男\(zhòng)"，\"女\"））”，則可以自動(dòng)求出性別。

當(dāng)我們?cè)凇吧矸葑C號(hào)碼”列輸入身份證號(hào)時(shí)，“所在地”、“出生年月”、“年齡”、“性別”列自動(dòng)求出數(shù)據(jù)，最終效果如圖2所示。

Excel函數(shù)功能強(qiáng)大，在高校學(xué)生檔案管理中有著舉足輕重的作用，我們應(yīng)充分發(fā)揮Excel優(yōu)勢，讓學(xué)生檔案管理工作更加地準(zhǔn)確、高效。同時(shí)，Excel還可以與其它軟件配合使用，如Word，Sql Server等。只要我們用心去探討、鉆研、實(shí)踐，一定會(huì)發(fā)揮出Excel更強(qiáng)大的功能。

參考文獻(xiàn)：

[1]周超.Excel在學(xué)生成績管理中的應(yīng)用[J].辦公自動(dòng)化，2006（15）.

[2]李春瑞.巧用Excel2000管理學(xué)生學(xué)籍檔案[J].唐山師范學(xué)院學(xué)報(bào)，2000（05）.

[3]鄧紅蘋.中文電子報(bào)表Excel在科研財(cái)務(wù)及管理中的應(yīng)用[J].長沙鐵道學(xué)院學(xué)報(bào)（社會(huì)科學(xué)版），2008（01）.

[4]張穎，趙連波.EXCEL在信息處理中的應(yīng)用[J].統(tǒng)計(jì)與咨詢，2002（04）.

作者單位：遼寧工程職業(yè)學(xué)院，遼寧鐵嶺 112008