Excel图文教程：数据验证的高级应用技巧（公式篇）

玄明堂

编按：之前给大家介绍了几个数据验证的基础操作技巧，今天继续介绍Excel数据验证的高级应用技巧，可以使用公式更有效地限制用户可以输入到单元格中的数据类型或范围，让大家对数据验证有更深一步的了解和掌握。
技巧1：自动更新下拉选项

想通过下拉选项输入部门，并且可以通过在单元格里增减部门名称来调整选项中的内容。



技巧中用到的公式是=OFFSET(J1,1,,COUNTA(J:J)-1)

可以根据下拉选择存放的具体位置修改公式即可。
技巧2：批量标注不及格的成绩

对于一份成绩表，想快速标注出低于60分的成绩。



这个技巧里用到的公式很简单，但是涉及到一个平时很少用的功能【圈释无效数据】。

技巧3：限制不能输入重复数据

每个人的工号都是唯一的，可以通过有效性设置防止工号录入重复。



用到的公式为=COUNTIF(D:D,D2)<2，还可以用=COUNTIF(D:D,D2)=1，效果是一样的。

技巧4：限制只能输入两位小数以内的数字

录入员工绩效评分的时候，范围是0-10，可以是两位小数。



用到的公式为=TRUNC(E2,2)=E2，这里用到了一个函数TRUNC，简单介绍一下。

TRUNC函数的功能是将数字截取到指定的位数，格式为：TRUNC(要截取的数字，截取到第几位)。
本例中第二参数为2，即表示将数字小数点后2位（不四舍五入）的小数部分直接截去。
如果截去后的数字等于原来的数字，则表示这个数字小数点不超过2位。
技巧5：限制输入不规则的日期

很多人输入日期非常随意，导致后期要处理表格，难上加难。
Excel里的标准日期是以“-”或者“/”作为分隔符的，我们可以让单元格只输入日期，从源头上避免乱输入日期格式，从而在后期处理数据得心应手。



例子中用到的公式为=CELL("FORMAT",F2)="D1"。

CELL函数的功能是返回单元格的格式，格式为：CELL("FORMAT",单元格)，D1表示“年-月-日“格式，用CELL来判断单元格的格式是否为D1，就可以避免不规范的日期。
以上5个例子只是帮大家开了个头，只要掌握了在数据验证里使用公式的方法，就会打开另一片天地，能有多少更好的用法就取决于你的公式函数水平了。

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

做Excel高手，快速提升工作效率，部落窝教育《一周Excel直通车》视频和《Excel极速贯通班》直播课全心为你！
相关推荐：
将excel中的数据按照指定数量进行重复，非得用VBA才能实现吗？
如何在特定位置批量插入空行等12种实用办公技巧
4种删除excel重复值的小妙招，速收藏
Power Query的数据替换技巧比Excel函数更万能！
版权申明：
本文作者老菜鸟；同时部落窝教育享有专有使用权。若需转载请联系部落窝教育。