|
身份证的类型简单来讲有一代和二代之分，一代是15位号码，二代是18位号码，与一代的不同之处在于补足了四位年份日期并且在末尾添加了一个校验字符。二代身份证自2005年开始换发，一代可以同时通用。根据规定，在2013年1月1日起全面停止使用一代身份证。 18位身份证的号段含义如下： 前2位表示所属省份代码，3~6位表示所属城市和区县代码，7~14位表示出生日期，格式为YYYYMMDD，15~17位是个人顺序码，其中17位可以标识性别，奇数表示男性，偶数表示女性，第18位是校验码，由前17位通过计算求得。 输入18位身份证显示4.45121E+17，怎么回事？ 这个是科学计数法，E+17就是10的17次方。默认情况下，数字超过12位就显示科学技术法，数字超过15位后面都显示0。 选择A列，将单元格格式设置为文本，再重新输入身份证即可。 设置好后，又有一个问题。如何才能保证身份证刚好是18位，同时还得符合身份证的所有条件，前17位必须是数字，第18位是X或者是数字。 18位可以用： =LEN(A2)=18 前17位是数字： =ISNUMBER(-LEFT(A2,17)) 第18位是数字或者字母X： =OR(ISNUMBER(-RIGHT(A2)),RIGHT(A2)="X") 综合这3个条件，得到可以进行数据有效性设置。 选择A2:A5，设置数据有效性（注：2013版叫数据验证），在“允许”下拉列表框中选择“自定义”选项。在“公式”文本框输入下面的公式，点击“确定”按钮。 =AND(LEN(A2)=18,ISNUMBER(-LEFT(A2,17)),OR(ISNUMBER(-RIGHT(A2)),RIGHT(A2)="X")) 数据有效性对于已经输入好的身份证无效，但可以把不满足条件的圈出来。对设置好数据有效性后进行“圈释无效数据”。 把无效的数据重新更改即可，然后输入正确的身份证，以后如果输入不正确的话，会提示错误，直到你输入正确为止。 关于身份证的准备工作已经做好，后面是获取各种信息的关键时刻。 通过提取码可以查询到省份跟地区。 省份就是提取身份证前2位，然后去地区码查询对应值。 =VLOOKUP(LEFT(A2,2),地区码!A:B,2,0) 地区就是提取身份证前6位，然后去地区码查询对应值。 =VLOOKUP(LEFT(A2,6),地区码!A:B,2,0) 出生日期就是从身份证第7位开始提取8位，然后将提取到的数字转换成0-00-00这种形式。 =TEXT(MID(A2,7,8),"0-00-00") 性别就是第15-17位，奇数就是男，偶数就是女。 =IF(MOD(MID(A2,15,3),2),"男","女") 验证码这个太复杂了，就不做说明。通过出生日期还可以获取一系列问题：周岁、生肖、生日提醒、星座等。现在就以这4个常见问题再进行说明。为了方便，出生日期就引用D2单元格。 周岁 =DATEDIF(D2,NOW,"y") 生肖 =MID("鼠牛虎兔龙蛇马羊猴鸡狗猪",MOD(TEXT(D2,"[$-130000]e")-4,12)+1,1) TEXT(D2,"[$-130000]e")的作用就是将日期转换成阴历年份。 生日提醒 =TEXT(TEXT(D2,"m-d")-TEXT(NOW,"m-d"),"还有0天生日;;今天生日") 先将日期转变成月日再进行相减，大于0就显示还有多少天生日，小于0就不显示，0就是今天生日。 星座 =LOOKUP(--TEXT(D2,"mdd"),{0,"摩羯座";121,"水瓶座";220,"双鱼座";321,"白羊座";421,"金牛座";522,"双子座";622,"巨蟹座";724,"狮子座";824,"处女座";924,"天秤座";1024,"天蝎座";1123,"射手座";1223,"摩羯座"}) 这个看着很长，其实很简单，只要获取星座对应表就可以。 经过了N步处理，最终效果如图。 Excel效率手册交流总群 361139809，有问题加群交流，想学习的朋友也可以点击“阅读原文”购买系列书籍。 |
|
|
