打开APP
userphoto
未登录

开通VIP,畅享免费电子书等14项超值服

开通VIP
Excel中输入身份证号码及提取信息

  一、解决输入身份证号码的问题
  默认情况下在Excel当前单元格中输入的数字位数如果超过11位(不含11位)时,系统将以“科学记数”格式显示输入的数字;当输入的数字位数超过15位(不含15位)时,系统将15位以后的数字全部显示为“0”。这样一来,如果我们要输入身份证号码(15位或18位),身份证号码就不能正确显示出来了。但我们有两种办法解决此问题:
  1、利用数字标签
  选中需要输入身份证号码的单元格(或区域),打开“单元格格式”对话框,进入“数字”标签,在“分类”下面选中“文本”选项,确定退出即可。
  2、在输入身份证号码时,在号码前面加上一个英文单引号“'”,即可让身份证号码完全显示出来(该单引号不显示)。
  二、让单元格内自动添加身份证前面相同部分
  身份证号码的前6位,是省、市、区(县)的代号(如资中县:511025),如果持证人都是资中县的,输入这样的的身份证号码,可以让单元格格式的“数字”标签帮你减轻输入的工作量:选中需要输入身份证号码的单元格区域,单击Excel上面菜单栏的格式打开“单元格”对话框(或者右键点击设置单元格格式),进入默认的“数字”标签,在“分类”下面选中最下面的“自定义”选项,然后在右侧“类型”下面的方框中输入:"511025"@,确定。以后输入身份证号码9位及以后的数字(如“197303246211”)后,系统会自动在前面加上51102519,形成完整的身份证号码(如“511025197303246211”)。
  其它需要输入数字含相同部分的也可按照这个方法输入。

Excel从身份证号码中提取个人信息。

  如18位身份证号码:前6位是公民户口登记地,第7到14位代表出生日期,第17位代表性别,奇数为男,偶数为女。 (15位身份证号码15位数代表性别)

  比如,某员工的身份证号码(18位)是320521197208170241,那么表示1972年8月17日出生,性别为女。如果能想办法从这些身份证号码中将上述个人信息提取出来,不仅快速简便,而且不容易出错。

  提取个人信息 需要使用IF、LEN、MOD、 MID、DATE等函数。某表格,其中员工的身份证号码信息已输入到C列,出生年月信息填写在D列,性别信息填写在B列。

  
1. 提取出生年月信息

  由于上交报表时只需要填写出生年月,不需要填写出生日期,因此这里我们只需要关心身份证号码的相应部位即可,即显示为“7208”这样的信息。在D2单元格中输入公式“=IF(LEN(C2)=15,MID(C2,7,4),MID(C2,9,4))”,其中:

  LEN(C2)=15:检查C2单元格中字符串的字符数目是否等于15,意即检查身份证号码的长度是否是15位。

  MID(C2,7,4):从C2单元格中字符串的第7位开始提取四位数字,即提取15位身份证号码的第7、8、9、10位数字。

  MID(C2,9,4):从C2单元格中字符串的第9位开始提取四位数字,即提取18位身份证号码的第9、10、11、12位数字。
  IF(LEN(C2)=15,MID(C2,7,4),MID(C2,9,4)):IF是一个逻辑判断函数,表示如果C2单元格是15位,则提取第7位开始的四位数字,否则就从第9位开始提取四位数字。

  如果需要显示为“70年12月”这样的格式,请使用DATE格式,并在“单元格格式→日期”中进行设置。

  2. 提取性别信息

  由于报表中各位员工的序号编排是按照上级核定的编制进行的,因此不可能按照男、女固定的顺序进行编排,如果一个一个手工输入的话,既麻烦又容易出错
  例如性别信息统一在B列填写,可以在B2单元格中输入公式“=IF(MOD(IF(LEN(C2)=15,MID(C2,15,1),MID(C2,17,1)),2)=1,"男","女")”,其中:

  LEN(C2)=15:检查身份证号码的长度是否是15位。

  MID(C2,15,1):如果身份证号码的长度是15位,那么提取第15位的数字。

  MID(C2,17,1):如果身份证号码的长度不是15位,即18位身份证号码,那么应该提取第17位的数字。

  MOD(IF(LEN(C2)=15,MID(C2,15,1),MID(C2,17,1)),2):用于得到给出数字除以指定数字后的余数,本例表示对提出来的数值除以2以后所得到的余数。

  IF(MOD(IF(LEN(C2)=15,MID(C2,15,1),MID(C2,17,1)),2)=1,"男","女"):如果除以2以后的余数是1,那么B2单元格显示为“男”,否则显示为“女”。

  回车确认后,即可在B2单元格显示正确的性别信息,接下来就是选中填充柄直接拖曳。如图2所示,现在这份报表无论是提取信息或是核对,都方便多了!

本站仅提供存储服务,所有内容均由用户发布,如发现有害或侵权内容,请点击举报
打开APP,阅读全文并永久保存 查看更多类似文章
猜你喜欢
类似文章
【热】打开小程序,算一算2024你的财运
excel如何输入身份证号码及自动显示性别
在Excel中根据身份证号码自动生成性别、出生日期和年龄
Excel告诉你,身份证号码中隐藏的秘密(15/18位通用)
EXCEL中如何从身份证号码求出生年月日及年龄公式,性别信息方法?
在excel中从身份证号中提取出生日期--方法
Excel表解读身份证号码信息
更多类似文章 >>
生活服务
热点新闻
分享 收藏 导长图 关注 下载文章
绑定账号成功
后续可登录账号畅享VIP特权!
如果VIP功能使用有故障,
可点击这里联系客服!

联系客服