用WPS表格正确录入身份证号码并自动提取个人信息

用WPS表格正确录入身份证号码并自动提取个人信息

时间:2013-05-21 09:47 来源:跟我学电脑

每次新生报到的时候，班主任老师都要统计上报一份包含学生身份证号码、性别、出生

日期等信息的电子表格，以便办理学籍卡等。这份工作说来并不是太难，但是要想高效而又

准确地完成，咱们却不得不说说那些在WPS表格中关于身份证号码的事)。

一、正确录入身份证号码

1.直接录入

现在的身份证号码都是18位，而金山表格对于单元格中超出15位的数字确认后均自动

以"0"表示，这样稍不注意就会造成错误。解决的办法有两个：一个是在录入时先输入英文

半角状态下的单引号，然后再接着录人身份证号码;另一个办法是先将单元格格式设置为"文

本"。具体操作是：选中要录入身份证号码的那些单元格区域，点击右键，在弹出的快捷菜

单中点击"设置单元格格式"命令，打开"单元格格式"对话框，在"数字"选项卡左侧的"分类"

列表中选中"文本"选项，确定后再录入身份证号码就不会错了，如图1所示。

2.验证录入我据有效性

担心录入时漏位或者多位，我们可以设置一个数据有效性的验证，一旦录入数字不是

18位，那么就会弹出消息、框进行提醒。具体操作如下：选中要录入身份证号码的那些单

1

元格区域，点击"数据"选项卡中的"数据工具"命令组中"有效性"命令按钮，打开"数据有效性

"对话框。在对话框的"设置"选项卡中，点击"允许"下拉列表中的"文本长度"，点击"数据"下

拉列表中的"等于"，然后在"数值"下方的输入框中输入"18"，如图2所示。这样就可以设置

单元格只能输入四位数据了。我们可以点击"出错警告"选项卡，在"错误信息"下方的输入框

中输入提示信息，比如"您输入的身份证号码不是18位，请重新输入"。这样，一旦录入的

身份证号出现漏位或者多位，那么就会弹出我们设置好的信息框提醒我们(图3)，可以在一

定程度上避免录入错误的发生。

二、从身份证号码中提取有用的信息

2

身份证号码录入之后，我们可以从这些号码中直接提取需要的信息，比如性别、出生年

月、年龄等。比起手工录人来说，直接提取既不会产生录入错误，完成速度也要快得多。

1.提取性别信息

身份证号码的倒数第二位是性别信息，奇数为男，偶数为女。假定身份证号码存在C2

单元格，D2为性别信息。那么我们只要在D2单元格录人公

式:"=IF(MOD(LEFT(RIGHT(C2,2),1),2)=0，"女"，"男")"，回车后­就可以了，如图4所示。

公式的意思是：提取C2单元格的倒数第二位数字，被2除看余数是否为零，如果是，显示

"女"，否则显示"男"。拖动D2单元格的填充句柄向下至最后一行，所有学生的性别信息就

有了，很方便。

2.提取出生同期

身份证号码从第7位开始的8个数据表示出生日期。第7至第10位数为年份，11-12，

13-14位数据分别为月份和日期。直接依次提取就是了。在E2单元格录入公式

"=MID(C2,7,4)&"-"&MID(C2,11,2)&"­"&MID(C2,13,2)。回车看看，标准不?如图5所示。公

式作用是把各数据提取出来，然后中间用短横线连接起来。其中"MID(C2,7,4)"表示从C2单

元格的第7位数开始提取4个数据。

3.提取年龄信息

3

正式表格填写年龄都是填周岁，这个比较好算，只要用现在的年份减去身份证号上的年

份就可以了。为此，我们需要在F2单元格录入公式"=YEAR(OW())-MID(C2,7,4)"，如图6

所示。公式中"YEAR(OW())"得到的结果是当前年份，即"2013"。下拉F2单元格的填充句

柄至最后一行，所有人的年龄信息就有了。速度快，而且避免了经常遇到的"周岁"、"虚岁"

的错误，公式也简单，不错吧!

三、查表格中重复的身份证号码

多个班级的表格合成到一起，保不齐就会有操作上的失误导致有重复的信息出现。而姓

名重复也是正常的。只有身份证信息肯定是唯一性的。因此，在大表格中查重复的身份证

号码也是必要的操作。通常统计重复所用的函数是COUTIF，计算在特定单元格区域中某

数据出现的次数。但对于身份证号码，这个公式是不能使用的。我们得另想办法。在G2单

元格中录入如下公式："=IF(SUMPRODUCT((C:C=C2)*1)>1,"是",'"')"，向下填充公式后所得

结果如图6所示一目了然吧!公式的含义是判断C列的单元格数据出现的次数，如果大于1，

则显示"是"，否则显示空白。

通常我在学生基本情况表格中跟身份证号码有关的那些事儿也就这么多了。只要相关的

技巧使用熟练了，最基本的身份证号码准确录入了，那么，快速、准确地完成一份表格就不

是一件太困难的事情了。

4