下载文档原格式
将15位⾝份证补全为18位⾝份证的算法⽰例详解前⾔最近在参与⼀个银⾏项⽬-某银⾏安防系统-反洗钱需求的开发，银⾏项⽬的离不开⾝份证号码，⾝份证号码作为我国公民的唯⼀标识，有这⾮同寻常的意义，由于业务的要求15位的⾝份证号码⽆法命中，所以需要补全为18位，⼀开始⾃⼰想着加个年份的前两位，后⾯再加个X不就⾏了嘛，后来代码写不下去了，上⽹查了资料，才知道⾃⼰想的是多么天真，还是⽐较复杂的，折腾了⼀下午终于有了眉⽬。
⼀、15位⾝份证和18位⾝份证号码结构介绍要进⾏⾝份证号码的验证，⾸先需要了解我国⾝份证号码的编码规则。
我国⾝份证号码多由若⼲位数字或者数字与字母混合组成。
早期⾝份证由15位数字构成，这主要是在1980年以前发放的⾝份证，后来考虑到千年⾍问题，因为15位的⾝份证号码只能为1900年1⽉1⽇到1999年12⽉31⽇出⽣的⼈编号，所以⼜增加了18位⾝份证号码编号规则。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15A A A A A A Y Y M M D D N N S前六位AAAAAA是⾝份证编码对象的所在地（出⽣地）的编码，该号码可由国家统计局公布的相关标准中得到。
YY表⽰出⽣年的后两位，MM和DD表⽰出⽣⽉和⽇，不⾜两位的⾼位补0，NNS为顺序号，⽆法确定。
S为性别识别码，男性为奇数，⼥性为偶数。
了解了这些，再来写代码就变得容易多了。
⼆、算法实现了解了⾝份证号码的规则后，我们就可以推断出，⾝份证的15位转化位需要两步。
⾸先把15位⾝份证号补全为17位，然后再补全最后⼀位。
但是最后⼀位是数字还是字母X？这⾥⼜出现了问题。
我们知道，⾝份证的最后⼀位为校验位，那么最后⼀位是怎么得到的呢？原来，最后⼀位是由数字1-9组成，超过9的⽐如11就⽤字母X表⽰，否则号码就变成了19位。
了解了这些，经过整理得出⾝份证补全算法实现思想如下：step1、将15位⾝份证号码加⼊出⽣年变为17位 step2、将step1得到的⾝份证17位数分别乘以不同的系数。
给你5个实用Excel函数公式搞定盘踞在Excel表格中的身份证在工作中，将身份证输入表格的大多是财务或是HR，本以为只要输入数字就可以了，可是，当大家做这事儿的时候，有多少人发现，这个身份证真的很抽风……如果你正做着有关工作，那么就一起来看一下以下内容吧，让你的Excel操作轻松碾压各种有关身份证的信息。
以下是一组和身份证有关的函数公式，如果你是财务人员或是一名HR，那就一起来看一下啦：1、查找重复身份证号码在Excel表格上方输入【=IF(COUNTIF(A:A,A1&"*")>1,"重复","")】，其中“A1”可以自定义修改。
说明：COUNTIF函数在计算文本型数字时，会默认按数值型进行处理，但Excel中的数字精度只有15位，但身份证号码是18位，因此会把前15位相同的身份证全部识别为相同的内容。
在第二参数后加上一个星号 &"*"，就是告诉Excel，要查找包含A1单元格内容的文本，通过这样变通的手段，COUTNIF函数就会乖乖听话啦。
2、出生年月来可计算年龄在Excel表格上方输入【=DATEDIF(A4,TODAY(),"y")】，然后按回车键，即可在其中“A4”可以自定义修改。
说明：DATEDIF函数用于计算两个日期之间的间隔。
=DATEDIF（开始日期,结束日期,指定要返回的类型)；第三参数Y表示年，M则表示月。
TODAY函数返回系统当前的日期。
DATEDIF函数是隐藏函数，输入时没有屏幕提示，需要手工录入。
3、根据身份证号计算出生年月在Excel表格上方输入【=--TEXT(MID(A2,7,8),"0!/00!/00")】，然后按回车键，即可在其中“A2”可以自定义修改。
说明：身份证号码中的第8位到15位是出生年月信息。
先用MID函数从A2单元格的第7位开始，提取出8个字符，即可得到身份证号中的出生年月日信息。
Excel中身份证号码显示格式错误的解决方法
Excel中身份证号码显示格式错误的解决方法
很多人在excel表格中输入身份证号码等长串数字，如何输入才能变成我们熟悉的18位数字的形式呢?今天，小编就教大家在Excel 中身份证号码显示格式错误的解决方法。
Excel中身份证号码显示格式错误的解决步骤举例说明，如下图所示，可以看到在默认情况下如果输入了18位的身份证号，就会自动变成科学计数法的格式来显示;
首先选取身份证号所在的单元格，右键--设置单元格格式;
在左边的选项框中选择文本选项，点击确定;
点击确定之后，原来的数字变为靠左显示，但仍然显示为科学计数法的形式;
此时我们双击该单元格，就会显示出完整的18位号码;
仔细的同学们会发现，这个身份证号貌似不太正确，因为后四位变成了0;这时我们就需要手动修改一下后四位数字;
选中单元格，左侧会出现带有感叹号的按钮，点击后选择忽略错误，这时单元格左上方的小箭头就会消失，ok，修改完毕;
看过Excel中身份证号码显示格式错误的解决还看了：
1.excel身份证号码格式怎么显示
2.Excel中表格身份证显示不正确的解决方法
3.Excel表格如何显示身份证号码
4.excel表格打不开,显示格式错误的解决方法
5.Excel输入身份证号显示不全如何解决
6.Excel中输入身份证号后变成E+17格式的解决方法
7.Excel身份证号显示不全如何解决
8.excel表格身份证号乱码怎么处理。
EXCEL中你输入的公民身份号码正确吗？目录：1、输入错误自动红色显示提示2、15位身份证号码升位为18位公民身份号码3、自动生成出生日期和性别4、中华人民共和国国家标准GB 11643-1999 公民身份号码一、输入错误自动红色显示提示在企业报送的EXCEL电子表格中，经常遇到公民身份号码输入错误而给工作带来不必要的麻烦，降低了工作效率。
有没有办法在公民身份号码录入错误时进行自动提示呢？其实【中华人民共和国国家标准 GB 11643-1999】对公民身份号码第18位校验码已作了详细的规定，只是我们在电算工作中很少用到而已。
可用以下方法在EXCEL中录入公民身份号码错误时自动红字显示：选择需要录入公民身份号码的单元格（如A1），选择菜单：格式/条件格式。
①点击条件1左边的下拉箭头，选择公式，在右边的公式栏中输入以下公式：=AND(LEN(D4)<>0,LEN(D4)<>15,LEN(D4)<>18)接着单击格式，点击字体，颜色选择红色，确定。
②在条件格式中点添加，点击条件2左边的下拉箭头，选择公式，在右边的公式栏中输入以下公式：=MID("10X98765432",MOD(SUMPRODUCT(MID(D4,ROW($1:$17),1)*MOD(2^(18-ROW($1:$17)),11) ),11)+1,1)<>MID(D4,18,1)接着单击条件2中的格式，点击字体，颜色选择红色，确定，确定。
右键按住A1单元格右下角的小黑点，向下拖至所需要录入公民身份号码的所有单元格，松开右键，选择仅填充格式。
在接着的录入中，凡是输入不是15位或18位，或者输入18位错误的公民身份号码都会红色显示。
身份证重复显蓝色的公式：COUNTIF（$D$4:$D$65536,D4）>1 二、15位身份证号码升位为18位公民身份号码要使单元格中A1中的15位身份证号码升位为18位公民身份号码，只需在单元格A2中输入以下公式即可：=REPLACE(A1,7,,19)&MID("10X98765432",MOD(SUMPRODUCT(MID(REPLACE(A1,7,,19),ROW($1:$1 7),1)*MOD(2^(18-ROW($1:$17)),11)),11)+1,1)或=REPLACE(A1,7,,19)&LOOKUP(MOD(SUMPRODUCT(MID(REPLACE(A1,7,,19),ROW($1:$17),1)*MOD(2^(18-ROW($1:$17)),11)),11),{0,1,2,3,4,5,6,7,8,9,10},{1,0,"X",9,8,7,6,5,4,3,2})三、自动生成出生日期和性别如果单元格A1为身份证号码或公民身份号码，单元格A2为性别，单元格A3为出生日期，则操作如下：在单元格A2中输入以下公式，则会自动生成性别：=IF(LEN(A1)=18,IF(INT(MID(A1,17,1)/2)=MID(A1,17,1)/2,"女","男"),IF(LEN(A1)=15,IF(INT(MID(A1,15,1)/2)=MID(A1,15,1)/2,"女","男"),""))在单元格A3中输入以下公式，则会自动生成出生日期：=IF(LEN(A1)=18,DATE(MID(A1,7,4),MID(A1,11,2),MID(A1,13,2)),IF(LEN(A1)=15,DATE(MID(A1,7,2),MID(A1,9,2),MID(A1,11,2)),""))[顶] EXCEL中多条件求和、计数的4种方法EXCEL中多条件求和、计数的方法大致可归纳为4种：⒈自动筛选法⒉合并条件法⒊数组公式法⒋调用函数法先打开上面的工作表，分别用这4种方法对同时满足“A2：A15区域为A，B2：B15区域为10，C2：C15区域为Ⅰ”条件的E2：E15区域进行求和、计数。
身份证号码换算年龄、性别、出生日期的公式15位和18位的身份证号都合适的了。
设内容如下：........A列......B列......C列......D列....1 身份证号性别出生年月年龄2（输入身份证号）算年龄的：“=IF(OR(LEN(A2)=15,LEN(A2)=18),RIGHT(IF(IF(LEN(A2)=15, MID(A2,9,2),MID(A2,11,2) )*100+IF(LEN(A2)=15,MID(A2,11,2),MI D(A2,13,2))>MONTH(TODAY())*100+DAY(TODAY()),YEAR(TODAY())-IF(LEN(A2)=15,MID(A2,7,2),MID(A2,9,2))-1,YEAR(TODAY())-IF(L EN(A2)=15,MID(A2,7,2),MID(A2,9,2))),2),"证号错误")”算性别的：“=IF(LEN(A2)=15,(IF(OR(ABS(RIGHT(A2))=1,ABS(RIGHT(A2) )=3,ABS(RIGHT(A2))=5,A BS(RIGHT(A2))=7,ABS(RIGHT(A2))=9),"男","女")),(IF(OR(ABS(MID(A2,17,1))=1,ABS(MID(A2,17,1))=3,ABS(M ID(A2,17,1))=5,ABS(MID( A2,17,1))=7,ABS(MID(A2,17,1))=9),"男","女")))”算出生年月的：“=DATE(IF(LEN(E3)=15,MID(E3,7,2),MID(E3,9,2)),IF(LEN(E3) =15,MID(E3,9,2),MID(E3,11,2)),IF(LEN(E3)=15,MID(E3,11,2),MID(E3,13,2)))”只要把输入身份证号的单元格格式设为“文本”就是最关键的，准确。
在EXCEL2003中把数字转换成日期1。
假定A2为文本数字，B2存放转换后的日期。
请在B2输入公式：=DATE(MID(A2,1,4),MID(A2,5,2),MID(A2,7,2))=DATE(MID(H2,1,4),MID(H2,5,2),MID(H2,7,2))2。
把B2设置成你要的格式。
右击B2单元格-----选“设置单元格格式”----选“数字”下的“自定义”-----在“类型”框中输入“YYYY-MM-DD-”（引号不要输入）-----点“确定”。
格式为“**年**月”的：设A列是身份证号，可在B1输入：=IF(LEN(A1)=15,MID(A1,7,2)&"年"&MID(A1,9,2)&"月",MID(A1,9,2)&"年"&MID(A1,11,2)&"月")再将公式向下复制即可。
格式为“****年**月”的：=IF(LEN(A1)=15,"19"&MID(A1,7,2)&"年"&MID(A1,9,2)&"月","19"&MID(A1,9,2)&"年"&MID(A1,11,2)&"月")再将公式向下复制即可。
如果你再想限制身份证号输入错误，可用这个公式：=IF(OR(LEN(A1)=15,LEN(A1)=18),IF(LEN(A1)=15,MID(A1,7,2)&"年"&MID(A1,9,2)&"月",MID(A1,9,2)&"年"&MID(A1,11,2)&"月"),"证号错误")这样只要输入的证号不是15位或18位就提示了。
基于VBA实现身份证由15位到18位转换
刘韶华
【期刊名称】《电脑编程技巧与维护》
【年(卷),期】2012(000)012
【摘要】通过对身份证验证算法的分析,给出基于VBA编程解决Excel中身份证信息由15位到18位的转换方法.
【总页数】2页(P25-26)
【作者】刘韶华
【作者单位】哈尔滨高科技创业中心,哈尔滨150090
【正文语种】中文
【相关文献】
1.基于ExcelVba的身份证信息查询系统的设计与实现 [J], 黄钢;
2.基于VBA编程技术实现水文整编成果向基础水文数据库的转换与导入 [J], 张然
3.基于Excel VBA角度转换问题的算法研究及其实现 [J], 姚德生;赵淑湘
4.基于VBA的PPT文档转换为Word文档的研究与实现 [J], 张捷;封俊红
5.基于Auto CAD VBA实现最小二乘配置在坐标转换中的应用 [J], 马朝帅;黄明;郭胜利
因版权原因，仅展示原文概要，查看原文内容请购买。
