《转excel表格中根据身份证号码自动填出生日期》由会员分享，可在线阅读，更多相关《转excel表格中根据身份证号码自动填出生日期（10页珍藏版）》请在金锄头文库上搜索。
1、EXCEL 中如何从身份证号码求出生年月日及年龄公式，性别信息方法？注：今天帮助一位网友处理一些表格的问题，也使自己重新又温习了一下电子表格的相关注：今天帮助一位网友处理一些表格的问题，也使自己重新又温习了一下电子表格的相关内容，在解决这个问题的过程中，借鉴了一位网友的文章，感觉里面的信息非常全面，可内容，在解决这个问题的过程中，借鉴了一位网友的文章，感觉里面的信息非常全面，可能许多朋友都有可能遇到，特转载到自己的博客当中。也对这位网友表示感谢。能许多朋友都有可能遇到，特转载到自己的博客当中。也对这位网友表示感谢。excelexcel 中如何根据身份证号，自动求出出生年月？中如何根据身份证号，
2、自动求出出生年月？很好用！根据我的实际工作需要，又少作修改的方法：假如身份证号数据在 A1 单元格，在 B1 单元格中编辑公式=IF(LEN(A1)=15,“19“&MID(A1,7,2)&MID(A1,9,2)&MID(A1,11,2),MID(A1,7,4)&MID(A1,11,2)&MID(A1,13,2)这样输出格式就都是 19821010 这种格式了。excelexcel 中如何根据身份证号，自动求出性别？中如何根据身份证号，自动求出性别？假定身份证号在 A1 单元格，则在 B1 单元格中编辑公式=IF(AND(LEN(A1)=15,MOD(MID(A1,15,1)*1,2)=1),
3、“男“,IF(AND(LEN(A1)=18,MOD(MID(A1,17,1)*1,2)=1),“男“,“女“)在在 excelexcel 是根据身份证自动填充年龄？是根据身份证自动填充年龄？假设 A 列从 A1 开始是身份证号，在 B 列中进行转换，则在 B1 中输入=YEAR(TODAY()-IF(LEN(A1)=15,“19“&MID(A1,7,2),MID(A1,7,4)，然后再向下复制即可。=YEAR(TODAY()-IF(LEN(F2)=15,“19“&MID(F2,7,2),MID(F2,7,4)公式解析：公式解析：一、分析身份证号码其实，身份证号码与一个人的性别、出生年月、籍贯等
4、信息是紧密相连的，无论是 15 位还是 18 位的身份证号码，其中都保存了相关的个人信息。15 位身份证号码：第 7、8 位为出生年份(两位数)，第 9、10 位为出生月份，第 11、12 位代表出生日期，第 15 位代表性别，奇数为男，偶数为女。18 位身份证号码：第 7、8、9、10 位为出生年份(四位数)，第 11、第 12 位为出生月份，第 13、14 位代表出生日期，第 17 位代表性别，奇数为男，偶数为女。例如，某员工的身份证号码(15 位)是 320521720807024，那么表示 1972 年 8 月 7 日出生，性别为女。如果能想办法从这些身份证号码中将上述个人信息提取出来
5、，不仅快速简便，而且不容易出错，核对时也只需要对身份证号码进行检查，肯定可以大大提高工作效率。二、提取个人信息这里，我们需要使用 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
6、)=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 位开始
7、的四位数字。如果需要显示为“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 位，那么提取第
8、 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 单元格显示正确的性别信息，接下来就是选中填充柄直接拖曳
9、。转转 ExcelExcel 表格中根据身份证号码自动填出生日期、计表格中根据身份证号码自动填出生日期、计算年龄算年龄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“
10、&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)WPSWPS 表格提取身份证详细信息表格提取身份证详细信息前些天领导要求统计所有员工的性别、出生日期、年龄等信息，并且要得 很急。而我们单位员工人数众多，短时间内统计相关信息并且输入计算机几乎 是不太可能的。幸好在以前的一份金山表格中我们曾经统计有
11、所有员工的身份 证号码，而身份证中正有我们所需要的性别、出生日期、年龄等信息的。所以， 干脆，还是直接在金山表格中从身份证号码提取相关的信息吧。身份证号放在 A2 单元格以下的区域。我们需要从身份证号码中提取性 别、出生日期、年龄等相关信息。由于现在使用的身份证有 15 位和 18 位两种。 所以，在提取相关信息时，首先应该判断身份证号码的数字个数，然后再区别 不同情况进行相关处理。一、身份证号的位数判断在 B2 单元格输入如下公式“=LEN($A2)”，回车后即可得到 A2 单元格 身份证号码的数字位数，如图 1 所示。LEN($A2)公式的含义是求出 A2 单元格字 符串中字符的个数。由于
12、当初身份证输入时就是以文本形式输入的，所以用此 函数正可以很方便地求到身份证号码的位数。二、判断男女性别第一步：提取性别识别数字。如果是 18 位身份证，那么倒数第二位就 是性别判断的识别数字，奇数为男，偶数则为女。而如果是 15 位身份证，那么 倒数第一位则是性别识别数字，同样奇数为男，偶数为女。所以首先应该提取 这两种身份证的性别识别数字。在 C2 单元格输入公式 “=IF(B2=18,MID(A2,17,1),RIGHT(A2,1)”，回车后，立刻可以得到相应的识 别数字了。如图 2 所示。图 2 WPS 中键入函数 函数 MID(A2,17,1)的作用是从 A2 单元格字符串的第 17
13、 个字开始截取 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 所示
14、。图 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 所示。这 种形式的出生日期我们并不常用，此处提取出来只是为了我们下一步计算年龄 时要用，也是一个辅
15、助列。“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”的格式。此处用到的函数在前面
16、已经有过解释，所以不再罗嗦了。图 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 的函数功能，只需要设置一下相应的参数，把一 个个隐藏在身份证号码里的信息提取出来，完成上面所有操作用时也不到半小 时，将所得到的表格美化一下，就可以向领导交差了，呵呵。依宪治国是依法治国的核心 依法治国的基本要求是有法可依、有法必依、执法必严、违法必究。 依照宪法和法律来治理
