利用EXCEL函數(shù)功能從身份證號提取學(xué)生基礎(chǔ)學(xué)籍信息

張繼瑄

摘要：該文通過對18位公民身份證號碼各字段含義的分析，明晰了各字段含義。應(yīng)用常用辦公軟件MS office軟件的電子表格Excel的VLOOKUP、LEFT、MID、DATE等函數(shù)功能，分別提取所需要籍貫、出生日期、年齡、性別等學(xué)籍身份信息。這樣就直接從身份證號中得到相關(guān)信息，不但不用各個項目重新讓學(xué)生自己填寫、管理人員重復(fù)錄入的繁瑣過程，更重要的是避免了錄入數(shù)據(jù)時出現(xiàn)的二次錯誤。利用Excel函數(shù)的功能不但提高了工作效率和管理水平，而且學(xué)籍信息的準(zhǔn)確性。

關(guān)鍵詞：學(xué)籍信息；Excel；身份證號；函數(shù)

中圖分類號：TP311 文獻(xiàn)標(biāo)識碼：A 文章編號：1009-3044（2014）33-7948-03

1 概述

在高校，學(xué)籍管理是學(xué)校對學(xué)生從入學(xué)到畢業(yè)整個培養(yǎng)過程的管理， 是高等學(xué)校教育管理的一個重要組成部分。它不但關(guān)系著學(xué)校及教育管理部門的決策，還直接關(guān)系到學(xué)生的學(xué)歷、學(xué)位證書的辦理以及就業(yè)、繼續(xù)深造正常進(jìn)行，這就要求學(xué)籍信息準(zhǔn)確無誤。在高校學(xué)籍管理中，需要學(xué)生的出生日期、性別、籍貫、年齡等信息，而身份證號就包括了這些信息，現(xiàn)已經(jīng)有了學(xué)生身份證號，如果讓學(xué)生逐個填寫、核對其出生日期、性別、籍貫、年齡等信息，是非常繁瑣的，不但工作量相當(dāng)大，并且在錄入時還可能出現(xiàn)第二次錯誤。現(xiàn)在利用Microsoft Office軟件電子表格Excel的函數(shù)功能就能輕松得到這些數(shù)據(jù)。電子表格軟件Excel功能強(qiáng)大，是“非專業(yè)編程人員解決復(fù)雜計算及繁雜數(shù)據(jù)管理”的理想工具軟件。該文通過實(shí)例，深入探討利用Excel軟件中的函數(shù)的一些功能，解決了工作中傳統(tǒng)錄入信息存在的弊病，找出了從身份證號中批量提取這些信息的方法，為更加準(zhǔn)確、高效的學(xué)籍管理提供了強(qiáng)有力的技術(shù)支撐。

2 身份證號碼含義解析

現(xiàn)在我國公民身份證號碼都是18位的，身份號碼是我國任意一個公民一個唯一的、不變的法定號碼。公民身份號碼是特征組合碼，由十七位數(shù)字本體碼和一位校驗碼組成。排列順序從左至右依次為：六位數(shù)字戶口所在地址碼，八位數(shù)字出生日期碼，三位數(shù)字順序碼和一位數(shù)字校驗碼。18位數(shù)值編碼依次分別代表的是：第1、2位表示省、市、自治區(qū)代碼：第3、4位表示市（地區(qū)、自治州、盟及直轄市所屬市轄區(qū)和縣）代碼；第5、6位表示縣、區(qū)代碼，7至14位分別表示出生的年月日日期，15、16位為順序碼，即所標(biāo)識的區(qū)域范圍內(nèi)，對同年、月、日出生的人員編定的順序號；17位為性別代碼，其中奇數(shù)代表男性，偶數(shù)代表女性；18位為效驗碼，是根據(jù)有關(guān)公式和前17位數(shù)計算出來的。知道了身份證號各個字段表示的含義，就可結(jié)合EXCEL的有關(guān)函數(shù)程序提取出我們首先要的數(shù)據(jù)。

3 Excel 函數(shù)在學(xué)籍管理中的應(yīng)用

3.1 從身份證號碼提取籍貫

籍貫是個人經(jīng)常要填寫的一項重要信息，其中身份證號碼的1-4位就表示出生地的代碼 ，各省市縣都有指定的代碼。

1） 首先建立區(qū)縣代碼庫，從國家統(tǒng)計局網(wǎng)（http：//www.stats.gov.cn/tjsj/tjbz/xzqhdm/）下載最新的行政區(qū)劃代碼，建立學(xué)生信息Excel工作簿，將其中的Sheet2工作表重新命名為“date”工作表，建立區(qū)縣代碼庫（date表）如圖1格式，按各省市區(qū)縣代碼依次排列。

3.4 從身份證號碼自動生成性別

身份證號17位為公民性別代碼，其中單數(shù)為“男性”、雙數(shù)為“女性”。選中“性別”列第一個單元格（H2） （如圖3） ，輸入以下函數(shù)：=IF（MOD（MID（A2，17，1），2）=1，"男"，"女"）回車后即得到A2單元格身份證號的性別。選中該單元格，單左下角出現(xiàn)黑色十字形的填充柄時，雙擊左鍵，將公式填充到該列最后一個非空單元格。

公式解釋：MID（A2，17，1），函數(shù)表示從A2單元格第17位為字符開始，提取字符長度為1的那個字符。MOD（MID（A2，17，1），2），對mid函數(shù)提取出的那個字符，用mod函數(shù)將其除以2，看余數(shù)為0還是1，MOD函數(shù)是返回兩數(shù)相除的余數(shù)，此為mid函數(shù)的返回數(shù)除以2，余數(shù)分別為0或者1，也就是整除或者不整除，數(shù)學(xué)中，偶數(shù)除2余數(shù)為0，奇數(shù)除2余數(shù)為1，mod和mid函數(shù)相配合，即可得知A2單元格的第17位是偶數(shù)還是奇數(shù)。如果是奇數(shù)則在單元格內(nèi)填寫“男”，若為偶數(shù)則為“女”。

4 結(jié)束語

函數(shù)作為Excel數(shù)據(jù)處理的一個最重要工具， 功能十分強(qiáng)大，在工作實(shí)踐中還可以有更多的應(yīng)用?？傊绻芨鶕?jù)具體情況巧妙地對各類函數(shù)加以綜合運(yùn)用，那函數(shù)確實(shí)是一種無比強(qiáng)大的工具，是“非專業(yè)編程人員解決復(fù)雜計算及繁雜數(shù)據(jù)管理”的理想工具軟件。 為各類數(shù)據(jù)的管理工作帶來了極大的便利。

上面介紹的只是筆者在學(xué)籍管理近十年來，Excel在學(xué)籍信息管理中應(yīng)用的一小部分，另外，其在成績管理、成績單打印、證書打印等方面應(yīng)用更加廣泛。筆者在以后的工作實(shí)踐中，會繼續(xù)嘗試?yán)肊xcel的強(qiáng)大功能在其它領(lǐng)域中的應(yīng)用。

參考文獻(xiàn)：

[1] Excel home.Exce高效辦公——學(xué)校管理[M].北京：人民郵電出版社，2008.

[2] 李繼兵..Exce公式與函數(shù)應(yīng)用范例[M].北京：中國青年出版社，2005.

[3] 鄭小玲，等.Exce在信息管理中的應(yīng)用[M].北京：人民郵電出版社，2004.

[4] 張永.Excel 函數(shù)在開放教育學(xué)籍管理中的應(yīng)用[M].南京：南京廣播電視大學(xué)學(xué)報，2014（3）.