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

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

Windows macOS网络

下载我们的示例。

下载包含以下所有数据验证示例的示例工作簿。

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

选择数据数据验证。

在设置选项卡上的允许下，选择一个选项：

整数将单元格限制为只接受整数。

小数-限制单元格只接受小数。

列表从下拉列表中选择数据。

日期将单元格限制为只接受日期。

时间限制单元格只接受时间。

文本长度限制文本的长度。

用户定义适用于用户定义的公式。

在“数据”下，选择一个条件：

“之间”

“不在之间”

“等于”

“不等于”

“大于”

“小于”

"大于或等于"

"小于或等于"

在设置选项卡上的允许下，选择一个选项：

根据为“允许”和“数据”选择的值，设置其他必需的值。例如，如果选择介于，则可以为单元格选择最小值和最大值。

如果要忽略空白，可以选择忽略空值。

如果要向规则添加标题和信息，请选择“输入信息”选项卡，然后键入标题和输入信息。

选中“选中单元格时显示输入信息”复选框，以便当用户选择或悬停在选定单元格上时显示此信息。

选择“确定”。

现在，如果用户试图输入一个无效值，将出现一个弹出窗口，并显示消息“该值与该单元格的数据有效性限制不匹配。”

如果要创建需要用户输入数据的工作表，可能需要将输入限制在某个日期或数字，或者确保只输入正整数。Excel使用数据验证/数据确认将输入数据限制在特定的单元格内，选中单元格时提示用户输入有效数据，用户输入无效数据时显示错误信息。

当前正在使用哪个版本？

当前正在使用哪个版本？最新版本Office 2011限制数据输入。

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

在数据选项卡上，单击数据验证数据验证。

注意：如果验证命令不可用，则工作表可能受到保护，或者工作簿可能处于共享状态。如果工作簿是共享的或工作表受到保护，则不能更改数据验证设置。有关工作簿保护的更多信息，请参见保护工作簿。

在“允许”框中，选择要允许的数据类型，并填写“约束和值”。

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

提示用户输入有效项目。

当用户单击有数据输入要求的单元格时，将显示一条消息，指示哪些数据有效。

选择单元格以提示用户输入有效数据。

在数据选项卡上，单击数据验证数据验证。

注意：如果验证命令不可用，则工作表可能受到保护，或者工作簿可能处于共享状态。如果工作簿是共享的或工作表受到保护，则不能更改数据验证设置。有关工作簿保护的更多信息，请参见保护工作簿。

在“输入信息”选项卡上，选中选中单元格时显示输入信息旁边的框。

在“标题”框中，键入邮件的标题。

在输入信息框中，键入要显示的消息。

输入无效数据时会显示错误信息。

如果您有数据限制，如果用户在单元格中输入无效数据，将显示一条消息解释错误原因。

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

在数据选项卡上，单击数据验证数据验证。

注意：如果验证命令不可用，则工作表可能受到保护，或者工作簿可能处于共享状态。如果工作簿是共享的或工作表受到保护，则不能更改数据验证设置。有关工作簿保护的更多信息，请参见保护工作簿。

在“错误警告”选项卡的“标题”框中，键入邮件的标题。

在“错误消息”框中，键入输入无效数据时显示的消息。

请执行以下操作之一：

到

在“样式”弹出式菜单中，选择。

用户需要先修复错误，然后才能继续。

停止

用户被警告数据无效，并被要求选择“是”或“否”以指示是否继续。

警告

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

重要提示

限制数据输入。

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

在“数据”选项卡的“工具”下，单击“验证”。

注意：如果验证命令不可用，则工作表可能受到保护，或者工作簿可能处于共享状态。如果工作簿是共享的或工作表受到保护，则不能更改数据验证设置。有关工作簿保护的更多信息，请参见保护工作簿。

在“允许”弹出式菜单中，选择要允许的数据类型。

在“数据”弹出菜单中，选择所需的资格类型，然后输入“限制值”。

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

提示用户输入有效项目。

当用户单击有数据输入要求的单元格时，将显示一条消息，指示哪些数据有效。

选择单元格以提示用户输入有效数据。

在“数据”选项卡的“工具”下，单击“验证”。

注意：如果验证命令不可用，则工作表可能受到保护，或者工作簿可能处于共享状态。如果工作簿是共享的或工作表受到保护，则不能更改数据验证设置。有关工作簿保护的更多信息，请参见保护工作簿。

在“输入信息”选项卡上，选中选中单元格时显示输入信息旁边的框。

在“标题”框中，键入邮件的标题。

在输入信息框中，键入要显示的消息。

输入无效数据时会显示错误信息。

如果您有数据限制，如果用户在单元格中输入无效数据，将显示一条消息解释错误原因。

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

在“数据”选项卡的“工具”下，单击“验证”。

注意：如果验证命令不可用，则工作表可能受到保护，或者工作簿可能处于共享状态。如果工作簿是共享的或工作表受到保护，则不能更改数据验证设置。有关工作簿保护的更多信息，请参见保护工作簿。

在“错误警告”选项卡的“标题”框中，键入邮件的标题。

在“错误消息”框中，键入输入无效数据时显示的消息。

请执行以下操作之一：

到

在“样式”弹出式菜单中，选择。

用户需要先修复错误，然后才能继续。

停止

用户被警告数据无效，并被要求选择“是”或“否”以指示是否继续。

警告

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

重要提示

向单元格或区域添加数据验证。

请注意，这一部分的前两个步骤是添加任何类型的数据验证。步骤3-7专门用于创建下拉列表。

选择一个或多个要验证的单元格。

在数据选项卡的数据工具组中，单击数据验证。

在“设置”选项卡的“允许”框中，选择“列表”。

在“源”框中，键入列表值，用逗号分隔。例如，键入“低”、“平均”和“高”。

确保选中了“单元格中的下拉列表”复选框。否则，您将看不到单元格旁边的下拉箭头。

要指定如何处理空值，请选中或清除“忽略空值”复选框。

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

注意：

创建下拉列表后，确保它满足您的需求。例如，您可能想要检查单元格是否足够宽以显示所有条目。

删除数据验证选择包含要删除的验证的单元格，然后转到数据数据验证，按数据验证对话框中的全部清除按钮，然后单击确定。

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

下表列出了其他类型的数据有效性，并显示了如何将它们添加到工作表中。

为此：

请遵循以下步骤：

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

执行上述步骤1至2。

从允许列表中，选择整数。

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

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

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

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

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

执行上述步骤1至2。

在“允许”框中，选择“小数”。

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

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

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

注意：要允许用户输入百分比(例如20%)，请在“允许”框中选择“十进制”，在“数据”框中选择所需的限制类型，以十进制形式输入最小值、最大值或特定值(例如0.2)，然后选择单元格并单击“开始”。

选项卡中，单击百分比样式以百分比形式显示数据验证单元格。

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

执行上述步骤1至2。

在“允许”框中，选择“日期”。

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

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

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

将数据输入限制在一定时间内。

执行上述步骤1至2。

在允许框中，选择时间。

在“数据”框中选择所需的限制类型。例如，要设置较低的时间限制，可以选择小于。

输入允许的开始、结束或特定时间。如果要输入具体时间，请使用hh:mm时间格式。

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

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

执行上述步骤1至2。

在“允许”框中，选择“文本长度”。

在“数据”框中选择所需的限制类型。例如，要设置最大字符数，请选择小于或等于。

这种情况下，需要限制输入25个字符，所以在“数据”框中选择“小于等于”，然后在“最大值”框中输入25。

根据其他单元格的内容。

执行上述步骤1至2。

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

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

在“数据”框或其下的框中，单击指定允许输入的单元格。

例如，如果仅当结果不超过E1单元格中的预算时才允许输入帐户，请选择“允许”、“整数”、“日期”、“小于或等于”和“最大值==E1”。

数据验证中的公式示例。

注意：

以下示例使用自定义选项(在此写入公式)来设置条件。无论“数据”框中显示什么，不要担心，因为自定义选项已禁用此操作。

本文截图拍摄于Excel 2016但是Excel Web版中的功能是一样的。

确保满足以下条件：

请输入以下公式。

包含产品标识的单元格(C2)总是以标准前缀“标识-”开头，并且至少包含10个(多于9个)字符。

=AND(左(C2，3)=“ID-”，LEN(C2)9)

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

=ISTEXT(D2)

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

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

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

=COUNTIF(2:澳元10澳元，A2)=1

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

详细信息

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

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

需要更多帮助吗？

您可以随时咨询Excel技术社区的专家，在回答社区获得支持，或者在Excel用户语音上提出新功能或功能改进建议。

另见。

数据验证的其他相关信息。

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

删除下拉列表。