Excel小白的数据验证课②身份证的双重验证设置等
编按:哈喽!小伙伴们大家好!上期我们主要讲了用系列做下拉菜单确保数据规范的操作。今天我们主要讲数据验证的其他操作,如数字区间设置、身份证双重验证、输入提示等,让小伙伴一次学个通透!
还是原来的配方,还是原来的表!
一、设置数值区间验证
我们的评分范围是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”的方式统一录入,不能出现“1998年1月14”“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”“2001年4月12日”“1年4月12日”的方式录入日期,录入后日期统一显示为2001-4-12的样式。如果录入格式不对,以及录入日期不在规定的范围内,则会弹出错误提示。
四、录入前的用户提示
利用数据验证不但可以在数据录入后验证其是否符合设置的规则,也可以在录入前提示用户该怎么做。譬如当用户在身份证号单元格上单击鼠标时就弹出提示“请输入18位号码;最后一位是字母的话,必须是大写的X”。
步骤:
① 选中需要设置的区域“E2:E8”
② 点击“数据验证”按钮,进入“输入信息”界面,勾选“选定单元格时显示输入信息”
③ 在“标题”栏和“输入信息”栏,分别录入相关提示,点击“确定”
设置后我们选中“身份证号”下的单元格,即可看到提示内容:
讲了这么多关于数据验证的应用,小编还要偷偷告诉小伙伴其中的一个BUG!
不知道小伙伴有没有发现,数据有效性只对设置后录入的数据有用,设置前录入的数据不受规则限制:你设置或者不设置,我就在那里,不改不变~~~
那我们怎样才能一眼找出设置前录入有误的数据呢?其实很简单~
五、圈释无效数据
利用“圈释无效数据”功能可以把数据验证设置前录入的错误数据找出来。下面以考核得分为例介绍圈释无效数据。
步骤:
① 首先选中G2:G8单击数据验证按钮,在“设置”选卡中将“允许”设置为“任意值”,然后确定,取消前面的数据验证设置
② 在G2:G8中随意输入一些数据,有大于10的,有小于10的
③ 选中G2:G8,设置数据验证,规则为0-10的整数
④ 确定后,点击“数据验证”下拉箭头(是点箭头哈!千万不要点“数据验证”哦~),选择 “圈释无效数据”命令。
此时,不符合规定的数据,都被画上了红圈圈。
按规则修改圆圈中数字之后,红圈就会消失。
提示:如果想取消圈释,直接单击“数据验证”下拉箭头,选择“清除验证标识圈”命令即可。
好了,关于数据验证的内容就讲到这里啦~小伙伴们可要勤加练习哦~