浅谈用Excel从身份证号码中提取出生日期和性别

摘 要 每年新入学的一年级学生，都需要向上级教育部门上报一份包含身份证号、出生年月等内容的电子表格，以备建立全省统一的电子学籍档案。数百个新生，就得输入数百行相应数据，这可不是个轻松活儿。有没有什么办法能减轻一下输入工作量、提高一下效率呢？其实，我们只需在Excel中将学生的身份证号完整地输入后，它就可以帮我们自动填好出生日期和性别。

关键词 身份证号，Excel，出生日期，性别

中图分类号：F311 文献标识码：A

现在学生的身份证号已经全部都是18位的新一代身份证了，里面的数字都是有规律的。前6位数字是户籍所在地的代码，7－14位就是出生日期。第17位代表的是性别，偶数为女性，奇数为男性。比如，某学生的身份证号码(18位)是320521199208170241，那么表示该生1992年8月17日出生，性别为女。如果能想办法从这些身份证号码中将上述个人信息提取出来，不仅快速简便，而且不容易出错。现在就让我们想办法把这些数字“提取出来”吧。

在“提取”身份证号的数字之前，我们先来认识一下所用到函数以及它们的用途，方便大家理解。提取个人信息需要使用IF、MOD、 MID、INT、TODAY等函数。

一、MID(text,start_num,num_chars)

返回文本字符串中从指定位置开始的特定数目的字符，该数目由用户指定。本例中用来判断身份证里数值的奇偶数

Tex是包含要提取字符的文本字符串。

Start_num是文本中要提取的第一个字符的位置。文本中第一个字符的 start_num 为 1，以此类推。

Num_chars 指定希望 MID 从文本中返回字符的个数。

Num_bytes 指定希望 MIDB 从文本中返回字符的个数（按字节）。

如果 start_num 大于文本长度，则 MID 返回空文本 ("")。

如果 start_num 小于文本长度，但 start_num 加上 num_chars 超过了文本的长度，则 MID 只返回至多直到文本末尾的字符。

如果 start_num 小于 1，则 MID 返回错误值 #VALUE!。

如果 num_chars 是负数，则 MID 返回错误值 #VALUE!。

例如：MID(E3,7,4)表示：在E3中从左边第七位起提取4位数。

二、MOD(number,divisor)

返回两数相除的余数。结果的正负号与除数相同。

Number为被除数。

Divisor为除数。

如果 divisor 为零，函数 MOD 返回错误值 #DIV/0!。

例如：=MOD(3, 2) 3/2 的余数 (1) 。

=MOD(-3, 2) -3/2 的余数。符号与除数相同 (1)。

=MOD(3, -2) 3/-2 的余数。符号与除数相同 (-1) 。

=MOD(-3, -2) -3/-2 的余数。符号与除数相同 (-1)。

三、INT(number)

将数字向下舍入到最接近的整数。

number需要进行向下舍入取整的实数。

例如：=INT(8.9) 将 8.9 向下舍入到最接近的整数 (8)

=INT(-8.9) 将 -8.9 向下舍入到最接近的整数 (-9)

=A2-INT(A2) 返回单元格 A2 中正实数的小数部分 (0.5)

四、TODAY( )

返回当前日期的序列号。序列号是 Microsoft Excel 日期和时间计算使用的日期-时间代码。如果在输入函数前，单元格的格式为“常规”，则结果将设为日期格式。

五、IF(logical_test, [value_if_true], [value_if false])

如果指定条件的计算结果为 TRUE，IF 函数将返回某个值；如果该条件的计算结果为 FALSE，则返回另一个值。例如，如果 A1 大于 10，公式 =IF(A1>10,"大于 10","不大于 10") 将返回“大于 10”，如果 A1 小于等于 10，则返回“不大于 10”。

IF 函数语法具有下列参数 （参数:为操作、事件、方法、属性、函数或过程提供信息的值。）：

logical_test必需。计算结果为 TRUE 或 FALSE 的任何值或表达式。例如，A10=100 就是一个逻辑表达式；如果单元格 A10 中的值等于 100，则表达式的计算结果为 TRUE。否则，表达式的计算结果为 FALSE。此参数可以使用任何比较计算运算符。

value_if_true可选。logical_test 参数的计算结果为 TRUE 时所要返回的值。例如，如果此参数的值为文本字符串“预算内”，并且 logical_test 参数的计算结果为 TRUE，则 IF 函数返回文本“预算内”。如果 logical_test 的计算结果为 TRUE，并且省略 value_if_true 参数（即 logical_test 参数后仅跟一个逗号），IF 函数将返回 0（零）。若要显示单词 TRUE，请对 value_if_true 参数使用逻辑值 TRUE。

value_if_false可选。logical_test 参数的计算结果为 FALSE 时所要返回的值。例如，如果此参数的值为文本字符串“超出预算”，并且 logical_test 参数的计算结果为 FALSE，则 IF 函数返回文本“超出预算”。如果 logical_test 的计算结果为 FALSE，并且省略 value_if_false 参数（即 value_if_true 参数后没有逗号），则 IF 函数返回逻辑值 FALSE。如果 logical_test 的计算结果为 FALSE，并且省略 value_if_false 参数的值（即，在 IF 函数中，value_if_true 参数后没有逗号），则 IF 函数返回值 0（零）。

第一步，转换身份证号码格式。

我们先将表头，学生的姓名和身份证号完整地输入到Excel表格中(如图1)，并且要校对无误，这时身份证号这列默认为“数字”格式（单元格内显示的是科学记数法的格式），需要更改一下数字格式。把鼠标指针指向E这列单元格最上方，也就是字母E所在的“单元格”单击左键把这列选中，然后把鼠标指针指向这列的任意地方右键单击，在打开的快捷菜单中，左键单击“设置单元格格式”，在打开的“单元格格式”对话框中，选择“数字”选项卡，在“分类”列表框中选择“文本”(因为后四位很有可能是数字0打头，所以要选择“文本”格式，不然开头的0显示不出来;既使不是数字0打头,Excel中默认不显示18位数字,而是用科学记数法表示。)，单击“确定”按钮退出,然后再挨个输入每个人身份证号码。（如图2）

图1 、2通过上述方法，输入身份证号

第二步，“提取出”出生日期。

将光标指针放到“出生日期”列的单元格内，这里以C3单元格为例。然后输入“=MID(E3,7,4)&"年"&MID(E3,11,2)&"月"&MID(E3,13,2)&"日"”（注意：外侧的双引号不用输入，函数式中的引号和逗号等符号应在英文状态下输入）。回车后，你会发现在C3单元格内已经出现了该学生的出生日期。然后，把鼠标指针指向C3单元格的右下角，当鼠标指针变成黑色实线加号时，按下鼠标左键向下拖动填充柄，将公式向下复制到C10单元格后放手，这时其它单元格内就会出现相应的出生日期。（如图3 ）。

图3 通过上述方法，系统自动获取了出生年月日信息

MID(E3,7,4)表示：在E3中从左边第七位起提取4位数，即1990。

MID(E3,11,2) 表示：在E3中从左边第十一位起提取2位数，即12。

MID(E3,13,2) 表示：在E3中从左边第十三位起提取2位数，即15。

&""&表示：其左右两边所提取出来的数字不用任何符号连接。

&"-"&表示：其左右两边所提取出来的数字间用“-”符号连接。若需要的日期格式是yyyy年mm月dd日，则可以把公式中的“-”分别用“年月日”进行替换就行了（如图例就是用年月日连接）。若需要的日期格式是yyyy/mm/dd，则可以把公式中的“-”分别用“/”进行替换就行了。这样的数据以后就可以作为日期类型进行年龄计算。

小提示：MID函数是EXCEL提供的一个“从字符串中提取部分字符”的函数命令，具体使用格式在EXCEL中输入MID后会出现提示。

第三步，判断性别“男女”。

选中“性别”列的单元格，如B3。输入“=IF(MOD(MID(E3,17,1),2)=1,"男","女")” （注意：外侧的双引号不用输入，函数式中的引号和逗号等符号应在英文状态下输入）。回车后，该生“是男还是女”已经乖乖地判断出来了。拖动填充柄让其他学生的性别也自动输入。（如图4）

图4 性别被自动填入指定位置

MID(E3,17,1) 表示：在E3中从左边第十七位起提取1位数，即7。

MOD(MID(E3,17,1) ,2)表示：7除以2的余数。

IF(MOD(MID(E3,17,1),2)=1,"男","女")表示：7除以2的余数等于1，返回值“男”，即单元格B3显示为“男”，否则余数等于0则B3显示为“女”。

第四步，得出年龄。

选中“年龄”列的单元格，如D3。输入“=INT((TODAY()-C3)/365)” （注意：外侧的双引号不用输入，函数式中的引号和逗号等符号应在英文状态下输入）。回车后，该生的年龄就出来了。拖动填充柄让其他学生的年龄也自动输入。如图5。

图5 年龄被自动填入指定位置

TODAY()表示：系统当前日期，即现在当下的日期。

TODAY()-C3表示：当前日期减去出生日期（1990年12月15日），就可以算出这个人的出生天数。

INT((TODAY()-C3)/365)表示：这个出生天数再除去365天，就是这个人的年龄，这个年龄可能有多位小数，用INT可以自动将后面的小数去掉，只保留整数部分。

这样，通过三个简单的函数，我们就可以让EXCEL从身份证号中自动提取出生日期、性别和年龄并填充到单元格内，极大地减轻了我们的输入工作量。这个方法不仅可以用在学生管理方面，还可以用到其他的人事管理中，由此可见，在实际工作中，如果能够经常利用信息技术手段，将信息技术融入到实际工作中，对我们的工作将会起到一个事半功倍的作用。

（作者：本科，太原铁路机械学校，助理讲师，研究方向：计算机与教育）

参考文献：

[1]李相君，李海鹏.计算机应用基础(WindowsXP+Office2003)上机指导.上海.华东师范大学出版社，2009

[2]让Excel自动从身份证号中提取出生日期和性别. 2012年3月17访问.