用EXCEL從身份證號中提取信息並計算出退休年齡

身份證號碼隱藏著你的地區,出生日期,性別信息,如果建的表中有身份證號碼那麼出生日期、性別、年齡、工齡、退休日期就不用單獨錄入了,利用公式就可以。這樣方便錄入提高工作效率。

目前我們用的都18位的身份證號碼:1~6位為地區代碼,7~10位為出生年份(4位),11~12位為出生月份,13~14位為出生日期,第15~17位為順序號,並能夠判斷性別,奇數為男,偶數為女。18位為效驗位。

了解了上面的這些內容,接下來說說18位身份證號碼的信息提取。

工具/原料

電腦

方法/步驟

首選錄入信息時,錄入身份號那一列全部選中,設置單元格格式中選「文本」,避免出現圖2這種情況。

同上,D列全部選中,設置單元格格式中選「日期」。如圖。避免出現一串不能理解的數字。

在B3單元格輸入以下公式,提取性別:

=IF(MOD(MID(C3,17,1),2),"男","女")

公式中的MID(C3,17,1)部分意思是,從C3單元格第17位數字提取。計算結果是"3"。奇數為男,偶數為女。

MOD函數用於返回兩數相除的餘數,此例中的MOD函數第二參數使用2,就是用於判斷MID函數的結果是否能被2整除,能夠整除返回0,否則返回1。

IF函數根據MOD函數的計算結果,返回指定內容。如果MOD函數的計算結果為 1,IF 函數將返回「男」;如果MOD函數的計算結果為0,則返回「女」。

出生日期的提取使用以下公式來完成:

=--TEXT(MID(C3,7,8),"0-00-00")

公式中的MID(C3,7,8)部分,用於提取出C3單元格中身份證號碼的第7-14位。計算結果為"19850430",用TEXT函數將這個公式結果強制變成"1985-04-30",前面加了兩個減號用於減負運算,將文本"1985-04-30"變成真正的日期格式。

接下來看一下年齡的計算,E3單元格輸入以下公式:

=DATEDIF(D3,TODAY(),"y")

DATEDIF函數是一個隱藏函數,沒有出現在函數列表中,Excel中的公式自動完成功能也不會自動生成這個函數名稱,甚至在多個版本的幫助文件中都找不到這個函數的蹤影。

這個函數主要用於計算兩日期相差年月日數,利用該函數可計算相差的天數、月數和年數。對於DATEDIF函數的使用方法,咱們可以這樣理解:

DATEDIF(起始日期,結束日期,指定的匯總方式)

第三參數為所需信息的返回時間單位代碼。各代碼對應的含義如下:

第三參數代碼               函數返回值

"y"                              時間段中的整年數。

"m"                             時間段中的整月數。

"d"                              時間段中的天數。

"md"                           起始日期與結束日期天數的差。忽略日期中的月和年。

"ym"                           起始日期與結束日期月數的差。忽略日期中的日和年。

"yd"                            起始日期與結束日期天數的差。忽略日期中的年。

在這個公式中,DATEDIF函數返回D3單元格的出生日期到當前日期(TODAY())的整年數,也就是實際年齡。

如果將上面這條公式稍作改動,換成=DATEDIF(入司時間,TODAY(),"y"),那就是咱們常用到的工齡計算公式了。

在F3單元格用下面這個函數計算退休年齡:

=EDATE(D3,660+(B3="男")*60)

EDATE函數用於返回指定日期之前或之後的月數。在本例中,咱們利用了D3單元格已經計算出來的出生日期和B3單元格性別信息作為計算條件。用D3單元格的出生日期作為起始日期,指定的月數是660+(B3="男")*60這串字符初看起來可能有點費解,咱們分解一下:

660,也就是12個月/年*55年(女性退休年齡);(B3="男")*60這裡表示如果B3單元格性別是「男」,就再加上60個月(12個月/年*5年),否則就是660。

可以這樣理解:如果B3單元格性別是「女」,按=EDATE(D3,660)計算退休日期,如果B3單元格性別是「男」,則按=EDATE(D3,660+60)計算退休日期。

本文內容整理自網絡, 文中所有觀點看法不代表淘大白的立場