|
01 計算年齡，年齡分布 如下表，是某員工入職時填寫的身份證號，現(xiàn)需根據(jù)其提供的身份證號，來執(zhí)行以下提取。 （1）提取出生日期 在D2中輸入公式為：=TEXT(MID(C2,7,8),'0000-00-00')，之后按Enter鍵完成，再向下下拉填充即可。
注：MID函數(shù)用于截取某一個字符串中的內(nèi)容，此處是截取身份證號的出生日期那部分；TEXT函數(shù)是使截取的那部分格式轉(zhuǎn)為日期格式。 另外一種更簡便的方法是，使用DATE函數(shù)，DATE函數(shù)共三個參數(shù)，分別是年，月，日。 因此公式可寫為：=DATE(MID(C2,7,4),MID(C2,11,2),MID(C2,13,2)) （2）計算年齡 在E2中輸入公式為：=DATEDIF(D2,TODAY(),'y')，之后按Enter鍵完成，再向下下拉填充即可。
注：DATEDIF函數(shù)用于計算兩日期之間相隔的年數(shù)，月數(shù)，天數(shù)，TODAY函數(shù)用于返回當前日期，“y”為DATEDIF的第三個屬性參數(shù)，用于返回兩年份之間相隔的年數(shù)。 （3）計算年齡分布 按要求可以將年齡分為：18歲以下，18-29歲,30-49歲，40-49歲，50歲以上。按上述分布，來判斷年齡處于哪個范圍之間。 在F2中輸入公式為：=VLOOKUP(E2,{0,'18歲以下';18,'18-29歲';30,'30-39歲';40,'40-49歲';50,'50歲以上'},2,1)，之后按Enter鍵完成，再向下下拉填充。
注：此處使用VLOOKUP函數(shù)的模糊查找屬性（最后一個參數(shù)設為1時），來判斷一個數(shù)字是否處于某一范圍之內(nèi)，當然也可使用LOOKUP函數(shù)。 02 計算性別 如下表，是某員工入職時提供的身份證號，現(xiàn)需根據(jù)其提供的身份證號，來判斷該員工性別。 在D2中輸入公式為：=IF(MOD(MID(C2,17,1),2),'男','女')，之后按Enter鍵完成，再向下下拉填充。
注：身份證號第17位，若是奇數(shù)，則為男，若偶數(shù)，則為女。先使用MID函數(shù)截取第17位數(shù)據(jù)，和MOD求取余數(shù)，再用IF函數(shù)判斷。 當然，也可使用ISODD來判斷是否為奇數(shù)。 公式為：=IF(ISODD(MID(C2,17,1)),'男','女')。 03 計算司齡、賬齡 如下表，是某員工的入職時間和在公司預借款項的時間，計算出其司齡與賬齡。 在D2單元中輸入公式為： =DATEDIF(C2,TODAY(),'y')&'年'&DATEDIF(C2,TODAY(),'ym')&'個月'&DATEDIF(C2,TODAY(),'md')&'天'，之后按Enter鍵完成，再向下下拉填充即可。
注： DATEDIF函數(shù)用于返回兩個日期相隔的時間。 “YM”參數(shù)會忽略相隔年數(shù)，僅返回月份數(shù)； “MD”忽略兩日期之間的年數(shù)和月份數(shù)，僅返回天數(shù)。 若只是按月來計算，公式可寫為： =DATEDIF(C2,TODAY(),'m')/12，但此時，不能精確到天。 04 計算生肖、星座 如下表，從身份證號中提取生肖。 在D2中輸入公式為： =MID('猴雞狗 豬鼠?；⒚恺埳唏R羊',MOD(MID(C2,7,4),12)+1,1)，之然后按Enter鍵完成，再向下下拉填充。
注：首先使用MID函數(shù)從身份證號中提取出出生的年份，再使用MOD函數(shù)取余，按照規(guī)則計算出生肖。此處根據(jù)公歷生日來計算，若為陰歷生日，則會存在一定偏差。 在E2輸入公式為： =LOOKUP(--MID(C2,11,4),{101,'摩羯';120,'水瓶';219,'雙魚';321,'白羊';420,'金牛';521,'雙子';621,'巨蟹';723,'獅子';823,'處女';923,'天秤';1023,'天蝎';1122,'射手';1222,'摩羯'})&'座'，之后按Enter鍵完成，再向下下拉填充。
注：按照日期格式的不同，可靈活改變第一個參數(shù)?！?-”表示將文本強制轉(zhuǎn)為數(shù)值。 05 身份證號歸屬地查詢 查詢下述身份證號的歸屬地。
注：身份證號的前六位，代表省市省的相關地區(qū)信息。 |
|
|
