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. 配置好条件后，您可以切换到“输入信息”或“出错警告”选项卡，按需为验证单元格设置输入提示或错误警告。（如无需设置警告，请直接单击“确定”完成。）
3.1）添加输入信息（可选）：
您可以设置一条提示消息，在用户选中包含数据验证的单元格时自动显示，提醒用户该单元格允许输入的内容。
转到“输入信息”选项卡并执行以下操作：
 |
|
现在，当您选中已验证的单元格时，将显示如下消息框：
3.2）创建有意义的错误消息（可选）：
除了创建输入提示外，当用户在包含数据验证的单元格中输入无效数据时，您还可以显示错误警告。
在“数据验证”对话框的“出错警告”选项卡中，请执行以下操作：
 |
|
当输入无效值时，将弹出警告框，如下图所示：
“停止”选项：单击“重试”以重新输入数值，或单击“取消”放弃输入。
“警告”选项：单击“是”以接受无效输入，单击“否”以修改输入，或单击“取消”以放弃输入。
“信息”选项：单击“确定”以接受无效输入，或单击“取消”放弃输入。
注意：如果您未在“出错警告”框中设置自定义消息，则会显示默认的“停止”警告框，如下图所示：
3. 数据验证基础示例
使用数据验证功能时，系统提供 8 种内置选项供您设置数据验证规则，例如：任何值、整数、小数、日期、时间、序列、文本长度以及自定义公式。本节将介绍如何在 Excel 中使用部分内置选项。
3.1 整数和小数的数据验证
1. 选择您希望仅允许输入整数或小数的单元格区域，然后单击“数据”>“数据验证”>“数据验证”。
2. 在“数据验证”对话框的“设置”选项卡中，请执行以下操作：
- 在“允许”下拉框中选择“整数”或“小数”。
- 然后，在“数据”框中选择一个条件（本例中选择“介于”选项）。
- 提示：条件包括：介于、不介于、等于、不等于、大于、小于、大于等于、小于等于。
- 接下来，请输入所需的“最小值”和“最大值”（本例中为介于 0 到 100 之间的数字）。
- 最后，单击“确定”按钮。
3. 现在，所选单元格仅允许输入 0 到 100 之间的整数。
3.2 日期和时间的数据验证
若要验证输入特定的日期或时间，使用“数据验证”功能非常简单，请按以下步骤操作：
1. 选择您希望仅允许输入特定日期或时间的单元格区域，然后单击“数据”>“数据验证”>“数据验证”。
2. 在“数据验证”对话框中，切换至“设置”选项卡，并执行以下操作：
- 在“允许”下拉框中选择“日期”或“时间”。
- 然后，在“数据”框中选择一个条件（此处我选择了“大于”选项）。
- 提示：条件包括：介于、不介于、等于、不等于、大于、小于、大于等于、小于等于。
- 接下来，输入所需的“开始日期”（日期需晚于）8/20/2021）。
- 最后，单击“确定”按钮。
3. 现在，所选单元格中仅允许输入大于 8/20/2021 的日期。
3.3 文本长度的数据验证
如果您希望限制单元格中可输入的字符数量——例如将特定区域的内容限定为不超过 10 个字符，“数据验证”功能也能轻松帮您实现。
1. 选择您希望限制文本长度的单元格区域，然后依次单击“数据”>“数据验证”>“数据验证”。
2. 在“数据验证”对话框中，切换至“设置”选项卡，并执行以下操作：
- 从“允许”下拉框中选择“文本长度”。
- 然后，在“数据”框中选择一个条件（本例中，我选择了“小于”选项）。
- 提示：条件包括：介于、不介于、等于、不等于、大于、小于、大于等于、小于等于。
- 接下来，请输入“最大值”（文本长度不得超过 10 个字符）。
- 最后，单击“确定”按钮。
3. 现在，所选单元格仅允许输入少于 10 个字符的文本字符串。
3.4 数据验证序列（下拉列表）
借助这一强大的“数据验证”功能，您还能快速、轻松地在单元格中创建下拉列表。请按以下步骤操作：
1. 请选择要插入下拉列表的目标单元格，然后依次单击“数据”>“数据验证”>“数据验证”。
2. 在“数据验证”对话框中，切换至“设置”选项卡，并执行以下操作：
- 从“允许”下拉列表中选择“序列”。
- 在“来源”文本框中，直接输入以逗号分隔的选项列表。例如，若要将用户输入限制为三个选项，请键入 Not Started， In Progress， Completed；您也可以选择包含这些值的单元格区域，以此创建下拉列表。
- 最后，单击“确定”按钮。
3. 现在，下拉列表已按如下截图所示创建到单元格中：
4. 数据验证的高级自定义规则
本节将介绍如何创建高级自定义数据验证规则，以应对多种场景需求，例如：设置验证公式，仅允许输入数字或文本字符串、仅接受唯一值、或限定输入特定格式的电话号码、邮箱地址等。
4.1 仅允许输入数字或文本的数据验证
使用数据验证功能仅允许输入数字
若要在单元格区域中仅允许输入数字，请按以下步骤操作：
1. 选择仅允许输入数字的单元格区域。
2. 单击“数据”>“数据验证”>“数据验证”。在弹出的“数据验证”对话框中，切换至“设置”选项卡，然后执行以下操作：
- 从“允许”下拉列表中选择“自定义”。
- 然后，在“公式”文本框中输入以下公式（其中“A2”是您要限制的选择区域的首个单元格）。
=ISNUMBER(A2) - 单击“确定”按钮，即可关闭此对话框。
3. 从现在起，所选单元格仅允许输入数字。
注意：此“ISNUMBER”函数允许在已验证的单元格中输入任意数值，包括整数、小数、分数、日期和时间。
使用数据验证功能仅允许输入文本字符串
若要将单元格输入限制为仅文本，您可以使用“数据验证”功能，并基于“ISTEXT”函数创建自定义公式，请按以下操作：
1. 请选择您希望仅允许输入文本字符串的单元格区域。
2. 单击“数据”>“数据验证”>“数据验证”。在弹出的“数据验证”对话框中，切换到“设置”选项卡，然后执行以下操作：
- 从“允许”下拉列表中选择“自定义”。
- 然后，在“公式”文本框中输入以下公式（其中“A2”为所选区域的第一个单元格）。
=ISTEXT(A2) - 单击“确定”按钮，即可关闭此对话框。
3. 现在，向指定单元格输入数据时，仅允许输入文本格式的内容。
4.2 数据验证仅允许字母数字值
出于特定需求，您可能希望仅允许输入字母和数字，同时禁止特殊字符（如 ~、%、$ 或空格）。本节为您介绍几种实用方法。
使用数据验证功能仅允许输入字母数字值
为防止输入特殊字符并仅允许字母数字值，请按照以下步骤在“数据验证”功能中创建自定义公式：
1. 请选择一个单元格区域，仅允许在其中输入字母数字值。
2. 单击“数据”>“数据验证”>“数据验证”，在弹出的“数据验证”对话框中切换至“设置”选项卡，然后执行以下操作：
- 从“允许”下拉列表中选择“自定义”。
- 然后，在“公式”文本框中输入以下公式：
=IF(A2="",TRUE,IF(ISERROR(SUMPRODUCT(SEARCH(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1),"0123456789abcdefghijklmnopqrstuvwxyz"))),FALSE,TRUE)) - 单击“确定”按钮，即可关闭此对话框。
注意：在上述公式中，“A2”是您要限制的选择区域中的首个单元格。
3. 现在仅允许输入字母和数字，输入特殊字符时将受到限制，如下图所示：
使用一项强大功能仅允许字母数字值
上述公式可能较为复杂，不易理解和记忆。为此，我向您推荐“Kutools for Excel”中的一项便捷功能——“限制输入”，它能显著简化这一任务。
1. 选择一个仅允许输入字母数字值的单元格区域。
2. 随后，单击“Kutools”>“限制输入”>“限制输入”，如下图所示：
3. 在弹出的“限制输入”对话框中，勾选“禁止输入特殊字符”选项，如下图所示：
4. 然后，单击“确定”按钮，并在随后弹出的提示框中依次点击“是” > “确定”以完成设置。此时，所选单元格将仅允许输入字母和数字，如下图所示：
Kutools for Excel——通过 300 多款必备工具全面增强 Excel 功能，助您工作更快速、更轻松，并借助 AI 功能实现更智能的数据处理与高效办公！立即获取
4.3 数据验证允许文本以特定字符开头或结尾
若需确保某个范围内的所有值均以特定字符或子字符串开头或结尾，可使用基于 EXACT、LEFT、RIGHT 或 COUNTIF 函数的自定义公式来设置数据验证。
仅使用一个条件，允许文本以特定字符开头或结尾
例如，如果您希望特定单元格中的文本条目以“CN”开头或结尾，请按以下步骤操作：
1. 选择一个单元格区域，仅允许输入以特定字符开头或结尾的文本。
2. 接着，单击“数据”>“数据验证”>“数据验证”，在弹出的“数据验证”对话框中切换至“设置”选项卡，并执行以下操作：
- 从“允许”下拉列表中选择“自定义”。
- 然后，在“公式”文本框中输入以下公式。
仅允许输入以 CN 开头的文本：
=EXACT(LEFT(A2,2),"CN")仅允许输入以 CN 结尾的文本：=EXACT(RIGHT(A2,2),"CN") - 单击“确定”按钮，即可关闭此对话框。
注意：在上述公式中，“A2”是所选区域中的第一个单元格，数字“2”为您指定的字符数，“CN”是您希望添加到文本开头或结尾的内容。
3. 从现在起，仅允许输入以指定字符开头或结尾的文本字符串到所选单元格中；否则，系统将弹出如下图所示的警告提示：
提示：上述公式区分大小写，如果您不需要区分大小写，请使用以下 COUNTIF 公式：
=COUNTIF(A2,"CN*")=COUNTIF(A2,"*CN")注意：星号 * 为通配符，可匹配一个或多个字符。
使用多个条件（OR 逻辑），允许文本以特定字符开头或结尾
例如，如果您希望文本条目以“CN”或“UK”开头或结尾（如下图所示），请使用加号（+）添加另一个 EXACT 实例。操作步骤如下：
1. 选择一个单元格区域，仅支持以多条件开头或结尾的文本。
2. 随后，依次单击“数据”>“数据验证”>“数据验证”，在弹出的“数据验证”对话框中切换至“设置”选项卡，并执行以下操作：
- 从“允许”下拉列表中选择“自定义”。
- 然后，在“公式”文本框中输入以下公式。仅允许输入以 CN 或 UK 开头的文本：
=EXACT(LEFT(A2,2),"CN")+EXACT(LEFT(A2,2),"UK")仅允许输入以 CN 或 UK 结尾的文本：=EXACT(RIGHT(A2,2),"CN")+EXACT(RIGHT(A2,2),"UK") - 单击“确定”按钮，即可关闭此对话框。
注意：在上述公式中，“A2”是所选区域中的第一个单元格，数字“2”为您指定的字符数，“CN”和“UK”则是您希望添加到文本开头或结尾的特定内容。
3. 现在，仅允许将指定字符开头或结尾的文本字符串输入到所选单元格中。
提示：若要忽略大小写，请使用以下 COUNTIF 公式：
=COUNTIF(A2,"CN*")+COUNTIF(A2,"UK*")=COUNTIF(A2,"*CN")+COUNTIF(A2,"*UK")注意：星号 * 是通配符，可匹配一个或多个字符。
4.4 数据验证允许条目必须包含/不得包含特定文本
本节将介绍如何在 Excel 中应用数据验证，以确保输入内容必须包含（或不得包含）某个特定子字符串，或多个子字符串中的任意一个。
允许输入的内容必须包含一个或多个指定文本之一
允许条目必须包含一个特定文本
若要允许仅包含特定文本字符串的条目（例如，所有输入值都必须包含“KTE”文本，如下图所示），您可以结合使用 FIND 和 ISNUMBER 函数创建自定义公式，以应用数据验证。请按以下步骤操作：
1. 选择一个单元格区域，仅包含特定文本的单元格。
2. 随后，单击“数据”>“数据验证”>“数据验证”，在弹出的“数据验证”对话框中切换至“设置”选项卡，并执行以下操作：
- 从“允许”下拉列表中选择“自定义”。
- 然后，根据需要在“公式”文本框中输入以下任一公式：区分大小写：
=ISNUMBER(FIND("KTE",A2))不区分大小写：=ISNUMBER(SEARCH("KTE",A2)) - 单击“确定”按钮，即可关闭此对话框。
注意：在上述公式中，“A2”是所选区域中的第一个单元格，而文本“KTE”则是条目必须包含的文本字符串。
3. 现在，当输入值不包含所需文本时，系统将弹出警告提示框。
允许条目必须包含多个特定文本之一
上述公式仅适用于单个文本字符串。若需匹配多个文本字符串中的任意一个（如下图所示），请结合使用 SUMPRODUCT、FIND 和 ISNUMBER 函数来构建公式。
1. 选择一个单元格区域，其中仅包含多个项目中的任意一项文本。
2. 然后依次单击“数据”>“数据验证”>“数据验证”，在弹出的“数据验证”对话框中，切换到“设置”选项卡，并执行以下操作：
- 从“允许”下拉列表中选择“自定义”。
- 然后，根据需要在“公式”文本框中输入以下任一公式：区分大小写：
=SUMPRODUCT(--ISNUMBER(FIND($C$2:$C$4,A2)))>0非区分大小写：=SUMPRODUCT(--ISNUMBER(SEARCH($C$2:$C$4,A2)))>0 - 然后，单击“确定”即可关闭对话框。
注意：在上述公式中，“A2”是所选区域中的第一个单元格，“C2:C4”是您希望条目包含其中任意一个值的列表。
3. 现在，仅允许输入特定列表中的任一值。
允许输入的内容不得包含一个或多个指定文本之一
允许条目不得包含一个特定文本
若要确保条目不包含特定文本（例如，禁止单元格值中出现“KTE”），您可结合 ISERROR 与 FIND 函数创建数据验证规则。请按以下步骤操作：
1. 选择一个单元格区域，仅允许输入不包含特定文本的内容。
2. 随后，单击“数据”>“数据验证”>“数据验证”，在弹出的“数据验证”对话框中切换至“设置”选项卡，并执行以下操作：
- 从“允许”下拉列表中选择“自定义”。
- 然后，在“公式”文本框中输入以下任一公式。区分大小写：
=ISERROR(FIND("KTE",A2))不区分大小写：=ISERROR(SEARCH("KTE",A2)) - 单击“确定”按钮，即可关闭此对话框。
注意：在上述公式中，“A2”是所选区域中的首个单元格，而文本“KTE”则是条目中不得包含的字符串。
3. 现在，包含特定文本的条目将无法输入。
允许条目不得包含多个特定文本之一
若要防止列表中的任意一个文本字符串被输入（如下图所示），请按以下步骤操作：
1. 选择一个单元格区域，您希望阻止某些文本的输入。
2. 随后，依次单击“数据”>“数据验证”>“数据验证”，在弹出的“数据验证”对话框中切换至“设置”选项卡，并执行以下操作：
- 从“允许”下拉列表中选择“自定义”。
- 然后，在“公式”文本框中输入以下公式。区分大小写：
=SUMPRODUCT(--ISNUMBER(FIND($C$2:$C$4,A2)))=0非区分大小写：=SUMPRODUCT(--ISNUMBER(SEARCH($C$2:$C$4,A2)))=0 - 然后，单击“确定”即可关闭对话框。
注意：在上述公式中，“A2”是所选区域中的第一个单元格，而“C2:C4”是您希望阻止条目包含其中任意值的列表。
3. 从现在起，任何包含特定文本的条目都将无法输入。
4.5 数据验证仅允许唯一值
如果您希望防止在单元格区域中输入重复数据，本节将为您介绍几种在 Excel 中快速实现这一目标的方法。
使用数据验证功能仅允许输入唯一值
通常，基于 COUNTIF 函数的自定义公式配合“数据验证”功能即可实现，请按以下步骤操作：
1. 请选择您希望仅输入唯一值的单元格或列。
2. 随后，依次单击“数据”>“数据验证”>“数据验证”，在弹出的“数据验证”对话框中，切换至“设置”选项卡，并执行以下操作：
- 从“允许”下拉列表中选择“自定义”。
- 然后，在“公式”文本框中输入以下公式。
=COUNTIF($A$2:$A$9,A2)=1 - 单击“确定”按钮，即可关闭此对话框。
注意：在上述公式中，“A2:A9”是您希望仅允许输入唯一值的单元格范围，而“A2”则是该区域中的第一个单元格。
3. 现在仅允许输入唯一值；若输入重复数据，系统将弹出如下图所示的警告消息：
使用 VBA 代码仅允许输入唯一值
以下 VBA 代码也可帮助您防止重复项值被输入，请按以下操作：
1. 右键单击您希望仅允许唯一值的工作表标签，从上下文菜单中选择“查看代码”，在弹出的“Microsoft Visual Basic for Applications”窗口中，将以下代码复制并粘贴到空白模块中：
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” 和 “A:A” 是您希望防止重复项的列范围，请根据实际需求进行调整。
2. 然后保存并关闭此代码。此后，当在单元格 A1:A100 中输入重复值时，系统将弹出如下方截图所示的警告提示框：
通过一项便捷的功能仅允许输入唯一值
若您已安装“Kutools for Excel”，只需轻点几下，即可通过其“防止重复项”功能，快速为单元格区域设置数据验证，有效杜绝重复内容。
1. 选择您希望防止重复值、仅允许唯一数据的单元格区域。
2. 然后，单击“Kutools”>“限制输入”>“防止重复项”，如下图所示：
3. 系统将弹出一条警告消息，提示您启用此功能会删除数据验证。请单击“是”，然后在随后出现的提示框中单击“确定”，如下方截图所示：
 | |  |
4. 现在，当您在指定单元格中输入重复数据时，系统将弹出提示框，提醒您该重复数据无效，如下图所示：
Kutools for Excel——通过 300 多款必备工具全面增强 Excel 功能，助您工作更快速、更轻松，并借助 AI 功能实现更智能的数据处理与高效办公！立即获取
4.6 数据验证仅允许大写 / 小写 / 首字母大写输入
数据验证功能是一项强大工具，可帮助您确保在指定单元格区域中仅输入大写、小写或首字母大写的内容。请按以下步骤操作：
1. 选择您希望仅允许输入大写、小写或首字母大写文本的单元格区域。
2. 随后，依次单击“数据”>“数据验证”>“数据验证”，在弹出的“数据验证”对话框中，切换至“设置”选项卡，并执行以下操作：
- 从“允许”下拉列表中选择“自定义”。
- 然后，根据需要在“公式”文本框中输入以下任一公式：仅允许大写文本：
=AND(EXACT(A2,UPPER(A2)),ISTEXT(A2))仅允许小写文本：=AND(EXACT(A2,LOWER(A2)),ISTEXT(A2))仅允许首字母大写文本：=AND(EXACT(A2,PROPER(A2)),ISTEXT(A2)) - 单击“确定”按钮，即可关闭此对话框。
注：在上述公式中，“A2”是您要使用的列中的首个单元格。
3. 现在，仅符合您所设规则的条目才会被接受。
4.7 数据验证允许/禁止输入另一列表中存在的值
根据值是否存在于另一列表中来允许或阻止输入，对许多用户来说可能颇具挑战性。实际上，您只需结合使用 COUNTIF 函数与数据验证功能，配合一个简单公式，即可轻松完成此任务。
例如，我希望仅允许在单元格区域中输入 C2:C4 范围内的值（如下方截图所示）。要实现此限制，请按以下步骤操作：
1. 请选择您要应用数据验证的单元格区域。
2. 然后依次单击“数据”>“数据验证”>“数据验证”，在弹出的“数据验证”对话框中，切换至“设置”选项卡，并执行以下操作：
- 从“允许”下拉列表中选择“自定义”。
- 然后，根据需要在“公式”文本框中输入以下任一公式：仅允许输入另一列中存在的值
=COUNTIF($C$2:$C$4,A2)>0禁止输入另一列中已存在的值=COUNTIF($C$2:$C$4,A2)=0 - 单击“确定”按钮，即可关闭此对话框。
注：在上述公式中，“A2”是您要验证的列中的首个单元格，“C2:C4”是您希望允许或阻止输入的值列表。
3. 现在，仅允许输入符合您所设规则的条目，其余内容将被自动拦截。
4.8 数据验证强制仅允许输入电话号码格式
当您录入公司员工信息时，若某一列需填写电话号码，可通过设置数据验证来确保快速、准确地输入。例如，您可能希望仅允许在工作表中输入格式为（123) 456-7890 的电话号码。本节将为您介绍两种高效技巧，轻松实现这一目标。
通过数据验证功能强制仅允许电话号码格式
若要仅允许输入特定的电话号码格式，请按以下步骤操作：
1. 请选择您希望应用特定电话号码格式的单元格区域，然后右键单击，在上下文菜单中选择“设置单元格格式”，如下图所示：
2. 在“设置单元格格式”对话框中，切换到“数字”选项卡，于左侧“分类”列表中选择“自定义”，然后在“类型”文本框内输入您所需的电话号码格式。例如，可使用“（###） ###-####”格式，如下图所示：
3. 然后，单击“确定”以关闭对话框。
4. 设置单元格格式后，再次选中这些单元格，单击“数据”>“数据验证”>“数据验证”，打开“数据验证”对话框。在弹出的对话框中，切换到“设置”选项卡，并执行以下操作：
- 从“允许”下拉列表中选择“自定义”。
- 然后，在“公式”文本框中输入以下公式：
=AND(ISNUMBER(A2),LEN(A2)=10) - 单击“确定”按钮，即可关闭此对话框。
注：在上述公式中，“A2”是您用于验证电话号码的列中的首个单元格。
5. 现在，输入 10 位数字后，系统将自动将其转换为您所需的特定电话号码格式，参见截图：
 | |  |
注：如果输入的数字不是 10 位，将弹出警告消息框提醒您，参见截图：
通过实用功能强制仅允许电话号码格式
“Kutools for Excel”的“仅能输入电话号码”功能可助您仅需几次单击，即可轻松强制限定输入内容为电话号码格式。
1. 请选择仅允许输入特定电话号码的单元格区域，然后单击“Kutools”>“限制输入”>“仅能输入电话号码”，如下图所示：
2. 在“电话号码”对话框中，选择您所需的电话号码格式，或单击“添加”按钮创建自定义格式，参见截图：
3. 选择或设置好电话号码格式后，单击“确定”。此后，仅允许输入符合该特定格式的电话号码，否则将弹出警告消息提醒您，参见截图：
Kutools for Excel——通过 300 多款必备工具全面增强 Excel 功能，助您工作更快速、更轻松，并借助 AI 功能实现更智能的数据处理与高效办公！立即获取
4.9 数据验证强制仅允许输入邮箱地址
假设您需要在工作表的某一列中输入多个邮箱地址，为避免误输无效格式，可设置数据验证规则，仅允许符合邮箱地址格式的内容。
通过数据验证功能强制仅允许邮箱地址格式
通过结合自定义公式的数据验证功能，您可以快速创建规则，有效防止输入无效的邮箱地址。请按以下步骤操作：
1. 选中仅允许输入邮箱地址的单元格，然后依次点击“数据”>“数据验证”>“数据验证”。
2. 在弹出的“数据验证”对话框中，切换至“设置”选项卡，并执行以下操作：
- 从“允许”下拉列表中选择“自定义”。
- 然后，在“公式”文本框中输入以下公式：
=ISNUMBER(MATCH("*@*.?*",A2,0)) - 单击“确定”按钮，即可关闭此对话框。
注：在上述公式中，“A2”是您要使用的列中的首个单元格。
3. 现在，若输入的文本不符合邮箱地址格式，系统将弹出警告消息框提醒您，如截图所示：
通过便捷功能强制仅允许邮箱地址格式
“Kutools for Excel”提供了一项出色的功能——“仅允许输入邮箱地址”。借助此工具，您只需单击一次，即可有效阻止无效邮箱地址的输入。
1. 选择仅允许输入邮箱地址的单元格，然后单击“Kutools”>“限制输入”>“仅能输入邮箱”。参见截图：
2. 此后，仅允许输入邮箱地址格式，否则将弹出警告消息框提醒您，参见截图：
Kutools for Excel——通过 300 多款必备工具全面增强 Excel 功能，助您工作更快速、更轻松，并借助 AI 功能实现更智能的数据处理与高效办公！立即获取
4.10 数据验证强制仅允许输入 IP 地址
本节将为您介绍一些快速技巧，助您轻松设置数据验证，确保单元格区域仅接受 IP 地址。
使用数据验证功能强制仅允许输入 IP 地址格式
若要在特定单元格区域中仅允许输入 IP 地址，请按以下步骤操作：
1. 请选择仅允许输入 IP 地址的单元格，然后依次单击“数据”>“数据验证”>“数据验证”。
2. 在弹出的“数据验证”对话框中，切换至“设置”选项卡，并执行以下操作：
- 从“允许”下拉列表中选择“自定义”。
- 然后，在“公式”文本框中输入以下公式：
=AND((LEN(A2)-LEN(SUBSTITUTE(A2,".","")))=3,ISNUMBER(SUBSTITUTE(A2,".","")+0)) - 单击“确定”按钮，即可关闭此对话框。
注：在上述公式中，“A2”是您要使用的列中的首个单元格。
3. 现在，若在单元格中输入了无效的 IP 地址，系统将弹出如下方截图所示的警告消息框：
使用 VBA 代码强制仅允许输入 IP 地址格式
此外，以下 VBA 代码也可帮助仅允许输入 IP 地址并限制其他输入，请按以下方式操作：
1. 右键单击工作表标签，从上下文菜单中选择“查看代码”，在弹出的“Microsoft Visual Basic for Applications”窗口中，将下方的 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 地址”功能同样能助您轻松完成此任务。
1. 请选择仅允许输入 IP 地址的单元格，然后单击“Kutools”>“限制输入”>“仅能输入 IP 地址”。参见截图：
2. 应用此功能后，仅允许输入 IP 地址，否则将弹出警告消息框提醒您，如下图所示：
Kutools for Excel——通过 300 多款必备工具全面增强 Excel 功能，助您工作更快速、更轻松，并借助 AI 功能实现更智能的数据处理与高效办公！立即获取
4.11 数据验证限制超过总值的输入值
假设您有一份月度支出报告，预算总额为 $18,000. 为确保支出列表中的总金额不超过该预设上限（如下图所示），您可以使用 SUM 函数创建数据验证规则，有效防止录入数值的总和超出预算总额。
1. 请选择您希望限制输入值的单元格区域。
2. 随后，依次点击“数据”>“数据验证”>“数据验证”，在弹出的“数据验证”对话框中，切换至“设置”选项卡，并执行以下操作：
- 从“允许”下拉列表中选择“自定义”。
- 然后在“公式”文本框中输入以下公式。
=SUM($B$2:$B$7)<=18000 - 单击“确定”按钮，即可关闭此对话框。
注意：在上述公式中，“B2:B7”为您要限制输入的单元格区域。
3. 现在，在 B2:B7 范围内输入数值时，若总和不超过 $18,000，验证将通过；一旦输入导致总和超过 $18,000，系统将立即弹出警告消息框提醒您。
4.12 数据验证根据另一单元格限制单元格输入
当您希望根据另一单元格的值来限制某组单元格的数据输入时，数据验证功能也能轻松帮您实现。例如，若 C1 单元格内容为“Yes”，则 A2:A9 区域可自由输入；而当 C1 包含其他文本时，A2:A9 的输入将受到限制，如下图所示：
 | |  |
要实现此功能，请按以下步骤操作：
1. 请选择您希望限制输入值的单元格区域。
2. 随后，单击“数据”>“数据验证”>“数据验证”，在弹出的“数据验证”对话框中切换至“设置”选项卡，并执行以下操作：
- 从“允许”下拉列表中选择“自定义”。
- 然后，在“公式”文本框中输入以下公式。
=$C$1="Yes" - 单击“确定”按钮，即可关闭此对话框。
注意：在上述公式中，“C1”是包含您要使用的特定文本的单元格，“Yes”是用于限制单元格输入的文本，请根据实际需求进行修改。
3. 现在，若 C1 单元格包含文本“Yes”，则可在 A2:A9 范围内自由输入任意内容；若 C1 包含其他文本，则无法在该范围内输入 Y 值，请参见下方演示：
4.13 数据验证仅允许输入工作日或周末日期
如果您需要在一组单元格中仅允许输入工作日（周一至周五）或周末（周六和周日）的日期，“数据验证”功能也能帮您实现，请按以下步骤操作：
1. 请选择您希望输入工作日或周末日期的单元格区域。
2. 随后，依次单击“数据”>“数据验证”>“数据验证”，在弹出的“数据验证”对话框中，切换至“设置”选项卡，并执行以下操作：
- 从“允许”下拉列表中选择“自定义”。
- 然后，根据需要在“公式”文本框中输入以下任一公式：仅允许工作日
=WEEKDAY(A2,2)<6仅允许周末=WEEKDAY(A2,2)>5 - 单击“确定”按钮，即可关闭此对话框。
注：在上述公式中，“A2”是您所用列中的首个单元格。
3. 现在，您只能在指定单元格中输入符合所选条件的工作日或周末日期。
4.14 数据验证基于今日日期限制输入日期
有时，您可能希望在一组单元格中仅允许输入早于或晚于今天的日期。“数据验证”功能结合“TODAY”函数即可轻松实现这一需求。请按以下步骤操作：
1. 请选择您希望仅输入未来日期（即晚于今日的日期）的单元格区域。
2. 然后依次单击“数据”>“数据验证”>“数据验证”，在弹出的“数据验证”对话框中，切换至“设置”选项卡，并执行以下操作：
- 从“允许”下拉列表中选择“自定义”。
- 然后，在“公式”文本框中输入以下公式：
=A2>Today() - 单击“确定”按钮，即可关闭此对话框。
注：在上述公式中，“A2”是您要使用的列中的首个单元格。
3. 现在，仅允许在这些单元格中输入晚于今天的日期；否则，系统将弹出警告消息框提醒您，请参见下图：
提示：
1. 若要允许输入早于今日的日期，请在数据验证中应用以下公式：
=A2<Today()2. 若要允许在特定日期范围内输入日期（例如未来 30 天内），请在数据验证中输入以下公式：
=AND(A2>TODAY(),A2<=(TODAY()+30))4.15 数据验证基于当前时间限制输入时间
如果您希望根据当前时间验证数据（例如，仅允许在单元格中输入早于或晚于当前时间的时间），可创建自定义数据验证公式。请按以下步骤操作：
1. 请选择您希望仅输入早于或晚于当前时间的时间值的单元格区域。
2. 随后点击“数据”>“数据验证”>“数据验证”，在弹出的“数据验证”对话框中，切换至“设置”选项卡，并执行以下操作：
- 从“允许”下拉列表中选择“时间”。
- 然后，从“数据”下拉列表中选择“小于”，仅允许输入早于当前时间的时间；或根据需要选择“大于”，以允许输入晚于当前时间的时间。
- 接着，在“结束时间”或“开始时间”框中输入以下公式：
=TIME(HOUR(NOW()),MINUTE(NOW()),SECOND(NOW())) - 单击“确定”按钮，即可关闭此对话框。
注：在上述公式中，“A2”是您所用列中的首个单元格。
3. 现在，仅可向指定单元格中输入早于或晚于当前时间的值。
4.16 数据验证限制输入特定年份或当年的日期
若要仅允许输入某一年或当年的日期，您可以使用基于 YEAR 函数的自定义公式设置数据验证。
1. 请选择您希望仅输入某一年日期的单元格区域。
2. 然后依次单击“数据”>“数据验证”>“数据验证”，在弹出的“数据验证”对话框中，切换至“设置”选项卡，并执行以下操作：
- 从“允许”下拉列表中选择“自定义”。
- 接着，在“公式”文本框中输入以下公式：
=YEAR(A2)=2020 - 单击“确定”按钮，即可关闭此对话框。
注意：在上述公式中，“A2”是您要使用的列的首个单元格，“2020”是您要限定的年份。
3. 此后，仅可输入 2020 年的日期；否则，将弹出如下图所示的警告消息框：
提示：
若要仅允许输入当年的日期，可将以下公式应用于数据验证：
=YEAR(A2)=YEAR(TODAY())4.17 数据验证限制输入本周或本月的日期
如果您希望用户只能在特定单元格中输入本周或本月的日期，本节将为您介绍 Excel 中实现这一限制的实用公式。
允许输入本周日期
1. 请选择您希望仅输入本周日期的单元格区域。
2. 然后依次单击“数据”>“数据验证”>“数据验证”，在弹出的“数据验证”对话框中，切换至“设置”选项卡，并执行以下操作：
- 从“允许”下拉列表中选择“日期”。
- 接着，从“数据”下拉列表中选择“介于”。
- 在“开始日期”文本框中输入此公式：
=TODAY()-WEEKDAY(TODAY(),3) - 在“结束日期”文本框中输入此公式：
=TODAY()-WEEKDAY(TODAY(),3)+6 - 最后，单击“确定”按钮。
3. 此后，仅可输入本周内的日期，其他日期将被禁止，如下图所示：
允许输入本月日期
若要仅允许输入本月的日期，请按以下步骤操作：
1. 请选择您希望仅输入本月日期的单元格区域。
2. 然后依次单击“数据”>“数据验证”>“数据验证”，在弹出的“数据验证”对话框中，切换至“设置”选项卡，并执行以下操作：
- 从“允许”下拉列表中选择“日期”。
- 接着，从“数据”下拉列表中选择“介于”。
- 在“开始日期”文本框中输入此公式：
=DATE(YEAR(TODAY()),MONTH(TODAY()),1) - 在“结束日期”文本框中输入此公式：
=DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(DATE(YEAR(TODAY()),MONTH(TODAY())+1,1)-1)) - 最后，单击“确定”按钮。
3. 从现在起，仅可向所选单元格中输入本月的日期。
5. 如何在 Excel 中编辑数据验证规则？
要编辑或更改现有的数据验证规则，请按以下步骤操作：
1. 请选择任意一个已应用数据验证规则的单元格。
2. 随后，点击“数据”>“数据验证”>“数据验证”，打开“数据验证”对话框，根据需要编辑或修改规则，并勾选“将这些更改应用到所有其他具有相同设置的单元格”选项，即可将新规则同步应用至所有原已设置相同验证条件的单元格。请参见下图：
3. 点击“确定”以保存您的更改。
6. 如何在 Excel 中快速查找并选中所有包含数据验证的单元格？
如果您在工作表中设置了多个数据验证规则，现在需要快速查找并选中所有应用了数据验证的单元格，“定位条件”命令可助您一键选中全部数据验证单元格，或按需指定特定类型的数据验证进行选择。
1. 激活您要查找的工作表，并选择包含数据验证的单元格。
2. 随后点击“开始”>“查找和选择”>“定位条件”，如下图所示：
3. 在“定位条件”对话框中，选择“数据验证”>“全部”，如下图所示：
4. 此时，当前工作表中所有包含数据验证的单元格均已自动选中。
提示：若要选中特定类型的数据验证区域，请先选中一个包含目标数据验证的单元格，然后打开“定位条件”对话框，依次选择“数据验证”>“相同”。
 | |  |
7. 如何将数据验证规则快速复制到其他单元格？
假设您已为一组单元格设置了数据验证规则，现在只需轻松复制并粘贴，即可将相同的规则快速应用到其他单元格，无需重复创建。
1. 点击选择一个包含您所需验证规则的单元格，然后按“Ctrl + C”复制。
2. 然后选择您要验证的单元格；若需选择多个不相邻的单元格，请在点击时按住 Ctrl 键。
3. 然后右键单击所选区域，选择“选择性粘贴”选项，如下图所示：
4. 在“选择性粘贴”对话框中，选择“验证”选项，如下图所示：
5. 点击“确定”按钮，验证规则现已成功复制到新单元格中。
8. 如何在 Excel 中利用数据验证功能圈出无效数据？
有时，您可能需要为已有数据设置数据验证规则，此时单元格区域中可能已存在一些无效数据。如何快速检查并修正这些无效数据？在 Excel 中，您可以使用“圈释无效数据”功能，以红色圆圈醒目地标出所有不符合验证规则的单元格。
要圈出所需的无效数据，请先使用“数据验证”功能为数据区域设置规则。操作步骤如下：
1. 请选择您要圈出无效数据的数据区域。
2. 随后点击“数据”>“数据验证”>“数据验证”，在弹出的“数据验证”对话框中，按需设置验证规则——例如此处我设定了仅允许输入大于 500 的值，请参见下图：
3. 然后点击“确定”关闭对话框。设置好数据验证规则后，依次点击“数据”>“数据验证”>“圈释无效数据”，所有小于 500 的无效值都会被红色椭圆圈出。请参见下图：
 | |  |
注意事项：
- 1. 更正无效数据后，红色圆圈将自动消失。
- 2。“圈出无效数据”功能最多可标记 255 个单元格。保存当前工作簿后，所有红色圆圈将自动清除。
- 3. 这些圆圈无法打印。
- 4. 您也可以通过单击“数据”>“数据验证”>“清除验证圆圈”，轻松移除红色圆圈。
9. 如何在 Excel 中删除数据验证？
要从单元格区域、当前工作表或整个工作簿中移除数据验证规则，请按以下方法操作。
使用数据验证功能移除数据验证选择区域
1. 请选择包含要移除数据验证的单元格。
2. 随后，依次单击“数据”>“数据验证”>“数据验证”，在弹出的对话框中切换至“设置”选项卡，点击“全部清除”按钮，参见截图：
3. 随后单击“确定”按钮关闭此对话框，所选区域的数据验证规则将立即被移除。
提示：若要从当前工作表中移除数据验证，请先全选工作表，再执行上述步骤。
使用便捷功能移除数据验证选择区域
如果您已安装“Kutools for Excel”，其“清除数据验证限制”功能还能帮您轻松移除所选区域或整个工作表中的数据验证规则。
1. 请选择包含需移除数据验证的单元格区域或整个工作表。
2. 然后单击“Kutools”>“限制输入”>“清除数据验证限制”，详见截图：
3. 在弹出的提示框中单击“确定”，即可按需清除数据验证规则。
Kutools for Excel——通过 300 多款必备工具全面增强 Excel 功能，助您工作更快速、更轻松，并借助 AI 功能实现更智能的数据处理与高效办公！立即获取
使用 VBA 代码从所有工作表中移除数据验证
若需从整个工作簿中移除数据验证规则，当工作表数量较多时，上述方法将耗费大量时间。以下代码可助您快速完成此任务。
1. 按下“Alt + F11”组合键，即可打开“Microsoft Visual Basic for Applications”窗口。
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”键运行此代码，即可立即从整个工作簿中移除所有数据验证规则。
最佳办公效率工具
| 🤖 | KUTOOLS AI 助手：基于以下内容革新数据分析：智能执行 | 生成代码| 创建自定义公式 | 数据分析及生成图表| 调用 Kutools Functions…… |
| 热门功能：查找、高亮或标记重复项 | 删除空白行 | 合并列或单元格且不丢失数据 | 不使用公式的四舍五入…… | |
| 高级 LOOKUP：多条件 VLookup | 多值 VLookup | 跨多工作表 VLookup | 模糊查找…… | |
| 高级下拉列表：快速创建下拉列表 | 级联下拉列表 | 多选下拉列表…… | |
| 列管理器：添加指定数量的列|移动列|切换隐藏列的可见性状态|比较区域与列…… | |
| 特色功能：网格聚焦 | 设计视图 |增强编辑栏 | 工作簿和表管理器 | 资源库（自动文本）| 日期提取 | 汇总工作表 | 加密/解密单元格 | 按列表发送邮件 | 超级筛选 | 特殊筛选（筛选粗体单元格/斜体/删除线……） ...... | |
| 精选 15 工具集：12 文本工具（添加文本，删除特定字符，……）| 50+ 图表 类型（甘特图，……）| 40+ 实用公式（基于生日计算年龄，……）| 19 插入工具（插入二维码，从路径插入图片，……）| 12 转换工具（小写金额转大写，汇率转换，……）| 7 合并和拆分工具（高级合并行，分割单元格，……）|……更多 |
使用 Kutools for Excel 大幅提升您的 Excel 技能，体验前所未有的高效。Kutools for Excel 提供 300 多项高级功能，助您提升生产力、节省时间。立即点击此处，获取您最需要的功能……
Office Tab 为 Office 带来标签式界面，让您的工作更轻松
- 在 Word、Excel、PowerPoint、Publisher、Access、Visio 和 Project 中启用标签式编辑和阅读。
- 在同一个窗口的新标签页中打开并创建多个文档，而非在新窗口中。
- 将您的工作效率提升 50%，每天减少数百次鼠标点击！
所有 Kutools 插件，一个安装程序
Kutools for Office 套件捆绑了适用于 Excel、Word、Outlook 和 PowerPoint 的插件以及 Office Tab Pro，非常适合需要跨多个 Office 应用高效协作的团队。
- 一体化套件— Excel、Word、Outlook 和 PowerPoint 插件 + Office Tab Pro
- 一个安装程序，一个许可证— 几分钟内完成设置（支持 MSI）
- 协同效果更佳— 在多个 Office 应用中实现高效协同
- 30 天全功能试用— 无需注册，无需信用卡
- 超值之选— 比单独购买插件更省钱