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

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个赞
荣耀手表s1表带宽度(荣耀es手表表带接口宽度)

荣耀手表s1表带宽度(荣耀es手表表带接口宽度)

大家好,linda来为大家解答荣耀手表s1表带
使命召唤很卡怎么回事(使命召唤手游特别卡怎么办)

使命召唤很卡怎么回事(使命召唤手游特别卡怎么办)

关于使命召唤很卡怎么回事,这个很多人
杭州iphone14(杭州iphone14pro官网入口)

杭州iphone14(杭州iphone14pro官网入口)

大家好,来来为大家解答杭州iphone14以下
颜色)(颜色大全)

颜色)(颜色大全)

关于颜色),这个很多人还不知道,今天团
qq防沉迷身份证(qq防沉迷身份证)

qq防沉迷身份证(qq防沉迷身份证)

关于qq防沉迷身份证,这个很多人还不知
兵线是从游戏开始后多少秒(兵线是从游戏开始后多少秒的)

兵线是从游戏开始后多少秒(兵线是从游戏开始后多少秒的)

大家好,一只小美来为大家解答兵线是从
电脑失灵自己乱点(电脑失灵自己乱点怎么办恢复)

电脑失灵自己乱点(电脑失灵自己乱点怎么办恢复)

夏团来为大家解答电脑失灵自己乱点以下
cpu温度高怎么办(cpu温度高解决办法)

cpu温度高怎么办(cpu温度高解决办法)

关于cpu温度高怎么办,这个很多人还不知
惠普打印机系统安装(惠普打印机驱动安装步骤)

惠普打印机系统安装(惠普打印机驱动安装步骤)

大家好,来来为大家解答惠普打印机系统
修复)(修复疤痕)

修复)(修复疤痕)

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