编按：

哈喽，大家好！今天小E特意为大家总结了数据有效性的7个实用案例，全面剖析了数据有效性的使用方法。不管是各种验证条件的应用，还是出错信息和出错警告的设置。看完这篇文章，大家都可以轻松掌握！

为了保证表格中输入的数据都是有效的，可以提前设置单元格的数据验证功能。通过数据验证设置数据有效性后，不仅可以减少输入错误的概率，保证数据的准确性，还可以圈释无效数据。下面一起来学习下。

一、数据验证的验证条件

在向工作表中输入数据时，为了防止输入错误的数据，可以为单元格设置有效的数据范围，这样可以极大地减少数据处理中的操作复杂性。

选中要设置数据有效性的单元格，用鼠标点击“数据”选项卡下“数据工具”选项组中的“数据验证”按钮。在弹出的“数据验证”对话框中，选择“设置”，在“允许”的下拉列表中选择合适的数据格式类型。

数据格式类型分别有：任何值、整数、小数、序列、日期、时间、文本长度、自定义。其中大部分都比较好理解，下面，笔者和大家一起熟悉一下。

1.数据格式为“序列”。

序列条件是指为有效性数据制定一个序列，序列的内容可以是单元格引用或公式，也可以是手动输入的内容。

【例1】在性别栏输入男女。

选择B列单元格区域，选择“数据验证”按钮。在弹出的“数据验证”对话框中，选择“允许”下拉列表中的“序列”选项，在“来源”文本框中输入“男,女”。最后，用鼠标点击“确定”按钮即可。

如此，返回工作表中后，用鼠标点击设置了有效验证条件的任意单元格，在单元格右侧都会出现一个下拉按钮，点击按钮即可选择指定的序列内容。

注意：

①输入序列内容时，以半角的逗号隔开不同的内容项。

②如果勾选“提供下拉箭头”复选框，被设置的单元格右侧会出现下拉箭头，用鼠标点击该按钮，序列内容将出现在下拉列表中。（本案例中已勾选“提供下拉箭头”复选框。）

2.数据格式格式为“文本长度”。

“文本长度”条件，将数据输入限制为指定长度的文本。

【例2】输入手机号码。

同上，在弹出的“数据验证”对话框中，选择“允许”下拉菜单中的 “文本长度”选项，在“数据”文本框中选择“等于”，在长度栏输入“11”。最后，用鼠标点击“确定”按钮即可。

如此，当输入的文本长度不等于11时，会提示“此值与此单元格定义的数据验证限制不匹配”。

3.数据格式为“自定义”。

使用自定义类型时，允许用户使用自定义公式、表达式或引用其它单元格的计算值，来判定输入数据的有效性。

【例3】判断车牌输入是否正确。

如下图所示，要求A列的车牌号必须输入以汉字开头，且总长度为7位的内容。输入错误就禁止输入。

数据有效性公式：“=AND(LENB(LEFT(B2))=2,LEN(B2)=7)”。

注意：

汉字占用2个字节，数字和字母占用1个。

【例4】每行输入完成才能输入下一行

如下图，当在excel表格的A:D输入时，只有上一行的四列都输入数据，才能在下一行中输入内容；否则就无法输入并提示错误信息。

操作：

选取A2:D100（写D100是为了能保证所有数据行数被包括到其中，读者也可以依据实际数据来设置），用鼠标依次点击“数据”、“数据验证”、“允许”，选择“自定义”，在来源框中输入以下公式：“=COUNTA($A1:$D1)=4”。

公式说明：

COUNTA()函数可以统计非空单元格个数。$A1:$D1添加$是把范围固定在A:D列。

【例5】库存表中有才能出库

如下图所示，左边为库存表，要求在右边出库表中设置限制。即当出库量大于库存量时,则禁止在出库表中输入内容，并提示“此值与此单元格定义的数据验证限制不匹配”。

操作:

用鼠标依次点击“数据”、“数据验证”、“允许”，选择“自定义”，在来源框中输入以下公式：“=E3<=VLOOKUP(D3,A:B,2,0)”,就可以限制E列中商品的数量为小于B列中该商品对应的数量。

小结：

在数据有效性中使有公式，可以完成复杂的判断和输入限制。对于规范表格的数据输入非常有帮助。

二、设置出错信息和出错警告提示

1.设置输入前的提示信息

用户输入数据前，如果能够提示输入什么样的数据是符合要求的，那么出错率就会大大下降。

【例6】输入学号前，提示用户应该输入8位数的学号。

如下图，当鼠标点击B2:B7单元格区域的任意单元格时，就会有提示信息。

操作：

选中B2:B7单元格区域，选择“数据验证”按钮。在弹出的“数据验证”对话框中，选择“输入信息”，在“标题”栏中输入“学号”，在“输入信息”文本框中输入“应输入8位数的学号”。最后，用鼠标点击“确定”按钮即可。

2.设置输入错误时的警告信息

用户可以通过设置，在输入的数据不符合要求时，弹出警告信息。

同样以【例6】（输入8位数的学号）为例子，介绍该功能。

步骤1：

同上，在弹出的“数据验证”对话框中，点击“设置”，在“允许”的下拉列表中选择“文本长度”，在“数据”下拉列表中选择“等于”，在“长度”文本框中输入“8”。最后，用鼠标单击“确定”按钮即可。

步骤2：

选择“出错警告”选项卡，在“样式”下拉列表中选择“警告”，在“标题”中输入“输入错误”，在“错误信息”中输入“警告信息”，用鼠标点击“确定”按钮即可。

返回工作表后，当输入不符合要求的数字时，会提示如下的警告信息。

3.圈定无效数据

是指系统自动的将不符合要求的数据用红色的圈标注出来，以便查找和修改。

【例7】将学生出生时间较早的日期标识出来。

步骤1：

选中C2:C7单元格区域，选择“数据验证”按钮。在弹出的“数据验证”对话框中，选择“设置”，在“允许”的下拉列表中选择“日期”，在“开始日期”栏中输入“1986-1-1”，在“结束日期”栏中输入“1995-1-1”。最后，用鼠标点击“确定”按钮即可。

步骤2：

选择C2:C7单元格区域，选择“数据验证”按钮，在右侧的下拉按钮中选择“圈示无效数据”。此时，区域中的无数数据就会以椭圆标注出来了。

如果想清除数圈示，只需要在“数据验证”按钮的下拉菜单中选择“清除验证标识圈”，红色的标识圈就会自动消除了，很简单，这里就不做演示了。

OK，今天大家通过很多例子系统的学习了数据有效性，这样不仅可以减少输入错误的概率，保证数据的准确性，提高工作效率，还可以圈示无效数据。小伙伴们，还有别的什么想法，欢迎到公众号中留言。

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

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

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

相关推荐：

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

3个小招让数据有效性更高效

最简单的多级下拉菜单制作方法，不需要定义名称

更高效的搜索式下拉菜单，你一定要懂！

版权申明：

本文作者赋春风；同时部落窝教育享有专有使用权。若需转载请联系部落窝教育。