EXCEL中-应用公式-提取身份证当中所包含的信息

姓名身份证号码提取性别提取出生日期提取现在年龄提取生肖(从出身日期中提取)身份证号码是否正确提取所属省份(从地址码提取)提取所属地区(从地址码提取)张三110221650815224女1965/8/1555蛇TRUE北京市(市辖区)昌平李四510221197412010219男1974/12/145虎TRUE四川省重庆市王五132426590620123男1959/6/2061猪TRUE河北省保定地区完县周六140121700228420女1970/2/2850狗TRUE山西省太原市清徐县钱七350583197810120072男1978/10/1242马TRUE福建省泉州市南安市彭九511801520925520女1952/9/2568龙TRUE四川省雅安市市辖区刘二620123790513150女1979/5/1341羊TRUE甘肃省兰州市榆中县说明如下:以B2单元格为例提取生日1965/8/15此为公式中第1种方法的结果公式1=DATE(MID(B2,7,IF(LEN(B2)=18,4,2)),MID(B2,IF(LEN(B2)=18,11,9),2),MID(B2,IF(LEN(B2)=18,13,11),2))2=--TEXT(MID(B2,7,6+(LEN(B2)=18)*2),0-00-00)3=TEXT(RIGHT(TEXT((0&MID(B2,7,11))-500,1900-00-00,;!0),10),e-mm-dd;;;错误)4=TEXT(TEXT((0&MID(B2,7,11))-500,00-00-00,;!0),e-mm-dd;;;错误)5=TEXT(MID(REPLACE(B2,7,,IF(LEN(B2)=15,19,)),7,8),0000-00-00)6=TEXT(REPLACE(MID(B2,7,6+(LEN(B2)=18)*2),1,,IF(LEN(MID(B2,7,6+(LEN(B2)=18)*2))=6,19,)),0000-00-00)7=TEXT(TEXT(MID(B2,7,6+(LEN(B2)=18)*2),0-00-00),emmdd)8=IF(LEN(B2)=15,19&MID(B2,7,6),MID(B2,7,8))9=IF(LEN(B2)=15,19,)&MID(B2,7,6+(LEN(B2)=18)*2)提取性别女此为公式中第1种方法的结果公式1=IF(MOD(IF(LEN(B2)=15,MID(B2,15,1),MID(B2,17,1)),2)=1,男,女)2=IF(MOD(RIGHT(LEFT(B2,17)),2),男,女)提取生肖蛇此实例引用的是出生日期公式=MID(鼠牛虎兔龙蛇马羊猴鸡狗猪,MOD(YEAR(D2)-4,12)+1,1)15位改18位110221196508152247注意此公式是数组。输入后请按Shift+Ctrl+Enter输入，否则无效！此实例引用的单元格为B2公式=IF(LEN(B2)=15,REPLACE(B2,7,,19)&MID(10X98765432,MOD(SUM(MID(REPLACE(B2,7,,19),ROW(INDIRECT(1:17)),1)*2^(18-ROW(INDIRECT(1:17)))),11)+1,1),B2)提取现在年龄1900/2/24公式=IF(B2,DATEDIF(TEXT((LEN(B2)=15)*19&MID(B2,7,6+(LEN(B2)=18)*2),#-00-00),TODAY(),y),)号码正确否TRUE注意此公式是数组。输入后请按Shift+Ctrl+Enter输入，否则无效！公式=OR(LEN(B2)=15,IF(LEN(B2)=18,MID(10X98765432,MOD(SUM(MID(B2,ROW(INDIRECT(1:17)),1)*2^(18-ROW(INDIRECT(1:17)))),11)+1,1)=RIGHT(B2)))设置身份证输入单元格有效性（只能输入15位和18位数的正确的号码）公式在数据有效性里选择允许自定义在公式里输入=OR(LEN(*)=15,IF(LEN(*)=18,MID(10X98765432,MOD(SUM(MID(*,ROW(INDIRECT(1:17)),1)*2^(18-ROW(INDIRECT(1:17)))),11)+1,1)=RIGHT(*)))提取所属省份北京市地址码指所引用的地址区域公式=IF(B2,VLOOKUP(LEFT(B2,2),地址码!B:C,2,),)提取所属地区(市辖区)昌平地址码指所引用的地址区域公式=IF(B2,VLOOKUP(LEFT(B2,6),地址码!B:C,2,),)提取星座獅子座Leo此实例引用的是出生日期注意此公式是数组。输入后请按Shift+Ctrl+Enter输入，否则无效！此实例引用的单元格为B2公式=LOOKUP(--TEXT($D2,m.dd),{0,魔羯座Capricorn;1.2,水瓶座Aquarius;2.19,雙魚座Pisces;3.21,牡羊座Aries;4.2,金牛座Taurus;5.21,雙子座Gemini;6.22,巨蟹座Cancer;7.23,獅子座Leo;8.23,處女座Virgo;9.23,天秤座Libra;10.24,天蠍座Scorpio;11.23,射手座Sagittarius;12.22,魔羯座Capricorn})提取幸运日星期三此实例引用的是出生日期公式=LOOKUP(--TEXT($D2,m.dd),{0,星期五;1.2,星期六;2.19,星期二;3.21,星期二;4.2,星期天;5.21,星期三;6.22,星期四;7.23,星期三;8.23,星期五;9.23,星期四;10.24,星期五;11.23,星期一;12.22,星期五})提取星座(从出身日期中提取)提取幸运日(从出身日期中提取)身份证号码十五位改十八位獅子座Leo星期三110221196508152247射手座Sagittarius星期一510221197412010219雙子座Gemini星期三132426195906201239雙魚座Pisces星期二140121197002284204天秤座Libra星期四350583197810120072天秤座Libra星期四511801195209255201金牛座Taurus星期天620123197905131505=DATE(MID(B2,7,IF(LEN(B2)=18,4,2)),MID(B2,IF(LEN(B2)=18,11,9),2),MID(B2,IF(LEN(B2)=18,13,11),2))=TEXT(RIGHT(TEXT((0&MID(B2,7,11))-500,1900-00-00,;!0),10),e-mm-dd;;;错误)=TEXT(TEXT((0&MID(B2,7,11))-500,00-00-00,;!0),e-mm-dd;;;错误)=TEXT(REPLACE(MID(B2,7,6+(LEN(B2)=18)*2),1,,IF(LEN(MID(B2,7,6+(LEN(B2)=18)*2))=6,19,)),0000-00-00)=IF(MOD(IF(LEN(B2)=15,MID(B2,15,1),MID(B2,17,1)),2)=1,男,女)注意此公式是数组。输入后请按Shift+Ctrl+Enter输入，否则无效！此实例引用的单元格为B2=IF(LEN(B2)=15,REPLACE(B2,7,,19)&MID(10X98765432,MOD(SUM(MID(REPLACE(B2,7,,19),ROW(INDIRECT(1:17)),1)*2^(18-ROW(INDIRECT(1:17)))),11)+1,1),B2)=IF(B2,DATEDIF(TEXT((LEN(B2)=15)*19&MID(B2,7,6+(LEN(B2)=18)*2),#-00-00),TODAY(),y),)注意此公式是数组。输入后请按Shift+Ctrl+Enter输入，否则无效！=OR(LEN(B2)=15,IF(LEN(B2)=18,MID(10X98765432,MOD(SUM(MID(B2,ROW(INDIRECT(1:17)),1)*2^(18-ROW(INDIRECT(1:17)))),11)+1,1)=RIGHT(B2)))在数据有效性里选择允许自定义在公式里输入=OR(LEN(*)=15,IF(LEN(*)=18,MID(10X98765432,MOD(SUM(MID(*,ROW(INDIRECT(1:17)),1)*2^(18-ROW(INDIRECT(1:17)))),11)+1,1)=RIGHT(*)))注意此公式是数组。输入后请按Shift+Ctrl+Enter输入，否则无效！此实例引用的单元格为B2=LOOKUP(--TEXT($D2,m.dd),{0,魔羯座Capricorn;1.2,水瓶座Aquarius;2.19,雙魚座Pisces;3.21,牡羊座Aries;4.2,金牛座Taurus;5.21,雙子座Gemini;6.22,巨蟹座Cancer;7.23,獅子座Leo;8.23,處女座Virgo;9.23,天秤座Libra;10.24,天蠍座Scorpio;11.23,射手座Sagittarius;12.22,魔羯座Capricorn})=LOOKUP(--TEXT($D2,m.dd),{0,星期五;1.2,星期六;2.19,星期二;3.21,星期二;4.2,星期天;5.21,星期三;6.22,星期四;7.23,星期三;8.23,星期五;9.23,星期四;10.24,星期五;11.23,星期一;12.22,星期五})在数据有效性里选择允许自定义在公式里输入=OR(LEN(*)=15,IF(LEN(*)=18,MID(10X98765432,MOD(SUM(MID(*,ROW(INDIRECT(1:17)),1)*2^(18-ROW(INDIRECT(1:17)))),11)+1,1)=RIGHT(*)))=LOOKUP(--TEXT($D2,m.dd),{0,魔羯座Capricorn;1.2,水瓶座Aquarius;2.19,雙魚座Pisces;3.21,牡羊座Aries;4.2,金牛座Taurus;5.21,雙子座Gemini;6.22,巨蟹座Cancer;7.23,獅子座Leo;8.23,處女座Virgo;9.23,天秤座Libra;10.24,天蠍座Scorpio;11.23,射手座Sagittarius;12.22,魔羯座Capricorn})=LOOKUP(--TEXT($D2,m.dd),{0,星期五;1.2,星期六;2.19,星期二;3.21,星期二;4.2,星期天;5.21,星期三;6.22,星期四;7.23,星期三;8.23,星期五;9.23,星期四;10.24,星期五;11.23,星期一;12.22,星期五})=LOOKUP(--TEXT($D2,m.dd),{0,魔羯座Capricorn;1.2,水瓶座Aquarius;2.19,雙魚座Pisces;3.21,牡羊座Aries;4.2,金牛座Taurus;5.21,雙子座Gemini;6.22,巨蟹座Cancer;7.23,獅子座Leo;8.23,處女座Virgo;9.23,天秤座Libra;10.24,天蠍座Scorpio;11.23,射手座Sagittarius;12.22,魔羯座Capricorn})编码所在地001102(县)001201(市辖区)001202(县)001301石家庄市001302唐山市001303秦皇岛市001304邯郸市001305邢台市001306保定市001307张家口市001308承德市001309沧州市001310廊坊市001311衡水市001321邯郸地区001322邢台地区0