返回目录:excel表格制作
职场中的精英们,相信大家在使用EXCEL的时候,您肯定曾经对处理身份证号信息头疼不已,本节以EXCEL2010为准,将从以下五个方面对身份证号在EXCEL中的处理方式进行详细讲解。
一、身份证号的输入
方式一:单元格输入身份证号前,将单元格格式设置为“文本”格式(Ctrl+1,可快捷调出单元格格式设置页面)后,然后再输入身份证号。
方式二:在单元格输入身份证号时候,首先输入英文的单引号“'”,然后再输入身份证号,神奇的事情发生了,引号消失不见,身份证号自动变为文本。
为什么输入要这样啰嗦呢,因为Excel单元格默认情况下为“常规”格式,该状态会自动按照自己的喜好自动将内容进行归类,它会将输入的身份证号视同为“数字”格式,所以当单元格长度不够的情况下,您输入一串身份证号后会变成科学计数法;即使当前长度足以显示身份证号,您将该单元格宽度变小,也会变成科学计数法,再加长,会发现份证号后几位会丢失变成0。
二、身份证号信息的输出
假设在A1单元格存储了身份证号:370983201705040067
(一)通过身份证号采集出生日期
mid函数语法:
mid(text,start_num,num_chars),中间某位置开始截取部分字符串;其中text表示要截取的字符串,start_num表示从第几位字符串开始截取,num_chars表示要截取的字符数。
1、如果显示格式为:20170504,那么我们应该在单元格中输入函数:=mid(A1,7,8),就可以得到20170504;不过此时显示的内容为文本状态,并不是日期格式,需要通过其他方法再转化为日期。
2、如果显示格式为:2017年05月04日,那么我们应该在单元格中输入函数:
=mid(A1,7,4)&"年"&mid(A1,11,2)&"月"&mid(A1,13,2)&"日"
不过此时显示仍然为文本状态,并不是日期格式,需要通过其他方法进行转化。
该函数的意思是:从A1单元格中的第7位开始选,往后数4位,返回年2017;从A1单元格中的第11位开始选,往后数2位,返回月05;从A1单元格中的第13位开始选,往后数2位,返回日04;同时通过连接字符串连接“年”“月”“日”,即得到2017年05月04日。
知识补充:
“&”(输入方式:shift键+大键盘上的7),相当于数学运算附中的+,“&”可以将两个单元格的内容,或者字符串拼接在一起,例如假设A1单元格为:张,A2单元格为:三,我们在A3中输入:=A1&A2,则A3中会显示:张三。
3、前面两种方式显示都为文本格式,假设有人想一步到位,直接将身份证中的日期采集成日期格式,如2017-5-4,那么我们应该在单元格中输入函数:
=DATE(MID(A1,7,4),MID(A1,11,2),MID(A1,13,2))
此时直接为日期格式(可以通过设置单元格中日期的格式,变为2017年5月4日或者2017/5/4等等)。
函数的意思是:从A1单元格中的第7位开始选,往后数4位,返回年2017;从A1单元格中的第11位开始选,往后数2位,返回月05;从A1单元格中的第13位开始选,往后数2位,返回日04;然后通过DATE函数将返回的字符串转化为日期格式。
(二)通过身份证号采集性别
mod函数语法:
mod(number,divisor), 用于返回两数相除的余数;其中number:表示被除数,divisor表示除数。如果该参数为 0,MOD 函数将返回错误值 #DIV/0!。
我们通过mod函数与mid函数组合,在单元格中输入函数:
=IF(MOD(MID(A1,17,1),2)=1,"男","女"),即可得到性别。
函数的意思是:首先从A1单元格中的第17位开始选,往后数1位,得到1个数字,然后用这个数字除以2,得到余数;假设余数为1,则显示“男”;假设余数不为1,则显示“女”。
知识补充:18位的身份证号的第17位如果是奇数,代表性别是男,如果是偶数,代表性别是女。
(三)通过身份证号采集年龄
-----------------------------
NOW函数语法:now(),用于返回当前日期。
YEAR函数语法:
YEAR(serial_number),用于返回日期中的年份;其中serial_number表示一个日期值。
DATEDIF函数语法:
DATEDIF(start_date,end_date,unit),两个日期之间做差额;其中:
Start_date 为一个日期,它代表时间段内的第一个日期或起始日期;
End_date 为一个日期,它代表时间段内的最后一个日期或结束日期;
Unit 为所需信息的返回类型。
Unit 返回
注:结束日期必须大于起始日期
"Y" 时间段中的整年数。
"M" 时间段中的整月数。
"D" 时间段中的天数。
"YM" 起始日期与结束日期的间隔月数,忽略日期中年份。
-----------------------------
1、YEAR、NOW和MID函数组合,计算年数差额即年龄,函数如下:
=YEAR(NOW())-MID(A1,7,4),显示为**年。
2、DATEDIF、NOW和MID函数组合,计算年数差额即年龄,函数如下
=DATEDIF(DATE(MID(A1,7,4),MID(A1,11,2),MID(A1,13,2)),NOW(),"y"),显示为**年。
函数的意思是:首先通过DATE函数将从身份证中采集的字符串转化为日期格式;通过NOW函数调取当前日期;然后通过DATEDIF函数将两个日期以“年”为单位做差额。
3、补充:
(1)月数差额:计算当前日期与身份证日期的月数差额,函数如下:
=DATEDIF(DATE(MID(A1,7,4),MID(A1,11,2),MID(A1,13,2)),NOW(),"m"),显示为**月。
(2)天数差额:计算当前日期与身份证日期的天数差额,函数如下:
=DATEDIF(DATE(MID(A1,7,4),MID(A1,11,2),MID(A1,13,2)),NOW(),"d"),显示为**天。
(3)年月差额:计算当前日期与身份证日期的年月差额,函数如下:
=CONCATENATE(DATEDIF(DATE(MID(A1,7,4),MID(A1,11,2),MID(A1,13,2)),NOW(),"y"),"年",DATEDIF(DATE(MID(A1,7,4),MID(A1,11,2),MID(A1,13,2)),NOW(),"ym"),"个月"),显示为**年**个月。
(四)通过函数检验身份证号码的正确性
我们有的时候手动输入的身份证号会不小心发生错误,我们可以通过函数来自动判定是否自己输错了。
原理:18位身份证号码的最后一位是检验码,它是根据身份证前17位数字依照规则计算出来的,其值0~9或X。一般情况只要有一位数字输入错误,依照规则计算后就会与第18位数不符。当然不排除按错误号码计算后恰好与检验码相符的情况,但这种情况出现的可能性较低。
函数表达式如下:
=MID("10X98765432",MOD(SUMPRODUCT(MID(A1,ROW(INDIRECT("1:17")),1)*2^(18-ROW(INDIRECT("1:17")))),11)+1,1)=MID(A1,18,1),显示结果为TRUE或者FALSE,如果身份证号符合规则显示TRUE,如果身份证号不符合规则显示FALSE。
(五)身份证号18位与15位的转化
一代身份证是15位(个别也有18位的),二代身份证为18位,工作中难免有时候会发生需要通过一个人15位的老身份证号推算18位身份证号,也可能需要通过一个人的18位身份证号来推算15位身份证号的情况。
1、身份证号15位升级为18位
20世纪的人:
=IF(LEN(A1)=15,REPLACE(A1,7,,19)&MID("10X98765432",MOD(SUMPRODUCT(MID(REPLACE(A1,7,,19),ROW(INDIRECT("1:17")),1)*2^(18-ROW(INDIRECT("1:17")))),11)+1,1),A1)
21世纪的人:
=IF(LEN(A1)=15,REPLACE(A1,7,,20)&MID("10X98765432",MOD(SUMPRODUCT(MID(REPLACE(A1,7,,20),ROW(INDIRECT("1:17")),1)*2^(18-ROW(INDIRECT("1:17")))),11)+1,1),A1)
2、身份证号18位降级为15位
=IF(LEN(A1)=18,LEFT(REPLACE(A1,7,2,),15),A1)
相信看到本节内容的读者们肯定有眼前一亮的感觉,估计大家都会收藏或者转发,不过提醒大家,如果提高EXCEL水平,最好是自己实际操作一遍,增强记忆哦。