每位親都知道,身份證號碼中包含著出生年月,性別等信息,但在實際的工作中,從身份證號碼中提取出生年月、性別的親並不多,一般都是采用詢問的方式填寫,這樣不僅費時費力,而且容易出錯,今天,小編帶大傢瞭解身份證號碼中隱藏的秘密。
一、提取出生年月。
(一)Text+Mid函數法。
函數Text:
作用:根據指定的數值格式將數值轉換為文本。
語法結構:=Text(值,格式代碼)。
函數Mid:
作用:從文本字符串中指定的其實位置返回指定長度的值。
語法結構:=Mid(字符串,開始位置,字符長度)。
方法:
在目標單元格中輸入公式:=TEXT(MID(C3,7,8),"00-00-00")。
解讀:
利用Mid函數首先從C3單元格中提取從第7位開始,長度為8的字符串,即出生日期的8位數字,然後用Text函數將其設置為日期形式。
(二)Ctrl+E+分列法。
方法:
1、在第一個目標單元格中輸入對應出生年月的8位數字。
2、選定所有目標單元格(包括第一步已經輸入值的單元格),快捷鍵Ctrl+E。
3、【數據】-【分列】-【下一步】-【下一步】,選擇【列數據格式】中的【日期】並【完成】。
解讀:
1、如果沒有步驟3,提取的值僅為8位數字,而出生年月是日期格式,所以需要步驟3進一步完善設置。
2、如果對Text函數不夠理解,可以先用Mid函數提取出生年月的8位數字,然後進行【分列】操作。
(三)分列法。
方法:
1、選定目標單元格,【數據】-【分列】,打開【文本分列向導】對話框。
2、選擇【原始數據類型】中的【固定寬度】,並【下一步】。
3、在【數據預覽】區域中的“出生年月”開始數字和計數數字處單擊鼠標左鍵(如果選擇錯誤,需要清除分割線,在分割線上雙擊即可),將數據分為3部分,並【下一步】。
4、選中第一部分(系統默認選中,即【數據預覽】區域中的黑色部分),選擇【列數據格式】中的【不導入此列】;選中“出生年月”所在的部分,選擇【列數據格式】中的【日期】;選中“剩餘部分”,選擇【列數據格式】中的【不導入此列】並【完成】。
解讀:
因為提取的是出生年月,所以出生年月前面和後面的數據【不導入此列】,並且要將“出生年月”設置為【日期】形式。
(二)提取性別。
函數:If+Mod+Mid。
函數Mod:
作用:返回兩數相除的餘數。
語法結構:=Mod(被除數,除數)。
方法:
在目標單元格中輸入公式:=IF(MOD(MID(C3,17,1),2),"男","女")。
解讀:
公式中首先利用Mid函數提取身份證號碼的第17位,並作為Mod函數的第一個參數,即被除數,然後用IF函數判斷Mod函數的結果,如果為奇數,返回“男”,如果為偶數,返回“女”。
三、計算年齡。
函數:Datedif。
功能:以指定的方式計算兩個日期之間的差值。
語法結構:=Datedif(開始日期,結束日期,統計方式),常見的“統計方式”有“Y”、“M”、“D”三種,即“年”、“月”、“日”。
方法:
在目標單元格中輸入公式:=DATEDIF(D3,TODAY(),"y")。
解讀:
1、如果數據源中沒有“出生年月”列,也可以用公式=DATEDIF(TEXT(MID(C3,7,8),"00-00-00"),TODAY(),"y")來提取,即首先用Text+Mid提取出生年月,並作為Datedif函數的第一個參數。
2、Datedif函數也可以用於計算工齡,或任意兩個日期之間的差值。
四、計算退休年齡。
函數:Edate。
作用:返回指定日期之前或之後的日期。
語法結構:=Edate(開始日期,月份),如果“月份”為負數,則計算的為“開始日期”之前的日期,如果為正數,則為“開始日期”之後的日期。
方法:
在目標單元格中輸入公式:=EDATE(D3,MOD(MID(C3,17,1),2)*120+600)。
解讀:
本示例中是以“男:60歲,女:50歲”為標準計算退休年齡的,如果以“男:55歲,女50歲”為標準計算退休年齡,則公式為:=EDATE(D3,MOD(MID(C3,17,1),2)*60+600)。需要註意的就是輔助值的變化。
-
扫码下载安卓APP
-
微信扫一扫关注我们微信扫一扫打开小程序手Q扫一扫打开小程序
-
返回顶部