极板电容公式EXCEL中如何能从身份证号码提取出生年月日及性别

2020年10月6日发(作者：叶子铭)
实用标准文档
EXCEL中如何从提取出生年月日及性别
(2009-09-24 11:49:19)
▼
标签： 分类： 电脑
技巧
电子表格
函数
it
近段时间，各校都忙坏了，学生城镇居民医疗保险材料
要保，今年必须提供每位学生的，还有性别、出生日期等信息；
新生学籍要报，学籍信息包含50多项， 其中也有、性别、出生
日期等信息……。为了简化工作，提高效率，特整理出从里提取
出生年月 日及性别等信息的方法，希望对大家有所帮助。

一、分析

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

15位：第7、8位为出生年份(两位数)，第9、1 0位为
出生月份，第11、12位代表出生日期，第15位代表性别，奇数
为男，偶数为女。< br>
文案大全
实用标准文档
18位：第7、8、9、10位为出生年 份(四位数)，第11、
第12位为出生月份，第13、14位代表出生日期，第17位代表
性 别，奇数为男，偶数为女。

例如，某学生的(15位)是320242，那么表示1989年8月7日出生，性别为女。如果能想办法从这些中将
上述个人信息提取出来，不仅快速 简便，而且不容易出错，核对
时也只需要对进行检查，肯定可以大大提高工作效率。

二、转换格式

我们先将学生的号完整地输入到EXCEL表格中，这时默
认 为“数字”格式（单元格显示的是科学记数法的格式），需要
更改一下数字格式。选中该列中的所有号后 ，右击鼠标，选择“设
置单元格格式”。在弹出对话框中“数字”标签的“分类”设为
“文本” ，然后点击确定。

三、提取个人信息

这里，我们需要使 用IF、LEN、MOD、MID、DATE等函数
从中提取个人信息。假设学生的信息已输入完毕(C 列)，出生年
月信息填写在D列，性别信息填写在B列。

1. 提取出生年月信息

文案大全
实用标准文档
如果上交报表时需要 填写出生年月日，我们需要关心的
相应部位即可，即显示为“19890807”这样的信息。在D2单 元
格中输入公式

=IF(LEN(C2)=15,，其
中：




文本函数
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单元格中字符串的字符数目，本例< br>的含义是检查的长度是否是15位。

MID(C2,7,4)：从C2单元格 中字符串的第7位开始提取
6位数字，本例中表示提取15位的第7、8、9、10、11、12位数字。

表示如果是15位，在年份前加“19”。

MID(C2,7,8)：从C2单元格中字符串的第7位开始提取
8位数字，本例中表示提取18位的 第7、8、9、10、11、12、13、
14位数字。

IF(LEN(C 2)=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,
&
(C2,13,2))。

文案大全
实用标准文档
解释：如果是15位，则从第7位开始取两位数字 ，并在
这两位数字前加“19”，后面加“-”,从第9位开始取两位数字，
并在这两位数字后 面加“-”,从第11位开始取两位数字。否则，
从第7位开始取四位数字，并在这四位数字后面加“- ”,从第
11位开始取两位数字，并在这两位数字后面加“-”,从第13位
开始取两位数字。

也可以使用DATE格式，并在“单元格格式→日期”中进
行设置。

如果学生的号全部都是18位的新一代，可以以把上面的
公式进行简化：

=MID(C2,7,4)&

2. 提取性别信息

由于 报表中学生不是按照男、女固定的顺序进行编排，
如果一个一个手工输入的话，既麻烦又容易出错。
例如性别信息统一在B列填写，可以在B2单元格中输入
公式：

=IF(MOD(IF
(LEN(C2)=15,MID(C2,15,1),MI D(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,1 5,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,男女
男女

文案大全
实用标准文档
如果学生的号全部都是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 所提取的字符数。


文案大全