第一篇：在Excel中根据身份证号码自动生成出生生日期和性别
在Excel中根据身份证号码自动生成出生生日期和性别
【身份证号判断性别的方法】 18位的看倒数第二位，奇男偶女； 15位的看倒数第一位，奇男偶女。假如：身份证所在的列为C2
首先要判断该身份证的长度，在excel中使用len函数来获取len(C2)
如果是18位的就取第17位，即倒数第二位，Excel中使用mid函数还获取指定位置开始，指定长度的字符串，如果是15位的就去最后一位也就是第15位 =If(len(C2)=15,mid(C2,15,1),mid(C2,17,1))
获取到这个数后，再判断该数是奇数还是偶数就可以得出性别了，excel中使用mod函数实现 =IF(MOD(IF(LEN(C2)=15,MID(C2,15,1),MID(C2,17,1)),2)=1,“男”,“女”)获取年龄的函数如下：
=DATEDIF(TEXT((LEN(C2)=15)*19&MID(C2,7,6+(LEN(C2)=18)*2),“00-00-00”),TODAY(),“y”)
在Excel中根据身份证号码自动生成出生生日期和性别 根据身份证号码自动生成出生生日期 方法一：
15位身份证号：*** 输出出生日期1979/06/05 =CONCATENATE(“19”,MID(E2,7,2),“/”,MID(E2,9,2),“/”,MID(E2,11,2))公式解释： a.MID(E2,7,2)为在身份证号码中获取表示年份的数字的字符串
b.MID(E2,9,2)为在身份证号码中获取表示月份的数字的字符串 c.MID(E2,11,2)为在身份证号码中获取表示日期的数字的字符串
d.CONCATENATE(“19”,MID(E2,7,2),“/”,MID(E2,9,2),“/”,MID(E2,11,2))目的就是将多个字符串合并在一起显示。
18位身份证号：***521 输出出生日期1979/06/05 =CONCATENATE(MID(E2,7,4),“/”,MID(E2,11,2),“/”,MID(E2,13,2))方法二：
15位身份证号：*** 出生日期790605 =IF(LEN(E3)=15,MID(E3,7,6),MID(E3,9,6))18位身份证号：***521 出生日期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中根据身份证号码自动填出生日期、性别、计算年龄
出生日期：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,“女”,“男”)计算年龄：=IF(LEN(B2)=15,109-VALUE(MID(B2,7,2)),if(LEN(B2)=18,2009-VALUE(MID(B2,7,4)),“身份证错”))
说明：VALUE将一个文本字符串转换成数值，LEN返回文本字符串中的字符个数，MID从文本字符串中指定的起始位置起返回指定长度的字符，MID(文本,开始字符,所取字符数)。109表示当前日期为2009年，如果是2010年则改为110，2009表示当前日期为2009年。DATE代表日期的数字，MIDB自文字的指定起始位置起提取指定长度的字符串
第三篇：如何在Excel中填写身份证号码后自动提取出生年月日、年龄、性别
如何在Excel中填写身份证号码后自动提取出生年月日、年龄、性别
本公式只适合于18位身份证号码即二代居民身份证。
假如：A1输入身份证号码、B1显示性别、C1显示出生日期、D1显示周岁。（要注意A1、B1、C1、D1分别对应A列1行B列1行C列1行D列1行，如你的A1单元格不为你的表格第一项身份证编号则需将A1替换成你选取的单元格）
需要设置：A1单元格为文本格式、B1和D1为常规格式、C1为日期格式。（选中列右键设置单元格格式可选取格式）
1、显示性别：
B1输入公式：
=IF(A1=“",”“,IF(AND(LEN(A1)<>15,LEN(A1)<>18),”错误
“,IF(ISERROR(1*(TEXT(MID(A1,7,6+(LEN(A1)=18)*2),”#-00-00“))),”错误
“,IF(OR((1*(TEXT(MID(A1,7,6+(LEN(A1)=18)*2),”#-00-00“)))
2、C1输入公式：
=IF(A1=”“,”“,IF(AND(LEN(A1)<>15,LEN(A1)<>18),”错误
“,IF(ISERROR(1*(TEXT(MID(A1,7,6+(LEN(A1)=18)*2),”#-00-00“))),”错误
“,IF(OR((1*(TEXT(MID(A1,7,6+(LEN(A1)=18)*2),”#-00-00“)))
3、D1输入公式：
=IF(ISERROR(1*(TEXT(MID(A1,7,6+(LEN(A1)=18)*2),”#-00-00“))),”错误
“,IF(OR((1*(TEXT(MID(A1,7,6+(LEN(A1)=18)*2),”#-00-00“)))
“,DATEDIF(TEXT(MID(A1,7,6+(LEN(A1)=18)*2),”#-00-00“),TODAY(),”y")))
注意：A1、B1、C1、D1分别对应A列1行B列1行C列1行D列1行，如你的A1单元格不为你的表格第一项身份证编号则需将A1替换成你选取的单元格
如下图
那么上述B1、C1、D1输入函数中应将A1替换成F4即可。（替换简便方法：ctrl+F键调出查找对话框选择—替换—查找内容填A1—替换填你填写身份证号码的第一项单元格名称）
第四篇：EXCEL中如何从身份证号码提取出生年月日及性别
EXCEL中如何从身份证号码提取出生年月日及性别
近段时间，各校都忙坏了，学生城镇居民医疗保险材料要保，今年必须提供每位学生的身份证号码，还有性别、出生日期等信息；新生学籍要报，学籍信息包含50多项，其中也有身份证号码、性别、出生日期等信息„„。为了简化工作，提高效率，特整理出从身份证号码里提取出生年月日及性别等信息的方法，希望对大家有所帮助。
一、分析身份证号码
其实，身份证号码与一个人的性别、出生年月、籍贯等信息是紧密相连的，无论是15位还是18位的身份证号码，其中都保存了相关的个人信息。
15位身份证号码：第7、8位为出生年份(两位数)，第9、10位为出生月份，第11、12位代表出生日期，第15位代表性别，奇数为男，偶数为女。
18位身份证号码：第7、8、9、10位为出生年份(四位数)，第11、第12位为出生月份，第13、14位代表出生日期，第17位代表性别，奇数为男，偶数为女。
例如，某学生的身份证号码(15位)是***242，那么表示1989年8月7日出生，性别为女。如果能想办法从这些身份证号码中将上述个人信息提取出来，不仅快速简便，而且不容易出错，核对时也只需要对身份证号码进行检查，肯定可以大大提高工作效率。
二、转换身份证号码格式
我们先将学生的身份证号完整地输入到EXCEL表格中，这时默认为“数字”格式（单元格内显示的是科学记数法的格式），需要更改一下数字格式。选中该列中的所有身份证号后，右击鼠标，选择“设置单元格格式”。在弹出对话框中“数字”标签内的“分类”设为“文本”，然后点击确定。
三、提取个人信息
这里，我们需要使用IF、LEN、MOD、MID、DATE等函数从身份证号码中提取个人信息。假设学生的身份证号码信息已输入完毕(C列)，出生年月信息填写在D列，性别信息填写在B列。
1.提取出生年月信息
如果上交报表时需要填写出生年月日，我们需要关心身份证号码的相应部位即可，即显示为“19890807”这样的信息。在D2单元格中输入公式
=IF(LEN(C2)=15,“19”&MID(C2,7,6),MID(C2,7,8))，其中：
文本函数MID返回文本字符串中从指定位置开始的特定数目的字符，该数目由用户指定。语法：MID(text,start_num,num_chars)即MID(文本,开始字符,所取字符数)，Text 是包含要提取字符的文本字符串。
Start_num 是文本中要提取的第一个字符的位置。文本中第一个字符的start_num 为 1，以此类推。
Num_chars 指定希望 MID 从文本中返回字符的个数。
字符个数计算函数LEN返回文本字符串中的字
符数。语法：LEN(text)
Text 是要查找其长度的文本。空格将作为字符进行计数。
LEN(C2)=15：检查C2单元格中字符串的字符数目，本例的含义是检查身份证号码的长度是否是15位。
MID(C2,7,4)：从C2单元格中字符串的第7位开始提取6位数字，本例中表示提取15位身份证号码的第7、8、9、10、11、12位数字。
“19”&:表示如果身份证号码是15位，在年份前加“19”。
MID(C2,7,8)：从C2单元格中字符串的第7位开始提取8位数字，本例中表示提取18位身份证号码的第7、8、9、10、11、12、13、14位数字。
IF(LEN(C2)=15,MID(C2,7,6),MID(C2,7,8))：IF是一个逻辑判断函数，表示如果C2单元格是15位，则提取第7位开始的6位数字并在年份前加“19”，如果不是15位则提取自第7位开始的8位数字。
如果需要显示为“1989-08-07”这样的格式，可以把上面的公式稍加修改即可：
=IF(LEN(C2)=15,“19”&MID(C2,7,2)&“-”&MID(C2,9,2)&“-”&MID(C2,11,2),MID(C2,7,4)&“-”&MID(C2,11,2)&“-”&MID(C2,13,2))。
解释：如果身份证号码是15位，则从第7位开始取两位数字，并在这两位数字前加“19”，后面加“-”,从第9位开始取两位数字，并在这两位数字后面加“-”,从第11位开始取两位数字。否则，从第7位开始取四位数字，并在这四位数字后面加“-”,从第11位开始取两位数字，并在这两位数字后面加“-”,从第13位开始取两位数字。
也可以使用DATE格式，并在“单元格格式→日期”中进行设置。
如果学生的身份证号全部都是18位的新一代身份证，可以以把上面的公式进行简化：
=MID(C2,7,4)&“-”&MID(C2,11,2)&“-”&MID(C2,13,2)
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单元格显示正确的性别信息，接下来就是选中填充柄直接拖曳。现在这份报表无论是提取信息或是
核对，都方便多了。
提取性别也可以使用下面这个公式（无论是15位还是18位）： =IF(LEN(C2)=15,IF(MOD(MID(C2,15,1),2)=1,“男”,“女”),IF(MOD(MID(C2,17,1),2)=1,“男”,“女”)
如果学生的身份证号全部都是18位的新一代身份证，可以使用下面的公式： =IF(MID(C2,17,1)/2=TRUNC(MID(C2,17,1)/2),“女”,“男”)
数学函数TRUNC 将数字的小数部分截去，返回整数。
语法：TRUNC(number,num_digits)
Number 需要截尾取整的数字。
Num_digits 用于指定取整精度的数字。Num_digits 的默认值为 0。
说明：函数 TRUNC 和函数 INT 类似，都返回整数。函数 TRUNC 直接去除数字的小数部分，而函数 INT 则是依照给定数的小数部分的值，将其四舍五入到最接近的整数。函数 INT 和函数 TRUNC 在处理负数时有所不同。
如果身份证号的输入已是15或18位，还可以用公式：
=IF(MOD(LEFT(RIGHT(C2,(LEN(C2)=18)+1)),2),“男”,“女”）
RIGHT 根据所指定的字符数返回文本字符串中最后一个或多个字符。
语法：RIGHT(text,num_chars)
Text 是包含要提取字符的文本字符串。
Num_chars 指定希望 RIGHT 提取的字符数。
LEFT 基于所指定的字符数返回文本字符串中的第一个或前几个字符。
语法：LEFT(text,num_chars)
Text 是包含要提取字符的文本字符串。
Num_chars 指定要由 LEFT 所提取的字符数。
第五篇：如何在Excel里根据身份证号码自动填写性别、出生年月、年龄
如何在Excel里根据身份证号码自动填写性别、出生年月、年龄
一、分析身份证号码
其实，身份证号码与一个人的性别、出生年月、籍贯等信息是紧密相连的，无论是15位还是18位的身份证号码，其中都保存了相关的个人信息。
15位身份证号码：第7、8位为出生年份(两位数)，第9、10位为出生月份，第11、12位代表出生日期，第15位代表性别，奇数为男，偶数为女。
18位身份证号码：第7、8、9、10位为出生年份(四位数)，第11、第12位为出生月份，第13、14位代表出生日期，第17位代表性别，奇数为男，偶数为女。
例如，某员工的身份证号码(15位)是***，那么表示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单元格显示正确的性别信息，接下来就是选中填充柄直接拖曳。如图2所示，现在这份报表无论是提取信息或是核对，都方便多了
点击咨询 