Excel应用

朱晓玲

摘要：身份证作为每个人独一无二的公民身份的证明工具，身份证号码中包含着一个人的性别、出生年月日、籍贯、年龄等基本信息，在日常办公当中，我们常常需要录入这些基本信息，本文介绍了如何在Excel表格中快速提取这些信息的方法，避免了简单枯燥的录入工作，从而大大提高了工作效率。

关键词：Excel 身份证号码 基本信息

【中图分类号】TP391.14

日常办公当中，我们常常需要在表格中输入人员的姓名、性别、身份证号码、出生年月日，籍贯等基本信息，如果涉及到人员数量比较多的时候，录入的数据量大，逐项录入非常麻烦。如果利用功能强大的Excel来完成这些信息的录入，那么将会变得非常容易。首先我们来了解一下现在所使用的第二代身份证号码的编码规则，其由18位数字组成，第1到6位为归属地，第7到第10位为出生年份，第11到第12位为出生月份，第13到第14位为出生日期，第17位为性别（其中奇数为男，偶数为女），第18位为校验码。将下来，本文介绍如何使用Excel2010快速提取“表一”中的这些基本信息。

一、 出生日期的提取方法

1. 数据分列的使用

首先，选中“表一”中要分列的数据区域C2：C3，然后点击“数据→数据工具→分列”，调出分列窗口，在“请选择最合适的文件类型”处选择根据“固定宽度”分列，在“数据预览”区分别在身份证号码的第6和第7位之间及第14和第15位之间点击添加分列线将身份证号码分成三个部分，如“图1”所示；其次，在“数据预览”区点击身份證号码的第一部分前6位， 选择“列数据格式”区的“不导入此列（跳过）”，对身份证号码的最后一部分最后4位应用同样的“列数据格式”；再次，在“数据预览”区点击身份证号码中间部分第7到第14位，选择“列数据格式”区的“日期（D）：YMD”，将“目标区域”定位D2单元格，如“图2”所示；最后，点击“完成”即可，实现了从身份证号码中提取出生日期。

2. 函数的使用（MID函数、DATE函数、TEXT函数）

根据第二代身份证号码的编码规则，我们只需要将第7到14位的年月日按照需要的格式提取出来即可，方法如下：

（1） MID函数和DATE函数的组合使用

首先设置D2单元格为任一种日期格式，然后在D2单元格中输入公式为：

=DATE（MID（C2，7，4）， MID（C2，11，2）， MID（C2，13，2））

然后回车确认，即可提取出该人员的出生日期，再拖动填充柄把公式向下复制，其余人员的出生日期也可提取出来。

对上式函数的解释：①DATE函数返回年月日，语法结构为：DATE（year，month，day）。②MID函数返回文本字符串中从指定位置开始的特定数目的字符，该数目由用户指定，语法结构为：MID（text， start_num， num_chars），MID（C2，7，4）表示提取C2中的年份即1995，MID（C2，11，2）表示提取C2中的月份02，MID（C2，13，2）表示提取C2中的日期05。

（2） MID函数和TEXT函数的组合使用

首先设置D2单元格为任一种日期格式，然后输入公式为：

=--TEXT（MID（C2，7，8），”0-00-00”）

对上式函数的解释：①MID（C2，7，8）表示提取C2单元第7位开始往后的8位数字19950205。②TEXT函数表示将数值转化为自己想要的文本格式，TEXT函数前的两个减号，负负为正的意思，被称为减负运算，然后用TEXT函数结合减负运算把数据转换成指定的日期格式。

二、 性别的判断方法

根据第二代身份证号码的编码规则，我们只需要判断它的第17位数字，奇数为男，偶数为女，方法如下：

在E2单元格中输入公式为：=IF（MOD（MID（C2，17，1），2）=1，"男"，"女"）

然后回车确认，即可判断出该人员的性别，再拖动填充柄把公式向下复制，其余人员的性别也可判断出来。

对上式函数的解释：①MID（C2，17，1）表示提取C2的第17位数字2。②MOD函数返回两数相除后的余数，MOD（MID（C2，17，1），2）表示C2的第17位数字和2相除后的余数0。③IF函数根据指定的条件的逻辑判断的真假结果，返回相对应的内容，上述公式表示如果第17位数字是奇数，就显示“男”，否则就显示“女”。

三、 年龄的计算方法

根据前面提取出来的出生日期计算年龄，方法如下：

（1） 周岁计算

首先设置F2单元格为“常规”，然后在F2单元格中输入公式为：=INT（TODAY（）-D2）/365）

最后回车确认，即可完成计算出该人员的周岁年龄，再拖动填充柄把公式向下复制，其余人员的周岁年龄也可计算出来。

对上式函数的解释：①TODAY函数返回当前系统的日期。②INT函数是取整函数，上述公式表示将今天的日期与出生日期相减，所得数除以365化为年，再取整数，计算出周岁年龄。

（2） 虚岁计算

周岁公式基础上加1即可，方法如下：

在F2单元格中输入公式为：=YEAR（TODAY（））-YEAR（D2）+1，然后回车确认。

参考文献：

[1] 梁广洪.巧用Excel函数提取身份证中信息.铜仁职业技术学院学报，2010年02期

[2] 周长春.Excel函数的新应用—从身份证号码中自动生成性别与出生日期.才智，2011年26期endprint