跳至主要内容

Excel 数据有效性:在 Excel 中添加、使用、复制和删除数据有效性

Author: Xiaoyang Last Modified: 2025-07-31

在 Excel 中,数据有效性功能是一项强大的工具,可以用来限制用户在单元格中输入的内容。例如,你可以设置规则限制文本长度、限定输入特定格式、强制唯一值,或确保文本以特定字符开头或结尾。这些功能有助于维护数据的完整性,减少工作表中的错误。

本教程将介绍如何在 Excel 中添加、使用和删除数据有效性。内容涵盖基础操作和高级用法,提供详细的分步指导,帮助你高效应用该功能于实际工作。

目录:

1. 什么是 Excel 中的数据有效性?

2. 如何在 Excel 中添加数据有效性?

3. 数据有效性的基本示例

4. 数据有效性的高级自定义规则

5. 如何编辑 Excel 中的数据有效性?

6. 如何查找并选择 Excel 中含有数据有效性的单元格?

7. 如何将数据有效性规则复制到其他单元格?

8. 如何使用数据有效性圈出 Excel 中的无效输入?

9. 如何删除 Excel 中的数据有效性?


1. 什么是 Excel 中的数据有效性?

“数据有效性”功能可以帮助你在工作表中限制输入内容。通常,你可以为选定的单元格创建一些验证规则,以防止或仅允许某些类型的数据被输入。

数据有效性功能的一些基本用法:

  • 1. “任意值”:不进行任何验证,可以在指定单元格中输入任何内容。
  • 2. “整数”:仅允许输入整数。
  • 3. “小数”:允许输入整数和小数。
  • 4. “列表”:仅允许输入或选择预设列表中的值,值会以下拉列表形式显示。
  • 5. “日期”:仅允许输入日期。
  • 6. “时间”:仅允许输入时间。
  • 7. “文本长度”:仅允许输入指定长度的文本。
  • 8. “自定义”:通过自定义公式规则验证用户输入。

2. 如何在 Excel 中添加数据有效性?

在 Excel 工作表中,你可以按照以下步骤添加数据有效性:

1.选择你想要设置数据有效性的单元格区域,然后点击“数据”>“数据有效性”>“数据有效性”,如下图所示:

2. 在“数据有效性”对话框的“设置”选项卡下,请创建你自己的验证规则。在条件框中,你可以选择以下类型:

  • “值”:直接在条件框中输入数字;
  • “单元格引用”:引用工作表或其他工作表中的单元格;
  • “公式”:创建更复杂的公式作为条件。

例如,我将创建一个仅允许输入100 到1000之间整数的规则,设置条件如下图所示:

3. 配置好条件后,你可以切换到“输入信息”或“错误警告”选项卡,根据需要为验证单元格设置提示信息或错误警告。(如果不需要设置警告,直接点击“确定”即可完成。)

3.1)添加输入信息(可选):

你可以创建一个在选择含有数据有效性的单元格时显示的提示信息,帮助用户了解可输入的内容。

进入“输入信息”选项卡,操作如下:

  • 勾选“选定单元格时显示输入信息”选项;
  • 在相应字段中输入你想要的标题和提示信息;
  • 点击“确定”关闭对话框。

现在,当你选择带有数据有效性的单元格时,会弹出如下信息框:

3.2)创建有意义的错误提示(可选):

除了输入提示外,你还可以在输入无效数据时显示错误警告。

在“数据有效性”对话框的“错误警告”选项卡,操作如下:

  • 勾选“输入无效数据后显示错误警告”选项;
  • 在“样式”下拉列表中,选择你需要的警告类型:
    • “停止(默认)”:此警告类型会阻止用户输入无效数据。
    • “警告”:提醒用户数据无效,但不阻止输入。
    • “信息”:仅告知用户输入了无效数据。
  • 在相应字段中输入你想要的标题和警告信息;
  • 点击“确定”关闭对话框。

当输入无效值时,会弹出如下警告框:

“停止”选项:点击“重试”重新输入,或点击“取消”放弃输入。

“警告”选项:点击“是”接受无效输入,“否”修改输入,或“取消”放弃输入。

“信息”选项:点击“确定”接受无效输入,或点击“取消”放弃输入。

注意:如果你没有在“错误警告”框中设置自定义信息,将显示默认的“停止”警告框,如下所示:

A screenshot of the default Stop alert box in Excel's data validation


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 的“限制输入”功能,可以大大简化操作。

Kutools for Excel 提供了超过 300 种高级功能,简化复杂任务,提升创造力与效率。 通过集成 AI 能力,Kutools 能够精准自动执行任务,让数据管理变得轻松简单。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公式:

只允许输入以 CN 开头的文本(不区分大小写):
=COUNTIF(A2,"CN*")
只允许输入以 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公式:

只允许输入以 CN 或 UK 开头的文本(不区分大小写):
=COUNTIF(A2,"CN*")+COUNTIF(A2,"UK*")
只允许输入以 CN 或 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
A screenshot of the View Code option on the sheet tab context menu Arrow A screenshot of the pasted code in the code editor

注意:上述代码中的“A1:A100”和“A:A”为你要防止重复的列或区域,请根据实际需要修改。

2. 保存并关闭代码。现在,在 A1:A100 区域输入重复值时,会弹出警告提示,如下图所示:

A screenshot of a warning prompt box when duplicate values are entered in cells A1:A100


通过便捷功能仅允许唯一值

如果你安装了 Kutools for Excel,可以通过其“防止重复项”功能,仅需几次点击即可快速设置防止重复的数据有效性。

Kutools for Excel 提供了超过 300 种高级功能,简化复杂任务,提升创造力与效率。 通过集成 AI 能力,Kutools 能够精准自动执行任务,让数据管理变得轻松简单。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 的“仅能输入电话号码”功能也可以帮助你仅允许输入电话号码格式,只需几次点击即可完成。

Kutools for Excel 提供了超过 300 种高级功能,简化复杂任务,提升创造力与效率。 通过集成 AI 能力,Kutools 能够精准自动执行任务,让数据管理变得轻松简单。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 提供了强大的“仅能输入邮箱”功能,使用该工具只需一键即可防止输入无效邮箱地址。

Kutools for Excel 提供了超过 300 种高级功能,简化复杂任务,提升创造力与效率。 通过集成 AI 能力,Kutools 能够精准自动执行任务,让数据管理变得轻松简单。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
A screenshot of the View Code option on the context menu Arrow A screenshot showing the VBA editor with the IP address validation code added to a worksheet

注意:上述代码中的“A2:A10”为你只允许输入 IP 地址的单元格区域。

2. 保存并关闭代码。现在,只有有效的 IP 地址才能输入到指定单元格。


通过简便功能强制仅输入 IP 地址格式

如果你的工作簿已安装 Kutools for Excel,其“仅能输入 IP 地址”功能也可以帮助你轻松实现该需求。

Kutools for Excel 提供了超过 300 种高级功能,简化复杂任务,提升创造力与效率。 通过集成 AI 能力,Kutools 能够精准自动执行任务,让数据管理变得轻松简单。Kutools for Excel 的详细信息...         免费试用...

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,其“清除数据验证限制”功能也可帮助你快速移除选定区域或整个工作表的数据有效性规则。

Kutools for Excel 提供了超过 300 种高级功能,简化复杂任务,提升创造力与效率。 通过集成 AI 能力,Kutools 能够精准自动执行任务,让数据管理变得轻松简单。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,支持您的首选语言——包括英语、西班牙语、德语、法语、中文及40 多种其他语言!

用 Kutools for Excel 加速你的 Excel 技能,体验前所未有的高效办公。 Kutools for Excel 提供300 多项高级功能,助您提升效率,节省大量时间。点击此处,获取你最需要的功能...


Office Tab 为 Office 带来标签式界面,让你的工作更加轻松

  • 在 Word、Excel、PowerPoint 启用标签式编辑和阅读
  • 在同一窗口的新标签中打开和创建多个文档,无需新建窗口。
  • 办公效率提升50%,每天帮你减少上百次鼠标点击!