使用Excel函数从公民ID号（出生日期，性别）中提取信息

每个人都可能经常在单元中处理某些Excel表格，这些表格可能要经过ID号的处理，例如提取出生日期，判断性别和计算年龄等. 为此，我们可以通过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（文本，s​​tart_num，num_chars）

其中text是要截断的文本，可以是表格单元格的位置，start_num是从1开始的位数，num_chars是截断的字符的长度（字符数）. 好吧，我们要截取15位ID号的年份，那么start_num应该为7，num_chars应该为2，相同的截获月份应该为9，start_num应该为2，num_chars应该为2，依此类推. / p>

假定15位ID号存储在单元格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))

= MID（A1，7，2）'获取2位数字年份'获取2位数字的年份并用19到4位数字的年份进行缝合（通用功能）=“ 19”＆MID（A1，7，2 ）= MID（A1，9，2）'Get month'取整月份，以便01、02等的月份变为1、2，且不带前导0 = INT（MID（A1，9，2））= MID （A1，11，2）'Get the day'整天运行，以使像01、02等的日期变为1、2，且不带前导0 = INT（MID（A1、11、2））

请注意此处的第二个功能，其中15位ID号被年份的2位数字截获. 为了便于阅读，我们需要将其与字符串19连接起来. 连接方法是使用＆字符串连接. 此外，INT函数还应注意，我们可能不需要前导零的月份和日期. 四舍五入将有助于消除这些前导零.

相同的原因拦截了18位ID号. 该函数可以这样写:

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))

= MID（A1、7、4）'获取4位数的年份= MID（A1、11、2）'获取月份'取整个月，这样01、02等月份变为1、2删除了前导0 = INT（MID（A1excel函数号码，11，2））= MID（A1、13、2）'Get the day'整天处理，使01、02等日期变为1， 2删除前导0 = INT（MID（A1，13，2））

好的，我们分别计算了年，月和日. 也许读者不得不问，如何取出一个类似于2011.03.12或2011-03-12的日期. 实际上，这很容易. 该功能可以用“. ”进行拼接. 或“-”，我们以18位ID号为例，如下:

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) & "日"

“日期如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年3月，例如第12个日期= MID（A1，7，4）＆“年”＆MID（A1，11，2）＆“ month”＆MID（A1，13，2 ）和“日期”

在提取出生日期时，许多朋友可能会想到如何计算年龄. 在这里，我们将首先介绍Excel的两个函数NOW（）和YEAR（）. NOW（）始终代表当前时间. YEAR从合法时间中提取年份. 因此，精明的读者应该认为，通过YEAR（NOW（）），我们可以获得当前年份，然后从ID号中减去年份的4位数字即可得出年龄. 好吧，它仍然是18位数字. 例如，让我们看一下公式:

=年（现在（））-中（A1、7、4）

这很简单，但是有些读者可能需要更复杂的计算机系统来计算年龄. 例如，要精确到该月份，如果出生月份小于当前月份，则将减去当前年份，否则，将从当前年份中减去1. 然后减去它们. 这里我们需要使用IF函数，具体格式为IF（条件，true，false）. 我们也刚刚了解了年份函数YEAR（）. 现在，我们将介绍月份函数MONTH（）. 最终功能如下（18位ID号）:

= IF（INT（MONTH（NOW（NOW（）））> = INT（MID（A1，11，2）），YEAR（NOW（）），YEAR（NOW（））-1）-MID（A1， 7、4）

它也可以写为

=（年份（NOW（））-MID（A1，7，4））-IF（INT（MONTH（NOW（）））> = INT（MID（A1，11，2）），0，1 ）

好的，我先在这里介绍出生日期和年龄计算. 我将向您展示如何判断自己的性别.

根据以前的公民ID编号格式，我们知道对于15位ID号，性别是最后一位，对于18位ID号，性别是最后两位. 如果数字是奇数，则为男性. 如果偶数是女性，则可以通过MID函数提取该位，但是如何确定奇偶校验. 实际上，这里使用另一个Excel函数MOD来查找余数. 我们可以通过将位加2来执行余数. 如果为0，则为女性，如果为1，则为男性. 以18张为例:

= MOD（MID（A1，17，1），2）

如果我们需要使显示的文本更加友好并且可以通过IF函数进行判断，则原始公式将更改如下:

= IF（MOD（MID（A1excel函数号码，17，1），2）= 1，“男”，“女”）

好的，我在这里介绍了如何使用Excel公式函数提取公民ID号中的信息. 据推测，每个人都可以在实际操作中找到更好的方法.

2011年8月16日更新

一些读者报告说，年龄计算不正确. 实际上，计算年龄的一个重要点是NOW（）函数. NOW（）函数返回当前计算机时间. 如果NOW（）函数返回的时间不正确，则计算结果当然是错误的. 只有一种情况会导致NOW（）函数返回错误的时间，即计算机上的时间设置不正确. 当然，您也可以将NOW（）替换为时间字符串以写入Dead time.

本文来自电脑杂谈，转载请注明本文网址：
http://www.pc-fly.com/a/tongxinshuyu/article-152937-1.html