如何设置单元格身份证号数字格式（一篇文章搞定所有单元格中身份证号码问题）

一篇文章搞定所有单元格中身份证号码问题

大家好，很高兴又和你们见面了，今天，我想和大家分享交流关于单元格身份证号码的问题。提到这个问题，小伙伴们的第一反应该是比较麻烦的事，按照常用方法录入的身份证号码结果是会出现错误。这是因为Excel中默认的数字格式是“常规”，它最多只能显示11位有效的数字，如果超过11位就会以科学记数形式表达。身份证号码的位数是18位，所以超过了该区域范围，自然就出错了，那应该如何避免这种情况的发生呢？身份证号码作为比较特殊的一段数字，能不能在日常办公中被我们所利用呢？请继续向下看，通过本篇文章让你搞定所有单元格中身份证号码的问题。

一、搞定所有单元格中身份证号码问题

1、身份证号码的录入技巧

由于身份证号码的位数超过了11位，导致单元格在默认情况下无法正常显示，我们一般可以采用两种方法进行解决。

第一种方法：首先设置单元格格式为“文本”，再进行设置。

具体操作方法为：选中输入身份证号码单元格区域，右键点击鼠标—设置单元格格式—文本格式，然后再输入身份证号码即可正常显示（见下图）。

2、从身份证号码中提取出生日期

大家都知道，身份证号码短短的十几位数字包含了很多的个人信息，如何从中快速提取出个人的出生日期为我所用呢？这里介绍两种方法供您选择。

第一种方法：数据分列法

由于身份证号码的位数都是18位，每个人的出生日期在数据中所占的位置都是一样的，所以我们可以选择利用数据分列法来解决。

具体方法如下：选中所有单元格区域，接着点击数据—分列，在打开的对话框中选择固定宽度，拖动标记线将出生日期框选出来，再将其他不需要的内容选择不导入，最后点击完成（见下图）。

这里需要注意的是，提前在身份证号列的后方添加一列数据提取存放列，防备由于数据分列导致后方数据被覆盖的可能。

第二种方法：快速填充法

和上一种方法相类似，不同的是这里采用的是快速填充的方法，具体设置为：先在第一个单元格中对应输入出生日期（如下图），再选中所有需要填充的区域，按快捷键Ctrl E即可快速完成填充。再按右键单击选择设置单元格格式，在自定义中输入0-00-00代码即可实现想要的结果。

3、性别判断

首先我们应该知道，在18位身份证号码中，第17位是判断性别的，如果是偶数就为女，是奇数就为男。所以在D2单元格设置公式=IF(MOD(MID(B2,17,1),2)=1,"男","女")

相关函数说明：

MID函数=（被提取值，从第几位开始，提取几位数），表示从第几位开始提取几位数据。

MOD函数=（被除数，除数），表示用提取的结果，去除以2的结果。

最后利用IF函数对结果进行判断，即可得到想要的结果。

4、籍贯的判断

该项操作需要提前准备相关籍贯的统计表，利用查找函数找到相对应的籍贯所在地。具体公式=VLOOKUP(--LEFT(B2,2),行政区域代码表!A:B,2,0)

相关公式说明：

VLOOKUP函数=VLOOKUP（查找值，查找区域，返回的列，查找的类型），表示利用目标单元格在查找范围内对应列显示的内容。

LEFT函数= LEFT（字符串，从左侧提取字符数），表示从左第几位开始提取指定的字符数。

也就是说，首先利用LEFT函数提取需要的字符，再利用VLOOKUP函数查找该值对应的籍贯所在地。

5、提取年龄

利用上述第一个操作提取的出生日期，与现在的日期进行相减计算，最终得到实际的年龄。具体设置公式=YEAR(TODAY())-YEAR(C2)，见下图。

简单的说就是用现在的年份减去出生日期的年份获取到实际的年龄。

6、对应属相的获取

根据对应的年份获取属相。具体设置公式=CHOOSE(MOD(MID(B2,7,4)-1900,12) 1,"鼠","牛","虎","兔","龙","蛇","马","羊","猴","鸡","狗","猪")

提示：这里设置的减去1900，是因为Excel的日期最早是1900年，而这一年是鼠年，所以后面设置的对应属相也相一致。

7、禁止重复身份证号码的输入

总有那么一些大马虎，在输入身份证号码时候，将不同人的号码设置为相同的数字，显然这样是不对的，如何避免呢？我们可以通过限制单元格的输入，来达到对错误信息的提示作用，从而提醒输入者修改错误的信息。具体设置如下：

选中所有需要设置的区域，点击数据—数据验证，在设置标签中选择验证条件为自定义，在公式区域输入=COUNTIF($A$1:$A$11,A1&"*")=1。

经过设置以后，当输入重复身份证号码时，就会弹出错误的提示，提醒操作者进行修改。

二、总结

身份证号码在Excel中输入时比较特殊的一种，但是身份证号码每个数据背后都隐藏着对应的秘密所在，对于办公人来说，今天统计出生日期，明天需要属相……，如果我们掌握了今天的操作技巧，对于后续的工作一定会有很大的帮助。让我们每天学习一点点，进步一点点，不断提升自己的个人技能，用“最短的时间，最快的方法，完成工作任务”。

好了，今天的分享就到这里，如果您觉得文章对您有所帮助的话，欢迎点击左上角图标或文字关注我；如果有什么问题或是好的建议，欢迎在下方留言交流。每天半点工夫，玩转办公软件，我还是你们的工夫哥，我们下次再见。

,