今天来学习一组关于 数据验证 的相关技巧。

这个功能的用处是验证用户输入到单元格的 数据是否有效 ，可以 限制输入数据的类型或范围，防止用户输入无效数据 。

总之，就有点像是 excel里的教导主任 ，时刻准备着规范你的言行，稍不注意就给你个警告那种。

技巧1：利用下拉选项输入性别

使用“数据验证”的“序列”功能，制作可选择下拉菜单。

操作要点：选项之间用英文状态的逗号进行分隔。

技巧2：F2键的妙用

有时候选项比较多，在输入“序列”的过程中来回切换中英文状态比较麻烦，想先把汉字输完，再输入逗号，这时候就需要用方向键来移动光标的位置，但是在实际操作的时候，就很容易出现状况。

在移动鼠标的时候会出现+$B$2这样的情况，新手往往就不知所措了。

有些老手可能会用鼠标配合定位光标的位置，但这样肯定不如直接用方向键方便。

遇到这种情况，先按一下F2键，再去移动光标就可以了。

技巧3：只复制下拉选项而不影响原来的格式

可以复制已经设置好下拉选项的单元格，然后粘贴到其他的单元格，但是原来的格式会受到影响。

能不能只复制下拉选项呢，其实用选择性粘贴就可以实现。

技巧4：选中单元格时出现提示信息

有的时候无法通过下拉选项来限制用户输入数据，但是又得让用户了解输入的规则，就需要用到提示信息。

例如在输入工号的时候，要显示以下提示内容： 1.请检查单元格是否为文本格式；

2.工号为六位数字的形式，前两位为部门编号，后四位为员工编号；

3.部门编号对应规则：人事部01，销售部02，采购部03，财务部04。

设置方法为：

技巧5：如何设置可以输入下拉选项以外的内容

通常下拉选项是严格限制输入内容的，不在选项范围内的数据不能输入。

但在有些情况下，我们想实现的是通过下拉选项控制最常用的一些输入内容，也允许输入不在选项里的内容。

操作方法为：

以上就是有关“数据验证”的基础操作技巧，你学会了吗？

下面，再来给大家分享一组结合函数公式的数据验证用法。

赶紧跟我们一起来看看吧~

技巧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，就可以避免不规范的日期。

只要掌握了在数据验证里使用公式的方法，大家就能举一反三，打开另一片天地哟~

(职场责编：拓荒牛 )