从身份证中提取信息:Excel的自动处理技巧
在我们的日常生活和工作中,身份证信息处理是一项常见的任务。无论是为了整理客户资料,还是为了进行数据分析,从身份证中提取信息都是一个重要的环节。然而,手动处理这些信息既耗时又容易出错。幸运的是,Excel提供了一系列强大的功能,可以帮助我们自动处理这些信息。本文将介绍如何使用Excel从身份证中提取信息,以及如何利用自动处理技巧提高工作效率。
一、身份证信息的构成
中国的第二代居民身份证由18位数字组成,包括6位地址码、8位生日码、3位顺序码和1位校验码。地址码表示身份证持有人的常住户口所在地的行政区划代码;生日码表示身份证持有人的出生日期;顺序码表示同一地址码所标识区域内的同年同月同日出生的的人数;校验码则是根据前17位计算得出的。
二、使用Excel提取身份证信息
- 打开Excel,新建一个工作簿。
- 将身份证号码粘贴到A列的单元格中。
- 在B列的第1个单元格中输入“出生日期”,然后使用函数公式“=MID(A1,7,8)”提取出生日期。
- 在C列的第1个单元格中输入“性别”,然后使用函数公式“=IF(MOD(VALUE(MID(A1,17,1)),2)=0,"女","男")”提取性别。
- 在D列的第1个单元格中输入“年龄”,然后使用函数公式“=DATEDIF(DATE(VALUE(MID(A1,7,4)),VALUE(MID(A1,11,2)),VALUE(MID(A1,13,2))),TODAY(),"y")”提取年龄。
- 在E列的第1个单元格中输入“地址”,然后使用函数公式“=MID(A1,1,6)”提取地址。
- 在F列的第1个单元格中输入“省份”,然后使用函数公式“=VLOOKUP(LEFT(E2,2),省份表!1:339,2,FALSE)”提取省份。
- 在G列的第1个单元格中输入“城市”,然后使用函数公式“=VLOOKUP(MID(E2,4,2),城市表!1:339,2,FALSE)”提取城市。
- 在H列的第1个单元格中输入“校验码”,然后使用函数公式“=IF(LEN(A1)=18,IF(MOD(VALUE(MID(A1,17,1))+VALUE(MID(A1,10,1))+VALUE(MID(A1,3,1))+VALUE(MID(A1,15,3))+VALUE(LEFT(A1,3))+VALUE(MID(A1,7,4))+VALUE(MID(A1,11,2))+VALUE(MID(A1,13,2))+VALUE(RIGHT(A1,2)),0),IF(LEN(A1)=15,IF((VALUE(LEFT(A1,3))-97)*5+(VALUE(MID(A1,3,2))-90)*5+(VALUE(RIGHT(A1,2))-97)*5+VALUE(RIGHT(LEFT(A1,6),3))+VALUE(RIGHT(LEFT(A1,9),2))+VALUE(RIGHT(LEFT(A1,9),4))+VALUE(RIGHT(LEFT(A1,9),6))+VALUE(RIGHT(LEFT(A1,9),9)),0),IF((LEN(A1)=18)×((VALUE(MID(A1,7,4))>900)×((VALUE(MID