确定性别 =IF(VALUE(MID(E7,15,3))/2=INT(VALUE(MID(E7,15,3))/2),"女","男") 瑞意恒动确定出生日期 =MID(E7,7,4)&"-"&MID(E7,11,2)&"-"&MID(E7,13,2)
确定年龄 =2010-IF(LEN(C3)=18,MID(C3,7,4),"19"&MID(C3,7,2))
重名筛选 =countif(B:B,B2)
重复身份证号查 =if(countif(d:d,d3&"*")>1,"重复","唯一") 身份证算生肖 =CHOOSE(MOD(MID(A2,LEN(A2)/2,2),12)+1,"鼠","牛","虎","兔","龙","蛇","马","羊","猴","鸡","狗","猪"
=replace(A1,n,2,"s")
其中：A1--为身份证所在的单元格。
n--为从左边数第n个字符
2--为要替换的连续字符串的个数
s--为你要替换成的字符串
吴元欣
在EXCEL表格里的一列数值是出生日期，有什么办法计算其退休年龄
=DATE(YEAR(A1)+60,MONTH(A1),DAY(A1)
有效性公式(适用于新输入身份证号):
=AND(OR(LEN(A1)={15,18}),ISNUMBER(1*TEXT(MID(A1,7,LEN(A1)/2-1),"#-00-00")),1*TEXT(MID(A1,7,LEN(A1)/2-1),"#-00-00")>1828,1*TEXT(MID(A1,7,LEN(A1)/2-1),"#-00-00")<TODAY())
性别:
=IF(MOD(MID(A1,15,3),2),"男","女")
出生日期:
=TEXT(RIGHT(19&MID(A1,7,LEN(A1)/2-1),8),"#-00-00")
年龄:
=MOD(YEAR(NOW())-MID(A1,7,LEN(A1)/2-5),190)
或者:
=DATEDIF(TEXT(RIGHT(19&MID(A1,7,LEN(A1)/2-1),8),"#-00-00"),NOW(),"Y")
已有身份证的情况下，公式应加合法性，有效性判断，所以公式改为:
出生日期:
=IF(A1="","",IF(AND(LEN(A1)<>15,LEN(A1)<>18),"错误",IF(ISERR(1*TEXT(MID(A1,7,LEN(A1)/2-1),"#-00-00")),"错误",IF(OR(1*TEXT
(MID(A1,7,LEN(A1)/2-1),"#-00-00")<1828,1*TEXT(MID(A1,7,LEN(A1)/2-1),"#-00-00")>TODAY()),"错误",--TEXT(RIGHT(19&MID(A1,7,LEN(A1)/2-1),8),"#-00-00")))))
年龄:
=IF(A1="","",IF(AND(LEN(A1)<>{15,18}),"错误",IF(ISERR(1*TEXT(MID(A1,7,LEN(A1)/2-1),"#-00-00")),"错误",IF(OR(1*TEXT(MID(A1,7,LEN(A1)/2-1),"#-00-00")<1828,1*TEXT(MID(A1,7,LEN(A1)/2-1),"#-00-00")>TODAY()),"错误",MOD(YEAR(NOW())-MID(A1,7,LEN(A1)/2-5),190)))))
折算为天数:
=IF(A1="","",IF(AND(LEN(A1)<>{15,18}),"错误",IF(ISERROR(1*(TEXT(MID(A1,7,LEN(A1)/2-1),"#-00-00"))),"错误",IF(OR
(1*TEXT(MID(A1,7,LEN(A1)/2-1),"#-00-00")<1828,1*TEXT(MID(A1,7,LEN(A1)/2-1),"#-00-00")>TODAY()),"错误",DATEDIF(TEXT((LEN(A1)=15)*19&MID(A1,7,6+(LEN(A1)=18)*2),"#-00-00"),TODAY(),"D")))))
叶绿素荧光参数身份证第18位（校验码）的计算方法
1、将前面的身份证号码17位数分别乘以不同的系数。从第一位到第十七位的系数分别为：7－9－10－5－8－4－2－1－6－3－7－9－10－5－8－4－2。 2、将这17位数字和系数相乘的结果相加。
3、再用求和数除以11，看余数是多少？
4、余数只可能有0－1－2－3－4－5－6－7－8－9－10这11个数字。其分别对应的最后一位身份证的号码为1－0－X－9－8－7－6－5－4－3－2。
5、通过上面得知如果余数是2，就会在身份证的第18位数字上出现罗马数字的Ⅹ。如果
余数是10，身份证的最后一位号码就是2。
例如：某男性的身份证号码是532927************。我们要看看这个身份证是不是合法的身份证。
首先我们得出前17位的乘积和是189，然后用189除以11得出的结果是17余2，也就是说其余数是2。最后通过对应规则就可以知道余数2对应的数字是x。所以，可以判定这是一个合格的身份证号码。
=IF(LEN(A1)=15,REPLACE(A1,7,,19)&MID("10X98765432",MOD(SUM(MID(REPLACE(A1,7,,19),ROW(INDIRECT("1:17")),1)*2^(18-ROW(INDIRECT("1:17")))),11)+1,1),A1)
excel技巧，身份证常用计算公式
1、将分隔符为·的文本日期转变为日期型日期
=TEXT(DATE(LEFT(K2,4),MID(K2,6,2),RIGHT(K2,2)),"YYYY-M-D")
2、根据日期计算年龄，精确到天
=CONCATENATE(DATEDIF(L2,TODAY(),"y"),"年",DATEDIF(L2,TODAY(),"ym"),"个月",DATEDIF(L2,
TODAY(),"md"),"天")
3.1、公式一
=IF(U2<>"",DATEDIF(TEXT((LEN(U2)=15)*19&MID(U2,7,6+(LEN(U2)=18)*2),"#-00-00"),TODAY(),"y"),)
3.2、公式二
=IF(OR(LEN(U2)=15,LEN(U2)=18),RIGHT(IF(IF(LEN(U2)=15,MID(U2,9,2),MID(U2,11,2))*100+IF(LEN(U2)=15,MID(U2,11,2),MID(U2,13,2))>MONTH(TODAY())*100+DAY(TODAY()),YEAR(TODAY())-IF(LEN(U2)=15,MID(U2,7,2),MID(U2,9,2))-1,YEAR(TODAY())-IF(LEN(U2)=15,MID(U2,7,2),MID(U2,9,2))),2),"证号错误")
3.3、公式三
=DATEDIF(IF(LEN(U2)=18, DATE(MID(U2,7,4), MID(U2,11,2), MID(U2,13,2)), DATE(MID(U2,7,2), MID(U2,9,2), MID(U2,11,2))), TODAY(), "Y") &"岁" & DATEDIF(IF(LEN(U2)=18, DATE(MID(U2,7,4), MID(U2,11,2), MID(U2,13,2)), DATE(MID(U2,7,2), MID(U2,9,2), MID(U2,11,2))), TODAY(), "YM") &"
月" & DATEDIF(IF(LEN(U2)=18, DATE(MID(U2,7,4), MID(U2,11,2), MID(U2,13,2)), DATE(MID(U2,7,2), MID(U2,9,2), MID(U2,11,2))), TODAY(), "MD") & "天"
4、从身份证号码中提取出生日期
4.1、公式一
乌氏粘度计原理
=DATE(IF(LEN(U2)=15,MID(U2,7,2),MID(U2,9,2)),IF(LEN(U2)=15,MID(U2,9,2),MID(U2,11,2)),IF(LEN(U2)=15,MID(U2,11,2),MID(U2,13,2)))
4.2、公式二
=IF(LEN(U2)=15,"19"&MID(U2,7,2)&"年"&MID(U2,9,2)&"月"&MID(U2,11,2)&" 日",MID(U2,7,4)&"年"&MID(U2,11,2)&"月"&MID(U2,13,2)&"日")
5、从身份证号码中提取性别
5.1、公式一
=IF(LEN(U2)=15,(IF(OR(ABS(RIGHT(U2))=1,ABS(RIGHT(U2))=3,ABS(RIGHT(U2))=5,ABS(RIGHT(U2))=7,ABS(RIGHT(U2))=9),"男","女")),(IF(OR(ABS(MID(U2,17,1))=1,ABS(MID(U2,17,1))=3,ABS(MID(U2,17,1))=5,ABS(MID(U2,17,1))=7,ABS(MID(U2,17,1))=9),"男","女")))
5.2、公式二
=IF(MOD(IF(LEN(U2)=15,MID(U2,15,1),MID(U2,17,1)),2)=1,"男","女")
未完待续……
xcel技巧，从身份证号码中提取个人信息
一、分析身份证号码
其实，身份证号码与一个人的性别、出生年月、籍贯等信息是紧密相连的，无论是15位还是18位的身份证号码，其中都保存了相关的个人信息。
15位身份证号码：第7、8位为出生年份(两位数)，第9、10位为出生月份，第11、12位代表出生
日期，第15位代表性别，奇数为男，偶数为女。
18位身份证号码：第7、8、9、10位为出生年份(四位数)，第11、第12位为出生月份，第13、14位代表出生日期，第17位代表性别，奇数为男，偶数为女。
例如，某员工的身份证号码(15位)是320521*********，那么表示1972年8月7日出生，性别为女。如果
能想办法从这些身份证号码中将上述个人信息提取出来，不仅快速简便，而且不容易出错，核对时也只需要对身份证号码进行检查，肯定可以大大提高工作效率。
二、提取个人信息
这里，我们需要使用IF、LEN、MOD、数据流图
MID、DATE等函数从身份证号码中提取个人信息。如图1所示，其中员工的身份证号码信息已输入完毕(C列)，出生年月信息填写在D列，性别信息填写在B列。
1. 提取出生年月信息
由于上交报表时只需要填写出生年月，不需要填写出生日期，因此这里我们只需要关心身份证号码的相应部位即可，即显示为“7208”这样的信息。在D2单元格中输入公式“=IF(LEN(C2)=15,MID(C2,7,4),MID(C2,9,4))”，其中：
恩斯特迈尔LEN(C2)=15：检查C2单元格中字符串的字符数目，本例的含义是检查身份证号码的长度是否是15位。
MID(C2,7,4)：从C2单元格中字符串的第7位开始提取四位数字，本例中表示提取15位身份证号码的第7、8、9、10位数字。
MID(C2,9,4)：从C2单元格中字符串的第9位开始提取四位数字，本例中表示提取18位身份证号码的第9、10、11、12位数字。
IF(LEN(C2)=15,MID(C2,7,4),MID(C2,9,4))：IF是一个逻辑判断函数，表示如果C2单元格是15位，则提取第7位开始的四位数字，如果不是15位则提取自第9位开始的四位数字。
如果需要显示为“70年12月”这样的格式，请使用DATE格式，并在“单元格格式→日期”中进行设置。
2. 提取性别信息
由于报表中各位员工的序号编排是按照上级核定的编制进行的，因此不可能按照男、女固定的顺序进行编排，如果一个一个手工输入的话，既麻烦又容易出错
例如性别信息统一在B列填写，可以在B2单元格中输入公式“=IF(MOD(IF(LEN(C2)=15,MID(C2,15,1),MID(C2,17,1)),2)=1,”男”,”女”)”，其中：
LEN(C2)=15：检查身份证号码的长度是否是15位。
MID(C2,15,1)：如果身份证号码的长度是15位，那么提取第15位的数字。
MID(C2,17,1)：如果身份证号码的长度不是15位，即18位身份证号码，那么应该提取第17位的数字。
MOD(IF(LEN(C2)=15,MID(C2,15,1),MID(C2,17,1)),2)：用于得到给出数字除以指定数字后的余数，本例表示对提出来的数值除以2以后所得到的余数。
IF(MOD(IF(LEN(C2)=15,MID(C2,15,1),MID(C2,17,1)),2)=1,”男”,”女”)：如果除以2以后的余数是1，那么B2单元格显示为“男”，否则显
示为“女”。
回车确认后，即可在B2单元格显示正确的性别信息，接下来就是选中填充柄直接拖曳。
Excel 根据身份证计算年龄
Excel表格中根据身份证号码自动填出生日期、计算年龄 Excel表格中根据身份证号码自动填出生日期、计算年龄、录入男女的函数公式；
18位身份证号码转换成出生日期的函数公式：如果E2中是身份证，在F2中求出出生日期，F2=DATE(MIDB(E2,7,4),MIDB(E2,11,2),MIDB(E2,13,2)) 自动录入男女：=IF(MOD((IF(LEN(e2)=18,MID(e2,17,1),MID(e2,15,1))) ,2)=0,"女","男") 15/18位都可以的公式：转换出生日期：=IF(LEN(e2)=18,TEXT(MID(e2,7,8),"#-00-00"),"19"&TE XT(MID(e2,7,6),"#-00-00")) 自动录入男女：=IF(E2="","",IF(MOD(RIGHT(LEFT(E2,17),1),2)=0,"女","男")) 计算年龄（新旧身份证号都
可以）：=IF(AND(E2=""),"",IF(MIDB(E2,7,2)="19",107-MIDB(E2 ,9,2),107-MIDB(E2,7,2)))
一、身份证号的位数判断
在B2单元格输入如下公式“=LEN($A2)”。LEN($A2)公式的含义是求出A2单元格字符串中字符的个数。由于当初身份证输入时就是以文本形式输入的。所以用此函数正可以很方便地求到身份证号码的位数。
二、判断男女性别
第一步：提取性别识别数字。如果是18位身份证，那么倒数第二位就是性别判断的识别数字，奇数为男，偶数则为女。而如果是15位身份证，那么倒数第一位则是性别识别数字，同样奇数为男，偶数为女。所以首先应该提取这两种身份证的性别识别数字。在C2元格输入公式“=IF(B2=18,MID(A2,17,1),RIGHT(A2,1))”回车后，立刻可以得到相应的识别数字了。
函数MID(A2,17,1)的作用是从A2单元格字符串的第17个字开始截取1个字符。这正好是倒数第二个字符；函数RIGHT(A2,1)的作用则是从A2单元格字符串的右侧开始截取1个字符，也就是倒数第一个字符。至于IF函数。则进行相应的判断。公式“=IF(B2=18,MID(A2,17,1),RIGHT(A2,1))”的作用是“如果B2单元格等于18，那么从A2单元格字符串的第17位开始截取1个字符。否则从A2单元格字符串的右侧截取1个字符”。
第二步：根据识别数字判断男女性别，在D2单元格输入公式“=IF(ISEVEN($C2)=TRUE,"女","男")”，回车后就可以得到的结果了。此处函数ISEVEN($C2)的作用是判断C2单元格的数字是否为偶数，如果是，则返回结果TRUE，否则返回结果FALSE。因此，公式“=IF(ISEVEN($C2)=TRUE,"女","男")”的含义就是如果C2单元格数字为偶数，那么在当前单元格显示“女”，否则显示“男”。
三、出生日期提取
第一步：提取数字型出生日期。在E2单元格输入公式“=IF($B2=18,MID($A2,7,8),"19"&MID($A2,7,6))”并回车。这种形式的出生日期我们并不常用，此处提取出来只是为了我们下一步计算年龄时要用，也是一个辅助
列。"19"&MID($A2,7,6)的意思是把字符“19”和MID($A2,7,6)截取到的6个字符合并在一起成为一个新的字符串。
第二步：提取带连接符的出生日期。在F2单元格输入公式“=IF($B2=18,MID($A2,7,4)&"-"&MID($A2,11,2)&"-"&MID( $A2,13,2),"19"&MID($A2,7,2)&"-"&MID($A2,9,2)&"-"&MI D($A2,11,2))” 并回车，可以得到我们常用的如 “1977-08-11”的格式。
四、公式计算年龄
在G2单元格输入公式“=YEAR(NOW())-LEFT(E2,4)”并回车，年龄计算就这么简单，用当前的年份减去出生的年份就行了。所示。函数YEAR(NOW())可以返回当前的年数“2007”，而LEFT(E2,4)则从E2单元格的左侧截取4个字符，也就是出生年份。最后、选中B2:G2单元格。向下拖动填充句柄至最后一行。
=TEXT(TODAY(),"yyyy") 截取当前年
=LEFT(E2,4) 截取E2单元格前4个字符
=TEXT(TODAY(),"yyyy")-LEFT(MID(D4,7,2)&""&MID(D4,9,2)&"-"&MID(D4,11,2)&"-"&MID(D4,13,2),4)根据身份证获取岁数
豫公网安备41160202000603
站长QQ:729038198
关于我们
投诉建议