下载掌阅APP，畅读海量书库
立即打开
为了避免用户随意输入无效内容而影响数据的正常分析，可以使用“数据验证”功能对可以输入到单元格中的内容加以控制。“数据验证”在Excel 2013之前的版本中称为“数据有效性”。
使用“数据验证”功能可以根据预先设定的数据验证规则，对输入到单元格中的数据进行检查，只有符合规则的数据才会被添加到单元格中。如需为单元格添加数据验证规则，需要选择一个或多个单元格，然后在功能区的“数据”选项卡中单击“数据验证”按钮，如图3-1所示。
图3-1 单击“数据验证”按钮
打开如图3-2所示的“数据验证”对话框，在“设置”“输入信息”“出错警告”和“输入法模式”4个选项卡中设置数据验证规则的相关选项，完成后单击“确定”按钮，即可为选中的单元格设置数据验证规则。“数据验证”对话框中的4个选项卡的功能如下。
●“设置”选项卡：设置验证条件，在“允许”下拉列表中选择一种验证条件，下方会显示所选验证条件的相关选项。“允许”下拉列表中包含的8种验证条件的功能如表3-1所示。如果勾选“忽略空值”复选框，则无论为单元格设置哪种验证条件，空单元格都是有效的，否则在空单元格中按Enter键会显示出错警告信息。
●“输入信息”选项卡：当选择包含数据验证规则的单元格时，设置要向用户显示的提示信息，以便让用户了解应该输入什么类型的数据。
●“出错警告”选项卡：当输入不符合数据验证规则的数据时，设置要向用户显示的出错警告信息，以便让用户了解出错原因并及时更正错误。
●“输入法模式”选项卡：当选择特定的单元格时，设置要自动切换到哪种输入法。
图3-2 “数据验证”对话框
表3-1 8种数据验证条件的功能
在“数据验证”对话框中的每个选项卡的左下角都有一个“全部清除”按钮，单击该按钮将清除所有选项卡中的设置。下面将介绍几种常用的数据验证规则的设置方法。
使用“数据验证”功能中的“序列”验证条件，可以为单元格提供一个下拉列表，只能将列表中的任意一项添加到单元格中，而禁止在单元格中输入列表项之外的内容。
示例文件/第3章/只能从下拉列表中选择数据.xlsx
如图3-3所示，根据A列中的商品名称，在B列中输入正确的商品类别（“饮料”“果蔬”和“熟食”）。为了避免输入无效的商品类别，需要为B列设置数据验证规则。
操作步骤如下：
（1）选择B2:B6单元格区域，然后在功能区的“数据”选项卡中单击“数据验证”按钮。
（2）打开“数据验证”对话框，在“设置”选项卡中设置以下几项，如图3-4所示。
图3-3 在B列中输入商品类别
图3-4 设置验证条件
●在“允许”下拉列表中选择“序列”选项。
●在“来源”文本框中输入“饮料，果蔬，熟食”，文字之间的逗号需要在英文半角状态下输入。如需在文本框中移动插入点的位置，需要按F2键进入“编辑”模式。
●勾选“提供下拉箭头”复选框，取消勾选“忽略空值”复选框。
技巧：
如果已将下拉列表包含的选项输入到单元格区域中，则可以单击“来源”文本框右侧的
按钮，然后在工作表中选择该单元格区域，即可将其中的内容添加到“来源”文本框中。
（3）切换到“输入信息”选项卡，设置以下几项，如图3-5所示。
●勾选“选定单元格时显示输入信息”复选框。
●在“标题”文本框中输入“选择商品类别”。
●在“输入信息”文本框中输入“从下拉列表中选择商品类别”。
（4）切换到“出错警告”选项卡，设置以下几项，如图3-6所示。
图3-5 设置提示信息
图3-6 设置警告信息
●勾选“输入无效数据时显示出错警告”复选框。
●在“样式”下拉列表中选择“停止”选项。
●在“标题”文本框中输入“类别错误”。
●在“错误信息”文本框中输入“类别不正确，请从下拉列表中选择一种类别”。
（5）单击“确定”按钮，关闭“数据验证”对话框。
当选择B2:B6区域中的任意一个单元格时，将自动在单元格中显示一个下拉按钮，并显示提示信息，如图3-7所示。单击该按钮，将打开一个下拉列表，其中包含“饮料”“果蔬”和“熟食”三项，选择其中之一，即可将选择的选项输入到单元格中，如图3-8所示。
图3-7 在单元格中显示下拉按钮和提示信息
图3-8 从下拉列表中选择指定的选项
如果在B2:B6单元格区域中输入其他内容，则在按Enter键后，会显示如图3-9所示的警告信息。此时有两种处理方式：
●单击“重试”按钮，然后重新输入数据，或者直接从下拉列表中选择一个选项。
●单击“取消”按钮，放弃本次输入，清空当前单元格中的所有内容。
图3-9 输入不符合规则的数据时显示警告信息
提示： 由于为不同数据验证规则设置提示信息和警告信息的方法都相同，只是信息的内容不同，为了节省篇幅，后续几个示例将省略设置提示信息和警告信息的步骤。
将数据的验证条件设置为“整数”“小数”“日期”“时间”或“文本长度”等时，可以将输入的数据限制在一个指定的范围内。
示例文件/第3章/只能输入指定范围内的数值和日期.xlsx
如图3-10所示，在B列中输入的商品发货量需要限制在1～30。为了避免输入无效的数字，需要为B列设置数据验证规则。
图3-10 在B列中输入商品的发货量
操作步骤如下：
（1）选择B2:B6单元格区域，然后在功能区的“数据”选项卡中单击“数据验证”按钮。
（2）打开“数据验证”对话框，在“设置”选项卡中设置以下几项，如图3-11所示。
●在“允许”下拉列表中选择“整数”选项。
●在“数据”下拉列表中选择“介于”选项。
●在“最小值”文本框中输入“1”。
●在“最大值”文本框中输入“30”。
●勾选“忽略空值”复选框。
图3-11 设置验证条件
（3）使用与3.1.2小节类似的方法，在“输入信息”和“出错警告”两个选项卡中设置提示信息和警告信息。完成后单击“确定”按钮，关闭“数据验证”对话框。
如需实现灵活的数据验证方式，可以使用表3-1中的“自定义”验证条件。此时需要使用公式来检测输入的数据是否符合规则，如果公式的结果是逻辑值TRUE或非0数字，则表示符合规则；如果公式的结果是逻辑值FALSE或0，则表示不符合规则。
示例文件/第3章/禁止输入重复内容.xlsx
如图3-12所示，在A列中输入商品的编号，为了避免输入重复的商品编号，需要为A列设置数据验证规则。
图3-12 在A列中输入不重复的商品编号
操作步骤如下：
（1）选择A2:A6单元格区域，使A2成为活动单元格，然后在功能区的“数据”选项卡中单击“数据验证”按钮。
（2）打开“数据验证”对话框，在“设置”选项卡中设置以下几项，如图3-13所示。
●在“允许”下拉列表中选择“自定义”选项。
●在“公式”文本框中输入以下公式，其中的A2单元格需要使用相对引用。该公式统计A2单元格中的值在A2:A6单元格区域中是否唯一，如果唯一，则说明没有出现重复，此时就可以将数据添加到单元格中。
=COUNTIF($A$2:$A$6,A2)=1
图3-13 设置数据验证条件
提示： 将A2单元格设置为相对引用，是因为上述公式会逐一对A2:A6单元格区域的每一个单元格中的数据进行检测。为了从A2单元格移动到A3单元格时，公式中的A2能够自动变成A3，所以公式中的A2需要使用相对引用。以便它可以根据公式移动的相对位置而自动调整行号。有关公式、相对引用和COUNTIF函数的详细内容，请参考第5章。
在A2:A6区域中输入商品编号时，如果输入了重复的编号，则会显示出错警告信息，如图3-14所示。
图3-14 输入重复编号时显示出错警告信息
如果在为单元格设置数据验证规则之前，已经在单元格中输入了数据，则可以使用数据验证功能将不符合规则的数据标记出来。标记数据前，需要先为数据区域设置数据验证规则，然后在功能区的“数据”选项卡中单击“数据验证”按钮的下拉按钮，在弹出的快捷菜单中选择“圈释无效数据”命令，如图3-15所示，即可将不符合规则的数据标记出来，如图3-16所示。
图3-15 选择“圈释无效数据”命令
图3-16 标记无效数据
提示： 标记无效数据时，不必非要选中要标记的数据区域，无论当前选中哪个单元格或单元格区域，Excel都会对设置了数据验证规则的数据进行检查，并标记不符合规则的数据。
如需清除标记，可以在功能区的“数据”选项卡中单击“数据验证”按钮的下拉按钮，然后在弹出的快捷菜单中选择“清除验证标识圈”命令。
如需修改现有的数据验证规则，需要先选择包含数据验证规则的单元格，然后在“数据验证”对话框中进行修改。
如果为多个单元格设置了相同的数据验证规则，则可以先修改其中一个单元格的数据验证规则，然后在关闭“数据验证”对话框之前，在该对话框的“设置”选项卡中勾选“对有同样设置的所有其他单元格应用这些更改”复选框，即可将对当前单元格的数据验证规则的修改结果自动更新到其他包含相同数据验证规则的单元格，如图3-17所示。
当复制包含数据验证规则的单元格时，将同时复制该单元格中的内容和数据验证规则。如果只想复制单元格中的数据验证规则，则可以使用“选择性粘贴”功能，为此需要打开“选择性粘贴”对话框，然后选择其中的“验证”选项，如图3-18所示。
图3-17 快速修改数据验证规则的方法
图3-18 只粘贴数据验证规则
注意： 如果复制一个不包含数据验证规则的单元格，并将其粘贴到包含数据验证规则的单元格，则会删除该单元格中的数据验证规则。
如需删除单元格中的数据验证规则，需要打开“数据验证”对话框，然后单击“全部清除”按钮。如果一个工作表中存在多个数据验证规则，则可以使用下面的方法删除所有的数据验证规则，操作步骤如下：
（1）单击工作表左上角（字母A和数字1之间）的三角标记，将会选中工作表中的所有单元格，如图3-19所示。
（2）在功能区的“数据”选项卡中单击“数据验证”按钮，将显示如图3-20所示的提示信息，单击“确定”按钮。
图3-19 位于左上角的三角标记
图3-20 删除所有数据验证规则时的提示信息
（3）打开“数据验证”对话框，不做任何设置，直接单击“确定”按钮，即可删除当前工作表中所有的数据验证规则。