轉Excel表格中根據身份證號碼自動填出生日期、計算年齡

06-21

Excel表格中根據身份證號碼自動填出生日期、計算年齡、錄入男女的函數公式

18位身份證號碼轉換成出生日期的函數公式：如果E2中是身份證,在F2中求出出生日期，F2=DATE(MIDB(E2,7,4),MIDB(E2,11,2),MIDB(E2,13,2))

自動錄入男女：=IF(MOD((IF(LEN(e2)=18,MID(e2,17,1),MID(e2,15,1))) ,2)=0,"女","男")

15/18位都可以的公式：轉換出生日期：=IF(LEN(e2)=18,TEXT(MID(e2,7,8),"#-00-00"),"19"&TE XT(MID(e2,7,6),"#-00-00"))

自動錄入男女：=IF(E2="","",IF(MOD(RIGHT(LEFT(E2,17),1),2)=0,"女","男"))

計算年齡（新舊身份證號都可以）：=IF(AND(E2=""),"",IF(MIDB(E2,7,2)="19",107-MIDB(E2 ,9,2),107-MIDB(E2,7,2)))

WPS表格提取身份證詳細信息

前些天領導要求統計所有員工的性別、出生日期、年齡等信息，並且要得很急。而我們單位員工人數眾多。

短時間內統計相關信息並且輸入計算機幾乎是不太可能的。幸好在以前的一份金山表格中我們曾經統計有所有員工的身份證號碼，而身份證中正有我們所需要的性別、出生日期、年齡等信息的。所以。

乾脆，還是直接在金山表格中從身份證號碼提取相關的信息吧。

身份證號放在A2單元格以下的青島DNA檢測單位區域。我們需要從身份證號碼中提取性別、出生日期、年齡等相關信息。由於現在使用的身份證有15位和18位兩種。所以，在提取相關信息時。

首先應該判斷身份證號碼的數字個數，然後再區別不同情況進行相關處理。

一、身份證號的位數判斷

在B2單元格輸入如下公式「=LEN($A2)」。

回車後即可得到A2單元格身份證號碼的數字位數。

如圖1所示。LEN($A2)公式的含義是求出A2單元格字元串中字元的個數。由於當初身份證輸入時就是以文本形式輸入的。

所以用此函數正可以很方便地求到身份證號碼的位數。

二、判斷男女性別

第一步：提取性別識別數字。如果是18位身份證，那麼倒數第二位就是性別判斷的識別數字，奇數為男。

偶數則為女。而如果是15位身份證，那麼倒數第一位則是性別識別數字，同樣奇數為男，偶數為女。所以首先應該提取這兩種身份證的性別識別數字。在C2單小孩子在肚子里可以做DNA嗎？元格輸入公式「=IF(B2=18,MID(A2,17,1),RIGHT(A2,1))」。

回車後，立刻可以得到相應的識別數字了。如圖2所示。

函數MID(A2,17,1)的作用是從A2單元格字元串的第17個字開始截取1個字元。

這正好是倒數第二個字元；函數RIGHT(A2,1)的作用則是從A2單元格字元串的右側開始截取1個字元，也就是倒數第一個字元。至於IF函數。

則進行相應的判斷。公式「=IF(B2=18,MID(A2,17,1),RIGHT(A2,1))」的作用是「如果B2單元格等於18，那麼從A2單元格字元串的第17位開始截取1個字元。

否則從A2單元格字元串的右側截取1個字元」。

第二步：根據識別數字判斷男女性別，在D2單元格輸入公式「=IF(ISEVEN($C2)=TRUE,"女","男")」，回車後就可以得到的結果了。如圖3所示。

此處函數ISEVEN($C2)的作用是判斷C2單元格的數字是否為偶數，如果是，則返回結果TRUE，否則返回結果FALSE。因此，公式「=IF(ISEVEN($C2)=TRUE,"女","男")」的含義就是如果C2單元格數字為偶數，那麼在當前單元格顯示「女」，否則顯示

三、出生日期提取

第一步：提取數字型出生日期。在E2單元格輸入公式「=IF($B2=18,MID($A2,7,8),"19"&MID($A2,7,6))」並回車，如婚後親子鑒定是否負法律責任圖4所示。這種形式的出生日期我們並不常用，此處提取出來只是為了我們下一步計算年齡時要用，也是一個輔助列。"19"&MID($A2,7,6)的意思是把字元「19」和MID($A2,7,6)截取到的6個字元合併在一起成為一個新的字元串。其它的，就不用再解釋了吧？

第二步：提取帶連接符的出生日期。在F2單元格輸入公式「=IF($B2=18,MID($A2,7,4)&"-"&MID($A2,11,2)&"-"&MID( $A2,13,2),"19"&MID($A2,7,2)&"-"&MID($A2,9,2)&"-"&MI D($A2,11,2))」並回車，如圖5所示，可以得到我們常用的如「1977-08-11」的格式。此處用到的函數在前面已經有過解釋，所以不再羅嗦了。

四、公式計算年齡

在G2單元格輸入公式「=YEAR(NOW())-LEFT(E2,4)」並回車，年齡計算就這麼簡單，用當前的年份減去出生的年份就行了。如圖6所示。函數YEAR(NOW())可以返回當前的年數「2007」，而LEFT(E2,4)則從E2單元格的左側截取4個字元，也就是出生年份。

最後。

選中B2:G2單元格。

向下拖動填充句柄至最後一行。

那麼我們所需要的所有數據就有了，再選中B列、C列、E列，並點擊右鍵，在彈出的右鍵菜單中點擊「隱藏」命令，將這些輔助列隱藏上海親子鑒定大學起來，就可以得到一份完整的表格了。

很簡單吧？利用WPS的函數功能，只需要設置一下相應的參數。

把一個個隱藏在身份證號碼里的信息提取出來，完成上面所有操作用時也不到半小時，將所得到的表格美化一下，就可以向領導交差了。