Excel 数据验证

MS Excel 是最流行的电子表格软件，具有广泛的功能、公式和函数。数据验证是 MS Excel 中的一项重要功能。在为用户或客户创建 Excel 工作表时，我们可能经常需要根据不同标准限制输入，以确保所有条目或输入都是正确且一致的。数据验证是一种解决方案，可帮助我们根据指定的规则控制用户对特定单元格或范围的输入。

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

什么是数据验证？

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

使用数据验证可以设置的一些重要任务（限制/验证）如下：

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

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

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

在 Excel 工作表的任何单元格或单元格范围上应用数据验证，都可以根据验证规则阻止用户在相应单元格中输入任何不需要的条目。例如，如果我们设置验证以仅接受数字或数值，其他用户或我们自己将无法输入除数字以外的任何值。

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

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

数据验证控件

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

一旦我们从功能区单击“数据验证”图标，它会立即启动一个数据验证对话框。

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

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

数据验证对话框包含三个重要选项/选项卡：设置、输入信息和错误警告。

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

设置选项卡

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

输入信息选项卡

输入信息选项卡有一个文本框，用于输入在选中相应单元格后显示的邮件。输入信息是数据验证的可选功能。如果我们不将任何邮件定义为输入信息，则 Excel 在用户选择具有数据验证的相应单元格时不会显示任何邮件。它不会影响数据验证的工作，也不会对用户在单元格中输入的内容产生任何影响或控制。但是，它可以帮助告知用户允许或预期的数据值。

错误警告选项卡

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

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

• 停止：当错误样式设置为“停止”时，Excel 会阻止用户在相应单元格中输入无效数据。如果用户输入无效数据，它会触发一个带有指定消息的弹出窗口，并且输入将被拒绝。停止警报窗口显示两个选项：“重试”（编辑无效数据）和“取消”（删除无效数据）。尽管停止警报窗口显示重试选项，但用户必须输入正确的、在重试时通过验证的值。
  
• 警告：当错误样式设置为“警告”时，Excel 会警告用户输入的值无效。它还会显示一个不同的图标和指定的邮件。但是，它不会阻止用户输入无效值；用户可以忽略警告消息并注册一个甚至不通过验证的值。警告警报窗口显示三个选项：“是”（接受无效数据）、“否”（编辑无效数据）和“取消”（删除无效数据）。
  
• 信息：当错误样式设置为“信息”时，Excel 会告知用户输入的数据无效。它会显示一个不同的图标和指定的邮件。与警告错误样式类似，信息错误样式也不会阻止无效数据。用户可以忽略信息消息并注册不通过验证的无效数据。信息警报窗口显示两个选项：“确定”（接受无效数据）和“取消”（删除无效数据）。
  

数据验证选项

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

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

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

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

如何在 Excel 中添加数据验证？

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

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

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

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

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

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

设置完所有验证设置后，我们需要单击“确定”按钮关闭验证对话框，或者移至下一个选项卡以插入输入消息和/或错误警告。另外两个选项卡是可选的，用于告知用户输入符合验证规则的适当值。

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

如果我们想显示一条消息告诉用户所选单元格支持或允许什么类型的数据，我们可以使用输入消息选项卡。使用输入消息，我们可以在用户选择相应的单元格/单元格范围时告知用户允许的数据类型格式。

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

输入完输入消息后，我们可以单击“确定”按钮或继续进入“错误警告”选项卡。

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

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

除了输入消息，我们还可以设置一个错误警告，以便在用户在相应单元格中输入无效数据时显示。此外，我们还可以添加自定义错误消息。

在上图所示的示例中，我们使用了“停止”选项作为错误警报样式。我们可以根据需要使用其他两种样式：警告和信息。最后，我们必须单击确定按钮。

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

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

数据验证示例

以下是 Excel 中数据验证的一些重要示例：

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

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

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

• 首先，我们需要选择一个或多个单元格来应用验证。在我们的示例中，我们选择单元格 B1。
  
• 接下来，我们通过单击工具栏上“数据”选项卡下的“数据验证”图标来启动数据验证对话框。
  
• 之后，我们需要在“设置”选项卡下的下拉菜单中选择“列表”选项，并在源框中用逗号分隔指定列表项。但是，如果我们想指定多个列表项，我们必须引用包含列表项的单元格范围，而不是直接在源框中输入它们。
  
• 最后，我们需要单击“确定”按钮。我们还可以从下一个选项卡指定输入消息，如下面的图像所示：
  
  除了输入消息，我们还可以从下一个选项卡指定错误警告消息：
  
  当用户单击受限制的单元格时，会显示下拉图标。用户可以单击该图标打开列表并选择所需的选项。
  
  当用户单击受限制的单元格时，会显示输入消息。
  
  当用户输入自定义文本时，会显示错误消息。
  

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

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

假设我们想在特定单元格中应用数据验证，以限制用户输入具有有效格式的电子邮件 ID，即 username@domain，其中 domain 指的是电子邮件服务提供商。

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

• 首先，我们需要选择一个或多个特定单元格来应用数据验证。在本例中，我们选择单元格 B1。
  
• 接下来，我们需要导航到“数据”选项卡并选择“数据验证”选项。
  
• 之后，我们需要在“设置”选项卡下的下拉菜单中选择“自定义”选项。我们指定一个自定义公式“=ISNUMBER(FIND("@",B1))”（不含外部引号），以仅允许在选定单元格中包含“@”符号的值。
  
• 我们可以指定输入消息来指示用户输入有效的电子邮件 ID，如下所示：
  
• 最后，我们必须指定错误警告消息，该消息仅在用户输入无效电子邮件 ID 时显示。
  
• 配置完数据验证对话框中的所有选项后，我们需要单击确定按钮来保存应用的更改。
  现在，当用户选择相应的单元格时，会显示输入消息。
  
  当用户输入有效的电子邮件 ID 时，在通过应用验证后，它将被接受到单元格中。
  
  当用户输入无效电子邮件 ID 时，验证会失败并触发错误警告。
  

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

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

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

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

• 首先，我们需要选择特定的单元格并从工具栏中单击“数据验证”图标。我们必须在“设置”选项卡下的“数据验证”对话框中选择“日期”选项。它将显示另外两个部分，例如开始日期和结束日期。由于我们想阻止未来日期，我们选择“小于或等于”选项，并在相应的字段中输入“=TODAY()”日期公式（不含引号）。
  设置完所有验证后，“设置”选项卡如下所示：
  
• 设置完验证规则后，我们可以使用接下来的两个选项卡来设置输入消息和错误警告，这与我们在前面两个示例中所做的类似。
  现在，当用户选择相应的单元格时，会显示输入消息。
  
  当用户输入未来日期时，会显示错误警告。
  

类似地，我们可以使用 Excel 中的数据验证功能对日期进行其他限制。

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

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

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

• 首先，我们需要选择特定的单元格并从工具栏中单击“数据验证”图标。我们必须在“设置”选项卡下的“数据验证”对话框中选择“文本长度”选项。它将显示另外两个部分，例如数据和结束长度。由于我们想允许 10 位数字，我们分别选择“等于”选项，并在相应的字段中输入长度“10”（不含引号）。
  设置完所有验证后，“设置”选项卡如下所示：
  
• 设置完特定文本长度的验证规则后，我们可以使用接下来的两个选项卡来设置输入消息和错误警告。
  现在，当用户选择相应的单元格时，会显示输入消息。
  
  当用户输入的字符数少于 10 个或多于 10 个时，会显示错误警告消息。
  

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

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

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

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

• 首先，我们需要选择特定的单元格并从工具栏中单击“数据验证”图标。我们必须在“设置”选项卡下的“数据验证”对话框中选择“自定义”选项。接下来，我们需要应用自定义公式“=EXACT(B1,UPPER(B1))”（不含引号）。我们在公式中输入单元格 B1，因为在我们的示例中 B1 是结果单元格。
  
  在上图所示的示例中，UPPER 函数用于将字符与大写字母匹配。此外，EXACT 函数确保单元格输入与大写版本匹配。
• 设置完仅大写输入的验证规则后，我们可以使用接下来的两个选项卡来设置输入消息和错误警告。
  现在，当用户选择相应的单元格时，会显示输入消息。
  
  当用户输入无效值（小写值）时，会显示错误警告。
  

类似地，我们可以使用 Excel 中的数据验证通过自定义公式设置其他限制。

如何在 Excel 中编辑验证规则？

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

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

如何定位或查找数据验证？

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

• 导航到工具栏上的“开始”选项卡。
• 在“编辑”组下的快捷方式“查找和选择”中，单击下拉图标。
• 从列表中选择“数据验证”选项。

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

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

假设我们有一些具有验证规则的单元格，并且我们想将相同的验证规则应用于其他单元格。为此，我们可以使用粘贴特殊功能，如下所示：

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

这将把相同的验证应用于复制了内容的其他单元格。

如何删除或清除数据验证？

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

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

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

这将从选定的单元格中删除/清除数据验证。

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

• 选择没有验证规则的空白单元格，然后使用快捷方式Ctrl + C复制。
• 选择要删除数据验证的具有验证的单元格。
• 使用Ctrl + V粘贴内容，然后按Enter。

此方法通常会用空单元格替换数据验证，这是 Excel 中删除数据验证的一种间接方法。