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

EXCEL电子用函数计算年龄、工龄及从身份证中算出周岁等技巧 电子表格常用函数汇总 ―――(潘世华2013年版) 注:(1)如何截取身份证号第17位:MID(C2,17,1) ‎‎ Value(字符型数字) 这个函数就是转换字符型数字转成 数字 N(value)这个函数,将不是数值形式的值转成数值形式.日期转换成序列值,True转换成 1,False转换成0 不需要函数，乘1即可 例如 001 变数值 =A1*1 即等于1 1、用“身份证号”提起出生年月日第一种: =IF(LEN(C2)=15,19&MID(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(C2,11,2),"月",MID(C2,13,2),"日") 3、“用身份证”号算出性别第一种公式‎‎:=IF(LEN(C2)=15,IF(OR(RIGHT (C2,1)="0",RIGHT(C2,1)="2",RIGHT(C2,1)="4",RIGHT (C2,1)="6",RIGHT(C2,1)="8"),"女","男 "),IF(LEN(C2)=18,IF(OR(MID(C2,17,1)="0",MID(C2,17,1)="2",MID( C2,17,1)="4",MID(C2,17,1)="6",MID(C2,17,1)="8"),"女","男 "),"")) 说明:C2为身份证号码所在的单元格，在实践过程‎‎中，把“C2”转换成实际表中的“身份证栏”(身份证栏的输入格式为‎‎“文本”)。 4、“用身份证”号算出性别第二种公式:(很好)‎‎ =IF(VALUE(RIGHT(B2,3))/2=INT(VALUE(RIGHT(B2,3))/2),"女","男") 5、由上式算出“出生年月日”后，如何用该出生年月日计算周岁公式 :=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"该格式可以手动输入第一格，以后可用填冲方式下拉‎‎即可。 8、用 “出生年月日”计算天数 题目: 计算日期为1973-4-1和当前日期的间隔天数. 公式: =DATEDIF("1973-4-1",TODAY(),"D") 结果: 12273 简要说明 当单位代码为"D"时,计算结果是两个日期间隔的天数. "1973-4-1"该格式可以手动输入第一格，以后可用填冲方式下拉‎‎即可。 9、根据参加工作时间求年资(即工龄)(很好): =CONCATENATE(DATEDIF(F4,TODAY(),"y"),"年",DATEDIF(F4,TODAY(),"ym"),"个月") excel用身份证号计算年龄 截止到当前日期，要求精确一些。 =DATEDIF(--TEXT((LEN(A1)=15)*19&MID(A1,7,6+(LEN(A1)=18)*2),"0-00-00"),TODAY(),"y")&"周岁零 "&DATEDIF(--TEXT((LEN(A1)=15)*19&MID(A1,7,6+(LEN(A1)=18)*2),"0-00-00"),TODAY(),"ym")&"月 "&DATEDIF(--TEXT((LEN(A1)=15)*19&MID(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)取身份证中的年份，两个相减就是年龄。 1. 首先选中表格中要输入身份证号的列,单击“格式”,,,“单元格”,,,“数字”选择“文本”。这样输入的身份证号就不会出现“1.50428E+18”科学记数的情况。 2. 求性别: 身份证号的第17位表示性别信息。单数为男,双数为女。我样只要半 数第17位数的单双即可。性别的单元格下输入 =IF(mod(mid(E2,15,3),2),"男","女")。其中“E2”为身份证所在的单元格。 可以根据自己需要设置为相应的单元格即可。 3. 求年龄: 这个比较简单,网上有很多公式,但都很复杂,我们的思路就是用当 前年份减去出生年份即可。在Excel中获取当前年份的凼数是 =year(now())。所以我们的公式为:=YEAR(NOW())-MID(E2,7,4)。 4. 出生日期: 出生日期我们用: =TEXT((LEN(E2)=15)*19&MID(E2,7,6+2*(LEN(E2)=18)),"#-00-00")即可 求出。 5. 基它人的相关信息,我们只要把其身份证号输入完毕后。利用Exc el的自动填充功能向下拉就可以了。 你的公式是错。请用 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位日期。 =IF(MID(C2,17,1)/2=INT(MID(C2,17,1)/2),"女","男") 如何判断一个数是奇数还是偶数:比较该数与该数的向下取整得到的数是否相等，相等为偶 系统自动生成了一个excel文件，里面是一些数字。我想把这些数字sum起来却发现结果总是0。经检查后发现里面的数字是以文本形式保存进去的。用设定单元格格式等方法均无法把文本的数字转换成真正的数字。请问有什么办法可以一批把文本型的单元格变成数字型的吗, 数据—分列—完成:可以把文本型数字转换为数字型数字。 例如数据在B2:G10范围内,可以用下列公式: =SUM(VALUE(B2:G10)) 然后同时按三键结束,形成数组公式. 三键是指Ctrl+Shift+Enter. 或者 用SUM求和，那么用数组公式=SUM(--区域)。 在EXCEL表中输入身份证号码自动生成周岁，(算到日) =(AF4,TEXT(TODAY)()，”yyyymmdd“)-MID(G4，7，8))/10000-0.5确认下拉。 注解表头:A1„姓名?B1„性别?C1„D1„岀生年月日?E1„身份证号码? 2是电脑序号也是第一人所在的行。 选中第一人的„周岁?单元格输入公式: =(AD2,TEXT(TODAY)()，”yyyymmdd“)-MID(E2，7，8))/10000-0.5确认下拉。 注:?AD2、是A列到身份证号前的„列?的区间，要输入两个字母; ?E2、是指身份证号所在的„列?和„行?的位置; ?7，8、是指身份证号从左向右数第7位和第8位; ?除10000-0.5、是电子表计算周岁的系数; ?选中周岁列?点击„格式栏??单元格?数字?数值?小数为„0??确认。 ?AD列必须为空;你任何时候打开电脑，周岁都是自动生成的。