Excel提取身份证年龄和性别③

• 2020 年 8 月 23 日
• 笔记

问题场景

• 从user表中的身份信息中拿到用户的年龄和性别；

• 以下方法也可适用于提取其他数据，目的在于通过实例操作了解更多函数用法；

• 以下图中数据都为测试数据，不具备真实性！

场景一

• 从user表中的18位身份证信息列中提取所有用户的年龄。

• • 不考虑15位的身份证信息。

目标

• 提取18位身份证信息列的出生日期来判断所有用户的年龄。

解决方案一

用year()、today()、mid()函数实现。

• 函数解释

=YEAR(serial_number)
//将系列数转换为年，serial_number一个日期值，其中包含要查找的年份=TODAY()
//TODAY函数没有参数，作为YEAR函数的参数来获取当前年份
//MID函数参考上篇文章，有解释含义和用法

• 第一步：在G2单元格英文状态下输入:=YEAR(TODAY())-MID(D2,7,4)。

• • 公式解释：使用TODAY函数作为 YEAR函数的参数来获取当前年份，减去他的出生年份（MID函数从D2格单元的第7位起截取4位获取出生年份）。

• 第二步：鼠标移到G2单元格的右下角，出现实心的黑色【+】号，双击填充此公式。

• 第三步：最后结果。

解决方案二

用Datedif()函数、Text()函数和MID()函数。

• 函数解释

=DATEDIF(start_date,end_date,unit)
//Start_date起始日期；End_date结束日期；Unit为所需信息的返回类型，通常有三种"Y""M""D"。
//Y、M、D参数：分别计算开始日期与结束日期的年份差、月份差、和天数差。
//text函数参考上篇文章，有解释含义和用法。

• 第一步：在G2单元格英文状态下输入:=DATEDIF(TEXT(MID(D2,7,8),”00-00-00″),TODAY(),”y”)。

• • 公式解释：MID函数从D2格单元的第7位起截取8位获取出生年月日，再用Text函数转换为日期格式，最后用datedif函数计算当前年份和出生日期的年份差。

• 第二步：鼠标移到G2单元格的右下角，出现实心的黑色【+】号，双击填充此公式。

• 第三步：最后结果。

解决方案三

用Datedif()函数、 date()函数和MID()函数。

• 函数解释

=DATE(year,month,day)
//将结果将设为日期格式，且year,month,day三个参数为年、月、日参数
//用法示例参考上篇。

• 第一步：在G2单元格英文状态下输入:=DATEDIF(DATE(MID(D2,7,4),1,1),TODAY(),”y”)。

• • 公式解释：MID函数从D2格单元的第7位起截取8位获取出生年月日，再用Date函数转换为日期格式（月和日的参数定1），最后用datedif函数计算当前年份和出生日期的年份差。

• 第二步：鼠标移到G2单元格的右下角，出现实心的黑色【+】号，双击填充此公式。

• 第三步：最后结果。

解决方案四

• 分两步，先提取出生日期，上篇中详细介绍了怎么提取出生日期，这列可以拿来用；

• 再用上述函数直接跟当前年份做差算出年龄。

场景二

• 从user表中的18位身份证信息列中判断所有用户的性别。

• • 不考虑15位的身份证信息。

目标

• 从18位身份证信息中判断所有用户的性别。

• • 18位身份证的第17位代表性别，奇数为男，偶数为女。

解决方案一

用if()、MOD()、MID()函数。

• 函数解释

=IF(logical_test,value_if_true,value_if_false)
//例：=if(2>1,1,0),如果2>1,则返回1否则返回0；
//例：=if(2>1,"Ture","False"):如果2>1,则返回Ture否则返回False=MOD(number,divisor)
//求余函数，Number 为被除数；Divisor 为除数，返回余数。

• 第一步：在H2单元格英文状态下输入:=IF(MOD(MID(D2,17,1),2),”男”,”女”)或者=IF(MOD(MID(D2,17,1),2)=1,”男”,”女”)。

• • 公式解释：MID函数从D2格单元的第17位，再用MOD函数求余，在用IF函数判断余数，余数为0则是假值，返回“女”，否则返回“男”（Excel中真值为0可以代表假）。

• 第二步：鼠标移到H2单元格的右下角，出现实心的黑色【+】号，双击填充此公式。

• 第三步：最后结果。

解决方案二

用if()、ISODD()、MID()函数。

• 函数解释

=ISODD(number)
//判断参数是不是奇数，如果是返回ture，否则返回False.

• 第一步：在H2单元格英文状态下输入:=IF(ISODD(MID(D2,17,1)),”男”,”女”)。

• • 公式解释：MID函数从D2格单元的第17位，然后用ISODD判断第17位是否为奇数，在用IF函数判断如果是，返回“男”，否则返回“女”。

• 第二步：鼠标移到H2单元格的右下角，出现实心的黑色【+】号，双击填充此公式。

• 第三步：最后结果。

总结

实际业务场景复杂时，如果不能一次性解决问题，可择优使用、或可结合使用，多尝试，办法总比困难多！