统计
  • 文章总数:855 篇
  • 评论总数:0 条
  • 分类总数:14 个
  • 最后更新:8月3日

看Excel和身份证号码相关的7个公式、4个技巧

本文阅读 3 分钟
首页 常用办公 正文

 

 

一、技巧篇

 

 

1、身份证的输入

在excel中输入大于11位数字,会以科学记数法显示。而大于15位,后面的数字全转换为0,所以不能直接在excel中输入身份证号。

 

解决方法:先把该列设置为文本型格式,或输入前先输入单撇(')再输入身份证号码

 


2、身份证的导入

 

从word、网页、数据库中复制含身份证信息的表格时,如果直接粘贴到excel中,身份证码列同样会后3位变成0。

 

解决方法:同1粘贴或导入前把excel表中存放身份的列设置为文本类型。

 

3、身份证的分列

 

如下图所示的员工信息在一列中 ,我们可以用分列的方式分隔成多列,但分列后身份证号码后3位会变成0。

解决方法:在分列的第3步,选取身份证列,选取文本类型即可。


4、身份证号长度验证

在输入身份证号码时,数字个数看起来很费劲。用数据有效性可以限制身份证号码输入必须是18位。

解决方法:选取输入单元格 - 数据 - 有效性 - 允许:文本长度,输入18

二、公式篇

 

单元格A1中是身份证号码

 

1、提取生日

=TEXT(MID(A1,7,8),"0-00-00")

 

2、提取年龄

=YEAR(TODAY())-MID(A1,7,4)

 

3、提取性别

=IF(MOD(MID(A2,17,1),2),"男","女")

 

4、提取出生地

参考:

http://mp.weixin.qq.com/s?__biz=MjM5NDYyNzAzNQ==&;mid=212006319&idx=1&sn=f9f9d177701f709470eee5771f04d2ee#rd

 

5、提取属相

=CHOOSE(MOD(MID(A1,7,4)-1900,12)+1,"鼠","牛","虎","兔","龙","蛇","马","羊","猴","鸡","狗","猪")

 

6、提取星座

=VLOOKUP(--TEXT(MID(A1,11,4),"2015-00-00"),{0,"摩羯";42024,"水瓶";42054,"双鱼";42084,"白羊";42114,"金牛";42145,"双子";42177,"巨蟹";42208,"狮子";42239,"处女";42270,"天秤";42301,"天蝎";42330,"射手";42360,"摩羯"},2)

 

7、Countif函数统计身份证号码出错的解决方法

由于Excel中数字只能识别15位内的,在Countif统计时也只会统计前15位,所以很容易出错。不过只需要用&"*"转换为文本型即可正确统计。

=Countif(A:A,A2&"*")

本文来自投稿,不代表本站立场,如若转载,请注明出处:
WPS文字中文字转表格与表格转文字,你转晕了么?
« 上一篇 03-13
photoshop怎么制作漂亮的钻石字体
下一篇 » 03-13