二维码 购物车
部落窝在线教育欢迎您!

Excel小白的数据验证课②身份证的双重验证设置等

 

作者:壹仟伍佰万来源:部落窝教育发布时间:2019-01-15 19:38:41点击:4879

分享到:
0
收藏    收藏人气:1人
版权说明: 原创作品,禁止转载。
编按:

哈喽!小伙伴们大家好!上期我们主要讲了用系列做下拉菜单确保数据规范的操作。今天我们主要讲数据验证的其他操作,如数字区间设置、身份证双重验证、输入提示等,让小伙伴一次学个通透!

 

 

 

还是原来的配方,还是原来的表!

 

一、设置数值区间验证

 

我们的评分范围是0-10,并且容许小数,如何才能确保输入的分值在0-10中呢?

 

步骤:

  选中需要设置规则的区域“G2:G8

  点击“数据有效性”按钮,进入“设置”选卡

  在“允许”栏中,点击“小数”

  “数据”栏选择“介于”,“最小值”和“最大值”一栏分别输入“0”“10”,点击“确定”

 

 

提示:如果不允许出现小数,则“允许”栏只能选择“整数”。

设置后如果在该区域输入“11”,将直接跳出错误提示框。(想让提示框的文字变得温柔,可以查看Excel小白的数据验证课①。)

 

二、设置身份证号码位数和非重复验证

1.单纯号码位数验证

总有粗心大意的人,在输入身份证号码时要么多一位要么少一位。我们应该如何限制呢?

 

步骤

      选中需要设置规则的区域“E2:E8”

      点击数据有效性按钮,进入设置选卡

      允许栏中,选择文本长度

      数据栏选择等于长度一栏输入“18”,最后点击确定

 

 

确定后,当输入号码位数多了或少了,就会弹出提示框。

 

 

2.单纯非重复验证

身份证号码是唯一的,因此输入时必须确保号码不能与前面的号码重复。

单一的非重复验证,可以自定义公式进行验证。公式=countif(e:e,e2)=1

 

 

3.位数和非重复双重验证

把位数和非重复验证一起使用,同样需要自定义公式。

位数验证的公式=len(e2)=18

非重复验证公式= countif(e:e,e2)=1

把两者用and函数结合起来,即可实现位数和非重复双重验证。

=and(len(e2)=18, countif(e:e,e2)=1)

 

 

三、日期验证和格式统一

假定入职日期我们需要按“1998-1-14”的方式统一录入,不能出现“1998114”“1998.1.14”“1998/1/14”等形式;另外日期的范围需要限制在1950年到2002年之间。

 

步骤

      选中需要设置规则的区域“F2:F8”,按Ctrl+1设置日期格式为“2012-03-14

 

 

      点击数据有效性按钮,进入设置选卡

      允许栏中,选择日期

      数据栏选择介于,开始日期设置为1950-1-1,结束日期设置为2002-12-31

 

 

确定后EXCEL只接受以“2001-4-12”“1-4-12”“2001/4/12”“1/4/12”“2001412日”“1412日”的方式录入日期,录入后日期统一显示为2001-4-12的样式。如果录入格式不对,以及录入日期不在规定的范围内,则会弹出错误提示。

 

 

 

四、录入前的用户提示

利用数据验证不但可以在数据录入后验证其是否符合设置的规则,也可以在录入前提示用户该怎么做。譬如当用户在身份证号单元格上单击鼠标时就弹出提示“请输入18位号码;最后一位是字母的话,必须是大写的X”。

步骤:

    选中需要设置的区域“E2:E8”

    点击数据验证按钮,进入输入信息界面,勾选“选定单元格时显示输入信息”

    标题栏和“输入信息”栏,分别录入相关提示,点击确定

 

 

设置后我们选中“身份证号”下的单元格,即可看到提示内容:

 

 

讲了这么多关于数据验证的应用,小编还要偷偷告诉小伙伴其中的一个BUG

不知道小伙伴有没有发现,数据有效性只对设置后录入的数据有用,设置前录入的数据不受规则限制:你设置或者不设置,我就在那里,不改不变~~~

那我们怎样才能一眼找出设置前录入有误的数据呢?其实很简单~

 

五、圈释无效数据

利用“圈释无效数据”功能可以把数据验证设置前录入的错误数据找出来。下面以考核得分为例介绍圈释无效数据。

步骤:

      首先选中G2:G8单击数据验证按钮,在“设置”选卡中将允许设置为“任意值”,然后确定,取消前面的数据验证设置

      G2:G8中随意输入一些数据,有大于10的,有小于10

      选中G2:G8,设置数据验证,规则为0-10的整数

      确定后,点击“数据验证”下拉箭头(是点箭头哈!千万不要点“数据验证”哦~),选择 “圈释无效数据”命令

 

 

此时,不符合规定的数据,都被画上了红圈圈。

 

 

按规则修改圆圈中数字之后,红圈就会消失。

 

 

提示:如果想取消圈释,直接单击“数据验证”下拉箭头,选择“清除验证标识圈”命令即可。

 

 

好了,关于数据验证的内容就讲到这里啦~小伙伴们可要勤加练习哦~

 

 

本文配套的练习课件请加入QQ群:264539405下载。

Excel高手,快速提升工作效率,部落窝教育《一周Excel直通车》视频和Excel极速贯通班》直播课全心为你!

扫下方二维码关注公众号,可随时随地学习Excel

http://www.itblw.com/ewebeditor/uploadfile/20180914164059384001.png

相关推荐:

数据有效性技巧1《数据有效性只能引用一列数据?但他这样用1000列也行!

数据有效性技巧2 3个小招让数据有效性更高效

数据有效性基础1Excel小白的数据验证课①用下拉菜单录入的那些事儿