【HR必看】Excel中对身份证号码的处理技巧

刘老师为大家深入讲解Excel中，与身份证号码处理相关的方方面面。...





身份证号码的处理是HR部门经常遇到的问题，我给几个地方的企业培训时发现，相当一部分人员基本还是手动对身份证号码进行处理，效率可想而知啦！下面，刘老师为大家深入讲解Excel中，与身份证号码处理相关的方方面面。

刘老师，我在做人事表时，遇到身份证号，手工输入老遇到问题，并且生日，年龄，性别也得手动输，有没有好办法啊？

好吧，我整理了与身份证号码有关的所有技巧，去认真学习体会吧。





1）手动输入问题与解决方法

问题：在Excel中输入身份证号码时，发现输入后变成了科学计数格式，如图：使用单引号“’ “强制转成文本类型，发现后三位竟然全变成了0。原因：Excel中输入大于11位数字时，会自动以科学计数法显示。Excel最大精确到15位，大于15位，后面的数字全转换为0。

解决办法：

1）手动输入时，在身份证号码先输入单引号，再输入身份证号，如上图，将强制格式为文本。

2）先把单元格格式设置为“文本”，再输入身份证号码。2）身份证号码长度验证

在输入身份证号码时，由于数字太多，很容易多输或漏输。使用“数据验证”可以限制输入的必须是数字且必须是18位。

解决办法：

单击“数据”选项卡—“数据工具”分组--“数据验证”。在弹出的对话框中的“验证条件”里选择“自定义”，公式框中输入“=LEN(--E2)=18”。

“Len”函数：用来统计字符串中字符的个数。

“--E2”：负负得正，对单元格数值没有影响，但输入”-“的目的，确保E2单元格必须为数值。3）使用分列批量强制转为文本

选择身份证号码所在的单元格区域，单击”数据”选项卡，选择“数据工具”分组—“分列”。在弹出的“文本分列”对话框中，单击“下一步”，“下一步”，第3步的“列数据格式”中选择“文本”，然后单击“完成”4）批量导入

可以把来自文本文件、网页、Access、SQL等数据内容直接导入到Excel中进行处理。下面为从文本文件导入为例讲解导入步骤。如图，已有“人事名单”的文本文件。步骤1：单击“数据”选项卡，在“获取外部数据”分组中，选择“自文本”，在“导入文本文件”对话框中，找到要导入的文本文件“人事名单”，再单击“导入”。步骤2：如果文本文件中，包含标题，则选中“数据包含标题”选项。然后单击“下一步”。步骤3：分隔符号选择“Tab键”，然后单击“下一步”。步骤4：对下列对话框中，对每列设置“列数据格式”，其中包含身份证号码的列设置为“文本”格式，单击“完成”。步骤5：在“导入数据”对话框中，选择数据放置的起始位置后，单击“确定”。

1）提取生日

在D2单元格中输入公式：

=TEXT(MID(C2,7,8),"0000-00-00")利用填充快速复制公式：

选中D2单元格，鼠标放在右下角，双击，快速填充。2）提取性别

身份证号码的17位为性别，奇数为”男“，偶数为”女“。

=IF(MOD(MID(C2,17,1),2),"男","女")

Mid:从字符串指定位置提取指定个数的字符。

MOD：返回两数相除的余数。3）计算年龄

DATEDIF函数是Excel隐藏函数，可以返回两个日期之间的年月日间隔数。

DATEDIF(start_date,end_date,unit)

Start_date时间段内的起始日期。

End_date时间段内结束日期。

Unit 指定返回数值类型。

Unit 返回

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

"Y" 返回整年数。

"M" 返回整月数。

"D" 返回间隔天数。

"MD" start_date 与 end_date 日期中天数的差。忽略日期中的月和年。

"YM" start_date 与end_date 日期中月数的差。忽略日期中的年。

"YD" start_date 与 end_date 日期中天数的差。忽略日期中的年。=DATEDIF(D2,TODAY(),"Y")

或

=YEAR(TODAY())-YEAR(D2)

回复[  ]中的编号，查看往期Excel精彩内容：

[E001] Excel中如何批量合并单元格


[E002] 巧用Excel中的“选择性粘贴”

[E003] 全面认识Excel数据类型之一：数值类型

[E004] 全面认识Excel数据类型之二

[E005]Excel错误值总结

[函数001]COUNTIF函数应用案例解析

[HR01]【HR必看】Excel中对身份证号码的处理技巧


哇！刘老师，太棒啦

    关注 Office职场训练