巧学函数：进行身份证号码信息挖掘
    眼下是大学生求职应聘的黄金季节，人事主管小刘忙得不亦乐乎，她负责把应聘者的个人信息录入Excel，确保信息真实可信是必须解决的问题。为此，小刘特地向信息部主管小张求教，学会了从身份证“挖掘”个人信息的方法，又快又好地完成了招聘的前期准备工作。
mid函数提取年月日可谓：“整理工作无穷尽，信息问题难小刘，Excel函数应用，从此更上一层楼。”

 一．数据录入快又准
 小刘负责录入的个人信息内容如图1所示，除了“序号”、“姓名”和“身份证号码”以外，其余信息由小张设计公式从“身份证号码”中“挖掘”。



 1．别让数据变“乱”
 刚开始工作小刘就碰到了难题，她输入的身份证号码变成了“1.10155E+17”之类。请教小张之后才知道“身份证号码”要用“文本”格式。实现这一点的第一种方法是选中D列右击鼠标，选择快捷菜单中的“设置单元格格式”，打开对话框的“数字”选项卡选中，选中“分类”下的“文本”然后“确定”即可。第二种方法是在输入的身份证号码前加一个单引号，Excel就可以把输入的数字变为“文本”了。第三种方法是选中D列，单击“格式”菜单下的“单元格”命令打开对话框，按如图1所示选中“分类”下的“自定义”。然后在“类型”框中输入一个“@”再“确定”即可。小刘按小张教的方法继续操作，录入的身份证号码就一切正常了。

 2．录入校验 错误靠边
 由于前来应聘的大学生高达几百人，一旦身份证号码录入出错可是要扣“银子”的，于是小刘“命令”小张拿出解决办法。在小刘的“威逼利诱”面前，小张很快想出了“高招”：
  第一步选中存放身份证号码的数据区域（例如“D2：D800”），单击Excel“数据”菜单下的“
有效性”命令，打开“数据有效性”对话框的“设置”选项卡。在“允许”下拉列表中选择“自定义”，接着在如图2所示“公式”框中输入“=COUNTIF（D:D，D2）=1”。

  第二步打开“出错警告”选项卡，在“标题”框内输入“数据重复”，并按如图3所示输入更详细的警告信息，单击“确定”按钮将打开的对话框关闭。当然，这一步是可选的，使用时可以根据具体情况取舍。
  此后只要在当前单元格中输入了重复数据，Excel就会弹出“数据重复”对话框告知小刘，并拒绝接受已经输入的重复数据。
  除了防止录入身份证号码出现重复以外，还要防止小张输入的号码长度不足15位或18位。接下来的第三步仍然是选中录入身份证号码的数据区域（例如“D2：D80”），单击“格式”菜单下的“条件格式”命令打开如图4所示对话框，在“条件一”下拉列表中选择“公式”，然后在中间的框内输入公式“=IF（LEN（D10）<>15，LEN（D10）<>18）”。
  第四步单击如图4中的“格式”按钮打开对话框，在“字体”选项卡中选择合适的颜或删除线等。之后如果D列中输入的数据长度不是15位或18位，其字体就会显示前面选择的颜（例如红）。


  3．录后检查 万无一失
  看到这里小刘忽然问道：假如上面的操作执行前已经录入了部分数据，那么有没有办法检查录入的身份证号码是否重复？稍微思考了一会，小张设计了一个带有公式的“条件格式”，圆满解决了小刘提出的问题。
  小张操作的第一步是选中如图1中的D2单元格，单击“格式”菜单中的“条件格式”命令，打
开如图5所示对话框。在“条件1”下拉列表选择“公式”，然后在右边的输入框中输入公式“=COUNTIF（$D:$D，D2）>1”。它的用途是计算D列单元格中的数据是否与D2相同，再进行比较以确定这个结果是否大于1（为“真”）。如果计算结果大于1（即存在相同的身份证号码），就应用右边设置的条件格式，否则保持单元格的格式不变。

 第二步是设置比较结果为“真”时应用的条件格式，方法是单击“格式”按钮打开如图6所示对话框，在“颜”下拉列表选中条件为“真”时显示的字体颜（例如红）。也可以根据需要选择其它字形或选中“删除线”，连续两次单击“确定”按钮将打开的对话框关闭。

 第三步是将D2单元格中的条件格式应用于D列的其它单元格，方法是选中D2单元格单击工具栏的“复制”按钮。再选中D列中需要应用条件格式的区域（例如D3：D80区域），单击“编辑”菜单中的“选择性粘贴”命令，打开对话框选中“格式”单击“确定”，那么D列中存在的重复数据就会显示前面设置的条件格式，例如用红带删除线的字体身份证号码。
  这时小刘忽然说到：如果我只想看出D列中重复录入的身份证号码，那么应当怎样操作？小张说：可以将如图5所示对话框中的公式修改为“=COUNTIF（$D$2:$D2，D2）>1”。这个公式中对数据区域结束单元格（即“$D2”）的引用会随它向下复制的过程而变化，例如格式复制到D3单元格后公式变为“=COUNTIF（$D$2:$D3，D3）>1”。因此查是否存在重复数据时，公式进行比较的范围是从当前单元格向上直到数据区域第一个单元格（即“$D2”），所以比较的结果只对第一个以后的相同名称的数据应用条件格式。

 二．隐藏信息充分“挖掘”
 当小刘将姓名和身份证号码输入如图1所示的工作表以后，小张设计的公式马上从身份证号码中“挖掘”出了信息。不过小刘的好学精神上来了，非要小张说清楚“挖掘”信息的基本原理，小张只好一一给她解释：
1．性别
  根据现行居民身份证号码的编码规定，18位身份证编码的1~6位为归属地（公民第一次申领身份证时常住户口所在地的行政区划，下同）代码，7~10位为4位的出生年份，11~12位为出生月份，13~14位为出生日期，17位（也就是倒数第二位）为性别（奇数为男，偶数
为女），18位（也就是最后一位）则是校验位。而早期使用的是15位的身份证编码，它的1~6位为归属地代码，7~8位是2位的出生年份，9~10位为出生月份，11~12位为出生日期，15位（也就是最后一位）是性别（奇数为男，偶数为女）。
  为了适应上述情况，性别计算公式要能够适应两种身份证号码的，使用时只须在C2单元格输入“=IF（LEN（D3）=15，IF（MOD（MID（D3，15，1），2）=1，"男"，"女"），IF（MOD（MID（D3，17，1），2）=1，"男"，"女"））”。回车即可得到D2单元格中存储的身份证号码的性别，而后只要把公式复制（选中D2单元格，鼠标指向单元格右下角然后向下拖动）到D3、D4等单元格，即可“挖掘”出其他身份证号码中的“性别”。
  看到这里，小刘问到上述公式是怎样的工作的？小张说：该公式由三个IF函数构成，其中“IF（MOD（MID（D2，15，1），2）=1，"男"，"女"）”和“IF（MOD（MID（D2，17，1），2）=1，"男"，"女"）”作为第一个函数的参数。公式中的“LEN（D2）=15”是一个逻辑判断语句，LEN函数提取D2、D3等单元格中的字符长度，如果该字符的长度等于15，则执行参数中的第一个IF函数，否则就执行第二个IF函数。
  在参数IF（MOD（MID（D2，15，1），2）=1，"男"，"女"）中。MID函数从D2的指定位置（第15位）提取1个字符，而MOD函数将该字符与2相除，获取两者的余数。如果两者
能够除尽，说明提取出来的字符是0（否则就是1）。逻辑条件MOD（MID（D2，15，1），2）=1不成立，这时就会在D2单元格中填入"女"，反之则会填入"男"。
  如果LEN函数提取的D2等单元格中的字符长度不等于15，则会执行第2个IF函数。除了MID函数从D2的指定位置（第17位，即倒数第2位）提取1个字符以外，其它运算过程与上面的介绍相同。

2．生日
 接下来小张让小刘仔细看看E2单元格中的公式“=IF（LEN（D2）=15，CONCATENATE（"19"，MID（D2，7，2），"年"，MID（D2，9，2），"月"，MID（D2，11，2），"日"），CONCATENATE（MID（D2，7，4），"年"，MID（D2，11，2），"月"，MID（D2，13，2），"日"））”，然后让她说出这个公式的执行过程。
  小刘说：上面这个公式中的“LEN（D2）=15”仍然是逻辑判断语句，它可以判断身份证号码是15位的还是18位，从而调用相应的计算语句。对15位的身份证号码来说，左起第7至12个字符表示出生年月日，此时可以使用MID函数从身份证号码的特定位置，分别提取出生年、月、日。然后用CONCATENATE函数将提取出来的文字合并起来，就能得到对应的
出生年月日。公式中"19"是针对早期身份证号码中存在2000年问题设计的，它可以在计算出来的出生年份前加上19。对18位的身份证号码的计算思路相同，只是它不存在2000年问题，公式中不用给计算出来的出生年份前加上19。

 3．年龄
  出生日期计算出来以后很容易得到“当前年龄”，小刘在G2单元格中输入公式“=YEAR（TODAY（））-YEAR（F2）”。由于F2单元格中存储着上面计算出来“出生日期”（例如“1982年03月21日”），若TODAY（）函数返回系统当前日期为“2006年3月1日”，那么G2单元格中计算出来的年龄就是24岁。
  看到这里小刘立刻指出上述公式计算出来的是“虚岁”，即每过一个元旦，年龄就增加一岁。她又给小张出了一道“难题”：计算“实岁”，即生日过后满12个月，年龄再增加一岁的公式怎样设计？小张说：可以在H2单元格输入“=TRUNC（（G2- TODAY（））/365）”。公式中的“G2- TODAY（）”计算系统当前时间与“出生日期”相差的天数，“（G2- TODAY（））/365）”计算出两个日期之间相差的年数（小数）。最后使用TRUNC去掉结果的小数部分，得到两个日期之间相差的整数（即“实岁”）。


 4．籍贯
 从身份证号码中“挖掘”籍贯信息是比较复杂的一项工作，它花费了小张不少时间，顺便了解了很多的知识：身份证号码的第l至6位数是归属地代码，其中前两位是省、直辖市或自治区的代码。例如“北京市居民身份证”的前两位是“11”，“新疆维吾尔自治区居民身份证”的前两位是“65”等。出于保留资源等方面的考虑，各省、直辖市或自治区居民身份证的归属地代码不是连续的，例如“北京”、“天津”、“河北”、“山西”和“内蒙古”五个省区市的归属地代码是从11到15，而“辽宁”、“吉林”、“黑龙江”三个省的归属地代码却是从21到23。因此小张设计的“挖掘”籍贯信息的公式就显得比较长了，使用时首先在E2单元格输入下面的公式，回车以后即可得到D2单元格中存储的归属地信息。再将公式复制到E3、E4等单元格，即可得到对应D3、D4等单元格的身分证归属地（籍贯）信息。
  =IF（MID（D2，1，2）<="15"，CHOOSE（MID（D2，1，2）-10，"北京"，"天津"，"河北"，"山西"，"内蒙古"），IF（MID（D2，1，2）<="23"，CHOOSE（MID（D2，1，2）-20，"辽宁"，"吉林"，"黑龙江"），IF（MID（D2，1，2）<="37"，CHOOSE（MID（D2，1，2）-30，"上海"，"江苏"，"浙江"，"安徽"，"福建"，"江西"，"山
东"），IF（MID（D2，1，2）<="46"，CHOOSE（MID（D2，1，2）-40，"河南"，"湖北"，"湖南"，"广东"，"广西"，"海南"），IF（MID（D2，1，2）<="54"，CHOOSE（MID（D2，1，2）-49，"重庆"，"四川"，"贵州"，"云南"，"西藏"），CHOOSE（MID（D2，1，2）-60，"陕西"，"甘肃"，"青海"，"宁夏"，"新疆"））））））

  由于上面这个公式的结构比较复杂，小张就给小刘仔细讲解了一遍：公式一共使用了五个嵌套的IF函数，第一个IF函数中的逻辑判断式“MID（D2，1，2）<="15"”用来判断身份证归属地代码是否在11到15之间。如果这个逻辑判断式成立，那么公式就执行参数“CHOOSE（MID（D2，1，2）-10，"北京"，"天津"，"河北"，"山西"，"内蒙古"）”。其中“（MID（D2，1，2）”返回身份证归属地（省市区）的代码（即身份证号码的前两位），如果（MID（D2，1，2）返回的结果是11（即北京市的代码），那么“MID（D2，1，2）-10”返回供CHOOSE函数使用的索引号。当“MID（D2，1，2）-10”的结果是“1”时，CHOOSE函数就会返回后面参数表中的身份证归属地“北京”。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。