用Excel函数提取公民身份证号码中信息(出生日期、性别)

!本文可能 超过1年没有更新,今后内容也许不会被维护或者支持,部分内容可能具有时效性,涉及技术细节或者软件使用方面,本人不保证相应的兼容和可操作性。

大家可能在单位经常要处理一些Excel表格,其中可能接触到身份证号码的处理,比如提取出出生年月日,判断性别和计算年龄等等,对于这些我们都可以通过Excel函数进行处理。

根据身份证号码标准GB11643-1989以及最新的标准GB11643-1999,得知我国公民身份证号码的格式如下:

15位号码:区域地址编码(6位)+出生年(2位)+出生月(2位)+出生日(2位)+数字顺序码(2位)+性别码(1位)
示例:110111 00 01 01 112 (1900年1月1日出生)

18位号码:区域地址编码(6位)+出生年(4位)+出生月(2位)+出生日(2位)+数字顺序码(2位)+性别码(1位)+校验码(1位)
示例:110111 1900 01 01 112 4 (1900年1月1日出生)

好了有了这些信息,下面主要是如何取出年月日,首先介绍一下Excel函数中的MID函数,学过Visual Basic的都应该对这个函数比较眼熟,是的,这个函数主要是用于截取字符串的指定位和指定长度的子字符串,比如aabbccdde这个字符串中bcc就是从第4位开始长度为3的子字符串。

MID函数的原型如下:
MID(text, start_num, num_chars)

其中text是要截取的文本,可以是表格单元格位置,start_num是开始的位数从1开始,num_chars是截取的字符长度(字符个数)。好吧,我们要截取15位身份证号码的年,那么start_num应该是7,num_chars应该是2,同样的截取月份start_num应该是9,num_chars应该是2,依次类推。

假设15位身份证号码存放于A1单元格,那么函数可以如下写法:

1
2
3
4
5
6
7
8
9
=MID(A1, 7, 2)        ' 获取2位的年
' 获取2位的年并与19拼接成4位的年(常用函数)
="19" & MID(A1, 7, 2)
=MID(A1, 9, 2)        ' 获取月份
' 取整月份,这样形如01、02等等月份就变成1、2去除了前导0
=INT(MID(A1, 9, 2))
=MID(A1, 11, 2)       ' 获取日份
' 取整日份,这样形如01、02等等日份就变成1、2去除了前导0
=INT(MID(A1, 11, 2))

这里注意一下第二个函数,其中15位身份证号码截取的是2位年份,为了便于阅读,我们需要将其与字符串19进行拼接,拼接的办法就是使用&字符串连接符。另外INT函数也要注意,就是我们可能不需要前导0的月和日,这时取整有助于去掉这些前置0。

同样的道理截取18位身份证号码,函数可以这样写:

1
2
3
4
5
6
7
=MID(A1, 7, 4)        ' 获取4位的年
=MID(A1, 11, 2)       ' 获取月份
' 取整月份,这样形如01、02等等月份就变成1、2去除了前导0
=INT(MID(A1, 11, 2))
=MID(A1, 13, 2)       ' 获取日份
' 取整日份,这样形如01、02等等日份就变成1、2去除了前导0
=INT(MID(A1, 13, 2))

好了,我们已经分别取出了年月和日,可能读者要问了,如何取出类似于2011.03.12或者2011-03-12这种格式的日期呢,其实很容易,只要将上面的函数与“.”或者“-”进行拼接就可以了,我们以18位身份证号码为例,具体如下:

1
2
3
4
5
6
' 形如2011.03.12这样的日期
=MID(A1, 7, 4) & "." & MID(A1, 11, 2) & "." & MID(A1, 13, 2)
' 形如2011-03-12这样的日期
=MID(A1, 7, 4) & "-" & MID(A1, 11, 2) & "-" & MID(A1, 13, 2)
' 形如2011年03月12日这样的日期
=MID(A1, 7, 4) & "年" & MID(A1, 11, 2) & "月" & MID(A1, 13, 2) & "日"

谈到提取出生日期,很多朋友可能会想到如何计算年龄,这里还是先介绍下Excel的两个函数NOW()和YEAR()。其中NOW()始终代表着当前的时间。而YEAR则是在合法时间量中提取出年份。那么聪明的读者应该想到通过YEAR(NOW())我们就能获取当前的年份,然后通过与身份证号码上截取的4位年份进行相减就可以获得年龄了,好,还是以18位身份证号码为例,下面我们来看公式:

=YEAR(NOW())-MID(A1, 7, 4)

很简单吧,但是有读者可能需要一种更复杂的计算机制来计算年龄,比如要精确到月,出生月小于当前月的就以当前年份相减,否则就按当前年份减去1,然后再相减。这里我们需要用到IF函数,具体格式是 IF(条件, 为真, 为假),另外刚刚我们了解了取年份函数YEAR(),现在再介绍个取月份函数MONTH(),最后具体函数如下(18位身份证号码):

=IF(INT(MONTH(NOW()))>=INT(MID(A1, 11, 2)), YEAR(NOW()), YEAR(NOW())-1)-MID(A1, 7, 4)

也可以写成

=(YEAR(NOW())-MID(A1, 7, 4))-IF(INT(MONTH(NOW()))>=INT(MID(A1, 11, 2)), 0, 1)

好的,关于出生日期及年龄计算就先介绍到这里,下面我将给大家介绍如何判断性别。

根据先前讲的公民身份证号码格式,我们知道对于15位身份证号码,性别是看最后一位,对于18位身份证号码性别看倒数第二位,如果该位为奇数就为男性,如果为偶数就为女性,我们可以通过MID函数提取出该位,但是如何判断奇偶呢,其实这里用到了Excel的另一个函数MOD进行求余数,我们可以通过将该位与2进行求余,为0就为女性,为1就为男性,以18位身份证为例:

=MOD(MID(A1, 17, 1), 2)

如果我们需要将显示文本变得更友好,可以通过IF函数进行判断,那么原来的公式更改如下:

=IF(MOD(MID(A1, 17, 1), 2)=1, “男”, “女”)

好的,我关于用Excel公式函数提取公民身份证号码中信息的介绍到这里就结束了,想必大家在实际操作中能发掘出更好的方法吧。

2011年8月16日更新

部分读者反映,取年龄会计算不正确,实际上计算年龄的一个重点就是NOW()函数,NOW()函数返回的是当前计算机的时间,如果NOW()函数返回的时间不正确,计算结果当然也就不正确了,只有一种情况导致NOW()函数返回错误的时间,那就是你电脑上的时间设置错了,当然你也可以将NOW()替换成时间字符串,从而写死时间。

若无特别说明,本网站文章均为原创,原则上这些文章不允许转载,但是如果阁下是出于研究学习目的可以转载到阁下的个人博客或者主页,转载遵循创作共同性“署名-非商业性使用-相同方式共享”原则,请转载时注明作者出处谢绝商业性、非署名、采集站、垃圾站或者纯粹为了流量的转载。谢谢合作!
请稍后...

发表评论

电子邮件地址不会被公开。 必填项已用*标注