7组Excel公式，提取身份证号中的7类信息，直接粘贴就能用

身份证号码，每个人都有，相信大家都不陌生，但是它是如何构成的，相信很多人都不知道。今天就跟大家分享下身份证号码的构成方式，以及我们使用Excel表格可以提取到哪些信息，如下图所示，我们一共可以获取6种信息

一、身份证号码的构成

籍贯代码：前6位表示籍贯代码，1-2位表示省份，3-4位表示城市，5-6位表示区县

出生日期代码：7-14位是每个人的出生日期，年份占4位，月份与天数各占2位

顺序代码：15-17为顺序码，15-16为派出所代码，17位是性别代码，奇数是男性，偶数是女性

校验码：第18位，用于检验身份证号码是否正确

二、判断身份证号码是否正确

公式：=IF(IF(RIGHT(A2,1)=”X”,”X”,–RIGHT(A2,1))=VLOOKUP(MOD(SUMPRODUCT(–MID(A2,{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17},1),{7;9;10;5;8;4;2;1;6;3;7;9;10;5;8;4;2}),11),{0,1;1,0;2,”X”;3,9;4,8;5,7;6,6;7,5;8,4;9,3;10,2},2,0),”正确”,”错误”)

使用方法：粘贴公式，按下快捷键Ctrl+Shift+回车填充公式。将公式中的三个A2替换为你表格中的号码位置即可，人事行政的必备公式

三、计算籍贯

计算籍贯关键是需要有一个对照表，这个很多大家可以搜下，获得对照表后，直接将公式设置为：=VLOOKUP(LEFT(A3,6),E:F,2,0)即可

使用方法：将A3更改你表格中的单元格位置，E:F更改为你表格中对照表的位置即可

四、提取出生日期

提取出生日期主要是将号码中的7-14位提取出来，在这里我们利用mid函数将其提取出来，然后再利用text将提取出来的一串数字设置为日期的显示格式。

公式为：=TEXT(MID(A3,7,8),”0000-00-00″)

使用方法：复制粘贴公式，直接更改A3为你表格中的单元格位置即可

五、计算年龄

这个主要是利用datedif函数来计算出生日期与今天的差值

公式：=DATEDIF(TEXT(MID(A3,7,8),”0-00-00″),TODAY(),”Y”)

使用方法：与【提取出生日期】的使用方法相同

六、计算性别

计算性别主要是判断第17位的奇偶性，如果是奇数则表示为男性，如果是偶数则表示为女性。在这里我们用mid函数将第17位提取出来，利用iseven判断是否为偶数，最后利用IF函数输出性别。

公式：=IF(ISEVEN(MID(A3,17,1)),”女”,”男”)

使用方法：粘贴公式后，更改A3的位置即可

七、提取星座

星座主要是跟月份与天数有关，所以需要将其提取出来，使用的还是mid函数，这个公式比较长是因为将查找表直接放在了函数中，计算过程并不复杂，大家直接使用即可

公式：=LOOKUP(–MID(A3,11,4),{100;120;219;321;421;521;622;723;823;923;1023;1122;1222},{“摩羯座”;”水瓶座”;”双鱼座”;”白羊座”;”金牛座”;”双子座”;”巨蟹座”;”狮子座”;”处女座”;”天秤座”;”天蝎座”;”射手座”;”摩羯座”})

使用方法：粘贴公式，更改A3为你表中的单元格位置即可，需要按下Ctrl+Shift+回车三键填充公式，因为其是一个数组公式