当前位置:编程问答 > Word >

函数自动提取身份证号码信息

问题:笔者在用金山表格2002制作职工简明登记表时,常常要输入身份证号码、性别、出生时间等内容。其实,身份证号码中已经包含了性别、出生时间等信息,我们何不利用函数将这些信息数值提取出来,进行简单判断以后,将其自动转换成性别、出生时间等内容显示出来呢!

  大家知道,目前的身份证号码有两种格式,一种是15位号码(如340501761217022),一种是18位号码(如340503197001090319)。在15位号码中,第7—12位数字(如761217)表示持证人的出生时间(如1976年12月17日),第15位数字(如2)表示持证人的性别(奇数为“男”,偶数为“女”);在18位号码中,第7—14位数字(如19700109)表示持证人的出生时间(如1970年1月9日),第17位数字(如1)表示持证人的性别。et源文件下载

  一、信息的提取、判断和自动显示

  此处,假定身份证号码保存在C列中,性别和出生时间分别保存在D列和E列中。

  1、性别的自动显示

  ①选中D2单元格,输入公式:=IF(MOD(IF(LEN(C2)=15,MID(C2,15,1),MID(C2,17,1)),2)=0,"女","男"),输入完成后,按下“Enter”键进行确认,第1位员工的性别则自动显示在D2单元格中[如图1]。


 
  上述函数式中涉及到的几个函数的含义分别是:
  LEN(C2)函数,用于统计C2单元格中字符串的字符数目。
  MID(C2,15,1)函数,用于从C2单元格中字符串的第15位开始提取1个字符。
  MOD(number,divisor)函数,用于给出数字number除以数字divisor后的余数。
  IF()函数,是一个逻辑判断函数。

  上述函数式的意思是:

  IF(LEN(C2)=15,MID(C2,15,1),MID(C2,17,1)):如果[IF]C2单元格中字符串的字符数是15[LEN(C2)=15],则从第15位开始,提取C2单元格字符串中的1个字符[MID(C2,15,1)];如果不是15位,则从第17位开始,提取1个字符[MID(C2,17,1)]。
  =IF(MOD(IF(LEN(C2)=15,MID(C2,15,1),MID(C2,17,1)),2)=0,"女","男"):如果[IF]提取出来的数值[IF(LEN(C2)=15,MID(C2,15,1),MID(C2,17,1))]除以“2”后余数为“0”[MOD(IF(LEN(C2)=15,MID(C2,15,1),MID(C2,17,1)),2)=0],则显示为“女”,反之显示为“男”。   


  按住左键向下拖拉,将D2单元格中的公式复制到下面的单元格中,显示出其他持证人的性别。

  2、出生时间的自动显示

  ①选中E2单元格,输入公式:=IF(LEN(C2)=15,MID(C2,7,2)+1900,MID(C2,7,4))&"-"&IF(LEN(C2)=15,MID(C2,9,2),MID(C2,11,2))&"-"&IF(LEN(C2)=15,MID(C2,11,2),MID(C2,13,2)),输入完成后,按下“Enter”键进行确认,第1位员工的出生时间则自动显示在D2单元格中[参见图1]。

  上述函数式的意思是:
  IF(LEN(C2)=15,MID(C2,7,2)+1900,MID(C2,7,4)):如果[IF]C2单元格中字符串的字符数为15[LEN(C2)=15],提取C2字符串中7、8两位数字,并加上1900[MID(C2,7,2)+1900],如果字符数不是15,则提取C2字符串的7—10位数字[MID(C2,7,4)]。
  函数式“IF(LEN(C2)=15,MID(C2,9,2),MID(C2,11,2))和IF(LEN(C2)=15,MID(C2,11,2),MID(C2,13,2))”的意思与上面相似。

  然后用连字符[&]将上述三个函数式的运算结果与两个间隔短线“-”连接成一个整体,表示出第一位持证人的出生时间。

  ②仿照上面的操作,用填充柄将E2单元格中的公式复制到下面的单元格中,用于判断出其他持证人的出生时间。

  ③选中E列中含有日期的单元格区域,执行“格式→单元格”命令,打开“单元格格式”对话框,切换到“格式”标签中(通常是默认的标签),先在“格式类别”下面选中“日期”选项,然后在“示例”下面选中一种适合国人习惯的日期样式,确定返回[显示结果参见图1]。

  [特别提示]如果不进行上述第③步的操作,也是完全可以的,只不过显示出来的日期是“1976-12-17”样式。

二、错误信息的提醒

  采取上面的函数式时,如果输入的身份证号码有错误,函数式可能仍返回一个合理的结果,使我们难以发现错误。于是,我对上述相关的函数式进行了改进。

  1、对“性别”函数的改进

  先将D2单元格中的公式修改为:=IF(MOD(IF(LEN(C2)=15,MID(C2,15,1),IF(LEN(C2)=18,MID(C2,17,1),"错误")),2)=0,"女","男"),然后用“填充柄”将D2中的公式复制到下面的单元格中即可。

  2、对“出生时间”函数的改进

  先将E2单元格中的公式修改为:=IF(LEN(C7)=15,MID(C7,7,2)+1900,IF(LEN(C7)=18,MID(C2,7,4),"号码错误"))&"-"&IF(LEN(C7)=15,MID(C7,9,2),IF(LEN(C7)=18,MID(C7,11,2),""))&"-"&IF(LEN(C7)=15,MID(C7,11,2),IF(LEN(C7)=18,MID(C7,13,2),"")),然后用“填充柄”将D2中的公式复制到下面的单元格中即可。

  经测试,经过这样改进后,当输入的身份证号码位数不正确时,“性别”函数返回错误的结果,“出生时间”函数直接给出提示[参见图1],提醒输入者进行改正。


CopyRight © 2012 站长网 编程知识问答 www.zzzyk.com All Rights Reserved
部份技术文章来自网络,