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

应用对象

Microsoft 365 专属 Excel Microsoft 365 Mac 版专属 Excel Excel 2024 Excel 2024 for Mac Excel 2021 Excel 2021 for Mac Excel 2019 Excel 2016

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

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

选择 “数据 >数据验证”。
在“设置”选项卡的“允许”下，选择一个选项：
- “整数” - 将单元格限制为仅接受整数。
- “小数”- 将单元格限制为仅接受小数。
- “列表”- 从下拉列表中选取数据。
- “日期”- 将单元格限制为仅接受日期。
- 时间 - 将单元格限制为仅接受时间。
- “文本长度”- 限制文本长度。
- “自定义”- 适用于自定义公式。
在 “数据”下，选择一个条件。
根据为“允许”和“数据”选择的值，设置其他必需值。
选择“输入消息”选项卡，并自定义用户在输入数据时将看到的消息。
选择“选定单元格时显示输入信息”复选框，在用户选择或在所选单元格上悬停时显示此信息。
选择“错误警报”选项卡来自定义错误消息，并选择“样式”。
选择“确定”。
现在，如果用户尝试输入无效值，则会出现一条 错误警报 ，其中包含您的自定义消息。

下载我们的示例

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

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

限制数据输入

选择要限制数据输入的单元格。
在 “数据 ”选项卡上，选择 “数据验证”。

注意

如果验证命令不可用，则表明工作表可能受保护，或者工作簿可能处于共享状态。如果工作簿处于共享状态或工作表受保护，则无法更改数据验证设置。有关工作簿保护的详细信息，请参阅保护工作簿。
在“允许”框中，选择要允许的数据类型，并填写“限制条件”和“值”。

注意

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

提示用户输入有效项

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

选择要提示用户输入有效数据的单元格。
在 “数据 ”选项卡上，选择 “数据验证”。

注意

如果验证命令不可用，则表明工作表可能受保护，或者工作簿可能处于共享状态。如果工作簿处于共享状态或工作表受保护，则无法更改数据验证设置。有关工作簿保护的详细信息，请参阅保护工作簿。
在“输入信息”选项卡上，勾选“选定单元格时显示输入信息”旁的框。
在“标题”框中，键入邮件的标题。
在“输入信息”框中，键入要显示的消息。

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

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

选择要在其中显示错误消息的单元格。
在 “数据 ”选项卡上，选择 “数据验证” 。

注意

如果验证命令不可用，则表明工作表可能受保护，或者工作簿可能处于共享状态。如果工作簿处于共享状态或工作表受保护，则无法更改数据验证设置。有关工作簿保护的详细信息，请参阅保护工作簿。
在错误警告选项卡上的“标题”框中，键入邮件的标题。
在“错误消息”框中, 键入在输入无效数据时要显示的消息。

执行下列操作之一：

目的	在“样式”弹出菜单上，选择
需要用户先修复错误，然后再继续操作	停止
警告用户数据无效，并要求他们选择“是”或“否”以指示是否要继续操作	警告
警告用户数据无效，但在关闭警告消息后允许他们继续操作	重要提示

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

注意

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

选择一个或多个单元格进行验证。
在数据选项卡的数据工具组中，选择 数据验证。
在“设置”选项卡上的“允许”框中，选择“列表”。
在“源”框中，键入列表值，用逗号分隔。例如，键入"低"、"平均值"、"高"。
确保选中“单元格内下拉列表”复选框。否则，单元格旁边将看不到下拉箭头。
若要指定希望如何处理空值 (null)，请选中或清除“忽略空值”复选框。
测试数据验证以确保其正常工作。尝试在单元格中输入有效和无效数据，以确保设置按预期方式工作并且显示所预期的消息。

注意

创建下拉列表后，确认它满足你的需求。例如，你可能想检查单元格的宽度是否足以显示所有输入项。
删除数据验证 - 选择包含要删除的验证的单元格，转到 “数据 > 数据验证” ，然后在“数据验证”对话框中按 “全部清除”，然后选择“ 确定”。

添加其他类型的数据有效性

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

要执行此操作：	请按以下步骤操作：
将数据输入限制为一定范围内的整数。	执行上述的步骤 1 到 2。从“允许”列表中，选择“整数”。在“数据”框中选择所需的限制类型。例如，若要设置上限和下限，请选择“介于”。输入允许的最小值、最大值或特定值。您还可以输入返回数值的公式。例如，假定您正在验证单元格 F1 中的数据。若要将最小扣除限额设置为该单元格中子项数的两倍，请在“数据”框中选择“大于或等于”，然后在“最小值”框中输入公式 *=2F1**。
将数据输入限制为一定范围内的小数。	执行上述的步骤 1 到 2。在“允许”框中，选择“小数”。在“数据”框中选择所需的限制类型。例如，若要设置上限和下限，请选择“介于”。输入允许的最小值、最大值或特定值。您还可以输入返回数值的公式。例如，若要在单元格 E1 中将佣金和奖金的最高限额设置为销售人员工资的 6%，请在“数据”框中选择“小于或等于”，然后在“最大值”框中输入公式 *=E16%。若要允许用户输入百分比（例如 20%），请在“允许”框中选择“小数”，在“数据”框中选择所需的限制类型，以小数形式输入最小值、最大值或特定值（例如 .2），然后通过选择单元格并单击“开始”选项卡上“数字”组中的“百分比样式**”来将数据验证单元格显示为百分比。
将数据输入限制为某日期范围内的日期。	执行上述的步骤 1 到 2。在 “允许 ”框中，选择“ 日期 ”。在“数据”框中选择所需的限制类型。例如，若要设置日期上限，请选择“大于”。输入允许的开始、结束或特定日期。还可以输入返回日期的公式。例如，若要设置一个介于当前日期和当前日期之后 3 天之间的时间范围，请在“数据”框中选择“介于”，在“开始日期”框中输入“=TODAY()”，然后在“结束日期”框中输入“=TODAY()+3”。
将数据输入限制为某时段内的时间。	执行上述的步骤 1 到 2。在“允许”框中，选择“时间”。在“数据”框中选择所需的限制类型。例如，若要设置时间下限，可以选择“小于”。输入允许的开始、结束或特定时间。如果您要输入特定的时间，请使用 hh:mm 时间格式。例如，假设你已将单元格 E2 设置为开始时间 (8:00 AM)，并将单元格 F2 设置为结束时间 (5:00 PM)，而希望将会议时间限制于二者之间，请在“数据”框中选择“介于”，并在“开始时间”框中输入“=E2”，在“结束时间”框中输入“=F2”。
将数据输入限制为指定长度的文本。	执行上述的步骤 1 到 2。在“允许”框中，选择“文本长度”。在“数据”框中选择所需的限制类型。例如，若要设置字符数上限，请选择“小于或等于”。在这种情况下，我们希望将输入限制为 25 个字符，因此在“数据”框中选择小于或等于，然后在“最大值”框中输入 25。
根据其他单元格的内容计算允许输入的内容。	执行上述的步骤 1 到 2。在“允许”框中选择所需的数据类型。在“数据”框中选择所需的限制类型。在 “数据 ”框下方的框中，选择要用于指定允许内容的单元格。例如，若要仅在结果未超过单元格 E1 中的预算时才允许输入帐户，请选择“ 允许整>数”、“数据”、小于或等于和 “最大值> = =E1”。

数据验证中的公式示例

注意

以下示例使用自定义选项（在此处编写公式）设置条件。无论“数据”框中显示何种内容，都无需担心，因为自定义选项已禁用此操作。
本文中的屏幕截图是在 Excel 2016 中拍摄的;但 Excel 网页版中的功能相同。

若要确保满足如下条件	请输入如下公式
包含产品 ID 的单元格 (C2) 始终以标准前缀“ID-”开头，且至少包含 10 个（大于 9 个）字符。	=AND (LEFT (C2,3) =“ID-”，LEN (C2) >9)
包含产品名称 (D2) 的单元格只包含文本。	=ISTEXT (D2)
包含某人生日的单元格 (B6) 必须大于单元格 B4 中设置的年份。	*=IF (B6<= (TODAY () - (365B4) ) ，TRUE，FALSE)** 将
单元格区域 A2:A10 中的所有数据都包含唯一值。	=COUNTIF ($A$2：$A$10，A2) =1 注意：必须先为单元格 A2 输入数据验证公式，然后将 A2 复制到 A3：A10 以使 COUNTIF 的第二个参数与当前单元格匹配。 A2)=1 部分将更改为 A3)=1、A4)=1 等。
请确保单元格 B4 中的电子邮件地址输入包含符号 @。	=ISNUMBER (FIND (“@”，B4) )