一起认识数据有效性

祝洪忠-

数据有效性不仅能够对单元格的输入数据进行条件限制，还可以在单元格中创建下拉列表菜单方便用户选择输入。今天咱们就来共同学习一下数据有效性的使用。
在日常工作中，咱们经常会看到如下图这样的下拉列表式的输入：

这样的输入方法不仅非常简便快捷，而且可以从源头上约束输入内容的一致性，避免同一个姓名输入时出现类似“高翠兰”和“高翠蓝”这样的同音字错误。

这样的下拉列表式输入实现起来其实非常简单。选择需要设置数据有效性的单元格区域，依次单击【数据】，【数据有效性】，弹出【数据有效性】对话框，单击【设置】选项卡【允许】输入框右侧的按钮，在下拉列表中选择【序列】。

在【允许】输入框的下拉列表中，有多种选项，我们可以根据自己的需要进行选择。

选择“序列”选项后，可以在【来源】输入框中直接输入文字，词组之间要用半角的逗号隔开。如果需要设置数据有效性的输入为性别，这里可以直接输入”男,女”。如果允许数据有效性的内容较多，可以引用工作表内的单元格区域或是其他工作表的数据区域做为数据来源。

这里需要说明一点，如果需要引用其他工作表的数据区域作为数据有效性的数据来源，不同Excel版本有所差异。

在Excel 2010和Excel 2013版本中（Excel 2013的数据有效性改名了，叫“数据验证”），可以点击【来源】右侧的选取按钮选择其他工作表的数据区域。

在Excel 2007版本中，点击【来源】右侧的选取按钮，则不能选取其他工作表的数据区域，并且无任何提示。

虽然无法直接选取，但是可以在输入框中直接输入带有工作表名称的单元格区域地址，并且不会影响数据有效性的使用。

在Excel 2003版本中使用其他工作表的数据区域作为数据有效性的数据来源，则相对麻烦一些。需要先在员工花名册工作表中自定义名称。

然后在当前工作表中，设置数据有效性的序列来源等于自定义的名称。

单元格设置数据有效性后，输入的内容就可以受到限制了，当我们尝试输入不在允许范围的数据，Excel就会弹出一个警示窗口。

单击“重试”按钮，可以在单元格再次输入其他内容，如果单击取消，则结束当前的输入。

如果感觉这个警告对话框有点生硬，咱们可以定义成自己喜欢的提示文字。

依次单击【数据】，【数据有效性】，弹出【数据有效性】对话框。单击【出错警告】选项卡，在标题输入框和错误信息输入框中输入自定义的警告信息，单击确定。这样当再次输入不在允许范围的数据，Excel就会弹出自定义警示信息的窗口。

朋友们可能注意到了，在【数据有效性】对话框的【出错警告】选项卡下，还有一个样式的选项。单击下拉按钮会有“停止”，“警告”，“信息”三个选项供我们选择。

默认选项是停止，也就是咱们刚刚看到的这些提示信息。

如果选择其他两个选项会有什么样的变化呢？咱们一起来看一下。

首先在样式下拉列表中选择“警告”，单击【确定】。然后在设置了数据有效性的单元格内，输入一个不在允许序列内的名字，就会出现下面这样的提示窗口。

单击“取消”，结束当前的输入。单击“否”，单元格变成活动单元格，已经输入的内容变成黑色选中状态，等待我们再次输入内容。

单击“是”，则可以输入不符合数据有效性条件的数据。

接下来在样式下拉列表中选择“信息”，单击【确定】。然后在设置了数据有效性的单元格内，输入一个不在允许序列内的名字，就会出现下面这样的提示窗口。

单击“取消”，结束当前的输入。单击“是”，则可以输入不符合数据有效性条件的数据。

祝洪忠-

除了直接引用单元格区域作为序列来源，还可以设置动态的数据区域引用，让有效性中的下拉列表能够随着数据源的增减动态调整。

首先说一下跨工作表引用有效性序列来源的方法：

在员工花名册工作表中自定义名称：

花名册=OFFSET(员工花名册!$A$2,,,COUNTA(员工花名册!$A:$A)-1)

然后在员工出勤表工作表中设置数据有效性。有效性条件设置为允许序列，来源输入框中写上自定义的名称。

咱们对自定义名称中公式的意思简单说明一下。

公式中的“COUNTA(员工花名册!$A:$A)”部分，用来计算员工花名册!工作表A列非空单元格的个数。

OFFSET函数的作用是以指定的引用为参照系，通过给定偏移量返回新的引用。

整个公式的意思是，以员工花名册!$A$2单元格为基点，向下偏移的行数为0行，向右偏移的列数为0列，新引用的行数为A列非空单元格个数减去1。因为不需要引用员工花名册A1单元格的列标题，所以这里减去1。

设置完数据有效性，下拉列表的最后一个姓名是“陈秀雯”。

如果在员工花名册工作表中删除部分姓名，员工考勤表工作表的数据有效性下拉列表内容就会自动更新。

如果使用同一工作表内的数据作为有效性序列来源，也可以使用插入列表的方法来实现动态更新。

单击数据源任意单元格，依次单击【插入】，【表格】，弹出【创建表】对话框。Excel会自动判断数据区域，因为G1单元格是列标题，所以这里保留“表包含标题”的勾选，单击【确定】。

选中A2:A9单元格区域，依次单击【数据】，【数据有效性】，在弹出的【数据有效性】对话框中，有效性条件选择序列，来源输入框中写上：

=$G$2:$G$9

设置完数据有效性后，如果在G列的数据源中增加数据，A列的有效性下拉列表就会自动更新。

注意，这种方法仅限于数据源在当前工作表内，如果需要跨工作表引用，则只能使用自定义名称的方法。

对于已经输入的内容，再设置数据有效性后，如何标识不符合要求的内容呢？接下来咱们就一起来看一下，下图中，员工出勤表的A列已经用数据有效性的下拉列表输入了部分内容。

这时候，咱们将数据源员工花名册最后几个单元格的内容清除：

然后在员工出勤表中单击【数据】，再单击【数据有效性】按钮下面的小三角，在下拉列表中选择【圈释无效数据】，Excel会判断已经输入的内容是否符合有效性条件，对不符合条件的，会自动添加一个红色的标识。

如果需要清除这些标识，只要单击【数据】，再单击【数据有效性】按钮下面的小三角，在下拉列表中选择【清除无效数据标识圈】即可。

如果需要清除数据有效性，首先选中数据区域，依次单击【数据】，【数据有效性】，在【数据有效性】对话框中单击【全部清除】，单击【确定】。

祝洪忠-

除了下拉列表式的输入，数据有效性还有一个非常有魅力的应用，就是二级（多级）下拉菜单。如图所示，在A列输入不同的省份，B列的有效性下拉列表中就会出现对应省份的城市名称。

制作这样的二级下拉菜单，需要准备一个包含层级对应关系的对照表。

首先要将对照表中的内容自定义名称。按F5，弹出定位对话框，单击【定位条件】按钮，在定位条件对话框中选择【常量】，单击【确定】，使所有文本部分都处于选中状态。

再依次单击【公式】，【根据所选内容创建】，在弹出的【以选定区域创建名称】对话框中，保留默认的“首行”勾选，单击【确定】。

这时候，如果按Ctrl+F3打开名称管理器，就会看到以对照表A1:E1单元格中的内容为名称的自定义名称。

接下来，在信息表的A列设置数据有效性。选中A2:A9单元格区域，依次单击【数据】，【数据有效性】，允许序列来源=对照表!$A$1:$E$1。

再对B列数据区域设置数据有效性。选中B2:B9单元格区域，依次单击【数据】，【数据有效性】，允许序列来源=INDIRECT(A2)，单击确定，会弹出“源当前包含错误，是否继续”的警告，这是因为A列没有输入内容，INDIRECT函数返回错误结果，单击确定即可。

至此，一个二级下拉菜单就制作完成了。

通过这样设置的数据有效性，在A列没有内容的情况下，B列可以直接输入任意内容，且不会有任何提示。

这种情况如何避免呢？咱们再回到数据有效性的设置对话框中，去掉“忽略空值”的勾选，当再次尝试A列空白的情况下，在B列输入内容就会拒绝输入，并且弹出警告。

如果需要更改数据有效性的规则，可以单击已设置数据有效性的任意单元格，依次单击【数据】，【数据有效性】，在【数据有效性】对话框中修改规则，勾选“对有同样设置的所有其他单元格应用这些更改”，单击确定即可。

祝洪忠-

朋友们可能注意到了，在数据有效性对话框中，除了咱们前面介绍的【设置】和【出错警告】，还有【输入信息】和【输入法模式】两个选项卡没有介绍，接下来，咱们就分别看一下这两个选项卡下的内容。

先看一下【输入法模式】选项卡，这里面有三个选项，分别是“随意”、“打开”和“关闭（英文模式）”。相信很多人会对这里的输入法模式有困惑，很多时候无论怎么设置都不起作用，这是因为我们的电脑大都删除了系统自带的英文键盘输入法。

下面讲一下它的具体设置方法：

首先，要保证电脑内安装有英文键盘输入法。在计算机文字服务和输入语言选项中，将默认的输入语言设置为中文输入法。

以下图为例，选择C2：C9单元格区域，依次单击【数据】，【数据有效性】，单击【输入法模式】选项卡，在“模式”下拉列表中选择“关闭（英文模式）”，单击【确定】完成设置。

当鼠标单击C2:C9区域中的单元格时，系统自动切换到英文输入法，而单击其他单元格时，则变成中文输入法。

同理，如果计算机文字服务和输入语言选项中，我们将默认的输入语言设置为英文输入法。然后再设置【数据有效性】【输入法模式】选择“打开”。单击设置了输入法模式的单元格，系统会自动切换到中文输入法。

这里要注意一点，就是设置计算机文字服务和输入语言选项后，需要重新打开Excel程序，否则输入法模式的设置无效。

接下来再看一下数据有效性中【输入信息】的设置。

选择数据区域，依次单击【数据】，【数据有效性】，在【输入信息】选项卡中，标题文本框中输入“注意”，输入信息文本框中输入文字“请在此输入英文大写代码”，单击【确定】完成设置。

当鼠标点击该区域的单元格时，就会出现一个提示框，提醒录入者正确输入。

利用数据有效性，还可以限制重复数据的录入，在录入数据出现重复时，Excel拒绝录入，并弹出警告：

具体设置的方法如下：

选择C2:C9单元格区域，依次单击【数据】，【数据有效性】，有效性条件选择“自定义”。

在公式输入框中输入：

=COUNTIF(C:C,C2)=1

单击【确定】，完成设置。

COUNTIF函数用于计算指定区域中满足给定条件的单元格数目，这里的第二参数C2，是需要设置数据有效性单元格区域的左上角第一个单元格，也就是常说的活动单元格。

公式的意思是，C列中等于当前单元格内容的单元格个数为1。

数据有效性自定义中的公式，可以根据我们的需要进行灵活的个性化设置，以满足不同的数据输入要求。

最后需要说明的一点，数据有效性只对手工输入的内容进行限制，对复制粘贴过来的内容或是外部导入的数据不起作用。

祝洪忠-

Excel默认只能选择一列多行（垂直方向）或是一行多列（水平方向）的数据源作为数据有效性的序列来源。

以下图为例，选择E2:E10单元格区域设置数据有效性。在序列来源中选择=$A$2:$C$10，单击确定，Excel会拒绝输入并弹出警告。

如果我们将A2:C10单元格区域自定义名称后，能不能在数据有效性中引用呢，咱们来试一下。

选择A2:C10单元格区域，依次单击【公式】，【定义名称】，弹出【新建名称】对话框。在名称文本框中输入自定义文字：姓名，引用位置输入=Sheet1!$A$2:$C$10，单击【确定】。

选择E2:E10单元格区域设置数据有效性。在序列来源中输入自定义的名称：“=姓名”，单击【确定】，Excel同样也会拒绝输入并弹出警告。

对于这种情况，如何突破限制呢？

首先来自定义名称。选择A2:A10单元格区域，依次单击【公式】，【定义名称】，弹出【新建名称】对话框，在“名称”文本框中输入自定义文字“姓名”，引用位置输入：=Sheet1!$A$2:$A$10，单击【确定】。

选择E2:E10单元格区域设置数据有效性。在序列来源中输入自定义的名称：=姓名  单击【确定】。

此时，E列单元格区域数据有效性的下拉列表中只包含数据源A2:A10单元格区域的内容。

接下来就是关键的步骤——编辑自定义名称的引用范围。按Ctrl+F3调出名称管理器，单击【编辑】，弹出【编辑名称】对话框。在引用位置输入框中，将引用区域改成=Sheet1!$A$2:$C$10，单击【确定】。然后关闭名称管理器窗口。

再来看一下E列单元格区域数据有效性的下拉列表，就已经包含了数据源A2:C10单元格区域中的所有姓名了。

DZ潇潇

泪奔呀，祝老师新帖上市了，占个6楼近观。
（老婆在另一台电脑上看乡村爱情故事，让我的网速如蜗牛，也让我泪奔……）

cmccsq

谢谢祝教师，近距离观看。

little-key

老祝这个帖子比我原来写的更加详尽

qqyyh

向祝老师学习了！！！！！！！！！！！！！！！

www.zhp

谢谢祝教师