3.1 使用数据验证功能让数据输入规范化
Excel为用户提供了灵活的数据输入方式，用户可以在工作表中输入任意内容。灵活输入的同时也会带来一些弊端，格式不规范的数据为以后的数据汇总和分析带来麻烦。使用“数据验证”功能可以设置数据输入的规则，只有符合规则的数据才能被输入单元格中，从而避免输入无效数据。在Excel 2013之前的版本中，“数据验证”功能的名称为“数据有效性”。
3.1.1 了解数据验证
使用“数据验证”功能可以根据预先设置好的验证规则，对用户输入的数据进行检查，并将符合规则的数据输入单元格中，而拒绝输入不符合规则的数据。选择要设置数据验证的一个或多个单元格，然后在功能区的“数据”选项卡中单击“数据验证”按钮，如图3-1所示。打开如图3-2所示的“数据验证”对话框，在“设置”“输入信息”“出错警告”和“输入法模式”4个选项卡中设置数据验证规则的相关选项，然后单击“确定”按钮，即可为选中的单元格设置数据验证规则。“数据验证”对话框中4个选项卡的功能如下：
· “设置”选项卡：在该选项卡中设置数据的验证条件，在“允许”下拉列表中选择一种验证条件，下方显示所选验证条件的相关选项。“允许”下拉列表中包含8种数据验证条件功能如表3-1所示。如果选中“忽略空值”复选框，则无论为单元格设置哪种验证条件，空单元格都是有效的，否则在空单元格中按Enter键将显示出错警告信息。
图3-1 单击“数据验证”按钮
图3-2 “数据验证”对话框
表3-1 8种数据验证条件的功能说明
· “输入信息”选项卡：在该选项卡中设置当选择包含数据验证规则的单元格时显示的提示信息，以帮助用户正确地输入数据。
· “出错警告”选项卡：在该选项卡中设置当输入不符合规则的数据时显示的出错警告信息，以提醒用户输入正确的数据。
· “输入法模式”选项卡：在该选项卡中设置当选择特定的单元格时自动切换到相应的输入法模式。
在“数据验证”对话框中的每个选项卡的左下角有一个“全部清除”按钮，单击该按钮将清除所有选项卡中的设置。
3.1.2 只允许用户从列表中选择选项来输入
使用“数据验证”功能中的“序列”数据验证条件，将为单元格提供包含指定选项的下拉列表，用户通过选择其中的选项输入数据，从而达到限定输入内容的目的。
如图3-3所示，需要根据A列中的商品名称，在B列中输入正确的商品类别（本例为“饮料”“果蔬”和“熟食”）。为了避免输入无效的商品类别，可以为B列设置数据验证，操作步骤如下：
图3-3 需要在B列中输入商品的类别
（1）选择要输入商品类别的单元格区域，本例为B2:B6，然后在功能区的“数据”选项卡中单击“数据验证”按钮。
（2）打开“数据验证”对话框，在“设置”选项卡中进行以下设置，如图3-4所示。
· 在“允许”下拉列表中选择“序列”。
· 在“来源”文本框中输入“饮料，果蔬，熟食”，文字之间的逗号需要在英文半角状态下输入。如果要在文本框中移动插入点的位置，需要按F2键进入编辑模式。
· 选中“提供下拉箭头”复选框。
提示：如果已将下拉列表包含的选项输入到单元格区域中，则可以单击“来源”文本框右侧的折叠按钮
，在工作表中选择该单元格区域，将其中的内容导入“来源”文本框。
设置完成后单击“确定”按钮。当选择B2:B6区域中的任意一个单元格时，将自动在单元格的右侧显示一个下拉按钮，单击该按钮将打开一个下拉列表，其中包含“饮料”“果蔬”和“熟食”3个选项，选择一个选项即可将其输入到单元格中，如图3-5所示。
图3-4 设置数据验证条件
图3-5 选择下拉列表中的选项以将其输入到单元格中
3.1.3 限制输入的数值和日期范围
在很多情况下，需要将输入的内容限制在一个有效的范围内，例如员工年龄、考试成绩、发货日期等。使用“整数”“小数”“日期”“时间”“文本长度”等验证条件可以针对不同类型的数据设置输入的限制范围。
如图3-6所示，需要在B列中输入商品的发货量，发货量限制在1～30。为了避免输入无效的数字，可以为B列设置数据验证，操作步骤如下：
图3-6 需要在B列中输入商品的发货量
（1）选择要输入发货量的单元格区域，本例为B2:B6，然后在功能区的“数据”选项卡中单击“数据验证”按钮。
（2）打开“数据验证”对话框，在“设置”选项卡中进行以下设置，如图3-7所示。
· 在“允许”下拉列表中选择“整数”。
· 在“数据”下拉列表中选择“介于”。
· 在“最小值”文本框中输入1。
· 在“最大值”文本框中输入30。
（3）切换到“输入信息”选项卡，进行以下设置，如图3-8所示。
· 选中“选定单元格时显示输入信息”复选框。
· 在“标题”文本框中输入“输入发货量”。
· 在“输入信息”文本框中输入“请输入1～30的数字”。
图3-7 设置数据验证条件
图3-8 设置提示信息
（4）切换到“出错警告”选项卡，进行以下设置，如图3-9所示。
· 选中“输入无效数据时显示出错警告”复选框。
· 在“样式”下拉列表中选择“停止”。
· 在“标题”文本框中输入“输入有误”。
· 在“错误信息”文本框中输入“输入的数字超出有效范围”。
（5）单击“确定”按钮，关闭“数据验证”对话框。
选择B2:B6区域中的任意一个单元格时，将显示如图3-10所示的提示信息。如果输入1～30的数字，该数字会被添加到单元格中。如果输入其他数字，则在按下Enter键时将显示预先定制好的出错警告信息，此时只能重新输入或取消输入，如图3-11所示。
图3-9 设置出错警告信息
图3-10 选择单元格时显示提示信息
图3-11 输入无效数字时显示的警告信息
3.1.4 禁止输入重复内容
在实际应用中，可能需要输入一些具有唯一性的内容，例如员工编号或商品编号。为了避免输入重复的内容，可以使用“自定义”数据验证条件，通过设置公式和函数判断输入的数据是否发生重复并加以限制。
如图3-12所示，需要在A列中输入商品的编号。为了避免输入重复的商品编号，可以为A列设置数据验证，操作步骤如下：
（1）选择要输入商品编号的单元格区域，本例为A2:A6，确保A2是活动单元格，然后在功能区的“数据”选项卡中单击“数据验证”按钮。
（2）打开“数据验证”对话框，在“设置”选项卡中进行以下设置，如图3-13所示。
图3-12 需要在A列中输入不重复的商品编号
图3-13 设置数据验证条件
· 在“允许”下拉列表中选择“自定义”。
· 在“公式”文本框中输入以下公式，其中的A2单元格需要使用相对引用。
=COUNTIF($A$2:$A$6,A2)=1
提示：关于公式、相对引用和COUNTIF函数的更多内容，请参考第4章。
（3）切换到“出错警告”选项卡，进行以下设置，然后单击“确定”按钮，如图3-14所示。
· 选中“输入无效数据时显示出错警告”复选框。
· 在“样式”下拉列表中选择“停止”。
· 在“标题”文本框中输入“编号有误”。
· 在“错误信息”文本框中输入“输入了重复的商品编号”。
在A2:A6区域中输入商品编号时，如果输入了重复的编号，则将显示出错警告信息，此时只能重新输入或取消输入，如图3-15所示。
图3-14 设置出错警告信息
图3-15 输入重复编号时显示出错警告信息
3.1.5 检查并圈释无效数据
如果在设置数据验证规则前，已经在单元格中输入了数据，则可以使用数据验证功能圈释不符合规则的数据，以帮助用户快速找到无效数据。圈释数据前，需要先为数据区域设置数据验证规则，然后在功能区的“数据”选项卡中单击“数据验证”按钮上的下拉按钮，在弹出的菜单中选择“圈释无效数据”命令，即可为选区中不符合验证规则的数据添加红色标识圈，如图3-16所示。
图3-16 圈释无效数据
清除红色标识圈的一种方法是修改数据以使其符合验证规则，红色标识圈会自动消失；另一种方法是在功能区的“数据”选项卡中单击“数据验证”按钮上的下拉按钮，然后在弹出的菜单中选择“清除验证标识圈”命令。
3.1.6 管理数据验证
如果要修改现有的数据验证规则，需要先选择包含数据验证规则的单元格，然后打开“数据验证”对话框并进行所需的修改。
如果为多个单元格设置了相同的数据验证规则，则可以先修改其中任意一个单元格的数据验证规则，然后在关闭“数据验证”对话框前，在“设置”选项卡中选中“对有同样设置的所有其他单元格应用这些更改”复选框，即可将当前设置结果应用到其他包含相同数据验证规则的单元格中，如图3-17所示。
当复制包含数据验证规则的单元格时，将同时复制该单元格中包含的内容和数据验证规则。如果只想复制单元格中的数据验证规则，则可以在执行复制命令后，右击要粘贴的单元格，然后在弹出的菜单中选择“选择性粘贴”命令，在打开的对话框中选中“验证”单选按钮，最后单击“确定”按钮，如图3-18所示。
图3-17 批量修改数据验证规则的方法
图3-18 只粘贴数据验证规则
注意：如果复制一个不包含数据验证规则的单元格，并将其粘贴到包含数据验证规则的单元格中，则将覆盖目标单元格中的数据验证规则。
如果要删除单元格中的数据验证规则，可以打开“数据验证”对话框，然后在任意一个选项卡中单击“全部清除”按钮。当工作表中包含不止一种数据验证规则时，删除所有数据验证规则的操作步骤如下：
（1）单击位于行号和列标交叉位置处的全选按钮（一个三角形标记），选中工作表中的所有单元格，如图3-19所示。
（2）在功能区的“数据”选项卡中单击“数据验证”按钮，将显示如图3-20所示的提示信息，单击“确定”按钮。
图3-19 位于左上角的全选按钮
图3-20 删除所有数据验证规则时的提示信息
（3）打开“数据验证”对话框，不做任何设置，直接单击“确定”按钮，即可删除当前工作表中包含的所有数据验证规则。