数据有效性(转载)

使用数据有效性可以控制用户输入到单元格的数据或值的类型。

例如，您可能希望将数据输入限制在某个日期范围、使用列表限制选择或者确保只输入正整数。

本文介绍Excel数据有效性的工作原理，并总结出可供您使用的各种数据有效性方法。

本文未涉及单元格保护，这是一种用于“锁定”或隐藏工作表中的某些单元格以使其无法被编辑或覆盖的功能。

有关如何保护单元格的详细信息，请参阅“请参阅”部分中的链接。

什么是数据有效性？数据有效性是一种Excel功能，用于定义可以在单元格中输入或应该在单元格中输入哪些数据。

您可以配置数据有效性以防止用户输入无效数据。

如果愿意，您可以允许用户输入无效数据，但当用户尝试在单元格中键入无效数据时会向其发出警告。

此外，您还可以提供一些消息，以定义您期望在单元格中输入的内容，以及帮助用户更正错误的说明。

例如，在市场营销工作簿中，您可以将某一单元格设置为只允许包含长度为三个字符的帐号。

当用户选择该单元格时，可以向其显示如下消息：如果用户忽略此消息，并在该单元格中键入无效数据（例如，两位或五位数字），您可以向其显示一条真正的错误消息。

在更高级一些的方案中，您可能需要使用数据有效性，以根据工作簿中其他某一位置的值来计算单元格中的最大允许值。

在下面的示例中，用户在单元格E7中键入了￥4,000，这超出了为佣金和奖金指定的最大限制。

如果工资预算发生了增减，则E7中的最大允许值也会随之增减。

数据有效性选项位于“数据”选项卡上的“数据工具”组中。

您可以在“数据有效性”对话框中配置数据有效性。

数据有效性何时有用？当您要与单位中的其他人员共享工作簿，并希望工作簿中所输入的数据准确无误且保持一致时，数据有效性十分有用。

除此之外，您还可以使用数据有效性执行下列操作：∙将数据限制为列表中的预定义项例如，您可以将部门类型限制为销售、财务、研发和IT。

同样，也可以从工作表中其他位置的单元格区域创建值列表。

∙将数字限制在指定范围之外例如，可以将扣除额的最小限制指定为特定单元格中小孩数量的两倍。

∙将日期限制在某一时间范围之外例如，可以指定一个介于当前日期和当前日期之后3天之间的时间范围。

∙将时间限制在某一时间范围之外例如，可以指定一个供应早餐的时间范围，它介于餐馆开始营业和开始营业后的5小时之内。

∙限制文本字符数例如，可以将单元格中允许的文本限制为10个或更少的字符。

同样，也可以将全名字段(C1)的特定长度设置为名字字段(A1)与姓氏字段(B1)的当前长度之和再加10个字符。

∙根据其他单元格中的公式或值验证数据有效性例如，可以使用数据有效性，根据计划的工资总额将佣金和提成的上限设置为￥3,600。

如果用户在单元格中输入的金额超过￥3,600，就会看到一条有效性消息。

数据有效性消息用户在单元格中输入无效数据时看到的内容取决于您配置数据有效性的方式。

您可以选择在用户选择单元格时显示输入信息。

输入信息通常用于指导用户单元格中可输入的数据类型。

如果需要，可以将此消息移走，但在您移到其他单元格或按Esc前，该消息会一直保留。

您还可以选择显示出错警告，它仅在用户输入无效数据后才显示。

您可以从下列三种类型的出错警告中进行选择：图标类型用于停止阻止用户在单元格中输入无效数据。

“停止”警告消息具有两个选项：“重试”或“取消”。

警告在用户输入无效数据时向其发出警告，但不会禁止他们输入无效数据。

在出现“警告”警告消息时，用户可以单击“是”接受无效输入、单击“否”编辑无效输入，或单击“取消”删除无效输入。

信息通知用户，他们输入了无效数据，但不会阻止他们输入无效数据。

这种类型的出错警告最为灵活。

在出现“信息”警告消息时，用户可单击“确定”接受无效值，或单击“取消”拒绝无效值。

您可以自定义用户在出错警告消息中所看到的文本。

如果您选择不进行自定义，则用户看到的是默认消息。

仅当直接在单元格中键入数据时，才会出现输入信息和出错警告。

在下列情况下，不会出现这些消息：∙用户通过复制或填充输入了数据。

∙单元格中的公式计算出无效结果。

∙宏在单元格中输入了无效数据。

有关使用数据有效性的提示在以下列表中，您可以找到有关使用Excel数据有效性的提示和技巧。

∙如果您打算保护工作表或工作簿，请在指定任何有效性设置后执行。

在保护工作表之前，请确保解除锁定任何有效单元格。

否则，用户将无法在这些单元格中键入任何数据。

∙如果您打算共享工作簿，请在指定数据有效性和保护设置之后执行。

共享工作簿后，除非您停止共享，否则将无法更改有效性设置。

不过，在共享工作簿时，Excel将继续验证指定单元格是否有效。

∙您可以将数据有效性应用到已在其中输入数据的单元格。

但是，Excel不会自动通知您现有单元格包含无效数据。

在这种情况下，可以通过指示Excel在工作表上的无效数据周围画上圆圈来突出显示这些数据。

标识无效数据后，可以再次隐藏这些圆圈。

如果更正了无效输入，圆圈便会自动消失。

∙要快速删除单元格的数据有效性，请选择相应的单元格，然后打开“数据有效性”对话框（位于“数据”选项卡上的“数据工具”组中）。

在“设置”选项卡上，单击“全部清除”。

∙若要在工作表上查找具有数据有效性的单元格，请在“开始”选项卡上的“编辑”组中，单击“查找和选择”，然后单击“数据有效性”。

找到具有数据有效性的单元格后，可以更改、复制或删除有效性设置。

∙创建下拉列表时，可以使用“定义名称”命令（位于“公式”选项卡上的“定义的名称”组中）为包含该列表的区域定义名称。

在另一个工作表上创建该列表后，可以隐藏包含该列表的工作表，然后保护工作簿以使用户无法访问该列表。

如果数据有效性无效，请确保：用户未复制或填充数据数据有效性的设计用途是：仅当用户直接在单元格中键入数据时才显示消息并阻止无效输入。

在复制或填充数据时，不会出现消息。

若要防止用户通过拖放单元格来复制和填充数据，请清除“Excel选项”对话框（“文件”选项卡中的“选项”命令）的“高级”类别中的“启用填充柄和单元格拖放功能”复选框，然后保护工作表。

有关如何保护工作表的详细信息，请参阅保护工作表或工作簿元素。

关闭手动重算如果打开手动重算，则未计算的单元格可能会无法正常验证数据是否有效。

要关闭手动重算，请在“公式”选项卡上的“计算”组中，单击“计算选项”，然后单击“自动”。

公式没有错误确保有效单元格中的公式未产生#REF!或#DIV/0!等错误。

除非您更正了错误，否则Excel将忽略数据有效性。

公式中引用的单元格正确无误如果被引用的单元格发生了更改，以致于有效单元格中的公式计算出无效结果，则不会对该单元格显示有效性消息。

如何处理数据有效性警报当您尝试在工作表单元格中输入或更改数据时，会看到数据有效性错误警告。

此警告表示工作簿的所有者对该单元格应用了数据有效性以防止用户输入无效数据，并且已实施错误警告以通知您所输入的数据无效。

您只能在应用了数据有效性的单元格中输入有效数据。

如果不了解可输入数据的有效性的相关信息，应与工作簿所有者联系。

如果已继承工作簿，那么除非使用了您不知道的密码对工作表进行了保护，否则可以修改或删除数据有效性。

如果可能，您可以联系以前的所有者以帮助您取消对工作表的保护。

您也可以将数据复制到另一个工作表中，然后再删除数据有效性。

将数据有效性添加到单元格或区域在以下各部分中，您将了解向工作表单元格添加有效性的各种方法。

将数据输入限制为下拉列表中的值不能更改列表项的字体或字号。

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

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

问题：“数据有效性”命令不可用。

Excel表可能链接到SharePoint网站不能将数据有效性添加到链接到SharePoint网站的Excel表中。

要添加数据有效性，必须取消该Excel表的链接或将该Excel表转换为区域。

您当前可能正在输入数据当您在单元格中输入数据时，“数据”选项卡上的“数据有效性”命令不可用。

若要终止数据输入，请按Enter或Esc。

工作表可能受保护或处于共享状态如果工作簿处于共享状态或受保护，则无法更改数据有效性设置。

在“数据有效性”对话框中，单击“设置”选项卡。

1.在“允许”框中，选择“序列”。

2.单击“来源”框，然后键入用Microsoft Windows列表分隔符（默认情况下使用逗号）分隔的列表值。

∙例如：∙若要将对问题（如“您是否有小孩?”）的输入限制在两个选择，请键入“是,否”。

∙若要将供应商的质量信誉限制在三个等级，请键入“低,中,高”。

∙此外，还可以通过引用工作簿中其他位置的单元格区域来创建列表项。

∙注意下拉列表的宽度由具有数据有效性的单元格的宽度来确定。

您可能需要调整该单元格的宽度，以防将宽于下拉列表宽度的有效输入部分截断。

3.请确保选中“提供下拉箭头”复选框。

否则，将无法看到单元格旁边的下拉箭头。

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

∙注意如果允许值基于具有已定义名称的单元格区域，并且该区域中的任意位置存在空单元格，则选中“忽略空值”复选框将允许在有效单元格中输入任意值。

同样，被有效性公式引用的任何单元格也是如此：如果被引用的单元格为空，则选中“忽略空值”复选框将允许在有效单元格中输入任意值。

5.还可以选择在单击该单元格时显示一条输入信息。

∙如何显示输入信息∙单击“输入信息”选项卡。

∙确保选中“选定单元格时显示输入信息”复选框。

∙填充输入信息的标题和文本。

6.指定输入无效数据时希望Microsoft Office Excel如何响应。

∙如何指定对无效数据的响应∙单击“出错警告”选项卡，确保选中“输入无效数据时显示出错警告”复选框。

1.注意如果希望允许用户键入列表以外的内容，请改为清除“输入无效数据时显示出错警告”复选框。

∙请在“样式”框中选择下列选项之一：1.若要显示可输入无效数据的信息性消息，请选择“信息”。

2.若要显示可输入无效数据的警告消息，请选择“警告”。

3.若要防止输入无效数据，请选择“停止”。

∙填写消息的标题和文本（最多225个字符）。

1.注意如果没有输入标题或文本，则标题使用默认字符串“MicrosoftEx cel”，并显示如下消息（默认情况下）：“输入值非法。

其他用户已经限定了可以输入该单元格的数值。

”7.测试数据有效性以确保其正常工作。

尝试在单元格中输入有效和无效数据，以确保设置按预期方式工作并且显示所预期的消息。

提示如果更改了单元格的有效性设置，则可以将这些更改自动应用于具有相同设置的所有其他单元格。

为此，请打开“数据有效性”对话框，然后在“设置”选项卡上选中“对有同样设置的所有其他单元格应用这些更改”复选框。