论文部分内容阅读
眼下是大学生求职应聘的黄金季节，人事主管小刘忙得不亦乐乎，她负责把应聘者的个人信息录入Excel，确保信息真实可信是必须解决的问题。为此，小刘特地向信息部主管小张求教，学会了从身份证“挖掘”个人信息的方法，又快又好地完成了招聘的前期准备工作。可谓：“整理工作无穷尽，信息问题难小刘，Excel函数应用，从此更上一层楼。”
一、数据录入快又准
小刘负责录入的个人信息内容如图1所示，除了“序号”、“姓名”和“身份证号码”以外，其余信息由小张设计公式从“身份证号码”中“挖掘”。
1．别让数据变“乱”
一开始小刘就碰到了难题，她输入的号码变成了“1.10155E+17”之类，请教小张之后才知道“身份证号码”要用“文本”格式。实现这一点的第一种方法是选中D列右击鼠标，选择快捷菜单中的“设置单元格格式”，打开对话框的“数字”选项卡选中，选中“分类”下的“文本”然后“确定”即可。第二种方法是在输入的身份证号码前加一个单引号，Excel就可以把输入的数字变为“文本”了。第三种方法是选中D列，单击“格式”菜单下的“单元格”命令打开对话框，按如图1所示选中“分类”下的“自定义”，然后在“类型”框中输入一个“@”再“确定”即可。小刘按小张教的方法继续操作，录入的身份证号码就一切正常了。
2．录入校验 错误靠边
由于前来应聘的大学生多达几百人，一旦身份证号码录入出错可是要扣“银子”的，于是小刘“命令”小张拿出解决办法。在小刘的“威逼利诱”面前，小张很快想出了“高招”：
STEP1
选中存放身份证号码的数据区域（例如“D2：D800”），单击Excel“数据”菜单下的“有效性”命令，打开“数据有效性”对话框的“设置”选项卡。在“允许”下拉列表中选择“自定义”，接着在如图2所示“公式”框中输入“=COUNTIF（D:D，D2）=1”。
STEP2
打开“出错警告”选项卡，在“标题”框内输入“数据重复”，并按如图3所示输入更详细的警告信息，单击“确定”按钮将打开的对话框关闭。当然，这一步是可选的，使用时可以根据具体情况取舍。
此后只要在当前单元格中输入了重复数据，Excel就会弹出“数据重复”对话框告知小刘，并拒绝接受已经输入的重复数据。
STEP3
除了防止录入身份证号码出现重复以外，还要防止小刘输入的号码长度不足15位或18位。接下来的第三步仍然是选中录入身份证号码的数据区域（例如“D2：D80”），单击“格式”菜单下的“条件格式”命令打开如图4所示对话框，在“条件一”下拉列表中选择“公式”，然后在中间的框内输入公式“=IF（LEN（D10）<>15，LEN（D10）<>18）”。
STEP4
单击如图4中的“格式”按钮打开对话框，在“字体”选项卡中选择合适的颜色或删除线等。之后如果D列中输入的数据长度不是15位或18位，其字体就会显示前面选择的颜色（例如红色）。
3．录后检查 万无一失
看到这里小刘忽然问道：“假如上面的操作执行前已经录入了部分数据，那么有没有办法检查录入的身份证号码是否重复？”稍微思考了一会儿，小张设计了一个带有公式的“条件格式”，圆满解决了小刘提出的问题。
STEP1
选中如图1中的D2单元格，单击“格式”菜单中的“条件格式”命令，在“条件1”下拉列表选择“公式”，然后在右边的输入框中输入公式“=COUNTIF（$D:$D，D2）>1”。它的用途是计算D列单元格中的数据是否与D2相同，再进行比较以确定这个结果是否大于1（为“真”）。如果计算结果大于1（即存在相同的身份证号码），就应用右边设置的条件格式，否则保持单元格的格式不变。
STEP2
设置比较结果为“真”时应用的条件格式，方法是单击“格式”按钮，在“颜色”下拉列表选中条件为“真”时显示的字体颜色（例如红色）。也可以根据需要选择其他字形或选中“删除线”，连续两次单击“确定”按钮将打开的对话框关闭。
STEP3
将D2单元格中的条件格式应用于D列的其他单元格，方法是选中D2单元格单击工具栏的“复制”按钮。再选中D列中需要应用条件格式的区域（例如D3：D80区域），单击“编辑”菜单中的“选择性粘贴”命令，打开对话框选中“格式”单击“确定”，那么D列中存在的重复数据就会显示前面设置的条件格式，例如用红色带删除线的字体显示身份证号码。
二、隐藏信息充分“挖掘”
当小刘将姓名和身份证号码输入如图1所示的工作表以后，小张设计的公式马上从身份证号码中“挖掘”出了信息。现在小刘的好学精神上来了，非要小张说清楚“挖掘”号码信息的基本原理，小张只好一一给她解释：
1．性别：性别计算公式要能够适应两种身份证号码，使用时只需在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等单元格，即可“挖掘”出其他身份证号码中的“性别”。
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），"日"））”，执行后，生日就自动显示出来了。
3．年龄：出生日期计算出来以后很容易得到“当前年龄”，小刘在G2单元格中输入公式“=YEAR（TODAY（））-YEAR（F2）”。由于F2单元格中存储着上面计算出来“出生日期”（例如“1982年03月21日”），若TODAY（）函数返回系统当前日期为“2006年3月1日”，那么G2单元格中计算出来的年龄就是24岁。
三、身份证号码验证
上面的工作完成之后，小刘却把小张“打击”了一番：你设计的公式好是好，但是我怎么知道某个身份证号码的真假？
1．验证网站：“你使用身份证号码验证网站和工具就可以了。”小张顺手在IE地址栏输入“http://www.oicq88.com/idsearch/index.asp”，打开“身份证号码验证专业在线查询网”。在主页输入“15或18位身份证号”，单击“查询”便可得到性别、出生日期和发证地的信息。他接着说：“这个网站还有手机地址查询、IP地址所在地查询和邮编电话区号查询等功能，你按照查询身份证号码的方法操作就可以了。”
小刘问：“我要查询15位身份证号码升至18位后的结果以及它的归属地该怎样做？”小张说：“你可以上中国居民身份证升级换代/居民身份证验证查询（http://www.xjshzedu.com/oblog3/id/index.htm）。该网站的主页如图5所示，你只要打开它输入15位身份证号码，单击“查询”即可得到需要的结果。”
2．验证软件：“真不错”，小刘赞叹道，“如果我不能上网该怎么办？”“使用身份证号码验证软件呀。”小张回答。如“身份证信息解读7.5”（http://hbcrc.onlinedown.net:82/down/sfz75.rar），是一款“绿色软件”，将下载得到的压缩包释放到某个文件夹，执行其中的“身份证信息解读.exe”就可以查询身份证持有人的归属地、出生日期和性别等信息，已校验身份证号码的真实性（见图6）。
此外，如果需要查询全国各个地区的邮政编码、身份证号码和手机号码等等，可以考虑“邮编区号、身份证、手机号码查询器2.95”（http://cttxj.onlinedown.net/down/Query295.exe），该软件功能比较全面，但使用时需要进行安装，如果偶尔使用一次还是前者比较方便。
一、数据录入快又准
小刘负责录入的个人信息内容如图1所示，除了“序号”、“姓名”和“身份证号码”以外，其余信息由小张设计公式从“身份证号码”中“挖掘”。
1．别让数据变“乱”
一开始小刘就碰到了难题，她输入的号码变成了“1.10155E+17”之类，请教小张之后才知道“身份证号码”要用“文本”格式。实现这一点的第一种方法是选中D列右击鼠标，选择快捷菜单中的“设置单元格格式”，打开对话框的“数字”选项卡选中，选中“分类”下的“文本”然后“确定”即可。第二种方法是在输入的身份证号码前加一个单引号，Excel就可以把输入的数字变为“文本”了。第三种方法是选中D列，单击“格式”菜单下的“单元格”命令打开对话框，按如图1所示选中“分类”下的“自定义”，然后在“类型”框中输入一个“@”再“确定”即可。小刘按小张教的方法继续操作，录入的身份证号码就一切正常了。
2．录入校验 错误靠边
由于前来应聘的大学生多达几百人，一旦身份证号码录入出错可是要扣“银子”的，于是小刘“命令”小张拿出解决办法。在小刘的“威逼利诱”面前，小张很快想出了“高招”：
STEP1
选中存放身份证号码的数据区域（例如“D2：D800”），单击Excel“数据”菜单下的“有效性”命令，打开“数据有效性”对话框的“设置”选项卡。在“允许”下拉列表中选择“自定义”，接着在如图2所示“公式”框中输入“=COUNTIF（D:D，D2）=1”。
STEP2
打开“出错警告”选项卡，在“标题”框内输入“数据重复”，并按如图3所示输入更详细的警告信息，单击“确定”按钮将打开的对话框关闭。当然，这一步是可选的，使用时可以根据具体情况取舍。
此后只要在当前单元格中输入了重复数据，Excel就会弹出“数据重复”对话框告知小刘，并拒绝接受已经输入的重复数据。
STEP3
除了防止录入身份证号码出现重复以外，还要防止小刘输入的号码长度不足15位或18位。接下来的第三步仍然是选中录入身份证号码的数据区域（例如“D2：D80”），单击“格式”菜单下的“条件格式”命令打开如图4所示对话框，在“条件一”下拉列表中选择“公式”，然后在中间的框内输入公式“=IF（LEN（D10）<>15，LEN（D10）<>18）”。
STEP4
单击如图4中的“格式”按钮打开对话框，在“字体”选项卡中选择合适的颜色或删除线等。之后如果D列中输入的数据长度不是15位或18位，其字体就会显示前面选择的颜色（例如红色）。
3．录后检查 万无一失
看到这里小刘忽然问道：“假如上面的操作执行前已经录入了部分数据，那么有没有办法检查录入的身份证号码是否重复？”稍微思考了一会儿，小张设计了一个带有公式的“条件格式”，圆满解决了小刘提出的问题。
STEP1
选中如图1中的D2单元格，单击“格式”菜单中的“条件格式”命令，在“条件1”下拉列表选择“公式”，然后在右边的输入框中输入公式“=COUNTIF（$D:$D，D2）>1”。它的用途是计算D列单元格中的数据是否与D2相同，再进行比较以确定这个结果是否大于1（为“真”）。如果计算结果大于1（即存在相同的身份证号码），就应用右边设置的条件格式，否则保持单元格的格式不变。
STEP2
设置比较结果为“真”时应用的条件格式，方法是单击“格式”按钮，在“颜色”下拉列表选中条件为“真”时显示的字体颜色（例如红色）。也可以根据需要选择其他字形或选中“删除线”，连续两次单击“确定”按钮将打开的对话框关闭。
STEP3
将D2单元格中的条件格式应用于D列的其他单元格，方法是选中D2单元格单击工具栏的“复制”按钮。再选中D列中需要应用条件格式的区域（例如D3：D80区域），单击“编辑”菜单中的“选择性粘贴”命令，打开对话框选中“格式”单击“确定”，那么D列中存在的重复数据就会显示前面设置的条件格式，例如用红色带删除线的字体显示身份证号码。
二、隐藏信息充分“挖掘”
当小刘将姓名和身份证号码输入如图1所示的工作表以后，小张设计的公式马上从身份证号码中“挖掘”出了信息。现在小刘的好学精神上来了，非要小张说清楚“挖掘”号码信息的基本原理，小张只好一一给她解释：
1．性别：性别计算公式要能够适应两种身份证号码，使用时只需在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等单元格，即可“挖掘”出其他身份证号码中的“性别”。
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），"日"））”，执行后，生日就自动显示出来了。
3．年龄：出生日期计算出来以后很容易得到“当前年龄”，小刘在G2单元格中输入公式“=YEAR（TODAY（））-YEAR（F2）”。由于F2单元格中存储着上面计算出来“出生日期”（例如“1982年03月21日”），若TODAY（）函数返回系统当前日期为“2006年3月1日”，那么G2单元格中计算出来的年龄就是24岁。
三、身份证号码验证
上面的工作完成之后，小刘却把小张“打击”了一番：你设计的公式好是好，但是我怎么知道某个身份证号码的真假？
1．验证网站：“你使用身份证号码验证网站和工具就可以了。”小张顺手在IE地址栏输入“http://www.oicq88.com/idsearch/index.asp”，打开“身份证号码验证专业在线查询网”。在主页输入“15或18位身份证号”，单击“查询”便可得到性别、出生日期和发证地的信息。他接着说：“这个网站还有手机地址查询、IP地址所在地查询和邮编电话区号查询等功能，你按照查询身份证号码的方法操作就可以了。”
小刘问：“我要查询15位身份证号码升至18位后的结果以及它的归属地该怎样做？”小张说：“你可以上中国居民身份证升级换代/居民身份证验证查询（http://www.xjshzedu.com/oblog3/id/index.htm）。该网站的主页如图5所示，你只要打开它输入15位身份证号码，单击“查询”即可得到需要的结果。”
2．验证软件：“真不错”，小刘赞叹道，“如果我不能上网该怎么办？”“使用身份证号码验证软件呀。”小张回答。如“身份证信息解读7.5”（http://hbcrc.onlinedown.net:82/down/sfz75.rar），是一款“绿色软件”，将下载得到的压缩包释放到某个文件夹，执行其中的“身份证信息解读.exe”就可以查询身份证持有人的归属地、出生日期和性别等信息，已校验身份证号码的真实性（见图6）。
此外，如果需要查询全国各个地区的邮政编码、身份证号码和手机号码等等，可以考虑“邮编区号、身份证、手机号码查询器2.95”（http://cttxj.onlinedown.net/down/Query295.exe），该软件功能比较全面，但使用时需要进行安装，如果偶尔使用一次还是前者比较方便。