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

还是原来的配方，还是原来的表！

一、设置数值区间验证

我们的评分范围是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的整数

④      确定后，点击“数据验证”下拉箭头（是点箭头哈！千万不要点“数据验证”哦～），选择 “圈释无效数据”命令

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

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

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

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

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

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

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

相关推荐：

数据有效性技巧１《数据有效性只能引用一列数据？但他这样用1000列也行！》

数据有效性技巧２《 3个小招让数据有效性更高效》

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