2022年巧用Excel：利用身份证号码 提取个人信息.docx
20222022 年巧用年巧用 ExcelExcel：利用身份证号码：利用身份证号码 提取提取个人信息个人信息一、分析身份证号码其实，身份证号码与一个人的性别、诞生年月、籍贯等信息是紧密相连的，无论是 15 位还是 18 位的身份证号码，其中都保存了相关的个人信息。15 位身份证号码：第 7、8 位为诞生年份(两位数)，第9、10 位为诞生月份，第 11、12 位代表诞生日期，第 15 位代表性别，奇数为男，偶数为女。18 位身份证号码：第 7、8、9、10 位为诞生年份(四位数)，第 11、第 12 位为诞生月份，第 13、14 位代表诞生日期，第 17 位代表性别，奇数为男，偶数为女。例如，某员工的身份证号码(15 位)是 320521720807024，那么表示 1972 年 8 月 7 日诞生，性别为女。假如能想方法从这些身份证号码中将上述个人信息提取出来，不仅快速简便，而且不简单出错，核对时也只须要对身份证号码进行检查，确定可以大大提高工作效率。二、提取个人信息这里，我们须要运用 IF、LEN、MOD、MID、DATE 等函数从身份证号码中提取个人信息。如图1 所示，其中员工的身份证号码信息已输入完毕(C 列)，诞生年月信息填写在 D 列，性别信息填写在 B 列。1.提取诞生年月信息由于上交报表时只须要填写诞生年月，不须要填写诞生日期，因此这里我们只须要关切身份证号码的相应部位即可，即显示为“7208”这样的信息。在 D2 单元格中输入公式“=IF(LEN(C2)=15,MID(C2,7,4),MID(C2,9,4)”，其中：LEN(C2)=15：检查 C2 单元格中字符串的字符数目，本例的含义是检查身份证号码的长度是否是 15 位。MID(C2,7,4)：从 C2 单元格中字符串的第 7 位起先提取四位数字，本例中表示提取 15 位身份证号码的第 7、8、9、10 位数字。MID(C2,9,4)：从 C2 单元格中字符串的第 9 位起先提取四位数字，本例中表示提取 18 位身份证号码的第 9、10、11、12 位数字。IF(LEN(C2)=15,MID(C2,7,4),MID(C2,9,4)：IF 是一个逻辑推断函数，表示假如 C2 单元格是 15 位，则提取第 7 位起先的四位数字，假如不是 15 位则提取自第 9 位起先的四位数字。假如须要显示为“70 年 12 月”这样的格式，请运用DATE 格式，并在“单元格格式日期”中进行设置。2.提取性别信息由于报表中各位员工的序号编排是根据上级核定的编制进行的，因此不行能根据男、女固定的依次进行编排，假如一个一个手工输入的话，既麻烦又简单出错例如性别信息统一在 B 列填写，可以在 B2 单元格中输入公式“=IF(MOD(IF(LEN(C2)=15,MID(C2,15,1),MID(C2,17,1),2)=1,男,女)”，其中：LEN(C2)=15：检查身份证号码的长度是否是 15 位。MID(C2,15,1)：假如身份证号码的长度是 15 位，那么提取第 15 位的数字。MID(C2,17,1)：假如身份证号码的长度不是 15 位，即18 位身份证号码，那么应当提取第 17 位的数字。MOD(IF(LEN(C2)=15,MID(C2,15,1),MID(C2,17,1),2)：用于得到给出数字除以指定数字后的余数，本例表示对提出来的数值除以 2 以后所得到的余数。IF(MOD(IF(LEN(C2)=15,MID(C2,15,1),MID(C2,17,1),2)=1,男,女)：假如除以 2 以后的余数是 1，那么 B2 单元格显示为“男”，否则显示为“女”。回车确认后，即可在 B2 单元格显示正确的性别信息，接下来就是选中填充柄干脆拖曳。如图 2 所示，现在这份报表无论是提取信息或是核对，都便利多了！图 1 输入身份证号码图 2 提取个人信息
