什么是 Excel 中的数据验证？在 Excel 中添加、编辑与删除数据验证规则的全面指南

理解并实施数据验证原则对初级数据分析师和资深专家都至关重要。在本教程中，我们将讲解如何在 Excel 中执行数据验证。我们将介绍可用的各类数据验证技术，并演示如何添加、编辑和删除数据验证规则。

如果您想在开始前复习一下 Excel 基础（包括主要公式、运算符、数学函数等），请查看我们的Excel 公式速查表。

什么是 Excel 中的数据验证？

Excel 中的数据验证是一个用于控制单元格可输入值的特定功能。它提供了一系列技术，可用于检查现有值，或创建适用于新输入值的规则。

要在 Excel 中找到数据验证功能，请按以下步骤操作：

1. 打开数据选项卡。
2. 转到数据工具组。
3. 单击数据验证按钮。

打开数据验证功能

随后将出现如下弹出窗口。

数据验证弹出窗口

在本教程的后面部分，我们将更详细地讲解如何设置此窗口，包括如何修改和删除数据验证规则。首先，让我们进一步了解数据验证。

为什么数据验证很重要？

数据验证是指检查已输入或将要输入数据库以供后续存储与处理的数据的准确性和一致性。通过数据验证，我们确保数据符合特定的预定义标准。这些标准包括正确的数据类型、在指定范围内的取值、以及合适长度的数据元素等。

健全的数据验证至关重要，原因有很多。它可以防止数据录入错误，确保用于分析的信息可靠有效。下面我们来看一看进行数据验证的主要原因。

数据质量

数据验证可防止不准确或不一致的值存入数据库。这会减少后续错误、提升整体数据质量，并增强数据分析的有效性。

对高质量数据重要性的诠释，正好体现在计算机科学中广为人知的GIGO（garbage-in garbage-out）概念中。简单来说，分析有缺陷的数据会得出错误且具有误导性的结果。

让我们看一些未应用数据验证规则而导致出现异常值的示例。

• 高校记录：一所大学不可能有小数个学生；只有整数才有效。
• 医疗记录：人的体温不可能高于 50°C。
• 年龄录入：年龄字段应只接受现实可行的数值，通常在 0 到 120 之间。
• 电子邮箱地址：邮箱字段应符合特定格式，包括“@”符号。
• 库存数量：库存系统中的产品数量不应为负数。

请记住，尽管数据验证确保了数据完整性，但并不保证绝对正确。例如，在一张猫狗表中，验证规则可以确保只允许输入“cat”或“dog”，但仍无法阻止误将猫标记为狗的情况。

数据完整性与合规

数据验证对于维护数据完整性以及确保符合行业法规和标准至关重要。

在医疗领域，遵循HIPAA以保护患者信息是强制性的。同样，在欧盟经营的企业必须遵守GDPR，以确保个人数据的隐私与安全。

数据清洗与预处理

有一个广为流传的原则认为，数据分析师大约将 80% 的时间花在数据准备上，只有 20% 用于实际分析。由于数据分析师往往将大量时间用于清洗与预处理数据，因此在工作流早期应用数据验证规则可以节省大量时间。

决策制定

对经过验证的数据进行分析，会得出可靠且有洞见的结论，从而帮助做出更明智的数据驱动决策。从长期来看，数据验证能为公司节省时间与成本，使业务更健康、更高效、更具盈利能力。

Excel 中不同的数据验证技术

为满足不同需求，Excel 提供了多种数据验证方法。为便于理解，我们将它们分组并逐一说明。

限制数据类型与取值范围/长度

在 Excel 中，您可以从以下数据类型中选择：Whole number、Decimal、Date、Time 和 Text。

接下来，我们创建一张表，来说明某些逻辑条件对不同数据类型的适用性。这些逻辑条件包括：between、not between、equal to、not equal to、greater than、less than、greater than or equal to 和 less than or equal to。

• Between 与 Not Between：这些条件通常用于数值、日期和时间。对于text，between 与 not between 指的是单元格中字符数（长度）。

• Equal to 与 Not Equal to：可适用于所有数据类型。对于 text，它会检查是否精确匹配。

• Greater Than、Less Than、Greater Than or Equal To、Less Than or Equal To：这些条件适用于 whole number、date 和 time 数据类型，但不直接适用于 text，除非考虑文本长度，或在某些特定情况下考虑字母顺序。

创建下拉列表

有时，需要将某些 Excel 单元格中的选项限制为一组预定义的值。以我们之前的示例为例：在表中只有猫和狗是有效输入。为了防止鳄鱼等其他动物被录入表格，最佳做法是为每个相关单元格创建一个下拉列表。让我们看看具体步骤。

1. 输入您希望出现在下拉列表中的项目。您也可以通过选中整个列表，右键单击，选择排序并选择一种方式来对列表项进行排序。

为下拉列表输入项目

2. 将列表转换为 Excel 表格。选择其范围内的任意单元格并按下 Ctrl+T。此步骤很重要，因为如果您添加或删除列表项，所有依赖的下拉列表都会自动更新。

将下拉数据列表转换为表格

3. 选择您希望添加下拉列表的工作表单元格。
4. 打开数据验证弹窗。打开数据选项卡，转到数据工具组并单击数据验证按钮。
5. 在设置选项卡的允许字段中选择序列（List）。保持选中单元格下拉。如果目标单元格允许为空，保持选中忽略空值；否则，请取消勾选：

将下拉列表设为数据验证方法

6. 将光标置于来源框中，选择您的列表范围并单击确定。需要排除表头行，以避免其成为可选项。

选择下拉列表的数据来源

7. 选择已创建数据验证的单元格。此时您会看到单元格右侧出现一个下拉三角。单击三角后，您即可为该单元格选择所需的值。

在单元格中打开下拉列表

应用自定义公式

• range：指要应用基于自定义公式的数据验证的单元格范围。
• uppermost_cell：指该范围内的首个（最上方）单元格。

仅数字或仅文本

下面两条公式用于验证特定单元格内容是数字或文本。当您需要区分数值与非数值，或文本与非文本时非常有用。

=ISNUMBER(uppermost_cell)技术支持

=ISTEXT(uppermost_cell)技术支持

仅大写或仅小写文本

=AND(EXACT(uppermost_cell, UPPER(uppermost_cell)), ISTEXT(uppermost_cell))技术支持

=AND(EXACT(uppermost_cell, LOWER(uppermost_cell)), ISTEXT(uppermost_cell))技术支持

不允许重复

=COUNTIF(range, uppermost_cell)<=1技术支持

仅允许以特定子串开头的值

下式用于检查字符串的前 n 个字符是否完全匹配。将返回 TRUE 或 FALSE 值。

=EXACT(LEFT(uppermost_cell, n), substring)技术支持

仅工作日或仅周末

=WEEKDAY(uppermost_cell, 2)<=5技术支持

=WEEKDAY(uppermost_cell, 2)>5技术支持

仅允许过去日期

=uppermost_cell<TODAY()技术支持

不允许前后空格

=uppermost_cell=TRIM(uppermost_cell)技术支持

不允许空格

=uppermost_cell=SUBSTITUTE(uppermost_cell, " ", "")技术支持

防止总和超限

=SUM(range)<=total_cell技术支持

想要快速提升您在 Excel 公式方面的专业度，建议学习实践导向的Excel 数据分析课程。

如何在 Excel 中使用数据验证

为清晰起见，本节分为两个小节。第一部分演示如何配置工作簿来纳入数据验证规则。第二部分说明如何将这些规则应用于已包含数据的单元格。我们来看看。

如何为数据验证规则配置 Excel 工作簿

1. 选择需要进行数据验证的单元格。
2. 打开数据验证弹窗。打开数据选项卡，转到数据工具组并单击数据验证按钮。
3. 在设置选项卡的允许字段中选择所需方法，默认是任意值（表示不进行数据验证）。

选择所需的方法

4. 如果目标单元格允许为空，请保持选中忽略空值；否则，请取消勾选。

处理空白单元格

5. 如果选择了序列方法，请根据数据类型选择相关的预设规则；如果选择了自定义方法，请输入自定义公式。

选择逻辑条件以限制取值范围/长度

输入上下限以限制取值范围/长度

应用自定义公式

6. 在输入信息选项卡中，您可以设置标题和输入信息。默认情况下，没有输入信息。

设置输入提示以引导用户输入正确类型的值

查看输入提示的显示效果

7. 在出错警告选项卡中，您可以设置错误消息的样式（停止、警告或信息）、标题和错误消息。默认的错误消息样式为停止。

设置错误消息

单击确定后，您可以在任一受此数据验证约束的空白单元格中输入无效值以测试其效果。下面是每种样式的示例错误消息。

输入无效值时显示“停止”样式错误消息

输入无效值时显示“警告”样式错误消息

输入无效值时显示“信息”样式错误消息

默认错误消息示例

注意：警告或信息样式的错误消息不会阻止人们输入无效数据，而停止样式会阻止。

如何在已填充单元格上使用数据验证

如果要进行数据验证的部分单元格已包含值，该怎么办？数据验证功能同时支持录入前与录入后的数据验证。

1. 选中要进行数据验证的单元格，其中部分单元格可能已包含值。

选择要进行数据验证的单元格（包含已有值的单元格）

2. 打开数据验证弹窗。打开数据选项卡，转到数据工具组并打开数据验证按钮。
3. 在设置选项卡的允许字段中选择所需方法，默认是任意值。

为所选单元格设置数据验证

3. 在已应用数据验证的这些单元格中任选一个，打开数据选项卡，单击数据验证按钮右侧的下拉箭头（位于数据工具组），选择圈释无效数据。

圈释无效数据

被圈释的无效数据

4. 现在您可以逐一修改每个无效值，使其满足设定条件。完成后，围绕该值的圆圈会自动消失。请注意，如果只是删除无效值，圆圈会保留。
5. 要移除圈释，请在这些已应用数据验证的单元格中任选一个，打开数据选项卡，进入数据工具组，打开数据验证按钮，然后选择清除圈释。

移除无效数据的圈释

如何在 Excel 中编辑数据验证

1. 选择任一已应用数据验证的单元格。
2. 打开数据验证弹窗。打开数据选项卡，转到数据工具组并单击数据验证按钮。
3. 在数据验证窗口的任一选项卡上进行所需修改。如有需要，请参考“如何在 Excel 中使用数据验证”一节中的说明。
4. 转到设置选项卡，勾选将这些更改应用于具有相同设置的所有其他单元格。
5. 单击确定。

将修改应用于所有具有相同设置的单元格

如何在 Excel 中删除数据验证

1. 选择任一已应用数据验证的单元格。
2. 打开数据验证弹窗。打开数据选项卡，转到数据工具组并单击数据验证按钮。
3. 转到设置选项卡，勾选将这些更改应用于具有相同设置的所有其他单元格。
4. 单击全部清除按钮。
5. 单击确定。

删除数据验证

结论

本教程介绍了数据验证的定义、优势、在 Excel 中的实现、可用的多种技术，以及如何在 Excel 中使用、编辑和删除数据验证。

如果您希望充分释放 Excel 的能力，并通过动手练习学习如何准备、可视化与分析数据，同时了解一些高级功能，建议尝试内容全面、对初学者友好的技能路径Excel 基础。此外，还可考虑专业课程，例如Excel 数据准备或Excel 财务建模，以进行针对性训练、深化您的专业知识。这些资源将帮助您从基本的数据验证迈向精通 Excel 高级功能。