未登录

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

开通VIP
excel如何输入身份证号码及自动显示性别

excel如何输入身份证号码

  默认情况下,Excel中每个单元格所能显示的数字为11位,输入超过12位的数值,系统自动将其转换为科学记数格式,如果输入超过15位的数值,系统自动将15位以后的数值转换为“0”。比如123456789012,就会显示为1.23457E+11。

  这些都是excel软件本身的原因,Excel软件会把超出12位以上的数值自动转换成小数,就出现了上述情况。

  如果我们需要在单元格输入身份证号码,以上设置就不能符合我们的要求。为了能让这些长位数数值完整显示出来,就需要使用下面介绍的方法,将单元格转换成文本格式,然后再输入。

  excel如何输入身份证号码的第一种方法:

  在输入之前,先选中要输入身份证号码的整列,然后依次单击进入“格式|单元格|数字”,在“分类”中选择“文本”即可。另外一个办法就是在“分类”中选择“自定义”,然后在右边“类型”选项中选择“@”就可以了。

  注意:必须先定义格式,然后输入号码。如果输好号码再定格式,显示会不正确。

  excel如何输入身份证号码的第二种方法:

  在输入这些长数值时,先输入一个英文状态下的单引号(“'”),然后接头输入数值即可。

  注意:这种方法适合输入少量的长数值,输入的英文状态下的单引号不影响显示和打印
 

在excel中输入身份证号如何做到自动显示姓别

 

  • excel中输入身份证号如何做到自动显示姓别,年龄及出生年月日A1输入身份证号码 B1显示性别、C1显示出生日期、D1显示周岁。

    一.提取出生年月信息
         由于只需要填写出生日期,因此这里我们只需要关心身份证号码的相应部位即可,在C2单元格中输入公式“=IF(LEN(A2)=15,MID(A2,7,4),MID(A2,9,4))”,其中:
         LEN(A2)=15:检查A1单元格中字符串的字符数目,本例的含义是检查身份证号码的长度是否是15位。
         MID(A2,7,4):从A1单元格中字符串的第7位开始提取四位数字,本例中表示提取15位身份证号码的第7、8、9、10位数字。
         MID(A2,9,4):从A1单元格中字符串的第9位开始提取四位数字,本例中表示提取18位身份证号码的第9、10、11、12位数字。
         IF(LEN(A2)=15,MID(A2,7,4),MID(A2,9,4)):IF是一个逻辑判断函数,表示如果A2单元格是15位,则提取第7位开始的四位数字,如果不是15位则提取自第9位开始的四位数字。
         如果需要显示为“70年12月”这样的格式,请使用DATE格式,并在“单元格格式→日期”中进行设置。
         二.提取性别信息
          例如性别信息统一在B列填写,可以在B2单元格中输入公式“=IF(MOD(IF(LEN(A2)=15,MID(A2,15,1),MID(A2,17,1)),2)=1,"男","女")”,其中:
         LEN(A2)=15:检查身份证号码的长度是否是15位。
         MID(A2,15,1):如果身份证号码的长度是15位,那么提取第15位的数字。
         MID(A2,17,1):如果身份证号码的长度不是15位,即18位身份证号码,那么应该提取第17位的数字。
         MOD(IF(LEN(A2)=15,MID(A2,15,1),MID(A2,17,1)),2):用于得到给出数字除以指定数字后的余数,本例表示对提出来的数值除以2以后所得到的余数。
         IF(MOD(IF(LEN(A2)=15,MID(A2,15,1),MID(A2,17,1)),2)=1,"男","女"):如果除以2以后的余数是1,那么B2单元格显示为“男”,否则显示为“女”。
         回车确认后,即可在B2单元格显示正确的性别信息,接下来就是选中填充柄直接拖曳。
         这是根据身份证号码(15位和18位通用)自动提取性别和出生年月的自编公式,供需要的网友参考:
    说明:公式中的A2是身份证号
    1、根据身份证号码求性别:=IF(LEN(A2)=15,IF(MOD(VALUE(RIGHT(A2,3)),2)=0,"女","男"),IF(LEN(A2)=18,IF(MOD(VALUE(MID(A2,15,3)),2)=0,"女","男"),"身份证错"))
    2、根据身份证号码求出生年月:=IF(LEN(A2)=15,CONCATENATE("19",MID(A2,7,2),".",MID(A2,9,2)),IF(LEN(A2)=18,CONCATENATE(MID(A2,7,4),".",MID(A2,11,2)),"身份证错"))
    3、根据身份证号码求年龄:=IF(LEN(A2)=15,year(now())-1900-VALUE(MID(A2,7,2)),if(LEN(A2)=18,year(now())-VALUE(MID(A2,7,4)),"身份证错"))
    三、年龄(周岁):
    =DATEDIF(A2,B2,"Y")&"年"&MOD(DATEDIF(A2,B2,"M"),12)&"月",A2为出生年月,B2为截止日期
    • B1: =IF(MOD(MID(A1,15,3),2),"男","女")
      C1:=TEXT(MID(A1,7,11)-500,"0-00-00,")
      D1:=DATEDIF(C1,NOW(),"y")
       
      15,18位身份证通用
    • 性别=IF(MOD(IF(LEN(A2)=15,MID(A2,15,1),MID(A2,17,1)),2)=0,"女","男")
      出生日期=IF(LEN(A2)=15,DATE(MID(A2,7,2),MID(A2,9,2),MID(A2,11,2)),DATE(MID(A2,7,4),MID(A2,11,2),MID(A2,13,2)))
      周岁=DATEDIF(C2,TODAY(),"Y")

 

本站是提供个人知识管理的网络存储空间,所有内容均由用户发布,不代表本站观点。请注意甄别内容中的联系方式、诱导购买等信息,谨防诈骗。如发现有害或侵权内容,请点击一键举报
从APP上打开文章,阅读全文并永久保存 查看更多类似文章
来自:莽野之措  > 我的图书馆
举报
[荐]  原创奖励计划来了,万元大奖等你拿!
猜你喜欢
类似文章
由身份证号码分析出生日期及性别 EXCEL表格自动生成
Excel和身份证不得不说的那些事儿
Excel中输入身份证号码及提取信息
EXCEL?身份证号提取信息
在Excel中根据身份证号码自动生成性别、出生日期和年龄
如何从在excel中身份证号码里提取需要的信息
更多类似文章 >>
生活服务
绑定账号成功
后续可登录账号畅享VIP特权!
如果VIP功能使用有故障,
可点击这里联系客服!