Excel 数据验证

MS Excel是最受欢迎的电子表格软件之一，具有广泛的功能、公式和函数。数据验证是MS Excel中的一个基本功能之一。当为用户或客户创建Excel表时，我们经常需要根据不同的条件限制输入，以确保所有的输入或数据都是正确和一致的。数据验证是一种解决方案，可以根据指定的规则控制用户对特定单元格或范围的输入。

在本文章中，我们讨论Excel中的数据验证功能以及将其插入/ 应用于Excel表格的方法。本文还通过图片解释了相关实例，以帮助我们清楚地理解数据验证的概念。

数据验证是什么

数据验证是一项重要的Excel功能，可以帮助控制或限制用户在选定单元格中输入/录入的内容。它使用户能够设置所需的验证规则，以控制他们可以输入到Excel表格中相应单元格的数据类型。例如，我们可以限制用户输入1到10之间的值，输入少于30个字符的名称或密码，输入或从预定义的可接受值列表中选择条目，等等。

通过数据验证，我们可以设置以下一些重要任务（限制/验证）：

• 只允许用户输入 数字 或 文本 条目
• 允许输入小于、大于或介于指定 范围 的数字
• 允许输入特定 长度 的数据
• 限制在 下拉列表 中预定义的值
• 限制日期和时间的输入在 范围 内或 范围 外
• 基于 另一个单元格 验证特定的输入
• 在用户选择单元格时，显示 输入信息 ，告知用户相应单元格接受的内容
• 当用户输入错误数据时显示 警告或错误信息
• 在验证的单元格中定位 不正确或错误的条目

注意：需要注意的是，数据验证功能并不是一种完全可靠的控制输入的方式，它很容易被攻破。如果我们从没有验证规则的单元格复制数据，然后将这些单元格粘贴到带有数据验证的单元格中，验证将被破坏。具体来说，验证规则将根据复制的单元格从相应单元格中删除或更改。

Excel中的数据验证是如何工作的

在Excel表格中将数据验证应用于任何单元格或单元格范围，根据验证规则限制用户在相应的单元格中输入任何不希望的内容。例如，如果我们设置验证只接受数字或数值，其他用户或我们将无法输入除数字外的任何值。

数据验证可配置在用户选择相应单元格时显示输入消息，告知用户所允许的内容，如下所示：

一旦我们尝试在受限制的单元格中输入其他类型的数据，Excel将立即显示错误消息，甚至可以显示相应单元格可以接受的数据类型。错误消息可以有不同的样式，并且在设置Excel工作表的验证规则时可以自定义或手动创建。

数据验证控件

数据验证功能或其控件可以在“数据”选项卡下的功能区中找到。默认情况下，它位于“数据工具”类别下。

当我们从功能区点击“数据验证”图标后，它立即启动了一个 数据验证对话框 。

除了功能区上的“数据验证”快捷方式，我们还可以使用键盘快捷键 Alt + D + L （不带引号），它会立即启动数据验证对话框。

使用数据验证对话框定义验证规则

数据验证对话框包含三个重要选项/标签： 设置，输入信息 和 错误提示 。

让我们详细了解每个选项卡：

设置选项卡

设置选项卡提供了设定验证条件的选项。该选项卡帮助我们选择所期望允许在所选单元格中的内置选项中的验证规则。此外，我们还可以使用自定义公式设置自定义规则来验证用户输入。设置选项卡包含了Excel中的所有 数据验证选项 。

输入消息选项卡

输入消息选项卡有一个文本框，可以在选中相应单元格时显示输入的消息。输入消息是数据验证的可选功能。如果我们没有定义任何消息作为输入消息，Excel在用户选择具有数据验证的相应单元格时不会显示任何消息。它不会影响数据验证的工作，也无法控制用户在单元格中输入什么。然而，它可以帮助通知用户允许或期望的数据值。

错误警报标签

错误警报标签提供给我们控制验证强制执行的选项。我们可以设置条件，然后使用任何所需的错误样式来接受或拒绝用户输入。此外，我们还可以向用户显示一条消息，告知错误是什么或必须输入相应单元格的值。

目前在MS Excel中有三种错误样式，如下所示：

• Stop ：当错误样式设置为“Stop”时，Excel会阻止用户在相应单元格中输入无效数据。如果用户输入无效数据，会触发一个带有指定消息的弹出窗口，并拒绝输入。停止警报窗口显示两个选项： 重试 （编辑无效数据）和 取消 （删除无效数据）。尽管停止警报窗口显示了重试选项，但用户必须输入正确的值，以便在重试时通过验证。
  

• Warning ：当错误样式设置为“Warning”时，Excel会警告用户输入的值无效。它还显示不同的图标和指定的消息。然而，它不会阻止用户输入无效值；用户可以忽略警告消息并注册不通过验证的值。警告警报窗口显示三个选项： 是 （接受无效数据）、 否 （编辑无效数据）和 取消 （删除无效数据）。
  

• Information ：当错误样式设置为“Information”时，Excel会通知用户输入的数据无效。它显示不同的图标和指定的消息。与警告错误样式一样，信息错误样式也不能防止无效数据。用户可以忽略信息消息并在不通过验证的情况下注册无效数据。信息警报窗口显示两个选项： 确定 （接受无效数据）和 取消 （删除无效数据）。
  

数据验证选项

在设置选项卡中创建数据验证规则时，我们有八个选项来验证用户输入，它们如下：

• 任何值： 它移除了限制，允许在选择的单元格中输入任何值，不进行任何验证。但是，当选择对应的单元格时，在之前用输入消息进行了数据验证的单元格中会显示该消息。因此，在选择“任何值”选项时，我们必须编辑/移除输入消息。
• 整数： 它限制用户只能输入整数。一旦选择“整数”选项，我们会获得与限制用户输入相关的更多选项。其他选项通常包括小于、大于、等于、介于等。例如，我们可以限制用户在限制单元格中只能输入1到100之间的整数。
• 小数： 它几乎与“整数”选项相同，但它允许用户以小数形式输入值。例如，如果我们限制用户在0到5之间输入小数值，相应的单元格可以接受2.3、0.5、0.9、4.1等值。
• 列表： 它是一种典型的验证类型。它限制用户从预定义列表中选择值。具体而言，预定义的值以下拉菜单的形式显示给用户。我们可以直接在设置选项卡中定义值，或者将它们作为工作表上的一个范围提供。例如，我们可以限制用户从包含Male、Female和Others等值的列表中选择性别。
• 日期： 它限制用户以日期形式输入值。然而，我们可以设置一些验证规则，允许之前的日期、未来的日期、特定范围内的日期等。例如，我们可以限制单元格只接受未来日期作为约会行/列的输入。
• 时间： 它几乎与“日期”选项相同，但它限制用户输入时间。我们可以设置验证规则，允许之前、之后的特定时间，或在特定时间范围内。例如，我们可以限制单元格只接受8:00 AM到7:00 PM之间的时间。
• 文本长度： 它限制用户输入特定长度的值。这通常意味着输入根据字符或数字的数量进行验证。例如，如果我们限制用户在指定单元格中输入长度为4的值，那么该单元格只能接受具有四个字符或数字的值，如JTP1、0101、ABCD等。
• 自定义： 它是数据验证中的高级选项。这个选项可以用于基于自定义公式设置验证规则。具体而言，我们可以输入自定义公式来验证用户输入。使用公式极大地扩展了数据验证规则的可能性。例如，我们可以使用自定义公式来确保输入的值为大写或小写，检查值是否包括字符“abc”，只允许工作日等。

除了验证选项外，设置选项卡还显示两个复选框：

• 忽略空白： 如果选择了此选项，它指示Excel不验证空的单元格。从技术角度来说，这个选项主要影响“标记无效数据”命令。当选择为标记时，即使这些单元格未能通过验证，没有值的单元格也不会被标记。
• 将这些更改应用于所有具有相同设置的其他单元格： 如果选择了此选项，当被编辑的单元格/单元格的原始验证与其它单元格匹配时，Excel会将应用的验证更新到所有其它单元格。

如何在Excel中添加数据验证

在Excel工作表中添加数据验证，我们必须执行以下步骤：

步骤1：启动数据验证对话框

首先，我们必须选择要应用验证的所有单元格或范围。接下来，我们需要导航到 Data选项卡 – 数据工具 组，并选择“ 数据验证 ”来启动数据验证对话框。

步骤2：设置数据验证规则

在显示数据验证对话框后，我们需要转到“设置”选项卡来定义验证条件。我们可以在验证条件中提供所需的值、单元格引用或公式。

假设我们需要限制用户输入每个学生的分数，但分数必须在0到100之间。这样，我们可以在一定程度上消除错误输入的机会。为此，我们需要在“设置”选项卡中设置以下图像中的条件。

在设置完所有验证设置后，我们需要点击“确定”按钮关闭验证对话框，或者切换到下一个标签页来插入输入消息和/或错误警报。另外两个标签页是可选的，用于提示用户根据验证规则输入适当的值。

步骤3：创建要显示的输入消息（可选）

如果我们想向用户显示一条消息，告诉他们在所选单元格中支持或允许哪种类型的数据，我们可以使用输入消息标签页。使用输入消息，我们可以在用户选择相应的单元格/单元格时，告知用户允许的数据类型格式。

例如，我们可以将以下消息显示到所需的字段（单元格/单元格/范围）中。

一旦输入消息输入完成，我们可以点击“确定”按钮或移动到“错误提醒”选项卡。

设置完输入消息后，相应的单元格将显示如下的消息：

步骤4：添加错误警报（可选）

除了输入提示信息外，我们还可以设置错误警报，在用户输入无效数据到相应单元格时显示。此外，我们还可以添加自定义错误信息。

在上图中，我们使用了“停止”选项作为错误警报的样式。我们可以相应地使用另外两种样式，警告和信息。最后，我们必须点击“确定”按钮。

当用户输入无效数据时，它会触发一个带有消息的错误窗口，并且不允许无效输入。

如果我们不设置自定义错误警报并在Excel单元格中设置验证规则，则Excel会自动显示带有预定义错误消息的默认错误警报。它看起来像这样：

数据验证示例

以下是一些在Excel中使用数据验证的基本示例：

示例1：限制用户只能从下拉菜单中输入/选择指定的值

假设我们希望限制用户从预定义值列表中选择一个选项/值。这种数据验证情况在大多数Excel表格中使用。在我们的示例中，我们希望限制用户从预定义值中选择一个性别，例如“男性”，“女性”或“其他”。

为此，我们需要执行以下步骤：

• 首先，我们需要选择要应用验证的单元格。在我们的例子中，我们选择了单元格B1。
  

• 然后，我们通过点击工具栏上的 数据 选项卡下的 数据验证 图标来启动 数据验证对话框 。
  

• 接下来，我们需要在 设置 选项卡下的下拉菜单中选择 列表 选项，并在源框中用逗号分隔指定列表项。然而，如果我们想指定多个列表项，我们必须引用包含列表项的单元格范围，而不是直接在源框中输入。
  

• 最后，我们需要点击“确定”按钮。我们还可以在下一个选项卡中指定 输入提示 ，如下图所示：
  

这就是我们如何在Excel中使用数据验证功能并创建下拉列表。

示例2：限制用户输入有效的电子邮件地址

假设我们希望在特定单元格中应用数据验证，以限制用户只能输入具有有效格式的电子邮件地址，即用户名@域名，其中域名是电子邮件服务提供商。

为此，我们需要执行以下步骤：

• 首先，我们需要选择一个特定的单元格来应用数据验证。在我们的情况下，我们选择了单元格B1。
  

• 接下来，我们需要导航到“数据”选项卡，选择“数据验证”选项。
  

• 然后，我们需要从“设置”下拉菜单中选择“自定义”选项。在外层引号中，我们指定一个自定义公式”=ISNUMBER(FIND(“@”,B1))”，以允许在选定的单元格中仅输入带有”@”符号的值。
  

• 我们可以指定“输入提示”来指导用户输入有效的电子邮件ID，如下所示：
  

• 最后，我们必须指定”错误警告消息”，只有当用户输入无效的电子邮件ID时才会显示。
  

• 在数据验证对话框中配置所有选项后，我们需要点击“确定”按钮来保存应用的更改。现在，当用户选择相应的单元格时，将显示输入消息：
  

这就是我们如何限制Excel中特定单元格仅接受有效的电子邮件ID/地址的方法。类似地，我们可以键入任何其他特定文本，以相应地限制用户输入。但是，需要注意的是FIND函数区分大小写。因此，如果我们不需要限制文本的大小写，可以在上述过程中使用SEARCH函数。

示例3：限制用户输入未来日期

在Excel表格中输入日期是常见的数据输入任务之一。有时，用户可能会输入错误的日期或未来的日期，即使我们希望在表格中记录的所有日期都是之前的。在这种情况下，我们可以使用数据验证来防止在特定单元格中输入未来日期。

为此，我们需要执行以下步骤：

• 首先，我们需要选择特定的单元格，并点击工具栏上的“数据验证”图标。 在设置选项卡下的数据验证对话框中，我们必须选择一个选项 日期 。它将显示两个额外的部分，如日期和结束日期。由于我们想要阻止未来的日期，我们选择选项’ 小于等于 ‘， 并在相应的部分中输入 日期公式 “=TODAY()”（不带引号）。 一旦所有验证都设置完成，设置选项卡将如下所示：
  

• 在设置验证规则之后，我们可以使用下面的两个选项卡设置输入消息和错误提示，与上述两个示例中所做的相似。 现在，当用户选择相应的单元格时，将显示输入消息：
  

类似地，我们可以使用Excel的数据验证功能来允许其他日期约束。

示例4：限制用户输入指定长度的值

有时，我们希望限制用户只能输入特定长度或字符的值。假设需要接受用户输入的单元格中的PAN号码。由于PAN号码是一个十位数的唯一字母数字组合，我们可以创建验证规则来接受长度为10的值。

为此，我们需要执行以下步骤：

• 首先，我们需要选择特定的单元格，并点击工具栏上的“数据验证”图标。 在设置选项卡下的数据验证对话框中，我们必须选择一个选项 文本长度 。它将显示两个额外的部分，如数据和结束长度。由于我们想要允许10位数，我们选择选项’ 等于 ‘， 并在相应的部分中输入长度’ 10 ‘（不带引号）。 一旦所有的验证都设置完成，设置选项卡将如下所示：
  

• 在为特定文本长度设置验证规则之后，我们可以使用下面的两个选项卡设置输入消息和错误提示。 现在，当用户选择相应的单元格时，将显示输入消息：
  

类似地，我们可以使用Excel的数据验证功能来设置其他基于长度的值的限制。

示例5：限制用户只能输入大写字母

如果我们只想接受用户输入的大写字母，我们可以在Excel中设置验证。假设我们希望接受包含文本和数字的PAN号码输入，但我们只希望用户使用大写字母输入。

为此，我们需要执行以下步骤：

• 首先，我们需要选择特定的单元格并从工具栏中单击数据验证图标。在设置选项卡下的数据验证对话框中，我们必须选择 自定义 选项。接下来，我们需要应用无引号的自定义公式”=EXACT(B1,UPPER(B1))”。我们在公式中输入单元格B1，因为在我们的示例中B1是结果单元格。
  

• 在设置了只允许大写字母输入的验证规则后，我们可以使用接下来的两个选项卡设置输入消息和错误警报。 现在，当用户选择相应的单元格时，将显示输入消息：
  

同样，我们可以使用Excel中的数据验证来设置其他限制。

如何编辑Excel中的验证规则

假设我们之前在Excel表中应用了数据验证，现在要编辑验证规则。我们必须执行以下步骤来更改或编辑验证规则：

• 选择具有验证的单元格。
• 从工具栏中启动 数据验证对话框 。
• 在对话框的相应选项卡下进行所需的更改，并单击确定。

如何找到或查找数据验证

假设我们有一个应用了数据验证规则的Excel表。现在，我们想找出具有验证的单元格。我们必须按照以下步骤在Excel中查找具有数据验证的单元格：

• 在工具栏上导航至 主页 选项卡。
• 在快捷菜单中的 查找与选择 下拉按钮下，选择 数据验证 选项。

使用这些步骤后，将选中/突出显示具有验证的相应单元格。

如何将验证规则复制到其他单元格

假设我们有一些具有验证规则的单元格，并且我们希望将相同的验证规则应用于其他单元格。要做到这一点，我们可以使用“特殊粘贴”功能，如下所示：

• 使用快捷键 Ctrl + C 选择并复制具有验证规则的单元格。
• 使用快捷键 Ctrl + Alt + V + N 将复制的单元格粘贴到我们希望应用数据验证的单元格上。或者，我们可以使用右键菜单选项打开 特殊粘贴对话框 ，然后选择“验证”选项。

这将在复制的其他单元格上应用相同的验证。

如何删除或清除数据验证

有两种常用方法可以删除或清除Excel中的所有验证：

方法1：使用数据验证对话框清除数据验证

• 选择所有单元格 以删除数据验证。
• 从功能区中启动 数据验证对话框 。
• 在“设置”选项卡下点击“ 全部清除 ”，然后点击 确定 。

这将从所选单元格中移除/清除数据验证。

方法2：使用“粘贴特殊”功能清除数据验证

• 选择没有验证规则的空单元格，并使用快捷键 Ctrl + C 进行复制。
• 选择带有要移除数据验证的单元格。
• 使用 Ctrl + V 粘贴内容，并按 Enter 。

这种方法通常将数据验证替换为空单元格，间接地从Excel中移除数据验证。