Excel 数据验证：在 Excel 中添加、使用、复制和删除数据验证
在 Excel 中，数据验证是一项有用的功能，您可以使用它来限制用户可以在单元格中输入的内容。 例如，数据验证功能可以帮助您限制文本字符串的长度，或以特定字符开头/结尾的文本，或要输入的唯一值等。
在本教程中，我们将讨论如何在 Excel 中添加、使用和删除数据验证，并将详细演示该功能的一些基本和高级操作。
目录：
- 4.1 数据验证只允许数字或文本
- 4.2 数据验证只允许字母数字值
- 4.3 数据验证允许文本以特定字符开始或结束
- 4.4 数据验证允许条目必须包含/不得包含特定文本
- 4.5 数据验证只允许唯一值
- 4.6 数据验证只允许大写/小写/正确大小写
- 4.7 数据验证允许存在/不存在于另一个列表中的值
- 4.8 数据验证强制只输入电话号码格式
- 4.9 数据验证强制只输入电子邮件地址
- 4.10 数据验证强制只输入 IP 地址
- 4.11 数据验证限制超过总值的值
- 4.12 数据验证限制基于另一个单元格的单元格输入
- 4.13 数据验证只允许输入工作日或周末
- 4.14 数据验证允许根据今天的日期输入日期
- 4.15 数据验证允许根据当前时间输入时间
- 4.16 数据验证特定或当前年份的日期
- 4.17 数据验证当前周或月的日期
1. Excel 中的数据验证是什么？
数据验证 功能可以帮助您限制工作表中的输入内容。 通常，您可以创建一些验证规则，以防止或仅允许在选定单元格列表中输入某种类型的数据。
数据验证功能的一些基本用法：
 |
|
2.如何在Excel中添加数据验证？
在 Excel 工作表中，您可以通过以下步骤添加数据验证：
1. 选择要设置数据验证的单元格列表，然后单击 时间 > 数据验证 > 数据验证，请参见屏幕截图：
2。 在 数据验证 对话框中的 个人设置 选项卡，请创建您自己的验证规则。 在标准框中，您可以提供以下任何类型：
- 价值观：直接在条件框中键入数字；
- 单元格参考：引用工作表或其他工作表中的单元格；
- 公式：创建更复杂的公式作为条件。
例如，我将创建一个规则，只允许输入 100 到 1000 之间的整数，这里设置的条件如下图所示：
3. 配置好条件后，就可以进入 输入信息。 or 错误警报 选项卡，根据需要为验证单元格设置输入消息或错误警报。 （如果您不想设置提醒，请点击 OK 直接结束。）
3.1) 添加输入消息（可选）：
您可以创建在选择包含数据验证的单元格时显示的消息。 此消息有助于提醒用户他们可以在单元格中输入什么。
去 输入信息。 选项卡并执行以下操作：
 |
|
现在，当您选择一个经过验证的单元格时，将显示一个消息框，如下所示：
3.2）创建有意义的错误消息（可选）：
除了创建输入消息之外，您还可以在将无效数据输入到带有数据验证的单元格中时显示错误警报。
去 错误警报 的选项卡 数据验证 对话框，请这样做：
 |
|
现在，当输入无效值时，将弹出消息警告框，如下图所示：
Stop 停止 选项：您可以单击 Retry 重试 键入另一个值或 取消 删除条目。
警告 选项：点击 是 输入无效的条目， 没有 修改它，或 取消 删除条目。
资讯 选项：点击 OK 输入无效条目或 取消 删除条目。
备注: 如果您没有在 错误警报 框，默认 Stop 停止 将显示如下图所示的警报提示框：
3. 数据验证的基本示例
使用此数据验证功能时，有 8 个内置选项供您设置数据验证。 如：任何值、整数和小数、日期和时间、列表、文本长度和自定义公式。 在本节中，我们将讨论如何使用 Excel 中的一些内置选项？
3.1 整数和小数的数据验证
1. 选择您希望只允许整数或小数的单元格列表，然后单击 时间 > 数据验证 > 数据验证.
2。 在 数据验证 对话框中的 个人设置 选项卡，请执行以下操作：
- 选择对应的项目 完整的号码 or 十进制 ，在 让 下拉框。
- 然后，选择您需要的标准之一 时间 框（在这个例子中，我选择 之间 选项）。
- Tips：标准包含：介于、不介于、等于、不等于、大于、小于、大于或等于、小于或等于。
- 接下来，输入 最低限度 和 最大 您需要的值（我想要 0 到 1 00 之间的数字）。
- 最后点击 OK 按钮。
3. 现在，只允许在您选择的单元格中输入从 0 到 100 的整数。
3.2 日期和时间的数据验证
要验证要输入的特定日期或时间，使用它很容易 数据验证，请执行以下操作：
1. 选择您只想允许特定日期或时间的单元格列表，然后单击 时间 > 数据验证 > 数据验证.
2。 在 数据验证 对话框中的 个人设置 选项卡，请执行以下操作：
- 选择对应的项目 日期 or 时间 ，在 让 下拉框。
- 然后，选择您需要的标准之一 时间 框（这里我选择 比...更棒 选项）。
- Tips：标准包含：介于、不介于、等于、不等于、大于、小于、大于或等于、小于或等于。
- 接下来，输入 起租日期 你需要（我想要大于 8/20/2021 的日期）。
- 最后点击 OK 按钮。
3. 现在，只允许在所选单元格中输入大于 8/20/2021 的日期。
3.3 文本长度的数据验证
如果您需要限制可以键入到单元格中的字符数。 例如，要将特定范围的内容限制为不超过 10 个字符，这 数据验证 也可以帮你一个忙。
1. 选择要限制文本长度的单元格列表，然后单击 时间 > 数据验证 > 数据验证.
2。 在 数据验证 对话框中的 个人设置 选项卡，请执行以下操作：
- 选择 文本长度 来自 让 下拉框。
- 然后，选择您需要的标准之一 时间 框（在这个例子中，我选择 少于 选项）。
- Tips：标准包含：介于、不介于、等于、不等于、大于、小于、大于或等于、小于或等于。
- 接下来，输入 最大 您需要限制的数量（我希望文本长度不超过 10 个字符）。
- 最后点击 OK 按钮。
3. 现在，所选单元格只允许键入少于 10 个字符的文本字符串。
3.4 数据验证列表（下拉列表）
有了这个强大的 数据验证 功能，您也可以快速轻松地在单元格中创建下拉列表。 请这样做：
1. 选择要插入下拉列表的目标单元格，然后单击 时间 > 数据验证 > 数据验证.
2。 在 数据验证 对话框中的 个人设置 选项卡，请执行以下操作：
- 选择 清单 来自 让 下拉列表。
- 在 来源 在文本框中，键入以逗号直接分隔的列表项。 例如，要将用户输入限制为三个选项，请键入 Not Started、In Progress、Completed，或者您可以选择包含要插入下拉列表的值的单元格列表。
- 最后点击 OK 按钮。
3. 现在，下拉列表已创建到单元格中，如下图所示：
4. 高级自定义数据验证规则
本节，我将介绍如何制定一些高级的自定义数据验证规则来解决您的各种问题，例如：创建验证公式以仅允许数字或文本字符串、仅允许唯一值、仅指定电话号码、电子邮件地址等.
4.1 数据验证只允许数字或文本
仅允许使用数据验证功能输入数字
要仅允许一系列单元格中的数字，请执行以下操作：
1. 选择您只想输入数字的单元格范围。
2。 点击 时间 > 数据验证 > 数据验证，在弹出 数据验证 对话框中的 个人设置 标签，请执行以下操作：
- 选择 定制版 来自 让 下拉列表。
- 然后，输入这个公式： =编号(A2) 到 公式 文本框。 （A2 是您要限制的所选范围的第一个单元格）
- 点击 OK 按钮关闭此对话框。
3. 从现在开始，只能在选定的单元格中输入数字。
备注： 这个 ISNUMBER 函数允许验证单元格中的任何数值，包括整数、小数、分数、日期和时间。
仅允许使用数据验证功能输入文本字符串
要将单元格条目限制为仅文本，您可以使用 数据验证 具有基于自定义公式的功能 ISTEXT 功能，请这样做：
1. 选择您只想输入文本字符串的单元格范围。
2。 点击 时间 > 数据验证 > 数据验证，在弹出 数据验证 对话框中的 个人设置 标签，请执行以下操作：
- 选择 定制版 来自 让 下拉列表。
- 然后，输入这个公式： =ISTEXT(A2) 到 公式 文本框。 （A2 是您要限制的所选范围的第一个单元格）
- 点击 OK 按钮关闭此对话框。
3. 现在，当向特定单元格输入数据时，只能允许文本格式数据。
4.2 数据验证只允许字母数字值
出于某些目的，您只想允许输入字母和数字值，但限制 ~、%、$、空格等特殊字符，本节将为您介绍一些技巧。
数据验证功能只允许字母数字值
为了防止特殊字符但只允许字母数字值，您应该在 数据验证 功能，请执行以下操作：
1. 选择您只想输入字母数字值的单元格范围。
2。 点击 时间 > 数据验证 > 数据验证，在弹出 数据验证 对话框中的 个人设置 标签，请执行以下操作：
- 选择 定制版 来自 让 下拉列表。
- 然后，将下面的公式输入到 公式 文本框。
- =IF(A2="",TRUE,IF(ISERROR(SUMPRODUCT(SEARCH(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1),"0123456789abcdefghijklmnopqrstuvwxyz"))),FALSE,TRUE))
- 点击 OK 按钮关闭此对话框。
备注：在以上公式中， A2 是要限制的选定范围的第一个单元格。
3. 现在，只允许输入字母和数字值，输入时将限制特殊字符，如下图所示：
仅允许具有惊人功能的字母数字值
可能上面的公式对于我们理解和记忆起来比较复杂，在这里，我将介绍一个方便的功能—— 防止打字 of Kutools for Excel，借助此功能，您可以轻松快速地解决此工作。
安装后 Kutools for Excel，请这样做：
1. 选择您只想输入字母数字值的单元格范围。
2。 然后，点击 库工具 > 防止打字 > 防止打字，请参见屏幕截图：
3。 在弹出 防止打字 对话框中选择 禁止输入特殊字符 选项，请参见屏幕截图：
4。 然后，点击 Ok 按钮，然后在以下提示框中，单击 是 > OK 以完成操作。 现在，在选定的单元格中，只允许使用字母和数值，请看截图：
4.3 数据验证允许文本以特定字符开始或结束
如果某个范围内的所有值都应以特定字符或子字符串开头或结尾，您可以使用基于 EXACT、LEFT、RIGHT 或 COUNTIF 函数的自定义公式进行数据验证。
允许文本以特定字符开头或结尾，只有一个条件
例如，我希望在特定单元格中输入文本字符串时，文本必须以“CN”开头或结尾，请这样做：
1. 选择仅允许以特定字符开头或结尾的文本的单元格范围。
2。 然后，点击 时间 > 数据验证 > 数据验证，在弹出 数据验证 对话框中的 个人设置 标签，请执行以下操作：
- 选择 定制版 来自 让 下拉列表。
- 然后，将下面的公式输入到 公式 文本框。
- Begin with: =EXACT(LEFT(A2,2),"CN")
End with: =EXACT(RIGHT(A2,2),"CN") - 点击 OK 按钮关闭此对话框。
备注：在以上公式中， A2 是所选范围的第一个单元格，数字 2 是您指定的字符数， CN 是您想要开始或结束的文本。
3. 从现在开始，只有以指定字符开头或结尾的文本字符串才能输入到选定的单元格中。 否则，将弹出警告警报提醒您，如下图所示：
提示： 上述公式区分大小写，如果您不需要区分大小写，请应用以下 CONTIF 公式：
Begin with (non case sensitive): =COUNTIF(A2,"CN*")
End with (non case sensitive): =COUNTIF(A2,"*CN")
备注: 星号 * 是匹配一个或多个字符的通配符。
允许文本以具有多个条件的特定字符开头或结尾（OR 逻辑）
例如，如果您希望文本必须以“CN”或“UK”开头或结尾，如下图所示，您需要使用加号 (+) 添加另一个 EXACT 实例。 请按照以下步骤操作：
1. 选择仅允许以多个条件开头或结尾的文本的单元格范围。
2。 然后，点击 时间 > 数据验证 > 数据验证，在弹出 数据验证 对话框中的 个人设置 标签，请执行以下操作：
- 选择 定制版 来自 让 下拉列表。
- 然后，将下面的公式输入到 公式 文本框。
- Begin with: =EXACT(LEFT(A2,2),"CN")+EXACT(LEFT(A2,2),"UK")
End with: =EXACT(RIGHT(A2,2),"CN")+EXACT(RIGHT(A2,2),"UK") - 点击 OK 按钮关闭此对话框。
备注：在以上公式中， A2 是所选范围的第一个单元格，数字 2 是您指定的字符数， CN 和 UK 是您想要开始或结束的特定文本。
3. 现在，只有以指定字符开头或结尾的文本字符串才能输入到所选单元格中。
提示： 要忽略大小写敏感，请应用以下 CONTIF 公式：
Begin with (non case sensitive): =COUNTIF(A2,"CN*")+COUNTIF(A2,"UK*")
End with (non case sensitive): =COUNTIF(A2,"*CN")+COUNTIF(A2,"*UK")
备注: 星号 * 是匹配一个或多个字符的通配符。
4.4 数据验证允许条目必须包含/不得包含特定文本
本节，我将讨论如何应用数据验证来允许值必须包含或不得包含 Excel 中的一个特定子字符串或多个子字符串之一。
允许条目必须包含一个或多个特定文本之一
允许条目必须包含一个特定文本
例如，要允许包含特定文本字符串的条目，所有输入的值都应包含文本“KTE”，如下图所示，您可以使用基于 FIND 和 ISNUMBER 函数的自定义公式应用数据验证。 请这样做：
1. 选择仅允许包含特定文本的文本的单元格范围。
2。 然后，点击 时间 > 数据验证 > 数据验证，在弹出 数据验证 对话框中的 个人设置 标签，请执行以下操作：
- 选择 定制版 从允许下拉列表中。
- 然后，将以下公式之一输入 公式 文本框。
- =ISNUMBER(FIND("KTE",A2)) (Case sensitive)
=ISNUMBER(SEARCH("KTE",A2)) (Non case sensitive) - 点击 OK 按钮关闭此对话框。
备注：在以上公式中， A2 是所选范围的第一个单元格，文本 韩国电信 是条目必须包含的文本字符串。
3. 现在，当输入的值不包含设计的文本时，会弹出警告提示框。
允许条目必须包含许多特定文本之一
上述公式仅适用于一个文本字符串，如果您需要如下图所示的单元格中允许多个文本字符串中的任何一个，您应该使用 SUMPRODUCT、FIND 和 ISNUMBER 函数来创建一个公式。
1. 选择一个单元格范围，这些单元格只允许包含多个项目中的任何一个的文本。
2。 然后，点击 时间 > 数据验证 > 数据验证，在弹出 数据验证 对话框中的 个人设置 标签，请执行以下操作：
- 选择 定制版 来自 让 下拉列表。
- 然后，根据需要输入以下公式之一 公式 文本框。
- =SUMPRODUCT(--ISNUMBER(FIND($C$2:$C$4,A2)))>0 (Case sensitive)
=SUMPRODUCT(--ISNUMBER(SEARCH($C$2:$C$4,A2)))>0 (Non case sensitive) - 然后，单击 OK 关闭对话框。
备注：在以上公式中， A2 是所选范围的第一个单元格， C2：C4 是您希望允许条目包含其中任何一个的值列表。
3. 现在，只能输入包含特定列表中任何一个值的条目。
允许条目不得包含一个或多个特定文本之一
允许条目不得包含一个特定文本
要验证条目不得包含特定文本，例如，要允许单元格中不得包含文本“KTE”的值，您可以使用 ISERROR 和 FIND 函数来创建数据验证规则。 请这样做：
1. 选择仅允许不包含特定文本的文本的单元格范围。
2。 然后，点击 时间 > 数据验证 > 数据验证，在弹出 数据验证 对话框中的 个人设置 标签，请执行以下操作：
- 选择 定制版 来自 让 下拉列表。
- 然后，将以下公式之一输入 公式 文本框。
- =ISERROR(FIND("KTE",A2)) (Case sensitive)
=ISERROR(SEARCH("KTE",A2)) (Non case sensitive) - 点击 OK 按钮关闭此对话框。
备注：在以上公式中， A2 是所选范围的第一个单元格，文本 韩国电信 是条目不得包含的文本字符串。
3. 现在，将禁止输入包含特定文本的条目。
允许条目不得包含许多特定文本之一
为了防止列表中的许多文本字符串之一被输入如下屏幕截图所示，您应该这样做：
1. 选择要阻止某些文本的单元格范围。
2。 然后，点击 时间 > 数据验证 > 数据验证，在弹出 数据验证 对话框中的 个人设置 标签，请执行以下操作：
- 选择 定制版 来自 让 下拉列表。
- 然后，将下面的公式输入到 公式 文本框。
- =SUMPRODUCT(--ISNUMBER(FIND($C$2:$C$4,A2)))=0 (Case sensitive)
=SUMPRODUCT(--ISNUMBER(SEARCH($C$2:$C$4,A2)))=0 (Non case sensitive) - 然后，单击 OK 关闭对话框。
备注：在以上公式中， A2 是所选范围的第一个单元格， C2：C4 如果条目包含其中任何一个，则是要阻止的值列表。
3. 从现在开始，将禁止输入包含任何特定文本的条目。
4.5 数据验证只允许唯一值
如果您想防止在单元格范围内输入重复数据，本节将介绍一些在 Excel 中解决此任务的快速方法。
仅允许具有数据验证功能的唯一值
通常，带有基于 COUNTIF 函数的自定义公式的数据验证功能可以帮助您，请执行以下步骤：
1. 选择您只想输入唯一值的单元格或列。
2。 然后，点击 时间 > 数据验证 > 数据验证，在弹出 数据验证 对话框中的 个人设置 选项卡，请执行以下操作：
- 选择 定制版 来自 让 下拉列表。
- 然后，将下面的公式输入到 公式 文本框。
- =COUNTIF($A$2:$A$9,A2)=1
- 点击 OK 按钮关闭此对话框。
备注：在以上公式中， A2：A9 是您希望只允许唯一值的单元格范围，以及 A2 是所选范围的第一个单元格。
3. 现在，只允许输入唯一值，并且在输入重复数据时会弹出警告消息，请参见截图：
仅允许具有 VBA 代码的唯一值
以下 VBA 代码也可以帮助您防止输入重复值，请按以下步骤操作：
1. 右键单击要仅允许唯一值的工作表选项卡，然后选择 查看代码 从上下文菜单中，弹出 Microsoft Visual Basic应用程序 窗口，请将以下代码复制并粘贴到空白模块中：
VBA 代码：只允许单元格范围内的唯一值：
Private Sub Worksheet_Change(ByVal Target As Range)
'Updateby Extendoffice
Dim xRg As Range, iLong, fLong As Long
If Not Intersect(Target, Me.[A1:A100]) Is Nothing Then
Application.EnableEvents = False
For Each xRg In Target
With xRg
If (.Value <> "") Then
If WorksheetFunction.CountIf(Me.[A:A], .Value) > 1 Then
iLong = .Interior.ColorIndex
fLong = .Font.ColorIndex
.Interior.ColorIndex = 3
.Font.ColorIndex = 6
MsgBox "Duplicate Entry !", vbCritical, "Kutools for Excel"
.ClearContents
.Interior.ColorIndex = iLong
.Font.ColorIndex = fLong
End If
End If
End With
Next
Application.EnableEvents = True
End If
End Sub
 |
 |
 |
备注：在上面的代码中， A1：A100 和 答： 是要防止重复的列中的单元格，请根据需要更改它们。
2. 然后保存并关闭此代码，现在，当在单元格 A1:A100 中输入重复值时，会弹出警告提示框，如下图所示：
仅允许具有方便功能的唯一值
如果你有 Kutools for Excel，其 防止重复 功能，您可以设置数据验证以防止只需单击几下就可以防止一系列单元格重复。
安装后 Kutools for Excel，请这样做：
1. 选择要防止重复值但只允许唯一数据的单元格范围。
2。 然后，点击 库工具 > 防止打字 > 防止重复，请参见屏幕截图：
3. 并且会弹出警告消息提醒您如果应用此功能将删除数据验证，请单击 是 并在下面的提示框中，点击 OK，请参见屏幕截图：
 |
|
 |
4. 现在，当您在指定的单元格中输入一些重复数据时，会显示一个提示框，提醒您重复数据无效，请看截图：
4.6 数据验证只允许大写/小写/正确大小写
此数据验证是一项强大的功能，它还可以帮助用户在单元格范围内仅输入大写、小写或正确的大小写条目。 请按照以下步骤操作：
1. 选择您只想输入大写、小写或正确大小写文本的单元格范围。
2。 然后，点击 时间 > 数据验证 > 数据验证，在弹出 数据验证 对话框中的 个人设置 选项卡，请执行以下操作：
- 选择 定制版 来自 让 下拉列表。
- 然后，将您需要的以下公式之一输入 公式 文本框。
- =AND(EXACT(A2,UPPER(A2)),ISTEXT(A2)) (only allow uppercase text)
=AND(EXACT(A2,LOWER(A2)),ISTEXT(A2)) (only allow lowercase text)
=AND(EXACT(A2,PROPER(A2)),ISTEXT(A2)) (only allow proper case text) - 点击 OK 按钮关闭此对话框。
备注：在以上公式中， A2 是您要使用的列的第一个单元格。
3. 现在，只有符合您创建的规则的条目才会被接受。
4.7 数据验证允许存在/不存在于另一个列表中的值
允许将存在或不存在于另一个列表中的值输入到一系列单元格中对我们大多数人来说可能是一个痛苦的问题。 事实上，您可以使用基于 COUNTIF 函数的简单公式的数据验证功能来处理它。
例如，我只想将 C2:C4 范围内的值输入到如下屏幕截图所示的单元格范围内，要解决此工作，请执行以下操作：
1. 选择要应用数据验证的单元格范围。
2。 然后，点击 时间 > 数据验证 > 数据验证，在弹出 数据验证 对话框中的 个人设置 选项卡，请执行以下操作：
- 选择 定制版 来自 让 下拉列表。
- 然后，将您需要的以下公式之一输入 公式 文本框。
- =COUNTIF($C$2:$C$4,A2)>0 (only allow values exist in another column)
=COUNTIF($C$2:$C$4,A2)=0 (prevent values exist in another column) - 点击 OK 按钮关闭此对话框。
备注：在以上公式中， A2 是您要使用的列的第一个单元格， C2：C4 如果条目是其中之一，则是要阻止或允许的值列表。
3. 现在，只有符合您创建的规则的条目才能进入，其他条目将被阻止。
4.8 数据验证强制只输入电话号码格式
输入公司员工信息时，一栏需要输入电话号码，为保证输入电话号码快速准确，此时可以设置电话号码数据校验。 例如，我只希望允许在工作表中输入这种格式的电话号码 (123) 456-7890，本节将介绍解决此任务的两个快速技巧。
使用数据验证功能强制使用电话号码格式
要只允许输入特定的电话号码格式，请执行以下操作：
1. 选择要输入特定电话号码格式的单元格列表，然后右键单击，选择 单元格格式 从上下文菜单中，查看屏幕截图：
2。 在 单元格格式 对话框中的 联系电话 标签，选择 定制版 在左边 产品分类 列表框，然后在Type文本框中输入你需要的电话号码格式，比如我就用这个 （###）### - #### 格式，请参见屏幕截图：
3。 然后，点击 OK 关闭对话框。
4. 格式化单元格后，继续选择单元格，然后打开 数据验证 单击对话框 时间 > 数据验证 > 数据验证, 在弹出的对话框中，在 个人设置 选项卡，请执行以下操作：
- 选择 定制版 来自 让 下拉列表。
- 然后输入这个公式 = AND（ISNUMBER（A2），LEN（A2）= 10） 进入公式文本框。
- 点击 OK 按钮关闭此对话框。
备注：在以上公式中， A2 是要验证电话号码的列的第一个单元格。
5. 现在，当输入 10 位号码时，它会根据您的需要自动转换为特定的电话号码格式，请参见截图：
 |
|
 |
备注：如果输入的数字不是10位数字，会弹出一个警告信息框提醒您，请看截图：
强制使用具有有用功能的电话号码格式
Kutools for Excel“ 验证电话号码 功能还可以帮助您强制只输入电话号码格式，只需点击几下。
安装后 Kutools for Excel，请这样做：
1. 选择只允许特定电话号码的单元格列表，然后单击 库工具 > 防止打字 > 验证电话号码，请参见屏幕截图：
2。 在 电话 对话框中，选择您需要的特定电话号码格式，或者您可以通过单击 地址 按钮，请参见屏幕截图：
3. 选择或设置电话号码格式后，单击 OK，现在只能输入特定格式的电话号码，否则会弹出警告信息提醒，看截图：
4.9 数据验证强制只输入电子邮件地址
假设您需要在工作表的一列中键入多个电子邮件地址，以防止输入某些错误的电子邮件地址格式，通常您可以设置数据验证规则，仅允许使用电子邮件地址格式。
仅使用数据验证功能强制使用电子邮件地址格式
通过使用自定义公式的数据验证功能，您可以创建一个规则以防止快速输入无效的电子邮件地址，请执行以下操作：
1. 选择您只想输入电子邮件地址的单元格，然后单击 时间 > 数据验证 > 数据验证.
2。 在弹出 数据验证 对话框中的 个人设置 选项卡，请执行以下操作：
- 选择 定制版 来自 让 下拉列表。
- 然后输入这个公式 = ISNUMBER（MATCH（“ *@*.?*”，A2,0）） 到 公式 文本框。
- 点击 OK 按钮关闭此对话框。
备注：在以上公式中， A2 是您要使用的列的第一个单元格。
3. 现在，如果输入的文本不是电子邮件地址格式，则会弹出警告消息框提醒您，请看截图：
使用方便的功能强制使用电子邮件地址格式
Kutools for Excel 支持一个惊人的功能—— 验证电子邮件地址，使用此实用程序，您只需单击一下即可防止无效的电子邮件地址。
安装后 Kutools for Excel，请执行以下操作：
1. 选择您只允许输入电子邮件地址的单元格，然后单击 库工具 > 防止打字 > 验证电子邮件地址。 看截图：
2. 然后，只允许输入电子邮件地址格式，否则会弹出警告消息框提醒您，请看截图：
4.10 数据验证强制只输入 IP 地址
在本节中，我将介绍一些快速技巧来设置数据验证以仅接受单元格范围内的 IP 地址。
使用数据验证功能强制使用 IP 地址格式
只允许在特定范围的单元格中输入 IP 地址，请执行以下操作：
1. 选择您只想输入 IP 地址的单元格，然后单击 时间 > 数据验证 > 数据验证.
2。 在弹出 数据验证 对话框中的 个人设置 选项卡，请执行以下操作：
- 选择 定制版 来自 让 下拉列表。
- 然后，将下面的公式输入到 公式 文本框。
- =AND((LEN(A2)-LEN(SUBSTITUTE(A2,".","")))=3,ISNUMBER(SUBSTITUTE(A2,".","")+0))
- 点击 OK 按钮关闭此对话框。
请注意： 在上式中 A2 是您要使用的列的第一个单元格。
3. 现在，如果您在单元格中输入无效的 IP 地址，则会弹出一个警告消息框提醒您，如下图所示：
使用 VBA 代码强制使用 IP 地址格式
这里，下面的 VBA 代码也可以帮助只允许输入 IP 地址并限制其他输入，请这样做：
1。 右键单击工作表标签，然后单击 查看代码 从上下文菜单中，在开头 Microsoft Visual Basic应用程序 窗口，将下面的VBA代码复制到其中。
VBA 代码：验证单元格以仅接受 IP 地址
Private Sub Worksheet_Change(ByVal Target As Range)
'Update by ExtendOffice
Dim xArrIp() As String
Dim xIntIP1, xIntIP2, xIntIP3, xIntIP4 As Integer
If Intersect(Target, Range("A2:A10")) Is Nothing Then
Exit Sub
Else
If Target = "" Then
Exit Sub
End If
xArrIp = Split(Target.Text, ".")
If UBound(xArrIp) <> 3 Then
GoTo EIP
Else
xIntIP1 = CInt(xArrIp(0))
xIntIP2 = CInt(xArrIp(1))
xIntIP3 = CInt(xArrIp(2))
xIntIP4 = CInt(xArrIp(3))
If (xIntIP1 < 1) Or (xIntIP1 > 255) _
Or (xIntIP2 < 1) Or (xIntIP2 > 255) _
Or (xIntIP3 < 1) Or (xIntIP3 > 255) _
Or (xIntIP4 < 1) Or (xIntIP4 > 255) Then
GoTo EIP
End If
End If
End If
Exit Sub
EIP:
MsgBox "Please enter correct IP address"
Target = ""
End Sub
 |
|
 |
备注：在上面的代码中， A2：A10 是您只想接受 IP 地址的单元格范围。
2. 然后保存并关闭此代码，现在，只有正确的 IP 地址允许输入到特定单元格中。
使用简单的功能强制仅使用 IP 地址格式
如果你有 Kutools for Excel 安装在您的工作簿中，其 验证IP地址 功能也可以帮助您解决此任务。
安装后 Kutools for Excel，请这样做：
1. 选择您只允许输入 IP 地址的单元格，然后单击 库工具 > 防止打字 > 验证IP地址。 看截图：
2. 应用此功能后，现在只允许输入IP地址，否则会弹出警告信息框提醒，看截图：
4.11 数据验证限制超过总值的值
假设您有一份每月费用报表，预算总额为 $18000，现在，您需要费用清单中的总金额不超过预设总额 $18000，如下图所示。 在这种情况下，您可以使用 SUM 函数创建数据验证规则，以防止值的总和超过预设的总数。
1. 选择要限制值的单元格列表。
2。 然后点击 时间 > 数据验证 > 数据验证，在弹出 数据验证 对话框中的 个人设置 选项卡，请执行以下操作：
- 选择 定制版 来自 让 下拉列表。
- 然后，将下面的公式输入到 公式 文本框。
- =SUM($B$2:$B$7)<=18000
- 点击 OK 按钮关闭此对话框。
备注：在以上公式中， B2：B7 是要限制条目的单元格范围。
3. 现在，当输入范围 B2:B7 中的值时，如果值的总和小于 18000 美元，则验证通过。 如果任何值使总金额超过 18000 美元，则会弹出警告消息框提醒您。
4.12 数据验证限制基于另一个单元格的单元格输入
当您想根据另一个单元格中的值限制单元格列表中的数据条目时，数据验证功能也可以帮助解决此工作。 例如，如果单元格 C1 是文本“是”，则范围 A2:A9 允许输入任何内容，但是，如果单元格 C1 是另一个文本，则不允许在范围 A2:A9 中输入任何内容，如下图所示：
 |
|
 |
要解决此问题，请执行以下操作：
1. 选择要限制值的单元格列表。
2。 然后点击 时间 > 数据验证 > 数据验证，在弹出 数据验证 对话框中的 个人设置 选项卡，请执行以下操作：
- 选择 定制版 来自 让 下拉列表。
- 然后，将下面的公式输入到 公式 文本框。
- =$C$1="Yes"
- 点击 OK 按钮关闭此对话框。
备注：在以上公式中， C1 是单元格包含您要使用的特定文本，并且文本“是” 是您想要限制单元格的文本，请根据您的需要进行更改。
3. 现在，如果单元格 C1 有文本“是”，则可以在 A2:A9 范围内输入任何内容，如果单元格 C1 有其他文本，您将无法输入任何值，请参见下面的演示：
4.13 数据验证只允许输入工作日或周末
如果您只需要在单元格列表中输入工作日（从星期一到星期五）或周末（星期六和星期日），则 数据验证 也可以帮助您，请按照以下步骤操作：
1. 选择要在其中输入工作日或工作日的单元格列表。
2。 然后点击 时间 > 数据验证 > 数据验证，在弹出 数据验证 对话框中的 个人设置 选项卡，请执行以下操作：
- 选择 定制版 来自 让 下拉列表。
- 然后，将以下公式之一输入 公式 根据需要使用文本框。
- =WEEKDAY(A2,2)<6 (allow only weekdays)
=WEEKDAY(A2,2)>5 (allow only weekends) - 点击 OK 按钮关闭此对话框。
备注：在以上公式中， A2 是您要使用的列的第一个单元格。
3. 现在，您只能根据需要在特定单元格中输入工作日或周末的日期。
4.14 数据验证允许根据今天的日期输入日期
有时，您可能只需要在单元格列表中输入大于或小于今天的日期。 这 数据验证 功能与 今天 功能可以帮你一个忙。 请这样做：
1. 选择您只想输入未来日期（大于今天的日期）的单元格列表。
2。 然后点击 时间 > 数据验证 > 数据验证，在弹出 数据验证 对话框中的 个人设置 选项卡，请执行以下操作：
- 选择 定制版 来自 让 下拉列表。
- 然后，将下面的公式输入到 公式 文本框。
- =A2>Today()
- 点击 OK 按钮关闭此对话框。
备注：在以上公式中， A2 是您要使用的列的第一个单元格。
3. 现在，单元格中只能输入大于今天的日期，否则会弹出警告信息框提醒您，请看截图：
提示：
1. 要允许输入过去日期（日期小于今天），请将以下公式应用到数据验证中：
=A2<Today()
2. 允许输入特定日期范围内的日期，例如未来 30 天内的日期，请在数据验证中输入以下公式：
=AND(A2>TODAY(),A2<=(TODAY()+30))
4.15 数据验证允许根据当前时间输入时间
例如，如果您想根据当前时间验证数据，则只能在单元格中输入当前时间之前或之后的时间。 您可以创建自己的数据验证公式，请执行以下操作：
1. 选择您只想输入当前时间之前或之后的时间的单元格列表。
2。 然后点击 时间 > 数据验证 > 数据验证，在弹出 数据验证 对话框中的 个人设置 选项卡，请执行以下操作：
- 选择 时间 来自 让 下拉列表。
- 然后选择 少于 只允许当前时间之前的时间，或 比...更棒 允许您根据需要在当前时间之后的时间 时间 落下。
- 然后，在 结束时间 or 开始时间 框中，输入以下公式：
- =TIME(HOUR(NOW()),MINUTE(NOW()),SECOND(NOW()))
- 点击 OK 按钮关闭此对话框。
备注：在以上公式中， A2 是您要使用的列的第一个单元格。
3. 现在，只能将当前时间之前或之后的时间输入到特定单元格中。
4.16 数据验证特定或当前年份的日期
要仅允许输入特定年份或当前年份的日期，您可以使用基于 YEAR 函数的自定义公式进行数据验证。
1. 选择您只想输入某一年的日期的单元格列表。
2。 然后点击 时间 > 数据验证 > 数据验证，在弹出 数据验证 对话框中的 个人设置 选项卡，请执行以下操作：
- 选择 定制版 来自 让 下拉列表。
- 然后，将下面的公式输入到 公式 文本框。
- =YEAR(A2)=2020
- 点击 OK 按钮关闭此对话框。
备注：在以上公式中， A2 是您要使用的列的第一个单元格， 2020 是您要限制的年份编号。
3. 然后，只能输入 2020 年的日期，否则，将弹出警告消息框，如下图所示：
提示：
要仅允许当前年份中的日期，您可以将以下公式应用于数据验证：
=YEAR(A2)=YEAR(TODAY())
4.17 数据验证当前周或月的日期
如果您希望用户能够在特定单元格中输入当前周或月的日期，本节将介绍一些用于在 Excel 中处理此任务的公式。
允许输入当前周的日期
1. 选择您只想输入当前周日期的单元格列表。
2。 然后点击 时间 > 数据验证 > 数据验证，在弹出 数据验证 对话框中的 个人设置 选项卡，请执行以下操作：
- 选择 日期 来自 让 下拉列表。
- 然后选择 之间 来自 时间 落下。
- 在 起租日期 文本框中，输入以下公式： =今天（）-工作日（今天（），3）
- 在 结束日期 文本框中，输入以下公式： =今天()-工作日(今天(),3)+6
- 最后点击 OK 按钮。
3. 然后，只能输入当前周内的日期，其他日期将被阻止，如下图所示：
允许输入当月的日期
要只允许输入当月的日期，请执行以下操作：
1. 选择您只想输入当月日期的单元格列表。
2。 然后点击 时间 > 数据验证 > 数据验证，在弹出 数据验证 对话框中的 个人设置 选项卡，请执行以下操作：
- 选择 日期 来自 让 下拉列表。
- 然后，从以下选项中进行选择 时间 落下。
- 在 起租日期 文本框中，输入以下公式： =日期（年（今天（）），月份（今天（）），1）
- 在 结束日期 文本框中，输入以下公式： =日期(年(今天()),月(今天()),日(日期(年(今天()),月(今天())+1,1)-1))
- 最后点击 OK 按钮。
3. 从现在开始，只有当月的日期允许输入到选定的单元格中。
5.如何在Excel中编辑数据验证？
要编辑或更改现有的数据验证规则，请按照以下步骤操作：
1. 选择具有数据有效性规则的任一单元格。
2。 然后，点击 时间 > 数据验证 > 数据验证 去 数据验证 对话框，在框中，根据需要编辑或更改规则，然后选中 将这些更改应用到具有相同设置的所有其他单元格 选项将此新规则应用于具有原始验证标准的所有其他单元格。 看截图：
3。 点击 OK 保存更改。
6.如何在Excel中查找和选择带有数据验证的单元格？
如果您在工作表中创建了多个数据验证规则，现在，您需要查找并选择应用数据验证规则的单元格， 定位条件 命令可以帮助您选择各种数据验证或特定类型的数据验证。
1. 激活要查找的工作表并选择具有数据验证的单元格。
2。 然后，点击 主页 > 查找和选择 > 定位条件，请参见屏幕截图：
3。 在 定位条件 对话框中选择 数据验证 > 所有类型，请参见屏幕截图：
4. 并且在当前工作表中一次选择了所有具有数据验证的单元格。
Tips: 如果您只想选择一种特定类型的数据验证，请首先选择一个包含您要查找的特定数据验证的单元格，然后转到 定位条件 对话框，然后选择 数据验证 > 同.
 |
|
 |
7.如何将数据验证规则复制到其他单元格？
假设您已经为一个单元格列表创建了一个数据验证规则，现在您需要将相同的数据验证规则应用于其他单元格。 无需再次创建规则，您可以快速轻松地将现有规则复制并粘贴到其他单元格。
1. 单击以选择具有要使用的验证规则的单元格，然后按 按Ctrl + C 复制它。
2. 然后，选择要验证的单元格，要选择多个不相邻的单元格，按住 按Ctrl 键，同时选择单元格。
3. 然后，右键单击选择，选择 选择性粘贴 选项，请参见屏幕截图：
4。 在 选择性粘贴 对话框中选择 验证 选项，请参见屏幕截图：
5。 点击 OK 按钮，现在验证规则被复制到新单元格。
8.如何使用数据验证在Excel中圈出无效条目？
有时，您可能需要为现有数据创建数据验证规则，在这种情况下，单元格范围内可能会出现一些无效数据。 如何检查无效数据并修改它们？ 在 Excel 中，您可以使用 圈出无效数据 用红色圆圈突出显示无效数据的功能。
要圈出您需要的无效数据，您应该应用 数据验证 功能来设置数据范围的规则。 请按照以下步骤操作：
1. 选择要圈出无效数据的数据范围。
2。 然后，点击 时间 > 数据验证 > 数据验证在 数据验证 对话框，根据需要设置验证规则，例如，这里，我将验证大于 500 的值，请参见截图：
3。 然后，点击 OK 关闭对话框。 设置好数据验证规则后，点击 时间 > 数据验证 > 圈出无效数据，则所有小于 500 的无效值已用红色椭圆圈起来。 看截图：
 |
|
 |
笔记：
- 1.一旦您更正无效数据，红圈将自动消失。
- 2。 此 圈出无效数据 功能最多只能圈出255个单元格。 保存当前工作簿时，所有红色圆圈都将被删除。
- 3. 这些圆圈不可打印。
- 4.您也可以通过单击删除红色圆圈 时间 > 数据验证 > 清除验证圈.
9.如何去除Excel中的数据验证？
要从一系列单元格、当前工作表或整个工作簿中删除数据验证规则，以下方法可以帮到您。
使用数据验证功能删除选定范围内的数据验证
1. 选择要删除的具有数据验证的单元格。
2。 然后点击 时间 > 数据验证 > 数据验证, 在弹出的对话框中，在 个人设置 标签，点击 全部清除 按钮，请参见屏幕截图：
3。 然后，点击 OK 按钮关闭此对话框。 并且立即删除了应用于所选范围的数据验证规则。
Tips: 要从当前工作表中删除数据验证，请先选择整个工作表，然后应用上述步骤。
使用方便的功能删除选定范围内的数据验证
如果你有 Kutools for Excel，它的 清除数据验证限制 功能还可以帮助从选定范围或整个工作表中删除数据验证规则。
安装后 Kutools for Excel，请这样做：
1. 选择包含要删除的数据验证的单元格范围或整个工作表。
2。 然后，点击 库工具 > 防止打字 > 清除数据验证限制，请参见屏幕截图：
3. 在弹出的提示框中，点击 OK，并且已根据需要清除数据验证规则。
使用 VBA 代码从所有工作表中删除数据验证
要从整个工作簿中删除数据验证规则，如果工作表很多，上述方法会很耗时，这里，下面的代码可以帮助您快速处理此任务。
1。 按住 ALT + F11 键打开 Microsoft Visual Basic应用程序 窗口。
2。 然后，点击 插页 > 模块，然后将以下宏粘贴到 模块 窗口。
VBA 代码：删除所有工作表中的数据验证规则：
Sub RemoveDataValidation()
'Updateby Extendoffice
Dim xwsh As Worksheet
For Each xwsh In ActiveWorkbook.Worksheets
xwsh.Cells.Validation.Delete
Next xwsh
End Sub
3。 然后按 F5 键运行此代码，所有数据验证规则已立即从整个工作簿中删除。
- 超级公式栏 （轻松编辑多行文本和公式）； 阅读视图 （轻松读取和编辑大量单元格）； 粘贴到过滤范围...
- 合并单元格/行/列 和保存数据； 拆分单元格内容； 合并重复的行和总和/平均值...防止细胞重复； 比较范围...
- 选择重复或唯一 行; 选择空白行 （所有单元格都是空的）； 超级查找和模糊查找 在许多工作簿中； 随机选择...
- 确切的副本 多个单元格，无需更改公式参考； 自动创建参考 到多张纸； 插入项目符号，复选框等...
- 收藏并快速插入公式，范围，图表和图片； 加密单元 带密码 创建邮件列表 并发送电子邮件...
- 提取文字，添加文本，按位置删除， 删除空间; 创建和打印分页小计； 在单元格内容和注释之间转换...
- 超级筛选 （将过滤方案保存并应用于其他工作表）； 高级排序 按月/周/日，频率及更多； 特殊过滤器 用粗体，斜体...
- 结合工作簿和工作表; 根据关键列合并表； 将数据分割成多个工作表; 批量转换xls，xlsx和PDF...
- 数据透视表分组依据 周号，周几等 显示未锁定的单元格 用不同的颜色 突出显示具有公式/名称的单元格...
- 在Word，Excel，PowerPoint中启用选项卡式编辑和阅读，发布者，Access，Visio和Project。
- 在同一窗口的新选项卡中而不是在新窗口中打开并创建多个文档。
- 每天将您的工作效率提高50％，并减少数百次鼠标单击！
