excel身份证号码显示0000怎么恢复(excel表格身份证号怎么处理)

对于一些Excel的使用者来说,身份证处理是一个经常面对的问题,也经常有人问起相关的问题。以前发过一个视频,后来发现介绍的不是很全面,今天就完整的介绍一下在Excel中处理身份证的各种场景。本文的内容包括:输入身份证变乱码显示怎么办?从文本文件导入身份证变乱码怎么办?身份证判断性别根据身份证取出...


excel身份证号码显示0000怎么恢复(excel表格身份证号怎么处理)


excel身份证号码显示0000怎么恢复(excel表格身份证号怎么处理)

对于一些Excel的使用者来说,身份证处理是一个经常面对的问题,也经常有人问起相关的问题。以前发过一个视频,后来发现介绍的不是很全面,今天就完整的介绍一下在Excel中处理身份证的各种场景。

本文的内容包括:

  1. 输入身份证变乱码显示怎么办?
  2. 从文本文件导入身份证变乱码怎么办?
  3. 身份证判断性别
  4. 根据身份证取出生日期
  5. 根据身份证判断籍贯
  6. 判断一个身份证是否正确
  7. 如何查找重复的身份证号


excel身份证号码显示0000怎么恢复(excel表格身份证号怎么处理)

1.输入身份证号码变乱码怎么办?

初学者输入身份证时会遇到这个问题:


excel身份证号码显示0000怎么恢复(excel表格身份证号怎么处理)

这实际上不是乱码,是科学计数法。Excel的单元格格式缺省情况下设为常规,依据输入的内容自动判断数据类型。由于身份证号全部是数字,所以就自动按数值显示了。又由于身份证号比较长,Excel就自动按照科学计数法显示了。

很多人会修改单元格格式为数值:


excel身份证号码显示0000怎么恢复(excel表格身份证号怎么处理)

但是结果已经错了:


excel身份证号码显示0000怎么恢复(excel表格身份证号怎么处理)

Excel对于数值最多只保留前面15为数值,后面的都记成0。因此,这个身份证号的后面3位已经丢失了。

经常有人问起,这种情况下,能否找回原来的身份证号。答案是否定的,很残酷,但是确实没有办法。

只能在输入身份证号前先把单元格格式修改为文本:


excel身份证号码显示0000怎么恢复(excel表格身份证号怎么处理)

也可以在输入时首先输入一个英文的单引号('),


excel身份证号码显示0000怎么恢复(excel表格身份证号怎么处理)

相当于通知Excel,接下来这个单元格中输入的都当作文本处理。


excel身份证号码显示0000怎么恢复(excel表格身份证号怎么处理)

2.从文本文件导入身份证变乱码怎么办?

如果单独输入文本文件,我们可以采用上一节的方法来解决。但是,如果文本文件中有很多身份证号,我们需要将它们导入到Excel中。

你可能会在文本文件中全选,然后Ctrl+C复制,粘贴到Excel中。结果是错误的:


excel身份证号码显示0000怎么恢复(excel表格身份证号怎么处理)

也可能会在文件-打开对话框中,选择文本文件,然后选择这个保存有身份证号的文件:


excel身份证号码显示0000怎么恢复(excel表格身份证号怎么处理)

毫不意外的是,这个结果也是错误的!

这种情况下,可以采用这个方法导入到Excel中。

在数据选项卡下, 点击“自文本”:


excel身份证号码显示0000怎么恢复(excel表格身份证号怎么处理)

这本质是个分列操作,所以是跟分列一样的对话框:


excel身份证号码显示0000怎么恢复(excel表格身份证号怎么处理)

一直点下一步,来到最后一步:


excel身份证号码显示0000怎么恢复(excel表格身份证号怎么处理)

这里可以设置各列的数据类型,将身份证号设为文本,然后点击完成即可。


excel身份证号码显示0000怎么恢复(excel表格身份证号怎么处理)

3.身份证号判断性别

我们知道身份证号的倒数第2位表示性别,奇数是男,偶数是女。

假设A1单元格中有身份证号:110108198810017562。可以使用下面的公式判断这个身份证号的性别:

=IF(ISODD(MID(A1,17,1)),"男","女")

其中MID函数部分从身份证号里取出倒数第2位数字,ISODD函数判断该数字是否奇数,IF函数根据判断给出结果。


excel身份证号码显示0000怎么恢复(excel表格身份证号怎么处理)

4.根据身份证号取出日期

身份证号的第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")

然后将单元格格式设为日期即可。


excel身份证号码显示0000怎么恢复(excel表格身份证号怎么处理)

5.根据身份证判断籍贯

严格的说,我们这里判断的不是籍贯,而是一个人第一次办身份证时的户口所在地。这个信息反应在身份证号的前6位当中。

假设A1单元格中有身份证号:110108198810017562,我们可以使用公式:

=LEFT(A1,6)

得到身份证号前6位。但是怎么得到具体籍贯是哪里呢?

我们需要有一个对应表,将身份证号前6位与籍贯对应起来。

你可以上网搜这个表,也可以上国家统计局网站获得这个信息。国家统计局网站地址是:

http://www.stats.gov.cn/tjsj/tjbz/

在网站上点击统计解读,统计标准:


excel身份证号码显示0000怎么恢复(excel表格身份证号怎么处理)

然后点击统计用区划和城乡划分代码:


excel身份证号码显示0000怎么恢复(excel表格身份证号怎么处理)

点击选择2020年(最新年份):


excel身份证号码显示0000怎么恢复(excel表格身份证号怎么处理)

选择一个省份(如果想全部下载,手工操作太累,需要写爬虫程序):


excel身份证号码显示0000怎么恢复(excel表格身份证号怎么处理)

下面是北京所有区的编码(只到这个级别就可以了,不需要更细的编码):


excel身份证号码显示0000怎么恢复(excel表格身份证号怎么处理)

这个表格可以复制到Excel中,然后通过函数或其他方式将第一列只保留6位。

接下来就是将使用Vlookup函数,将前面的LEFT公式作为第一个参数,进行匹配查找就可以了。


excel身份证号码显示0000怎么恢复(excel表格身份证号怎么处理)

6.判断一个身份证是否正确

身份证的最后一位是校验位,是根据前面的数字计算出来的一个结果。如果是随便编写的一个身份证号,这个结果就对不上。在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,"?"),"是","否"))

公式虽然很长,本身并不复杂。但是计算的原理需要较长的解释,这个工作就放到以后的文章中进行。如果大家需要这么做,直接使用这两个公式即可。


excel身份证号码显示0000怎么恢复(excel表格身份证号怎么处理)

7.如何查找重复的身份证号

一般情况下通过条件格式,突出显示重复值就可以:


excel身份证号码显示0000怎么恢复(excel表格身份证号怎么处理)

但是对于下面的身份证号:

110108199901015431

110108199901015321

Excel是会判定他们重复的。(因为前面15位一样)。

使用一般的COUNTIF公式来判断也会出现同样的问题。要使用下面的公式:

=COUNTIFS($A$1:$A$1000,A1 & "*")

END

分享 / 投稿 / 商务合作 微信号: excel-plus

趣懂得网所有文章资讯、展示的图片素材等内容均为注册用户上传(部分报媒/平媒内容转载自网络合作媒体),仅供学习参考。用户通过本站上传、发布的任何内容的知识产权归属用户或原始著作权人所有。如有侵犯您的版权,请联系我们1875979182@qq.com 反馈 本站将在三个工作日内改正。

(0)
打赏 微信扫一扫 微信扫一扫
上一篇 2022年1月5日 上午10:47
下一篇 2022年1月5日 上午10:47

相关推荐

  • excel怎么找特定内容(excel将sheet1数据提取到sheet2)

    私信回复关键词【插件】~获取Excel高手都在用的“插件合集+插件使用小技巧”!大家好,我是秋小 E~这里是秋叶 Excel 的【问答精华】专栏!问题主要来自秋叶 Excel 学习班的同学,回答由讲师、助教共同完成;每周一期,每期主题不同,希望能更有针对性地解决大家的共性问题。今天的主题是——数据提...

    2022年1月5日 办公软件知识
  • excel怎么选择指定区域(excel中怎么快速选择不规则区域)

    在工作当中你是否经常遇到需要快速选取Excel表格中的所有单元格区域或部分单元格区域,然后再进行下一步操作的情况?如何快速选中这些区域呢?下面考呀呀会计网校给大家介绍四种常用的区域选择方法。1使用Ctrl+A组合键进行选择单击表格内容区域的任意一单元格,按Ctrl+A,可以选中整张表格有数据的所有...

    2022年1月4日 办公软件知识
  • word参考文献怎么自动生成(在中国知网中怎么检索文献)

    很多同学在论文快完成的时候,都特别兴奋,啊,终于要搞完了!然后,一不注意参考文献的格式就出了问题。参考文献的格式不规范的话,在查重的时候是会被标红的。下面小编就教大家一个小妙招,利用中国知网快速自动生成参考文献。1.进入中国知网文献检索页面,https://www.cnki.net/。2.按照论文...

    2021年12月30日 办公软件知识
  • 排名怎么算excel公式rank(excel rank函数公式大全讲解)

    有人的地方就有江湖,有数据的地方就有排名。相信大家对Excel数据排名不会陌生,里面使用最多的一个排名函数,它就是Rank函数。因为场景的不同,数据排名也有不同的操作,Rank函数已经不能解决我们的操作。今天我们就来学习一下,Excel中如何根据不同的场景,利用函数公式进行排名操作。案例一:根据人...

    2022年1月4日 办公软件知识
  • excel怎么选出需要的内容(5分钟学会excel高级筛选功能)

    昨天晚上啊,我左思右想,辗转难眠。因为了解到,在学 Excel 的过程中,很多人都只盯着那些看上去很酷炫的高精尖功能,却忽略了那些宝藏小功能,比如……筛选。所以今天,我决定做一件可以载入「家族史册」的大事——替「筛选」伸冤!01进入数据筛选刚开始,我们先来点简单的。在这里呢,瑞瑞给大家介绍两...

    2021年12月31日 办公软件知识
  • excel怎么按照指定内容排序(excel如何使表格按照指定顺序排序)

    大家好,我是Excel从零到一,今天跟大家分享下自定义序列排序的方法,昨天有粉丝私信提问说,能不能自己设置排序的规则,然后就跟他介绍了自定义序列的排序方法,今天就分享给大家一、排序的一般规则排序的一般规则有3种1.数据是数字,可以根据大小进行升序或者降序拍序列2.数据是字母,会根据英文26个大...

    2022年1月3日 办公软件知识

发表评论

您的电子邮箱地址不会被公开。