号中藏宝　巧学函数.docVIP

号中藏宝　巧学函数 　　眼下是大学生求职应聘的黄金季节，人事主管小刘忙得不亦乐乎，她负责把应聘者的个人信息录入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所示的工作表以后，小张设计的公式马上从身份证号码中“挖掘”出了信息。现在小刘的好学精神上来了，非要小张说清楚“挖