Excel中自动填写出生日期和性别

现在填写人事报表时，一般都包括性别、出生年月日和身份证号几项基本内容。而我们一般校对的重点都放在身证号这一项上，要保证它万无一失。但在这个信息化、无纸化办公的年代，让你在输入完身份证号后再去输入性别和出生日期是不是有点不情愿。如今二代身份证换发已经基本结束，身份证号相应的也都变成了18位。而18位身份证号是由17位数字本体码和1位校验码组成。排列顺序从左至右依次为：6位数字地址码，8位数字出生日期码，3位数字顺序码和1位校验码。1到6位为地区代码其中1、2位数为各省级政府的代码，3、4位数为地、市级政府的代码，5、6位数为县、区级政府代码。如15(内蒙古)04(赤峰市)03(元宝山区)，内蒙古赤峰市元宝山区的代码为150403。7到14位为出生年月日。15到17位为顺序号，表示在同一地址码所标识的区域范围内，对同年、同月、同日出生的人编定的顺序号，顺序码的奇数分配给男性，偶数分配给女性。18位为效验码(识别码)，是通过复杂公式算出的，普遍采用计算机自动生成。是前面17位的一种检验代码，如果你改变了前面某个数字而后面的效验代码不响应改变就会被计算机判断为非法身份证号码，注意X也是效验代码的一种。这也就是说，其实我们已经把性别和出年日期输入到计算机中了，只不过应该想办法把它提取出来。

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

图1

　2、在B2单元格中输入公式：=IF(MOD(MID(D2,17,1),2)=1,"男","女")，接着把鼠标指针指向B2单元格的右下角，当鼠标指针变成黑色实线加号时，按住左键向下拖动将公式向下复制到B16单元格后放手。(如图2)

图2

　　3、仿照上步的做法在C2单元格中输入公式：=DATE(MID(D2,7,4),MID(D2,11,2),MID(D2,13,2))，并将公式向下拖动复制到C16单元格后放手。(如图3)

图3

4、函数和公式说明：

　　MID函数是从字符串中提取指定个数的字符。其语法格式为：MID(string, start, [length])， string表示从该字符串表达式中提取指定个数的字符。Start表示被提取的字符在字符串中的开始位置(左边起第多少位)。Length是要提取多少个字符，如果省略或length超过字符串的总字符个数(包括start处的字符)，将返回字符串中从start处到字符串结束的所有字符。MID(D2,17,1)表示从D2单元格的身份证号中左边第17位起提取1位字符。

　　MOD函数是返回一个除法运算后的余数的函数。其语法格式为：MOD(number,divisor)这里number是被除数，divisor是除数。MOD(7,4)=3，3便是由7除以4所得出的。如果number小于divisor，则函数的结果会等于number;如果number正好被divisor除尽，则函数的结果等于0。MOD(MID(D2,17,1),2)表示D2单元格中的身份证号左边数第17位数除以2的余数。

　　IF函数用于执行真假值判断后，根据逻辑测试的真假值返回不同的结果，因此函数也称之为条件函数。可以使用函数 IF 对数值和公式进行条件检测。它的语法为IF(logical_test,value_if_true,value_if_false)。其中Logical_test表示计算结果为 TRUE(真) 或 FALSE(假) 的任意值或表达式。Value_if_true显示在logical_test 为 TRUE(真) 时返回的值，Value_if_true 也可以是其他公式。Value_if_false 显示在logical_test 为 FALSE(假) 时返回的值。Value_if_false 也可以是其他公式。也就是说如果第一个参数logical_test返回的结果为真的话，则执行第二个参数Value_if_true的结果，否则就执行第三个参数Value_if_false的结果。在=IF(MOD(MID(D2,17,1),2)=1,"男","女")公式中，logical_test是MOD(MID(D2,17,1),2)=1，也就是D2单元格中的身份证号左边数第17位数除以2的余数是否等于1。value_if_true是“男”，value_if_false是“女”。意思是如果余数等于1，则填入“男”，如果不等于1，则填入“女”。

　　DATE函数是返回代表特定日期的序列号的函数。其语法格式为：DATE(year,month,day) Year可以为一到四位数字。Month代表每年中月份的数字。如果所输入的月份大于12，将从指定年份的一月份开始往上加算。例如：DATE(2007,20,8) 返回代表 2008 年8月8日的序列号。Day?代表在该月份中第几天的数字。如果 day 大于该月份的最大天数，则将从指定月份的第一天开始往上累加。例如，DATE(2008,7,39) 返回代表 2008 年8月8日的序列号。如DATE(2008,8,8)=2008-8-8。在=DATE(MID(D2,7,4),MID(D2,11,2),MID(D2,13,2))公式中，MID(D2,7,4) 表示从D2单元格中的身份证号中左边第7位起提取4位数作为年份;MID(D2,11,2) 表示从D2单元格中的身份证号中左边第11位起提取2位数作为月份;MID(D2,13,2) 表示从D2单元格中的身份证号中左边第13位起提取2位数作为日。