【UN】Excel中身份证处理场景和方法全集！
对于一些Excel的使用者来说，身份证处理是一个经常面对的问题，也经常有人问起相关的问题。以前发过一个视频，后来发现介绍的不是很全面，今天就完整的介绍一下在Excel中处理身份证的各种场景。
本文的内容包括：
输入身份证变乱码显示怎么办？
从文本文件导入身份证变乱码怎么办？
身份证判断性别
根据身份证取出生日期
根据身份证判断籍贯
判断一个身份证是否正确
如何查找重复的身份证号
01
初学者输入身份证时会遇到这个问题：
这实际上不是乱码，是科学计数法。Excel的单元格格式缺省情况下设为常规，依据输入的内容自动判断数据类型。由于身份证号全部是数字，所以就自动按数值显示了。又由于身份证号比较长，Excel就自动按照科学计数法显示了。
很多人会修改单元格格式为数值：
但是结果已经错了：
Excel对于数值最多只保留前面15为数值，后面的都记成0。因此，这个身份证号的后面3位已经丢失了。
经常有人问起，这种情况下，能否找回原来的身份证号。答案是否定的，很残酷，但是确实没有办法。
只能在输入身份证号前先把单元格格式修改为文本：
也可以在输入时首先输入一个英文的单引号(')，
相当于通知Excel，接下来这个单元格中输入的都当作文本处理。
02
如果单独输入文本文件，我们可以采用上一节的方法来解决。但是，如果文本文件中有很多身份证号，我们需要将它们导入到Excel中。
你可能会在文本文件中全选，然后Ctrl+C复制，粘贴到Excel中。结果是错误的：
也可能会在文件-打开对话框中，选择文本文件，然后选择这个保存有身份证号的文件：
毫不意外的是，这个结果也是错误的！
这种情况下，可以采用这个方法导入到Excel中。
在数据选项卡下， 点击“自文本”：
这本质是个分列操作，所以是跟分列一样的对话框：
一直点下一步，来到最后一步：
这里可以设置各列的数据类型，将身份证号设为文本，然后点击完成即可。
03
我们知道身份证号的倒数第2位表示性别，奇数是男，偶数是女。
假设A1单元格中有身份证号：110108198810017562。可以使用下面的公式判断这个身份证号的性别：
=IF(ISODD(MID(A1,17,1)),"男","女")
其中MID函数部分从身份证号里取出倒数第2位数字，ISODD函数判断该数字是否奇数，IF函数根据判断给出结果。
04
身份证号的第7位至14位是出生年月。假设A1单元格中有身份证号：110108198810017562，可以使用下面的公式取出出生年月：
=MID(A1,7,8)
这样取出的是一个文本：
19881001
如果希望显示为日期形式，可以使用下面的公式：
=TEXT(MID(A1,7,8),"0000-00-00")
这么得到的就是一个日期形式：
1988-10-01
这个结果看上去是日期，实际是文本，如果希望得到真正的日期，可以使用下面的公式：
=--TEXT(MID(A1,7,8),"0000-00-00")
然后将单元格格式设为日期即可。
05
严格的说，我们这里判断的不是籍贯，而是一个人第一次办身份证时的户口所在地。这个信息反应在身份证号的前6位当中。
假设A1单元格中有身份证号：110108198810017562，我们可以使用公式：
=LEFT(A1,6)
得到身份证号前6位。但是怎么得到具体籍贯是哪里呢？
我们需要有一个对应表，将身份证号前6位与籍贯对应起来。
你可以上网搜这个表，也可以上国家统计局网站获得这个信息。国家统计局网站地址是：
http://www.stats.gov.cn/tjsj/tjbz/
在网站上点击统计解读，统计标准：
然后点击统计用区划和城乡划分代码：
点击选择2020年（最新年份）：
选择一个省份（如果想全部下载，手工操作太累，需要写爬虫程序）：
下面是北京所有区的编码（只到这个级别就可以了，不需要更细的编码）：
这个表格可以复制到Excel中，然后通过函数或其他方式将第一列只保留6位。
接下来就是将使用Vlookup函数，将前面的LEFT公式作为第一个参数，进行匹配查找就可以了。
06
判断一个身份证是否正确
身份证的最后一位是校验位，是根据前面的数字计算出来的一个结果。如果是随便编写的一个身份证号，这个结果就对不上。在Excel中，我们也可以判断一个身份证号是否正确。
假设A1单元格中有身份证号：110108198810017562，下面的公式可以判断这个身份证号是否正确：
在B1中输入公式：
=CHOOSE(MOD(SUM((MID(A2,1,1)+MID(A2,11,1))*7+(MID(A2,2,1)+MID(A2,12,1))*9+(MID(A2,3,1)+MID(A2,13,1))*10+(MID(A2,4,1)+MID(A2,14,1))*5+(MID(A2,5,1)+MID(A2,15,1))*8+(MID(A2,6,1)+MID(A2,16,1))*4+(MID(A2,7,1)+MID(A2,17,1))*2+MID(A2,8,1)+MID(A2,9,1)*6+MID(A2,10,1)*3),11)+1,1,0,"X",9,8,7,6,5,4,3,2)
在C1中输入公式：
=IF(LEN(A2)<18,"一代身份证无校验码",IF(RIGHT(A2,1)=TEXT(B6,"?"),"是","否"))
公式虽然很长，本身并不复杂。但是计算的原理需要较长的解释，这个工作就放到以后的文章中进行。如果大家需要这么做，直接使用这两个公式即可。
07
一般情况下通过条件格式，突出显示重复值就可以：
但是对于下面的身份证号：
110108199901015431
110108199901015321
Excel是会判定他们重复的。（因为前面15位一样）。
使用一般的COUNTIF公式来判断也会出现同样的问题。要使用下面的公式：
=COUNTIFS($A$1:$A$1000,A1 & "*")