身份证EXCEL函数自动提取生肖和星座表

Excel如何从身份证号码中计算出生日期、性别及年龄
如下图，要根据A列的身份证号码提取出生日期，判断性别和计算年龄
提取出生日期
现在身份证号码都是18位，代表出生日期的是第7位-第14位，总共8位数字，
所以在B2单元格输入公式：=--TEXT(MID(A2,7,8),"0-00-00")
公式说明：使用MID函数从第7位开始提取，提取8位数，但是提取出来的是一串数字，并不属于日期格式，所以使用TEXT函数将数字转换为日期格式。

判断性别
身份证号码的第17位数字是代表性别的，奇数为男生，偶数为女生，所以在C2单元格输入公式：=IF(MOD(MID(A2,17,1),2),"男","女")
公式说明：用MID函数提取第17位代表性别的数字，然后用MOD函数将第17位数跟2相除取余数，奇数跟2相除的余数为1，偶数跟2相除的余数为0，再用if函数判断MOD函数的余数是1则返回“男” 是0则返回“女”
计算年龄
在D2单元格输入公式：=DATEDIF(B2,TODAY(),"Y")
公式说明：用DATEDIF函数计算两个日期之间相差的日期间隔，用出生日期跟今天的日期（用TODAY函数表示当前的日期），计算类型为“Y”(年）
我是小螃蟹，如果您喜欢这篇教程，请帮忙点赞、转发和评论哦，感谢您的支持！。

如何利用Excel从身份证号中提取出生年月等信息一、首先要认真分析身份证号码身份证号码与一个人的籍贯、出生年月、性别等信息是紧密相连的，无论是15位还是18位的身份证号码，其中都保存了相关的个人信息。

15位身份证号码：第7、8位为出生年份(两位数)，第9、10位为出生月份，第11、12位代表出生日期，第15位代表性别，奇数为男，偶数为女。

18位身份证号码：第7、8、9、10位为出生年份(四位数)，第11、第12位为出生月份，第13、14位代表出生日期，第17位代表性别，奇数为男，偶数为女。

例如，某人的身份证号码(15位)是420983*********，那么表示1973年9月25日出生，性别为男。

一、首先要认真分析身份证号码身份证号码与一个人的籍贯、出生年月、性别等信息是紧密相连的，无论是15位还是18位的身份证号码，其中都保存了相关的个人信息。

15位身份证号码：第7、8位为出生年份(两位数)，第9、10位为出生月份，第11、12位代表出生日期，第15位代表性别，奇数为男，偶数为女。

18位身份证号码：第7、8、9、10位为出生年份(四位数)，第11、第12位为出生月份，第13、14位代表出生日期，第17位代表性别，奇数为男，偶数为女。

例如，某人的身份证号码(15位)是420983*********，那么表示1973年9月25日出生，性别为男。

图1二、其次运用EXCEL2003提取个人信息这里，我们需要使用IF、LEN、MOD、MID、DATE等函数从身份证号码中提取个人信息。

如图1所示，其中员工的身份证号码信息已输入完毕(C列)，出生年月日信息填写在D列，性别信息填写在B列。

1．提取出生年月信息由于上交报表时只需要填写出生年月，不需要填写出生日期，因此这里我们只需要关心身份证号码的相应部位即可，即显示为“6709”这样的信息。

在D2单元格中输入公式“=IF(LEN(C2)=15,MID(C2,7,4),MID(C2,9,4))”，其中：LEN(C2)=15：检查C2单元格中字符串的字符数目，本例的含义是检查身份证号码的长度是否是15位。

在Excel中自动提取身份证中生日和性别
1.转换身份证号码格式
我们先将学生的身份证号完整地输入到Excel2003表格中，这时默认为“数字”格式(单元格内显示的是科学记数法的格式)，需要更改一下数字格式。

选中该列中的所有身份证号后，右击鼠标，选择“设置单元格格式”。

在弹出对话框中“数字”标签内的“分类”设为“文本”，然后点击确定。

2.“提取出”出生日期
=MID(B2,7,4)&"年"&MID(B2,11,2)&"月"&MID(B2,13,2)&"日"
/判断性别“男女”
=IF(MID(B2,17,1)/2=TRUNC(MID(B2,17,1)/2),"女","男")
求年龄问题,要看你的数据是哪种格式(日期、文本），举例说明：假定你的A1单元格为日期格式，内容为“1986-2-20”，在B1单元格可
输入如下公式得到年龄：
=INT((TODAY()-A1)/365)。

从身份证号中提取生日，5秒完成！
1 、从身份证号中提取生日
老掉牙的技巧，不过还是经常有同学提问，再说一次。

最简公式：
=TEXT(MID(A2,7,8),'0000-00-00')
最简技巧：
数据 - 分列 - 固定宽度 - 设置为日期格式
2、根据生日计算年龄
公式：
=DATEDIF(B2,NOW(),'y')
3、根据生日算生肖
公式：
=MID('猴鸡狗猪鼠牛虎兔龙蛇马羊',MOD(YEAR(B2),12)+1,1)
4、根据生日算星座
公式：
=LOOKUP(--TEXT(C3,'mdd'),{101,'摩羯';120,'水瓶';219,'双鱼';321,'白羊';420,'金牛';521,'双子';621,'巨蟹';723,'狮子';823,'处女';923,'天秤';1023,'天蝎';1122,'射手';1222,'摩羯'})&'座'”
兰色说: 嘿嘿，今天兰色又老了一岁，不知不觉用excel已13年整了，每天分享好用的Excel教程，能帮上同学们一点点，兰色就觉得很开心。

这里也谢谢E友们一直以来对平台和兰色的支持和陪伴，如果你身边也有Excel爱好者，一定要介绍给兰色，让他/她也早日加入我们的Excel学习大家庭。

excel精英培训ID:excelpx-tete。

用Excel从身份证号码中提取信息（年龄、性别、出生地）1.出生年月日信息提取：方法一：在记录列中输入公式：=--TEXT(MID(B2,7,6+IF(LEN(B2)=15,0,2)),"#-00-00")，往下复制，无论15位还是18位身份证号码全部搞定，方法最简单。

方法二：在记录列中输入公式：=--IF(LEN(B2)=15,TEXT(MID(B2,7,6),"##-00-00"),TEXT(MID(B2,7,8),"####-00-00"))，往下复制，无论15位还是18位身份证号码全部搞定，公式增加了几个字符，原理差不多，结果一致。

原理：使用函数text、if、mid、len。

注意：1、B列存放身份证号码。

存放在其它列，则在公式中作相应调整。

2、计算出错（#V ALUE!），说明身份证号码有错。

3、日期显示格式，可在单元格格式中设置。

性别信息提取：在记录列中输入公式：=IF(LEN(B2)=15,IF(MOD(RIGHT(B2),2)=0,"女","男"),IF(MOD(LEFT(RIGHT(B2,2)),2)=0,"女","男"))无论15位还是18位身份证号码全部轻松完成。

原理：使用函数IF、LEN、MOD、LEFT、RIGHT。

注意：1、B列存放身份证号码。

存放在其它列，则在公式中作相应调整。

2、计算出错（#V ALUE!），说明身份证号码有错。

出生地信息提取：在记录列中输入公式：=LEFT(B2,6)，往下复制，然后根据代码用VLOOKUP查询发证地或者是出生地信息。

Excel文件模板：从身份证号码中提取信息使用的模板：使用Excel从身份证号码提取信息.xls点击该图标，打开该EXCEL文件，另存为××文件，即可使用。

谢谢你的使用。

【EXCEL】利用函数MID在身份证号码中提取出生日期
第一步：在单元格中填写“=”，然后点击“ƒx”插入函数，选择类别“文本”，找到函数“MID”，点击确定。

第二步：在函数参数中第一行选中身份证号码所在单元格“C2”，第二行填入“7”，第三行填入“4”，点击确定。

第三步：在后面填入“&”年”&”，然后再次插入函数“MID”。

在函数参数中第一行选中身份证号码所在单元格“C2”，第二行填入“11”，第三行填入“2”，点击确定。

（注意：双引号必须使用英文状态下的””）
第四步：在后面填入“&”月”&”，然后再次插入函数“MID”。

在函数参数中第一行选中身份证号码所在单元格“C2”，第二行填入“13”，第三行填入“2”，点击确定。

（注意：双引号必须使用英文状态下的””）
第五步：在后面填入“&”日””，点击Enter键，然后就得到了张三的出生日期。

（注意：双引号必须使用英文状态下的””）
第六步：选中第一个得到的数据，将鼠标放到表格右下角，让鼠标呈实心十字，然后下拉填充格式，得到所有数据。

excel属相公式
Excel属相公式是指利用Excel函数计算生肖属相的方法。

根据中国十二生肖的传统，每个人的出生年份都对应着不同的生肖属相，这些属相依次为鼠、牛、虎、兔、龙、蛇、马、羊、猴、鸡、狗、猪。

通过Excel属相公式，可以轻松地计算任意年份对应的生肖属相。

其中，利用MOD函数可以将年份除以12得到余数，再根据余数的不同计算出相应的生肖属相，具体计算方法如下：
=IF(MOD(A1-4,12)=0,'鼠',IF(MOD(A1-4,12)=1,'牛
',IF(MOD(A1-4,12)=2,'虎',IF(MOD(A1-4,12)=3,'兔
',IF(MOD(A1-4,12)=4,'龙',IF(MOD(A1-4,12)=5,'蛇
',IF(MOD(A1-4,12)=6,'马',IF(MOD(A1-4,12)=7,'羊
',IF(MOD(A1-4,12)=8,'猴',IF(MOD(A1-4,12)=9,'鸡
',IF(MOD(A1-4,12)=10,'狗','猪'))))))))))
其中，A1为需要计算的年份。

通过这个公式，可以快速准确地计算出任意年份对应的生肖属相，为日常工作和生活提供了便捷。

- 1 -。

如何从在excel中身份证号码里提取需要的信息一、在excel中如何从身份证号码里提取出出生年月日及性别等假如，A1是姓名，B1是身份证号码，C1是出生年月可以用以下公式求出＝IF(LEN(B2)=15,"19"&MID(B2,7,2)&"年"&MID(B2,9,2)&"月"&MID(B2,11,2)&"日",MID(B2,7,4)&"年"&MID(B2,11,2)&"月"&MID(B2,13,2)&"日")在C2设置单元格格式为“日期”即可在C2中正确提取出其出生年月2EXCEL从身份证号码求出生年月日、性别及年龄公式2007-11-26 16:40EXCEL中用身份证号码求出生年月日及年龄公式2007年11月21日星期三 17:17一、分析身份证号码其实，身份证号码与一个人的性别、出生年月、籍贯等信息是紧密相连的，无论是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日出生，性别为女。

如果能想办法从这些身份证号码中将上述个人信息提取出来，不仅快速简便，而且不容易出错，核对时也只需要对身份证号码进行检查，肯定可以大大提高工作效率。

二、如何从在excel中身份证号码里提取个人信息这里，我们需要使用IF、LEN、MOD、MID、DATE等函数从身份证号码中提取个人信息。

Excel身份证号码计算生成出生肖和星座：Excel函数不求人之前讲过通过Excel身份证号码来判断男女，求年龄等方法。

这次来说说通过身份证号码如何获取生肖以及星座的方法。

我们可以直接使用身份证号码来生成生肖和星座，也可以通过身份证号码提取出来的出生日期结果再来生成生肖和星座。

下面就让我们一起来看看公式吧！提取日期后再算出生肖步骤一、先用之前我们讲到的提取身份证出生年月日的方法来提取出一下日期。

公式：【=TEXT(MID(A2,7,8),'0-00-00')】步骤二、得出了出生年月日以后，我们就可以利用下面函数来生成出生肖了。

公式：【=MID('鼠牛虎兔龙蛇马羊猴鸡狗猪',MOD(YEAR(B2)-4,12) 1,1)】直接通过身份证号码提取生肖我们除了生成日期后提取，还可以直接将身份证号码生成生肖，这样的话，也是省去了一个步骤。

公式：【=MID('猴鸡狗猪鼠牛虎兔龙蛇马羊',MOD(RIGHT('19'&MID(A2,7,ABS(LEN(A2)*2-32)),4),12) 1,1)】提取日期后再生成星座同样的先用公式提取日期，再用提取出来的结果生成生肖。

提取日期函数公式：【=TEXT(MID(A2,7,8),'0-00-00')】用日期结果生成星座函数公式：【=LOOKUP(--TEXT(B2,'mdd'),{101,'摩羯座';120,'水瓶座';219,'双鱼座';321,'白羊座';420,'金牛座';521,'双子座';621,'巨蟹座';723,'狮子座';823,'处女座';923,'天秤座';1023,'天蝎座';1122,'射手座';1222,'摩羯座'})】直接通过身份证号码提取星座同样的，下面这个公式可以直接通过身份证号码来生成出星座。

身份证号中的出生日期如何用excel批量提取出来？
有了身份证号，如何快速批量把出生日期提取出来。

下面介绍两种方法：
第一种方法：用函数将身份证号中的出生日期提取出来。

单击B2单元格，在单元格中输入公式：=IF(LEN(A2)=15,DATE(MID(A2,7,2),MID(A2,9,2),MID(A2,11,2)),IF( LEN(A2)=18,DATE(MID(A2,7,4),MID(A2,11,2),MID(A2,13,2)),'号码有错'))。

说明：如果身份证号确保正确也可以用这个公式：=TEXT(IF(LEN(A2)=15,19,'')&MID(A2,7,6+IF(LEN(A2)=18,2,0)),'#-00-00')。

如果身份证都是18位，可以用这个公式：
=DATE(MID(A2,7,4),MID(A2,11,2),MID(A2,13,2))
注意：一定要将存放日期的单元格格式设置成日期类型，具体设置如下：
选中存放日期的单元格，单击右键，在弹出的菜单中选择设置单元格格式：
然后在弹出的对话框中选择数字标签，选择日期类型，单击确定。

拖动填充柄，复制公式到所有需要提出日期的单元格中，大功告成：
第二种方法：用数据菜单中的分裂命令。

将A列中的身份证号复制到B列，选中B列所有身份证号，选择数据菜单，单击分裂命令，打开分列向导：
选择固定宽度，单击下一步，在日期前后分别建立分列线，单击
下一步：
选择中间日期数据列，将列数据格式设置成日期型，单击完成，大功告成。

完成效果如下：
好累啊，给个大拇指好不好，(*^__^*) 嘻嘻……。