标签：身份证

用Excel函数公式升位15位、校验18位居民身份证号码

我在培训班讲课的内容，在这里再简单的说一下：

根据国标，18位身份证号码的最后一位是校验码，这位校验码是由前面17位数字经过数学运算得出来的，通过这位校验码，可以检测出18位数字是否存在录入错误，具体的算法如下：

1. 17位数字自身加权求和，公式是S=SUM(Ai*Wi), (0<i<18)这里的i也就是第1~17位的索引，比如A1就是第1个数字，依次类推。Wi是加权因子，对应的也是17位，数值是{7,9,10,5,8,4,2,1,6,3,7,9,10,5,8,4,2}，也就是说身份证号码前17位依次与这里17位相乘，最后求和，求和的结果为S。

2. 求余（求模），将S与11求模。Y=MOD(S,11)，得到Y。

3. 通过Y的值查找表{1,0,X,9,8,7,6,5,4,3,2}，最后得到校验位，为什么会有X，X是罗马数字10，因为10是两位，所以只好用X代替，但是不要认为带有X的身份证号码是错误的哦，还有不要将身份证号码字段设计成数字的哦。
好，有了上面的算法，简单的来说Excel函数也就知道怎么写了：

继续阅读“用Excel函数公式升位15位、校验18位居民身份证号码”

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

大家可能在单位经常要处理一些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()替换成时间字符串，从而写死时间。