在hr部門(mén)工作同學(xué)經(jīng)常會(huì)需要記錄員工的身份證號(hào)碼，然后需要手動(dòng)計(jì)算或者手動(dòng)查詢年齡，生肖戶籍地等信息，非常繁瑣。這次給大家分享只輸入身份證號(hào)碼就可以顯示你想要的信息。

性別判斷方法

獲取身份證號(hào)碼第17位數(shù)，然后除2的余數(shù)，奇數(shù)是男，偶數(shù)是女。

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

使用到的函數(shù)：

MID函數(shù)：從文本字符串中的指定位置起返回特定個(gè)數(shù)的字符

MOD函數(shù)：返回除法的余數(shù)

年齡獲取方式

=DATEDIF(--TEXT((LEN(A2)=15)*19&MID(A2,7,6+(LEN(A2)=18)*2),"0-00-00"),TODAY(),"y")&"周歲"&DATEDIF(--TEXT((LEN(A2)=15)*19&MID(A2,7,6+(LEN(A2)=18)*2),"0-00-00"),TODAY(),"ym")&"月"&DATEDIF(--TEXT((LEN(A2)=15)*19&MID(A2,7,6+(LEN(A2)=18)*2),"0-00-00"),TODAY(),"md")&"天"

使用到的函數(shù)：

DATEDIF函數(shù)：計(jì)算兩個(gè)日期之間的天數(shù)、月數(shù)或年數(shù)。此函數(shù)在用于計(jì)算年齡的公式中很有用。

LEN函數(shù)：返回文本字符串中的字符個(gè)數(shù)

MID函數(shù)：從文本字符串中的指定位置起返回特定個(gè)數(shù)的字符

TEXT函數(shù)：設(shè)置數(shù)字格式并將其轉(zhuǎn)換為文本

出生日期獲取方法

獲取身份證第7位數(shù)開(kāi)始提取后面的8位數(shù)，指定數(shù)字格式y(tǒng)yyy年mm月dd日。

=TEXT(MID(A2,7,8),"0年00月00日")

使用到的函數(shù)：

MID函數(shù)：從文本字符串中的指定位置起返回特定個(gè)數(shù)的字符

TEXT函數(shù)：設(shè)置數(shù)字格式并將其轉(zhuǎn)換為文本

生肖獲取方法

=IF(LEN(A2)=15,MID("鼠牛虎兔龍蛇馬羊猴雞狗豬",MOD("19"&MID(A2,7,2)-4,12)+1,1)

使用到的函數(shù)：

LEN函數(shù)：返回文本字符串中的字符個(gè)數(shù)

MID函數(shù)：從文本字符串中的指定位置起返回特定個(gè)數(shù)的字符

MOD函數(shù)：返回除法的余數(shù)

星座獲取方法

=VLOOKUP(VALUE("1900-"&TEXT(MID(A2,LEN(A2)/2+2,4),"#-##")),{1,"摩羯座";20,"水瓶座";50,"雙魚(yú)座";81,"白羊座";111,"金牛座";142,"雙子座";174,"巨蟹座";205,"獅子座";236,"處女座";267,"天秤座";298,"天蝎座";328,"射手座";357,"摩羯座"},2,TRUE)

使用到的函數(shù)：

TEXT函數(shù)：設(shè)置數(shù)字格式并將其轉(zhuǎn)換為文本

LEN函數(shù)：返回文本字符串中的字符個(gè)數(shù)

MID函數(shù)：從文本字符串中的指定位置起返回特定個(gè)數(shù)的字符

VALUE函數(shù)：將文本參數(shù)轉(zhuǎn)換為數(shù)字

VLOOKUP函數(shù)：在數(shù)組第一列中查找，然后在行之間移動(dòng)以返回單元格的值

出生地獲取方法

在sheet2工作簿添加下面的數(shù)據(jù)庫(kù)

然后在身份證號(hào)碼查詢器輸入以下公式

=VLOOKUP(LEFT(A2,6),戶籍地!1:1048576,2,1)

使用到的函數(shù)：

LEFT函數(shù)：返回文本值中最左邊的字符

VLOOKUP函數(shù)：在數(shù)組第一列中查找，然后在行之間移動(dòng)以返回單元格的值

這樣就完成了身份證查詢器，是不是特別棒，如果想獲取excel模板 請(qǐng)私信回復(fù)【身份】

?