在Ｅｘｃｅｌ　２００７中身份证号码的妙用

张　鹏

摘要：介绍在Excel2007中身份证号码的输入，以及提取生日、判断性别等信息。

关键词：Excel2007；身份证号码；妙用

我们在使用Excel2007进行人事档案管理，需要在单元格中输入身份证号码，会碰到如何输入身份证号码的问题。我们又知道身份证号码里包含了每个人的出生日期、性别等信息，那么如何从身份证号码里提取有用的信息呢?

1身份证号码的输入

在Excel2007中，在单元格中输入15位数字的时候，Excel2007会自动将其转换成科学记数法显示，执行“格式”菜单→“单元格”命令，设置数值格式，就能显示15位的老身份证号码。现在新的身份证号码一般为18位，用同样的方法，15位以后的数字会变为0。例如123456789012345678，回车，设置数值格式就会变为123456789012345000。那么如何输入18位身份证号码呢?

方法一：把18位身份证号码作为数字文本输入，在输入数字前先输入单引号。这种方法在输入前总要输入单引号，显得有点繁琐。

方法二：在輸入数字之前，先选定需要输入的单元格，然后单击“格式”菜单中的“单元格”命令，找到“数字”项中的“自定义”命令，然后在其右面的“类型”框中选择“＠”项，“确定”之后再输入即可。

2避免重复输入身份证号码

俗话说得好，解决问题要从根本抓起。在需要输入学号、身份证号码等具有唯一性的数据时，完全有条件也应该做到不出现重复的数据。过去这么难：打开“条件格式”对话框，在右侧的输入框中输入类似于“=COUNTIF($A$2:$A$21，A2)＞1”这样的公式，当然还需要通过“格式→图案”设置相关的颜色，确认后才能避免在A2:A21的单元格区域中出现重复数据。

2007更简单：首先选中A2:A21单元格区域，接下来切换到“开始”标签页，在“条件格式”下拉菜单中选择“突出显示单元格规则→重复值”，此时会打开“重复值”对话框，在这里可以自行定义重复值单元格的颜色，例如设置为醒目的红色，确认后即可生效。以后，每当输入重复数据时，当前单元格会立即变成红色。

如果木已成舟，大量的数据已经输入完毕，此时你需要做的恐怕就是将相关的重复数据找出来后将其删除。过去这么难：需要通过高级筛选隐藏重复项，然后再将不重复的项复制到其他的工作表中，前前后后需要好几个步骤才能解决问题，操作也颇为麻烦。

2007很简单：选中需要删除重复项的单元格区域，然后切换到“数据”标签页，直接单击工具栏中的“删除重复项”，此时会打开“删除重复项”对话框，确认后即可将指定区域中的重复项一次性清除。注意，选择处理区域时最好不要选择标题行。

3身份证号码中提取生日

大家都知道，身份证号码已经包含了每个人的出生年月日，对于老式的15位身份证而言，7-12位即个人的出生年月日，对于新式的18位身份证而言，7-14位代表个人的出身年月日。根据身份证号码的这种排列规律，结合Excel2007的有关函数，我们就能实现利用身份证号码自动输入出生年月日。

假设在A列单元格中输入身份证号码，在B列单元格中输入“=IF(LEN(A2)=15.CONCATENATE(“19”，MOD(A2,7,2)，“年”MID(A2,9,2)，“月”，MID(A2,11,2)，“日”)，CONCATENATE(MID(A2,7,4)，“年”，MID(A2,11,2)，“月”，MID(A2,13,2)，“日”))”这样的公式，很容易提取到生日。共用到四个函数，作用分别为：

(1)IF(10gical_test，value_if_true，vat-ue_iUalse)：根据逻辑表达式测试的结果，返回相应的值。IF函数允许嵌套；

(2)LEN(text)：返回文本字符串中字符个数；

(2)CONCA7ENATE(textl，text2……)：将若干个文字项合并至一个文字项中；

(4)M[D(textstart-num,num_chars)：从文本字符串中指定的起始位置起，返回指定长度的字符。

4判断性别

身份证号码中，老式的15位的身份证号码最后一位为性别(奇数为男，偶数为女)。对于新式的18位身份证，17位(即倒数第二位)为性别(同样奇数为男，偶数为女)。只要输入“=IF(LEN(A2)=15,1F(MOD(MID(A2,15,1)，2)=1，“男”，“女”)，1F(MOD(MID(A2,17.1)，2)=1”，“男”，：女”))”这样的公式，即可从身份证号码中判断性别。用到一新函数MOD(number，divisor)：返回两数相除后的余数。

5身份证号码中算年龄

根据前面出生年月日求年龄，我们可以编辑“＝(YEAR(TODAY())-YEAR(A1))“这样的公式。A1中存放的是出生年月日，并用到两个函数，作用分别为：

(1)TODAY()：返回系统的日期；

(2)YEAR()：提取日期中的年份。