excel身份证号提取性别(直接复制公式即可)

打开EXCEL表格,在身份证号码右侧单元格内输入函数IF和MOD的组合公式,按下回车键后提取出性别;1、首先在打开的表格中,选中需要的单元格;2、输入函数:=IF(MOD(MID(B2,3,1),2),男,女),其中奇数代表男性,偶数代表女性;3、然后点击【勾】图标;在单元格的右下角双击绿色小正方形;4、这时就可以看到,excel男女性别公式if操作完成。

标黄的为我目前在用的公式,要用的话可以直接复制粘贴。

第一,提取性别

我们每个人18位身份证号码中第17位是判断性别的数字,奇数代表男性,偶数代表女性。首先,我们还是用MID函数将第17位数字提取出来,然后利用MOD函数判断奇偶(能否被2整除),再用if函数判断男女。

=IF(MOD(MID(P24,17,1),2),"男","女")

如图

第二,提取出生日期

身份证号码中第7-14位数字即为出生年月日,日期需要利用DATE函数。当然您还可以不用函数,用分列或者快速填充功能来提取。

如果是用公式,可以参考下面两个公式:

=DATE(MID(P24,7,4),MID(P24,11,2),MID(P24,13,2))

=TEXT(MID(P24,7,8),"00-00-00")

如图

第三,提取年龄

当前年份减去出生年份即为我们每个人的年龄。

=DATEDIF(P24,TODAY(),"y")

=YEAR(TODAY())-MID(P24,7,4)

如图

第四,提取籍贯

18位身份证号码中,前6位是表示地址信息的,其中1-2位代表省,3-4位代表地(市),5-6位代表县(市)。

最好的方法就是找到原始籍贯信息匹配表,通过vlookup函数进行匹配。

这里,小星提供一种直接可以匹配到省的方法,将代表身份的前两位数字提取出来,然后使用lookup进行匹配,代表身份的数字与身份用数组公式表示。公式如下:

=LOOKUP(VALUE(LEFT(P24,2)),{11,"北京市";12,"天津市";13,"河北省";14,"山西省";15,"内蒙古";21,"辽宁省";22,"吉林省";23,"黑龙江省";31,"上海市";32,"江苏省";33,"浙江省";34,"安徽省";35,"福建省";36,"江西省";37,"山东省";41,"河南省";42,"湖北省";43,"湖南省";44,"广东省";45,"广西";46,"海南省";50,"重庆市";51,"四川省";52,"贵州省";53,"云南省";54,"西藏";61,"陕西省";62,"甘肃省";63,"青海省";64,"宁夏";65,"新疆";71,"台湾省";81,"香港";82,"澳门";"","0"})

如图

第五,提取星座

星座与出生月和日相关,我们如果不借助其他表来查询,也可以用lookup与数组函数的结合。公式:

=LOOKUP(--MID(P24,11,4),{100;120;219;321;421;521;622;723;823;923;1023;1122;1222},{"摩羯座";"水瓶座";"双鱼座";"白羊座";"金牛座";"双子座";"巨蟹座";"狮子座";"处女座";"天秤座";"天蝎座";"射手座";"摩羯座"})

第六,提取生肖属相

生肖属相与出生年份相关,所以提取生肖需要先提取在哪一年出生的。我们可以使用CHOOSE函数,公式为:

=CHOOSE(MOD(MID(P24,7,4)-2008,12)+1,"鼠","牛","虎","兔","龙","蛇","马","羊","猴","鸡","狗","猪")

如图

第七,根据入职时间计算司龄

司龄与入职时间相关,所以计算司龄可以使用DATEDIF函数,公式为:

=DATEDIF(F24,TODAY(),"Y")&"年"

相关内容