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

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

1.4.1　了解数据验证

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

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

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

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

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

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

1.4.2　为用户提供限定范围内的输入项

数据验证功能最常见的一个应用是为单元格提供下拉列表，用户可从中选择一项，并将其输入到单元格中，如果用户在单元格中输入列表之外的内容，Excel会禁止输入并发出警告信息。

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

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

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

提示：如果选中“忽略空值”复选框，则允许将设置了数据验证的单元格留空，即可以不在其中输入任何内容，Excel也不会发出警告信息。

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

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

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

在输入员工信息时，每个员工的编号都是唯一的，因此，需要避免误输入重复的员工编号，此时就可以通过数据验证功能来实现此目的，操作步骤如下：

（1）选择要输入员工编号的单元格区域，如A2:A10。

（2）打开“数据验证”对话框，在“设置”选项卡中进行以下设置，如图1-58所示。

• 　在“允许”下拉列表中选择“自定义”。
• 　在“公式”文本框中输入下面的公式：

提示：COUNTIF函数用于统计符合条件的单元格的数量，第3章将会介绍该函数的用法。

（3）切换到“出错警告”选项卡，然后进行以下设置：

• 　选中“输入无效数据时显示出错警告”复选框。
• 　在“样式”下拉列表中选择“停止”。
• 　在“标题”和“错误信息”两个文本框中分别输入“编号错误”和“不能输入重复的编号”。

（4）设置完成后，单击“确定”按钮，关闭“数据验证”对话框。

如果在设置了数据验证规则的区域中输入重复的编号，则会显示如图1-59所示的提示信息，并禁止将编号输入到单元格中。

1.4.4　管理数据验证

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

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

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

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

如果要删除单元格中的数据验证规则，可以打开“数据验证”对话框，然后在任意一个选项卡中单击“全部清除”按钮。

当工作表中包含不止一种数据验证规则时，删除所有这些数据验证规则的操作步骤如下：

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

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

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