Excel常用函数公式及使用技巧-身份证-信息提取-项目信息整理.pdf

Ex c e l函数公式及技巧【身份证信息的提取】从身份证号码中提取出生年月日=TEXT(MID(A 1,7,6+(LEN(A 1)=18)*2),#-00-00)+0=TEXT(MID(A1,7,6+(LEN(Al)=18)*2),#-00-00)*1=IF(A2o,TEXT(LEN(A2)=15)*19&MID(A2,7,6+(LEN(A2)=18)*2),#-00-00)+0,)显示格式均为yyyy-nrd最简单的公式，把单元格设置为日期格式)=IF(LEN(A2)=15,19&MID(A2,7,2)&-&MID(A2,9,2)&-&MID(A2,11,2),MID(A2,7,4)&-&MID(A2,11,2)&-&MID(A2,13,2)显示格式为yyyy-mm-dd如果要求为“1995/03/29”格式的话，将 换 成 即 可)=犷(4=,用(114(口4)=15,1*丁(19&乂 卬 伊4,7,6),0000年00 月 00 日),IF(LEN(D4)=18,TEXT(MID(D4,7,8),0000 年 00 月 00 日)显示格式为yyyy年 m m 月 d d 日如果将公式中“0000年 0 0 月 0 0 日”改 成“0000-00-00”，则显示格式为 yyyy-mm-dd)=IF(LEN(A 1:A2)=18,MID(A 1:A2,7,8),19&MID(A 1:A2,7,6)显示格式为yyyymmddo=TEXT(LEN(A 1)=15)*19&MID(A 1,7,6+(LEN(A 1)=18)*2),#-00-00)+0=IF(LEN(A2)=18,MID(A2,7,4)&-MID(A2,11,2),19&MID(A2,7,2)&-MID(A2,9,2)=MID(A 1,7,4)&年&MID(A 月&MID(A 1,13,2)&B=IF(Alo,TEXT(LEN(A 1)=15)*19&MID(A 1,7,6+(LEN(A 1)=18)*2),#-00-00)从身份证号码中提取出性别=IF(MOD(MID(A1,15,3),2),男 女 )（最 简 单 公 式）=IF(MOD(RIGHT(LEFT(A 1,17),2),男女)=IF(A 2o”,IF(MOD(RIGHT(LEFT(A2,17),2),”男”，“女),)=IF(VALUE(LEN(ROUND(RIGHT(A 1,1)/2,2)=1男,丁 女)从身份证号码中进行年龄判断=IF(A 3o,DATEDIF(TEXT(LEN(A3)=15*l 9&MED(A3,7,6+(LEN(A3)=18*2),#-00-00,),TODAY(),Y),)=DATEDIF(A l,TODAY(),“Y”)(以上公式会判断是否已过生日而自动增减一岁)=YE AR(NOW()-MID(E2,IF(LEN(E2)=18,9,7),2)-1900=YEAR(TODAY()-IF(LEN(A 1)=15,19&MID(A1,7,2),MID(A 1,7,4)=YEAR(TODAY()-VALUE(MID(B 1,7,4)&岁=YEAR(TODAY()-IF(M ID(B 1,18,1)=,CONC ATEN ATE(19,MID(B 1,7,2),MID(B1,7,4)按身份证号号码计算至今天年龄=DATEDIF(TEXT(LEN(A 1)=15)*19&MID(A 1,7,6+(LEN(A 1)=18)*2)J#D0-00)TODAY()Jy)以2006年10月3 1日为基准日,按按身份证计算年龄(周岁)的公式=DATEDIF(TEXT(MID(A 1,7,6+(LEN(A 1)=18)*2),#-00-00)*1,2006-10-31按身份证号分男女年龄段按身份证号分男女年龄段,身份证号在K歹 U,年龄段在J 列（身份证号为18位）男 性 16周岁以下为 1男 性 16周 岁（含 16周岁）以上至50周 岁 为 2男性50周 岁（含 50周岁）以上至6 0 周 岁 为 3男性60周 岁（含 60周岁）以上为4女性16周岁以下为1女性16周 岁(含16周岁)以上至45周 岁 为2女性45周 岁(含4 5周岁)以上至55周 岁 为3女性55周 岁(含55周岁)以上为 4=MATCH(DATEDIF(DATE(MID(K 1,7,4),MID(K 1,11,2),MID(K 1,13,2),TODAY(),y),0,l 6,50,60-0,0,5,5*ISEVEN(MID(K 1,17,1)=SUM(-(DATEDIF(MID(Kl,7,4)&7&MID(Kl,ll,2)&7&MID(Kl,13,2),TODAY(),y)=0,l6,45,55+0,0,5,5*MOD(MID(K1,17,1),2)mm mm mm mm mm mm mm mm mm mm mm mm mm mm mm mm mm mm mm mm mm mm mm mm mm【年龄和工龄计算】根据出生年月计算年龄=DATEDIF(Al,TODAY(),y)=DATEDIF(A 1 ,TODAY(),y)&周岁=DATEDIF(A l,NOW(),y)根据出生年月推算生肖中国人有1 2生肖，属什么可以推算出来。

即用诞生年份除以1 2,再用除不尽的余数对照如下：0-猴，1-鸡，2 f狗，3 f猪，4一鼠，5 f牛，6 f虎，7 f兔，8 f龙，9 f蛇，10一马，11-羊例如：XXX出生于1921年，即用1921年除以1 2,商得数为1 6 0,余数为1,对照上面得知余数1对应生肖是鸡，XXX就属鸡MID(猴鸡狗猪鼠牛虎兔龙蛇马羊,MOD(YEAR(A2),12)+1,1)(2007)如何求出一个人到某指定日期的周岁？=DATEDIF(起始日期，结束日期JY)计算距离退休年龄的公式=IF(E2=,IF(E2=V2,已经退休，距离退休还有&DATEDIF(TODAY(),DATE(YEAR(U2)+(V2),MONTH(U2),DAY(U2),Y)&年&DATEDIF(TODAY(),DATE(YEAR(U2)+(V2),MONTH(U2),DAY(U2),YM)&个月&DATEDIF(TODAY(),DATE(YEAR(U2)+(V2),MONTH(U2),DAY(U2),Md)&)其 中E2为 年 龄(可用身份证号码的公式生成)；V2 为 法 定 退 休 年 龄(男 6 0,女 5 0)公 式 为：=IF(D2=,IF(D2=,60,50)D2为 男 或 女(可用身份证号码的公式生成)；U2为 出 生 年 月 日(可用身份证号码的公式生成)。

求工龄=DATEDIF(B2,TODAY(),y)=DATEDIF(B2,TODAY(),yi-n)=DATEDIF(B2,TODAY(),md)=DATEDIF(B2,TODAY(),y)&年&DATEDIF(B2,TODAY(),ym)&月&DATEDIF(B2,TODAY(),md)&H 计算工龄=DATEDIF(C6,C8,y)求两日期间的年数=DATEDIF(C6,C8,ym)求两日期间除去整年数剩余的月数=DATEDIF(C6,C8,m)求两日期间的总月数如 果 只 需 要 算 出 周 年 的 话，可 以 用=datedif(1978-8,2006-5,Y)年龄及工龄计算有出生年月如何求年龄？有工作时间如何求工龄？(求出的结果为多少年另几个月，如：0303的形式，即3年零3个月)a l是出生年月或工作时间：=datedif(al,today(),y)=text(datedif(a 1 ,today(),y),00)&text(datedif(a l,today(),m),00)如 B2=1964-9-1 则：=TEXT(DATEDIF(B2,TODAY(),y),00)&TEXT(MOD(DATEDIF(B2,TODAY(),m),12),00),显示4009=TEXT(DATEDIF(B2,TODAY(),y)JOO 年)&TEXT(MOD(DATEDIF(B2,TODAY(),m),12),00 月)显示 40 年 09 月如果你找不到DATEDIF函数，也可以不用DATEDIF函数，如 B2=1964-9-1 则：=TEXT(RIGHT(YEAR(NOW()-B2),2),00)&TEXT(MOD(MONTH(NOW()-B2)-1,12),00)显示4009=TEXT(RIGHT(YEAR(NOW()-B2),2)&MOD(MONTH(NOW()-B2)-1,12)&个月，显 示 40年 09个月自动算出工龄日期格式为(yyyy.mm.dd)能否用：(yyyy.mm.dd)这种格式来计算出工龄有多长呢？以前用这样段(=TEXT(RIGHT(YEAR(NOW()-A1),2)&年&MOD(MONTH(NOW()-A 1)-1,12)&个月)。

但这种方法只能用：(yyyy-mm-dd)这样的日期格式才能实现!你 不 妨 把 替 换 成 不 就 行 了 吗，再说后者是日期的一种标准格式,=TEXT(RIGHT(YEAR(NOW()-SUBSTITUTE(A 12)&年&MOD(MONTH(NOW()-SUBSTITUTE(A 11,12)&个月【时间和日期应用】自动显示当前日期公式=YEAR(NOW()当前年=MONTH(NOW()当前月=DAY(NOW()当前日如何在单元格中自动填入当前日期Ctrl+;如何判断某日是否星期天=WEEKDAY(A2,2)=TEXT(Al,aaaa)=MOD(A1,7)1)显示昨天的日期每天需要单元格内显示昨天的日期，但双休日除外例如，今天是7月3号的话，就显示7月2号，如果是7月9号，就显示7月6号IF(TEXT(TODAY(),AAA尸 一,TODAY()-3,IF(TEXT(TODAY(),AAA尸旧,TODAY()-2,TODAY()-1)=IF(TEXT(TODAY(),AAA)=,TODAY()-3,TODAY()-1)关于取日期怎么设个公式使A1在年月日向后推5 年，变成2011-7-15=DATE(YEAR(A 1)+5,MONTH(A 1),DAY(A 1)=EDATE(A1,12*5)如何对日期进行上、中、下旬区分=LOOKUP(DAY(A 1),0,11,21,31,上旬，中旬，下旬，下旬 )如何获取一个月的最大天数“=口 人 丫(口 八 丁 (2002,3,1)-1)或=口人丫出1-1)”,B1 为2001-03-01日期格式转换公式将“01/12/2005”转换成“20050112”格式=RIGHT(A 1,4)&MID(A 1,4,2)&LEFT(A 1,2)=YEAR($A2)&TEXT(MONTH($A2),00)&TEXT(DAY($A2),00)该公式不用设置数据有效性，但要设置储存格格式。

也可以用下列两方法：1、先转换成文本，然后再用字符处理函数2、数据-分列 日期HMDY将“2005年 9 月”转换成“200509”格式先用公式：=text(al,yyyymm)+O 然后将单元格格式为常规将“2005-8-6”格式转换为“20050806”格式用公式：=TEXT(A1,YYYYMMDD)反之，将 20050806转为日期2005-8-6格式，可用公式:=DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2)另四种公式：=text(al,0000-00-00)=-TEXT(Al,#-00-00),把单元格设置为日期格式=TEXT(20050806,0000-00-00)*1,单元格设置日期型=VALUE(LEFT(A 1,4)&-&MID(A 1,5,2)&-&RIGHT(A 1,2)将“20060501”转换为“2006-05-01”格式=DATE(LEFT(A2,4),MID(A2,5,2),RIGHT(A2。