9个经常使用的数据验证（数据有效性）场景 / 开普饭

数据验证（以前的版本中叫做数据有效性）是Excel中一个非常有用的功能。我们可以使用这个功能控制对单元格内容的输入，保证输入的数据符合我们的要求。

对于不熟悉这个功能的朋友，可以按照以下步骤操作，即可使用数据验证了：

选择希望进行数据验证的单元格区域
点击“数据”菜单，点击“数据验证”
设置数据验证规则

下面我们就介绍那些常用的数据验证场景

只能输入今天以前的日期

我们总是强调Excel数据中日期采用标准的日期格式，因为这会给我们后续处理和分析数据带来极大的方便。但是，我们拿到的数据经常包含错误的日期，这里面既包含不正确格式的日期数据，也包含那些不应该出现在数据中的日期，例如“将来的日期”。

步骤如下：

选中需要限制日期输入的区域
点击“数据”菜单，点击“数据验证”。在“允许”框中选择“日期”，在“数据”框中选择“小于或等于”，在“结束日期”框中输入公式：

=TODAY()

创建下拉列表

创建下拉菜单是一个常用的需求。步骤如下：

选择需要下拉列表的区域
在“数据验证”对话框中选择允许“序列”，在来源中输入序列。

这里有两种情况，一种是直接输入序列：

第二种是引用一个区域

多级联动下拉列表

多级联动下拉列表也非常有用。在这种列表中，我们的第二个列表的内容依赖于第一个列表的选择。

例如，在下图中，I3中选择了事业部，在J3的下来列表中，只能出现该事业部对应的部门。

要实现这个效果，必须定义名称（Name）。

首先，I3单元格的数据验证规则还是“序列”，来源是“C2:E2”。

重点是J3中的数据验证规则：

选中C2:E7区域，按Ctrl+Shift+F3，出现如下对话框：

点击确定后，设置J3单元格的数据验证规则为“序列”，“来源”中输入公式：

=INDRECT(I3)

禁止输入重复值

重复值是数据处理中面临的常见问题之一。通常在进行数据分析之前都需要进行数据清洗，其中重复值处理是一个重要步骤。当然，最好还是在一开始就避免输入重复值。

我们使用下面的函数来进行数据验证：

=COUNTIF($B$3:$B$8,B2)=0

这个函数计算B2:B8区域中出现的次数，如果次数为0（即不重复），则允许输入。

注意，这里的规则是“自定义”。

只允许数值（或文本）

可以使用Isnumber函数来限制只能输入数值。

如果只允许文本，公式改成：

=ISTEXT(B3)

只允许工作日的日期

我们可以限制只能输入一周中的某些日期，比如，只有周一至周五才可以输入。

限制文本长度

有时，我们会限制输入的文本长度，比如，合同编号只能是11位。

包含特殊的文本

像合同编号之类的信息，有时需要保证其中某些子串是一致的。例如，上例中，合同编号必须以“C_TP_”开始。

Find是个文本函数，用于在一个字符串中查找另外一个字符串，如果找到了，就返回该子串的位置，否则，返回错误值。Isnumber用于判断Find的返回值，如果是数值，表示Find找到了那个子串。

使用有意义的出错信息

很多人设置了数据验证规则，然后别人在输入数据时，违反了规则，就会得到如下的信息：

这个提示太不友好了！输入者完全摸不着头脑：哪里错了？正确的又是什么？

所以，不管你设置了哪种数据验证规则，都需要设置友好的出错提示信息。

这样，一旦输入了不满足规则的数据，就会得到下面的提示信息：

输入者很清楚应该如何调整输入信息了。

总结

数据验证是一个被很多人忽略的功能。在很多地方，它可以帮助我们获得满足要求的数据，从而为后续的分析（数据透视表，图表等）提供正确的源数据。

但是，如果数据不是输入的，而是从其他来源（网站，数据库等）导入的，数据验证就起不到应有的作用了。在这种情况下，建议大家学习使用Excel中的Power Query。可以阅读这个专辑中的文章：

Excel大数据分析 — Power BI

点个赞

再走吧

9个经常使用的数据验证（数据有效性）场景

相关推荐