admin 管理员组文章数量: 887021
2024年1月5日发(作者:贷款源码下载)
从身份证号码中自动生成性别和生日
生成性别:(其中B2是身份证号码所在列)
一性别双击性别所在列的第二行,然后输入下面公式,然后按ENTER键;再利用下拉方式将公式复制到该列的其他行中即可
1=CHOOSE(MOD(IF(LEN(B2)=18,MID(B2,17,1),IF(LEN(B2)=15,RIGHT(B2,1),"")),2)+1,"女","男")
2=IF(MOD(IF(LEN(B2)=15,MID(B2,15,1),MID(B2,17,1)),2)=1,"男","女")
3=IF(LEN(B2)=15,IF(MOD(MID(B2,15,1),2)=1,"男","女"),IF(MOD(MID(B2,17,1),2)=1,"男","女"))
二出生日期提取出生日期:(其中B2是身份证号码所在列)
双击出生日期所在列的第二行,然后输入下面公式,然后按ENTER键;再利用下拉方式将公式复制到该列的其他行中即可
=DATE(MID(B2,7,4),MID(B2,11,2),MID(B2,13,2))
三计算年龄:(其中C3是出生日期所在列)
双击年龄所在列的第二行,然后输入下面公式,然后按ENTER键;再利用下拉方式将公式复制到该列的其他行中即可
=YEAR(NOW())-YEAR(C3)
Excel自动从身份证中提取生日性别
出处:天空软件作者:佚名日期:2009-09-16
每年新入学的一年级学生,都需要向上级教育部门上报一份包含身份证号、出生年月等内容的电子表格,以备建立全省统一的电子学籍档案。数百个新生,就得输入数百行相应数据,这可不是个轻松活儿。有没有什么办法能减轻一下输入工作量、提高一下效率呢?其实,我们只需在Excel2003中将学生的身份证号完整地输入后,它就可以帮我们自动填好出生日期和性别。
现在学生的身份证号已经全部都是18位的新一代身份证了,里面的数字都是有规律的。前6位数字是户籍所在地的代码,7-14位就是出生日期。第17位“2”代表的是性别,偶数为女性,奇数为男性。我们要做的就是把其中的部分数字想法“提取出来”。
STEp1,转换身份证号码格式
我们先将学生的身份证号完整地输入到Excel2003表格中,这时默认为“数字”格式(单元格内显示的是科学记数法的格式),需要更改一下数字格式。选中该列中的所有身份证号后,右击鼠标,选择“设置单元格格式”。在弹出对话框中“数字”标签内的“分类”设为“文本”,然后点击确定。
STEP2,“提取出”出生日期
将光标指针放到“出生日期”列的单元格内,这里以C2单元格为例。然后输入“=MID(B2,7,4)&"年"&MID(B2,11,2)&"月"&MID(B2,13,2)&"日"”(注意:外侧的双引号不用输入,函数式中的引号和逗号等符号应在英文状态下输入)。回车后,你会发现在C2单元格内已经出现了该学生的出生日期。然后,选中该单元格后拖动填充柄,其它单元格内就会出现相应的出生日期。如图1 。
图1 通过上述方法,系统自动获取了出生年月日信息
小提示:MID函数是EXCEL提供的一个“从字符串中提取部分字符”的函数命令,具体使用格式在EXCEL中输入MID后会出现提示。
STEP3,判断性别“男女”
选中“性别”列的单元格,如D2。输入“=IF(MID(B2,17,1)/2=TRUNC(MID(B2,17,1)/2),"女","男")”(注意如上)后回车,该生“是男还是女”已经乖乖地判断出来了。拖动填充柄让其他学生的性别也自动输入。如图2。
图2 性别被自动填入指定位置
这样,通过两个简单的函数,我们就可以让EXCEL从身份证号中自动提取出生日期和性别并填充到单元格内,极大地减轻了我们的输入工作量。最后效果如图3。
图3 用EXECL可以自动识别并填入学生信息
根据身份证号码让Excel自动输入出生日期和性别
方法一:
15位身份证号:412 输出出生日期1979/06/05
=CONCATENATE("19",MID(E2,7,2),"/",MID(E2,9,2),"/",MID(E2,11,2))
公式解释:
(E2,7,2)为在身份证号码中获取表示年份的数字的字符串
b. MID(E2,9,2) 为在身份证号码中获取表示月份的数字的字符串
c. MID(E2,11,2) 为在身份证号码中获取表示日期的数字的字符串
ENATE("19",MID(E2,7,2),"/",MID(E2,9,2),"/",MID(E2,11,2))目的就是将多个字符串合并在一起显示。
18位身份证号:415521 输出出生日期1979/06/05
=CONCATENATE(MID(E2,7,4),"/",MID(E2,11,2),"/",MID(E2,13,2))
方法二:
15位身份证号:412 出生日期790605
=IF(LEN(E3)=15,MID(E3,7,6),MID(E3,9,6))
18位身份证号:415521 出生日期790605
=IF(LEN(E3)=15,MID(E3,7,6),MID(E3,9,6))
公式解释:
LEN(E2)=15:检查E2单元格中字符串的字符数目,本例的含义是检查身份证号码的长度是否是15位。
MID(E2,7,6):从E2单元格中字符串的第7位开始提取6位数字,本例中表示提取15位身份证号码的第7、8、9、10、11、12位数字。
MID(E2,9,6):从C2单元格中字符串的第9位开始提取6位数字,本例中表示提取18位身份证号码的第9、10、11、12、13、14位数字。
=IF(LEN(E2)=15,MID(E2,7,6),MID(E2,9,6)):IF是一个逻辑判断函数,表示如果额E2单元格是15位,则提取第7位开始的6位数字,如果不是15位则提取自第9位开始的6位数字。
根据身份证号码自动生成性别:
方法一:
在C列输入身份证号,在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单元格显示为“男”,否则显示为“女”。
15位身份证,看最后一位,奇男偶女;18位的,看第17位数,也是奇男偶女。
方法二:
如果你是想在Excel表格中,从输入的身份证号码内让系统自动提取性别,可以输入以下公式:
=IF(LEN(C2)=15,IF(MOD(MID(C2,15,1),2)=1,"男","女"),IF(MOD(MID(C2,17,1),2)=1,"男","女"))
公式内的“C2”代表的是输入身份证号码的单元格。
EXCEL中用身份证号码自动生成出生年月年龄及性别
在学校的人事管理中经常会遇到需要统计教职工的年龄的问题,但案头的原始资料只有身份证号码,其实这足够了。在EXCEL中,引用其内置函数利用身份证号码达到此目的比较简单。
1、身份证号码简介(18位):
1~6位为地区代码;7~10位为出生年份;11~12位为出生月份;13~14位为出生日期;15~17位为顺序号,并能够判断性别,奇数为男,偶数为男;第18位为校验码。
2、确定“出生日期”:
18位身份证号码中的生日是从第7位开始至第14位结束。提取出来后为了计算“年龄”应该将“年”“月”“日”数据中添加一个“/”或“-”分隔符。
①正确输入了身份证号码。(假设在D2单元格中)
②将光标定位在“出生日期”单元格(E2)中,然后在单元格中输入函数公式“=MID(A2,7,4)&"-"&MID(A2,11,2)&"-"&MID(A2,13,2)”即可计算出“出生日期”。
关于这个函数公式的具体说明:MID函数用于从数据中间提取字符,它的格式是:MID(text,starl_num,num_chars)。
Text是指要提取字符的文本或单元格地址(上列公式中的D2单元格)。
starl_num是指要提取的第一个字符的位置(上列公式中依次为7、11、13)。
num_chars指定要由MID所提取的字符个数(上述公式中,提取年份为4,月份和日期为2)。
多个函数中的“&”起到的作用是将提取出的“年”“月”“日”信息合并到一起,“/”或“-” 分隔符则是在提取出的“年”“月”“日”数据之间添加的一个标记,这样的数据以后就可以作为日期类型进行年龄计算。操作效果如下图:
3、确定“年龄”:
“出生日期”确定后,年龄则可以利用一个简单的函数公式计算出来了:将光标定位在“年龄”单元格中,然后在单元格中输入函数公式“=INT((TODAY()-E2)/365)”即可计算出“年龄”。
关于这个函数公式的具体说明:
①TODAY函数用于计算当前系统日期。只要计算机的系统日期准确,就能立即计算出当前的日期,它无需参数。操作格式是TODAY()。
②用TODAY()-E2,也就是用当前日期减去出生日期,就可以计算出这个人的出生天数。
③再除以 “365”减得到这个人的年龄。
④计算以后可能有多位小数,可以用【减少小数位数】按钮,将年龄的数值变成“整数”,也可在公式= (TODAY()-E2)/365中再嵌套一个 “INT”函数取整数,即“ =INT((TODAY()-E2)/365)”,这样就会自动将后面的小数去掉,只保留整数部分。操作效果如下图:
4、确定“性别”:
每个人的性别可以利用“身份证号码”进行判断,18位身份证号码中,第15~17位为顺序号,奇数为男,偶数为女。
将光标定位在“性别”单元格中,然后在单元格中输入函数公式“=IF(VALUE(MID(A2,15,3))/2=INT(VALUE(MID(A2,15,3))/2),"女","男")”即可计算出“性别”。
关于这个函数公式的具体说明:
①函数公式中,MID(D2,15,3)的含义是将身份证中的第15~17位提取出来。
②VALUE(MID(D2,15,3))的含义是将提取出来的文本数字转换成能够计算的数值。
③VALUE(MID(D2,15,3))/2=INT(VALUE(MID(D2,15,3))/2)的含义是判断奇偶。(“INT”在上面说过是取整函数,如果是偶数,则前后相等;如果是奇数,则前后不相等。)
④=IF(VALUE(MID(D2,15,3))/2=INT(VALUE(MID(D2,15,3))/2),"女","男")的含义是若是“偶数”就填写“女”,若是“奇数”就填写“男”。
版权声明:本文标题:从身份证号码中自动生成性别和生日 内容由网友自发贡献,该文观点仅代表作者本人, 转载请联系作者并注明出处:http://www.freenas.com.cn/jishu/1704448350h460214.html, 本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容,一经查实,本站将立刻删除。
发表评论