excel设置数据验证条件

Answer 1

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函数的判断结果即为假（不允许输入）。

Answer 2

Step1：在Excel中选择要限制输入内容的单元格；

鼠标拖动单元格，可多选也可单选

Step2：点击【数据】选项卡下【数据工具】组中的【数据验证】，再选择下拉中的【数据验证】选项；

注意红色框内容

Step3：在弹出的【数据验证】对话框中选择【设置】选项卡；

Step4：在【验证条件】中选择“允许”的类型（即对单元格想要限制的“类型”或“格式）和“数据”的范围，点击“确定”按钮即可设置成功。

*注意·注意·注意*

1. 验证条件选择允许“任何值”，则Excel单元格中输入的内容将不受限制；

选择“任何值”时，不需要再选择“数据”范围

2. 验证条件选择允许“整数”、“小数”、“日期”和“时间”后，还需要设置【数据】的范围及临界值，则Excel单元格中输入的内容将受到该类型及临界值的限制；

选择【数据】范围

设置【数据】临界值（当【数据】为日期和时间时，注意临界值的格式）

3. 验证条件选择允许“序列”后，还需要设置【来源】（即该“序列”中允许包含的值），则Excel单元格中将仅限输入“序列”中包含的值；

1) 【来源】中输入的值以“逗号”隔开；

2) 若勾选【提供下拉箭头】选项，则可通过点击下拉框的方式将【来源】中设置的值选择到Excel表格中；若不勾选，则输入非【来源】中的值将报错；

4. 验证条件选择允许“文本长度”后，还需要设置“文本长度"的范围及临界值，则Excel单元格中输入的内容长度将受到范围及临界值的限制；

5. 验证条件选择允许“自定义”后，还需要设置【公式】，则Excel单元格中输入的内容将受到公式的限制，具体举例如下：

1) 例如在Excel中B3单元格仅限输入“文本”；

（B3）指的是单元格

2) 例如在Excel中B3单元格仅限输入“数值”；

（B3）指的是单元格

小贴士—小功能介绍

忽略空置：设置【验证条件】时，如果不勾选“忽略空值”选项，通过“Backspace”键删除Excel单元格中的内容时会正常报错提示；

2.设置验证条件的“数值”、“临界值”、数据“来源”或“公式”时，从Excel表格中直接选择内容；

点击“数值”、“临界值”、数据“来源”或“公式”后的箭头

拖动鼠标选择内容

3. 对同样设置的所有其他单元格应用这些更改：设置【验证条件】时，勾选“对同样设置的所有其他单元格应用这些更改”时，将对设置相同“数据验证”的单元格进行更改，具体举例如下：

1) 对Excel中所选单元格B3：B17设置“序列”数据验证“1,2,3,4,5”，则B3：B17的单元格均可录入1到5的数字；

选择单元格B3：B17

设置“序列”数据来源为“1,2,3,4,5

则B3：B17的单元格均可录入1到5的数字

2) 仅对Excel中所选单元格B3设置“序列”数据验证“a,b,c,d,e”，若勾选“对同样设置的所有其他单元格应用这些更改”则B3：B17的单元格均可录入a到e的字母；若不勾选则仅单元格B3可录入a到e的字母；

仅对单元格B3设置“序列”数据来源“a,b,c,d,e”

Answer 3

数据-数据工具-数据验证。可以选择整数、日期、自定义序列等。