excel数据有效性概述与示例

excel数据有效性概述与示例

 

什么是数据有效性验证？

Microsoft Excel 数据有效性验证使您可以定义要在单元格中输入的数据类型。例如，您仅可以输入从 A 到 F 的字母。您可以设置数据有效性验证，以避免用户输入无效的数据，或者允许输入无效数据，但在用户结束输入后进行检查。您还可以提供信息，以定义您期望在单元格中输入的内容，以及帮助用户改正错误的指令。
如果输入的数据不符合您的要求，Excel 将显示一条消息，其中包含您提供的指令。

当您所设计的表单或工作表要被其他人用来输入数据（例如，预算表单或支出报表）时，数据有效性验证尤为有用。
本文介绍了如何设置数据有效性验证，包括可以进行验证的数据类型和可以显示的消息。还提供了一个工作簿，您可以下载该工作簿，以获取您可以在自己的工作表上进行修改和使用的有效性验证的示例。

可以验证的数据类型

Excel 使您可以为单元格指定以下类型的有效数据：
数值   指定单元格中的条目必须是整数或小数。您可以设置最小值或最大值，将某个数值或范围排除在外，或者使用公式计算数值是否有效。
日期和时间   设置最小值或最大值，将某些日期或时间排除在外，或者使用公式计算日期或时间是否有效。
长度   限制单元格中可以输入的字符个数，或者要求至少输入的字符个数。
值列表   为单元格创建一个选项列表（例如小、中、大），只允许在单元格中输入这些值。用户单击单元格时，将显示一个下拉箭头，从而使用户可以轻松地在列表中进行选择。

可以显示的消息类型

对于所验证的每个单元格，都可以显示两类不同的消息：一类是用户输入数据之前显示的消息，另一类是用户尝试输入不符合要求的数据时显示的消息。如果用户已打开 Office 助手，则助手将显示这些消息。
输入消息   一旦用户单击已经过验证的单元格，便会显示此类消息。您可以通过输入消息来提供有关要在单元格中输入的数据类型的指令。
错误消息   仅当用户输入无效数据并按下 Enter 时，才会显示此类消息。您可以从以下三类错误消息中进行选择：

信息消息   此类消息不阻止输入无效数据。除所提供的文本外，它还包含一个消息图标、一个“确定”按钮（用于在单元格中输入无效数据）和一个“取消”按钮（用于恢复单元格中的前一个值）。 警告消息   此类消息不阻止输入无效数据。它包含您提供的文本、警告图标和三个按钮：“是”用于在单元格中输入无效数据；“否”用于返回单元格进一步进行编辑；“取消”用于恢复单元格的前一个值。 停止消息   此类消息不允许输入无效数据。它包含您提供的文本、停止图标和两个按钮：“重试”用于返回单元格进一步进行编辑）；“取消”用于恢复单元格的前一个值。请注意，不能将此类消息作为一种安全措施：虽然用户无法通过键入和按 Enter 输入无效数据，但是他们可以通过复制和粘贴或者在单元格中填写数据的方式来通过验证。

如果未指定任何信息，则 Excel 会标记用户输入数据是否有效，以便您以后进行检查，但用户输入的数据无效时，它不会通知用户。

设置数据有效性验证

确定要在工作簿中使用的有效性验证后，您可以使用“数据”菜单中的“有效性”命令对其进行设置。以下是该过程的一般概述：

设置工作表   通过在工作表中输入数据和公式开始。如果您要使用有效选项列表，请输入列表并为其命名。 定义单元格的设置   从要验证的第一个单元格开始，使用“数据有效性”对话框指定所需的有效性验证类型、输入信息（如果需要）和错误信息（如果需要）。 设置其他单元格的有效性验证   将有效性验证设置从第一个单元格复制到其他单元格，然后对设置进行修改，这样做通常可以节省时间。 测试有效性验证规则   尝试在单元格中输入有效和无效数据，以确保设置正常工作并且消息如期显示。使用“有效性”命令对这些设置进行更改。在一个单元格中更改有效性验证后，您可以将这些更改自动应用至其他具有相同设置的所有单元格中。 设置有效选项列表   如果您使用了有效选项列表并且不希望用户查看和更改列表，您可以将列表置于另一个工作表中，设置有效性验证，隐藏包含该列表的工作表，然后使用密码保护工作簿。不知道工作簿保护密码的用户将无法取消包含列表的工作表的隐藏。 在需要时应用保护   如果要保护工作表或工作簿，请在设置完有效性验证后进行。保护工作表之前，请确保“解除锁定”任何已经过验证的单元格，否则，用户将无法在单元格中输入内容。 在需要时共享工作簿   如果您要共享工作簿，请在完成有效性验证和保护的设置后执行。共享工作簿后，除非您停止共享，否则将不能更改有效性验证的设置，但是在共享工作簿时，Excel 将继续验证指定的单元格。 检查无效数据的结果   用户在工作表中输入数据后，您可以按照本文后面的介绍检查无效数据。 在验证的单元格中输入数据

以下是用户输入数据的过程。您可以使用输入消息和错误消息，提供一些用户需要了解的指令，使用户了解您如何设置工作表以确保数据正确。要查看此过程，请下载本文附带的示例工作簿，并查看“消息”工作表中的示例。
查看输入消息   用户单击经过验证的单元格或使用箭头键移至单元格时，您输入的消息将以“助手”气球或单独的消息框方式显示。如果您为单元格提供了下拉列表，则单元格右侧将显示下拉箭头。
键入数据   当用户键入数据或单击下拉箭头从列表中选择值时，输入的消息将停留在屏幕上（下拉列表可能会覆盖部分消息）。
输入有效数据   如果用户输入有效数据并按下 Enter，则数据将被输入到单元格并且不会出现特殊情况。
输入无效数据   如果用户输入的数据不符合条件，并且您为无效数据指定了错误消息，则错误消息将显示在“助手”气球或单独的消息窗口中。用户可以阅读消息，并决定如何处理。

对于信息消息，用户可以单击“确定”以输入无效数据，或者单击“取消”重新开始。 对于警告消息，用户可以单击“是”以输入无效数据，单击“否”继续编辑单元格，或者单击“取消”重新开始。 对于停止消息，用户将无法输入无效数据，但可以单击“重试”编辑单元格，或者单击“取消”重新开始。

如果未提供任何消息，则对于用户来说，在经过验证的单元格中输入数据与一般的 Excel 数据输入相同。然而，Excel 会标记包含无效条目的单元格，以便于您查找这些内容。

检查工作表中的无效内容

收回用户可能在其中输入了无效数据的工作表后，您可以使 Excel 将不符合条件的所有数据画上红色圆圈，以便于查找工作表中的错误。要实现此目的，请使用“公式审核”工具栏上的“圈释无效数据”按钮和“清除无效数据标识圈”按钮。

因为单元格中的值不符合标准，所以用圆圈标记。
更正单元格中的数据后，圆圈将消失。要查看此功能，请下载本文附带的示例工作簿，并查看“无效数据”工作表。