在Excel表格中,经常需要输入身份证号码,很多小伙伴很容易遇到一些问题,可能有些小伙伴知道怎么解决,但是只是知其然而不知其所以然。今天我们就来讲讲关于身份证的相关技巧,一起学习吧~
身份证号码录入
关于身份证的输入,初学Excel的童鞋一定会遇到以下问题,看看下面这个例子:
身份证号码录入
从上图中我们看到,末尾3位会变成0,这是因为在Excel当前单元格中输入的数字位数如果超过15位(不含15位)时,系统将15位以后的数字全部显示为“0”,并以科学计数法显示。这样是挺智能,但是失去了身份证原来的意义。我们怎么做可以让它显示正常呢。
来看看下图两种解决方式:
单元格式设置成文本或单引号
以上两种方式都能有效解决上述问题,一是设置单元格式为文本,二是输入单引号(英文输入法状态下)。有童鞋可能有点小疑问,输入西文单引号是否会影响文本的内容的计算,这里要告诉大家是不影响的哈。大家可以用len函数计算下字符串的长度来验证:
在C2单元格中输入=LEN(B2),在C3单元格中输入=LEN(B3),显示结果均为18.结果是一样的哈!
解决了身份证输入的问题,有时我们可能还要提取身份证中的出生年月、性别和年龄等等,然后再将提取的信息进行转化。
当然在提取和信息转化之前,我们必须要了解下身份证号码的结构及含义,见下图:
身份证号码组成图(来源百度)
这样我们就知道哪些信息分别对应的是性别和出生日期啦,那么要截取出文本中对应的字符串,我们一般用文本函数,文本函数相对比较简单,易学易会哈,今天我们要用到的文本截取函数是MID函数。
公式函数
MID函数
功能说明:MID 返回文本字符串中从指定位置开始的特定数目的字符,该数目由用户指定。
使用格式:=MID(text,start_num,num_chars),它包含三个参数
通俗解释:=MID(截取字符串,截取起始字符位置,截取的字符串个数)
学习了这个函数后我们就可以截取出性别的字符串啦,性别是从C2单元格中的第17位开始截取1位,即在D2单元格中输入公式:=MID(C2,17,1)
出生日期是从C2单元格中的第7位开始截取8位,在E2单元格中输入公式:=MID(C2,7,8)
输入好公式后,下拉填充公式即可。
mid函数
提取的这些数据与我们理解的性别和日期还有些差距,继续往下看↓↓↓:
性别中数字为奇数时则代表男性,为偶数时则代表女性
那我们只需要简单的判定即可。我们需要用的ISEVEN函数判定数字是否为偶数。
ISEVEN函数
功能说明:如果参数 number 为偶数,返回 TRUE,否则返回 FALSE。
使用格式:=ISEVEN(number)
通俗解释:=ISEVEN(被判断的数据),参数只有1个哈
从功能说明可以看出它返回的结果是TRUE或者FALSE,那我们还要配合IF函数使用,来得到性别。这里简单讲下IF函数的使用:
IF函数
功能说明:对逻辑值进行比较,得到一个判定结果对应的值
使用格式:=IF(logical_test,value_if_true,value_if_false)
通俗解释:=IF(真或假,结果为真返回的值,,结果为真返回的值)
说完了性别,我们说下出生日期,出生日期相对简单,只需要转化下格式即可。
我们这里要介绍的是Text函数,这个函数的功能非常强大,强大到你无法想象喔。按照小编的理解,这个函数都可以出个专题来讲。来看看它的用法:
TEXT函数
功能说明:TEXT函数可通过格式代码向数字应用格式,进而更改数字的显示方式。如果要按更可读的格式显示数字,或者将数字与文本或符号组合,它将非常有用。
使用格式:=TEXT(value,format_text)
通俗解释:=TEXT(将要转化格式的内容,将要显示的格式)
结合上面的介绍,我们来分别对D2和E2单元格的公式加工改造下吧,来看看下图:
显示格式转换
分别在D2和E2单元格中输入公式:
=IF(ISEVEN(MID(C2,17,1)),"女","男")
=TEXT(MID(C2,7,8),"0-00-00")
搞定啦,是不是很简单,还有最后一个问题需要解决,那就是年龄啦,这里给大家介绍另外两个函数,一个是YEAR函数,另外一个是TODAY函数
分别看看用法:
YEAR函数
功能说明:返回对应于某个日期的年份。 Year 作为 1900 - 9999 之间的整数返回。
使用格式:=YEAR(日期)
通俗解释:=YEAR(日期),带一个参数
TODAY函数
功能说明:获取今天的日期(来源于电脑的日期)
使用格式:=TODAY()
假通俗解释:=TODAY(),直接输入,不需要带参数.
学习了这两个函数后,就可以知道怎么得到年龄啦,在F2单元格中输入公式=YEAR(TODAY())-YEAR(E2),搞定!
来看看下图操作:
年龄转化
总结:
单元格输入数字长度大于15时,后面的数字会变成0,需要在输入前,将单元格格式设置成文本,或在输入的数字前加上英文状态下的单引号ISEVEN是判定偶数函数,与之对应的是ISODD函数,判定数据是否为奇数TEXT文本函数可用于各种文本格式转化,需要重点掌握YEAR函数、TODAY函数是日期相关的函数,还有其他使用频率高的日期函数,如:MONTH,DAY,DATE函数等等关于相关函数的学习,大家可以根据上面的例子,自己练习学习哈,强烈推荐大家参考Excel函数对应的帮助,写的比较详细。
今天的分享就到这里,内容比较多,但大部分的函数都相对比较简单,容易上手。相信小伙伴们,很快就会啦!感谢大家阅读,大家如有任何问题,欢迎评论留言,小编会一一解答。
效率优先
Copyright (C) 1999-20120 www.ahcar.com, All Rights Reserved
版权所有 环球快报网 | 联系我们:265 073 543 9@qq.com