众所周知当前我们的身份证号共18位数,在我们职场工作当中,不知道大家有没有这样的情况,当我们需要做人事档案时,面对一串长长的身份证号码,经常需要提取更多有效的信息,若我们手动一个一个录入不仅费时费力,而且事倍功半。在此我们只需要利用Excel函数就可以自动化的从身份证号码当中判断出很多信息。例如用Excel函数判断性别,提取出生日期,判断年龄周岁,虚岁,以及判断生肖、籍贯和家庭地址等信息。让其我们在工作当中自动化的完成,大大提升我们的工作效率,让朋友、同事、领导对你刮目相看! 学习Excel函数首先我们必须要来聊聊Excel函数输入编辑时的规则: 1. 必须以等于号“=”开头 2. 每个函数必须要有一个完整的括号 3. 所有的标点符号必须要以英文状态的符号输入 4. 输入完整的公式按回车键Enter确定得出结果 5. 当公式引用的位置错误或在编辑时出现问题时按ESC取消退出,重新输入函数公式 一、通过身份证号自动判断性别 判断性别需要通过身份证号的第17位数来决定,当第17位数为奇数时表示的是男;当第17位数为偶数时表示的是女。首先需要通过MID函数截取身份证号第17位数,然后通过ISODD函数来判断截取的第17位数是否为奇数,最后通过IF来判断性别为男或女。 案例展示: 身份证号码为模拟数据 函数公式: =IF(ISODD(MID(B2,17,1)),"男","女") 函数说明:
中文解释:
二、通过身份证号自动提取出生日期 出生日期是我们身份证号中间的8位数,首先需要通过MID函数截取身份证号中间的8位数,然后通过TEXE函数将截取后的8位数转换为日期格式 案例展示: 身份证号码为模拟数据 函数公式: =TEXT(MID(B2,7,8),"0000-00-00") 函数说明:
中文解释:
三、通过身份证号自动计算年龄(虚岁和周岁) 计算虚岁:需要将当前年份值减去出生的年份值,首先通过MID函数截取身份证号码当中年份4位数,然后用今年减去出生年即可 计算周岁:需要将当前日期减去出生日期,首先通过MID函数截取身份证号码当中出生日期8位数,然后通过TEXE函数将截取后的8位数转换为日期格式,最后通过DATEDIF来解决返回两个日期相差的年数 计算虚岁 案例展示: 身份证号码为模拟数据 函数公式: =YEAR(TODAY())-MID(B2,7,4) 函数说明:
中文解释:
计算周岁 案例展示: 身份证号码为模拟数据 蒋舒清和陈成帆同一年,但出生日期不同根据当前日期计算,周岁就有相差 函数公式: =DATEDIF(TEXT(MID(B2,7,8),"0000-00-00"),TODAY(),"y") 函数说明:
中文解释:
四、通过身份证号自动提取生肖 生肖是通过出生的年份来进行决定的。首先通过MID函数截取身份证号码当中年份4位数,12个生肖是一个轮回,在此借助MOD函数与12进行相除,得到的余数在借用MID在12生肖中得到属相,但是提取的属相是不正确的,我们要熟知一个轮回的属相是由12个生肖来决定的,所以我们选择一个鼠年的年份进行相减,得到的结果要加上1(加上1的目的是包含当前的年份),最后会提取对应的生肖 案例展示: 身份证号码为模拟数据 函数公式: =MID("鼠牛虎兔龙蛇马羊猴鸡狗猪",MOD(MID(B2,7,4)-1984,12)+1,1) 函数说明:
中文解释:
五、通过身份证号自动提取籍贯 籍贯是我们身份证号码前2位数决定的。首先通过LEFT函数截取身份证号码前2位数,然后通过VLOOKUP函数在“行政代码”工作表中查找对应的行政区,匹配到数据表中从而提取对应的籍贯 案例展示: 身份证号码为模拟数据 函数公式: =VLOOKUP(LEFT(B2,2),行政代码!A:B,2,0) 函数说明:
中文解释:
六、通过身份证号自动提取家庭地址 家庭地址是我们身份证号码前6位数决定的。首先通过LEFT函数截取身份证号码前6位数,然后通过VLOOKUP函数在“行政代码”工作表中查找对应的行政区,匹配到数据表中从而提取对应的家庭地址 案例展示: 身份证号码为模拟数据 函数公式: =VLOOKUP(LEFT(B2,6),行政代码!A:B,2,0) 函数说明:
中文解释:
>>对您有帮助,别忘关注哦<< |
|