公民身份号码是特征组合码，由十七位数字本体码和一位数字校验码组成。排列顺序从左至右依次为：六位数字地址码，八位数字出生日期码，两位数字顺序码，一位性别属性码，最后一位是数字校验码。

根据上面的说明，我们就可以分步在excel中如何搞定这些操作。

一、六位数字地址码

1、提取行政区划代码

=LEFT(A3,6)
=MID(A3,1,6)

上面提供了两个函数，根据自己的习惯与掌握的熟练程度，酌情使用。

2、准备行政区划代码查询表

在查找之前，要先准备好行政区划代码查询表。

2022年中华人民共和国县以上行政区划代码（中华人民共和国民政部官网）
网址：https://www.mca.gov.cn/article/sj/xzqh/2022/202201xzqh.html

为了演示，我准备了一部分

3、根据代码找出县级行政区名称

这里我们用了lookup函数来查询，但明显是出错了，没有找出正确结果。

这是因为，我们通过第一步文本函数截取出来的前6位代码，是文本格式，而后面F列的区划代码是数字格式，要想得到正确结果，必须把两列数据转换成同一种格式。我们这里通过将截取出来的结果*1的方法变成数字格式。

标识1：我们可以看出，经过将文本*1转换后，能得出正确结果。

标识2：产生了明显的错误结果，这是因为F列中没有370102、512501的代码可供查询。这是因为lookup自带模糊匹配属性，从查询表里找到了比查询值小的，且最接近查询值的值，从而返回它的对应结果。（这里有点绕，对初学者来说，要仔细体会）

标识3：F列区划代码要按照升序排列。

既然结果不准确，是不是就不能用lookup来查询了呢？也不是的，因为lookup也有精确查找模式。

我们也可以用最熟悉的vlookup来做：

当然我们也可以用index+match、offset+match等方法来输出结果，请自行尝试。如果不会写这个公式，可下载本文附件，我将附上我的写法。

4、将两步操作用到的函数嵌套起来

这一步，对于新手来说，会有些困难，熟练之后就会好了，简单说就是一个函数参数的等量代换。

我们先把第二步公式复制过来，会发现此公式中用到了第一步的结果，也就是C3，我们把C3换成C3单元格中的公式即可。

二、八位出生年月日

1、mid截取出生日期

这里通过mid截取出来的出生日期是一个文本格式，从外观字样上看起来像是一个日期格式的数据，但与真正的日期格式数据本质上不同，不能用平常的日期函数计算出正确结果。要想转化成真正的日期格式，通常有两种方法：

=TEXT(MID(A3,7,8),"0000\/00\/00")或者=TEXT(MID(A3,7,8),"0000!/00!/00")

=DATE(MID(A3,7,4),MID(A3,11,2),MID(A3,13,2))这种方法适合新手掌握

2、计算年龄（计算工龄同此法）

对于年龄计算要求不精确时（俗称虚岁），可以用这个公式

=2023-MID(A3,7,4)

要求精确时（俗称周岁），用datedif这个隐藏函数，这个函数在excel公式里没有相关提示，白话解释是：

datedif(起始日期,结束日期,"显示方式")，其中，显示方式一般有“y”、“m”、“d”

=DATEDIF(TEXT(MID(A3,7,8),"0000!/00!/00"),TODAY(),"y")

三、身份证号中的性别判断

我们在文章开头就提到，身份证号中有一位是性别属性码，也就是第17位。但网上也经常看到用第15~17位来判断的，这从逻辑上是不对的，但从数学角度来看，又没有错，你其实可以用包含第17位在内的前边任意字符串来判断。我们这里用正规的解法。

1、提取第17位身份证号

同样可以用=mid(a3,17,1)截取，由于上面提到过多次，这里我用另一个方法提取，异曲同工。

我们先截取身份证号的后2位

再截取第1位，注意这里省略了left的第二参数1。

要注意的是：这里的结果是文本！文本！文本！，重要的事情说三遍。

2、判断奇偶性

我们通过求整除2的余数，可见结果为1的为奇数，为0的为偶数。

有时，我们也会用专门的奇偶判断函数来判断，如isodd,判断是否为奇数：

相对的，还有一个iseven,判断是否为偶数。可自行验证。

3、判断性别

=IF(MOD(LEFT(RIGHT(A3,2)),2),"男","女")

上述函数公式中，if的第一参数直接引用了mod的计算结果，并没有进一步进行判断，这是利用了mod结果非0即1的特性。而在函数中，1为真true,0为假false。

四、身份证号码校验

身份证号码最后一位：根据前面十七位数字码，按照【ISO 7064:1983.MOD 11-2校验码】计算出来的检验码。如果某人的尾号是0-9，都不会出现X，但如果尾号是10，那么就得用X来代替，X是罗马数字的10，用X来代替10，可以保证公民的身份证符合国家标准。

校验方法的文字描述是这样的：将身份证号码1-17位数字，分别乘以给定数字后求和，再除以11，余数对应相应的字符

1、先按位提取身份证号的前17位：

=MID(A2,ROW(1:17),1)

按位提取身份证号前17位

2、将提取出来的结果（要注意的是，这里通过mid函数提取出来的结果是文本类型的数据，但与之对应系数是数值型，两者相乘结果为数值型）与系数相乘（这个系数没有规律，可以从网上查出来）：

=MID(A2,ROW(1:17),1)*E2:E18

有辅助列

3、上面的公式看起来简洁，但是他得依附辅助列E2:E18存在，如果不用辅助列的话，我们可以选定上面公式中的E2:E18，按F9输出结果后，回车，就可以删除辅助列了。

=MID(A2,ROW(1:17),1)*{7;9;10;5;8;4;2;1;6;3;7;9;10;5;8;4;2}

无辅助列

我们可以看出有无辅助列，输出结果是一样的。

4、将c中结果求和

=SUM(MID(A2,ROW(1:17),1)*{7;9;10;5;8;4;2;1;6;3;7;9;10;5;8;4;2})

5、利用mod函数，除以11求余数

=MOD(SUM(MID(A2,ROW(1:17),1)*{7;9;10;5;8;4;2;1;6;3;7;9;10;5;8;4;2}),11)

6、我们肉眼观察，经过计算求出了余数为2，对应校验码为X（或10）

这里我们用lookup函数求出：

=LOOKUP(MOD(SUM(MID(A2,ROW(1:17),1)*{7;9;10;5;8;4;2;1;6;3;7;9;10;5;8;4;2}),11),E2:F12)

此图中，检验码2纯为对照用，计算结果要取校验码1

lookup函数用在这里，会使函数公式显得精简，但要注意的是，余数列（E列）一定要是升序。

同理，我们这里也可以选定E2:F12，按F9输出，但会使公式显得很长，为了演示方便，我在此处不输出，有需要的话，可以自行尝试。

7、取身份证最后一位

8、判断，输出结果，因为将来要向下拖动，把引用的辅助列锁定。

上图中，貌似出了结果，其实我们忽略了一个问题，就是lookup出来的结果是数值型数字，除非恰好对应的校验码是X，否则，都不会与right函数出来的文本型结果相等，但我们把right的结果*1也不行，因为此结果有为X的情况，*1会报错。所以只能在lookup的结果上想办法。

=IF(LEFT(LOOKUP(MOD(SUM(MID(A2,ROW($1:$17),1)*{7;9;10;5;8;4;2;1;6;3;7;9;10;5;8;4;2}),11),$E$2:$F$12))=RIGHT(A2),"正确","有误")

当然，上图中的left也可以换成right或者text等来转换，可以自行尝试。

还有一种方法用来校验，这里只提供一下文字说明，具体步骤，可以用来练习，如有疑问可以留言咨询！

文字表达：将2的17次幂+2的16次幂+……+2的1次幂分别对应乘以身份证号码的1-17位数字，最后求和，再除以11，余数对应相应的字符