找出Excel中错误身份证信息

找出Excel中错误身份证信息（通用3篇）

找出Excel中错误身份证信息 篇1

首先从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列单元格，点击工具栏中“张贴”按钮右侧的小箭头并挑选“值”选项。

将一切日期仿制到D列之后，D列的出世日期悉数处于选中状况，并且在D3单元格左侧呈现一个智能提示符号，点击提示符右侧的小箭头并挑选“变换为数字(C)”选项。

这样D列的数据就由文本格局变换成了实在的“日期”格局，仔细的你就会发现只要正确的日期才干成功变换，其对齐方法为“右对齐”，而不正确的日期则仍是文本格局，对齐方法仍为“左对齐”。有了比拟，咱们一眼就能看出不正确的身份证号码，更正起来那是适当的简单了。

找出Excel中错误身份证信息 篇2

居民身份证的号码是按照国家的标准编制的,由18位组成:前六位为行政区划代码,第七至第十四位为出生日期码,第15至17位为顺序码,第17位代表性别(奇数为男,偶数为女),第18位为校验码。作为尾号的校验码,是由号码编制单位按统一的公式计算出来的,如果某人的尾号是0-9,都不会出现X,但如果尾号是10,那么就得用X来代替,因为如果用10做尾号,那么此人的身份证就变成了19位,而19位的号码违反了国家标准,并且我国的计算机应用系统也不承认19位的身份证号码。Ⅹ是罗马数字的10,用X来代替10,可以保证公民的身份证符合国家标准。身份证号码与一个人的性别、出生年月、年龄等信息是紧密相连的,无论是15位还是18位的身份证号码,其中都保存了相关的个人信息。

15位身份证号码:第7、8位为出生年份(两位数),第9、10位为出生月份,第11、12位代表出生日期,第15位代表性别,奇数为男,偶数为女;18位身份证号码:第7、8、9、10位为出生年份(四位数),第11、第12位为出生月份,第13、14位代表出生日期,第17位代表性别,奇数为男,偶数为女。

例如,某员工的身份证号码(15位)是320521720807024,那么表示1972年8月7日出生,性别为女。如果能想办法从这些身份证号码中将上述个人信息提取出来,不仅快速简便,而且不容易出错,核对时也只需要对身份证号码进行检查,肯定可以大大提高工作效率。

二、提取个人信息

我们需要使用IF、LEN、MOD、MID、DATE、VALUE、CON-CATENATE等函数从身份证号码中提取个人信息。如图1所示,其中员工的身份证号码信息已输入完毕(C列),性别信息填写在B列,出生年月信息填写在D列,年龄信息填写在E列。

1、提取性别信息

如图1所示,性别信息统一在B列填写,可以在B2单元格中输入如下公式:

=IF(LEN(C2)=15,IF(MOD(VALUE(MID(C2,15,1)),2)=1,"男","女"),IF(LEN(C2)=18,IF(MOD(VALUE(MID(C2,17,1)),2)=1,"男","女"),"身份证错"))

结果如图2所示。

LEN(C2)=15:检查身份证号码的长度是否是15位。

MID(C2,15,1):提取身份证号码第15位的数字,即代表性别的数字。

MID(C2,17,1):提取身份证号码第17位的数字,即代表性别的数字。

VALUE(MID(C2,15,1)):将代表数字的文字串转换成数字。

MOD(VALUE(MID(C2,15,1)),2):用于得到给出数字除以指定数字后的余数。

IF函数:根据逻辑表达式测试的结果,返回相应的值。

2、提取出生年月信息

如图1所示,出生年月信息统一在D列填写,可以在D2单元格中输入如下公式:

=IF(LEN(C2)=15,CONCATENATE("19",MID(C2,7,2),".",MID(C2,9,2)),IF(LEN(C2)=18,CONCATENATE(MID(C2,7,4),".",MID(C2,11,2)),"身份证错"))

结果如图2所示。

LEN(C2)=15:检查C2单元格中字符串的字符数目。

MID(C2,7,2):从C2单元格中字符串的第7位开始提取2位数字,即出生年份的数字。

MID(C2,9,2):从C2单元格中字符串的第9位开始提取2位数字,即出生月份的数字。

MID(C2,7,4):从C2单元格中字符串的第7位开始提取4位数字,即出生年份的数字。

MID(C2,11,2):从C2单元格中字符串的第11位开始提取2位数字,即出生月份的数字。

CONCATENATE:将若干个文字项合并至一个文字项中。

IF函数:根据逻辑表达式测试的结果,返回相应的值。

3、提取年龄信息

如图1所示,年龄信息统一在E列填写,可以在E2单元格中输入如下公式:

=IF(LEN(C2)=15,YEAR(NOW())-1900-VALUE(MID(C2,7,2)),IF(LEN(C2)=18,YEAR(NOW())-VALUE(MID(C2,7,4)),"身份证错"))

结果如图2所示。

LEN(C2)=15:检查C2单元格中字符串的字符数目。

NOW():返回日期时间格式的当前日期和时间。

YEAR(NOW()):返回当前日期的年份值。

MID(C2,7,2):从C2单元格中字符串的第7位开始提取2位数字,即出生年份的简写,如82。

MID(C2,7,4):从C2单元格中字符串的第7位开始提取4位数字,即出生年份,如1982。

VALUE(MID(C2,7,2)):将代表数字的文字串转换成数字。

IF函数:根据逻辑表达式测试的结果,返回相应的值。

三、相关函数的语法形式

1、IF函数语法形式为:IF(logical_test,value_if_true,val-ue_if_false)。

2、LEN函数语法形式为:LEN(text)。

3、MOD函数语法形式为:MOD(number,divisor)。

4、MID函数语法形式为:MID(text,start_num,num_chars)。

5、CONCATENATE函数语法形式为:CONCATENATE(text1,text2……)。

6、VALUE函数语法形式为:VALUE(text)。

7、YEAR函数语法形式为:YEAR(serial_number)。

8、NOW函数语法形式为:NOW()。

参考文献

[1]刘剑.在Excel2000中身份证号码的妙用[J].电脑学习.2007年4期.

[2]张小华.巧用Excel函数提取身份证中的出生日期[J].信息技术教育[J].2006年2期.

[3]梁小宏.王彦博.Excel会认人,生日性别自动填[J].计算机应用文摘.2008年2期.

EXCEL数据统计错误信息 篇3

具体操作为：单击“文件→页面设置”指令，在呈现的对话框中单击“作业表”标签，切换到“过错单元格打印为”所对应的下拉列表，根据需要挑选“空白”或“--”即可，笔者挑选的“--”操作成果，

断定后退出看看，个人打印的表格是不是美丽多了。