每年开学时,经常发现各小学上报来的学生的姓名、性别、出生年月、年龄等信息或多或少的有错误。每年都要把学生信息打印出来让各班主任校对、勘误,非常麻烦,大家也怨声载道。安徽省从今年开始要求学生在入学时录入身份证号码,那么如果我们能利用Excel的函数功能,使录入的学生身份证号码自动生成性别、出生年月和年龄等信息,那岂不是省时省力又省心的大好事吗。下面让我们一起来探讨一下吧!
　　
　　一、身份证号码的录入
　　
　　现在学生的身份证号都是18位,而Excel默认的数字录入只能正确显示前面15位,后面3位都显示为0,那么该怎么办呢?其实只需要改变单元格的数字格式就可以了,将原来默认的“常规”更改为“文本”。 常用的有两种方法:
　　1. 在英文标点的状态下,先输入单引号“,”再输入身份证号码(如图1)。这种输入方法比较适合少量学生的身份证号码的输入。
　　2. 首先设置单元格的格式:选中所有需要录入身份证号码的单元格,然后单击“格式”|“单元格”,在弹出“单元格格式”对话框中选中“数字”选项,再在分类项里单击“文本”,最后单击“确定”即可(如图2)。然后逐个录入学生的身份证号码。
　　如果要大量输入同一个地方的身份证号码,可以采用下面的两种方法来提高输入的速度:
　　(1)首先在H列单元格输入身份证号码的后十位数字,接着在D2单元格输入公式“=34292319&H2”,按回车键,再将鼠标移至单元格右下角,当鼠标指针变成+形状时,拖动至填充的最后一个单元格,即可完成自动填充(自动填充方法一),然后将该列数据复制,并在原位置“选择性粘贴”,弹出“选择性粘贴”对话框,选择“数值”确定即可,最后把不需要的H列数据清空。
　　(2)首先自定义单元格的格式:选中所有需要录入身份证号码的单元格,然后单击“格式”|“单元格”,在弹出“单元格格式”对话框,选中“数字”选项,再在分类项里单击“自定义”,在类型中输入“34292319?”最后单击“确定”(如图3)。然后只需要逐个录入学生身份证号码的后十位数字,Excel将自动将刚才设置的“34292319”补充在输入的数字前。
　　温馨提示:在自定义单元格格式时,如果在“类型”中输入的数字中有“0”,要在“0”的前面加上英文标点在“!”,否则身份证号码会出现错误。
　　
　　二、利用身份证号码提取性别
　　
　　在E2单元格输入公式“=IF(MOD(VALUE(MID(D2,17,1)),2)=1,"男","女")”,按回车键,然后将鼠标移至单元格右下角,当鼠标指针变成+形状时,双击完成自动填充(自动填充方法二)。
　　 其中MID函数的作用是提取身份证号码的第17位字符的性别特征码,VALUE函数的作用是将性别特征码的格式由“文本”转换为“数字”,MOD函数的作用是将性别特征数字与2取余,IF函数的作用是进行性别判断,如果余数为1,则性别是“男”,否则为“女”。具体函数的使用方法大家可以参看Excel函数的帮助。(如图4)
　　
　　三、利用身份证号码提取出生年月
　　
　　在F2单元格中输入公式“=DATE(MID(D2,7,4),MID(D2,11,2),MID(D2,13,2))”,按回车键确定,右击该单元格,选择“设置单元格格式”,将日期的类型设置为“2001年3月”(如图5),然后将鼠标移至单元格右下角,当鼠标指针变成+形状时,双击完成自动填充(如图6)。其中MID函数的作用是提取身份证号码中的出生年月日,DATE函数的作用是将文本转换为日期格式。
　　
　　四、计算学生的实际年龄
　　
　　在G2单元格中输入公式“=DATEDIF(F4,TODAY
　　( ),"Y")”,Excel就会根据系统的日期,自动计算出学生的实际年龄,并且每次打开文件时Excel都会自动更新其数据。如果需要计算特定日期的实际年龄,只需要修改系统的时间,再打开文件即可。如果不想每次自动更新数据,只需将该列数据复制,然后在原位置“选择性粘贴”,弹出对话框,选择“数值”确定即可。其中TODAY函数的作用是返回当前日期,DATEDIF函数的作用是计算两个日期之间的间隔。(如图7)
　　 利用Excel函数还能从身份证中提取地址等信息。怎么样,Excel的功能是不是很强大?只要平时多用心,我们就会有更多的发现。让我们一起努力学习电脑知识吧!