由身份證號碼分析出生日期及性別 EXCEL表格自動生成

06-20

EXCEL表格自動生成

身份證包含了大量信息，如發證地址、出生日期、性別等，可以通過設置公式自動將出生日期和性別提取出來。設置方法如下：

(1)提取出生日期

身份證號碼通常是15位或18位，

1）若身份證號碼為15位，如某人的身份證號碼為「310112570416314」，說明他是1957年4月16日出生，如果出生的年份用M表示，月份用N表示，日期用P表示，用文本連接符將其連接起來就是「=＂19＂&M&＂年＂&N&＂月＂&P&＂日＂」。為了從身份證號碼中提取有關的信息，需要用一個文本函數"MID」，其格式為：MID(text,start_num,num_chars)，表示的意思是：MID（原來的字串，開始位置，字元的個數）。設原來字串即身份證號碼為R。則：年份M=MID（R，7，2），表示從第7個字元開始算起取2個，即取「57」。月份N=MID（R，9，2），表示從第9個字元開始算起取2個，即取「04」。日期P=MID（R，11，2），表示從第11個字元開始算起取2個，即取「16」。

這時上述函數就變為：="19"&MID(B2,7,2)&"年"&MID(B2,9,2)&"月"&MID(B2,11,2)&"日"

上式表示的意思是：「19」加上從B2單元格中的第7位開始取出2位的數值加上「年」，再加上B2單元格中從第9位開始取出2位的數值加上「月」，再加上B2單元格中從第11位開始取出2位的數值加上「日」。在C2中輸入公式後，打回車，然後拉動C2單元格的右下角，得出如圖2-72所示結果。

圖2-72

2）身份證號碼為18位，因為不需要再加年了，只需將公式改為「=MID(B9,7,4)&"年"&MID(B9,11,2)&"月"&MID(B9,13,2)&"日"」即可，如圖2-73所示。

圖2-73

3）若15位和18位的混合在一起。

方法一：可以將其排序，將15位的和18位的分開成兩類，分別用上述函數即可。方法二：不進行新的排序，再引入判別函數IF，對字元串進行判別，若是15位，則用前述函數，否則用後面的函數。公式為：=IF(LEN(B2)=15,」19」&MID(B2,7,2)&」年"&MID(B2,9,2)&」月"&MID(B2,11,2)&」日",MID(B2,7,4)&」年"&MID(B2,11,2)&」月"&MID(B2,13,2)&」日")。上式的含義是：若B2單元格中是15個字元時，用含有「19」的函數運算，否則用後面的函數運算。如圖2-74所示。「LEN」是確定單元格中字元串的個數的函數，既可以確定數值的個數，也可以確定文字的個數。

圖2-74

(2)確定男女性別

18位身份證號碼中的倒數第二位是用來確定性別的，單數為男，雙數則為女。要確定性別，用函數MID，該函數表示從字元串指定位置開始取出的字元位數，如「MID(B2,17,1),2)」表示從B2單元格中的字元中，從左邊數第17位開始，取出一位數值。再用函數MOD判別是奇數還是偶數。在D2中輸入的函數為：「=IF(MOD(MID(B2,17,1),2)=1,"男","女")」。公式的含義是：在B2單元格中取出倒數第二位，然後除以2，當餘數是1時，則為男，否則為女。然後下拉進行公式的填充。如圖2?75所示。也可以在D2單元格中輸入公式：「=(IF(B2="","",IF(MOD(MID(B2,17,1),2)=1,"男","女")))」，其含義是，當B2單元格為空時，顯示為空，否則再進行性別的判斷。如果15位和18位混合在一起，可以用公式：「=(IF(B2="","",IF(MOD(IF(LEN(B2)=15,MID(B2,15,1),MID(B2,17,1)),2)=1,"男","女")))」。當B2單元格為空時，顯示為空，否則，當B2單元格為15位時，取15位的最後一位（身份證號為15位的最後一位表示性別），否則，從18位身份證號第17開始取一位，進行性別的判斷。

圖2-75

(3)確定年齡

由身份證分析了出生年月後，利用判斷兩個時間間隔的函數「DATEDIF」可以確定年齡。在E2單元格中輸入函數：「=DATEDIF(C2,TODAY(),"Y")」，含義是，當前時間與C2單元格中的時間差，以「年」顯示在E2單元格中。然後向下拉動，將該公式一直填充到E11單元格中。如圖2-76所示。