EXCEL_电子表格用函数计算年龄、工龄与从身份证中算出周岁等技巧.docVIP

电子表格常用函数汇总 ―――（潘世华2013年版） 注：(1)如何截取身份证号第17位：MID(C2,17,1) Value（字符型数字）这个函数就是转换字符型数字转成数字N(value)这个函数,将不是数值形式的值转成数值形式.日期转换成序列值,True转换成1,False转换成0不需要函数，乘1即可例如 001 变数值 =A1*1 即等于1=IF(LEN(C2)=15,19MID(C2,7,2)/MID(C2,9,2)/MID(C2,11,2),IF(LEN(C2)=18,MID(C2,7,4)/MID(C2,11,2)/MID(C2,13,2),)) 说明：C2为身份证号码所在的单元格，在实践过程中，把“C2”转换成实际表中的“身份证栏”（身份证栏的输入格式为“文本”）。 2、用“身份证号”提起出生年月日第二种公式：（很好）=CONCATENATE(MID(C2,7,4),年,MID(2,11,2),月,MID(2,13,2),日)=IF(VALUE(RIGHT(B2,3))/2=INT(VALUE(RIGHT(B2,3))/2),女,男) =IF(C2=,,INT((TODAY()-C2)/365.25)) 说明：C2为身份证号码所在的单元格，在实践过程中，把“C2”转换成实际表中的“身份证栏”（身份证栏的输入格式为“文本”）。 6、用 “出生年月日”计算周岁第二种公式（很好）：=DATEDIF(C2,TODAY(),y) 说明：C2为出生“年月日”所在的单元格，在实践过程中，把“K4”转换成实际表中的出生“年月日”（出生“年月日”栏的输入格式为“*2013-3-28”）。 7、用 “出生年月日”计算月份数 题目： 计算日期为1973-4-1和当前日期的间隔月份数. 公式： =DATEDIF(1973-4-1,TODAY(),M) 结果： 403 简要说明 当单位代码为M时,计算结果是两个日期间隔的月份数. 1973-4-1 题目： 计算日期为1973-4-1和当前日期的间隔天数. 公式： =DATEDIF(1973-4-1,TODAY(),D) 结果： 12273 简要说明 当单位代码为D时,计算结果是两个日期间隔的天数. 1973-4-1 9、根据参加工作时间求年资（即工龄）（很好）： =CONCATENATE(DATEDIF(F4,TODAY(),y),年,DATEDIF(F4,TODAY(),ym),个月) 截止到当前日期，要求精确一些。 =DATEDIF(--TEXT((LEN(A1)=15)*19MID(A1,7,6+(LEN(A1)=18)*2),0-00-00),TODAY(),y)周岁零DATEDIF(--TEXT((LEN(A1)=15)*19MID(A1,7,6+(LEN(A1)=18)*2),0-00-00),TODAY(),ym)月DATEDIF(--TEXT((LEN(A1)=15)*19MID(A1,7,6+(LEN(A1)=18)*2),0-00-00),TODAY(),md)天 如果身份证号在A1，则在B1输入：=IF(LEN(A1)=18,YEAR(TODAY())-MID(A1,7,4),IF(LEN(A2)=15,YEAR(TODAY())-MID(A2,7,2)-1900,))上述公式是考虑了一代、二代身份证号共存的情况。如果全部是二代身份证，则可以简单点：=YEAR(TODAY())-MID(A1,7,4)，其中YEAR(TODAY())代表今年，MID(A1,7,4)取身份证中的年份，两个相减就是年龄。 求性别： 身份证号的第17位表示性别信息。单数为男，双数为女。我样只要半数第17位数的单双即可。性别的单元格下输入=IF(mod(mid(E2,15,3),2),男,女)。其中“E2”为身份证所在的单元格。可以根据自己需要设置为相应的单元格即可。 求年龄： 这个比较简单，网上有很多公式，但都很复杂，我们的思路就是用当前年份减去出生年份即可。在Excel中获取当前年份的函数是=year(now())。所以我们的公式为：=YEAR(NOW())-MID(E2,7,4)。 出生日期： 出生日期我们用：=TEXT((LEN(E2)=15)*19MID(E2,7,6+2*(LEN(E2)=18)),#-00-00)即可求出。 基它人的相关信息，我们只要把其身份证号输入完毕后。利用Excel的自动填充功能向下拉就可以了。 你的公式是错。请用 ngjtyw | 十三级 =datedif(text(mid(B3,7,len(B3)*2/3-4),00-00-00),today(),y) len(B3)*2/3-4的意思是15位号码取6位日期，18位号码取8位