《Excel自动从身份证中提取生日、性别、年龄》由会员分享,可在线阅读,更多相关《Excel自动从身份证中提取生日、性别、年龄(2页珍藏版)》请在金锄头文库上搜索。
1、Excel 自动从身份证中提取生日、性别、年龄现在学生的身份证号已经全部都是 18 位的新一代身份证了,里面的数字都是有规律的。前 6 位数字是户籍所在地的代码,714 位就是出生日期。第 17位“2”代表的是性别,偶数为女性,奇数为男性。我们要做的就是把其中的部分数字想法“提取出来”。转换身份证号码格式我们先将学生的身份证号完整地输入到 Excel2003 表格中,这时默认为“数字”格式(单元格内显示的是科学记数法的格式),需要更改一下数字格式。选中该列中的所有身份证号后,右击鼠标,选择“设置单元格格式”。在弹出对话框中“数字”标签内的“分类”设为“文本”,然后点击确定。“提取出”出生日期将
2、光标指针放到“出生日期”列的单元格内,这里以 C2 单元格为例。然后输入“=MID(B2,7,4)&年&MID(B2,11,2)&月&MID(B2,13,2)&日”(注意:外侧的双引号不用输入,函数式中的引号和逗号等符号应在英文状态下输入)。回车后,你会发现在 C2 单元格内已经出现了该学生的出生日期。然后,选中该单元格后拖动填充柄,其它单元格内就会出现相应的出生日期。小提示:MID 函数是 EXCEL 提供的一个“从字符串中提取部分字符”的函数命令,“=MID(B2,7,4)”表示取 B2 中的数字,从第 7 位开始,往后连续取 4 位。或者:假设数据在 A 列并从 A1 开始,在 B1 输
3、入:=text(mid(a1,7,8),0-00-00)下拉填充。如果不要“年月日”的话,只要输入“=MID(B2,7,4) &MID(B2,11,2) &MID(B2,13,2)”就可以了。判断性别“男女”选中“性别”列的单元格,如 D2。输入“=IF(MID(B2,17,1)/2=TRUNC(MID(B2,17,1)/2),女,男)”(注意如上)后回车,该生“是男还是女”已经乖乖地判断出来了。拖动填充柄让其他学生的性别也自动输入。这个公式的思路是:根据 18 位身份证的第 17 位的奇偶来判断男女性别。第 17 位数除以 2,如果是奇数,则会出现小数,然后用 trunc 截去小数,这个值就不等于第 17 位数除以 2。如果是偶数,除以 2 后,仍然等于截去小数后的值。此外,还有其它许多方法。例如:=IF(MOD(MID(D3,17),2),男,女)即求第 17 位数除以 2 的余数,如果余数为 1,则值为 “男”(返回真值);否则,如果余数不为 1,则值为 “女”(返回假值)。计算年龄:(其中 C3 是出生日期所在列)=YEAR(NOW()-YEAR(A1)另外,有专门的一个函数解决这个问题,在任意单元格中输入=DATEDIF(A1,NOW(),Y)就可以返回年数。注:A1 必须是日期格式。