揭开身份证号码潜藏的秘密.pdfVIP

揭开身份证号码潜藏的秘密 解决了最为棘手的身份证号码录入问题后，每位员工的出生年月、性别、籍贯等信息的 录入又成了周小民目前最难逾越的“大山”了。因为在图346 所示的人事档案信息中，他需 要逐行、逐列的录入，稍不留神，都可能出现错行或错列的现象。所以，他需要目不转睛地 认真录入，并且多次核对。但有时上帝好象也并不总眷顾勤奋的人，虽然周小民这样认真的 录入，辛苦地核对，但交到罗拉手上的档案信息还是出现了不少张冠李戴的错误。 “实际上，如果身份证号码准确无误，我们完全可以借助Excel 强大的函数来自动填充 很多信息，象性别、出生年月和年龄等，是不需要手工录入的……”周小民的辛苦，罗拉看 在眼里，周小民的Excel 水平有限，罗拉也看在眼里，她更感觉到对新人的Excel 等自动化 办公软件培训的必要性。 我国身份证号码的每个编码都有着十分重要的意义。在18 位身份证号码中，前6 位是 地区代码，紧接着8 位是出生年月代码，由4 位年份、2 位月份、2 位日期组成；然后2 位 顺序码，倒数第2 位为性别代码，把奇数分配给男性，偶数分配给女性，最后1位是校验码。 在15位身份证号码中，前6 位是地区代码，紧接着是6 位出生年月代码，由2 位年份、2 位月份和2 位日期组成，接下来是2 位顺序码和最后1 位性别代码，同样的把奇数分配给男 性，偶数分配给女性。 根据这一规则，大家就可以很方便地从身份证号码中提取出诸如性别、出生年月等有效 信息。 在图346 所示的人事档案表中，D2 单元格性别的提取公式如下。 =IF(MOD(MID(C2,15,3),2),男,女) 按照身份证号码的编码规则，用户只需提取18 位身份证的第17 位或15位身份证的第 15 位，然后通过判断其是奇数还是偶数即可得出其性别。 因为是15位和18 位身份证混合存在，公式就必须做到15 位和18位兼容。所以，使用 MID 函数，从身份证号码中提取编码时，从第15位开始提取3 个字符。身份证号码为15位 时，从第15 位开始提取3 个字符，根据MID 函数的特性，所能提取到的也只有最后一个字 符，即第15 位；身份证号码为18 位时，将会从第15 位开始提取出3 个字符，如本例中的 “143”。 要判断一个数是奇数，还是偶数，最常用的方法就是使用MOD 函数与“2”求余，结果 等于1 则为奇数，等于0 则为偶数。 任何一个三位数的奇偶判断完全取决于他的个位数，个数数是奇数，原数即为奇数，反 之，为偶数。基于这个考虑，提取后三位判断其奇偶和提取最后一位判断奇偶的作用是完全 一致的，所以，当身份证为18位时，提取其15～17 位，即这里的 “143”，与仅提取 17 位 的 “3”的结果是一样的。 G2 单元格出生年月的公式如下。 =TEXT(RIGHT(19MID(C2,7,LEN(C2)/2-1),8),#-00-00) 由身份证号码的编码规则可知，18位身份证的第7～14位为出生日期代码，15 位身份 证号码的第7～12 位为出生日期代码。用户只需将18 位身份证中的7～14 位和15位的7～ 12 位代码提取出来，再利用格式函数，进一步把格式转换为日期格式，即可提取生日信息。 在G2 单元格的公式中，首先使用MID 函数，从身份证的第7 位开始提取，使用LEN 函 数，得出其字符个数，除以2 再减去1，可得出需要提取的位数，15 位身份证为LEN（15/2-1） =6，18 位为LEN （18/2-1）=8，即达到提取6 位与8 位出生日期代码的目的。 因为15 位身份证的出生日期缺少两位年份代码“19”（15 位身份证通常属于2000 年以 前出生的公民），所以公式使用“19”来扩充。可对于18位身份证，却会出现10 位的出生 日期，即“19XXXXXXXX”的情况。所以，公式中使用了RIGHT 函数，取右边8 位数，最后通 过TEXT 函数进行日期格式处理。 H2 单元格的年龄公式设置如下。 =DATEDIF (G2,TODAY(),y) DATEDIF 函数是Excel 隐秘函数之一。为了沿袭Lotus1-2-3，使之与其兼容，Excel2007 中仍然保留了这个函数，该函数主要用于计算两个日期之间相差的年、月与天数。其语法如 下。 DATEDIF (start_date,end_date,unit) Start_date 为一个代表开始日期的日期。 End_date 为时间段内的