1.7　使用数据验证功能限制数据的输入

Excel为用户提供了非常灵活的数据输入方式，用户可以在工作表中随意输入任何内容。这样带来的问题也很明显，输入的很多不规范数据为后期的数据汇总和分析带来麻烦。利用Excel中的数据验证功能，用户可以设置数据输入规则，只有符合规则的数据才会被输入到单元格中，从而起到规范化数据输入的目的。从Excel 2013开始，将原来的“数据有效性”改名为“数据验证”。

1.7.1　了解数据验证

数据验证功能是根据用户指定的验证规则，检查用户输入的数据，只有符合规则的数据才会被添加到单元格中，并禁止在单元格中输入不符合规则的数据。数据验证功能是基于单元格的，因此，可以针对一个或多个单元格进行设置。复制单元格时，默认也会复制其中包含的数据验证规则。

选择要设置数据验证规则的单元格，然后在功能区“数据”|“数据工具”组中单击“数据验证”按钮，打开“数据验证”对话框，在“设置”选项卡中打开“允许”下拉列表，从中选择一种数据验证方式，如图1-44所示。

下面对这几种验证方式进行简要说明。

• 　任何值：在单元格中输入的内容不受限制。
• 　整数：只能在单元格中输入特定范围内的整数。

• 　小数：只能在单元格中输入特定范围内的小数。
• 　序列：为单元格提供一个下拉列表，只能从下拉列表中选择一项输入到单元格中。
• 　日期：只能在单元格中输入特定范围内的日期。
• 　时间：只能在单元格中输入特定范围内的时间。
• 　文本长度：只能在单元格中输入特定长度的字符。
• 　自定义：使用公式和函数设置数据验证规则。如果公式返回逻辑值TRUE或非0数字，则表示输入的数据符合验证规则；如果公式返回逻辑值FALSE或0，则表示输入的数据不符合验证规则。

除了“设置”选项卡外，“数据验证”对话框还包含“输入信息”“出错警告”和“输入法模式”3个选项卡，经常设置的是“输入信息”和“出错警告”选项卡。“输入信息”选项卡用于设置当选择包含数据验证规则的单元格时，要向用户显示的提示信息；“出错警告”选项卡用于设置当输入的数据不符合数据验证规则时，向用户发出的警告信息，并可选择是否禁止当前的输入。

在“数据验证”对话框中设置好所需的选项，单击“确定”按钮，即可为所选单元格创建数据验证规则。单击任意一个选项卡左下角的“全部清除”按钮，将清除用户在所有选项卡中进行的设置。

1.7.2　在单元格中提供下拉列表选项

数据验证功能最常见的一个应用是为单元格提供下拉列表，用户可以通过选择下拉列表中的选项来向单元格输入内容，如果用户在单元格中输入列表之外的内容，Excel会发出警告信息并禁止输入，这样就可以只允许用户向单元格输入指定范围中的内容。

要实现此功能，需要在“数据验证”对话框的“设置”选项卡的“允许”下拉列表中选择“序列”，然后在“来源”文本框中输入列表中的每一项，各项之间使用英文半角逗号分隔，如图1-45所示。如果要修改“来源”文本框中的内容，则需要按F2键进入编辑状态，然后才能随意移动光标，与在单元格中输入和编辑数据的方法类似。

为了让用户可以正常打开下拉列表，需要确保已选中“提供下拉箭头”复选框，这样就会在设置了数据验证规则的单元格中显示一个下拉按钮，单击该按钮将打开一个下拉列表，如图1-46所示。

提示：如果单元格区域中已经包含下拉列表中的各项，则可以单击“来源”文本框右侧的折叠按钮，在工作表中选择该区域，即可将区域中的内容直接导入到“来源”文本框中。

如果要禁止用户在单元格中输入列表之外的内容，则需要在“数据验证”对话框的“出错警告”选项卡中进行设置。首先选中“输入无效数据时显示出错警告”复选框，然后在“样式”下拉列表中选择“停止”。如果希望在输入无效数据时，向用户发出自定义的提示信息，则可以设置“标题”和“错误信息”两项，如图1-47所示。如果在设置了数据验证规则的单元格中输入了无效的数据，在按下Enter键时，会显示如图1-48所示的警告信息。

提示：如果要求用户必须在设置了数据验证规则的单元格中输入一个有效的值，则需要在“数据验证”对话框的“设置”选项卡中取消选中“忽略空值”复选框。

1.7.3　创建基于公式的数据验证规则

如果想要发挥数据验证的强大功能，则需要在数据验证规则中使用公式。1.7.1节曾经介绍过，在数据验证规则中使用的公式需要返回一个逻辑值TRUE或FALSE，逻辑值TRUE表示输入的数据符合验证规则，Excel允许将其输入到单元格中，逻辑值FALSE表示输入的数据不符合验证规则，Excel禁止将其输入到单元格中。如果公式返回的是一个数字，那么所有非0数字等价于TRUE，0等价于FALSE。

要创建使用公式的数据验证规则，需要在“数据验证”对话框的“设置”选项卡的“允许”下拉列表中选择“自定义”，然后在“公式”文本框中输入公式。

使用公式的数据验证规则的一个常见应用是在输入编号类的数据时，检查并防止输入重复的编号。首先选择要输入编号的单元格区域，如A2:A10，然后打开“数据验证”对话框，在“设置”选项卡中进行以下设置，如图1-49所示。

• 　在“允许”下拉列表中选择“自定义”。
• 　在“公式”文本框中输入下面的公式，判断A2:A10中的每一个单元格在该区域中的计数是否是1，如果是则说明没有重复，否则说明出现重复。

单击“确定”按钮，关闭“数据验证”对话框。如果在A2:A10单元格区域中输入重复的编号，则会显示警告信息并禁止当前重复编号的输入，如图1-50所示。这里显示的是默认的警告信息，用户可以使用前面介绍的方法自定义警告信息。

提示：COUNTIF函数将在第3章进行详细介绍。

1.7.4　管理数据验证

如果要修改现有的数据验证规则，则需要先选择包含数据验证规则的单元格，然后打开“数据验证”对话框，再进行所需的修改。

如果为多个单元格设置了相同的数据验证规则，则可以先修改任意一个单元格的数据验证规则，然后在关闭“数据验证”对话框之前，在“设置”选项卡中选中“对有同样设置的所有其他单元格应用这些更改”复选框，即可将当前设置结果应用到其他包含相同数据验证规则的单元格中。

当复制包含数据验证规则的单元格时，会同时复制该单元格包含的内容和数据验证规则。如果只想复制单元格中的数据验证规则，则可以在单击“复制”命令后，右击要进行粘贴的位置，然后在弹出的快捷菜单中单击“选择性粘贴”命令，在打开的对话框中选择“数据验证”选项，最后单击“确定”按钮。

注意：如果复制一个不包含数据验证规则的单元格，并将其粘贴到包含数据验证规则的单元格中，则会覆盖目标单元格中的数据验证规则。

如果要删除单元格中的数据验证规则，则可以打开“数据验证”对话框，然后在任意一个选项卡中单击“全部清除”按钮。当工作表中包含不止一种数据验证规则时，删除所有这些数据验证规则的操作步骤如下：

（1）单击单元格区域左上角的全选按钮，选中工作表中的所有单元格。

（2）在功能区“数据”|“数据验证”组中单击“数据验证”按钮，将显示如图1-51所示的提示信息，单击“确定”按钮。

（3）打开“数据验证”对话框，不作任何设置，直接单击“确定”按钮，即可删除当前工作表中包含的所有数据验证规则。