您的位置: 首页 > EXCEL技巧 > Excel基础 >

Excel表格中使用数据有效性判断输入的身份证号码

时间:2013-12-03 整理:docExcel.net

1、身份证位数(是否为15位或18位)

2、日期是否合法(主要是判断月份是否在1-12之间,日期是否超出当月的天数等)

3、身份证号是否重复。
数据有效性公式如下:
=NOT(OR(AND(LEN(A1)<>15,LEN(A1)<>18),COUNTIF(A:A,A1)<>1,ISERROR(1*TEXT((LEN(A1)=15)*19&MID(A1,7,6+(LEN(A1)=18)*2),"#-00-00"))))
可以设置提示内容为:
“身份证位数或者日期有误,或者身份证号有重复,请核准后重新输入!”

这样设置好后有以上三错误就不能输入了。
但还有个缺点,就是不知道和上面那个身份证号重复了。

所以建议采用如下方法:
在数据有效性中只判断位数和日期问题,公式如下:
=NOT(OR(AND(LEN(A1)<>15,LEN(A1)<>18),ISERROR(1*TEXT((LEN(A1)=15)*19&MID(A1,7,6+(LEN(A1)=18)*2),"#-00-00"))))
提示内容为:
“身份证位数或者日期有误,请核准后重新输入!”
然后在A列用条件格式显示重复的身份证号码,条件格式的条件设为:
公式=COUNTIF(A:A,A1)>1
将字体设为红色。
这样设置后,位数和日期有问题不能输入,重复的可以输入,但会显示为红色字体,以便你检查是这个单元格错了,还是先输入的那个单元格(红色字体)错了。

用Excel 2010数据有效性拒绝错误数据 问题:用Excel 2010数据有效性拒绝错误数据
回答:...会堕落,一不小心就会录入一些错误的数据,好比反复的份证号码,超出范畴的无效数据等。其实,只要公道配置数据效性法则,就可以制止错误。下面咱们通过两个实例,Excel 2010数据效性的妙用 实例一:拒绝录入反复...
Excel表格技巧:单元格格式数据有效性 问题:Excel表格技巧:单元格格式数据有效性
回答:...输入的“=C2 ”的单元格全部会跟着更改。 ②设置份证的数据长度输入限制:15位或18位。则运用公式=OR(LEN(C8)=15, LEN(C8)=18),可满足要求。 注:出错警告里边的停止、信息、警告的程度不同,如是“信息”时,允...
Excel2003入门动画教程68:防止重复录入数据 问题:Excel2003入门动画教程68:防止重复录入数据
回答:...。工作当中,员工的资料做存档的时候有时需要把员工的份证号码都记录下来,每个人的份证号码都是唯一的,为了在Excel中防止重复输入,我们可以用“数据效性”来提示大家,具体操作方法就让Excel办公网小编为...
[office]Excel2010有效拒绝错误数据 问题:[office]Excel2010有效拒绝错误数据
回答:...会堕落,一不小心就会录入一些错误的数据,好比反复的份证号码,超出范畴的无效数据等。其实,只要公道配置数据效性法则,就可以制止错误。下面咱们通过两个实例,Excel 2010数据效性的妙用 实例一:拒绝录入反...
如何对excel同身份证号码的人员不进行重复编号 问题:如何对excel同身份证号码的人员不进行重复编号
回答:该公式不能用来设置份证不重!数据效性中正确公式应为=COUNTIF(A:A,"*"&A1)=1=1表示只有COUNTIF(A:A,"*"&A1)统计出该数据仅有1条若不等于1,程序报输入重复。
excel如何限制唯一数据的录入 问题:excel如何限制唯一数据的录入
回答:...”来防止重复输入。 例如我们要在B2:B200中,来输入份证号码 1、先选定单元格区域B2:B200 2、单击菜单栏中的“数据”→“效性”命令,打开“数据效性”对话框,在“设置”选项下,单击...
相关知识:

下面内容对您也许有用

      话题:如何利用excel数据有效性来限制身份证号码输入
      问:如何利用excel数据有效性来限制身份证号码输入,即只能输入15或18位,其它一律不
      答:设置数据有效性,选自定义 在输入中输入=OR(LEN(A1)=15,LEN(A1)=18)这样指允许输入15位和18位字符了 在office excel 2003中怎样自动 excel身份证号码限制只能是18
      话题:EXCEL中数据有效性怎么使用
      答:[应用一]下拉菜单输入的实现 例1:直接自定义序列 有时候我们在各列各行中都输入同样的几个值,比如说,输入学生的等级时我们只输入四个值:优秀,良好,合格, 在EXCEL中,数据有效性如何使用 EXCEL中数据有效性 自定义怎么
      话题:我想在excel表格中输入身份证号码,但是为了避免输入校验位为
      问:我想在excel表格中输入身份证号码,但是为了避免输入校验位为小写字母,如何设置
      答:身份证号从A2开始输,选择需要输入身份证号单元格,有效性,自定义,公式=EXACT(RIGHT(A2),"X")输入信息,出错警告自己设置一下,确定即可。 excel表格中输入身份证号码, 用excel校验表格内的两列人名
      话题:EXCEL表格中的有效性怎么使用,具体些的!知道
      答:一楼的给的很全面了,赞,也学习了。我常用的最简单的用法是:选中要设置有效性的单元格,选项卡上点:数据-有效性-允许(序列),在来源处,输入,比如:1 EXCEL表格中有效性怎么使用excel表格中,在一个表各中输入
      话题:EXCEL身份证数据有效性检验中含X的问题
      问:EXCEL里面身份证数据有效性检验中,设置的是不是15位或者18位就提示错误,可是在
      答:如你的身份证号全部输在C列:1.全选C列,格式设为文字2.全选C列,数据-有效性,然后选择自定义3.输入=OR(LEN(C1)=15,LEN(C1)=18)4.在出错警告中输入:"正确身份证号是
      话题:excel表格中,在一个表各中输入两个性别中要怎样使用数据有效
      答:参见本人空间实例
      话题:excel表格中,数据有效性匹配
      问:excel表格中,A列是车型,B列是车型代码。已为A列设置数据有效性朗逸、帕萨特,
      答:估计只能用VBA了吧,Private Sub Worksheet_Change(ByVal Target As Range)If Target.Column2 Then Exit Sub If Target.Offset(0,-1).Text="朗逸"&Left(Target 高手来!excel表格中相邻的两列 excel表格中,两个单元格设置
      话题:excel 手上有五十万公安身份证,在录入中,如何像数据有效性
      答:数据有效性可以实现:追问 感谢!但数据量太大,把50厄存到别一个文件,来做为有效性,必须要打开吗?有没有不打开放身份证文件的方法?提问者评价 谢谢! 在EXCEL中输入身份证号时出生 在Excel表格中怎样才能让输入的
      话题:在EXCEL中输入身份证号时出生日期有效性判断
      问:用EXCEL录入身份证号,因为是学生学籍输入。出生日期中的出生年份要限制在一定的
      答:可以做的,你试一下这个方法吧:利用数据有效性:假设你的身份证号在A列,先选定A列,点“数据”菜单,有效性,设置选项卡,允许:自定义,公式输入=AND(AND(
      话题:如何判断EXCEL表格中数据的有效性?知道
      问:表格中某一单元格的数据是时间(以6位数表示200012),但这一值必须是小于当前
      答:假设单元格是A1 则选中A1,点击菜单:数据-有效性 设置为:自定义,=AND(A1(TEXT(TODAY(),"YYYYMM")),MOD(A1,100))第一个条件是判定数据小于当前的年月,后一个 电子表格数据有效性 excel表格数据有效性
最新评论