《利用excel提取身份证号中的信息》由会员分享，可在线阅读，更多相关《利用excel提取身份证号中的信息（87页珍藏版）》请在金锄头文库上搜索。
1、Excel 应应用用Excel 利用身份利用身份证证号提取出生日期号提取出生日期性性别别籍籍贯贯2011.10 月月- 1 -目目录录一、身份证号码中的信息.3二、用 EXCEL 算工龄.3劳动合同期限的自动生成和提前 30 天定期提醒功能设置.5三、籍贯显示.7四、地区编码对照表.71一、身份一、身份证证号号码码中的信息中的信息（1）前 1、2 位数字表示：所在省份的代码； （2）第 3、4 位数字表示：所在城市的代码； （3）第 5、6 位数字表示：所在区县的代码； （4）第 714 位数字表示：出生年、月、日； （5）第 15、16 位数字表示：所在地的派出所的代码； （6）第 17 位
2、数字表示性别：奇数表示男性，偶数表示女性； （7）第 18 位数字是校检码：用来检验身份证的正确性。校检码可以是 010 的数字，10 用 x 表示。二、用二、用 EXCEL 算工算工龄龄首先我们打开一个新的 Excel 表建立一个人事信息库框架，信息项目的设置您可根据本单位实际情况而定。如按 ABCD 顺序分别是“编号、姓名、部门、职务、身份证号码、性别、出生年月、年龄、民族、籍贯、户口所在地、毕业院校、专业、学历、学位、入职日期、试用期到期时间、提前七天提醒、劳动合同到期时间、提前三十天提醒、续约到期时间”接下来请您不要急着录入人员信息，我们要对一些信息项进行函数设置，以便系统可以自动生成
3、相关信息，这会使我们的工作产生事半功倍的效果。 2性别、出生月日、年龄的自动填充功能设置性别、出生月日、年龄的自动填充功能设置 我们先对“性别”“出生年月”“年龄”进行函数设置。当我们输入某人身份证号码时，系统便会自动生成“性别”，“出生年月”及“年龄”，这样就减少了我们录入的工作量。请分别选择性别、出生月日、年龄信息项单元格输入下列公式: (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 中从第
4、 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(E
5、3,7,4),MID(E3,11,2),MID(E3,13,2) 含义：DATE(YEAR,MONTH,DAY); “MID(E3,7,4)”表示在 E3 中从第 7 个字符开始连续取 4 个字符表示年，用类似的表示方法一个人的出生年月日便可以通过函数设置表示出来，如果为了看起来方便，我们可以将单元格格式设置成年、月、日的日期格式，这样显示的结果会非常容易理解。 (3)年龄: =DATEDIF(G3,TODAY(),“Y“) （）=ROUND(TODAY()-A1)3/365.25,0)含义：“DATEDIF(date1,date2,“Y”)”表示两个日期的差值; “TODAY()”表示系统自
6、带的日期即显示当日日期; “DATEDIF(G3,TODAY(),“Y“)”表示今天的日期与 G3 所表示的出生月日之间的年份差值，这样一个人的年龄就会容易的显示出来了。 劳动合同期限的自动生成和提前劳动合同期限的自动生成和提前 30 天定期提醒功能设置天定期提醒功能设置 劳动合同管理也是人事管理中不可缺少的一部分，特别是劳动合同到期续签问题更是不可忽视。如果由于我们的人为原因造成了劳动合同漏签、延期签订等问题可能会给公司以及员工个人带来一定的损失。从一个人入职签订试用期合同开始，再到签订正式劳动合同以及后来的续签劳动合同，一系列的日期如何能够让系统自动生成，并且形成系统提前 30 天自动提醒
7、我们的功能呢？这需要对一些信息项进行函数设置。 (1)试用期到期时间: =DATE(YEAR(P3),MONTH(P3)+3,DAY(P3)-1) 含义：“DATE(YEAR(),MONTH(),DAY()”显示指定日期; 在这里我们假设试用期为 3 个月，我们需要在 Q3 单元格中输入上述公式，其中 MONTH(P3)+3 表示在此人入职时间月的基础上增加三个月。而 DAY(P3)-1 是根据劳动合同签订为整年正月而设置的。比如2005 年 11 月 6 日到 2006 年 11 月 5 日为一个劳动合同签订期。 (2)劳动合同到期时间: =DATE(YEAR(P3)+1,MONTH(P3)
8、,DAY(P3)-1) 我们同样采用上述函数的设置方法。这里我们假设劳动合同期限为 14年，则我们需要设置成 YEAR(P3)+1，另外这个数值依然以入职日期为计算根据，所以天数上还要设置成 DAY(P3)-1 的格式。 (3)续签合同到期时间: =DATE(YEAR(S3)+1,MONTH(S3),DAY(S3) 这里需要注意的是续签合同计算是以前份合同签订到期日期为根据的，所以只在前一份合同到期时间的基础上增加 1 年即可，无需天数上减 1。 (4)试用期提前 7 天提醒: =IF(DATEDIF(TODAY(),Q3,“d“)=7,“试用期快结束了“,“) 这里用到了 DATEDIF 函
9、数，表示两个日期差值，但是需要注意的是，我们要表示提前 7 天提醒，所以，将 TODAY()函数写到试用期时间前面即 TODAY(),Q3 而不能表示成 Q3，TODAY()。其中“d”表示两个日期天数差值。我们用 IF()函数来表示显示要求，那么这个函数设置的含义为:如果差值为 7 则显示“试用期快结束了”否则不显示信息，在编辑函数时用“”表示不显示任何信息。 (5)提前 30 天提醒: =IF(DATEDIF(TODAY(),S4,“m“)=1,“该签合同了“,“) 函数设置方法同上，其含义是两个日期相差 1 个月则显示“该签合同了”否则不显示任何信息。这里没有设置成相差 30 天提醒是因
10、为考虑到设置成月更利于我们人事工作的操作。同样需要注意的是不要将显示“今天日期”函数与显示“合同到期日期”函数顺序颠倒。其中“m“表示月的含义。5三、籍三、籍贯显贯显示示身份证号的前 6 位为地区编码，如果你有一份地区与编码对照表，就可以用 VLOOKUP 函数处理。如身份证号码在 SHEET1 的 A 列，SHEET2 为地区与编码对照表，A 列为编码，B 列为地区，在 SHEET1的 2 输入公式=VLOOKUP（-LEFT（A2，6），SHEET2！A：B，2，0），下拉。如果 SHEET2 的编码是文本格式，可以将公式中的-去掉。四、地区四、地区编码对编码对照表照表110000 北京市
11、 110100 北京市市辖区 110101 北京市东城区 110102 北京市西城区 110103 北京市崇文区 110104 北京市宣武区 110105 北京市朝阳区 110106 北京市丰台区 110107 北京市石景山区 110108 北京市海淀区 110109 北京市门头沟区 110111 北京市房山区 110112 北京市通州区 110113 北京市顺义区 110200 北京市县 110221 北京市昌平县 110224 北京市大兴县 110226 北京市平谷县 110227 北京市怀柔县 110228 北京市密云县 110229 北京市延庆县 120000 天津市 120100 天
12、津市市辖区 120101 天津市和平区 120102 天津市河东区 120103 天津市河西区 6120104 天津市南开区 120105 天津市河北区 120106 天津市红桥区 120107 天津市塘沽区 120108 天津市汉沽区 120109 天津市大港区 120110 天津市东丽区 120111 天津市西青区 120112 天津市津南区 120113 天津市北辰区 120200 天津市县 120221 天津市宁河县 120222 天津市武清县 120223 天津市静海县 120224 天津市宝坻县 120225 天津市蓟县 130000 河北省 130100 河北省石家庄市 1301
13、01 河北省石家庄市市辖区 130102 河北省石家庄市长安区 130103 河北省石家庄市桥东区 130104 河北省石家庄市桥西区 130105 河北省石家庄市新华区 130106 河北省石家庄市郊区 130107 河北省石家庄市井陉矿区 130121 河北省石家庄市井陉县 130123 河北省石家庄市正定县 130124 河北省石家庄市栾城县 130125 河北省石家庄市行唐县 130126 河北省石家庄市灵寿县 130127 河北省石家庄市高邑县 130128 河北省石家庄市深泽县 130129 河北省石家庄市赞皇县 130130 河北省石家庄市无极县 130131 河北省石家庄市平山
14、县 130132 河北省石家庄市元氏县 130133 河北省石家庄市赵县 130181 河北省石家庄市辛集市 130182 河北省石家庄市藁城市 130183 河北省石家庄市晋州市 130184 河北省石家庄市新乐市 130185 河北省石家庄市鹿泉市 130200 河北省唐山市 130201 河北省唐山市市辖区 7130202 河北省唐山市路南区 130203 河北省唐山市路北区 130204 河北省唐山市古冶区 130205 河北省唐山市开平区 130206 河北省唐山市新区 130221 河北省唐山市丰润县 130223 河北省唐山市滦县 130224 河北省唐山市滦南县 130225
15、河北省唐山市乐亭县 130227 河北省唐山市迁西县 130229 河北省唐山市玉田县 130230 河北省唐山市唐海县 130281 河北省唐山市遵化市 130282 河北省唐山市丰南市 130283 河北省唐山市迁安市 130300 河北省秦皇岛市秦皇岛市 130301 河北省秦皇岛市市辖区 130302 河北省秦皇岛市海港区 130303 河北省秦皇岛市山海关区 130304 河北省秦皇岛市北戴河区 130321 河北省秦皇岛市青龙满族自治县 130322 河北省秦皇岛市昌黎县 130323 河北省秦皇岛市抚宁县 130324 河北省秦皇岛市卢龙县 130400 河北省邯郸市邯郸市 130401 河北省邯郸市市辖区 130402 河北省邯郸市邯山区
