Excel解读身份证号码信息

第一篇：Excel解读身份证号码信息

Excel解读身份证号码信息

利用Excel自动解读身份证号码信息的方法。这个自动解读表可以将身份证表格中的出生日期、性别自动解读出来。如果您愿意，当然还可以用挖掘出来的信息做更多的数据统计和计算。

众所周知，15位（18位）身份证号码的第7—12位（14位）数字代表着持证人的出生年、月、日；第15位（17位）数字代表着持证人的性别（奇数为“男”，偶数为“女”）。下面，我们就利用Excel强大的函数功能，从身份证号码中将这些信息挖掘出来，显示在相应的单元格中。

本例涉及到的Excel函数如下：

1、DATE(year,month,day)

2、IF(Logical,Value_if_true,Value_if_false)

3、LEN(text)

4、MID(text,start_num,num_chars)

5、MOD(number,divisor)

在这里，我们假定身份证号码保存在C列（从C2单元格开始）中，性别和出生年、月、日分别保存在D列和E列中。

一、性别的判断

选中D2单元格，输入公式：

=IF(MOD(IF(LEN(C2)=15,MID(C2,15,1),MID(C2,17,1)),2)=0,“女”,“男”)或

=IF(MOD(IF(LEN(C7)=15,MID(C7,15,1),MID(C7,17,1)),2)=1,“男”,“女”)，输入完成后，按下“Enter”键进行确认，第1位持证人的性别则自动显示在D2单元格中。

上述第一个公式的含义分三层来理解：

①如果身份证号码是15位（LEN(C2)=15），则取其15位数字（MID(C2,15,1)），否则取其17位数字（MID(C2,17,1)）。

②然后求取出来的数值除2后的余数（MOD(IF(LEN(C2)=15,MID(C2,15,1),MID(C2,17,1)),2)）。

③如果（IF）余数为“0”（MOD(IF(LEN(C2)=15,MID(C2,15,1),MID(C2,17,1)),2)=0），则显示“女”，否则显示“男”。

再次选中D2单元格，用“填充柄”将上述公式复制到D列下面的单元格中，即可显示出其他持证人的性别。

二、显示出生年、月、日

选中E2单元格，输入公式：=IF(LEN(C2)=15,DATE(MID(C2,7,2),MID(C2,9,2),MID(C2,11,2)),DATE(MID(C2,7,4),MID(C2,11,2),MID(C2,13,2)))，输入完成后，按下“Enter”键进行确认，第1位持证人的出生年、月、日则自动显示在E2单元格中。

上述公式的含义如下：

如果（IF）身份证号码是15位（LEN(C2)=15），则取其7至8位（MID(C2,7,2)）、9至10位（MID(C2,9,2)）、11至12位（MID(C2,11,2)），并以日期的格式显示出来（DATE(MID(C2,7,2),MID(C2,9,2),MID(C2,11,2))）；

反之，取其7至10位（MID(C2,7,4)）、11至12位（MID(C2,11,2)）、13至14位（MID(C2,13,2)），再以日期的格式显示出来（DATE(MID(C2,7,4),MID(C2,11,2),MID(C2,13,2))）。

再次选中E2单元格，用“填充柄”将上述公式复制到E列下面的单元格中，即可显示出其他持证人的出生年、月、日。

选中E列中含有日期的单元格区域，执行“格式→单元格”命令，打开“单元格格式”对话框（如图2），切换到“格式”标签中（通常是默认的标签），先在“格式类别”下面选中“日期”选项，然后在“示例”下面选中一种适合国人习惯的日期样式，确定返回。

注意：如果不设置单元格格式，也是完全可以的，只不过显示出来的日期样式是“1976-12-17”。

演讲稿

尊敬的老师们，同学们下午好：

我是来自10级经济学（2）班的学习委，我叫张盼盼，很荣幸有这次机会和大家一起交流担任学习委员这一职务的经验。

转眼间大学生活已经过了一年多，在这一年多的时间里，我一直担任着学习委员这一职务。回望这一年多，自己走过的路，留下的或深或浅的足迹，不仅充满了欢愉，也充满了淡淡的苦涩。一年多的工作，让我学到了很多很多，下面将自己的工作经验和大家一起分享。

学习委员是班上的一个重要职位，在我当初当上它的时候，我就在想一定不要辜负老师及同学们我的信任和支持，一定要把工作做好。要认真负责，态度踏实，要有一定的组织，领导，执行能力，并且做事情要公平，公正，公开，积极落实学校学院的具体工作。作为一名合格的学习委员，要收集学生对老师的意见和老师的教学动态。在很多情况下，老师无法和那么多学生直接打交道，很多老师也无暇顾及那么多的学生，特别是大家刚进入大学，很多人一时还不适应老师的教学模式。学习委员是老师与学生之间沟通的一个桥梁，学习委员要及时地向老师提出同学们的建议和疑问，熟悉老师对学生的基本要求。再次，学习委员在学习上要做好模范带头作用，要有优异的成绩，当同学们向我提出问题时，基本上给同学一个正确的回复。

总之，在一学年的工作之中，我懂得如何落实各项工作，如何和班委有效地分工合作，如何和同学沟通交流并且提高大家的学习积极性。当然，我的工作还存在着很多不足之处。比日：有的时候得不到同学们的响应，同学们不积极主动支持我的工作；在收集同学们对自己工作意见方面做得不够，有些事情做错了，没有周围同学的提醒，自己也没有发觉等等。最严重的一次是，我没有把英语四六级报名的时间，地点通知到位，导致我们班有4名同学错过报名的时间。这次事使我懂得了做事要脚踏实地，不能马虎。

在这次的交流会中，我希望大家可以从中吸取一些好的经验，带动本班级的学习风气，同时也相信大家在大学毕业后找到好的工作。谢谢大家！

第二篇：身份证号码的解读教学心得（最终版）

身份证号码的解读教学心得

南安华侨中学傅镇清

一、学生的数学思想背景

本课题是在学生学会了用抽象的字母代替具体的数，也就是用符号表达某种对象，已经有一定的符号线的基础上开展的课题学习活动。

二、布置学生课外任务

每一位学生搜集10个或10个以上的身份证号码，并且尽可能多地向成人了解关于身份证号码的有关知识。

三、集体研究

在学生收集完身份证号码之后，提出如下问题：

1、你收集到的身份证号码是几位数的？

答：十五位或十八位。（这个问题比较简单，很快就可以回答）

2、我们班的同学每人都有一个座位号，它是几位数，是如何编排出来的？ 按顺序编码（一般无空码）

3、如果身份证号码也像我们的座位号，这样编排只要十位数也就够了。而为什么不这样做呢？请同学谈谈自己的看法。

这时同学的回答五花八门，主要是这样做的可能性小。因为人是不断在增减的，要对一个班（<70人）的同学编码相对比较容易，而要对全国十二亿多的人口排序那将是一个非常大的工程，而且编出来的这个序号意义不大，假如万一这张身份证丢失了要补办，那将又是一件麻烦的事情。

得出结论：身份证号码不能够象编座号这样编排，这样不科学。

4、身份证号码是如何对全国的人进行编码的呢，你们知道吗？请大家观察收集到的号码有什么异同点并谈谈自己的看法。

总结出：我们（学生）收集到的号码的前六位基本上相同，均为“350523”、“350583”或“350500”三种，中间六位（或八位）是该身份证持有者的生日码，后三位（或后四位）不太清楚是什么？但知道十五位男性公民他身份证号码的最后一位是奇数，女性公民她身份证号码的最后一位是偶数（十八位的则是倒数第二位），也就是说十五位的末位或十八位的倒数第二位用来辨性别。

这是同学对身份证号码的初步认识，也是比较容易获得的信息。

5、请同学思考一下前六位“350523”、“350583”、“350500”的意思是什么？猜猜看？培养同学的数学猜想能力。

生答：可能是表示地区的吧！

师问：那这是多大的一个范围，村、镇还是县呢？

同学沿着这个说法讨论我收集的是本村的有“350523”、“350583”，你收集到的也是你本村的也是“350523”和“350583”说明不是村至少是镇级，而某同学收集的又是另一个镇的号码显然也是“350523”或“350583”开头，说明这是县级或县以上级的地区，另一小部分同学收集到的泉州的号码是“350500”，这样就可以确定“350323”、“350583”同是表示南安。

讨论结束后，接下来老师给予肯定同学们的结果，解释这六位是用来表示地址简称地址码。把同学们已知的本泉州地区的几个县（区、市）地址码列出来，供同学参考，并解读这六位地址码前两个用以表示省或自治区、直辖市，也可以让同学考虑一下全国共有几个省、直辖市、自治区。用一位表示够吗？一般一个省、直辖市、自治区有几个地区用几位数表示

会比较科学，再接下来是地区内所辖的县或县级市，会有几个用几位数会比较科学？通过这样的诱导，就能比较自然地把地址码的编码方式和思想方法理清楚。为了更加好地让同学掌握这种思想可以举一下关于邮政编码方式的编码，让同学作类比学习，加以巩固，扫清同学的知识障碍。

6、在这样基础上提出如果只使用十二位或十四位（八位生日码）表示身份证号码即地址码+生日码好不好！请同学讨论一下这时同学会很快得出这样一定会存在很多重码。也就是同一个县级市同一天出生的婴儿一般不止一个。这时在后面就必须加一个顺序码加以区分。那用几位好呢？这个可以让同学提出各种假设，经讨论统一认识，三位就够了，而且可以用奇偶数来表示男、女，这样基本上也把整个身份证号码解读完毕，然后再把十八位身份证号码的最后一位校验码简单地介绍一下即可。

最后比较十五位与十八位号码的差别是十八位多一位“检验码”，还有出生年份用四位数，而十五位的是两位数。这时可问同学十五位的好呢？还是十八位的好呢？为什么要从十五位升格为十八位。可能同学一下子说不出十八位的好处。

这时老师可以问如果是单凭号码用十五位表示的一个百岁老人与一个零岁婴儿能否区分出来，而导出旧的十五位编码的的出生年份的表达缺陷。

8、最后我们平时使用的电话号码在编码上有规律吗？请你说一说看。

经过上述的巧妙地诱导，把同学的思维引到逐步解读身份证号码的问题上，培养了同学爱思考和增强了他们的“数感”及“符号感”，同时穿插着人文教育，培养同学良好的思想品德。推荐阅读：

计划书范文：http:///fanwen/jihuashu/

述职报告范文：http:///fanwen/shuzhi/

左眼皮跳是什么预兆：http://maren.bukeep.com

第三篇：身份证号码证明

公民身份证号码更正证明

本辖区公民吴彦（曾用名），女，汉族，河南省泌阳县泌水镇新村居委会住。

原使用的公民身份号码为：

系（重 / 错）号。根据《中华人民共和国居民身份证法》的有关规定及公民身份号码的编制原则，为确保公民身份证号码的准确性和唯一性，自2008 年月日起将原号码更正为：

特此证明

河南省泌阳县泌水派出所

2011 年9 月22 日

注：请为申请人吴彦办理公民身份号码变更的相关事宜。此件为原件，如需要请留存复印件。、

第四篇：身份证号码含义

新的身份证为18位，老的身份证是15位。关于身份证第18是怎么计算的，原理如下：根据〖中华人民共和国国家标准 GB 11643-1999〗中有关公民身份号码的规定，公民身份号码是特征组合码，由十七位数字本体码和一位数字校验码组成。

排列顺序从左至右依次为：六位数字地址码，八位数字出生日期码，三位数字顺序码和一位数字校验码。

地址码（身份证前六位）表示编码对象常住户口所在县(市、旗、区)的行政区划代码。（所有区域的编码可以到这个网站http:///tjbz/index.htm 查询到最新的县及县以上的行政编码资料。）

生日期码（身份证第七位到第十四位）表示编码对象出生的年、月、日，其中年份用四位数字表示，年、月、日之间不用分隔符。例如：1981年05月11日就用19810511表示。

顺序码（身份证第十五位到十七位）为同一地址码所标识的区域范围内，对同年、月、日出生的人员编定的顺序号。其中第十七位奇数分给男性，偶数分给女性。

校验码（身份证最后一位）是根据前面十七位数字码，按照ISO 7064:1983.MOD 11-2校验码计算出来的检验码。第十八位数字的计算方法为：

1.将前面的身份证号码17位数分别乘以不同的系数。从第一位到第十七位的系数分别为: 7 9 10 5 8 4 2 1 6 3 7 9 10 5 8 4 2

2.将这17位数字和系数相乘的结果相加。

3.用加出来和除以11，看余数是多少？

4余数只可能有0 1 2 3 4 5 6 7 8 9 10这11个数字。其分别对应的最后一位身份证的号码为1 0 X 9 8 7 6 5 4 3 2。

5.通过上面得知如果余数是2，就会在身份证的第18位数字上出现罗马数字的Ⅹ。如果余数是10，身份证的最后一位号码就是2。

例如：某男性的身份证号码是***01X。我们要看看这个身份证是不是合法的身份证。首先：我们得出，前17位的乘积和是189 然后：用189除以11得出的结果是17 + 2/11，也就是说余数是2。最后：通过对应规则就可以知道余数2对应的数字是x。所以，这是一个合格的身份证号码。例如：某男性的身份证号码是***01X。我们要看看这个身份证是不是合法的身份证。首先：我们得出，前17位的乘积和是189 然后：用189除以11得出的结果是17 + 2/11，也就是说余数是

2。最后：通过对应规则就可以知道余数2对应的数字是x。所以，这是一个合格的身份证号码。

第五篇：身份证号码函数

用vlookup函数，及其他各项功能，通过身份证号得出了出生日期、性别、户籍所在地等多项信息，并加入了身份证号正确性验证机制和15位、18位号码的转换功能。

详细制作步骤

要想了解身份证的秘密，首先让我们了解一下我国现行的公民身份证标准。1.我国现行使用公民身份证号码有两种标准。2.15位身份证号码（六位数字地址码，六位数字出生日期码，三位数字顺序码）。

3.18位身份证号码（六位数字地址码，八位数字出生日期码，三位数字顺序码和

一位数字校验码）。

4.5.地址码表示编码对象常住户口所在县(市、旗、区)的行政区划代码。

6.日期码表示编码对象出生的年、月、日，其中年份用四位数字表示，年、月、日之间不用分隔符。

7.顺序码表示同一范围内对同年、月、日出生的人员编定的顺序号。奇数分给男性，偶数分给女性。

8.校验码是根据前面十七位数字码计算出来的检验码。

复制代码

了解了以上知识后，让我们来制作Excel表。

1.新建一个Excel文档，2.将Sheet1重命名为“身份证信息”，在第1行各列中依次输入“身份证号码、十五位身份证号码、十八位身份证号码、性别、出生日期、年龄、省份、市、区县”等文字，并将“出生日期”列设置为日期（yyyy.mm.dd）格式，其余列

设置为文本格式。

3.将Sheet2重命名为“区域信息”，从国家统计局下载到最新县及县以上行政

区划代码，经整理后分别导入到“区域信息”的A、B两列中，以便查询。

复制代码

到此，Excel表框架搭建完成，我们来写入函数完成查询。

第一步 判断号码是否为正确的身份证号

一个正确的身份证号码，要符合以下几个标准：

①应为15位或18位；

②要包含数字（0－9）或字符（X）；

③18位身份证号最后一位是真正的校验码。

如果上面三个条件都满足，则是正确的身份证号。

我们利用Excel单元格的“有效性”限制来完成校验。

具体方法是选择“身份证信息”表，全选“身份证号码”列，选择“数据－有效性”菜单命令，在弹出的窗口“设置”标签中，将有效性条件的“允许”设置为“自定义”、“公式”

设置为

＝

OR(LEN(A2)=15,IF(LEN(A2)=18,MID(“10X98765432”,MOD(SUM(MID(A2,ROW(INDIRECT(“1:17”)),1)*2^(18-ROW(INDIRECT(“1:17”)))),11)+1,1)=RIGHT(A2)))，如图一。

在“出错警告”标签中，设置出错提示信息，如图二。

第二步 15位和18位身份证号码的互换

具体方法是选择“身份证信息”表，在B2单元格中输入

=IF(LEN(A2)=15,A2,LEFT(REPLACE(A2,7,2,),15))；

在C2单元格中输入

{=IF(LEN(A3)=15,REPLACE(A3,7，19)&MID(“10X98765432”,MOD(SUM(MID(REPLACE(A3,7，19),ROW(INDIRECT(“1:17”)),1)*2^(18-ROW(INDIRECT(“1:17”)))),11)+1,1),A3)} 这里要注意是数组！

第三步 判断性别

如果是15位的，则取最后一位；

如果是18位的号，则取倒数第二位；

最后判断该数值的奇偶性即可。

具体方法是选择“身份证信息”表，在D2单元格中输入

=IF(A2<>“",IF(MOD(RIGHT(LEFT(A2,17)),2),”男“,”女“),)

第四步 判断出生日期

如果是15位的，则取第7至12位，在年份数前加上“19”；

如果是18位的，则直接取第7至14位；

具体方法是选择“身份证信息”表，在E2单元格中输入

=IF(A2<>”“,TEXT((LEN(A2)=15)*19&MID(A2,7,6+(LEN(A2)=18)*2),”#-00-00“)+0,)

第五步 判断年龄

这里利用一个Excel的隐藏函数 DATEDIF（）

基本语法： =DATEDIF(开始日期，结束日期，单位代码），用“Y”来表示年份。具体方法是选择“身份证信息”表，在F2单元格中输入

=IF(A2<>”“,DATEDIF(TEXT((LEN(A2)=15)*19&MID(A2,7,6+(LEN(A2)=18)*2),”#-00-00“),TODAY(),”Y“),)

第六步 判断籍贯，包括省份、市、区县

这是本期比赛最难的一部分，关键在于如何多重判断省份、市、区县信息。

首先要知道地址码的构成。代码前两位是省或直辖市代码，中间两位是市代码，最后两位是区县代码。

其次是数据存放。“区域信息”表中已经存放了查询所需的数据，将数据区命名为“Code”以便查询。如图三

最后利用VLOOKUP函数来查询数据。

基本语法：＝VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

具体方法是选择“身份证信息”表，在G2单元格中输入=IF(A2<>”“,VLOOKUP(LEFT(A2,2)&”0000“,code,2,),)

在G2单元格中输入=IF(A2<>”“,VLOOKUP(LEFT(A2,4)&”00“,code,2,),)

在G2单元格中输入=IF(A2<>”",VLOOKUP(LEFT(A2,6),code,2,),)

最后一步 根据需要拖拽

刚才所写的公式只是在第2行写入的，大家可以根据需要向下拖拽，具体方法略去。