文/小雨

图/网络

Excel由于其强大的数据处理功能，成为了办公软件的主流软件之一。在使用Excel来处理数据的时候，我们也会遇到一些奇怪的问题。

比如，当我们要在Excel里面输入后18位的身份证号码的时候，会发现：明明输入的是18位的数字，但是等输入完后发现最后4位却成了“0”。

难道Excel不支持输入身份证号吗？当然不是了。

实际上，Excel在处理数据的时候也有一个潜规则：

输入数字超过了12位数值，Excel则会自动转换为科学记数格式，输入如果超出了15位数值，Excel则会自动将15位以后的数值转换为“0”。

那么怎么解决这个Excel输入身份证后四位变成0的问题呢？小雨在这里提供2种解决方法。

第一种解决方法：

将单元格的格式设置为【文本】类型

Excel默认会根据我们输入的内容格式按照它自己的系统设定来做出一些调整。但是我们在输入18位的身份证号后就不需要它再做这样的调整了。所以，我们需要手动将这个单元格内的格式设置为【文本】类型。

具体设置方法：

首先，选中所需输入身份证号码的所有单元格，然后【右键】-【设置单元格格式】-【文本】，确定即可，此时，我们再去输入身份证号即可正常显示！

第二种解决方法：

在输入身份证号码之前先在最前面直接输入一个单引号【’】，然后再输入身份证号码。这样输入后就会正常显示，并且最前面输入的那个单引号也不会显示出来。需要注意的是，在输入单引号的一定要将输入法切换到英文状态下才可以。

这种方法操作起来特别简单，只要记住这个单引号就可以了，非常方便实用。

好了，以上就是Excel输入身份证后四位变成0的解决方法，只要一个字符轻松搞定！今天的分享就到这里。

在excel中如果输入身份证号，结果是你不愿意看到的：后3位会变成0。

原因也很简单，因为excel对数字精度的识别只限15位。解决方法估计80%的同学都知道：把单元格格式设置为文本。（先输入'也可以）

但，身份证的问题就解决了吗？NO!

情形1：系统出为Excel格式的文件时，身份证号码后3位已变成为0，根本不给你设置文本格式的机会。

解决方法：

先导出为文本等其他格式文件，再导入到已设置好文件的Excel表格中。

情形2：当对身份证进行统计时，统计公式出错！

解决方法：用&*方法让excel知道公式在统计文本

=COUNTIF(A:A,D2&"*")

情形3：在用身份证作条件进行高级筛选时也会出错

解决方法：还是在身份证号后添加*

飞飞说：再遇到身份证出错时，就试试在后面添加*号吧。

我们都知道身份证号码的前6位是代表省、市、区的代号比如（340505等），如果我们的工作人员是同一个地方的，我们就可以简化输入过程，在excel中快速输入身份证号码，具体操作用一个通讯费用报销工作表来讲解。

1、在excel工作表中选择需要输入身份证号码的单元格区域 C5:C54。切换到【开始】选项卡，在【单元格】组中单击【格式】下拉按钮，执行【单元格】命令。

2、弹出【设置单元格格式】对话框，单击【数字】标签，在【分类】列表框中选择【自定义】选项。

3、在 【类型】 文本框中输入"152103"@， 其中数字部分是身份证号码的前几位。双引号在英文状态下输入。

图1

4、单击【确定】按钮。

5、在单元格中输入号码剩余的数字。

图2

6、按下 Enter 键， 发现单元格 C5 中的身份证号码的前几位152103已经自动添加到前面。

图3

通过上面的方法，我们就可以在excel中快速输入身份证号码了，这里我可以大大的提高工作效率，当然此项技巧我们也可以运用到很多工作领域。

本文来自：百度知道日报

表1为身份证号数据库，但不知道对应地址，表2就是身份证户籍地数据库，如何将自动将里面的地址导入到对应的身份证后面呢？我们来一起看一下！

用下面的函数可以完美搞定，我们先来看一下：

在身份证号码表C2位置输入：=VLOOKUP(VALUE(LEFT(B2,6)),身份证号码前6位!A:B,2,0) 即可，向下拉即搞定！

LEFT（B2,6):取身份证左边6位

VALUE(LEFT（B2,6)):将LEFT取值的6位文字格式转为数字格式

VLOOKUP:从“身份证号码前6位”表中查找上面的前6位所代表的户籍地！

具体操作见以下图示：

Excel表格中根据身份证号码自动填出生日期、性别、年龄

右击所在列→“设置单元格格式”为“常规” →复制以下（蓝色）公式：

=================================================================

◎ A1为15位或18位身份证号，要在B1算出 生日

=TEXT(19*(LEN(A1)=15)&M")

◎ A1为15位或18位身份证号，要在B1算出 性别

=IF(MOD(MID(A1,15+(LEN(A1)=18)*2,1),2),"男","女")

◎ A1为15位或18位身份证号，要在B1算出 截止到“今天”的 年龄

=DATEDIF(TEXT((LEN(A1)=15)*19&M"),TODAY(),"Y")

注意：根据表格修改单元格“(A1)”下图已经修改为“(B1)”。修改方式：将上述公式打入后，抹黑上述公式中的“A1”（不要抹括号）,再点“B1”单元格，公式中就自动生成“B1”。然后下拉“C1”，以下所有行就全部自动完成了。

---另：

根据身份证号码求出生日期、性别、年龄、15-18位转换

复制以下蓝色代码：

根据身份证计算年龄

当需要在单元格内输入18位身份证号码时，往往会出现数字太长，在单元格内变成科学记数法的现象，并且身份证末尾3位也会变成000的情况，就像下图中所示。

下面有两个比较简单的Excel技巧可以来解决这个问题，使身份证号码能完全在单元格内显示。

方法一：设置单元格格式

这里需要在输入身份证号码之前设置好单元格格式。

*选中目标单元格。

*使用快捷键Ctrl+1调出“设置单元格格式”对话框。

*在分类里面选择文本。

然后就可以输入身份证号码了，显示出来的就是完成的数字，并且左上角有一个绿色的箭头，代表单元格格式目前是以文本形式存储的数字。

但是在编辑栏中我们会发现，号码之前会自动有一个单引号的，这就是下面的第二个方法。

方法二：英文状态下输入单引号

这里是在输入身份证号码前，输入法为英文状态，输入英文的单引号“’”，然后再输入身份证号码。

这个方法也能实现完全显示身份证号码，并且前面有一个绿色标志，内容的最前面有一个单引号。两个方法不同的是，第一种的单元格格式为“文本”，而第二个则还是“常规”。

施老师：

作为办公人员，经常要编写关于职工信息档案的表格，其中最常输入的数据就是身份证号码，由于人员众多，输入确实麻烦，一不小心就可能把员工的出生日期弄错了，这可是关系到每个员工的切身利益，如工龄、社会保险金等。要是逐一验证那可是一件让人头疼的事情。下面就由宁双学好网施老师教大家一招，通过此方法就能够很快查找出Excel表格中错误的身份证号码。

一、首先从B列的身份证号码(注：输入前必须将该列单元格格式统一为“文本”)中提取出生日期到C列，以C3为例，选中C3单元格，然后在“编辑栏”中输入如下公式：=TEXT(IF(LEN(B3)=15，"19"，)&MID(B3，7，IF(LEN(B3)=18，8，6))，"####-##-##")，该公式运用了文本函数，可智能判断18位和15位的号码，并从中提取出生日期。然后后面的单元格直接用鼠标拉就行。

待全部提取完之后，大家就会发现，出生日期中竟出现了“1980-13-15”(C4)，请问有谁能在这样的日期出生呢？很显然这是错误的信息。

接下来我们要做的事情就是快速地把所有错误信息查找出来。全选C列的出生日期C3:C4并单击“复制”按钮，然后把光标定位到D列单元格，点击工具栏中“粘贴”按钮右侧的小箭头并选择“值”选项(图2)。

将所有日期复制到D列之后，D列的出生日期全部处于选中状态，并且在D3单元格左侧出现一个智能提示符号，点击提示符右侧的小箭头并选择“转换为数字(C)”选项(图3)。

这样D列的数据就由文本格式转换成了真实的“日期”格式，细心的你就会发现只有正确的日期才能成功转换，其对齐方式为“右对齐”，而不正确的日期则仍是文本格式，对齐方式仍为“左对齐”(图4)。有了比较，大家一眼就能看出不正确的身份证号码，更正起来那是相当的容易了。

相信好多朋友都和我一样，已经到公司报道有一段时间了，想必会经常与身份证号打交道，今天咱们就聊一聊关于『Excel身份证 』的事。

大家好，我的名字叫去力果果，我的身份证号码是

我猜大家都遇到过这种情况，因为Excel在处理身份证号时：

超过11位的数就会自动用科学记数法的方式来显示；

超过15位的数后都将被当成0进行处理。

【1】输入身份证的正确姿态

注：一定要先设置单元格格式为文本再进行输入号码。

在工作中，好多人录入身份证号码，在输入时，错漏是经常的。

那要如何确保身份证的输入位数是准确的呢？

【2】身份证位数限制

原理：通过『 数据验证 』来限制文本的长度，并提示输入有误。

OK, 解决了身份证号码的正确输入，再来看一看身份证号里隐藏了哪些信息。

【3】提取出生年月

公式：

=TEXT(MID(B2,7,8),"0000!/00!/00")

【4】提取性别

公式：

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

很神奇有没有，其实原理很简单，身份证的第17位数字代表性别，偶数是女性，奇数时男性。

【5】提取年龄

公式：

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

上面就是已知身份证号可以提取的三个重要信息。

如果我们一开始就全都设置好了，当录入身份证号码的时候，就可以全自动生成每个人的出生年月，性别，年龄了。

单元格公式：

C2：

=IFERROR(TEXT(MID(B2,7,8),"0000!/00!/00"),"")

D2：

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

E2：=IFERROR(DATEDIF(TEXT(MID(B2,7,8),"0000!/00!/00"),TODAY(),"y"),"")

其实身份证号码里面隐藏了好多信息，只要学会如何提取，就不必再花时间去录入潜在的信息了，好啦，感谢您的阅读，祝您事事顺心。

如果我们直接在Excel表格中输入身份证号，默认显示的是科学计数法表达方式，没有办法看到身份证号码原来的格式。比如下表：

第一招：设置单元格式：数字作为文本

1、那如何可以在输入身份证号码后，保持原来的格式呢？先教大家一招，这里我们需要先进行单元格的设置。我们选中单元格

2、鼠标右键-设置单元格格式

3、弹出的单元格格式对话框，数字选项卡页面的分类选择 文本 ，然后确定

4、当我们再次在刚才设置的单元格下输入身份证号码后，还保持了原来的18位格式

第二招：身份证号码前加一个英文的分号'

1、输入身份证号码我们还有另外一招也能实现，方法是：在输入身份证号码之前，输入一个英文的分号’（该符号在键盘回车键Enter键前面），然后在分号’的后面输入身份证号码，结果如下：

2、当我们回车后，身份证号码就保持了原来的格式

本百家号：时代新生分享工作、生活、技能方面的经验，愿和您一起成长。请多多关注哦！

职场中的精英们，相信大家在使用EXCEL的时候，您肯定曾经对处理身份证号信息头疼不已，本节以EXCEL2010为准，将从以下五个方面对身份证号在EXCEL中的处理方式进行详细讲解。

一、身份证号的输入

方式一：单元格输入身份证号前，将单元格格式设置为“文本”格式（Ctrl+1，可快捷调出单元格格式设置页面）后，然后再输入身份证号。

方式二：在单元格输入身份证号时候，首先输入英文的单引号“'”，然后再输入身份证号，神奇的事情发生了，引号消失不见，身份证号自动变为文本。

为什么输入要这样啰嗦呢，因为Excel单元格默认情况下为“常规”格式，该状态会自动按照自己的喜好自动将内容进行归类，它会将输入的身份证号视同为“数字”格式，所以当单元格长度不够的情况下，您输入一串身份证号后会变成科学计数法；即使当前长度足以显示身份证号，您将该单元格宽度变小，也会变成科学计数法，再加长，会发现份证号后几位会丢失变成0。

二、身份证号信息的输出

假设在A1单元格存储了身份证号：370983201705040067

（一）通过身份证号采集出生日期

mid函数语法：

mid(text,start_num,num_chars)，中间某位置开始截取部分字符串；其中text表示要截取的字符串，start_num表示从第几位字符串开始截取，num_chars表示要截取的字符数。

1、如果显示格式为：20170504，那么我们应该在单元格中输入函数：=mid(A1,7,8)，就可以得到20170504；不过此时显示的内容为文本状态，并不是日期格式，需要通过其他方法再转化为日期。

2、如果显示格式为：2017年05月04日，那么我们应该在单元格中输入函数：

=mid(A1,7,4)&"年"&mid(A1,11,2)&"月"&mid(A1,13,2)&"日"

不过此时显示仍然为文本状态，并不是日期格式，需要通过其他方法进行转化。

该函数的意思是：从A1单元格中的第7位开始选，往后数4位，返回年2017；从A1单元格中的第11位开始选，往后数2位，返回月05；从A1单元格中的第13位开始选，往后数2位，返回日04；同时通过连接字符串连接“年”“月”“日”，即得到2017年05月04日。

知识补充：

“&”(输入方式：shift键+大键盘上的7)，相当于数学运算附中的+，“&”可以将两个单元格的内容，或者字符串拼接在一起，例如假设A1单元格为：张，A2单元格为：三，我们在A3中输入：=A1&A2，则A3中会显示：张三。

3、前面两种方式显示都为文本格式，假设有人想一步到位，直接将身份证中的日期采集成日期格式，如2017-5-4，那么我们应该在单元格中输入函数：

=DATE(MID(A1,7,4),MID(A1,11,2),MID(A1,13,2))

此时直接为日期格式（可以通过设置单元格中日期的格式，变为2017年5月4日或者2017/5/4等等）。

函数的意思是：从A1单元格中的第7位开始选，往后数4位，返回年2017；从A1单元格中的第11位开始选，往后数2位，返回月05；从A1单元格中的第13位开始选，往后数2位，返回日04；然后通过DATE函数将返回的字符串转化为日期格式。

（二）通过身份证号采集性别

mod函数语法：

mod(number,pisor), 用于返回两数相除的余数；其中number：表示被除数，pisor表示除数。如果该参数为 0，MOD 函数将返回错误值 #p/0!。

我们通过mod函数与mid函数组合，在单元格中输入函数：

=IF(MOD(MID(A1,17,1),2)=1,"男","女")，即可得到性别。

函数的意思是：首先从A1单元格中的第17位开始选，往后数1位,得到1个数字，然后用这个数字除以2，得到余数；假设余数为1，则显示“男”；假设余数不为1，则显示“女”。

知识补充：18位的身份证号的第17位如果是奇数，代表性别是男，如果是偶数，代表性别是女。

（三）通过身份证号采集年龄

-----------------------------

NOW函数语法：now()，用于返回当前日期。

YEAR函数语法：

YEAR（serial_number），用于返回日期中的年份；其中serial_number表示一个日期值。

DATEDIF函数语法：

DATEDIF(start_date,end_date,unit)，两个日期之间做差额；其中：

Start_date 为一个日期，它代表时间段内的第一个日期或起始日期；

End_date 为一个日期，它代表时间段内的最后一个日期或结束日期；

Unit 为所需信息的返回类型。

Unit 返回

注：结束日期必须大于起始日期

"Y" 时间段中的整年数。

"M" 时间段中的整月数。

"D" 时间段中的天数。

"YM" 起始日期与结束日期的间隔月数，忽略日期中年份。

1、YEAR、NOW和MID函数组合，计算年数差额即年龄，函数如下：

=YEAR(NOW())-MID(A1,7,4)，显示为**年。

2、DATEDIF、NOW和MID函数组合，计算年数差额即年龄，函数如下

=DATEDIF(DATE(MID(A1,7,4),MID(A1,11,2),MID(A1,13,2)),NOW(),"y")，显示为**年。

函数的意思是：首先通过DATE函数将从身份证中采集的字符串转化为日期格式；通过NOW函数调取当前日期；然后通过DATEDIF函数将两个日期以“年”为单位做差额。

3、补充：

（1）月数差额：计算当前日期与身份证日期的月数差额，函数如下：

=DATEDIF(DATE(MID(A1,7,4),MID(A1,11,2),MID(A1,13,2)),NOW(),"m")，显示为**月。

（2）天数差额：计算当前日期与身份证日期的天数差额，函数如下：

=DATEDIF(DATE(MID(A1,7,4),MID(A1,11,2),MID(A1,13,2)),NOW(),"d")，显示为**天。

（3）年月差额：计算当前日期与身份证日期的年月差额，函数如下：

=CONCATENATE（DATEDIF(DATE(MID(A1,7,4),MID(A1,11,2),MID(A1,13,2)),NOW(),"y"),"年",DATEDIF(DATE(MID(A1,7,4),MID(A1,11,2),MID(A1,13,2)),NOW(),"ym"),"个月"），显示为**年**个月。

（四）通过函数检验身份证号码的正确性

我们有的时候手动输入的身份证号会不小心发生错误，我们可以通过函数来自动判定是否自己输错了。

原理：18位身份证号码的最后一位是检验码，它是根据身份证前17位数字依照规则计算出来的，其值０～９或X。一般情况只要有一位数字输入错误，依照规则计算后就会与第18位数不符。当然不排除按错误号码计算后恰好与检验码相符的情况，但这种情况出现的可能性较低。

函数表达式如下：

=MID("10X98765432",MOD(SUMPRODUCT(MID(A1,ROW(INDIRECT("1:17")),1)*2^(18-ROW(INDIRECT("1:17")))),11)+1,1)=MID(A1,18,1)，显示结果为TRUE或者FALSE，如果身份证号符合规则显示TRUE，如果身份证号不符合规则显示FALSE。

（五）身份证号18位与15位的转化

一代身份证是15位（个别也有18位的），二代身份证为18位，工作中难免有时候会发生需要通过一个人15位的老身份证号推算18位身份证号，也可能需要通过一个人的18位身份证号来推算15位身份证号的情况。

1、身份证号15位升级为18位

20世纪的人：

=IF(LEN(A1)=15,REPLACE(A1,7,,19)&MID("10X98765432",MOD(SUMPRODUCT(MID(REPLACE(A1,7,,19),ROW(INDIRECT("1:17")),1)*2^(18-ROW(INDIRECT("1:17")))),11)+1,1),A1)

21世纪的人：

=IF(LEN(A1)=15,REPLACE(A1,7,,20)&MID("10X98765432",MOD(SUMPRODUCT(MID(REPLACE(A1,7,,20),ROW(INDIRECT("1:17")),1)*2^(18-ROW(INDIRECT("1:17")))),11)+1,1),A1)

2、身份证号18位降级为15位

=IF(LEN(A1)=18,LEFT(REPLACE(A1,7,2,),15),A1)

相信看到本节内容的读者们肯定有眼前一亮的感觉，估计大家都会收藏或者转发，不过提醒大家，如果提高EXCEL水平，最好是自己实际操作一遍，增强记忆哦。