excel 如何利用公式设置数据验证条件

Excel教程 2021-10-04 08:38:37

Excel程序内置的验证条件可以解决一些常见的数据输入限制问题，但若想更加灵活地控制数据输入，则需要用公式设置验证条件。只要你对函数有足够的了解，则可以设计出非常灵活的验证条件，如“限制输入空格”“限制输入重复值”等。本文将用以下示例说明如何应用公式设置数据验证条件。

利用公式设置数据验证条件

一、避免输入重复值

用户在进行数据录入时，由于录入数据量较大，可能会发生重复录入的情况。比如在录入学生姓名制作成绩报表时，为了防止数据的录入重复，可以设置当数据录入重复时给出提示或者禁止录入。

具体步骤为：选中A2:A9单元格区域，在“数据”选项卡的“数据工具”组中单击“数据验证”按钮，打开“数据验证”对话框 - 选择“设置”标签，在“验证条件”栏下设置“允许”条件为“自定义”，在“公式”栏下的文本框内输入公式“=COUNTIF(A:A,A2)<2” - 单击“确定”按钮完成设置。当输入的姓名重复时就会自动弹出提示框，重新输入不重复的姓名即可。

注意：公式“=COUNTIF(A:A,A2)<2”用来统计A列中A2单元格中数据的个数，如果小于2则允许输入，否则不允许输入。因为当其个数大于1就表示出现了重复值了。

二、限制单元格输入的产品价格必需包含两位小数

本例记录了蔬菜的单价信息，为了规范单价的录入（需要保留两位小数），可以使用自定义公式，当录入不是两位小数的数据时自动弹出提示框。

具体步骤为：选中B2:B20单元格区域，在“数据”选项卡的“数据工具”组中单击“数据验证”按钮，打开“数据验证”对话框 - 选择“设置”标签，在“验证条件”栏下设置“允许”条件为“自定义”，在“公式”栏下的文本框内输入公式“=LEFT(RIGHT(B2,3),1)="."” - 单击“确定”按钮完成设置。当输入的小数位数不是两位数时就会自动弹出提示框，重新输入正确的小数位数（两位小数）即可。

注意：公式“=LEFT(RIGHT(B2,3),1)="."”，首先使用RIGHT函数从B2单元格中数据的右侧提取3个字符，接着使用LEFT函数从上步结果的左侧提取1个字符，判断其是否是小数点“.”,如果是则满足条件，不是则不满足条件。

三、限制输入空格

手工输入数据时，经常会有意或无意地输入一些空格，这些数据如果只是用于查看，有无空格并无大碍，但如果要用于统计查找，如“北京”和“北京”，则会被当作两个完全不同的对象，这时的空格则为数据分析带来困扰。为了规范数据的录入，则可以使用数据验证限制空格的录入，一旦有空格录入就会弹出提示框。

具体步骤为：选中B3:B13单元格区域，在“数据”选项卡的“数据工具”组中单击“数据验证”按钮，打开“数据验证”对话框 - 选择“设置”标签，在“验证条件”栏下设置“允许”条件为“自定义”，在“公式”栏下的文本框内输入公式“=ISERROR(FIND(" ",B3))” - 单击“确定”按钮完成设置。当输入的文本中间有空格时就会自动弹出提示框，重新输入正确的文本（不包含空格）即可。

注意：公式“=ISERROR(FIND(" ",B3))”，首先用FIND函数在B3单元格中查找空格的位置，如果找到返回位置值，如果未找到则返回的是一个错误值，然后用ISERROR函数判断值是否为错误值，如果是则返回TRUE,不是则返回FALSE。本例中当结果为“TRUE”时则说明未找到空格，此时允许输入，否则则不允许输入。

四、限定单元格内必需包括指定内容

例如某产品规格都是以“OMD”开头的，要求在输入产品规格时，只要不是以“OMD”开头的就自动弹出错误提示框，并提示如何才能正确输入数据。

具体步骤为：选中C3:C13单元格区域，在“数据”选项卡的“数据工具”组中单击“数据验证”按钮，打开“数据验证”对话框 - 选择“设置”标签，在“验证条件”栏下设置“允许”条件为“自定义”，在“公式”栏下的文本框内输入公式“=ISNUMBER(SEARCH("OMD?",C3))” - 切换至“出错警告”标签，在“输入无效数据时显示下列出错警告”栏下的“样式”下拉列表中选择“警告”，再分别设置“标题”和“错误信息”内容即可 - 单击“确定”按钮完成设置。当输入错误的产品规格时，会弹出错误警告提示框。

注意：ISNUMBER函数用于判断引用的参数或指定单元格中的值是否为数字。SEARCH函数用来返回指定的字符串在原始字符串中首次出现的位置。公式“=ISNUMBER(SEARCH("OMD?",C3))”用于在C3单元格中查找“OMD”，找到后返回其位置，位置值是数字，所以外层的ISNUMBER函数的判断结果即为真（允许输入）；如果找不到，SEARCH函数返回错误值，外层的ISNUMBER函数的判断结果即为假（不允许输入）。

五、禁止出库数量大于库存数

月末要编辑产品库存表，其中已记录了上月的结余量和本月的入库量。当产品要出库时，显然出库量应该小于库存量。为了保证可以及时发现错误，需要设置数据验证，禁止输入的出库量大于库存量。

具体步骤为：选中 E2:E12单元格区域，在“数据”选项卡的“数据工具”组中单击“数据验证”按钮，打开“数据验证”对话框 - 选择“设置”标签，在“验证条件”栏下设置“允许”条件为“自定义”，在“公式”栏下的文本框内输入公式“=C2+D2>E2” - 切换至“出错警告”标签，在“输入无效数据时显示下列出错警告”栏下的“样式”下拉列表中选择“停止”，再设置“错误信息”内容 - 单击“确定”按钮完成设置。当在E2:E12中输入的出库数量小于库存数时允许输入，当在E2:E12中输入的出库数量大于库存数时（上月结余与本月入库之和），系统则会弹出错误警告提示框。

六、用有效性设置保护公式

我们知道通过设置工作表保护的操作可以实现对表格中公式的保护，同样，为了保护公式结构不被修改，也可以使用数据验证功能巧妙实现。

具体步骤为：打开包含公式的表格后，在“开始”选项卡的“编辑”组中单击“查找和选择”下拉按钮，在打开的下拉列表中选择“公式”，即可选中表格中所有包含公式的单元格 - 保持单元格的选中状态，在“数据”选项卡的“数据工具”组中单击“数据验证”按钮，打开数据验证对话框 - 选择“设置”标签，在“验证条件”栏下设置“允许”条件为“自定义”，在“公式”栏下的文本框内输入0 - 切换至“出错警告”标签，在“输入无效数据时显示下列出错警告”栏下的“样式”下拉列表中选择“警告”，再分别设置“标题”和“错误信息”内容即可 - 单击“确定”按钮完成设置。当对含有公式的单元格进行编辑修改时，就会弹出警告提示框。

标签： excel设置数据验证条件

上一篇：如何利用鼠标和键盘输入excel公式下一篇：如何设置excel的列宽和行高