文档下载 文档下载导航
设为首页 | 加入收藏
搜索 请输入内容:  
 导航当前位置: 文档下载 > 所有分类 > IT/计算机 > EXCEL中如何提取身份证出生日期、性别、检验身份证号码的正确性
侵权投诉

EXCEL中如何提取身份证出生日期、性别、检验身份证号码的正确性

EXCEL中如何提取身份证出生日期、性别、检验身份证号码的正确性

“=YEAR(TODAY())-YEAR(F2)”。由于F2单元格中存储着上面计算出来“出生日期”(例如“1982年03月21日”),若TODAY()函数返回系统当前日期为“2006年3月1日”,那么G2单元格中计算出来的年龄就是24岁。

看到这里小刘立刻指出上述公式计算出来的是“虚岁”,即每过一个元旦,年龄就增加一岁。她又给小张出了一道“难题”:计算“实岁”,即生日过后满12个月,年龄再增加一岁的公式怎样设计?小张说:可以在H2单元格输入“=TRUNC((G2- TODAY())/365)”。公式中的“G2- TODAY()”计算系统当前时间与“出生日期”相差的天数,“(G2- TODAY())/365)”计算出两个日期之间相差的年数(小数)。最后使用TRUNC去掉结果的小数部分,得到两个日期之间相差的整数(即“实岁”)。

4.籍贯

从身份证号码中“挖掘”籍贯信息是比较复杂的一项工作,它花费了小张不少时间,顺便了解了很多的知识:身份证号码的第l至6位数是归属地代码,其中前两位是省、直辖市或自治区的代码。例如“北京市居民身份证”的前两位是“11”,“新疆维吾尔自治区居民身份证”的前两位是“65”等。出于保留资源等方面的考虑,各省、直辖市或自治区居民身份证的归属地代码不是连续的,例如“北京”、“天津”、“河北”、“山西”和“内蒙古”五个省区市的归属地代码是从11到15,而“辽宁”、“吉林”、“黑龙江”三个省的归属地代码却是从21到23。因此小张设计的“挖掘”籍贯信息的公式就显得比较长了,使用时首先在E2单元格输入下面的公式,回车以后即可得到D2单元格中存储的归属地信息。再将公式复制到E3、E4等单元格,即可得到对应D3、D4等单元格的身分证归属地(籍贯)信息。

=IF(MID(D2,1,2)<="15",CHOOSE(MID(D2,1,2)-10,"北京","天津","河北","山西","内蒙古"),IF(MID(D2,1,2)<="23",CHOOSE(MID(D2,1,2)-20,"辽宁","吉林","黑龙江"),IF(MID(D2,1,2)<="37",CHOOSE(MID(D2,1,2)-30,"上海","江苏","浙江","安徽","福建","江西","山东"),IF(MID(D2,1,2)<="46",CHOOSE(MID(D2,1,2)-40,"河南","湖北","湖南","广东","广西","海南"),IF(MID(D2,1,2)<="54",CHOOSE(MID(D2,1,2)-49,"重庆","四川","贵州","云南","西藏"),CHOOSE(MID(D2,1,2)-60,"陕西","甘肃","青海","宁夏","新疆"))))))

由于上面这个公式的结构比较复杂,小张就给小刘仔细讲解了一遍:公式一共使用了五个嵌套的IF函数,第一个IF函数中的逻辑判断式“MID(D2,1,2)<="15"”用来判断身份证归属地代码是否在11到15之间。如果这个逻辑判断式成立,那么公式就执行参数“CHOOSE(MID(D2,1,2)-10,"北京","天津","河北","山西","内蒙古")”。其中“(MID(D2,1,2)”返回身份证归属地(省市区)的代码(即身份证号码的前两位),如果(MID(D2,1,2)返回的结果是11(即北京市的代码),那么“MID(D2,1,2)-10”返回供CHOOSE函数使用的索引号。当“MID(D2,1,2)-10”的结果是“1”时,CHOOSE函数就会返回后面参数表中的身份证归属地“北京”。

上一页第9页

你可能喜欢

  • Excel使用技巧大全(超
  • 身份证号码验证
  • 出生日期
  • EXCEL人事管理系统
  • Excel中身份证计算年龄
  • EXCEL函数大全
  • Excel高级图表制作指南
  • EXCEL数组

热门文档

相关文档

站点地图 | 文档上传 | 侵权投诉 | 手机版
本站所有资源均来自互联网,本站只负责收集和整理,均不承担任何法律责任,如有侵权等其它行为请联系我们.
文档下载 Copyright 2013 doc.guandang.net All Rights Reserved.  -  email