巧用Excel的分列功能從身份證號碼中提取信息

☆ 陳建文

（泰興黃橋中學，江蘇泰興 225411）

筆者在平時的工作中遇到這樣的情況：每一屆學生畢業時都要打印畢業證書，需要的信息有學生的籍貫、出生年月、年齡以及性別等信息。這些信息如果手工輸入的話會花很大氣力，而且難免會出錯。那么,有沒有一個既省力又不會出錯的辦法呢？

答案是肯定的。因為每年高一新生入學注冊學籍時會采集每位學生的身份證號碼，另外高考報名時也會保存身份證號碼。其實，身份證號碼與一個人的性別、出生年月、籍貫等信息是緊密相連的，我們只要使用Excel從身份證號碼中提取相關信息就可以了。網上介紹的提取信息的方法幾乎都是使用函數，非常復雜，并且難以理解。其實我們使用Excel的“分列”功能經過一些簡單的操作就可以輕松得到我們需要的信息了。

在介紹操作方法和步驟之前，筆者先對身份證號碼的編排規則進行必要的說明。我們以18位的身份證號為例（因為新身份證號都是18位），從左向右第1、2兩位數字為身份證號碼注冊時所在省份，第3、4兩位數字為所在城市代碼，第5、6兩位為所在區縣代碼，第7～14位為出生年、月、日，第15、16位所在派出所代碼，第17位為性別代碼（奇數為男，偶數為女），第18位為校驗碼，一般由計算機隨機產生，用來檢驗身份證的正確性，可以是0觸9的數字，有時也用字母X表示。

圖1

下面筆者以圖2的數據為例，來介紹使用Excel的“分列”功能提取籍貫、出生年、月、日、性別的方法及步驟：

圖2

第一步，選中C列的數據，設置一下任意一種對齊方式，然后復制并將數據粘貼到D列，進行數據的備份，結果如圖3。因為身份證號碼分列后原來的身份證號碼就不存在了。

圖3

第二步，選中D列的數據，然后單擊“數據”菜單中的“分列”命令，就會出現“文本分列向導－3步驟之1”對話框，此時選擇其中的“固定寬度”選項，再單擊“下一步”。

第三步，此時進入“文本分列向導－3步驟之2”，也是關鍵的一個步驟。數據預覽區顯示了要分列的數據及水平方向的標尺，此時在要分列的位置單擊鼠標建立分列，拖動分列線可以改變分列位置，雙擊分列線可以清除分列線。使用以上的方法在正確的位置建立分列線。完成后結果如圖4。然后單擊“下一步”。

圖4

第四步，此時進入“文本分列向導－3步驟之3”，此步驟設置分列后每列數據的格式，方法是先選中“數據預覽區”的每一列，然后選中此對話框中右上角的“列數據格式”，本例中只有性別代碼列為“常規”格式，也就是“數值”，其余列都為“文本”格式。完成后結果如圖5。

圖5

第五步，單擊“完成”，D列的數據就分成了7列，然后在 D1、E1、F1、G1、I1 這五個單元格中分別輸入文字“籍貫碼”、“年”、“月”、“日”、“性別碼”，結果如圖6。

圖6

第六步，刪除H列和J列不需要的數據。然后在E列左側插入一列并在E1單元格中輸入“籍貫”文字，然后將“籍貫碼”列進行排序或篩選，并在E列填充相應的籍貫（籍貫碼對應的籍貫需要到網上查找）。結果如圖7。

圖7

第七步，出生“年”、“月”、“日”信息不需要進行任何操作。下面的任務就是利用公式對“性別碼”進行轉換產生“性別”。過程如下：在J1單元格中輸入文字“性別”，在 J2 單元格中輸入公式“＝IF(MOD(I2,2)＝1,″男″,″女″)”，不包括雙引號，另外公式中括號內的標點符號全為英文標點。下面再介紹一下公式的含義，其中MOD(I2,2)＝1的作用是判斷I2單元格的數字是否為奇數，然后用if函數進行判斷，如果MOD(I2,2)＝1為真，性別為“男”，如果MOD(I2,2)＝1為否，性別為“女”。最后選中J2單元格，并將鼠標指針移到J2單元格右下角的控制桿上，指針變為＋時雙擊鼠標，其他學生的性別也都產生了，效果如圖8。

圖8

第八步，選中所有數據，然后“復制”，再“選擇性粘貼”，選中其中的“數值”選項，再單擊“確定”。最后再將“性別碼”這一列刪除。

以上是使用Excel的“分列”功能從身份證號碼中提取籍貫、出生年、月、日、性別的方法及步驟。信息提取出來后，如果要計算年齡、設置出生日期格式等就非常簡單了。