|
Ⅰ背景 身份证号码隐藏很多信息，所在省份、区县、年龄、出生日期、性别等。 身份证分为一代身份证、二代身份证。号码长度，一代证15位，二代证18位。二代证与一代证相比，①出生日期变为"0000-00-00"，一代证出生日期为"00-00-00"；②二代证最后一位为校验码，判断身份证真伪。 二代身份证号码格式：000000 XXXX XX XX SQ S C。长度是18位，其中，000000为地区编码，XXXX XX XX分别为出生年、月、日，SQ为顺序编码，S为性别码（奇数为男，偶数为女），C为校验码。前17位全部是数字，第18位可以是数字，可以是字母"X"。 为了理解方便，本文只列出18位身份证号码计算方式。 Ⅱ准备 此处，用Excel软件来提取相关信息。以下随便举例一个身份证号。 通过Excel计算分析后，结果如下图： 相关信息的提取是通过Excel函数，用到的函数有LEFT、MID、TEXT、VLOOKUP、VALUE、MOD、IF、DATEDIF、TODAY、YEAR等。由于计算方法不同，可能还会用到其他函数。 Ⅲ操作： 1身份证号码输入： 单元格默认格式为"常规"，如果直接输入18位数字，默认会变成科学计数形式，单元格最多输入11位数字。在输入号码之前，需对单元格进行相关操作，主要2种方式：①改单元格格式为文本；②直接在号码前面加英文状态下单引号(必须是英文状态)。
2验证输入： 判断输入的身份证号码是否为18位 图片中验证输入结果是TRUE，说明是18位号码。如不是18位号码，比如只有前17位，如图： 验证输入结果是FALSE，说明不是18位号码，输入有误。 Excel函数为：=AND(LEN(A4)=18,OR(ISNUMBER(-A4),RIGHT(A4)="X")) 此外，可以通过"数据有效性"进行错误提示
3提取出生日期： 因为提取省份、区县需要用到VLOOKUP函数，操作稍微复杂一些，故先提取出生日期。 Excel函数为 =TEXT(MID(A3,7,8),"00-00-00") 4提取年龄 年龄分为周岁和虚岁，故计算方法不尽相同。周岁是按实际月份或天数计算，而虚岁是年份直接相减。 周岁 =DATEDIF(TEXT(MID(A3,7,8),"00-00-00"),TODAY(),"Y") 虚岁 =YEAR(TODAY())-YEAR(TEXT(MID(A3,7,8),"00-00-00")) 5提取性别 二代身份证号码第17位是性别码，奇数是男，偶数是女。故有两种方法，一是取余。这比较简单 取余 =IF(MOD(MID(A3,15,3),2),"男","女") 6提取省份 提取省份及区县，需要引用其他数据，省份及区县代码。之后会另外写一篇关于此代码的博文，视情况而定。引用数据需使用VLOOKUP函数。
=VLOOKUP(VALUE(LEFT($A3,2)),省份!$A:$B,2,0) 7提取区县，方法类似上面。
=VLOOKUP(VALUE(LEFT(A3,6)),区县!$A:$B,2,0) 注意事项： 使用VLOOKUP时，查找值需是数字。附上截图，个中区别，自己领会。
至于校验码，网上有相关计算方式，就不多赘述。 |
