使用Excel函数从公民ID号(出生日期,性别)中提取信息

电脑杂谈  发布时间:2020-03-26 12:11:12  来源:网络整理

每个人都可能经常在单元中处理某些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://xinshanjie.com/a/tongxinshuyu/article-152937-1.html

    相关阅读
    发表评论  请自觉遵守互联网相关的政策法规,严禁发布、暴力、反动的言论

    热点图片
    拼命载入中...