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

一、避免输入重复值

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

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

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

2、限制单元格输入的产品价格必须包括两位小数

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

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

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

3、限制输入空格

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

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

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

4、限订单元格内必须包括指定内容

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

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

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

5、制止出库数量大于库存数

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

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

6、用行之有效的性设置保护公式

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

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

excel 如何利用公式设置数据验证条件的下载地址：

下载地址1