论文部分内容阅读
摘 要：本篇文章介绍了用EXCEL软件，借助于国家统计局网络资料，来制作挖掘身份证号码隐藏信息的excel表格。
关键词：EXCEL；身份证代码；自动生成
中图分类号：TP3 文献标识码：A 文章编号：1009-0118（2011）-03-0023-01
一、引言
我们每一个人都有唯一的身份标示——身份证号码，其中包含了我们的原始户籍所在地、出生日期、身份顺序码以及18位身份证所带的有效验证码。对于这些信息我们可以充分利用，特别是对于一个人事管理人员特别有用，下面我们就从网上下载资源开始，来对制作这一EXCEL表做详细的介绍。
二、制作过程
（一）制作身份代码excel数据源
首先进入国家统计局网站全国县及县以上行政区划代码表（国家标准GB T 2260-1999），选中所有代码及名称复制放到EXCEL工作簿sheet1表中，选中“匹配目标格式”，然后点击“编辑”菜单栏，替换命令，将“ ”（引号内为空格）替换为空，删除掉所有的空格；再用同样的方法删除掉所有的“00”，对工作表中的“A列”按代码升序排列后生成一个新表（见图一，注：为了方便观看，图中隐藏了一部分数据）；在表中“c516”的单元格内输入嵌套函数“=VLOOKUP(VALUE(LEFT(A516,2)),A:B,2)&VLOOKUP(VALUE(LEFT(A516,4)),A:B,2)&B516”，然后复制“c516”单元格，在名称框中输入“C517:C3661”并回车，此时“C517:C3661”就会被选中，粘贴后就会发现县区级单位前加上了上一级的行政区名称；再在名称框中输入“A516:A3661,C516:C3661”并回车选中，将其复制后选择性粘贴数值（一定要是选择性粘贴）至sheet2表中；使用替换命令将“市县”替换成“市”。这样工作表sheet2就成为了可以用来当做数据源的身份证代码库。
（二）制作身份证信息表
新建一个“身份证信息表.xls”，“sheet1、sheet2、sheet3”分别重命名为：“身份证信息、身份证代码库、身份证校验及转换表”。我们主要利用“身份证代码库、身份证校验及转换表”这两个工作表作为数据源来使用。
将已做好的原工作簿中“sheet2”工作表内容复制到“身份证信息表.xls”的“身份证代码库”工作表中。
打开“身份证信息”工作表做好如图五的表头。从这个表头可以看出该excel表格的主要功能是挖掘输入的身份证号码的一些潜在信息，比如将15位身份证号码转换为18位新身份证号码，通过函数运算来对输入的18位身份证号码进行有效性验证，自动判断性别、出生日期、年龄、距离生日的天数以及原始的户口所在地。
从“A1”至“I1”区域分别填的是“姓名、身份证号码、对应的18位身份证号码 (可修正末一位)、身份证有效性验证（18位有效）、性别、出生日期、年龄、距下个生日的天数、原始户籍所在地”。
打开“身份证校验及转换表”工作表，制作如图六的表头。这个表主要用来对15位身份证的转换以及18位的校验进行函数计算。
从“A1”至“F1”区域分别填的是“输入的身份证号码、15位身份证转换为17位 (不含校验码)、15位身份证转换为18位、校验中间数、校验码、校验18位身份证结果”。
（三）填写函数
下一步为表格中的一些单元格填入函数和公式，这里的公式全是excel软件自带的公式，关于公式的用法就不一一做介绍了。
“身份证信息”工作表中：
选中C2:I2，复制后，在名称框中输入C3:I1000，回车选定后，在选中区域右键粘贴，就会把函数填充至1000行；设置B2:B1000单元格区域为文本格式；设置F2:F1000单元格区域为日期格式；设置G2:G1000为数值格式，并将小数位数设为0。
“身份证校验及转换表”工作表中：
同样，选中A2:F2，复制后，在名称框中输入A3:F1000，回车选定后，在选中区域右键粘贴，就会把函数填充至1000行。
（四）表格的编辑
可以利用条件格式调整“身份证信息”工作表中的B2:I1000单元格区域中的“验证有误！”、“身份证号码有误！”、“日期代码有误！”这些出错提示以鲜艳的色调或独特的文字格式突出显示。
还可以利用工作表保护功能保护那些输入函数和公式的单元格区域，避免出错。
三、功能及特点
（一）避免因为输入问题而导致身份证号码出错
（二）可以将15位身份证号码转换为18位
（三）兼容性好，可以在许多数平台使用
（四）自动生成籍贯等信息，提高办公效率
（五）实现容易，稍微有点计算机基础操作能力都可以制作
参考文献：
[1]国家统计局网站全国县及县以上行政区划代码表.(国家标准GBT2260-1999).
关键词：EXCEL；身份证代码；自动生成
中图分类号：TP3 文献标识码：A 文章编号：1009-0118（2011）-03-0023-01
一、引言
我们每一个人都有唯一的身份标示——身份证号码，其中包含了我们的原始户籍所在地、出生日期、身份顺序码以及18位身份证所带的有效验证码。对于这些信息我们可以充分利用，特别是对于一个人事管理人员特别有用，下面我们就从网上下载资源开始，来对制作这一EXCEL表做详细的介绍。
二、制作过程
（一）制作身份代码excel数据源
首先进入国家统计局网站全国县及县以上行政区划代码表（国家标准GB T 2260-1999），选中所有代码及名称复制放到EXCEL工作簿sheet1表中，选中“匹配目标格式”，然后点击“编辑”菜单栏，替换命令，将“ ”（引号内为空格）替换为空，删除掉所有的空格；再用同样的方法删除掉所有的“00”，对工作表中的“A列”按代码升序排列后生成一个新表（见图一，注：为了方便观看，图中隐藏了一部分数据）；在表中“c516”的单元格内输入嵌套函数“=VLOOKUP(VALUE(LEFT(A516,2)),A:B,2)&VLOOKUP(VALUE(LEFT(A516,4)),A:B,2)&B516”，然后复制“c516”单元格，在名称框中输入“C517:C3661”并回车，此时“C517:C3661”就会被选中，粘贴后就会发现县区级单位前加上了上一级的行政区名称；再在名称框中输入“A516:A3661,C516:C3661”并回车选中，将其复制后选择性粘贴数值（一定要是选择性粘贴）至sheet2表中；使用替换命令将“市县”替换成“市”。这样工作表sheet2就成为了可以用来当做数据源的身份证代码库。
（二）制作身份证信息表
新建一个“身份证信息表.xls”，“sheet1、sheet2、sheet3”分别重命名为：“身份证信息、身份证代码库、身份证校验及转换表”。我们主要利用“身份证代码库、身份证校验及转换表”这两个工作表作为数据源来使用。
将已做好的原工作簿中“sheet2”工作表内容复制到“身份证信息表.xls”的“身份证代码库”工作表中。
打开“身份证信息”工作表做好如图五的表头。从这个表头可以看出该excel表格的主要功能是挖掘输入的身份证号码的一些潜在信息，比如将15位身份证号码转换为18位新身份证号码，通过函数运算来对输入的18位身份证号码进行有效性验证，自动判断性别、出生日期、年龄、距离生日的天数以及原始的户口所在地。
从“A1”至“I1”区域分别填的是“姓名、身份证号码、对应的18位身份证号码 (可修正末一位)、身份证有效性验证（18位有效）、性别、出生日期、年龄、距下个生日的天数、原始户籍所在地”。
打开“身份证校验及转换表”工作表，制作如图六的表头。这个表主要用来对15位身份证的转换以及18位的校验进行函数计算。
从“A1”至“F1”区域分别填的是“输入的身份证号码、15位身份证转换为17位 (不含校验码)、15位身份证转换为18位、校验中间数、校验码、校验18位身份证结果”。
（三）填写函数
下一步为表格中的一些单元格填入函数和公式，这里的公式全是excel软件自带的公式，关于公式的用法就不一一做介绍了。
“身份证信息”工作表中：
选中C2:I2，复制后，在名称框中输入C3:I1000，回车选定后，在选中区域右键粘贴，就会把函数填充至1000行；设置B2:B1000单元格区域为文本格式；设置F2:F1000单元格区域为日期格式；设置G2:G1000为数值格式，并将小数位数设为0。
“身份证校验及转换表”工作表中：
同样，选中A2:F2，复制后，在名称框中输入A3:F1000，回车选定后，在选中区域右键粘贴，就会把函数填充至1000行。
（四）表格的编辑
可以利用条件格式调整“身份证信息”工作表中的B2:I1000单元格区域中的“验证有误！”、“身份证号码有误！”、“日期代码有误！”这些出错提示以鲜艳的色调或独特的文字格式突出显示。
还可以利用工作表保护功能保护那些输入函数和公式的单元格区域，避免出错。
三、功能及特点
（一）避免因为输入问题而导致身份证号码出错
（二）可以将15位身份证号码转换为18位
（三）兼容性好，可以在许多数平台使用
（四）自动生成籍贯等信息，提高办公效率
（五）实现容易，稍微有点计算机基础操作能力都可以制作
参考文献：
[1]国家统计局网站全国县及县以上行政区划代码表.(国家标准GBT2260-1999).