Excel 函数应用: 12种函数判断身份证号码性别 方法/步骤 1 数据源 A1到A4单元格输入身份证号码,15位和18位都可以。 2 提取性别码 15位为最后一位数,18位为第17位数 3 IF函数,在B1他单元格里输入=IF(ISODD(RIGHT(LEFT(A1,17),1)),"男","女") 4 RIGHT&LEFT函数,在C1单元格里输入=RIGHT("女男",MOD(RIGHT(LEFT(A1,17),1),2))&LEFT("女男",MOD(RIGHT(LEFT(A1,17),1)+1,2)) 5 MID函数,在D1单元格里输入=MID("女男",MOD(RIGHT(LEFT(A1,17),1),2)+1,1) 6 IFS函数,在E1单元格里输入=IFS(ISEVEN(RIGHT(LEFT(A1,17),1)),"女",TRUE,"男") 7 TEXT函数,在F1单元格里输入=TEXT(--ISEVEN(RIGHT(LEFT(A1,17),1)),"女;;男") 8 SWITCH函数,在H1单元格里输入=SWITCH(MOD(RIGHT(LEFT(A1,17),1),2),1,"男",0,"女") 9 CHOOSE函数,在I1单元格里输入=CHOOSE(MOD(RIGHT(LEFT(A1,17),1),2)+1,"女"," 男") 10 VLOOKUP函数,在G1单元格里输入=VLOOKUP(MOD(RIGHT(LEFT(A1,17),1),2),{0,"女";1,"男"},2) 11 HLOOKUP函数,在K1单元格里输入=HLOOKUP(MOD(RIGHT(LEFT(A1,17),1),2),{0,1;"女","男"},2) 12 LOOKUP函数,在L1单元格里输入=LOOKUP(MOD(RIGHT(LEFT(A1,17),1),2),{0,1},{"女","男"}) 13 INDEX函数,在M1单元格里输入=INDEX({"女";"男"},MOD(RIGHT(LEFT(A78,17),1),2)+1,1) 14 此外,还可以使用OFSSET函数,由于需要添加辅助列,这里不列出! 15和18位号码 身份证号码要正确 |
|