营长说
总有HR伙伴咨询,如何从身份证号中计算性别、生日和年龄等等信息。今天营长推荐给你身份证号在Excel中的常用计算公式和处理技巧。
我国身份证号码是18位,其中前2位代表省/自治区/直辖市,3,4位代表市,5,6位代表县区,7-14位代表出生年月日,15-18位分别为顺序码和校验码。
01 计算性别
D2=IF(MOD(MID(C2,17,1),2),"男","女")
MID函数获取倒数第2位数字
MOD是取余数函数,在这里可以判断奇偶性
IF函数根据奇偶性,奇数为男,偶数为女。
02 计算生日在Excel2013及以上版本的Excel中,使用快速填充功能可以从规则的数据中提取一定样式的数据。
快速填充需要给Excel软件做1个或多个例子,让电脑找规律。
快速填充的快捷键是【 Ctrl E】,效率更高。注意事项:
- 预先设置成两位月份和日期格式:yyyy-mm-dd
- 可以多做几个示例,提高准确度
2013以下的版本可以使用公式
E1=--TEXT(MID(C1,7,8),"0-00-00")
单元格格式需要设置为日期格式。
03 计算年龄
说明:计算日期为2020年4月20日
F2=DATEDIF(E2,TODAY(),"Y")
DATEDIF函数可以计算日期间隔,结束日期用today()函数,可以动态获取截止当天的年龄。
04 计算下次生日
说明:计算日期为2020年4月20日
G2=EDATE(E2,(F2 1)*12)
EDATE函数常用于合同到期日计算
下次生日计算思路是,从出生日期开始,经历虚岁(周岁 1)*12月数的日期。
05 计算下次生日天数
说明:计算日期为2020年4月20日
H2=G2-TODAY()
单元格格式设置为常规。
06 标记重复
使用条件格式,快速标记重复值。
07 统计重复次数
I2=COUNTIF(C:C,C2)
COUNTIF函数统计单一条件出现次数
COUNTIFS函数统计多条件出现次数
08 限制输入18位长度
使用数据验证,限制输入文本长度为18位。
不符合规则,禁止输入。
Excel办公教程
▼▼▼
购书赠送300分钟高清视频教程
详见图书封底说明
视频教程目录
,