Excel和身份证不得不说的事儿

EXCEL和身份证不得不说的那些事儿内容提要：

使用Excel从身份证号码中读取人员相关信息类型和格式一代：15位二代：18位112044541220243112044195412202431省份市县出生日期校验性别二代比一代多两位年份数字和最后一位校验码正确输入单元格只能保留15位有效数于Excel单元格只能保留15位有效数字，在直接输入18位身份证时最后三位会自动变成0112044195412202000112044195412202435确输入方法是在数字前加上一个单引号，或是事先将单元格格式设置为【文本】自动验证输入通过【数据有效性】验证输入的大致正确性（不考虑具体内容的正确性）输入位数为15位或18位1条件=OR(LEN(A1)=15,LEN(A1)=18)如果不全都是数字，那就只能是18位且末尾是字母X3条件=OR(ISNUMBER(-A1),AND(LEN(A1)=18,RIGHT(A1)="X"))=AND(OR(LEN(A1)=15,LEN(A1)=18),ISNUMBER(-LEFT(A1,17)),OR(ISNUMBER(-A1),AND(LEN(A1)=18,RIGHT(A1)="X")))1条件AND2条件前17位均为数字2条件=ISNUMBER(-LEFT(A1,17))AND3条件注：根据设置的单元格不同，公式中的A1也需相应变更提取生日将4位或6位出生日期数字转换为真实日期值一代身份证的年份均为19XX年如果是

15位身份证，数字前补充完整年份

19..（否则小于30时会被转成20XX年）=MID(A1,7,IF(LEN(A1)=15,6,8))如果是

位身份证，取其第7位开始的

位数字15186812=RIGHT(19&MID(A1,7,IF(LEN(A1)=15,6,8)),8)8位数字转换为真实日期值3=TEXT(RIGHT(19&MID(A1,7,IF(LEN(A1)=15,6,8)),8),"0-00-00")+0提取生日公式使用效果15412201954122019541220195412201954-12-201954-12-2023=MID(A1,7,IF(LEN(A1)=15,6,8))=RIGHT(19&MID(A1,7,IF(LEN(A1)=15,6,8)),8)=TEXT(RIGHT(19&MID(A1,7,IF(LEN(A1)=15,6,8)),8),"0-00-00")+0注：如果公式3得到结果是5位数值，在单元格格式中设置为【日期】就可以看到真实日期计算年龄在提取生日的基础上计算年龄=DATEDIF(TEXT(RIGHT(19&MID(A1,7,IF(LEN(A1)=15,6,8)),8),"0-00-00"),NOW(),"Y")根据前面公式计算得到出生日期，然后计算年龄5757判别性别通过标识性别数字的奇偶性来判别=IF(MOD(MID(A1,15,3),2),

"男","女")如果是

位身份证，判断其第

位数字的奇偶性，数是性151815171奇偶男女除以2取余数或通过负数的平方来判断奇偶性2提取这位数字，也可以取以这位数字为结尾多位数字（不要取太多位数）=MID(A1,15,3)或=RIGHT(LEFT(A1,17))=IF(-1^MID(A1,15,3)=1,"女","男")或判别性别公式使用效果13243男男2=MID(A1,15,3)=IF(MOD(MID(A1,15,3),2),

"男","女")获取所属地通过前2位可以查表取得所属省份信息11 北京市12 天津市13 河北省14 山西省15 内蒙古自治区21 辽宁省22 吉林省23 黑龙江省31 上海市32 江苏省33 浙江省34 安徽省35 福建省36 江西省37 山东省41 河南省42 湖北省通过前6位可以查表取得详细区县信息详细对应表：http:///s/gnJoe43 湖南省44 广东省45 广西壮族自治区46 海南省50 重庆市51 四川省52 贵州省53 云南省54 西藏自治区61 陕西省62 甘肃省63 青海省64 宁夏回族自治区65 新疆维吾尔自治区71 台湾省81 香港特别行政区82 澳门特别行政区验证有效性二代的最后一位校验位可以验证号码有效性校验位的字符可以通过前17位号码计算取得校验算法如下1716151413121110987654321112044195412202437910584216379105842792003216293012718200161660位数i前17位号码Mod(2^i,11)行2×行3Mod(行4求和,11)01234567891010X98765432查表计算值校验码校验码公式根据前17位计算校验码：一代号码升级为二代号码：=MID("10X98765432",1+MOD(SUMPRODUCT(MID(LEFT(A1,17),ROW($1:$17),1)*MOD(2^(18-ROW($1:$17)),11)),11),1)=REPLACE(A1,7,,19)&MID("10X98765432",1+MOD(SUMPRODUCT(MID(REPLACE(A1,7,,19),ROW($1:$17),1)*MOD(2^(18-ROW($1:$17)),11)),11),1)11204419541220243?1112044541220243112044195412202431可以通过公式验证校验位是否正确也可以通过公式将15位号码升级为18位新变化2013年1月1日起将停止使用一代身份证未来只有一种18位号码的情况下，各种应用都会变得更简单自动验证输入=AND(LEN(A1)=18,ISNUMBER(-LEFT(A1,17)),OR(ISNU