學電腦知識

身份證自動提取, Excel還能這么玩!

學電腦知識 http://www.nnyvff.tw 2018-09-19 11:57 出處:網絡 編輯:@學電腦
身份證是一組有規律的號碼,具體分割應該是:XXXXXXXXXXXXXXXXXX的格式。這里解釋一下,第一組和第二組共六個數字代表的是戶籍所在地代碼,第三組數據是出生年份,第四組兩位數代表出生月份,第五組兩位數代表出生日

身份證是一組有規律的號碼,具體分割應該是:XXX XXX XXXX XX XX XX X X的格式。這里解釋一下,第一組和第二組共六個數字代表的是戶籍所在地代碼,第三組數據是出生年份,第四組兩位數代表出生月份,第五組兩位數代表出生日期,倒數第二位則是性別判斷數字(偶數為女性,奇數為男性),在統計信息的時候,可能只登記了姓名和身份證號碼,如何能夠快速的將生日、性別、年齡一一提取計算出來,這是很多人都想知道的。而這些,都可以通過Excel公式來實現快速自動提取計算。

首先新建一個Excel表格,右鍵單擊要填寫身份證號碼的列(或者Ctrl+A整個表格),選擇“設置單元格格式”。

身份證自動提取, Excel還能這么玩!

在彈出對話框內,點擊“數字”中的“文本”確定。注意這個操作一定要在復制身份證數字前操作。

身份證自動提取, Excel還能這么玩!

如表格中所示,在姓名和身份證后的同行單元格內,輸入公式:“=MID(B2,7,4)&"年"&MID(B2,11,2)&"月"&MID(B2,13,2)&"日"”,注意,公式不包括中文半角雙引號,手動輸入時也要留意,所有符號全部為英文半角字符。另外,公式中“B2”為身份證所在單元格,具體到自己的表格中要根據情況變換。這里要解釋一下,公式中的MID函數是Excel提供的一個“從字符串中提取部分字符”的函數命令,具體使用格式在EXCEL中輸入MID后會出現提示。這個公式的大概意思是,提取B2單元格第7位后4個數字標記為年,第11位后2個數字標記為月,第13位后2個數字為日,由此組成出生日期。

身份證自動提取, Excel還能這么玩!

判斷男女則是根據身份證號的第17位數字決定,所以我們在后面的單元格中單擊輸入公式:“=IF(MID(B2,17,1)/2=TRUNC(MID(B2,17,1)/2),"女","男")”,大意為根據指定的條件(本例中即B2單元格第17位)來判斷其“真”(TRUE)、“假”(FALSE)(注:男、女),這里根據奇數、偶數來判斷,所謂單數即男性,偶數為女性。

身份證自動提取, Excel還能這么玩!

最后是年齡,一般使用公式:“=YEAR(NOW())-YEAR(C2)”(本文中C2單元格為出生日期),但是這個公式第一要建立在之前我們提取出生日期的單元格數據上,而且這個公式也僅僅是以年份相減得出的年齡,說起來有點像我們常說的“虛歲”,這個在人事工作中是不科學的。最精確的公式應為:“=DATEDIF(TEXT(MID(B2,7,6+2*(LEN(B2)=18)),"#-00-00"),NOW(),"y")”,DATEDIF函數其實是Excel隱藏函數,在幫助和插入公式里面沒有。公式主要作用是返回兩個日期之間的年月日間隔數,我們也正是借此來精確計算實際年齡。

身份證自動提取, Excel還能這么玩!

如此一來,通過三組不同的函數,我們可以依靠著身份證信息快速提取出生日期和性別,并能精確計算出當事人年齡,為統計錄入節約大量時間。


0

精彩評論

双色球26选5开奖结果