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. 数据有效性的基本示例
使用数据有效性功能时,Excel 提供了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. 从现在起,只有以指定字符开头或结尾的文本字符串才能输入到选定单元格,否则会弹出警告提示,如下图所示:
提示:上述公式区分大小写,如不需要区分大小写,请使用下方的 CONTIF公式:
=COUNTIF(A2,"CN*")
=COUNTIF(A2,"*CN")
注意:星号 * 是通配符,匹配一个或多个字符。
使用多个条件(或逻辑)允许文本以特定字符开头或结尾
例如,若你希望文本输入以“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.现在,只有以指定字符开头或结尾的文本字符串才能输入到选定单元格。
提示:如需忽略大小写,请使用下方的 CONTIF公式:
=COUNTIF(A2,"CN*")+COUNTIF(A2,"UK*")
=COUNTIF(A2,"*CN")+COUNTIF(A2,"*UK")
注意:星号 * 是通配符,匹配一个或多个字符。
4.4 数据有效性:必须包含/不得包含特定文本
本节将介绍如何应用数据有效性,要求输入值必须包含或不得包含某个特定子串或多个子串。
允许输入内容必须包含一个或多个特定文本
允许输入内容必须包含某个特定文本
若要允许输入内容必须包含某个特定文本字符串,例如所有输入值都应包含“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仅允许唯一值的数据有效性
如需防止在某个单元格区域输入重复数据,本节将介绍几种快速实现的方法。
通过数据有效性功能仅允许唯一值
通常,结合 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 限制输入值不能超过总值的数据有效性
假设你有一份月度支出报表,预算总额为 $18000。你需要确保支出列表中的总金额不超过此预算,如下图所示。此时,可以通过 SUM 函数自定义数据有效性规则,防止总和超出预算。
1.选择你要限制输入值的单元格区域。
2. 然后点击“数据”>“数据有效性”>“数据有效性”,在弹出的“数据有效性”对话框的“设置”选项卡下,执行以下操作:
- 在“允许”下拉列表中选择“自定义”。
- 然后,在“公式”文本框中输入以下公式。
=SUM($B$2:$B$7)<=18000
- 点击“确定”按钮关闭对话框。
注意:上述公式中,“B2:B7”为你要限制输入的单元格区域。
3.现在,在 B2:B7 区域输入数值时,如果总和小于 $18000,则验证通过;若有任一数值导致总和超过 $18000,则会弹出警告提示。
4.12 基于另一个单元格限制输入的数据有效性
当你需要根据另一个单元格的值限制某区域的输入时,数据有效性功能同样可以帮你实现。例如,若 C1 单元格为“是”,A2:A9 区域允许任意输入;若 C1 为其他内容,则 A2:A9 区域禁止输入,如下图所示:
![]() | ![]() | ![]() |
解决方法如下:
1.选择你要限制输入的单元格区域。
2. 然后点击“数据”>“数据有效性”>“数据有效性”,在弹出的“数据有效性”对话框的“设置”选项卡下,执行以下操作:
- 在“允许”下拉列表中选择“自定义”。
- 然后,在“公式”文本框中输入以下公式。
=$C$1="Yes"
- 点击“确定”按钮关闭对话框。
注意:上述公式中,“C1”为你要引用的单元格,“是”为你要根据其内容限制输入的文本,请根据实际需要修改。
3.现在,如果 C1 单元格为“是”,A2:A9 区域可输入任意内容;若 C1 为其他内容,则无法输入任何值,如下演示:
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仅允许输入本周或本月日期的数据有效性
如需仅允许用户在特定单元格输入本周或本月的日期,本节将介绍相关公式实现。
允许输入本周日期
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”运行代码,所有数据有效性规则会立即从整个工作簿中删除。
最佳 Office 办公效率工具
🤖 | Kutools AI 助手:基于智能执行,彻底革新数据分析 |生成代码|创建自定义公式|分析数据并生成图表|调用 Kutools Functions… |
热门功能:查找、选中项的背景色或标记重复项|删除空行|合并列或单元格且不丢失数据|四舍五入(无公式)... | |
高级 LOOKUP:多条件查找 (VLookup)|多值查找 (VLookup)|多表查找 (VLookup Across Multiple Sheets)|模糊查找 (Fuzzy Lookup)... | |
高级下拉列表:快速创建下拉列表|依赖型下拉列表|多选下拉列表... | |
列管理器:添加指定数量的列 |移动列 |切换隐藏列的可见状态| 比较区域及列... | |
特色功能:网格聚焦|设计视图|增强编辑栏|工作簿 & 工作表管理器|资源库(自动文本)|日期提取|合并数据|加密/解密单元格|按列表发送电子邮件|超级筛选|特殊筛选(筛选粗体/倾斜/删除线等)... | |
热门15 大工具集:12 款文本工具(添加文本、删除特定字符等)|50+ 种图表 类型(甘特图等)|40+ 实用公式(基于生日计算年龄等)|19 款插入工具(插入二维码、按路径插入图片等)|12 种转换工具(小写金额转大写、汇率转换等)|7 款合并与分割工具(高级合并行、分割单元格等)|...更多精彩等你发现 |
用 Kutools for Excel 加速你的 Excel 技能,体验前所未有的高效办公。 Kutools for Excel 提供300 多项高级功能,助您提升效率,节省大量时间。点击此处,获取你最需要的功能...
Office Tab 为 Office 带来标签式界面,让你的工作更加轻松
- 在 Word、Excel、PowerPoint 启用标签式编辑和阅读
- 在同一窗口的新标签中打开和创建多个文档,无需新建窗口。
- 办公效率提升50%,每天帮你减少上百次鼠标点击!