在excel中,为了在输入数据时尽量少出错,以及为了更高效率的输入,可以通过使用Excel的“数据有效性”来设置单元格中允许输入的数据类型或有效数据的取值范围。

一、位置

首先,我们来看看数据有效性在excel表中的位置,注意名称为“数据验证”,如下图所示:

wps数据验证在哪里(Excel与WPS表格中数据有效性验证的使用)(1)

在WPS中,是如下图的位置,注意名称为“有效性”,如下图所示:

wps数据验证在哪里(Excel与WPS表格中数据有效性验证的使用)(2)

虽然在Excel与WPS中名称不太一样,但是它们的位置是类似的,功能都是一样的。

二、类型

有效性验证的类型如下图所示,这个选项Excel与WPS是一样的,后面也就一起讲解,不再做区分,所有截图使用Excel2016版。验证条件默认是允许“任何值”,即代表不做数据验证,任何值都可以。其他选项则有相应的验证。

wps数据验证在哪里(Excel与WPS表格中数据有效性验证的使用)(3)

三、有效性“序列”的使用

日常用到相对较多的就是“序列”的验证了,下面我们就以输入一个学员信息表为例进行讲解。

wps数据验证在哪里(Excel与WPS表格中数据有效性验证的使用)(4)

首先,我们要选择要设置有效性验证的表格,如下图所示,然后按照箭头所示顺序,设置“性别”列的序列为“男,女”,注意男与女中间要用英文状态下逗号“,”进行分隔,当然对于更多选项的还可以按照“选项一,选项二,选项三,选项四,……”,也就是说更多选项都没问题的,注意的是,选项中间一定要是英文状态下的逗号!英文状态下的逗号!英文状态下的逗号!英文状态下的逗号!重要的事情不止说三遍,O(∩_∩)O~

wps数据验证在哪里(Excel与WPS表格中数据有效性验证的使用)(5)

选择完之后,使用的效果就如下图所示了。点击性别这列刚才我们设置的单元格,右侧会出现向下的箭头,点击箭头就可以选择了。下图就是具体的操作顺序。

wps数据验证在哪里(Excel与WPS表格中数据有效性验证的使用)(6)

有些时候,我们还需要在输入的单元格增加输入提示信息或者输入错了之后,自定义错误提示信息。这个Excel也是支持的。

输入提示信息设置及显示效果如下图所示:

wps数据验证在哪里(Excel与WPS表格中数据有效性验证的使用)(7)

出错警告信息设置如下图所示:

wps数据验证在哪里(Excel与WPS表格中数据有效性验证的使用)(8)

出错警告信息显示效果如下图所示:

wps数据验证在哪里(Excel与WPS表格中数据有效性验证的使用)(9)

对于数据有效性验证的来源,还有另外一种方式就是直接引用某个区域。如下图所示,可以直接在来源中输入“=$H$2:$H$3”(有不知道$这个符号的,可以关注我们下期介绍),点击确定之后跟在这里输入“男,女”的效果是一样一样一样的。

wps数据验证在哪里(Excel与WPS表格中数据有效性验证的使用)(10)

通过以上的例子可以看到,使用序列可以限制输入的范围,保证了内容正确性,同时默认提供下拉选项,可以下拉选择,方便快捷输入。

四、有效性“日期”的使用

有效性验证类型中,除了“序列”跟“自定义”其他选项包括日期,时间,整数,小数,文本长度其实在使用的时候有效性验证方式都是相似的,都提供了“介于、未介于、等于、不等于、大于、小于、大于或等于、小于或等于”这些验证区间的方式。所以我们只讲解日期类型的,其他相似的将不再细讲。下面我们先分别讲解各种验证区间的意思。

介于:例如我们只允许输入本月入学信息,则可以设置开始日期为2016-6-1,结束日期为2016-6-30。这样非本月日期则将无法输入。实际输入内容如下图所示。

wps数据验证在哪里(Excel与WPS表格中数据有效性验证的使用)(11)

未介于:例如我们只允许输入非本月的日期,注意此处是非本月日期。开始还是2017-6-1,结束日期还是2017-6-30,虽然区间跟上面是一样的区间,但是意思恰恰相反,此处是只有非本月的可以输入。注意下图,就是选择的未介于。

wps数据验证在哪里(Excel与WPS表格中数据有效性验证的使用)(12)

等于:例如只允许输入2017-6-7,则在日期中输入2017-6-7。

不等于:例如只要输入的不是2017-6-7就可以,则在日期中输入2017-6-7。

大于:例如日期只要在2017-6-7以后都可以。则在开始日期中输入2017-6-7。

小于:例如日期只要在2017-6-7以前都可以。则在结束日期中输入2017-6-7。

大于或等于:例如日期只要在2017-6-7(含这天)以后都可以。则在开始日期中输入2017-6-7。

小于或等于:例如日期只要在2017-6-7(含这天)以前都可以。则在结束日期中输入2017-6-7。

五、有效性“自定义”的使用

如果上面的数据有效性验证还不能满足你的需求,那么这个自定义就是终极武器了。来看看这个例子:包含产品 ID 的单元格 (C列) 始终以标准前缀“ID-”开头,且至少包含 10 个(大于 9 个)字符。则之前的验证都解决不了,此时可以用自定义验证,在公式中输入“=AND(LEFT(A3, 3) ="ID-",LEN(A3) > 9)”,则可以实现上述要求。截图如下:

wps数据验证在哪里(Excel与WPS表格中数据有效性验证的使用)(13)

好了,至此数据有效性的验证讲完了。