Excel中對身份證號碼的處理

蕭巍

摘要：在工作學習生活中，很多時候要在Excel工作表中輸入身份證號碼。在輸入身份證號碼時，經常會出現輸入的字符位數不符，輸入的數字自動轉換為科學計數法形式等問題。本文對身份證號碼的位數設置、輸入身份證號碼、提取出生年份性別等操作進行探討，從而闡明Excel中對身份證號碼的處理。

關鍵詞：Excel 身份證號碼 數據有效性 函數

中圖分類號：G634文獻標識碼：A文章編號：1009-5349（2017）15-0191-01

在工作學習生活中，很多時候要在Excel工作表中輸入身份證號碼。在輸入身份證號碼時，經常會出現輸入的字符位數不符，輸入的數字自動轉換為科學計數法形式等問題。 本文對身份證號碼的位數設置、輸入身份證號碼、提取出生年份性別等操作進行探討，從而闡明Excel中對身份證號碼的處理。

一、身份證號碼字符位數的限定

身份證號碼一般是15位或者是18位，現如今的新版身份證一般是18位字符。在輸入時，很容易多輸入或少輸入字符。有什么辦法限定其輸入的位數呢？可以使用數據有效性進行限定（本文就18位身份證號碼進行闡述，之后的操作也就18位身份證號碼闡述）。選定要輸入身份證號碼的單元格，執行“數據——數據有效性”命令，彈出數據有效性對話框，選擇“設置”選項卡，在“允許”列表框中選擇“文本長度”，在“數據”列表框中選擇“等于”，在“長度”文本框中輸入18，單擊“確定”即可。見圖1。

圖1

設置好后，若輸入不等于18位的身份證號碼，就會彈出“輸入值非法”對話框，禁止不符合要求的身份證號的輸入。

二、身份證號碼的輸入

在輸入身份證號碼時，比如輸入362101198701260018，回車確認后會變為362101E+17。這是因為在單元格內輸入的數字字符的位數超過11個字符時，Excel會將它轉換為科學計數法來處理，即轉換為362101E+17，其含義是362101乘以10的17次方。那么，遇到這種情況怎么解決呢？如何輸入才能得到我們需要的身份證號碼呢？有五種方法。第一種方法是在輸入身份證號碼時在其前面加上一個英文的單引號。其作用就是將該身份證號碼的屬性設置為“文本”屬性。即可讓身份證號碼完全顯示出來。第二種方法是將要輸入身份證號碼的單元格設置為“文本”格式，再輸入身份證號碼，則身份證號碼不會變為科學計數法形式。第三種方法是選中要輸入身份證號的單元格，執行“數據——數據有效性”命令，選擇“數字”選項卡，在“分類”列表框中選擇“自定義”選項，在“類型”列表框中選擇“@”，單擊“確定”。然后在該單元格中輸入身份證號碼即可。第四種方法操作同第三種方法，在”類型”文本框中輸入18個零。第五種方法是在需要輸入身份證號碼的單元格中輸入“ =‘身份證號碼”，這樣身份證號碼就會正常顯示，而不會變為科學計數。第五種方法其實是將身份證號碼表示成公式的值。

三、提取身份證號碼中的日期

在辦公室工作當中，很多時候要獲取員工的出生日期。而手頭沒有現成的出生日期數據，但員工一般都會留下身份證號碼，可以用MID函數將身份證號碼中的出生日期提取出來。假設在D2單元格有一個身份證號碼362101198701260018，可以在任意單元格輸入公式“=MID（B2，7，8）”，意思是在該身份證號碼中提取從左邊數第7個字符后的8個字符，而18位身份證號碼的第7個字符后的8個字符正好是身份證號碼主人的出生日期。這個公式的結果是19870126，即1987年1月26日。

四、提取身份證號碼中的性別

18位身份證號中倒數第二位數字如果是奇數則為代表身份證號的主人是男性，如果是偶數則代表其主人是女性。將身份證號碼中代表主人的性別提取出來分別要使用到If函數、Mod函數和Mid函數。假設在D2單元格有一個身份證號碼362101198701260018，可以在任意單元格輸入公式“=IF（MOD（MID（D2，17，1），2）=0，‘女，‘男）”。其含義是取出倒數第二位的數，然后跟2取余數，若余數為0則為女性，若不是則為男性。

五、結語

身份證號碼位數的限定要使用到數據有效性。輸入身份證號碼可以把其設置為文本格式，或者把其表示為一個公式的值。提取身份證號碼中的日期和性別要使用到相關函數。靈活地處理身份證號碼可以給我們的工作和學習帶來便利。

參考文獻：

[1]曾廣雄，吳秀英.計算機應用基礎項目化教程[M].西安電子科技大學出版社，2013.

責任編輯：于蕾