知识网经验知识百科全书!
登录

Excel如何设置身份证格式,excel如何设置选择项

158次浏览 | 2023-02-03 23:33:24 更新
来源 :一只小猪
最佳经验
本文由作者推荐

Hello,,大家好,今天跟大家分享一些关于Excel身份证号码的小技巧,这类问题最近问的人不少,相信很多人都用的到,特别是人事、财务工作者。废话不多说,让我们直接开始吧

一、输入身份证号码边E+显示

E+显示其实就是科学计数法的显示方式,之所以会这样,都是由于Excel的精度造成的

Excel的精度只有15位。默认情况下,如果数据超过了15位,15位之后的数据就会被舍去变为0,并且会以科学计数法的方式来进行数据显示,

在Excel中想要输入完整的身份证号码,就只能将单元格的格式设置为文本格式,然后再录入数据,其它的方法都是不行的

Excel如何设置身份证格式,excel如何设置选择项

二、身份证号码提取出生年月

公式:=TEXT(MID(A3,7,8),"0000-00-00")

在身份证号码中,第7到14位是每个人的出生日期,我们可以使用mid函数将其提取出来。最后再利用text函数来设置下日期的显示格式即可

Excel如何设置身份证格式,excel如何设置选择项

三、身份证号码计算年龄

公式:=DATEDIF(TEXT(MID(A3,7,8),"0000-00-00"),TODAY(),"y")

在这里主要是利用的DATEDIF,它的作用是计算2个日期的差值,参数一共有3个

第一参数:TEXT(MID(A3,7,8),"0000-00-00"),每个人的出生日期,表示开始的时间

第二参数:TODAY(),获取今天的日期,表示结束时间

第三参数:"y",计算类型,类型为Y DATEDIF表示计算年份的差值

在Excel中根据身份证号码计算年龄,这个方法是最准确的,只要没用DATEDIF,那么这个方法肯定不准确。

Excel如何设置身份证格式,excel如何设置选择项

四、身份证号码计算性别

公式:=IF(ISEVEN(MID(A3,17,1)),"女","男")

性别是由身份证号码的第17位决定的,偶数为女性,奇数为男性

首先我们使用MID将第17位的数字提取出来,随后使用ISEVEN来判断下这个数字是不是偶数,最后利用IF函数返回对应的性别即可

Excel如何设置身份证格式,excel如何设置选择项

五、身份证号码查重

公式:=IF(COUNTIF(A:A,A3u0026"*")=1,"","重复")

对身份证号码查重,最精确的就是利用countif函数,公式会自动将身份证号码转换为数值格式,这样的话后4位就会被舍去,不够精确,这里我们是使用连接符号让身份证号码依旧保持文本格式。

Excel如何设置身份证格式,excel如何设置选择项

六、身份证号码的真伪

公式:=IF(IF(RIGHT(A3,1)="X","X",--RIGHT(A3,1))=VLOOKUP(MOD(SUMPRODUCT(--MID(A3,{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17},1),{7;9;10;5;8;4;2;1;6;3;7;9;10;5;8;4;2}),11),{0,1;1,0;2,"X";3,9;4,8;5,7;6,6;7,5;8,4;9,3;10,2},2,0),"正确","错误")

判断身份证号码是否正确,主要是判断第18位,它是校验码,用于检验身份证号码是否正确,这个公式比较复杂,大家直接复制使用,更改A3单元格为你的数据位置即可

Excel如何设置身份证格式,excel如何设置选择项

以上就是今天分享的6个关于身份证号码的Excel技巧,大家也可以直接套用公式,只需更改为你表格的单元格位置即可

我是Excel从零到一,关注我,持续分享更多Excel技巧

想要从零学习Excel,这里↓↓↓

本文到此结束,希望对大家有所帮助。

标签:
收到0个赞
ipone4怎么设置铃声(iphone4铃声)

ipone4怎么设置铃声(iphone4铃声)

夏团来为大家解答ipone4怎么设置铃声以下
如何打开swf文件的压缩包(swf文件怎么解压)

如何打开swf文件的压缩包(swf文件怎么解压)

关于如何打开swf文件的压缩包,这个很多
手机可以冲q币吗(手机可以用q币开会员吗)

手机可以冲q币吗(手机可以用q币开会员吗)

大家好,linda来为大家解答手机可以冲q币
微信里面有个备用金怎么取消(微信支付备用金怎么取出来)

微信里面有个备用金怎么取消(微信支付备用金怎么取出来)

大家好,小乐来为大家解答微信里面有个
小米手机新品发布会时间(小米手机最新发布会几号开)

小米手机新品发布会时间(小米手机最新发布会几号开)

大家好,一只小美来为大家解答小米手机
如何一次性删除空间说说(怎么一次性删空间说说)

如何一次性删除空间说说(怎么一次性删空间说说)

大家好,来来为大家解答如何一次性删除
修复硬盘坏道(修复硬盘坏道)

修复硬盘坏道(修复硬盘坏道)

大家好,linda来为大家解答修复硬盘坏道
微信号改成什么呢好听(微信号改成什么好听英文)

微信号改成什么呢好听(微信号改成什么好听英文)

大家好,linda来为大家解答微信号改成什
如何杀手机病毒(如何杀手机病毒软件)

如何杀手机病毒(如何杀手机病毒软件)

大家好,linda来为大家解答如何杀手机病
关于我们 | 版权声明 | 免责声明 | 联系我们
免责声明:知识网所有文字、图片、视频、音频等资料均来自互联网,不代表本站赞同其观点,内容仅代表作者本人意见,若因此产生任何纠纷作者本人负责,本站亦不为其版权负责! 如有问题,请联系我们
CopyRight©1999-2024 www.44jj.com All Right Reserved 豫ICP备16009659号