Applies ToMicrosoft 365 专属 Excel Microsoft 365 Mac 版专属 Excel Excel 网页版 Excel 2024 Excel 2024 for Mac Excel 2021 Excel 2021 for Mac Excel 2019 Excel 2016

使用数据验证来限制用户输入单元格(如下拉列表)的数据类型或值。

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

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

  1. 选择“ 数据 >数据验证”。

    数据验证

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

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

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

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

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

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

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

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

  3. 在“ 数据”下,选择一个条件。

  4. 根据为 “允许 ”和“数据”选择的值设置其他必需值。

  5. 选择“ 输入消息 ”选项卡,然后自定义用户在输入数据时将看到的消息。

  6. 选中 “选择单元格时显示输入消息 ”复选框,当用户选择或将鼠标悬停在所选单元格 () 时显示消息。

  7. 选择“ 错误警报 ”选项卡以自定义错误消息,并选择 “样式”。

  8. 选择 “确定”。

    现在,如果用户尝试输入无效的值,将显示错误 警报 ,并显示自定义消息。

下载我们的示例

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

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

限制数据输入

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

  2. 在“ 数据 ”选项卡上,选择“数据验证”。

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

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

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

提示用户输入有效项

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

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

  2. 在“ 数据 ”选项卡上,选择“ 数据验证”。

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

  3. 在“输入消息”选项卡上,选择“选择单元格时显示输入消息 检查框。

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

  5. “输入消息 ”框中,键入要显示的消息。

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

如果你有数据限制,则如果用户将无效数据输入到单元格中,将显示一条消息,说明错误的原因。

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

  2. 在“ 数据 ”选项卡上,选择“数据验证 ”。

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

  3. 在“ 错误警报 ”选项卡上的“ 标题 ”框中,键入邮件标题。

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

  5. 执行下列操作之一:

    若要

    “样式弹出菜单上,选择

    需要用户先修复错误,然后再继续操作

    停止

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

    警告

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

    重要提示

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

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

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

  2. 在“ 数据 ”选项卡上的“ 数据工具” 组中,选择“ 数据验证”。

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

  4. 在“ ”框中,键入列表值,用逗号分隔。 例如,键入 Low、Average、High

  5. 确保选中“单元格内下拉列表 检查”框。 否则,将不会在单元格旁边看到下拉箭头。

  6. 若要指定如何处理空 (null) 值,请选择或清除“忽略空白 检查”框。

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

注意: 

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

  • 删除数据验证 - 选择包含要删除的验证的单元格,转到 “数据 > 验证” ,在“数据验证”对话框中按“ 全部清除”,然后选择“ 确定”。

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

要执行此操作:

请按以下步骤操作:

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

  1. 执行上述的步骤 1 到 2。

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

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

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

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

    例如,假定您正在验证单元格 F1 中的数据。 若要将最小扣减限制设置为该单元格中子项数的两倍,请在“数据”框中选择大于或等于,然后在“最小”框中输入公式 =2*F1

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

  1. 执行上述的步骤 1 到 2。

  2. “允许 ”框中,选择“ 十进制”。

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

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

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

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

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

  1. 执行上述的步骤 1 到 2。

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

  3. 在“ 数据 ”框中,选择所需的限制类型。 例如,若要允许日期在特定日期之后,请选择 “大于”。

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

    还可以输入返回日期的公式。 例如,若要设置从今天日期到 3 天的时间段,请在“数据”框中选择“在”开始日期“框中输入”=TODAY () “,然后在”结束日期“框中输入”=TODAY () +3”。

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

  1. 执行上述的步骤 1 到 2。

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

  3. 在“ 数据 ”框中,选择所需的限制类型。 例如,若要允许在一天中的某个时间之前有时间,请选择 “小于”。

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

    例如,假设单元格 E2 设置了开始时间 (上午 8:00) ,单元格 F2 的结束时间 (下午 5:00) ,并且你想要限制这些时间之间的会议时间,然后在“数据框中选择“之间”,在“开始时间”框中输入 =E2,然后在“结束时间”框中输入 =F2

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

  1. 执行上述的步骤 1 到 2。

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

  3. 在“ 数据 ”框中,选择所需的限制类型。 例如,若要允许最多一定数量的字符,请选择 小于或等于

  4. 在这种情况下,我们希望将条目限制为 25 个字符,因此请在“数据”框中选择“小于或等于”,并在“最大”框中输入 25 个字符。

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

  1. 执行上述的步骤 1 到 2。

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

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

  4. 在“ 数据 ”框下面的一个或多个框中,选择要用于指定允许的单元格。

    例如,若要仅当结果不会超过单元格 E1 中的预算时才允许帐户的条目,请选择“ 允许 > 整数、数据,小于或等于”和 “最大 >= =E1”。

注意: 

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

  • 本文中的屏幕截图是在 Excel 2016;但功能在 Excel 网页版 中相同。

若要确保满足如下条件

请输入如下公式

包含产品 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))

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

提示: 如果你是小型企业所有者,想要详细了解如何设置 Microsoft 365,请访问小型企业帮助和学习

希望获得更多信息?

创建下拉列表

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

数据验证其他相关信息

需要更多帮助?

需要更多选项?

了解订阅权益、浏览培训课程、了解如何保护设备等。

社区可帮助你提出和回答问题、提供反馈,并听取经验丰富专家的意见。