在Excel表格中输入身份证号码、出生日期和年龄的技巧

第一篇：在Excel表格中输入身份证号码、出生日期和年龄的技巧

在Excel表格中输入身份证号码、出生日期和年龄的技巧

在使用Excel表格进行人员信息等相关操作时，常常需要用到身份证号码、出生日期和年龄等信息。如何将这些信息方便地进行输入呢？下面是我在相关操作中用到的技巧：

假设需要在A1单元格输入身份证号码。

1、输入身份证号码的方法：选中A1单元格，设置单元格格式为“文本”，然后再使用数字键盘输入身份证号码；不设置单元格格式也可以，但必须先输入英文格式的“'”（引号内的符号），再输入身份证号码的数字。

2、提取出生日期的方法：在需要输入出生日期的单元格（比如B1）中，设置单元格格式为“日期”，并选择好需要的日期格式，再输

入“=IF(OR(LEN(A1)=18,LEN(A1)=15),TEXT(MID(A1,7,6+(LEN(A1)=18)*2),“#-00-00”)+0,“身份证错误”)”（引号内的公式）后回车就可以了；

也可以使用

“=TEXT(IF(LEN(A1)=15,“19”,)&MID(A1,7,IF(LEN(A1)=18,8,l)),“####-##-##”)”（引号内的公式）进行提取，但此时B1单元格中的日期格式是固定的“####-##-##”格式。

也可以

“=MID(A1,7,4)&-MID(A1,11,2)&-MID(A1,13,2)”（不含引号，其中蓝色突出标识的为你所引用数据，即身份证号码所在的单元格）回车即可提取到1980-02-14格式的出生年月日了

也可以

如果需要1980年2月14日格式的，可以用下面的公式“=MID(F2,7,4)&“年”&MID(F2,11,2)&“月”&MID(F2,13,2)&“日””（不含引号）（“＆”有合并的意思）

3、提取年龄的方法：提取年龄的方法有两种，一种是根据当前年份确定年龄的方法，一种是根据特定日期确定年龄（比如学校招收一年级新生时，要根据8月31日来确定年龄是否符合要求）的方法。第一种方法的公式是“=YEAR(TODAY())-MID(A1,7,4)”；第二种方法的公式是

“=DATEDIF(TEXT(MID(A1,7,LEN(A1)/2-1),“#-00-00”),“2010-8-31”,“Y”)”。

4、提取性别的方法：

=IF(LEN(A1)=15,IF(VALUE(RIGHT(A1,3))/2=INT(VALUE(RIGHT(A1,3))/2),“

女

”,“

男”),IF(VALUE(MID(A1,15,3))/2=INT(VALUE(MID(A1,15,3))/2),“女”,“男”))

第二篇：身份证号码提取年龄,出生日期,等

身份证号码提取年龄

=IF(LEN(A1)>15,YEAR(NOW())-MID(A1,7,4)+1,YEAR(NOW())-(MID(A1,7,2)+1900)+1)假设A1存放的号码，B1输出结果，那么在B1中输入：

=IF(LEN(TRIM(A1))=18,YEAR(TODAY())-MID(A1,7,4),RIGHT(YEAR(TODAY()),2)+100-MID(A1,7,2))

公式考虑了身份证号码是15位还是18位的问题

=YEAR(NOW())-MID(A1,7,4)1 用mid函数提取出生年份，然后用现在身份证号码在A列（在A1单元格里）B1输入 =MID(A1,7,8)回车后 公式

例如：

身份证号***000（填写在A1）在B1除填写公式：

=TEXT(TODAY(),“YYYY”)-(IF(LEN(A1)=18,“",19)&LEFT(REPLACE(A1,1,6,”“),2+(LEN(A1)=18)*2))回车后，B1将显示：26

根据身份证号码（15位和18位通用）自动提取性别和出生年月的自编公式，供需要的网友参考：

说明：公式中的B2是身份证号

1、根据身份证号码求性别：

=IF(LEN(B2)=15,IF(MOD(VALUE(RIGHT(B2,3)),2)=0,”女“,”男“),IF(LEN(B2)=18,IF(MOD(VALUE(MID(B2,15,3)),2)=0,”女“,”男“),”身份证错“))

2、根据身份证号码求出生年月：

=IF(LEN(B2)=15,CONCATENATE(”19“,MID(B2,7,2),”.“,MID(B2,9,2)),IF(LEN(B2)=18,CONCATENATE(MID(B2,7,4),”.“,MID(B2,11,2)),”身份证错“))

3、根据身份证号码求年龄：

=IF(LEN(B2)=15,year(now())-1900-VALUE(MID(B2,7,2)),if(LEN(B2)=18,year(now())-VALUE(MID(B2,7,4)),”身份证错“))

通过身份证号取得出身日期，年龄，是很多人力资源朋友遇到的问题；在这里个人的一些小经验分享给大家； 工具/原料

 Excel 2000及以上版本

方法/步骤

1.1 新建Excel文档；

2.2 根据需要，在新建的表头的上分别输入，员工姓名，身份证号，出生日期，年龄，入职日期，服务年限

3.3 将员工的姓名及身份证号从准备好的档案中copy过来；

4.4 在第二行，第二列出生日期单元格中，输入：

=IF(LEN(B2)<18;(DATE((”19“&MID(B2;7;2));MID(B2;9;2);MID(B2;11;2)));DATE(MID(B2;7;4);MID(B2;11;2);MID(B2;13;2)))

注：LEN(B2)<18，是判断身份证号是15位还是18位的；

DATE（YYYY;MM;DD），是将取得的年、月、日转换成时间格式；

MID（text;start_num;num_chars）,功能是根据text的内容，截取字符串；start_num：截取第1个字符所在的位置；num_chars：截取的字符数量。例如：Mid(b2;7;2)，表示从身份证的第7位开始，截取2个字符；

5.5 在第二行的，第三列单元格中，输入： =DATEDIF(C2;TODAY();”y“)这个方法是计算到当前日期，员工的年龄；

6.6 在第二行的，第四列单元格中，输入：员工到岗日期，单元格格式设为日期格式；即:YYYY-MM-DD

7.7 在第二行的，第五列单元格中，输入： =DATEDIF(E2;TODAY();”y")即可计算到当前日期的，服务年限

8.8 选中C2，D2，复制单元格；

9.9 选中C列，D列剩余的单元格，选粘贴，即可自动计算每个员工的出生日期及年龄；

第三篇：由身份证号码求出生日期、年龄、性别

excel中由身份证号求出生日期、性别、年龄

（1）根据身份证号码求性别 ：=IF(VALUE(RIGHT(B2,3))/2=INT(VALUE(RIGHT(B2,3))/2),“女”,“男”)

（2）根据身份证号码求出生日期：

=CONCATENATE(MID(B2,7,4),“年”,MID(B2,11,2),“月”,MID(B2,13,2),“日”)

（3）根据身份证号码求出年龄：=CONCATENATE(DATEDIF(D2,TODAY(),“y”),“周岁”)

或 =DATEDIF(D3,TODAY(),“y”)

1、身份证号码相关知识

在了解如何实现自动从身份证号码中提取出生年月、性别信息之前，首先需要了解身份证号码所代表的含义。我们知道，当今的身份证号码有15/18位之分。早期签发的身份证号码是15位的，现在签发的身份证由于年份的扩展（由两位变为四位）和末尾加了效验码，就成了18位。这两种身份证号码将在相当长的一段时期内共存。两种身份证号码的含义如下：

（1）15位的身份证号码：1~6位为地区代码，7~8位为出生年份(2位)，9~10位为出生月份，11~12位为出生日期，第13~15位为顺序号，并能够判断性别，奇数为男，偶数为女。

（2）18位的身份证号码：1~6位为地区代码，7~10位为出生年份(4位)，11~12位为出生月份，13~14位为出生日期，第15~17位为顺序号，并能够判断性别，奇数为男，偶数为女。18位为效验位。

2、应用函数

在此例中为了实现数据的自动提取，应用了如下几个Excel函数。（1）IF函数：根据逻辑表达式测试的结果，返回相应的值。IF函数允许嵌套。

语法形式为：IF（logical_test, value_if_true,value_if_false）（2）CONCATENATE：将若干个文字项合并至一个文字项中。语法形式为：CONCATENATE(text1,text2……)

（3）MID：从文本字符串中指定的起始位置起，返回指定长度的字符。语法形式为：MID(text,start_num,num_chars)（4）TODAY：返回计算机系统内部的当前日期。语法形式为：TODAY（）

（5）DATEDIF：计算两个日期之间的天数、月数或年数。语法形式为：DATEDIF(start_date,end_date,unit)（6）VALUE：将代表数字的文字串转换成数字。语法形式为：VALUE(text)（7）RIGHT：根据所指定的字符数返回文本串中最后一个或多个字符。语法形式为：RIGHT(text,num_chars)（8）INT：返回实数舍入后的整数值。语法形式为：INT(number)

4、公式写法及解释（以员工Andy为例说明）

说明：为避免公式中过多的嵌套，这里的身份证号码限定为15位的。如果您看懂了公式的话，可以进行简单的修改即可适用于18位的身份证号码，甚至可适用于15、18两者并存的情况。

（1）根据身份证号码求性别

=IF(VALUE(RIGHT(E4,3))/2=INT(VALUE(RIGHT(E4,3))/2),“女”,“男”)公式解释：a.RIGHT(E4,3)用于求出身份证号码中代表性别的数字，实际求得的为代表数字的字符串

b.VALUE(RIGHT(E4,3)用于将上一步所得的代表数字的字符串转换为数字 c.VALUE(RIGHT(E4,3))/2=INT(VALUE(RIGHT(E4,3))/2用于判断这个身份证号码是奇数还是偶数，当然你也可以用Mod函数来做出判断。

d.=IF(VALUE(RIGHT(E4,3))/2=INT(VALUE(RIGHT(E4,3))/2),“女”,“男”)及如果上述公式判断出这个号码是偶数时，显示“女”，否则，这个号码是奇数的话，则返回“男”。

（2）根据身份证号码求出生日期

=CONCATENATE(“19”,MID(E4,7,2),“/”,MID(E4,9,2),“/”,MID(E4,11,2))公式解释：a.MID(E4,7,2)为在身份证号码中获取表示年份的数字的字符串 b.MID(E4,9,2)为在身份证号码中获取表示月份的数字的字符串 c.MID(E4,11,2)为在身份证号码中获取表示日期的数字的字符串 d.CONCATENATE(“19”,MID(E4,7,2),“/”,MID(E4,9,2),“/”,MID(E4,11,2))目的就是将多个字符串合并在一起显示。

（3）根据参加工作时间求年资（即工龄）=CONCATENATE(DATEDIF(F4,TODAY(),“y”),“年”,DATEDIF(F4,TODAY(),“ym”),“个月”)公式解释：

a.TODAY()用于求出系统当前的时间

b.DATEDIF(F4,TODAY(),“y”)用于计算当前系统时间与参加工作时间相差的年份

c.DATEDIF(F4,TODAY(),“ym”)用于计算当前系统时间与参加工作时间相差的月份，忽略日期中的日和年。

d.=CONCATENATE(D

第四篇：Excel表格中如何依据据身份证号码自动填出生日期

一、excel里依据身份证号自动生成年龄

1、假设A1为身份号，15、18位都可，在B1显示此人年龄（至2009年底计算），公式如下：

=2009-LEFT(IF(LEN(A1)=15,19&TEXT(MID(A1,7,6),“00-00-00”),TEXT(MID(A1,7,8),“0000”)),4)

2、或者采取下列公式：

=IF((RIGHT(20090528-IF(LEN(A1)=15,19&TEXT(MID(A1,7,6),“00-00-00”),TEXT(MID(A1,7,8),“00000000”)),4))>0,LEFT(20090523-IF(LEN(A1)=15,19&TEXT(MID(A1,7,6),“00-00-00”),TEXT(MID(A1,7,8),“00000000”)),2),LEFT(20090523-IF(LEN(A1)=15,19&TEXT(MID(A1,7,6),“00-00-00”),TEXT(MID(A1,7,8),“00000000”)),2)-1)

3、年龄（周岁）：=DATEDIF(A2,B2,“Y”)&“年”&MOD(DATEDIF(A2,B2,“M”),12)&“月”，A2为出生年月，B2为截止日期

二、提取出生年月信息

由于只需要填写出生日期，因此这里我们只需要关心身份证号码的相应部位即可，在C2单元格中输入公式“=IF(LEN(A2)=15,MID(A2,7,4),MID(A2,9,4))”，其中：

LEN(A2)=15：检查A1单元格中字符串的字符数目，本例的含义是检查身份证号码的长度是否是15位。

MID(A2,7,4)：从A1单元格中字符串的第7位开始提取四位数字，本例中表示提取15位身份证号码的第7、8、9、10位数字。

MID(A2,9,4)：从A1单元格中字符串的第9位开始提取四位数字，本例中表示提取18位身份证号码的第9、10、11、12位数字。

IF(LEN(A2)=15,MID(A2,7,4),MID(A2,9,4))：IF是一个逻辑判断函数，表示如果A2单元格是15位，则提取第7位开始的四位数字，如果不是15位则提取自第9位开始的四位数字。

如果需要显示为“70年12月”这样的格式，请使用DATE格式，并在“单元格格式→日期”中进行设置。

三、提取性别信息

例如性别信息统一在B列填写，可以在B2单元格中输入公式“=IF(MOD(IF(LEN(A2)=15,MID(A2,15,1),MID(A2,17,1)),2)=1,“男”,“女”)”，其中：

LEN(A2)=15：检查身份证号码的长度是否是15位。

MID(A2,15,1)：如果身份证号码的长度是15位，那么提取第15位的数字。MID(A2,17,1)：如果身份证号码的长度不是15位，即18位身份证号码，那么应该提取第17位的数字。

MOD(IF(LEN(A2)=15,MID(A2,15,1),MID(A2,17,1)),2)：用于得到给出数字除以指定数字后的余数，本例表示对提出来的数值除以2以后所得到的余数。IF(MOD(IF(LEN(A2)=15,MID(A2,15,1),MID(A2,17,1)),2)=1,“男”,“女”)：如果除以2以后的余数是1，那么B2单元格显示为“男”，否则显示为“女”。

回车确认后，即可在B2单元格显示正确的性别信息，接下来就是选中填充柄直接拖曳。

四、根据身份证号码（15位和18位通用）自动提取性别和出生年月的自编公式，供需要的网友参考：

说明：假设公式中的A2是身份证号

1、根据身份证号码求性别：=IF(LEN(A2)=15,IF(MOD(VALUE(RIGHT(A2,3)),2)=0,“女”,“男”),IF(LEN(A2)=18,IF(MOD(VALUE(MID(A2,15,3)),2)=0,“女”,“男”),“身份证错”))

2、根据身份证号码求出生年月：=IF(LEN(A2)=15,CONCATENATE(“19”,MID(A2,7,2),“.”,MID(A2,9,2)),IF(LEN(A2)=18,CONCATENATE(MID(A2,7,4),“.”,MID(A2,11,2)),“身份证错”))

3、根据身份证号码求年龄：=IF(LEN(A2)=15,year(now())-1900-VALUE(MID(A2,7,2)),if(LEN(A2)=18,year(now())-VALUE(MID(A2,7,4)),“身份证错”))

五、输入身份证号的同时显示户口所在地：

假设在SHEET2中A列为六位代码，B列为对应区域，然后SHEET1中，B2为身份证号码，则在空白单元格中输入：

=IF(A1=“",”“,VLOOKUP(--MID(A1,1,6),Sheet2!A:B,2,))此公式适用于SHEET2中A列为六位代码为数字格式。

如果SHEET2中A列为六位代码为文本格式，则公式为： =IF(A2=”“,”“,VLOOKUP(MID(A2,1,6),Sheet2!A:B,2,))

如果引用的表二的名称不是“Sheet2”,而是“表二”，则把以上公式的“Sheet2“改为“表二”

身份证的前六位表示户口所在地。需要另建一个工作表存放全国各地（县）的代码，然后用vlookup函数。

这步做了，在SHEET2中A列为六位代码，B列为对应区域，然后SHEET1中，=VLOOKUP(tz($B2),表二!$A$1:$B$3239,2,FALSE)，用了这个函数，还是不行

转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“& TEXT(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)))

WPS表格提取身份证详细信息

一、身份证号的位数判断

在B2单元格输入如下公式“=LEN($A2)”，回车后即可得到A2单元格身份证号码的数字位数，如图1所示。LEN($A2)公式的含义是求出A2单元格字符串中字符的个数。由于当初身份证输入时就是以文本形式输入的，所以用此函数正可以很方便地求到身份证号码的位数。

二、判断男女性别

第一步：提取性别识别数字。如果是18位身份证，那么倒数第二位就是性别判断的识别数字，奇数为男，偶数则为女。而如果是15位身份证，那么倒数第一位则是性别识别数字，同样奇数为男，偶数为女。所以首先应该提取这两种身份证的性别识别数字。在C2单元格输入公式“=IF(B2=18,MID(A2,17,1),RIGHT(A2,1))”，回车后，立刻可以得到相应的识别数字了。如图2 所示。

图2 WPS中键入函数

函数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,”女“,”男“)”，回车后就可以得到的结果了。如图3所示。

图3 WPS中键入函数判断男、女性别

此处函数ISEVEN($C2)的作用是判断C2单元格的数字是否为偶数，如果是，则返回结果TRUE，否则返回结果FALSE。因此，公式“=IF(ISEVEN($C2)=TRUE,”女“,”男“)”的含义就是如果C2单元格数字为偶数，那么在当前单元格显示“女”，否则显示

三、出生日期提取

第一步：提取数字型出生日期。在E2单元格输入公式 “=IF($B2=18,MID($A2,7,8),”19“&MID($A2,7,6))”并回车，如图4所示。这种形式的出生日期我们并不常用，此处提取出来只是为了我们下一步计算年龄时要用，也是一个辅助列。”19“&MID($A2,7,6)的意思是把字符“19”和 MID($A2,7,6)截取到的6个字符合并在一起成为一个新的字符串。其它的，就不用再解释了吧？

图4 WPS中键入函数判断出生日期

第二步：提取带连接符的出生日期。在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)&”-“&MID($A2,11,2))” 并回车，如图5所示，可以得到我们常用的如 “1977-08-11”的格式。此处用到的函数在前面已经有过解释，所以不再罗嗦了。

图5 WPS中键入函数判断特殊日期

四、公式计算年龄

在G2单元格输入公式“=YEAR(NOW())-LEFT(E2,4)”并回车，年龄计算就这么简单，用当前的年份减去出生的年份就行了。如图6所示。函数YEAR(NOW())可以返回当前的年数“2007”，而LEFT(E2,4)则从E2单元格的左侧截取4个字符，也就是出生年份。

图6 WPS中用公式计算年龄

最后，选中B2:G2单元格，向下拖动填充句柄至最后一行，那么我们所需要的所有数据就有了，再选中B列、C列、E列，并点击右键，在弹出的右键菜单中点击“隐藏”命令，将这些辅助列隐藏起来，就可以得到一份完整的表格了。

很简单吧？利用WPS的函数功能，只需要设置一下相应的参数，把一个个隐藏在身份证号码里的信息提取出来，完成上面所有操作用时也不到半小时，将所得到的表格美化一下，就可以向领导交差了，呵呵。

Excel输入身份证号码及提取信息

在日常办公中Excel是大家普遍使用的办公助手，在制作表格的时候，输入身份证号码也是大家经常遇到的。有什么办法能更快、更好的输入身份证号码呢？下面就教你两招！

一、解决输入身份证号码的问题

默认情况下在Excel当前单元格中输入的数字位数如果超过11位（不含11位）时，系统将以“科学记数”格式显示输入的数字；当输入的数字位数超过15位（不含15位）时，系统将15位以后的数字全部显示为“0”。这样一来，如果我们要输入身份证号码（15位或18位），身份证号码就不能正确显示出来了。此时，我们有两种办法解决此问题：

1、利用数字标签

选中需要输入身份证号码的单元格（区域），打开“单元格格式”对话框，进入“数字”标签，在“分类”下面选中“文本”选项，确定退出，再输入身份证号码即可。

2、在输入身份证号码时，在号码前面加上一个英文状态下的单引号“'”，即可让身份证号码完全显示出来（该单引号在确认后是不会显示出来的）。

二、让单元格内自动添加身份证前面相同部分

大家知道，身份证号码的前6位，是省、市、区（县）的代号（如资中县是“511025”等），如果持证人都是资中县的，输入这样的的身份证号码，可以让单元格格式的“数字”标签帮你减轻输入的工作量：选中需要输入身份证号码的单元格区域，单击Excel上面菜单栏的格式打开“单元格”对话框（或者右键点击设置单元格格式），进入默认的“数字”标签，在“分类”下面选中最下面的“自定义”选项，然后在右侧“类型”下面的方框中输入：”511025“@，确定退出。以后输入身份证号码第7位及以后的数字（如“220324621”）后，系统会自

动在前面加上相应的代码，形成完整的身份证号码（如“***”）。如果是18位的身份证号码，则这样设置比较好：“自定义”选项右侧“类型”下面的方框中输入：”51102519“@，确定退出。以后输入身份证号码9位及以后的数字（如“2203246211”）后，系统会自动在前面加上相应的51102519，形成完整的身份证号码（如“***211”）。

同理，其它需要输入数字含相同部分的也可按照这个方法输入。

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所示，现在这份报表无论是提取信息或是核对，都方便多了！

在中验证身份证 ．excel．．．．．．．．．．．=IF(RIGHT(B2)=MID(”10X98765432“,MOD(SUM(MID(B2,ROW($1:$17),1)*2^(18-R．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．OW($1:$17))),11)+1,1),”真“,”假“)(同时按 ．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．Ctrl+Shift+Enter)．．．．．．．．．．．．．．．．．

图1

1．提取性别信息

由于报表中各位员工的序号编排是按照上级核定的编制进行的，因此不可能按照男、女固定的顺序进行编排，如果一个一个手工输入的话，既麻烦又容易出错。例如性别信息统一在B列填写，可以在B2单元格中输入公式”=IF(MOD((IF(LEN(B2)=15,RIGHT(B2),MID(B2,17,1))),2)=0,“女”,“男”)“，如图2所示。

图2 身份证号全部改为18位:

公式”=RIGHT(B2,1)=MID(“10X98765432”,MOD(SUM(MID(B2,ROW(INDIRECT(“1:17”)),1)*2^(18-ROW(INDIRECT(“1:17”)))),11)+1,1)“。15位转18位？？？

=IF(LEN(A1)=15,REPLACE(A1,7，19)&MID(”10X98765432“,MOD(SUMPRODUCT(--MID(REPLACE(A1,7，19),ROW(1:17),1),2^(18-ROW(1:17))),11)+1,1),A1)=IF(LEN(E4)=15,REPLACE(E4,7，19)&MID(”10X98765432“,MOD(SUM(MID(REPLACE(E4,7，19),ROW(INDIRECT(”1:17“)),1)*2^(18-ROW(INDIRECT(”1:17“)))),11)+1,1),E4)

假设A列自A2起是身份证号（15位或18位）。

1、身份证号全部改为18位，输入数组公式：=IF(LEN(A2)=15,REPLACE(A2,7，19)&MID(”10X98765432“,MOD(SUM(MID(REPLACE(A2,7，19),ROW(INDIRECT(”1:17“)),1)*2^(18-ROW(INDIRECT(”1:17“)))),11)+1,1),A2)注意：数组公式输入方法：输入公式后不要按回车，而是按。．．．．．．．．．．．．．．．．．．．．．．．．．．Ctrl+Shift+Enter．．．．．．．．．．．．．．．．

2、身份证号全部改为15位，输入公式：=IF(LEN(A2)=15,A2,LEFT(REPLACE(A2,7,2,),15))

EXCEL中如何提取身份证出生日期和性别信息以及检验身份证号码的正确性

中国居民身份证号码是一组特征组合码，原为１５位，现升级为１８位，其编码规则为：

１５位：６位数字常住户口所在县市的行政区划代码，６位数字出生日期代码，３位数字顺序码。

１８位：６位数字常住户口所在县市的行政区划代码，８位数字出生日期代码，３位数字顺序码和１位检验码。

其中３位数字顺序码，是为同一地址码的同年同月同日出生人员编制的顺序号，偶数的为女性，奇数的为男性。

１、提取籍贯地区的行政区划代码（A2为身份证号，下同）

１５与１８位通用：=LEFT(A2,6)

如果有一个编码和省份地区的对照表，可以用VLOOKUP函数来提取地区信息。

２、提取出生日期信息

１５与１８位通用：．．．．．．．．．=--TEXT(IF(LEN(A2)=15,19,”“)&MID(A2,7,6+IF(LEN(A2)=18,2,0)),”#-00-00“．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．)．简化公式： =--TEXT((LEN(A2)=15)*19&MID(A2,7,6+(LEN(A2)=18)*2),”#-00-00“)．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．(请将输入公式的单元格格式设置为日期格式）．．．．．．．．．．．．．．．．．．．．．

３、提取性别信息

１５位：=IF(MOD(RIGHT(A2),2)=1,”男“,”女“)

１８位：=IF(MOD(MID(A2),17,1)=1,”男“,”女“)

１５与１８位通用：=IF(MOD(MID(A2,IF(LEN(A2)=15,15,17),1),2)=1,”男“,”女“)

简化公式：=IF(MOD(RIGHT(LEFT(A2,17)),2),”男“,”女“)

４、检验身份证号码的正确性

１８位身份证号码的最后一位是检验码，它是根据身份证前17位数字依照规则计算出来的，其值０～９或X。一般情况只要有一位数字输入错误，依照规则计算后就会与第18位数不符。当然不排除按错误号码计算后恰好与检验码相符的情况，但这种情况出现的可能性较低。因此，对１８位号码的验证采用如下公式：

=MID(”10X98765432“,MOD(SUMPRODUCT(MID(A2,ROW(INDIRECT(”1:17“)),1)*2^(18-ROW(INDIRECT(”1:17“)))),11)+1,1)=RIGHT(A2,1)

对于１５位身份证，由于没有检验码，我们只能简单地去判断出生日期代码是否是一个有效的日期，避免输入一些像“731302”或“980230“等这样不存在的日期。

=ISNUMBER(--TEXT(19&MID(A2,7,6),”#-00-00“))

综合１５位和１８位后的通用公式为：

=IF(LEN(A2)=18,MID(”10X98765432“,MOD(SUMPRODUCT(MID(A2,ROW(INDIRECT(”．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．1:17“)),1)*2^(18-ROW(INDIRECT(”1:17“)))),11)+1,1)=RIGHT(A2),IF(LEN(A2．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．)=15,ISNUMBER(--TEXT(19&MID(A2,7,6),”#-00-00“))))．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．

由于目前１５位身份证号码已经很少了，如果对１５位的号码不需要作进一步的判断，则公式可以简化成：

=IF(LEN(E3)=18,MID(”10X98765432“,MOD(SUMPRODUCT(MID(E3,ROW(INDIRECT(”1:17“)),1)*2^(18-ROW(INDIRECT(”1:17“)))),11)+1,1)=RIGHT(E3),LEN(E3)=15)

将上面的公式放到B2单元格，如果结果为TRUE，则身份证号是正确的，结果为FALSE则是错误的。

你也可以将上述公式放在数据有效性中，防止录入错误的身份证号。操作方法:选择需要输入身份证的全部单元格区域,比如A2:A10,点菜单”数据“-”有效性“,在”允许“的下拉框中选择”自定义“,在”公式“输入上面的15位和18位通用公式,确定以后即可。注意:公式里的”A2“是你刚才选定要输入身份证的单元格区域的第一个单元格,如果你是要在C3:C20输入身份证号,则将公式里的”A2“改为

”C3“。另外,你也可以先设置好某单个单元格的数据有效性(这时公式的A2改为选定的单元格),再用格式刷将其格式刷到其他需要相同设置的单元格。

５、１５位升为１８位 ．．．．．．．．．．

=IF(LEN(A2)=15,REPLACE(A2,7，19)&MID(”10X98765432“,MOD(SUMPRODUCT(MID．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．(REPLACE(A2,7，19),ROW(INDIRECT(”1:17“)),1)*2^(18-ROW(INDIRECT(”1:17“．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．)))),11)+1,1),A2)．．．．．．．．．．．．．．．．．

６、１８位转换为１５位

=IF(LEN(A2)=18,LEFT(REPLACE(A2,7,2,),15),A2)

7、示例

表中公式:

B2 =IF(LEN(A2)=18,MID(”10X98765432“,MOD(SUMPRODUCT(MID(A2,ROW(INDIRECT(”1:17“)),1)*2^(18-ROW(INDIRECT(”1:17“)))),11)+1,1)=RIGHT(A2),IF(LEN(A2)=15,ISNUMBER(--TEXT(19&MID(A2,7,6),”#-00-00“))))

C2 =IF(A2<>”“,TEXT((LEN(A2)=15)*19&MID(A2,7,6+(LEN(A2)=18)*2),”#-00-00“)+0,)

D2 =IF(A2<>”“,IF(MOD(RIGHT(LEFT(A2,17)),2),”男“,”女“),)

E2 =IF(A2<>”“,DATEDIF(TEXT((LEN(A2)=15)*19&MID(A2,7,6+(LEN(A2)=18)*2),”#-00-00“),TODAY(),”y“),)

F2 =IF(A2<>”“,VLOOKUP(LEFT(A2,2),地区表!A:D,2,),)

H2

=IF(LEN(A2)=15,REPLACE(A2,7，19)&MID(”10X98765432“,MOD(SUMPRODUCT(MID(REPLACE(A2,7，19),ROW(INDIRECT(”1:17“)),1)*2^(18-ROW(INDIRECT(”1:17“)))),11)+1,1),A2)

I2 =IF(LEN(A2)=18,LEFT(REPLACE(A2,7,2,),15),A2)

身份证有15位和18位两种，身份证位数是否正确，我们可以用LEN函数判断。但身份证上的日期是否合法：月份是否在1-12之间，日期是否在1-31之间，并且2月份只有28或29天，其他月份30或31天，都不能超过范围。另外一般规定6岁以上才可以办理身份证，也就是年份也有一个超范围的可能性。综合起来看，有三类错误：“身份证位数不对”、“月日错误”、“年份错误”。假定身份证号码在B1单元格，下面的公式可以综合判断以上三种错误： =IF(OR(LEN(B1)=18,LEN(B1)=15),IF(LEN(B1)=18,IF(OR(MONTH(DATE(1*(MID(B1,7,4)),1*(MID(B1,11,2)),1*(MID(B1,13,2))))<>1*(MID(B1,11,2)),DAY(DATE(1*(MID(B1,7,4)),1*(MID(B1,11,2)),1*(MID(B1,13,2))))<>1*(MID(B1,13,2))),”月日错误“,”“),IF(LEN(B1)=15,IF(OR(MONTH(DATE(1*(MID(B1,7,2))+1900,1*(MID(B1,9,2)),1*(MID(B1,11,2))))<>1*(MID(B1,9,2)),DAY(DATE(1*(MID(B1,7,2))+1900,1*(MID(B1,9,2)),1*(MID(B1,11,2))))<>1*(MID(B1,11,2))),”月日错误“,”“))),”身份证位数不对“)&IF(AND(LEN(B1)=18,1*MID(B1,7,4)>YEAR(TODAY())-6),”年份错误“,”“)

Excel中输入身份证号码及提取信息

一、解决输入身份证号码的问题

默认情况下在Excel当前单元格中输入的数字位数如果超过11位（不含11位）时，系统将以“科学记数”格式显示输入的数字；当输入的数字位数超过15位（不含15位）时，系统将15位以后的数字全部显示为“0”。这样一来，如果我们要输入身份证号码（15位或18位），身份证号码就不能正确显示出来了。但我们有两种办法解决此问题：

1、利用数字标签

选中需要输入身份证号码的单元格（或区域），打开“单元格格式”对话框，进入“数字”标签，在“分类”下面选中“文本”选项，确定退出即可。

2、在输入身份证号码时，在号码前面加上一个英文单引号“'”，即可让身份证号码完全显示出来（该单引号不显示）。

二、让单元格内自动添加身份证前面相同部分

身份证号码的前6位，是省、市、区（县）的代号（如资中县：511025），如果持证人都是资中县的，输入这样的的身份证号码，可以让单元格格式的“数字”标签帮你减轻输入的工作量：选中需要输入身份证号码的单元格区域，单击Excel上面菜单栏的格式打开“单元格”对话框（或者右键点击设置单元格格式），进入默认的“数字”标签，在“分类”下面选中最下面的“自定义”选项，然后在右侧“类型”下面的方框中输入：”511025“@，确定。以后输入身份证号码9位及以后的数字（如“197303246211”）后，系统会自动在前面加上51102519，形成完整的身份证号码（如“***211”）。

其它需要输入数字含相同部分的也可按照这个方法输入。

Excel从身份证号码中提取个人信息。

如18位身份证号码：前6位是公民户口登记地，第7到14位代表出生日期，第17位代表性别，奇数为男，偶数为女。(15位身份证号码15位数代表性别)

比如，某员工的身份证号码(18位)是***241，那么表示1972年8月17日出生，性别为女。如果能想办法从这些身份证号码中将上述个人信息提取出来，不仅快速简便，而且不容易出错。

提取个人信息 需要使用IF、LEN、MOD、MID、DATE等函数。某表格，其中员工的身份证号码信息已输入到C列，出生年月信息填写在D列，性别信息填写在B列。

1.提取出生年月信息

由于上交报表时只需要填写出生年月，不需要填写出生日期，因此这里我们只需要关心身份证号码的相应部位即可，即显示为“7208”这样的信息。在D2单元格中输入公式“=IF(LEN(C2)=15,MID(C2,7,4),MID(C2,9,4))”，其中：

LEN(C2)=15：检查C2单元格中字符串的字符数目是否等于15，意即检查身份证号码的长度是否是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位开始的四位数字，否则就从第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所示，现在这份报表无论是提取信息或是核对，都方便多了！

第五篇：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自文字的指定起始位置起提取指定长度的字符串