| 楼主 gvntw |
2005年3月写的，现转到此版。 身份证号码的相关应用： 利用有效性限制录入身份证号码（限制15或18位，18位计算验证码正确与否）；提取出生日期；取得退休日期；15位与18位号码的互换；性别判断；提取号码所属省、市、区。  身份证号码集锦.zip |
| 2楼 wshcw |
有时间还是请版主再改改,传最新、最简短的公式的上来. |
| 3楼 五〇高手 |
非常感谢gvntw版主的分享，版主日理万机，还能给我们写这么实用的东西，真好！ wshcw先生，您如果有好的公式，也直接贴出来分享给大家看看，可以吗？ |
| 4楼 gvntw |
最近较忙，所以没进行改进。 我想公式也应尽量具有可读性，易于理解为主，先改进一下出生日期吧。 出生日期： =IF(A2="","",--TEXT(RIGHT(19&MID(A2,7,LEN(A2)/2-1),8),"0-00-00")) 年龄： =IF(A2<>"",DATEDIF(TEXT(RIGHT(19&MID(A2,7,LEN(A2)/2-1),8),"0-00-00"),TODAY(),"y"),) 退休日期： =IF(A2<>"",EDATE(TEXT(RIGHT(19&MID(A2,7,LEN(A2)/2-1),8),"0-00-00"),660+MOD(RIGHT(LEFT(A2,17)),2)*60),) 对于身份证号码的限制，以后我还会再改进，不仅仅是限制15或18位和验证码，还包括出生日期必须是有效日期，除验证码外，其他字符必须都是数字等，这将在我下一本书中进行介绍。 |
| 5楼 wshcw |
写了一个提取日期的,不妥之处请指正. =--RIGHT(TEXT((0&MID(A2,7,11))-500,"1900-00-00,;!0"),10) 单元格格式: yyyy-m-d;; |
| 6楼 gouweicao78 |
=--TEXT(INT(10*(0&MID(A2,7,11))%%),"#-00-00;;0")——48字 =--TEXT(INT((0&MID(A2,7,11))/1000),"#-00-00;;0")——48字 =--TEXT(TRUNC(0&MID(A2,7,11),-3),"#-00-00,;;0")——47字 =--TEXT((0&MID(A2,7,11))-500,"#-00-00,;!0")——43字 缺点：15位身份证年份为01~29时，默认为2001~2029，年份为30~99默认为1930~1999。不过一般来说，目前身份证大多用18位且1929年以前的身份证号码估计很少用到。 公式的长短，我倾向于gvntw兄的“便于理解”说法。公式的研究，则佩服wshcw兄的钻研精神以及思维的独到之处。 |
| 7楼 wshcw |
这是在其它社区中发表过的,但没考虑空值问题: =--TEXT(INT(MID(A1,7,11)/1000),"[<1E6]1900-00-00;#-00-00") =--MID(TEXT((19&MID(A1,7,11))-500,"000000-00-00,"),3,10) =--TEXT(MID(A1,7,11)-500,"[<1E6]19#-00-00,;#-00-00,") =--TEXT(MID(A1,7,11)-500,"[<1E9]1900-00-00,;#-00-00,") =--RIGHT(TEXT(MID(A1,7,11)-500,"1900-00-00,"),10) |
| 8楼 wshcw |
还有几个老公式,已是前个月(今年10月份)发表的了,问题多多请指正: 55 =--RIGHT(TEXT((19&MID(A1,7,LEN(A1)/2-1)),"#-00-00"),10) 57 =MOD(TEXT(MID(A1,LEN(A1)/2,6),"0-00-00")-3236,36525)+3236 58 =--TEXT(MOD((19&MID(A1,7,LEN(A1)/2-1)),19*10^8),"#-00-00") 69 =MOD(TEXT(MID(A1,LEN(A1)/2,6),"0-00-00")-TODAY(),36525)+TODAY()-36525 以下公式不能处理2000年后的问题 49 =MOD(TEXT(MID(A1,7,LEN(A1)/2-1),"#-00-00"),36525) 47 =MOD(TEXT(MID(A1,LEN(A1)/2,6),"0-00-00"),36525) |
| 9楼 gvntw |
wshcw兄的水平和研究精神令人佩服！ 不过，我用LEN(A1)/2-1已经有点取巧了，利用了15与18位号码相差的位数及MID函数参数中，带小数点的处理方法进行了取巧，已经得花点时间解释了。 wshcw兄的“-500”及格式中的“1900-00-00,”，都是需要解释的，如“-500”涉及的四舍五入，“1900”中的“19”，利用后面的“,”号缩小1000倍等都需要详细解释，没有一定水平的用户还是比较难以解释清楚的。 虽然省下了3个字符，但得花比较多的时间解释，有点得不偿失哦。 记得在EH时，我写过一个不重复值的公式，当时是省下了Match()函数的0，完整的写法：Match(0,Countif(),0)，省下后的写法：Match(,Countif(),)，当时也费了点时间解释为什么只有逗号，没有参数。不过这个还比较容易解释，呵呵。 再次谢谢wshcw兄精简的公式及敏捷的思路！ |
| 10楼 wshcw |
惭愧,MID(A2,7,11))的用法,并非是我的首创,我也只是引用改进,36525做除数求余的用法是我首创,但不太好. |
| 11楼 wshcw |
现在刚刚写了一个提取性别的公式,觉得这个公式不但简化4个字符,公式形体也更美些,考虑空值也不再使用""了呵呵. =TEXT(MOD((0&MID(A1,15,3)),2+1%%)-1,"女;;男") |
| 12楼 gvntw |
呵呵，wshcw兄看看附件吧：Try.zip |
| 13楼 wshcw |
确实是测试不够,失误了.公式中为零部分不好控制. |
| 14楼 wshcw |
这下可简化字符了. 43字 =TEXT(MOD(0&MID(A2,15,3),2)-(A2=""),"男;;女") 42字 =TEXT(ISODD(0&MID(A2,9,9))-(A2=""),"男;;女")  wshcw修改结果.rar |
| 15楼 gvntw |
-(A2="")：用得好！只是得费点时间解释了，呵呵。 |
| 16楼 linmuquan0410 |
我学习的比较简单，在高手面前不值得一提 |
| 17楼 w85511931 |
看看，学习！！ |
| 18楼 bbwsj |
用TEXT取生日的公式MS有半年多了吧，最初在EP看x.f.zhao用的 ，后来看他不断精简 |
| 19楼 linmuquan0410 |
初来乍到，不过还是请教wshcw老师一问题。什么是36525做除数求余的用法？ |
| 20楼 wshcw |
36525=1999-12-31 |
| 21楼 linmuquan0410 |
原来如此，又长知识了，谢谢 |
| 22楼 冰风萧萧 |
学习中～～～ |
| 23楼 洋五月 |
版主们晒公式和技巧，赶紧学习 |
| 24楼 jianjian624 |
谢谢,收下了 |
| 25楼 jackeroo |
请问，附件中15位转18位时，与身份证的规则不符合，但算出来的是正确的，能解释一下吗？ 下面是规则。 2007-11-07 14:20 15位的身份证号 dddddd yymmdd xx p 18位的身份证号 dddddd yyyymmdd xx p y 其中dddddd为地址码（省地县三级）18位中的和15位中的不完全相同 yyyymmdd yymmdd 为出生年月日 xx顺号类编码 p性别 18位中末尾的y为校验码，在网上可以找到算法 将前17位的ascii码值经位移、异或运算结果不在0-9的令其为x 现将“18位身份证标准”提供如下： 18位身份证标准在国家质量技术监督局于1999年7月1日实施 的GB11643-1999《公民身份号码》中做了明确的规定。 GB11643-1999《公民身份号码》为GB11643-1989《社会保障 号码》的修订版，其中指出将原标准名称“社会保障号码”更名 为“公民身份号码”，另外GB11643-1999《公民身份号码》从实 施之日起代替GB11643-1989。 GB11643-1999《公民身份号码》主要内容如下： 一、范围 该标准规定了公民身份号码的编码对象、号码的结构和表现 形式，使每个编码对象获得一个唯一的、不变的法定号码。 二、编码对象 公民身份号码的编码对象是具有中华人民共和国国籍的公民。 三、号码的结构和表示形式 1、号码的结构 公民身份号码是特征组合码，由十七位数字本体码和一位校 验码组成。排列顺序从左至右依次为：六位数字地址码，八位数 字出生日期码，三位数字顺序码和一位数字校验码。 2、地址码 表示编码对象常住户口所在县(市、旗、区)的行政区划代码， 按GB/T2260的规定执行。 3、出生日期码 表示编码对象出生的年、月、日，按GB/T7408的规定执行， 年、月、日代码之间不用分隔符。 4、顺序码 表示在同一地址码所标识的区域范围内，对同年、同月、同 日出生的人编定的顺序号，顺序码的奇数分配给男性，偶数分配 给女性。 5、校验码 (1)十七位数字本体码加权求和公式 S = Ai * Wi, i = 2, ... , 18 Y = mod(S, 11) i: 表示号码字符从右至左包括校验码字符在内的位置序号 Ai:表示第i位置上的身份证号码字符值 Wi:表示第i位置上的加权因子 i: 18 17 16 15 14 13 12 11 10 9 8 7 6 5 4 3 2 1 Wi: 7 9 10 5 8 4 2 1 6 3 7 9 10 5 8 4 2 1 (2)校验码字符值的计算 Y: 0 1 2 3 4 5 6 7 8 9 10 校验码: 1 0 X 9 8 7 6 5 4 3 2 四、举例如下： 北京市朝阳区: 11010519491231002X 广东省汕头市: 440524188001010014 15位的不需要研究了，很简单的： dddddd yymmdd xx p dddddd:地区码 yymmdd:出生年月日 xx:顺号类编码，无法确定 p:性别，男的奇数女的偶数 18位的： dddddd yyyymmdd xxx y dddddd:地区码 yyyymmdd:出身年月日，为了应付千年虫问题，年升成4位数 xxx:顺号类编码，无法确定。但是奇数分配给男性，偶数分配给女性 y:校验码，最麻烦的地方 校验码的计算方法： 假如某身份号码34052419800101001，首先按照公式⑴计算： ∑(ai×Wi)(mod 11)……………………………………(1) 公式(1)中： i----表示号码字符从由至左包括校验码在内的位置序号； ai----表示第i位置上的号码字符值； Wi----示第i位置上的加权因子 i 18 17 16 15 14 13 12 11 10 9 8 7 6 5 4 3 2 1 ai 3 4 0 5 2 4 1 9 8 0 0 1 0 1 0 0 1 a1 Wi 7 9 10 5 8 4 2 1 6 3 7 9 10 5 8 4 2 1 (加权因子,固定的数值) ai×Wi 21 36 0 25 16 16 2 9 48 0 0 9 0 5 0 0 2 a1 根据公式(1)进行计算： ∑(ai×Wi) =（21+36+0+25+16+16+2+9+48++0+0+9+0+5+0+0+2) = 189 189 ÷ 11 = 17 + 2/11 ∑(ai×Wi)(mod 11) = 2 然后根据计算的结果，从下面的表中查出相应的校验码，其中X表示计算结果为10： ∑(ai×WI)(mod 11) 0 1 2 3 4 5 6 7 8 9 10 校验码字符值ai 1 0 X 9 8 7 6 5 4 3 2 根据上表，查出计算结果为2的校验码为所以该人员的公民身份号码应该为 34052419800101001X。 这样，按以上的算法，加上我的这个行政区代码数据库，除了顺号类编码以外所有的数字都可以确定了。 数据库下在地址见附件 注：数据库是根据中华人民共和国国家统计局截至2003年6月30号的数据，但是之前有修改过的数据，详情见： http://www.stats.gov.cn/tjbz/index.htm。所以，假如是太早颁发的身份证在地区代码上可能有出入。可以参考国家统计局的数据 进一步应用：这些资料加上ip地址省份分布数据库（追捕或者qq的ip地址数据库都可以，qq的地址数据库使用方法可以在精华区找），可以 更准确地确认访问者的ip地址（不考虑使用代理或者访问者上网地不在其身份证颁发地的情况:>）。这个就不再深入了。 另： 我国自1999年实施公民身份号码制度以来，许多公民身份号码末位为“X”的公民，由于不明白“X”的含义，要求给予更换，产生了不必要的误会。目前我国公民身份证号码由18位数字组成：前6位为地址码，第7至14位为出生日期码，第15至17位为顺序码，第18位为校验码。检验码分别是“0、1、2、……10”共11个数字，当检验码为“10”时，为了保证公民身份证号码18位，所以用“X”表示。虽然校验码为“X”不能更换，但若需全用数字表示，只需将18位公民身份号码转换成15位居民身份证号码，去掉第7至8位和最后1位3个数码。 当今的身份证号码有15位和18位之分。1985年我国实行居民身份证制度，当时签发的身份证号码是15位的，1999年签发的身份证由于年份的扩展（由两位变为四位）和末尾加了效验码，就成了18位。这两种身份证号码将在相当长的一段时期内共存。两种身份证号码的含义如下： 18位的身份证号码 如：130429####%%%%0078 1~6位为地区代码，其中1、2位数为各省级政府的代码，3、4位数为地、市级政府的代码，5、6位数为县、区级政府代码。如13（河北省）04（邯郸市）29（永年县） 7~14位为出生年月日 15~17位为顺序号，是县、区级政府所辖派出所的分配码，每个派出所分配码为10个连续号码，例如“000-009”或“060-069”，其中单数为男性分配码，双数为女性分配码，如遇同年同月同日有两人以上时顺延第二、第三、第四、第五个分配码。如：007的就是个男生而且和他同年月日生的男生至少有两个 他们的后四位是001* 和 003* 18位为效验位（识别码），通过复杂公式算出，普遍采用计算机自动生成。是前面17位的一种检验代码，如果你改变了前面某个数字而后面的效验代码不响应改变就会被计算软件判断为非法身份正号码。X也是效验代码的一中 15位的身份证号码： （1）1~6位为地区代码 （2）7~8位为出生年份(2位)，9~10位为出生月份，11~12位为出生日期 （3）第13~15位为顺序号，并能够判断性别，奇数为男，偶数为女。 |
| 26楼 春雷 |
版主们，PK公式了！大饱眼福 |
| 27楼 雷鸣 |
厉害，本版高手云集！ |
| 28楼 fraway |
哇 好牛阿 学习学习！！！！！ |
| 29楼 林夕瑜 |
呵呵，在其它看到过。 |
| 30楼 skyfree |
感谢分享 学习一下！ |
| 31楼 shenying007 |
各位前辈啊，你们会不会太谦虚了点啊; 貌似我觉得每个公式都好“赞”哦！ 哈哈哈—— 各位老师不妨也教教小女子我吧~ 我超级想学呢，只是函数公式多的让我有点摸不着头脑。 拜托啦，各位前辈、老师~ 指点指点小女子我吧，我到底该从哪方面开始着手来？ |
| 32楼 ZJYF |
我下载后怎么全是英语的啊 一点也看不懂的 要怎么办才能看到啊 |
| 33楼 fanpx |
好东东，应用才是学习的动力哦 |
| 34楼 myliujjj |
几位版主的钻研学习精神真的令人佩服，我相信这个团队会越来越强大的。 |
| 35楼 ahui21 |
这里的讨论还真精彩，见大师们的风采了。 |
| 36楼 海洋之星 |
十分谢谢，我问题从你这得到答案了 |
| 37楼 shuiruyan |
谢谢分享！ |
| 38楼 海盗老大 |
多谢楼主分享，学习了！ |
| 39楼 ba3639 |
能否专门写个广西用的身份证号码应用函数。 |
| 40楼 fgaq111 |
看看高见。 |
| 41楼 gvntw |
2005年3月写的，现转到此版。 身份证号码的相关应用： 利用有效性限制录入身份证号码（限制15或18位，18位计算验证码正确与否）；提取出生日期；取得退休日期；15位与18位号码的互换；性别判断；提取号码所属省、市、区。 身份证号码集锦.zip |
| 42楼 wshcw |
有时间还是请版主再改改,传最新、最简短的公式的上来. |
| 43楼 五〇高手 |
非常感谢gvntw版主的分享，版主日理万机，还能给我们写这么实用的东西，真好！ wshcw先生，您如果有好的公式，也直接贴出来分享给大家看看，可以吗？ |
| 44楼 gvntw |
最近较忙，所以没进行改进。 我想公式也应尽量具有可读性，易于理解为主，先改进一下出生日期吧。 出生日期： =IF(A2="","",--TEXT(RIGHT(19&MID(A2,7,LEN(A2)/2-1),8),"0-00-00")) 年龄： =IF(A2<>"",DATEDIF(TEXT(RIGHT(19&MID(A2,7,LEN(A2)/2-1),8),"0-00-00"),TODAY(),"y"),) 退休日期： =IF(A2<>"",EDATE(TEXT(RIGHT(19&MID(A2,7,LEN(A2)/2-1),8),"0-00-00"),660+MOD(RIGHT(LEFT(A2,17)),2)*60),) 对于身份证号码的限制，以后我还会再改进，不仅仅是限制15或18位和验证码，还包括出生日期必须是有效日期，除验证码外，其他字符必须都是数字等，这将在我下一本书中进行介绍。 |
| 45楼 wshcw |
写了一个提取日期的,不妥之处请指正. =--RIGHT(TEXT((0&MID(A2,7,11))-500,"1900-00-00,;!0"),10) 单元格格式: yyyy-m-d;; |
| 46楼 gouweicao78 |
=--TEXT(INT(10*(0&MID(A2,7,11))%%),"#-00-00;;0")——48字 =--TEXT(INT((0&MID(A2,7,11))/1000),"#-00-00;;0")——48字 =--TEXT(TRUNC(0&MID(A2,7,11),-3),"#-00-00,;;0")——47字 =--TEXT((0&MID(A2,7,11))-500,"#-00-00,;!0")——43字 缺点：15位身份证年份为01~29时，默认为2001~2029，年份为30~99默认为1930~1999。不过一般来说，目前身份证大多用18位且1929年以前的身份证号码估计很少用到。 公式的长短，我倾向于gvntw兄的“便于理解”说法。公式的研究，则佩服wshcw兄的钻研精神以及思维的独到之处。 |
| 47楼 wshcw |
这是在其它社区中发表过的,但没考虑空值问题: =--TEXT(INT(MID(A1,7,11)/1000),"[<1E6]1900-00-00;#-00-00") =--MID(TEXT((19&MID(A1,7,11))-500,"000000-00-00,"),3,10) =--TEXT(MID(A1,7,11)-500,"[<1E6]19#-00-00,;#-00-00,") =--TEXT(MID(A1,7,11)-500,"[<1E9]1900-00-00,;#-00-00,") =--RIGHT(TEXT(MID(A1,7,11)-500,"1900-00-00,"),10) |
| 48楼 wshcw |
还有几个老公式,已是前个月(今年10月份)发表的了,问题多多请指正: 55 =--RIGHT(TEXT((19&MID(A1,7,LEN(A1)/2-1)),"#-00-00"),10) 57 =MOD(TEXT(MID(A1,LEN(A1)/2,6),"0-00-00")-3236,36525)+3236 58 =--TEXT(MOD((19&MID(A1,7,LEN(A1)/2-1)),19*10^8),"#-00-00") 69 =MOD(TEXT(MID(A1,LEN(A1)/2,6),"0-00-00")-TODAY(),36525)+TODAY()-36525 以下公式不能处理2000年后的问题 49 =MOD(TEXT(MID(A1,7,LEN(A1)/2-1),"#-00-00"),36525) 47 =MOD(TEXT(MID(A1,LEN(A1)/2,6),"0-00-00"),36525) |
| 49楼 gvntw |
wshcw兄的水平和研究精神令人佩服！ 不过，我用LEN(A1)/2-1已经有点取巧了，利用了15与18位号码相差的位数及MID函数参数中，带小数点的处理方法进行了取巧，已经得花点时间解释了。 wshcw兄的“-500”及格式中的“1900-00-00,”，都是需要解释的，如“-500”涉及的四舍五入，“1900”中的“19”，利用后面的“,”号缩小1000倍等都需要详细解释，没有一定水平的用户还是比较难以解释清楚的。 虽然省下了3个字符，但得花比较多的时间解释，有点得不偿失哦。 记得在EH时，我写过一个不重复值的公式，当时是省下了Match()函数的0，完整的写法：Match(0,Countif(),0)，省下后的写法：Match(,Countif(),)，当时也费了点时间解释为什么只有逗号，没有参数。不过这个还比较容易解释，呵呵。 再次谢谢wshcw兄精简的公式及敏捷的思路！ |
| 50楼 wshcw |
惭愧,MID(A2,7,11))的用法,并非是我的首创,我也只是引用改进,36525做除数求余的用法是我首创,但不太好. |
| 51楼 俟人.琳 |
=TEXT(MID(B32,7,6+(LEN(B32)=18)*2),"#-00-00")*1 身份证号码：530103195404062162，请问能不能解释下上面的公式呢？ |
| 52楼 mmoophy |
强帖，这个必须收藏 |
| 53楼 renyz2006 |
测试了一下退休年龄公式不正确？？ =IF(A1<>"",EDATE(TEXT(RIGHT(19&MID(A1,7,LEN(A1)/2-1),8),"0-00-00"),660+MOD(RIGHT(LEFT(A1,17)),2)*60),) |
| 54楼 jianzhanchou |
不错，好好看看 |
| 55楼 2276364850 |
感谢分享 |
| 56楼 传递 |
妙 -(A2="") |
| 57楼 ruoyu |
来向楼主学习了 |
| 58楼 icenotcool |
 |
| 59楼 swbuing |
学习中 |
| 60楼 swbuing |
学习学习 |
| 61楼 rongjun |
学习了! |
| 62楼 海洋之星 |
这几天正在处理员工信息，刚好用上 |
| 63楼 wise |
谢谢分享 |
| 64楼 芐雨 |
感谢分享 |
| 65楼 lrlxxqxa |
|
| 66楼 mjxiao21 |
XUEXI |
| 67楼 335081548 |
感谢分享 |