智能身份证查询工具模板

chen210

智能身份证查询工具模板
听取“大理”版主建议，5月6日对部分公式简化重新打包上传。

[ 本帖最后由 chen210 于 2011-5-6 20:32 编辑 ]

guzhen9315

记号，学习学习。谢谢！

wshcw

楼主:
=IF(C4=0,"",IF(LEN(C4)=15,IF(MOD(MID(C4,15,1),2)=1,"男","女"),IF(MOD(MID(C4,17,1),2)=1,"男","女")))
=IF(C4=0,"",LOOKUP(TRUNC(MID(C4,1,6)),数据库!$B$2:$B$3525,数据库!$C$2:$C$3525))
=IF(C4=0,"",IF(LEN(C4)=15,CONCATENATE("19",MID(C4,7,2),"年",MID(C4,9,2),"月",MID(C4,11,2),"日"),CONCATENATE(MID(C4,7,4),"年",MID(C4,11,2),"月",MID(C4,13,2),"日")))
=IF(C4=0,"",IF(VALUE(MID(F4,6,2))>12,"生日错误",IF(VALUE(MID(F4,9,2))>31,"生日错误",YEAR(TODAY())-YEAR(F4))))
=IF(C4=0,"",IF(LEN(C4)=15,IF(VALUE(MID(C4,9,2))>1,CHOOSE(MOD(MID(C4,7,2),12)+1,"鼠","牛","虎","兔","龙","蛇","马","羊","猴","鸡","狗","猪"),IF(MOD(MID(C4,7,2),12)=0,"猪",CHOOSE(MOD(MID(C4,7,2),12),"鼠","牛","虎","兔","龙","蛇","马","羊","猴","鸡","狗","猪"))),IF(VALUE(MID(C4,11,2))>1,CHOOSE(MOD(ABS(MID(C4,7,4)+8),12)+1,"鼠","牛","虎","兔","龙","蛇","马","羊","猴","鸡","狗","猪"),IF(VALUE(MOD(MID(C4,7,4)+8,12))=0,"猪",CHOOSE(MOD(MID(C4,7,4)+8,12),"鼠","牛","虎","兔","龙","蛇","马","羊","猴","鸡","狗","猪")))))
=#VALUE!
=IF(LEN(C4)=15,"---",IF(C4=0,"",CHOOSE((MOD(SUM(MID(身份证信息查询!C4,1,1)*7,MID(身份证信息查询!C4,2,1)*9,MID(身份证信息查询!C4,3,1)*10,MID(身份证信息查询!C4,4,1)*5,MID(身份证信息查询!C4,5,1)*8,MID(身份证信息查询!C4,6,1)*4,MID(身份证信息查询!C4,7,1)*2,MID(身份证信息查询!C4,8,1),MID(身份证信息查询!C4,9,1)*6,MID(身份证信息查询!C4,10,1)*3,MID(身份证信息查询!C4,11,1)*7,MID(身份证信息查询!C4,12,1)*9,MID(身份证信息查询!C4,13,1)*10,MID(身份证信息查询!C4,14,1)*5,MID(身份证信息查询!C4,15,1)*8,MID(身份证信息查询!C4,16,1)*4,MID(身份证信息查询!C4,17,1)*2),11))+2,QQ2370915,1,0,"X",9,8,7,6,5,4,3,2)))
公式太烦琐,要加油啊!

[ 本帖最后由 wshcw 于 2011-5-3 23:46 编辑 ]

爱上你

都是高人 我看着头都晕

chen210

"大理"版主高人！

chen210

部分简化后，重新打包

gxq1219

ddddddddddddddddddd

aisiluobo

记号，学习学习。谢谢！

hydycmc

{:soso_e100:}学习学习

xgirlwqh

很经典的工具