Excel提取身份證年齡和性別③
- 2020 年 8 月 23 日
- 筆記
問題場景
-
從user表中的身份資訊中拿到用戶的年齡和性別；
-
以下方法也可適用於提取其他數據，目的在於通過實例操作了解更多函數用法；
-
以下圖中數據都為測試數據，不具備真實性！
場景一
-
從user表中的18位身份證資訊列中提取所有用戶的年齡。
-
- 不考慮15位的身份證資訊。
目標
- 提取18位身份證資訊列的出生日期來判斷所有用戶的年齡。
解決方案一
用year()、today()、mid()函數實現。
- 函數解釋
=YEAR(serial_number)
//將系列數轉換為年，serial_number一個日期值，其中包含要查找的年份=TODAY()
//TODAY函數沒有參數，作為YEAR函數的參數來獲取當前年份
//MID函數參考上篇文章，有解釋含義和用法
-
第一步：在G2單元格英文狀態下輸入:=YEAR(TODAY())-MID(D2,7,4)。
-
- 公式解釋：使用TODAY函數作為 YEAR函數的參數來獲取當前年份，減去他的出生年份（MID函數從D2格單元的第7位起截取4位獲取出生年份）。
- 第二步：滑鼠移到G2單元格的右下角，出現實心的黑色【+】號，雙擊填充此公式。
- 第三步：最後結果。
解決方案二
用Datedif()函數、Text()函數和MID()函數。
- 函數解釋
=DATEDIF(start_date,end_date,unit)
//Start_date起始日期；End_date結束日期；Unit為所需資訊的返回類型，通常有三種"Y""M""D"。
//Y、M、D參數：分別計算開始日期與結束日期的年份差、月份差、和天數差。
//text函數參考上篇文章，有解釋含義和用法。
-
第一步：在G2單元格英文狀態下輸入:=DATEDIF(TEXT(MID(D2,7,8),”00-00-00″),TODAY(),”y”)。
-
- 公式解釋：MID函數從D2格單元的第7位起截取8位獲取出生年月日，再用Text函數轉換為日期格式，最後用datedif函數計算當前年份和出生日期的年份差。
- 第二步：滑鼠移到G2單元格的右下角，出現實心的黑色【+】號，雙擊填充此公式。
- 第三步：最後結果。
解決方案三
用Datedif()函數、 date()函數和MID()函數。
- 函數解釋
=DATE(year,month,day)
//將結果將設為日期格式，且year,month,day三個參數為年、月、日參數
//用法示例參考上篇。
-
第一步：在G2單元格英文狀態下輸入:=DATEDIF(DATE(MID(D2,7,4),1,1),TODAY(),”y”)。
-
- 公式解釋：MID函數從D2格單元的第7位起截取8位獲取出生年月日，再用Date函數轉換為日期格式（月和日的參數定1），最後用datedif函數計算當前年份和出生日期的年份差。
- 第二步：滑鼠移到G2單元格的右下角，出現實心的黑色【+】號，雙擊填充此公式。
- 第三步：最後結果。
解決方案四
-
分兩步，先提取出生日期，上篇中詳細介紹了怎麼提取出生日期，這列可以拿來用；
-
再用上述函數直接跟當前年份做差算出年齡。
場景二
-
從user表中的18位身份證資訊列中判斷所有用戶的性別。
-
- 不考慮15位的身份證資訊。
目標
-
從18位身份證資訊中判斷所有用戶的性別。
-
- 18位身份證的第17位代表性別，奇數為男，偶數為女。
解決方案一
用if()、MOD()、MID()函數。
- 函數解釋
=IF(logical_test,value_if_true,value_if_false)
//例：=if(2>1,1,0),如果2>1,則返回1否則返回0；
//例：=if(2>1,"Ture","False"):如果2>1,則返回Ture否則返回False=MOD(number,divisor)
//求余函數，Number 為被除數；Divisor 為除數，返回餘數。
-
第一步：在H2單元格英文狀態下輸入:=IF(MOD(MID(D2,17,1),2),”男”,”女”)或者=IF(MOD(MID(D2,17,1),2)=1,”男”,”女”)。
-
- 公式解釋：MID函數從D2格單元的第17位，再用MOD函數求余，在用IF函數判斷餘數，餘數為0則是假值，返回「女」，否則返回「男」（Excel中真值為0可以代表假）。
- 第二步：滑鼠移到H2單元格的右下角，出現實心的黑色【+】號，雙擊填充此公式。
- 第三步：最後結果。
解決方案二
用if()、ISODD()、MID()函數。
- 函數解釋
=ISODD(number)
//判斷參數是不是奇數，如果是返回ture，否則返回False.
-
第一步：在H2單元格英文狀態下輸入:=IF(ISODD(MID(D2,17,1)),”男”,”女”)。
-
- 公式解釋：MID函數從D2格單元的第17位，然後用ISODD判斷第17位是否為奇數，在用IF函數判斷如果是，返回「男」，否則返回「女」。
- 第二步：滑鼠移到H2單元格的右下角，出現實心的黑色【+】號，雙擊填充此公式。
- 第三步：最後結果。
總結
實際業務場景複雜時，如果不能一次性解決問題，可擇優使用、或可結合使用，多嘗試，辦法總比困難多！
