|
01 计算年龄，年龄分布 如下表，是某员工入职时填写的身份证号，现需根据其提供的身份证号，来执行以下提取。 （1）提取出生日期 在D2中输入公式为：=TEXT(MID(C2,7,8),'0000-00-00')，之后按Enter键完成，再向下下拉填充即可。
注：MID函数用于截取某一个字符串中的内容，此处是截取身份证号的出生日期那部分；TEXT函数是使截取的那部分格式转为日期格式。 另外一种更简便的方法是，使用DATE函数，DATE函数共三个参数，分别是年，月，日。 因此公式可写为：=DATE(MID(C2,7,4),MID(C2,11,2),MID(C2,13,2)) （2）计算年龄 在E2中输入公式为：=DATEDIF(D2,TODAY(),'y')，之后按Enter键完成，再向下下拉填充即可。
注：DATEDIF函数用于计算两日期之间相隔的年数，月数，天数，TODAY函数用于返回当前日期，“y”为DATEDIF的第三个属性参数，用于返回两年份之间相隔的年数。 （3）计算年龄分布 按要求可以将年龄分为：18岁以下，18-29岁,30-49岁，40-49岁，50岁以上。按上述分布，来判断年龄处于哪个范围之间。 在F2中输入公式为：=VLOOKUP(E2,{0,'18岁以下';18,'18-29岁';30,'30-39岁';40,'40-49岁';50,'50岁以上'},2,1)，之后按Enter键完成，再向下下拉填充。
注：此处使用VLOOKUP函数的模糊查找属性（最后一个参数设为1时），来判断一个数字是否处于某一范围之内，当然也可使用LOOKUP函数。 02 计算性别 如下表，是某员工入职时提供的身份证号，现需根据其提供的身份证号，来判断该员工性别。 在D2中输入公式为：=IF(MOD(MID(C2,17,1),2),'男','女')，之后按Enter键完成，再向下下拉填充。
注：身份证号第17位，若是奇数，则为男，若偶数，则为女。先使用MID函数截取第17位数据，和MOD求取余数，再用IF函数判断。 当然，也可使用ISODD来判断是否为奇数。 公式为：=IF(ISODD(MID(C2,17,1)),'男','女')。 03 计算司龄、账龄 如下表，是某员工的入职时间和在公司预借款项的时间，计算出其司龄与账龄。 在D2单元中输入公式为： =DATEDIF(C2,TODAY(),'y')&'年'&DATEDIF(C2,TODAY(),'ym')&'个月'&DATEDIF(C2,TODAY(),'md')&'天'，之后按Enter键完成，再向下下拉填充即可。
注： DATEDIF函数用于返回两个日期相隔的时间。 “YM”参数会忽略相隔年数，仅返回月份数； “MD”忽略两日期之间的年数和月份数，仅返回天数。 若只是按月来计算，公式可写为： =DATEDIF(C2,TODAY(),'m')/12，但此时，不能精确到天。 04 计算生肖、星座 如下表，从身份证号中提取生肖。 在D2中输入公式为： =MID('猴鸡狗 猪鼠牛虎免龙蛇马羊',MOD(MID(C2,7,4),12)+1,1)，之然后按Enter键完成，再向下下拉填充。
注：首先使用MID函数从身份证号中提取出出生的年份，再使用MOD函数取余，按照规则计算出生肖。此处根据公历生日来计算，若为阴历生日，则会存在一定偏差。 在E2输入公式为： =LOOKUP(--MID(C2,11,4),{101,'摩羯';120,'水瓶';219,'双鱼';321,'白羊';420,'金牛';521,'双子';621,'巨蟹';723,'狮子';823,'处女';923,'天秤';1023,'天蝎';1122,'射手';1222,'摩羯'})&'座'，之后按Enter键完成，再向下下拉填充。
注：按照日期格式的不同，可灵活改变第一个参数。“--”表示将文本强制转为数值。 05 身份证号归属地查询 查询下述身份证号的归属地。
注：身份证号的前六位，代表省市省的相关地区信息。 |
|
|
