单元格

将数据有效性应用于单元格

将数据有效性应用于单元格

注意: 我们希望能够尽快以你的语言为你提供最新的帮助内容。 本页面是自动翻译的,可能包含语法错误或不准确之处。 我们的目的是使此内容能对你有所帮助。 可以在本页面底部告诉我们此信息是否对你有帮助吗? 请在此处查看本文的英文版本以便参考。

可使用数据验证来限制数据类型或用户输入单元格的值。 数据验证的最常见用法之一是创建下拉列表

您的浏览器不支持视频。 安装 Microsoft Silverlight、Adobe Flash Player 或 Internet Explorer 9。

下载我们的示例

下载包含本文中的所有数据验证示例的示例工作簿

  1. 选择要对其创建规则的单元格。

  2. 选择 "数据 >Data 验证"。

    数据有效性

  3. 在“设置”选项卡的“允许”下,选择一个选项:

    • 整数-将单元格限制为仅接受整数。

    • Decimal -将单元格限制为仅接受十进制数字。

    • 列表”- 从下拉列表中选取数据。

    • 日期”- 将单元格限制为仅接受日期。

    • 时间-将单元格限制为仅接受时间。

    • 文本长度”- 限制文本长度。

    • 自定义”- 适用于自定义公式。

  4.  在“数据”下,选择一个条件:

    • “介于”

    • “未介于”

    • “等于”

    • “不等于”

    • “大于”

    • “小于”

    • “大于或等于”

    • “小于或等于”

  5. 在“设置”选项卡的“允许”下,选择一个选项:

  6. 基于选择的“允许”和“数据”值,选择所需的其他值。 例如, 如果您选择 "介于", 然后选择单元格的 "最小值:" 和 "最大值: " 值。

  7. 如果要忽略空白,可选择“忽略空值”。

  8. 如果要为规则添加标题和消息, 请选择 "输入消息" 选项卡, 然后键入标题和输入消息。

  9. 选择“选定单元格时显示输入信息”复选框,在用户选择或在所选单元格上悬停时显示此信息。

  10. 选择“确定”。

    现在,如果用户尝试输入无效值,将会出现弹出窗口,显示信息“此值与此单元格的数据有效性限制不匹配。”

如果要创建需要用户输入数据的工作表, 您可能希望将输入限制为某一特定日期或数字的范围, 或者确保仅输入正整数。 Excel 可以通过使用数据验证/数据有效性 将数据输入限制为特定单元格, 提示用户在选定单元格时输入有效的数据, 并在用户输入无效数据时显示错误消息。

限制数据输入

  1. 选择要限制数据输入的单元格。

  2. 在 "数据" 选项卡上, 单击 "数据验证>数据验证"。

    注意: 如果 "有效性" 命令不可用, 则工作表可能受保护或工作簿可能已共享。 如果工作簿处于共享状态或工作表受保护,则无法更改数据验证设置。 有关工作簿保护的详细信息,请参阅保护工作簿

  3. 在 "允许" 框中, 选择要允许的数据类型, 并填写 "限制条件" 和 "值"。

    注意: 输入限制值的框将基于你选择的数据和限制条件进行标记。 例如, 如果选择 "日期" 作为数据类型, 则可以在 "最小" 和 "最大值" 框中输入标记 "开始日期" 和 "结束日期" 的限制值。

提示用户输入有效条目

当用户在具有数据输入要求的单元格中单击时, 可以显示一条消息, 说明哪些数据有效。

  1. 选择要在其中提示用户输入有效数据的单元格。

  2. 在 "数据" 选项卡上, 单击 "数据验证>数据验证"。

    注意: 如果 "有效性" 命令不可用, 则工作表可能受保护或工作簿可能已共享。 如果工作簿处于共享状态或工作表受保护,则无法更改数据验证设置。 有关工作簿保护的详细信息,请参阅保护工作簿

  3. 在 "输入消息" 选项卡上, 选中 "选定单元格时显示输入信息" 复选框。

  4. 在 "标题" 框中, 键入邮件的标题。

  5. 在 "输入消息" 框中, 键入要显示的消息。

输入无效数据时显示错误消息

如果存在数据限制, 并且用户在单元格中输入无效数据, 则可以显示一条说明错误的消息。

  1. 选择要在其中显示错误消息的单元格。

  2. 在 "数据" 选项卡上, 单击 "数据验证>数据验证"。

    注意: 如果 "有效性" 命令不可用, 则工作表可能受保护或工作簿可能已共享。 如果工作簿处于共享状态或工作表受保护,则无法更改数据验证设置。 有关工作簿保护的详细信息,请参阅保护工作簿

  3. 在 "出错警告" 选项卡上的 "标题" 框中, 键入您的消息的标题。

  4. 在 "错误消息" 框中, 键入要在输入无效数据时显示的消息。

  5. 请执行下列操作之一:

    若要

    在 " 样式"弹出菜单上, 选择

    需要用户先修复错误才能继续操作

    停止

    警告用户数据无效, 要求他们选择"是" 或 "" 以指示是否要继续

    警告

    警告用户数据无效, 但允许他们在关闭警告消息后继续

    重要事项

限制数据输入

  1. 选择要限制数据输入的单元格。

  2. 在 "数据" 选项卡上的 "工具" 下, 单击 "验证"。

    "数据"选项卡,"工具"组

    注意: 如果验证命令不可用,则表明工作表可能受保护,或者工作簿可能处于共享状态。 如果工作簿处于共享状态或工作表受保护,则无法更改数据验证设置。 有关工作簿保护的详细信息,请参阅保护工作簿

  3. 在 "允许" 弹出菜单上, 选择要允许的数据类型。

  4. 在 "数据" 弹出菜单上, 选择所需的限制条件的类型, 然后输入 "限制值"。

    注意: 输入限制值的框将基于你选择的数据和限制条件进行标记。 例如, 如果选择 "日期" 作为数据类型, 则可以在 "最小" 和 "最大值" 框中输入标记 "开始日期" 和 "结束日期" 的限制值。

提示用户输入有效条目

当用户在具有数据输入要求的单元格中单击时, 可以显示一条消息, 说明哪些数据有效。

  1. 选择要在其中提示用户输入有效数据的单元格。

  2. 在 "数据" 选项卡上的 "工具" 下, 单击 "验证"。

    "数据"选项卡,"工具"组

    注意: 如果验证命令不可用,则表明工作表可能受保护,或者工作簿可能处于共享状态。 如果工作簿处于共享状态或工作表受保护,则无法更改数据验证设置。 有关工作簿保护的详细信息,请参阅保护工作簿

  3. 在 "输入消息" 选项卡上, 选中 "选定单元格时显示输入信息" 复选框。

  4. 在 "标题" 框中, 键入邮件的标题。

  5. 在 "输入消息" 框中, 键入要显示的消息。

输入无效数据时显示错误消息

如果存在数据限制, 并且用户在单元格中输入无效数据, 则可以显示一条说明错误的消息。

  1. 选择要在其中显示错误消息的单元格。

  2. 在 "数据" 选项卡上的 "工具" 下, 单击 "验证"。

    "数据"选项卡,"工具"组

    注意: 如果验证命令不可用,则表明工作表可能受保护,或者工作簿可能处于共享状态。 如果工作簿处于共享状态或工作表受保护,则无法更改数据验证设置。 有关工作簿保护的详细信息,请参阅保护工作簿

  3. 在 "出错警告" 选项卡上的 "标题" 框中, 键入您的消息的标题。

  4. 在 "错误消息" 框中, 键入要在输入无效数据时显示的消息。

  5. 请执行下列操作之一:

    若要

    在 " 样式"弹出菜单上, 选择

    需要用户先修复错误才能继续操作

    停止

    警告用户数据无效, 要求他们选择"是" 或 "" 以指示是否要继续

    警告

    警告用户数据无效, 但允许他们在关闭警告消息后继续

    重要事项

将数据验证添加到单元格或区域

注意: 本节中的前两个步骤用于添加任何类型的数据验证。 步骤3-7 专门用于创建下拉列表。 

  1. 选择一个或多个单元格进行验证。

  2. “数据”选项卡上的“数据工具”组中,单击“数据有效性”

  3. 在“设置”选项卡上的“允许”框中,选择“列表”。

  4. 在“”框中,键入列表值,用逗号分隔。 例如, 键入 "低"、"平均值"、"高"

  5. 确保选中“提供下拉箭头”复选框。 否则,您将无法看到单元格旁边的下拉箭头。

  6. 若要指定希望如何处理空值 (null),请选中或清除“忽略空值”复选框。

  7. 测试数据验证以确保其正常工作。 尝试在单元格中输入有效和无效数据,以确保设置按预期方式工作并且显示所预期的消息。

注意: 

  • 创建下拉列表后,确认它满足你的需求。 例如, 你可能希望检查单元格的宽度是否足以显示所有条目。

  • 删除数据验证-选择包含要删除的验证的一个或多个单元格, 然后转到 " data > 数据验证", 然后在 "数据有效性" 对话框中按 "全部清除" 按钮, 然后单击"确定"

下表列出了其他类型的数据有效性,并显示了将其添加到工作表的方法。

要执行此操作:

请按以下步骤操作:

将数据输入限制为一定范围内的整数。

  1. 按照上述步骤1-2。

  2. “允许”列表中,选择“整数”

  3. “数据”框中选择所需的限制类型。 例如,若要设置上限和下限,请选择“介于”。

  4. 输入允许的最小值、最大值或特定值。

    您还可以输入返回数值的公式。

    例如,假定您正在验证单元格 F1 中的数据。 要将扣除额的下限设置为单元格 F1 中小孩数量的两倍,请在“数据”框中选择“大于或等于”,并在“最小值”框中输入公式“=2*F1”

将数据输入限制为一定范围内的小数。

  1. 按照上述步骤1-2。

  2. “允许”框中,选择“小数”

  3. “数据”框中选择所需的限制类型。 例如,若要设置上限和下限,请选择“介于”。

  4. 输入允许的最小值、最大值或特定值。

    您还可以输入返回数值的公式。 例如,若要将销售人员的佣金和提成的上限设置为单元格 E1 中薪水的 6%,请在“数据”框中选择“小于或等于”,并在“最大值”框中输入公式“=E1*6%”

    注意: 若要让用户输入百分比 (例如 20%), 请在 "允许" 框中选择 "小数", 在 "数据" 框中选择所需的限制类型, 输入 "最小"、"最大" 或特定值作为小数, 例如2, 然后通过选择单元格并单击 "开始" 选项卡上的 "数字" 组中的 "百分比样式", 以百分比形式显示数据有效性单元格。 按钮图像

将数据输入限制为某日期范围内的日期。

  1. 按照上述步骤1-2。

  2. “允许”框中,选择“日期”

  3. “数据”框中选择所需的限制类型。 例如,若要设置日期上限,请选择“大于”

  4. 输入允许的开始、结束或特定日期。

    还可以输入返回日期的公式。 例如,若要设置一个介于当前日期和当前日期之后 3 天之间的时间范围,请在“数据”框中选择“介于”,在“开始日期”框中输入“=TODAY()”,然后在“结束日期”框中输入“=TODAY()+3”。

将数据输入限制为某时段内的时间。

  1. 按照上述步骤1-2。

  2. “允许”框中,选择“时间”

  3. “数据”框中选择所需的限制类型。 例如,若要设置时间下限,可以选择“小于”

  4. 输入允许的开始、结束或特定时间。 如果您要输入特定的时间,请使用 hh:mm 时间格式。

    例如,假设你已将单元格 E2 设置为开始时间 (8:00 AM),并将单元格 F2 设置为结束时间 (5:00 PM),而希望将会议时间限制于二者之间,请在“数据”框中选择“介于”,并在“开始时间”框中输入“=E2”,在“结束时间”框中输入“=F2”。

将数据输入限制为指定长度的文本。

  1. 按照上述步骤1-2。

  2. “允许”框中,选择“文本长度”

  3. “数据”框中选择所需的限制类型。 例如,若要设置字符数上限,请选择“小于或等于”

  4. 在这种情况下, 我们希望将输入限制为25个字符, 因此, 在 "数据" 框中选择 "小于或等于", 然后在 "最大值" 框中输入25

根据其他单元格的内容计算允许输入的内容。

  1. 按照上述步骤1-2。

  2. 在“允许”框中选择所需的数据类型。

  3. “数据”框中选择所需的限制类型。

  4. “数据”框或其下面的框中,单击用于指定允许的输入内容的单元格。

    例如,如果只有在结果不超出单元格 E1 中预算时才允许输入帐户,请选择“允许”>“整数,日期”、“小于或等于”,以及“最大值”>=“=E1”。

注意: 

  • 以下示例使用自定义选项(在此处编写公式)设置条件。 无论“数据”框中显示何种内容,都无需担心,因为自定义选项已禁用此操作。

  • 本文中的屏幕截图是在Excel 2016 中拍摄的;但Excel Online 中的功能是相同的。

若要确保满足如下条件

请输入如下公式

包含产品 ID 的单元格 (C2) 始终以标准前缀“ID-”开头,且至少包含 10 个(大于 9 个)字符。

= AND (LEFT (C2, 3) = "ID-", LEN (c2) >9)

示例 6:数据验证中的公式

包含产品名称 (D2) 的单元格仅包含文本。

=ISTEXT(D2)

示例 2:数据验证中的公式

包含某人生日的单元格 (B6) 必须大于单元格 B4 中设置的年份。

=IF(B6<=(TODAY()-(365*B4)),TRUE,FALSE)

将输入限制在最短存在时间的数据验证示例

单元格区域 A2:A10 中的所有数据都包含唯一值。

=COUNTIF($A$2:$A$10,A2)=1

示例 4:数据验证中的公式

注意: 必须先为单元格 A2 输入数据验证公式,然后将 A2 复制到 A3:A10 以使 COUNTIF 的第二个参数与当前单元格匹配。 A2)=1 部分将更改为 A3)=1、A4)=1 等。

详细信息

请确保单元格 B4 中的电子邮件地址输入包含符号 @。

= ISNUMBER (FIND ("@", B4))

确保电子邮件地址包含 @ 符号的数据验证示例

需要更多帮助吗?

可随时在 Excel 技术社区中咨询专家,在解答社区获得支持,或在 Excel User Voice 上建议新功能或功能改进。

另请参阅

数据验证的详细信息

从下拉列表中添加或删除项目

删除下拉列表

扩展你的 Office 技能
了解培训
抢先获得新功能
加入 Office 预览体验计划

此信息是否有帮助?

谢谢您的反馈!

谢谢你的反馈! 可能需要转接到 Office 支持专员。

×