excel：身份证号有效性检查

PAGEPAGE1excel：身份证号有效性检查第一篇：excel：身份证号有效性检查A：位数检查1、选中A1:A102、“数据/有效性/设置/允许/自定义/公式/=OR(LEN(A1)=15,LEN(A1)=18)/出错警告/样式/停止/标题/错误/错误信息/号码位数错误，请重新输入！/确定”。3、在选区输入非15、18位数值时即出现错误提示对话框并发出警告声响，只有点“取消”，再输入正确数值方可。B：用性别判别对与错：1、如果以C列输入性别为准，在B列输入公式“=IF(IF(MOD(MID(A1,17,1),2)=0,“女”,“男”)=C1,“第17位正确”,“第17位错”)”，向下复制到需要处如B3，即可判别A列号码第17位数码之对错，如黄色区域所示。（提取性别=IF(MOD(MID(A2,17,1),2)=0,“女”,“男”)）2、如果以A列输入号码为准，在D列输入公式“=IF(IF(MOD(MID(A1,17,1),2)=0,“女”,“男”)=C1,“C列正确”,“C列填错”)”，向下复制到需要处如D3，即可判别C列填写之对与错，如绿色区域所示。C：检查有无重复H1（或者其他空列)公式=COUNTIF(G:G,G1&“*”)向下填充到底出现大于1的数就是有重复的第二篇：人事ecel技巧www.ChinaHRD.Net散发人性道德光辉沉淀人类管理智慧轻松运用EXCEL建立人事信息库接下来请您不要急着录入人员信息，我们要对一些信息项进行函数设置，以便系统可以自动生成相关信息，这会使我们的工作产生事半功倍的效果。1、性别、出生月日、年龄的自动填充功能设置我们先对“性别”“出生年月”“年龄”进行函数设置。当我们输入某人身份证号码时，系统便会自动生成“性别”，“出生年月”及“年龄”，这样就减少了我们录入的工作量。请分别选择性别、出生月日、年龄信息项单元格输入下列公式:(1)性别:=IF(MOD(IF(LEN(E3)=15,MID(E3,15,1),MID(E3,17,1)),2)=1,“男”,“女”)含义：“LEN(E3)=15”表示看E3中是否有15个字符;“MID(E3,15,1)”表示在E3中从第15位开始提取1位字符;“MOD(MID()，2)=1”表示提取的字符除以2余数为1;“IF(LEN(E3)=15,MID(E3,15,1),MID(E3,17,1)”表示看E3中是否够15个字符，如果够就从第15个字符开始取1个字符，如果不够15个字符就从第17个字符开始取1个字符。我们的身份证号码一般是15位或18位。“IF(MOD(IF(LEN(E3)=15,MID(E3,15,1),MID(E3,17,1)),2)=1,“男”,“女””表示所取字符除以2如果余数为1显示男，否则显示女。(2)出生年月:=DATE(MID(E3,7,4),MID(E3,11,2),MID(E3,13,2))含义：DATE(YEAR,MONTH,DAY);“MID(E3,7,4)”表示在E3中从第7个字符开始连续取4个字符表示年，用类似的表示方法一个人的出生年月日便可以通过函数设置表示出来，如果为了看起来方便，我们可以将单元格格式设置成年、月、日的日期格式，这样显示的结果会非常容易理解。(3)年龄:=DATEDIF(TEXT(MID(B2,7,8),“0000-00-00”),TODAY(),“y”)含义：“DATEDIF(date1,date2,“Y”)”表示两个日期的差值;“TODAY()”表示系统自带的日期即显示当日日期;“DATEDIF(G3,TODAY(),“Y”)”表示今天的日期与G3所表示的出生月日之间的年份差值，这样一个人的年龄就会容易的显示出来了。2、劳动期限的自动生成和提前30天定期提醒功能设置管理也是人事管理中不可缺少的一部分，特别是劳动合同到期续签问题更是不可忽视。如果分享中人网·共建中人网www.ChinaHRD.Net散发人性道德光辉沉淀人类管理智慧由于我们的人为原因造成了劳动合同漏签、延期签订等问题可能会给公司以及员工个人带来一定的损失。从一个人入职签订试用期合同开始，再到签订正式劳动合同以及后来的续签劳动合同，一系列的日期如何能够让系统自动生成，并且形成系统提前30天自动提醒我们的功能呢？这需要对一些信息项进行函数设置。如图(二)所示:图二信息项进行函数设置(1)试用期到期时间:=DATE(YEAR(P3),MONTH(P3)+3,DAY(P3)-1)含义：“DATE(YEAR(),MONTH(),DAY())”显示指定日期;在这里我们假设试用期为3个月，我们需要在Q3单元格中输入上述公式，其中MONTH(P3)+3表示在此人入职时间月的基础上增加三个月。而DAY(P3)-1是根据劳动合同签订为整年正月而设置的。比如20XX年11月6日到20XX年11月5日为一个劳动合同签订期。(2)劳动合同到期时间:=DATE(YEAR(P3)+1,MONTH(P3),DAY(P3)-1)我们同样采用上述函数的设置方法。这里我们假设劳动合同期限为1年，则我们需要设置成YEAR(P3)+1，另外这个数值依然以入职日期为计算根据，所以天数上还要设置成DAY(P3)-1的格式。(3)续签合同到期时间:=DATE(YEAR(S3)+1,MONTH(S3),DAY(S3))这里需要注意的是续签合同计算是以前份合同签订到期日期为根据的，所以只在前一份合同到期时间的基础上增加1年即可，无需天数上减1。分享中人网·共建中人网www.ChinaHRD.Net散发人性道德光辉沉淀人类管理智慧(4)试用期提前7天提醒:=IF(DATEDIF(TODAY(),Q3,“d”)=7,“试用期快结束了”,“")这里用到了DATEDIF函数，表示两个日期差值，但是需要注意的是，我们要表示提前7天提醒，所以，将TODAY()函数写到试用期时间前面即TODAY(),Q3而不能表示成Q3，TODAY()。其中“d”表示两个日期天数差值。我们用IF()函数来表示显示，那么这个函数设置的含义为:如果差值为7则显示“试用期快结束了”否则不显示信息，在编辑函数时用“”表示不显示任何信息。(5)提前30天提醒:=IF(DATEDIF(TODAY(),S4,”m“)=1,”该签合同了“,”“)函数设置方法同上，其含义是两个日期相差1个月则显示“该签合同了”否则不显示任何信息。这里没有设置成相差30天提醒是因为考虑到设置成月更利于我们人事工作的操作。同样需要注意的是不要将显示“今天日期”函数与显示“合同到期日期”函数顺序颠倒。其中”m"表示月的含义。3、采用“单”录入信息通过对一些人事信息项进行函数设置后，我们便可以开始录入信息了。逐行的键入人事信息，会让人很快产生疲劳感，甚至会出现串行或输错信息的工作失误。我们可以采用Excel自带的“记录单”功能来解决这个问题。请点击编辑栏中的“数据”―“记录单”如图(三)我们可以用“Tab键进行项目的换行录入如图(四)。”图三记录单分享中人网·共建中人网www.ChinaHRD.Net散发人性道德光辉沉淀人类管理智慧图四Tab键进行项目的换行录入4、用“窗口冻结”功能可以进行简单的数据查询当我们录入完数据后，我们希望能够非常方便的查询信息，但是由于信息库所涉及的项目很多，我们常常会遇到这样的情况:看到左边的信息又看不到右边的信息，或者看到下面的信息又不知道此信息所对应的信息项。这时我们可以采用“窗口冻结”功能。例如我现在想保留各信息项，同时保留每个人的编号、姓名、部门，让其他信息可以根据需要进行查找，这时我们可以点击D3单元格，然后点击编辑菜单栏的“窗口”，选择“冻结窗口”，就可以出现如图(五)情况:分享中人网·共建中人网www.ChinaHRD.Net散发人性道德光辉沉淀人类管理智慧图五查询信息这样我们会非常容易查到某人相关信息，但是值得注意的是设置窗口冻结的规律:如果你想冻结第2行请将光标放到第3行单元格处，进行冻结窗口设置。如果你想冻结C列，请将光标放到D列单元格处，进行冻结窗口设置。如果你即想冻结第2行又想冻结C列，请将光标放到他们的交叉单元格D3上进行设置。如果想取消冻结功能可以点击“窗口”菜单选择取“消冻结窗口”功能。5、“自动筛选”功能可以进行简单的数据统计有时我们想非常快速的得到一些数据，比如本公司在职人员中，本科生学历的男生有多少人?这时我们可以采用自动筛选功能。请选择“人员类别”单元格，点击编辑菜单栏上的“数据”选择“筛选”，点击“自动筛选”。在每个信息项单元格右下角都会出现选择按钮。我们分别在人员类别处选择在职(图(六))，在学历处选择本科(图(七))，在性别处选择男(图(八))，最后用鼠标将显示的性别全部选上，这时注意图(九)中用红笔圈住的地方就是我们所需要的数据了。分享中人网·共建中人网www.ChinaHRD.Net散发人性道德光辉沉淀人类管理智慧6、用“数据透视表”功能快速汇总各项数据以上功能的设置多用于日常人事工作中，但到年底我们需要对这一年的人事情况进行大量的信息总结，比如人员的离职情况，入职情况，各类数据构成比例等。这项工作更是一项非常繁重的工作。我们可以利用Excel自带的“数据透视表”功能为我们排忧解难。请点击编辑菜单栏中“数据”选项，选择“数据透视表和数据透视图”选项，如图(十)分享中人网·共建中人网www.ChinaHRD.Net散发人性道德光辉沉淀人类管理智慧图十选择“数据透视表和数据透视图”选项选择“数据透视表”接下来点击“下一步”即可，我们需要确定建立数据透视表的数据源区域，一般系统会自动将整个信息库区域设置成我们要选择的区域。在出现“数据透视表和数据透视图向导-3”第3步时我们选择“现有工作表”，将工作表区域选择在新的sheet中A3单元格”如图(十一)。点击“完成”后，便会出现(图(十二))结果。这里解释一下我们为什么要选择A3单元格，这主要是因为显示区上面有两行用来放置页字段。图十一将工作表区域选择在新的sheet中A3单元格分享中人网·共建中人网www.ChinaHRD.Net散发人性道德光辉沉淀人类管理智慧图十二蓝圈圈住的地方例如我们现在想统计各部门20XX年入职人员情况。我们便可以将相关项目用鼠标托至指定位置，如图(十二)红色箭头显示。通过对信息项目的拖拽，系统会自动出现相关信息的统计数据。如果我们还想出现统计数据的图示，这时我们可以点击数据透视表编辑菜单栏上的图例图标，如图(十二)蓝圈圈住的地方。这时会出现chart1，如图(十三)所示。如果我们想改变图例显示类型可以点击图表向导进行自由选择。分享中人网·共建中人网www.ChinaHRD.Net散发人性道德光辉沉淀人类管理智慧图十三完成通过这个功能我们可以很容易的统计出各种人事资料，比如学历构成，性别比例等。同时您还可以根据自己的需要来设置布局。当出现“数据透视表和数据透视图向导”第3步时，我们可以点击“布局”按钮，接下来会出现“数据透视表和数据透视图向导-布局”在这里我们可以通过对话框中右侧的数据按钮添加或删除我们需要的数据项。如图(十四)分享中人网·共建中人网www.ChinaHRD.Net散发人性道德光辉沉淀人类管理智慧图十四统计人事资料以上讲述的是一些常用功能的设置，您还可以举一反三，加入一些好用的小功能分享中人网·共建中人网第三篇：身份证号身份证号:功能:1)查询身份证号所属地区2)分析出生年月日、性别是查看身份证所有人的所在地区和出生年月日、性别18位身份号一般是由所在地+所在区（县）+出生年月日+个人代码组成的！个人代码尾数（也就是身份证号的最后一位）双数为女性，单数为男性！举个例子：我是天津，天津地区号是120XX号（以河西区为例）103生日：1958年2月9号个人的代码是5918这样组成的身份证号就是***918中华人民共和国行政区划代码（GB/T2260—999）代码省、市名称代码省、市名称11北京市42湖北省12天津市43湖南省13河北省44广东省14山西省45广西壮族自治区15内蒙古自治区46海南省21辽宁省50重庆市22吉林省51四川省23黑龙江省52贵州省31上海市53云南省32江苏省54西藏自治区33浙江省61陕西省34安徽省62甘肃省35福建省63青海省36江西省64宁夏回族自治区37山东省65新疆维吾尔自治区41河南省我们要验证身份证号是否正确，就得先了解身份证号的含意。身份证号都代表什么意思？1、号码的结构公民身份号码是特征组合码，由十七位数字本体码和一位校验码组成。排列顺序从左至右依次为：六位数字地址码，八位数字出生日期码，三位数字顺序码和一位数字校验码。2、地址码(前六位数）表示编码对象常住户口所在县(市、旗、区)的行政区划代码，按GB/T2260的规定执行。3、出生日期码（第七位至十四位）表示编码对象出生的年、月、日，按GB/T7408的规定执行，年、月、日代码之间不用分隔符。4、顺序码（第十五位至十七位）表示在同一地址码所标识的区域范围内，对同年、同月、同日出生的人编定的顺序号，顺序码的奇数分配给男性，偶数分配给女性。5、校验码（第十八位数）（1）十七位数字本体码加权求和公式S=Sum(Ai*Wi),i=0,...,16，先对前17位数字的权求和Ai:表示第i位置上的身份证号码数字值Wi:表示第i位置上的加权因子Wi:7910584216379105842（2）计算模Y=mod(S,11)（3）通过模得到对应的校验码Y:012345678910校验码:10X98765432尾数“X”啥意思？升位后有一部分人身份证号码的尾数却是“X”，常常被怀疑是假证。其实，第18位校验码是由公安部门按统一的公式计算出来的，从“1”至“10”，由10个阿拉伯数字组成。假如身份证尾号是10，那么号码就成了19位数，有关部门采用了罗马数字“X”来代替10，这样，有一部分居民的身份证号码尾数就是“X”。辽宁省210000名称代码名称代码沈阳市210100西岗区21020XX市辖区210101沙河口区21020XX和平区210102甘井子区210211沈河区210103旅顺口区210212大东区210104金州区210213皇姑区210105长海县210224铁西区210106瓦房店市210281苏家屯区210111普兰店市210282东陵区210112庄河市210283新城子区210113鞍山市210300于洪区210114市辖区210301辽中县210122铁东区210302康平县210123铁西区210303法库县210124立山区210304新民市210181旧堡区210311大连市21020XX台安县210321市辖区21020XX岫岩满族自治县210323中山区21020XX海城市210381抚顺市210400市辖区210801市辖区210401站前区210802新抚区210402西市区210803露天区210403鲅鱼圈区210804望花区210404老边区210811顺城区210411盖州市210881抚顺县210421大石桥市210882新宾满族自治县210422阜新市210900清原满族自治县210423市辖区210901本溪市210500海州区210902市辖区210501新邱区210903平山区210502太平区210904溪湖区210503清河门区210905明山区210504细河区210911南芬区210511阜新蒙古族自治县210921本溪满族自治县210521彰武县210922桓仁满族自治县210522辽阳市211000丹东市210600市辖区211001市辖区210601白塔区211002元宝区210602文圣区211003振兴区210603宏伟区211004振安区210604弓长岭区211005凤城满族自治县210621太子河区211011宽甸满族自治县210624辽阳县211021东港市210681灯塔县211022锦州市210700盘锦市211100市辖区210701市辖区211101古塔区210702双台子区211102凌河区210703兴隆台区211103太和区210711大洼县211121北镇满族自治县210725盘山县211122黑山县210726铁岭市21120XX义县210727市辖区21120XX凌海市210781银州区21120XX营口市210800清河区21120XX铁岭县211221喀喇沁左翼蒙古族自治县211324西丰县211223北票市211381昌图县211224凌源市211382铁法市211281锦西市211400开原市211282市辖区211401朝阳市211300连山区211402市辖区211301葫芦岛区211403双塔区211302南票区211404龙城区211303绥中县211421朝阳县211321建昌县211422建平县211322兴城市211481第四篇：身份证号变更证明身份证号变更证明身份证号变更证明公民身份证号码变更证明本辖区居民：性别：民族住址：原使用的公民身份证号码为□□□□□□□□□□□□□□□□□□系(重/错号)。根据《中华人民共和国居民身份证法》的有关规定及公民身份号码的编制原则，为确保公民身份号码的准确性和唯一性，自年月日起将原号码更正为：□□□□□□□□□□□□□□□□□□。特此证明。您们好!我是重庆合川区二郎镇村民，因为我的身份证第一代与第二代还有银行卡都一起被小偷偷了，现第二代身份证已补办，但银行方面因为是第一代身份证与第二代身份证的号码有所变更，银行方面要求镇派出所出示一份证明，证明两个号码为同一个人，第一代的号码和第二代的，这也没什么影响，可我多次找到镇派出所开具证明，镇派出所都强行拒绝，我带上了户口本也不行，派出所的态度极度恶劣，拒绝的理由是为什么要为我开具证明，要求我出示银行卡，我有卡还出什么证明，吃多了没事做吗?说什么谁知道我搞什么，自己不把东西捡好，这是一个拿着老百姓的血汗钱为人民办的事吗?古有国以民为生，现也一样，老百姓需要的不就是一个为人民做事的人吗?他坐到那个位置上这点事都做不了、辩不了，那拿着钱做什么的，吃干饭的吗?像这种情况我的钱难道就不能要了吗?是政府改的身份证号码，让他查一下两个身份证确实属于同一人，开这样的证明他为什么不开，身份证号码是他改的，他出具一个证明有那么难吗?况且我还出具了户口本，要不给我补办第一代身份证了，那为什么政府更换身份证时不通知大家把银行卡也更换了，派出所的管事的人回答说，没有存档，他是管身份证的，这点事都做不好要来好看的吗?身份证是国民的代表信息，代表一个人的身份，他居然可以代表政府说出这样不负责任的话，查不到号码，看不到信息，没有存档，政府就是这样为人民做事的，问题是我补办身份证才几天，他居然这样说，现在我们老百姓要办点事比登天还难，补办身份证，今天叫你明天去，明天叫你后天去，跑了好多次，明明可以收也不收，就要难为老百姓，还凶得不得，政府官员代表的不止是一个政府还有一个国家，这样的态度难道就没人管了吗?政府就是这样为人民做事的，这样的人才来代表国家和政府的，别忘了你们的根也是老百姓。打电话到合川政府派出所，一个电话给你转一个电话，电话费都打了几十元，可没一个人管，就一个部门接一个部门的让你去找，找来找去就是循环的，难道政府就没人受理老百姓的事，真正为老百姓办事没有吗?这是什么时代，什么社会呀!政府总是让老百姓相信政府，可政府做了什么让老百姓相信的事了，值得信任的事了，如果再没人受理，我就不怕浪费时间继续反映，这里不受理我再重庆，再北京，上网发微博，我相信总有人管的，老百姓一定会找到有人帮的好官员的……急切的等待你们的回复，也等待你们处理此事，我的银行里的钱等着镇派出所开具证明才以能挂失……谢谢!办理单位:区公安局办理结果:来信人：您好，来信收悉，区政府高度重视，已责成区公安局调查处理，现将您反映的问题回复如下：关于您反映二代身份证和一代身份证号码不一致，要求派出所出具证明的问题。区公安局安排户籍科工作人员与您联系后，着手调查，并将有关情况向您进行了详细解释。经查，在常住人口系统中未发现您有变更身份证号码的信息，但是您一代身份证号码为15位，在更换为二代身份证后，15位的号码升为18位，属于正常的变化。因为您办理银行卡时，是使用第一代即15位号码的身份证登记的，现在遗失，需要到派出所出具证明。对此，请您将二代身份证及相关一代身份证材料带到二郎镇派出所，由派出所出具证明。经追踪调查，二郎镇派出所已于12月14日，在您家人提交齐全有关证明材料后，为您出具了证明材料，目前已由您的家人寄往您工作所在地。第五篇：山东各地身份证号370000山东省37010037010137010237010337010437010537011237011337012437012537012637018137020XX37020XX37020XX37020XX37020XX370211370212370213370214370281370282370283370284370285370300370301370302370303370304370305370306370321370322370323济南市市辖区历下区市中区槐荫区天桥区历城区长清区平阴县济阳县商河县章丘市青岛市市辖区市南区市北区四方区黄岛区崂山区李沧区城阳区胶州市即墨市平度市胶南市莱西市淄博市市辖区淄川区张店区博山区临淄区周村区桓台县高青县沂源县370400370401370402370403370404枣庄市市辖区市中区薛城区峄城区370405370406370481370500370501370502370503370521370522370523370600370601370602370611370612370613370634370681370682370683370684370685370686370687370700370701370702370703370704370705370724370725台儿庄区山亭区滕州市东营市市辖区东营区河口区垦利县利津县广饶县烟台市市辖区芝罘区福山区牟平区莱山区长岛县龙口市莱阳市莱州市蓬莱市招远市栖霞市海阳市潍坊市市辖区潍城区寒亭区坊子区奎文区临朐县昌乐县370781370782370783370784370785370786370800370801370802370811370826370827370828370829370830370831370832370881370882370883370900370901370902370903370921370923370982370983371000371001371002371081371082371083371100371101371102青州市诸城市寿光市安丘市高密市昌邑市济宁市市辖区市中区任城区微山县鱼台县金乡县嘉祥县汶上县泗水县梁山县曲阜市兖州市邹城市泰安市市辖区泰山区岱岳区宁阳县东平县新泰市肥城市威海市市辖区环翠区文登市荣成市乳山市日照市市辖区东港区37110337112137112237120XX37120XX37120XX37120XX371300371301371302371311371312371321371322371323371324371325371326371327371328371329371400371401371402371421371422371423371424371425371426371427371428371481371482371500371501371502岚山区五莲县莒县莱芜市市辖区莱城区钢城区临沂市市辖区兰山区罗庄区河东区沂南县郯城县沂水县苍山县费县平邑县莒南县蒙阴县临沭县德州市市辖区德城区陵县宁津县庆云县临邑县齐河县平原县夏津县武城县乐陵市禹城市聊城市市辖区东昌府区371521371522371523371524371525371526371581371600371601371602371621371622371623371624371625371626371700371701371702371721371722371723371724371725371726371727371728阳谷县莘县茌平县东阿县冠县高唐县临清市滨州市市辖区滨城区惠民县阳信县无棣县沾化县博兴县邹平县菏泽市市辖区牡丹区曹县单县成武县巨野县郓城县鄄城县定陶县东明县