计算机
Excel2016
身份证号码的第17位是性别代码,单数为男,双数为女。提取性别信息的函数书写格式是:=IF(MOD(MID(C2,17,1),2)=1,'男','女')
这是一个函数嵌套。由内向外共有三层嵌套。第一层嵌套是Mid函数的使用。Mid函数的作用是提取数据串中的部分数据,语法结构是:MID(text, start_num, num_chars),即Mid(提取数据的数据串,开始位置,提取数据串长度)MID(C2,17,1),C2是身份证号码所在单元格,性别代码是从第17位数字开始,长度为1。
第二层嵌套是Mod函数的使用。Mod函数是求余数函数。Mod函数的语法结构是:MOD(number,divisor),即Mod(被除数,除数)MOD(MID(C2,17,1),2),即把使用Mid函数提取出来的性别代码与2相除,得到余数。性别代码是双数时,余数为0,单数时,余数是1。
第三层嵌套是IF函数的使用。If函数是Excel的判断函数,语法结构是:IF(logical_test,value_if_true,value_if_false)即IF(判断条件,真值返回,假值返回)在该例中,IF(MOD(MID(C2,17,1),2)=1,'男','女'),意思是,判断Mod函数值是不是等1,如果等于1,说明性别代码是单数,返回“男”,否则返回“女”。
自动填充,完成其他人的性别填充。
身份证号码的前6位是一个人的籍贯信息,即省市县代码。如下列列表
根据这样的数据,就可以从身份证号码中判断出一个人的籍贯。函数书写格式:VLOOKUP(LEFT(C2,6),[身份证中的区域代码.xlsx]Sheet1!A$1:B$196,2)
这个运用既有函数的嵌套还引用了其他Excel工作簿中的数据。Left函数,功能是从数据串左侧开始,截取一定数量的数据。LEFT(C2,6),即对C2单元格中的数据从左侧开始,截取6位。在此例中,身份证左侧的6位数字是身份证中区域代码。
嵌套Vlookup函数。Vlookup函数的语法结构是:VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)即Vlookup(查找数据,数据区域,返回值所在列数,匹配类型(精确匹配/模糊匹配)VLOOKUP(LEFT(C2,6),[身份证中的区域代码.xlsx]Sheet1!A$1:B$196,2)使用Vlookup函数,查找Left(C2,6)提取出来的区域代码在“身份证中的区域代码.xlsx”工作簿中区域代码所对应的地区区域,即第2列中的数据,精确匹配,默认省略。
拖动鼠标完成自动填充。
出生年月日在身份证中是第7位到第14位。提取方法是:函数TEXT(MID(C2,7,8),'0000-00-00')*1
使用Mid函数从身份证号码中提取出年月日信息,即身份证号码的第7位到第14位。
提取出来的只是数据序列,还需要转换成年月日格式。Text函数语法结构是:Text(数据,格式)即把“数据”按“格式”参数进行转换。此例中函数书写为:TEXT(MID(C2,7,8),'0000-00-00')*1把Mid函数提取出来的数据按“0000-00-00”的格式转换成年月日样式,转换后是文本类型数据,通过“*1”,转换成日期类型数据。完成出生年月日的录入。
一个人的年龄就是现在的年月日减去出生年月日。从身份证号码中计算出一个人的年龄,使用的函数是: DATEDIF(F2,TODAY(),'Y')
Datedif是Excel的隐藏函数。Datedif函数的语法结构是:DATEDIF(start_date,end_date,unit),功能是返回两个日期之间的间隔数。UNIT参数是“Y”、”M”、“D”等,分别对应年、月、日。此例UNIt参数选择”Y”,返回两个日期之间的间隔年数,取整数年。Today()函数返回现在的日期。
至此,通过几组嵌套函数,把身份证号码中的性别、籍贯、出生年月日和年龄提取了出来,对于填写人员统计表起到了事半功倍的效果。
本文是小编日常工作的经验总结,如果帮到了您,请为我投上一票。谢谢!