求等差数列末项公式Excel表格中根据身份证号码自动填出生日期、计算年龄

2020年10月9日发(作者：殷润霖)

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,
女男
1518位都可以的公式：转换出生日期：
=IF(L EN(e2)=18,TEXT(MID(e2,7,8),
0-00
自动录入男女：=IF( E2=女男
计算年龄（新旧身份证号都可以）：
=IF(AND(E2=
,2)))

WPS表格提取身份证详细信息
前些天领导要求统计所有员工的性别、出生日期 、年龄等信息，并且要得
很急。而我们单位员工人数众多，短时间内统计相关信息并且输入计算机几乎< br>是不太可能的。幸好在以前的一份金山表格中我们曾经统计有所有员工的身份
证号码，而身份证中 正有我们所需要的性别、出生日期、年龄等信息的。所以，
干脆，还是直接在金山表格中从身份证号码提 取相关的信息吧。
身份证号放在A2单元格以下的区域。我们需要从身份证号码中提取性
别、出生日期、年龄等相关信息。由于现在使用的身份证有15位和18位两种。
所以，在提取相关信息 时，首先应该判断身份证号码的数字个数，然后再区别
不同情况进行相关处理。
一、身份证号的位数判断
在B2单元格输入如下公式“=LEN($$A2)”，回车后即可得到A 2单元格
身份证号码的数字位数，如图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单元格字符串的第1 7位开始截取1个字符，否
则从A2单元格字符串的右侧截取1个字符”。
第二步：根据 识别数字判断男女性别，在D2单元格输入公式
“=IF(ISEVEN($$C2)=TRUE,女男， 回车后就可以得到的结果了。如图3所
示。




图3 WPS中键入函数判断男、女性别
此处函数ISEVEN($$C2)的作用是判断 C2单元格的数字是否为偶数，
如果是，则返回结果TRUE，否则返回结果FALSE。因此，公式< br>“=IF(ISEVEN($$C2)=TRUE,女男的含义就是如果C2单元格数字为偶数，那
么在当前单元格显示“女”，否则显示
三、出生日期提取
第一步：提取数字型出生日 期。在E2单元格输入公式
“=IF($$B2=18,MID($$A2,7,8),并回车，如图4所示 。这种形
式的出生日期我们并不常用，此处提取出来只是为了我们下一步计算年龄时要
用，也是 一个辅助列。的意思是把字符“19”和MID($$A2,7,6)
截取到的6个字符合并在一起成为一 个新的字符串。其它的，就不用再解释了
吧？


图4 WPS中键入函数判断出生日期
第二步：提取带连接符的出生日期。在F2单元格输入公式“=IF($$B2=18,MID($$A2,7,4)&-
$$A2,7,2)& 并回车，如图5所示，可以得
到我们常用的如 “1977-08-11”的格式。此处用到的函数在前面已经有过解释，所
以不再罗嗦了。




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


图6 WPS中用公式计算年龄
最后，选中B2:G2单元格，向下拖动填充句柄至最后一行，那么我们
所需要的所有数据 就有了，再选中B列、C列、E列，并点击右键，在弹出的右
键菜单中点击“隐藏”命令，将这些辅助列 隐藏起来，就可以得到一份完整的表格
了。
很简单吧？利用函数功能，只需要设置一下相 应的参数，把一个个隐藏
在身份证号码里的信息提取出来，完成上面所有操作用时也不到半小时，将所得
到的表格美化一下，就可以向领导交差了。

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

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



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

例如，某员工的身份证号码(15位)是324，那 么表示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位数字。

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



表中用身份证号码中取其中的号码用:MID(文本,开始字符,所取字符数);
2.15位身份证号从第7位到第12位是出生年月日，年份用的是2位数。
18位身份证号从第7位到第14位是出生的年月日，年份用的是4位数。
从身份证号码中提 取出表示出生年、月、日的数字，用文本函数MID()可以达到目的。MID
()——从指定位置开始 提取指定个数的字符（从左向右）。
对一个身份证号码是15位或是18位进行判断，用逻辑判断函数 IF()和字符个数计算函数L
EN()辅助使用可以完成。综合上述分析，可以通过下述操作，完成形 如1978-12-24样式的
出生年月日自动提取：
假如身份证号数据在A1单元格，在B1单元格中编辑公式
=IF(LEN(A1)=15, MID(A1,7,2)&
D(A1,11,2)&
回车确认即可。
如果只要“年-月”格式，公式可以修改为
=IF(LEN(A1)=15,MID(A1, 7,2)&
3.这是根据身份证号码（15位和18位通用）自动提取性别的自编公式，供需要的朋友参 考：
说明：公式中的B2是身份证号
根据身份证号码求性别：
=IF(LEN( B2)=15,IF(MOD(VALUE(RIGHT(B2,3)),2)=0,女男
(VALUE (MID(B2,15,1)),2)=0,女男身份证错


根据身份证号码求年龄：
=IF(LEN(B2)=15,2007-VALUE(MID( B2,7,2)),if(LEN(B2)=18,2007-VALUE(MID(B2,7,4)),
身份证错
表中用YearMonthDay函数取相应的年月日数据;
另一方法：

这是根据身份证号码（15位和18位通用）自动提取性别和出生年月的自编公式，供需要的
网友参考：
说明：公式中的B2是身份证号
1、根据身份证号码求性别：
=IF(LEN(B2)=15,IF(MOD(VALUE(RIGHT(B2,3)),2)=0,女男< br>(VALUE(MID(B2,15,3)),2)=0,女男身份证错
2、根据身份证号码求出 生年月：
=IF(LEN(B2)=15,CONCATENATE(
CATENATE(M ID(B2,7,4),身份证错
3、根据身份证号码求年龄：
=IF(LEN(B2)=1 5,year(now())-1900-VALUE(MID(B2,7,2)),if(LEN(B2)=1 8,year(now())-VAL
UE(MID(B2,7,4)),身份证错

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

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

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

2、根据身份证号码求出生年月日：
=IF(LEN(B2)=15,C ONCATENATE(
B2)=18,CONCATENATE(MID(B2,7,4),身份证错

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


其他通用公式：（新旧身份证号通用）

（1）、根据“身份证号”获得“年龄”
A1为身份证号，
B1中输入公式


=DATEDIF(TE XT((LEN(A1)=15)*19&MID(A1,7,6+(LEN(A1)=18)*2),
,TODAY(),

（2）、根据出生“年-月-日”获得“年龄”
B1为出生日期，
C1中输入公式
=INT((TODAY()-B1)365)
或者
=DATEDIF(B1,TODAY(),

判断身份证格式是否正确：
B1为身份证号，
C1中输入公式
=IF( OR(LEN(B1)=18,LEN(B1)=15),IF(LEN(B1)=18,IF(OR(MONT H(DATE(1*(
MID(B1,7,4)),1*(MID(B1,11,2)),1*(MID (B1,13,2))))<>1*(MID(B1,11,2)),DAY(D
ATE(1*(MID (B1,7,4)),1*(MID(B1,11,2)),1*(MID(B1,13,2))))<>1*( MID(B1,13,2))
),月日错误

9,2)),1*(MID(B1,11, 2))))<>1*(MID(B1,9,2)),DAY(DATE(1*(MID(B1,7,2))+19 00,
1*(MID(B1,9,2)),1*(MID(B1,11,2))))<>1*(MID( B1,11,2))),月日错误身份
证位数不对年份
错误




以下是一位热心网友提供的通用公式：（很不错哦^_^）

假设C2为身份证号，输入公式

=IF(LEN(C2)=15,19&MID(C2,7,6),MID(C2,7,8))
这是获得出生日期

=IF(MOD(IF(LEN(C2)=15,MID(C2 ,15,1),MID(C2,17,1)),2)=1,男女
这是区分性别