未登录

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

开通VIP
EXCEL揭秘身份证号内含的秘密,实在是太全了

2016-12-26

① 身份证号输入 隐藏部分身份证号码

性别提取 出生年月提取

年龄计算 周岁计算

省市区县地区判断 星座和生肖提取

Excel

技巧-身份证号显示不全怎么办

设置单元格格式中,点击选择“文本”

Excel

技巧-隐藏部分身份证号码

我们有时为了演示函数的应用,需要用到大量的真实身份证号码,当然,我们在网络上是需要保护隐私的,所以我们就将部分数字用“*”代替,下面,小编告诉你如何办理。

下面,我们先建立一个身份证号码的列,我们需要将出生年月有“****”代替。

在B1列输入函数公式:=REPLACE(A1,7,4,'****');这个函数的意思是:从A1单元格第7个数据进行替换,替换后面的四个数,替换成****;

Excel

技巧-

身份证号码性别提取

身份证号码隐藏着你的性别信息,如果我们建立的数据表有身份证号码那就不必录入性别了,直接利用公式就可以将性别输入,这样既方便了录入,提高了工作的效率。还节省了数据的存储空间。下面我们就来具体的做一下吧。

在身份证号码中,如果你的号码是15位,那么你的身份证号码的最后一位是奇数的话你就是男滴,相反就是女滴;如果你的号码是18位,那么第17位号码透露你的性别信息。首先我们要将第15位号码或者第17位号码提取出来,先在数据表的后面建立一个新的列,列标题为【性别号码】

在【性别号码】下面的一个单元格中输入公式 =IF(LEN(N3)=15,MID(N3,15,1),MID(N3,17,1)) ;公式的意思是,如果是十五位的身份证号码,就提取第十五位数字;如果是18位的号码就提取第十七位数字。

接着在p列输入性别标题

在【性别】下面,输入公式 =MOD(O3,2) ,此公式的意思是,判断性别号码是否能被2整除,如果能,就输出0,如果不能就输出1

向下拖动P3单元格的右下角,快速填充所有的行。这样你就可以看到了,如果是奇数就输出1,如果是偶数就输出0。所以数据中,1就代表男,0就代表女

如果你觉得这样看起来不方便,还可以将数字转换成文字。Q2单元格输入【性别】列标题,然后再Q3单元格输入公式=IF(P3=1,'男','女') ,这个公式的意思是,如果p3单元格数字是1,就输出男,如果是0就输出女。

同样的方法填充所有的行就得到的性别这个列

Excel

技巧-身份证出生年月提取

在出生年月处输入:=text(mid(a1,7,8),'0000-00-00')

Excel

技巧-身份证年龄计算

年龄处输入:=DATEDIF(TEXT(MID(A1,7,8),'#-00-00'),TODAY(),'Y')

然后打上“√”,就得出第一个身份证号的年龄;

Excel

技巧-身份证周岁计算

假设身份证号码在A1中,则截止到“今天”的周岁年龄的公式为:

=DATEDIF(TEXT((LEN(A1)=15)*19&MID(A1,7,(LEN(A1)=18)*2+6),'#-00-00'),TODAY(),'Y')

说明:上式对15或18位身份证号码均适用。

如果全是18位身份证号,则公式可简化为:

=DATEDIF(TEXT(MID(A1,7,8),'#-00-00'),TODAY(),'Y')

Excel

技巧-身份证判断地区省市县

Excel表格身份证号显示不全怎么办18位身份证里前两位是省份代码

省是1、2位,=left(S2,2)

市是3、4位,=mid(S2,3,2)

县是5、6位,=mid(S2,5,2)

第一步,先在Excel中,建立这样的对应表:

全国县及县以上行政区划代码(2014年最新)

提示:可以到百度文库中搜索,得到百度网友提供的数据。

第二步,再另一张Sheet中(例如Sheet2),输入待查的身份证号码。

第三步,在另一个单单元格中输入公式

例如,在单元格B2输入公式

=VLOOKUP(VALUE(LEFT(A2,2))*10000,Sheet1!A:D,2,FALSE)&VLOOKUP(VALUE(LEFT(A2,4))*100,Sheet1!A:D,3,FALSE)&VLOOKUP(VALUE(LEFT(A2,6)),Sheet1!A:D,4,FALSE)

即可一次性得到相应身份证号码,所对应的省份、城市名、区名。

EXCEL

技巧-身份证提取星座和生肖

新建一张表格,在表格中输入身份证号、出生年月、月日、生肖、星座,并输入一些身份证号码,我们将根据身份证号码导出生肖和星座。

选择B2单元格,在B2中输入=DATE(MID(A2,7,4),MID(A2,11,2),MID(A2,13,2)),回车,就可以看到出生年月了,用填充手柄把剩余的单元格都填充好。

选择C2单元格,在C2中输入=TEXT(B2,'M-D'),回车,就可以看到月日了,用填充手柄把剩余的单元格都填充好。

选择D2单元格,在D2中输入=MID('鼠牛虎兔龙蛇马羊猴鸡狗猪',MOD(YEAR(TODAY())-DATEDIF(B2,TODAY(),'y')-4,12)+1,1),回车,就可以看到生肖了,用填充手柄把剩余的单元格都填充好。

选择E2单元格,在E2中输入

=IF(AND(C2>='3-21',C2<='4-19'),'白羊座',if(and(c2>='4-20',C2<='5-20'),'金牛座',if(and(c2>='5-21',C2<='6-21'),'双子座',if(and(c2>='6-22',C2<='7-22'),'巨蟹座',if(and(c2>='7-23',C2<='8-22'),'狮子座',if(and(c2>='8-23',C2<='9-22'),'处女座',if(and(c2>='9-23',C2<='10-23'),'天秤座',if(and(c2>='10-24',C2<='11-21'),'天蝎座',if(and(c2>'11-22',C2<='12-21'),'射手座',if(and(c2>='12-22',C2<='1-19'),'摩羯座',if(and(c2>='1-20',C2<='2-18'),'水瓶座',if(and(c2>='2-19',C2<>

本文为头条号作者发布,不代表今日头条立场。

本站是提供个人知识管理的网络存储空间,所有内容均由用户发布,不代表本站观点。请注意甄别内容中的联系方式、诱导购买等信息,谨防诈骗。如发现有害或侵权内容,请点击一键举报
从APP上打开文章,阅读全文并永久保存 查看更多类似文章
来自:ChadExcelpert  > Excel
举报
[荐]  原创奖励计划来了,万元大奖等你拿!
猜你喜欢
类似文章
excel技巧:与身份证有关的5个Excel函数公式
「Excel技巧」你会从身份证号中提取性别年龄吗?不会我教你!
EXCEL办公:用excel提供身份证的出生年月、性别和年龄
请教如何用Excel表中身份证号码提取出生年月,性别,
excel如何输入身份证号码及自动显示性别
Excel表中身份证号码提取出生年月,性别,年龄的使用技巧_电脑知识交流:
更多类似文章 >>
生活服务
绑定账号成功
后续可登录账号畅享VIP特权!
如果VIP功能使用有故障,
可点击这里联系客服!