设为首页收藏本站|繁體中文

Excel 技巧网

 找回密码
 注册

QQ登录

只需一步,快速开始

手机号码,快捷登录

查看: 25203|回复: 70

[综合应用] [原创]身份证号码应用

  [复制链接]
发表于 2008-12-18 13:38:47 | 显示全部楼层 |阅读模式
  • 署名作者: gvntw
  • 版权声明: 版权归本站与作者共有 除本站官方外非作者本人转载须经许可并注明出处
  • 本文来自:
  • 引用作品:
  • 适用版本: 2010 2007 2003以前版本 
  • 语言环境: 简体中文
  • 学习方法: 掌握Excel技巧的关键是动手操作 | 下载 ≠ 知识


  • 免费注册成为本站会员,享用更多功能,结识更多Office办公高手!

    您需要 登录 才可以下载或查看,没有帐号?注册

    x
    本帖最后由 lrlxxqxa 于 2012-5-26 21:29 编辑

    2005年3月写的,现转到此版。
    身份证号码的相关应用:
    利用有效性限制录入身份证号码(限制15或18位,18位计算验证码正确与否);提取出生日期;取得退休日期;15位与18位号码的互换;性别判断;提取号码所属省、市、区。

    身份证号码集锦.zip

    153.33 KB, 下载次数: 1923, 下载积分: 消费券 -5 Ti币

    [原创]身份证号码应用

    评分

    参与人数 4魅力值 +16 收起 理由
    ifljh + 5 思路巧妙:)
    zhanghi + 3 谢谢分享
    蒸蒸日上 + 5 谢谢分享!
    pldx1569 + 3 非常好

    查看全部评分

    发表于 2008-12-19 10:00:53 | 显示全部楼层
    有时间还是请版主再改改,传最新、最简短的公式的上来.
    回复 支持 反对

    使用道具 举报

    发表于 2008-12-19 10:19:47 | 显示全部楼层
    非常感谢gvntw版主的分享,版主日理万机,还能给我们写这么实用的东西,真好!
    wshcw先生,您如果有好的公式,也直接贴出来分享给大家看看,可以吗?
    回复 支持 反对

    使用道具 举报

     楼主| 发表于 2008-12-19 12:56:21 | 显示全部楼层


    最近较忙,所以没进行改进。
    我想公式也应尽量具有可读性,易于理解为主,先改进一下出生日期吧。
    出生日期:
    =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位和验证码,还包括出生日期必须是有效日期,除验证码外,其他字符必须都是数字等,这将在我下一本书中进行介绍。
    回复 支持 反对

    使用道具 举报

    发表于 2008-12-19 14:40:04 | 显示全部楼层
    写了一个提取日期的,不妥之处请指正.
    =--RIGHT(TEXT((0&MID(A2,7,11))-500,"1900-00-00,;!0"),10)
    单元格格式:
    yyyy-m-d;;

    评分

    参与人数 2技术分 +1 魅力值 +2 收起 理由
    gvntw + 2 研究精神,值得学习!
    gouweicao78 + 1 我的偶像

    查看全部评分

    回复 支持 反对

    使用道具 举报

    发表于 2008-12-20 01:41:48 | 显示全部楼层
    原帖由 wshcw 于 2008-12-19 14:40 发表
    写了一个提取日期的,不妥之处请指正.
    =--RIGHT(TEXT((0&MID(A2,7,11))-500,"1900-00-00,;!0"),10)
    单元格格式:
    yyyy-m-d;;

    =--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兄的钻研精神以及思维的独到之处。

    [ 本帖最后由 gouweicao78 于 2008-12-20 01:56 编辑 ]
    回复 支持 反对

    使用道具 举报

    发表于 2008-12-20 12:36:51 | 显示全部楼层
    这是在其它社区中发表过的,但没考虑空值问题:
    =--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)
    回复 支持 反对

    使用道具 举报

    发表于 2008-12-20 13:29:29 | 显示全部楼层
    还有几个老公式,已是前个月(今年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)
    回复 支持 反对

    使用道具 举报

     楼主| 发表于 2008-12-20 13:42:09 | 显示全部楼层
    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兄精简的公式及敏捷的思路!
    回复 支持 反对

    使用道具 举报

    发表于 2008-12-20 14:28:01 | 显示全部楼层
    原帖由 gvntw 于 2008-12-20 13:42 发表
    wshcw兄的水平和研究精神令人佩服!
    不过,我用LEN(A1)/2-1已经有点取巧了,利用了15与18位号码相差的位数及MID函数参数中,带小数点的处理方法进行了取巧,已经得花点时间解释了。
    wshcw兄的“-500”及格式中的“ ...

    惭愧,MID(A2,7,11))的用法,并非是我的首创,我也只是引用改进,36525做除数求余的用法是我首创,但不太好.
    回复 支持 反对

    使用道具 举报

    您需要登录后才可以回帖 登录 | 注册

    本版积分规则

    关闭

    站长推荐上一条 /1 下一条

    Excel技巧网的会员探讨问题仅代表其个人意见,与网站的立场无关。任何违反国家和地方相关法律法规的言论,本站有义务协助政府相关部门追究发言者的责任!
    本站中非注明转载文章与案例的版权为作者与Excel技巧网共有。若非原文作者,本站之外任何单位或个人未经允许,不得将其用于商业用途。
    若非原文作者,任何形式的非商业性转载必须获得Excel技巧网或作者允许,并注明作者和出处。
    会员发表的帖子如涉及版权纠纷,须自行负责。详情请参考注册时的网站服务条款。
    本站特聘法律顾问:沈学律师

    Archiver|手机版|Excel技巧网 ( 闽ICP备08107682号-2 ) | 闽公网安备 35020302032608号  

    GMT+8, 2019-3-23 06:46

    Powered by Discuz! X3.4

    © 2001-2017 Comsenz Inc.

    快速回复 返回顶部 返回列表