• 注册
  • 推广免费拿钻石

    • 查看作者
    • 用Excel提取出生日期、性别、年龄及户口所在地等信息 图文

      身份证是居民的身份象征,在各个领域都要用到身份证。身份证中可以提取许多的相关信息,然而大多数人都不知道如何应用身份证号来提取有用信息。下面,小编教大家用Excel提取出生日期、性别、年龄及户口所在地等信息。

      判断身份证号码是否是18位

      输入身份证号码时需要将单元格格式设置为文本。工作时有可能会将身份证号码输错,多输入或者少输入。所以,首先要判断身份证号码是否是18位,再提取身份证信息。也可以在计算公式中判断,但输入公式太多,容易出错。

      公式“=IF(LEN(A3)=18,"是","否")”返回“是”即身份证号码为18位,反之则不是18位,输入错误。

      LEN函数得到字符串的长度。=LEN(A3)返回18表示A3单元格字符串长度是18。


      出生日期

      身份证号码是:622924199605211012第7位开始往后的4位是出生的年,第11位往后两位是出生的月份,第13位往后两位是出生日期。

      公式“=1*MID(A3,7,4)”提取年份,从A3单元格第7个数字开始截取4个数字。

      MID函数:一个字符串中截取出指定数量的字符,MID(A3,7,4)返回1996表示从A3单元格第7个数字开始截取4个数字。

      =1*MID(A3,7,4)表示将MID(A3,7,4)提取的文本数据转为数值数据

      公式“=1*MID(A3,11,2)”提取月份,从A3单元格第11个数字开始截取2个数字

      公式“=1*MID(A3,13,2)”提取日期,从A3单元格第13个数字开始截取2个数字

      出生日期“=DATE(C3,D3,E3)”

      或者直接用公式

      “=DATE(1*MID(A3,7,4),1*MID(A3,11,2),1*MID(A3,13,2))”提取出生日期


      年龄

      • 实岁计算

      =TRUNC((TODAY()-E3)/365),当前日期减出生日期再除以365,然后用TRUNC函数去掉结果的小数部分

      TRUNC函数:作用是去掉结果的小数部分。

      TODAY函数取得当前系统日期,不需要参数。

      • 虚岁计算

      =YEAR(TODAY())+1-YEAR(F3)

      YEAR函数:返回某个日期的年份,YEAR(F3)返回1996表示出生年份是1996年。

      实岁换算成虚岁:在本人生日到来之前,虚岁=实岁+2;在本人生日到来及以后,虚岁=实岁+1。


      性别

      • 方法一

      身份证号码第17位是单数代表男性,双数代表女性。

      =IF(ISEVEN(MID(A3,17,1))=TRUE,"女","男")

      ISEVEN函数:ISEVEN(MID(A3,17,1))=TRUE表示身份证号码第17位是双数,为女性;ISEVEN(MID(A3,17,1))=FALSE表示身份证号码第17位是单数,为男性。

      • 方法二

      =IF(MOD(MID(A3,17,1),2)=1,"男","女")

      MOD函数:两个数值表达式作除法运算后的余数,MOD(MID(A3,17,1),2)表示第17位数除以2,返回1则为男性,反之则为女性。


      户口所在地

      • 地区编码:身份证地区编码信息可直接在网上查找

      • 城市

      =VLOOKUP(1*LEFT(A2,4),地区编码!A2:B3559,2,0)

      LEFT函数:一个字符串的左边截取出指定数量的字符,LEFT(A2,4)表示从A2单元格最左边开始,截取4个字符

      VLOOKUP函数:一个查找函数

      VLOOKUP(查找目标,查找范围,返回值的列数,精确OR模糊查找)

      =VLOOKUP(1*LEFT(A2,4),地区编码!A2:B3559,2,0)表示在地区编码!A2:B3559区域内匹配身份证前4位,采用模糊查找返回“地区编码!A2:B3559”的第二列。

      注意:此处必须用1*LEFT(A2,4)将LEFT(A2,4)提取的文本数据转化为数值数据才能用VLOOKUP函数查找

      • 区/县

      =VLOOKUP(1*LEFT(A2,6),地区编码!A2:B3559,2,0)

      • 户口所在地

      =B2&C2

      或者直接提取户籍信息:

      =VLOOKUP(1*LEFT(A2,4),地区编码!A2:B3559,2,0)&VLOOKUP(1*LEFT(A2,6),地区编码!A2:B3559,2,0)

      以上就是用Excel提取出生日期、性别、年龄、及户口所在地等信息的具体方法,视频操作见于之前发的文章。感谢您的阅读!

    • 0
    • 0
    • 0
    • 2
    • 单栏布局 侧栏位置: