2.3 数据有效性验证
数据有效性验证是指让指定单元格中所输入的数据满足一定的要求，如只能输入指定范围的整数，只能输入日期，设置可选择输入序列，添加公式验证等，根据实际情况设置数据有效性后，可以有效防止在单元格中输入无效的数据。
2.3.1 限制只能输入指定类型数据
关键点：限制允许输入的数据类型
操作要点：“数据”→“数据工具”→“数据验证”→“允许”
应用场景：对所输入的数据有限制，如只能是日期、整数、小数等，可设置为指定类型
1．只允许输入日期
例如，某些单元格区域中只允许输入当月的日期，可以按如下方法设置数据验证。
①选择需设置的单元格区域，在“数据”选项卡的“数据工具”组中单击“数据验证”按钮，如图2-65所示，打开“数据验证”对话框。
图2-65
②在“允许”下拉列表中选择“日期”，在“数据”下拉列表中选择“介于”，然后设置“开始日期”和“结束日期”，如图2-66所示。
图2-66
③单击“确定”按钮完成设置。当在单元格中输入程序无法识别为日期的数据时会弹出错误提示，如图2-67所示；当在单元格中输入不在指定区间的日期时也会弹出错误提示，如图2-68所示。
图2-67
图2-68
2．只允许输入指定范围的整数
①选择需设置的单元格区域，在“数据”选项卡“数据工具”组中单击“数据验证”按钮，如图2-69所示，打开“数据验证”对话框。
图2-69
②在“允许”下拉列表中选择“整数”，在“数据”下拉列表中选择“介于”，然后设置“最大值”和“最小值”，如图2-70所示。
图2-70
③切换到“出错警告”选项卡，在“标题”文本框中输入警告标题，如图2-71所示。
图2-71
④在单击“确定”按钮即可。当单元格数据不是介于22～40之间整数时，即会弹出警告提示框，如图2-72所示。
图2-72
知识扩展
在“允许”下拉列表中还可以设置小数、时间、文本长度、自定义等类型，用户可根据需要选择相应选项进行设置。
练一练
只允许金额小于等于5000元的整数
如图2-73所示的表格中要求活动经费小于等于5000元，当输入大于5000元的金额时弹出错误提示。
图2-73
2.3.2 建立可选择输入的序列
关键点：把允许输入的数据建立为序列
操作要点：“数据”→“数据工具”→“数据验证”→“允许”
应用场景：为避免手动输入的麻烦，可将数据建立为序列，通过下拉列表直接选择所需数据
①选中B2:B13单元格区域，在“数据”选项卡的“数据工具”组中单击“数据验证”按钮，如图2-74所示，打开“数据验证”对话框。
图2-74
②单击“允许”设置框右侧下拉按钮，在下拉列表中选择“序列”。接着在“来源”文本框中输入“白板系列，财务用品，文具管理，书写工具，纸张制品”（注意输入数据间注意使用半角逗号间隔），如图2-75所示。
图2-75
知识扩展
如果序列中的选项过多，可以把数据来源输入到工作表中，然后单击“来源”文本框右侧的
按钮，回到工作表中去选择想作为序列的单元格区域。
③单击“确定”按钮，返回到工作表中，单击B2单元格右侧下拉按钮，在下拉菜单中显示出可选择的序列如图2-76所示，选择相应的产品类别即可。
图2-76
2.3.3 用公式建立验证条件
关键点：用公式建立更灵活的验证条件
操作要点：“数据”→“数据工具”→“数据验证”→“允许”
应用场景：限制数据输入的长度、避免输入重复数值、避免求和数据超出限定数值、限制输入数据的长度等情况均可用公式建立验证条件
1．禁止输入重复值
面对信息庞大的数据源表格，在录入数据时，难免出现重复输入数据的情况，这会给后期的数据整理及数据分析带来麻烦。因此对于不允许输入重复值的数据区域，可以事先设置禁止输入重复值。
①选中A2:A13单元格区域，在“数据”选项卡的“数据工具”组中单击“数据验证”按钮，如图2-77所示。
图2-77
②打开“数据验证”对话框，单击“允许”设置框右侧下拉按钮，在下拉列表中选择“自定义”，如图2-78所示。
图2-78
③接着在“公式”文本框中输入公式“=COUNTIF (A:A,A1)=1”，如图2-79所示。
图2-79
④在单击“确定”按钮，返回到工作表中。在A列中输入的数据不能出现重复，一旦出现重复，则会弹出如图2-80所示的提示框。
图2-80
公式分析
COUNTIF函数用于计算区域中满足指定条件的单元格个数。即依次判断所输入的数据在A列中出现的次数是否等于1，如果等于1允许输入，否则不允许输入。
2．禁止输入空格
对于需要后期处理的数据库表格，在输入数据时一般都要避免输入空格字符，因为这些无关字符可能会导致查找不到结果，计算时出错等情况发生。通过数据验证设置则可以实现禁止空格的输入。
①选中目标数据区域，在“数据”选项卡的“数据工具”组中单击“数据验证”按钮，如图2-81所示。
图2-81
②打开“数据验证”对话框，单击“允许”设置框右侧下拉按钮，在下拉列表中选择“自定义”，然后在“公式”文本框中输入公式“=ISERROR (FIND (" ",A2))”，如图2-82所示。
图2-82
③单击“确定”按钮，返回到工作表中，当在A列中输入姓名时，只要输入了空格就会弹出警示并阻止输入，如图2-83所示。
图2-83
练一练
只允许输入小于10的数值
设置“允许”条件为整数时，则只能输入满足条件的整数；设置“允许”条件为小数时，则只能输入满足条件的小数。如果想实现的效果是小于某个数值的任意值（小数或整数均可），如图2-84所示，要求输入的值小于10，此时则需要用公式来建立验证条件。
图2-84
2.3.4 显示输入提示
关键点：鼠标指向时显示输入提醒
操作要点：“数据”→“数据工具”→“数据验证”→“输入信息”
应用场景：如果有些单元格对可输入的数据有限制要求，可以为这块单元格区域添加输入提醒
①选中想要设置的单元格区域（可以一次性选中不连续的单元格区域），在“数据”选项卡的“数据工具”组中单击“数据验证”按钮，如图2-85所示，打开“数据验证”对话框。
图2-85
②单击“输入信息”选项卡，在“标题”和“输入信息”文本框中输入要提示的信息，如图2-86所示。
图2-86
③单击“确定”按钮，返回到工作表中，此时当鼠标指向设置了数据验证的单元格时，系统会显示所设置的提示信息，如图2-87所示。
图2-87
练一练
提示输入正确的日期格式
如图2-88所示，为“招聘开始时间”列设置提示信息。
图2-88
2.3.5 圈释无效数据
关键点：将无效的数据圈出来
操作要点：“数据”→“数据工具”→“数据验证”→“圈释无效数据”
应用场景：为了便于查看和分析结果，可以将无效数据圈出来
例如，下面表格中要求将小于70的成绩直接圈释出来。
①选中D2:D11单元格区域，在“数据”选项卡的“数据工具”组中单击“数据验证”按钮，如图2-89所示。
图2-89
②打开“数据验证”对话框，在“允许”下拉列表中选择“小数”，在“数据”下拉列表中选择“大于”，在“最小值”文本框中输入“70”，如图2-90所示。
图2-90
③单击“确定”按钮，返回到工作表中，再次单击“数据验证”下拉按钮，在下拉菜单中选择“圈释无效数据”命令，如图2-91所示，系统自动将单元格区域小于70的数据圈释出来，效果如图2-92所示。
图2-91
图2-92
专家提醒
●圈释无效数据前必须要为已存在的数据设置数据验证条件，然后才能将不满足条件的数据圈释出来。
●查看后，在“数据验证”下拉菜单中选择“清除验证标识圈”命令即可取消圈释无效数据。