从身份证号中提取性别出生年月籍贯年龄最全功略

1、身份证号码的第17位是性别代码，单数为男，双数为女。

提取性别信息的函数书写格式是：=IF(MOD(MID(C2,17,1),2)=1,"男","女")

2、这是一个函数嵌套。由内向外共有三层嵌套。

第一层嵌套是Mid函数的使用。

Mid函数的作用是提取数据串中的部分数据，

语法结构是：MID(text, start_num, num_chars)，即Mid（提取数据的数据串，开始位置，提取数据串长度）

MID(C2,17,1)，C2是身份证号码所在单元格，性别代码是从第17位数字开始，长度为1。

3、第二层嵌套是Mod函数的使用。

Mod函数是求余数函数。

Mod函数的语法结构是：MOD(number,divisor)，即

Mod（被除数，除数）

MOD(MID(C2,17,1),2)，即把使用Mid函数提取出来的性别代码与2相除，得到余数。性别代码是双数时，余数为0，单数时，余数是1。

4、第三层嵌套是IF函数的使用。

If函数是Excel的判断函数，语法结构是：

IF(logical_test,value_if_true,value_if_false)

即IF（判断条件，真值返回，假值返回）

在该例中，IF(MOD(MID(C2,17,1),2)=1,"男","女")，意思是，判断Mod函数值是不是等1，如果等于1，说明性别代码是单数，返回“男”，否则返回“女”。

5、自动填充，完成其他人的性别填充。

1、身份证号码的前6位是一个人的籍贯信息，即省市县代码。

如下列列表

2、根据这样的数据，就可以从身份证号码中判断出一个人的籍贯。

函数书写格式：VLOOKUP(LEFT(C2,6),[身份证中的区域代码.xlsx]Sheet1!A$1:B$196,2)

3、这个运用既有函数的嵌套还引用了其他Excel工作簿中的数据。

Left函数，功能是从数据串左侧开始，截取一定数量的数据。

LEFT(C2,6)，即对C2单元格中的数据从左侧开始，截取6位。

在此例中，身份证左侧的6位数字是身份证中区域代码。

4、嵌套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列中的数据，精确匹配，默认省略。

5、拖动鼠标完成自动填充。

1、出生年月日在身份证中是第7位到第14位。

提取方法是：函数TEXT(MID(C2,7,8),"0000-00-00")*1

2、使用Mid函数从身份证号码中提取出年月日信息，即身份证号码的第7位到第14位。

3、提取出来的只是数据序列，还需要转换成年月日格式。

Text函数语法结构是：Text（数据，格式）即把“数据”按“格式”参数进行转换。

此例中函数书写为：TEXT(MID(C2,7,8),"0000-00-00")*1

把Mid函数提取出来的数据按“0000-00-00”的格式转换成年月日样式，转换后是文本类型数据，通过“*1”，转换成日期类型数据。

完成出生年月日的录入。

1、一个人的年龄就是现在的年月日减去出生年月日。

从身份证号码中计算出一个人的年龄，

使用的函数是： DATEDIF(F2,TODAY(),"Y")

2、Datedif是Excel的隐藏函数。

Datedif函数的语法结构是：DATEDIF(start_date,end_date,unit)，

功能是返回两个日期之间的间隔数。

UNIT参数是“Y”、”M”、“D”等，分别对应年、月、日。

此例UNIt参数选择”Y”，返回两个日期之间的间隔年数，取整数年。

Today()函数返回现在的日期。

3、至此，通过几组嵌套函数，把身份证号码中的性别、籍贯、出生年月日和年龄提取了出来，对于填写人员统计表起到了事半功倍的效果。