Excel中對身份證號碼的處理

蕭巍

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

關(guān)鍵詞：Excel 身份證號碼 數(shù)據(jù)有效性 函數(shù)

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

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

一、身份證號碼字符位數(shù)的限定

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

圖1

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

二、身份證號碼的輸入

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

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

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

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

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

五、結(jié)語

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

參考文獻：

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

責任編輯：于蕾