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

可使用数据验证来限制数据类型或用户输入单元格的值。数据验证的最常见用法之一是创建下拉列表。请观看此视频,快速了解数据验证概要。

下载我们的示例

可以下载一个包含本文中所有可查到的数据验证示例的示例工作簿。可以沿用已有数据验证方案,或创建自己的。

下载 Excel 数据验证示例

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

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

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

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

    数据验证位于“数据”选项卡的“数据工具”组上
  3. 在“设置”选项卡上的“允许”框中,选择“列表”。

    “数据验证”对话框中的“设置”选项卡
  4. 在“”框中,键入列表值,用逗号分隔。例如:

    1. 若要将答案限制为两个选项(例如,“你有孩子吗?”),请键入“,否”。

    2. 若要将供应商的质量信誉限制了三个等级,请键入“低,中,高”

      注意: 以上步骤一般推荐用于不太可能更改的列表项。如果列表可能更改,或随着时间推移需要添加或删除项目,则最好按以下最佳做法步骤进行操作。

      最佳做法:还可通过引用工作簿中其他位置的单元格区域来创建列表项。最有效的方法是创建列表,然后将其格式设置为“Excel 表格”(从“开始”选项卡中选择“样式”>“套用表格格式”,再选择最适合你的表格样式)。然后,选择表格的数据正文区域,即表格中仅含列表而不含表格标题的部分(此例中为“部门”),再在 A 列上方的名称框中填写有意义的名称。

      在名称框中为列表输入有意义的名称

    现在,不是在数据验证“”框中键入列表值,而是添加刚才定义的名称,前置等号 (=)。

    在表单名称前加个等号 (=)

    使用表的最大优点在于,当你向表添加或从中删除项目时,数据验证列表将自动更新。

    注意: 最好将你的列表放置在独立的工作表中(如有必要可隐藏),以防他人对其进行编辑。

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

    单元格旁显示的提供下拉箭头
  6. 若要指定希望如何处理空值 (null),请选中或清除“忽略空值”复选框。

    注意: 如果允许值基于具有已定义名称的单元格区域,并且此域中的任意位置存在空单元格,则选中“忽略空值”复选框将允许在有效单元格中输入任意值。同样,验证公式所引用的任何单元格也是如此:如果引用的单元格为空,则选中“忽略空值”复选框将允许在有效单元格中输入任意值。

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

注释: 

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

  • 如果下拉列表的条目列表在另一个工作表上,而您想要防止用户看到它或进行更改,请考虑隐藏和保护该工作表。有关如何保护工作表的详细信息,请参阅锁定单元格以对其进行保护

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

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

要执行此操作:

请按以下步骤操作:

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

  1. 按上面将数据验证添加到单元格或区域中的步骤 1 - 3 进行操作。

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

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

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

    “验证条件”对话框

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

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

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

  1. 按上面将数据验证添加到单元格或区域中的步骤 1 - 3 进行操作。

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

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

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

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

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

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

  1. 按上面将数据验证添加到单元格或区域中的步骤 1 - 3 进行操作。

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

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

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

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

    验证条件设置,用于将数据输入限制为某时段

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

  1. 按上面将数据验证添加到单元格或区域中的步骤 1 - 3 进行操作。

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

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

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

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

    验证设置,用于将时间条目限制在某时段内

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

  1. 按上面将数据验证添加到单元格或区域中的步骤 1 - 3 进行操作。

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

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

  4. 此情况下需要将输入限制为 25 个字符,因此选择“数据”框中的“小于或等于”,然后在“最大值”框中输入 25

    文本长度受限的数据验证示例

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

  1. 按上面将数据验证添加到单元格或区域中的步骤 1 - 3 进行操作。在“允许”框中选择所需数据类型。

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

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

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

    验证设置,用于基于另一单元格内容进行计算

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

若要确保满足如下条件

请输入如下公式

包含产品 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 中的电子邮件地址输入包含符号 @。

=ISUMBER(FIND("@",B4)

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

  • 为什么功能区上未启用数据验证命令​​?命令不可用的原因可能有:

    • Microsoft Excel 表已链接到 SharePoint 网站 不能向已链接到 SharePoint 网站的 Excel 表添加数据验证。若要添加数据验证,必须取消该 Excel 表的链接或将该 Excel 表转换为区域。

    • 当前正在输入数据 在单元格中输入数据时,“数据”选项卡上的数据有效性命令不可用。若要结束数据输入,请按 Enter 或 Esc。

    • 工作表受保护或处于共享状态 如果工作簿处于共享状态或受保护,则无法更改数据有效性设置。若要了解如何停止共享或保护工作簿,请参阅保护工作簿

  • 是否可以更改字号?不可以,字号是固定的。更改显示大小的唯一方法是在 Excel 窗口的右下角调整屏幕缩放。但也可使用 ActiveX 组合框。请参阅向工作表中添加列表框或组合框

  • 是否可使数据验证在我键入时自动填充或自动选择?不可以,但如果使用 ActiveX 组合框,则确有此功能。

  • 可否在数据验证列表中进行多重选择?不可以,除非使用 ActiveX 组合列表框

  • 可否选择数据验证列表中的项目并用其填充其它列表?可以!这称为“从属数据验证”。有关信息信息,请参阅创建从属下拉列表

  • 如何删除工作表上的所有数据验证?可以使用“转到”>“特殊”对话框。在“开始”选项卡上转到“编辑”>“查找和选择”(或在键盘上按 F5Ctrl+G,然后转到“特殊”>“数据验证”并选择“所有(以查找带数据验证的所有单元格)”或“相同”(以查找匹配特定数据验证设置的单元格)。

    转到“特殊”对话框

    接下来调出数据验证对话框(转到“数据”选项卡 >“数据验证”),按“全部清除”按钮,然后单击“确定”。

  • 是否可以强制他人在有数据验证的单元格中输入?不可以,但你可以使用 VBA (Visual Basic for Applications) 检查某人是否在情况下(例如在其保存或关闭工作簿之前)进行了输入。如果他们尚未选择,你可以取消事件,使其做出选择方可继续。

  • 如何基于数据验证列表选择填充单元格颜色?你可以使用条件格式。在本例中,你可以使用“仅格式化包含的单元格”选项。

    仅对包含选项的单元格进行格式设置
  • 如何验证电子邮件地址?可以使用“自定义”>“公式”方法,检查输入中是否存在 @ 符号。在本例中,使用的公式是 =ISNUMBER(FIND(“@”,D2))。FIND 函数将查找 @ 符号,如果找到,则返回它在文本字符串中的数字位置并允许输入。如果找不到,则 FIND 将返回错误并禁止输入。

你是否有特定函数问题?

在 Excel 社区论坛中发布问题

帮助我们改进 Excel

是否有关于如何改进下一版 Excel 的建议?如果有,请查看 Excel User Voice 上的主题。

另请参阅

数据有效性其他相关信息

视频:创建和管理下拉列表

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

删除下拉列表

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

此信息是否有帮助?

谢谢您的反馈!

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

×