18位的身份证号码常用作人员资料中必备信息，在作为常用资料信息大量用于Excel中，按照我国身份证号码规范，18位身份证号隐藏着大量的信息。
所以当我们获得了身份证号的时候，便可以从中提取人员的有关信息，包括籍贯、出生日期、性别等信息。在Excel中可以通过公式的运用快速提取出来相关信息。
一、提取出生日期
出生日期代码位于第7位至第14位，分别是前4位代表年份，其后2位代表月份，最后2位代表日期。
提取用到的公式：
TEXT（value, format_text）：用特殊的格式转换文本。Value指的是目标数据，format_text指的是特殊转换格式，例如“m/d/yyyy”（年月日）、#.000（小数位保留3位）。
MID(text, start_num, num_chars)：返回文本字符串中从指定位置开始的特定数目的字符。Text指的是目标文本，start_num指的是开始位置，num_chars指的是字符数。例如MID（”中华人民共和国”，3，2）=人民。
提取出生日期公式：=TEXT(MID(C3,7,8),"0年00月00日")。
二、提取性别
性别代码位于身份证号码第17位，即倒数第2位。奇数代表男性，偶数代表女性。
提取用到的公式：
MID（text, start_num, num_chars）：同上。
MOD（Number，Divisor ）：返回两数相除的余数。其中Number指的被除数，Divisor指的是除数，例如MID（3，2）=3÷2=1（余数）。
IF(logical_test, [value_if_true],)：指定条件返回指定结果。其中logical_test表达式，[value_if_true] 条件为真（true）时返回的结果，[value_if_false]条件为否（false）时返回的结果。例如if(A2>0，“大于”，”小于”)。
=IF(MOD(MID(D3,17,1),2)=0,"女","男")
三、提取籍贯
籍贯代码位于前6位，前2位代表省级代码，中2位代表市级代码，后2位代表县（区）级代码。相关的代码已成规则，比如11代表北京，12代表天津。
提取籍贯需要提前准备“全国省市区代码表”作为数据表源。
提取用到的公式：
VLOOKUP（搜索值，搜索区域，搜索区域列数，逻辑值）：依据给定的搜索值，在指定搜索区域内第1列相同的值返回对应列数的值。
LEFT（$D3,6）*1：指的是搜索值为身份证号D3中从左数前6位的字符，“*1”是因为身份证号在Excel中单元格格式为“文本”，通过与1相乘使其转换为数字，使其与搜索区域“全国省市区代码表”中第1列格式一致，同为数字。LEFT（$D3,6）*1＝110101。
从“全国省市区代码表”的A列至E列搜索区域的第1列与110101相同时，返回110101对应的第3列的数值“北京市”。逻辑值=0表现相似匹配即可，如果逻辑值=1表示精确匹配。
其他市级、县（区）级代码同理。
评论 (0)