本帖最后由 祝洪忠- 于 2014-2-28 09:06 编辑
EH微信内容选编 微信号：iexcelhome
昨天给大家留了一个关于提取身份证信息的互动题目，要求根据模拟数据表中的身份证号码，分别提取性别、出生年月和退休日期。
下面一起学习一下如何提取这些信息。 在了解如何实现自动从身份证号码中提取出生年月、性别信息之前，首先需要了解身份证号码所代表的含义。
我们知道，以前的身份证号码有15/18位之分。早期签发的身份证号码是15位的，现在签发的身份证由于年份的扩展（由两位变为四位）和末尾加了效验码，就成了18位。
这两种身份证号码在过去相当长的一段时期内共存，现在已经停止了15位的身份证的使用。两种身份证号码的含义如下： （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位为效验位。
了解了上面的这些内容，接下来说说18位身份证号码的信息提取。 在B3单元格输入以下公式，提取性别： =IF(MOD(MID(C3,15,3),2),"男","女") 公式中的MID(C3,15,3)部分意思是，从C3单元格第15位开始提取，提取的字符长度为3位。计算结果是"224"。 MOD函数用于返回两数相除的余数，此例中的MOD函数第二参数使用2，就是用于判断MID函数的结果是否能被2整除，能够整除返回0，否则返回1。 IF函数根据MOD函数的计算结果，返回指定内容。如果MOD函数的计算结果为 1，IF 函数将返回“男”；如果MOD函数的计算结果为0，则返回“女”。 以上这个公式也同样适用于15位身份证号码中性别信息的提取。
出生日期的提取使用以下公式来完成： =--TEXT(MID(C3,7,8),"0-00-00") 公式中的MID(C3,7,8)部分，用于提取出C3单元格中身份证号码的第7-14位。计算结果为"19690815"，用TEXT函数将这个公式结果强制变成"1969-08-15"，前面加了两个减号用于减负运算，将文本"1969-08-15"变成真正的日期格式。
接下来看一下年龄的计算，E3单元格输入以下公式： =DATEDIF(D3,TODAY(),"y") DATEDIF函数是一个隐藏函数，没有出现在函数列表中，Excel中的公式自动完成功能也不会自动生成这个函数名称，甚至在多个版本的帮助文件中都找不到这个函数的踪影。 这个函数主要用于计算两日期相差年月日数，利用该函数可计算相差的天数、月数和年数。对于DATEDIF函数的使用方法，咱们可以这样理解： DATEDIF(起始日期,结束日期,指定的汇总方式) 第三参数为所需信息的返回时间单位代码。各代码对应的含义如下： | 第三参数代码 |
函数返回值
|
"y"
|
时间段中的整年数。
|
"m"
|
时间段中的整月数。
|
"d"
|
时间段中的天数。
|
"md"
|
起始日期与结束日期天数的差。忽略日期中的月和年。
|
"ym"
|
起始日期与结束日期月数的差。忽略日期中的日和年。
|
"yd"
|
起始日期与结束日期天数的差。忽略日期中的年。
|
在这个公式中，DATEDIF函数返回D3单元格的出生日期到当前日期（TODAY()）的整年数，也就是实际年龄。 如果将上面这条公式稍作改动，换成=DATEDIF(入司时间,TODAY(),"y")，那就是咱们常用到的工龄计算公式了。
在F3单元格用下面这个函数计算退休年龄： =EDATE(D3,660+(B3="男")*60) EDATE函数用于返回指定日期之前或之后的月数。在本例中，咱们利用了D3单元格已经计算出来的出生日期和B3单元格性别信息作为计算条件。用D3单元格的出生日期作为起始日期，指定的月数是660+(B3="男")*60这串字符初看起来可能有点费解，咱们分解一下： 660，也就是12个月/年*55年（女性退休年龄）；(B3="男")*60这里表示如果B3单元格性别是“男”，就再加上60个月（12个月/年*5年），否则就是660。 可以这样理解：如果B3单元格性别是“女”，按=EDATE(D3,660)计算退休日期，如果B3单元格性别是“男”，则按=EDATE(D3,660+60)计算退休日期。
 该贴已经同步到 祝洪忠-的微博 | 
[复制链接]
沪公网安备 31011702000001号 沪ICP备11019229号-2
发表于 2014-2-28 09:04
发表于 2014-2-28 09:13
发表于 2014-5-1 21:35
