Excel数据有效性

Excel数据验证技巧如何设置有效性约束

Excel是一款功能强大的电子表格软件，在商务和学术领域广泛应用。为了确保数据的准确性和一致性，Excel提供了数据验证功能，可以通过设置有效性约束来限制输入数据的范围和格式。本文将介绍Excel中的数据验证技巧，帮助读者更好地掌握有效性约束的设置方法。

1. 基本概念

在Excel中，有效性约束是一种规则，用于限制单元格中输入的数据。它可以确保数据符合特定条件，比如数值范围、文本长度、日期格式等。通过设置有效性约束，可以避免输入错误和数据不一致性，提高数据处理的准确性。

2. 设置有效性约束

2.1 打开数据验证对话框

在Excel中，设置有效性约束的第一步是打开数据验证对话框。选择需要设置约束的单元格，然后点击“数据”选项卡，再点击“数据验证”按钮。或者使用快捷键Alt+D+L。

2.2 选择验证条件

在数据验证对话框中，选择“设置”选项卡，可以看到多种验证条件可供选择。根据实际需求，选择适合的验证条件，比如整数、小数、日期、时间、文本长度等。

2.3 配置验证规则 在验证条件选择完毕后，需要配置具体的验证规则。根据选定的条件，在相应的输入框中输入约束规则。例如，如果选择整数条件，可以设置最小值、最大值等参数；如果选择文本长度条件，可以设置最小长度、最大长度等参数。

2.4 配置输入消息和错误警告

在数据验证对话框的“输入消息”选项卡中，可以配置一个输入提示，以提醒用户输入数据的要求或输入的意义。而在“错误警告”选项卡中，可以配置一个错误警告，以提醒用户输入错误或不合法数据时的提示。这些消息和警告对于保证数据输入的准确性和规范性非常重要。

2.5 应用有效性约束

完成对数据验证对话框中的设置后，点击“确定”按钮，就可以将有效性约束应用到选择的单元格中。此时，输入的数据将会受到设置的约束条件的限制。

3. 实际应用场景

有效性约束在实际应用中非常广泛。下面列举几个常见的应用场景。

EXCEL中数据有效性“自定义”怎么使用

[应用一]下拉菜单输入的实现

例1：直接自定义序列

有时候我们在各列各行中都输入同样的几个值，比如说，输入学生的等级时我们只输入四个值：优秀，良好，合格，不合格。我们希望Excel2000单元格能够象下拉框一样，让输入者在下拉菜单中选择就可以实现输入。

操作步骤：先选择要实现效果的行或列；再点击"数据\有效性"，打开"数据有效性"对话框；选择"设置"选项卡，在"允许"下拉菜单中选择"序列"；在"数据来源"中输入"优秀，良好，合格，不合格"（注意要用英文输入状态下的逗号分隔！）；选上"忽略空值"和"提供下拉菜单"两个复选框。点击"输入信息"选项卡，选上"选定单元格显示输入信息"，在"输入信息"中输入"请在这里选择"。

例2：利用表内数据作为序列源。

有时候序列值较多，直接在表内打印区域外把序列定义好，然后引用。

操作步骤：先在同一工作表内的打印区域外要定义序列填好(假设在在Z1：Z8），如“单亲家庭，残疾家庭，残疾学生，

特困，低收人，突发事件，孤儿，军烈属”等，然后选择要实现效果的列（资助原因）；再点击"数据\有效性"，打开"数据有效性"对话框；选择"设置"选项卡，在"允许"下拉菜单中选择"序列"；“来源”栏点击右侧的展开按钮（有一个红箭头），用鼠标拖动滚动条，选中序列区域Z1：Z8（如果记得，可以直接输入=$Z$1:$Z$8；选上"忽略空值"和"提供下拉菜单"两个复选框。点击"输入信息"选项卡，选上"选定单元格显示输入信息"，在"输入信息"中输入"请在这里选择"。

例3：横跨两个工作表来制作下拉菜单

用INDIRECT函数实现跨工作表

在例2中，选择来源一步把输入=$Z$1:$Z$8换成=INDIRECT("表二!$Z$1:$Z$8"),就可实现横跨两个工作表来制作下拉菜单。

[应用二]自动实现输入法中英文转换

有时，我们在不同行或不同列之间要分别输入中文和英文。我们希望Excel能自动实现输入法在中英文间转换。

1、下拉列表选择数据 （直接输入）

我们可以使用“数据→有效性”来进行处理。具体操作方法为：先选中要输入性别的单元格，比如C4:C50，选择菜单“数据→有效性”命令，打开“数据有效性”对话框，单击“设置”选项卡的“允许”下拉箭头，在弹出的菜单中选择“序列”项，然后在“来源”框中输入“男,女”，注意其中的逗号是在英文状态下输入的。单击“确定”即可。

2、下面就通过一个编排教师的课表为例教大家如何实现，该Excel表格能在填表时选择教师姓名，并能在另一列表中选择他所负责的课程名称。

一 创建数据源表

在sheet2表中输入教师姓名以及所负责的课程，把教师姓名横放在第2行。选中B2:F2，即教师姓名。然后在名称框为它输入一个名字“name”（图1），输入完成后一定要按回车，转到sheet1工作表。

二 数据关联

为了在sheet1表引用name名称，在教师姓名列下拉框选（B3:B9）单元格，点击菜单栏中的“数据→有效性”，在弹出的“数据有效性”对话框中选择“设置”选项卡，在“允许”选择框中选择“序列”，在来源输入框中输入“=name”（图2），点击“确定”后，在下拉列表中就可选择各个教师了。

提示：现在就可体会出名称框的妙用，因为来源的拾取按钮是不能跨表去拾取其他表的数据的。

第二步就是实现能够自动选择教师所负责的课程，由于教师姓名是变动的，要求负责的课程名称也要随之变动。负责课程这一列中的有效性数据来自于教师姓名这一列，怎么解决这个问题？同样，我们可用名称框来解决。

回到sheet2表，用不着给表中的每个教师的课程单独取名，很麻烦也很耽误时间。把整个区域选中（B2:F6），用每一列的第一行数据取名，点击“插入→名称→指定”，在指定名称对话框中只选中“首行”（图3），点击“确定”后就可在sheet1表中使用了。

转到sheet1表，把负责课程列下的区域选中（C3:C9），点击“数据→有效性→序列”。接着就要注意来源输入框中的内容了，因为不能等于单元格，在这里希望引用教师姓名所对应的名称里的数据来做下拉列表，这里要用到函数indirect，它表示从某一单元格中取数据，然后把此数据转换成一个区域。在来源输入框中输入“＝indirect（”，点击B3单元格，出现“=indirect($B$3)”，这里是绝对引用，按F4键改成相对引用 “=indirect(B3)”，确定后会有一个警告提示框，源目前包含错误，是否继续（图4）？点击“是”继续就行了。

Excel -- 数据有效性—在下拉列表中隐藏前面使用过使用

过的条目

设置主表 创建列表条目 创建有效列表 应用数据有效性 测试数据有效性

在数据有效性列表中你可以限制选择条目, 隐藏前面选择过的条目.

例如, 某公司分派员工出差，为避免重复派出，即已经派出在外的员

工再次派出则会造成失误。

这时，你就可以设计在有效性下拉列表中，移除已经使用过的条目（即

已经派出的员工不会再出现在侯选列表中）.

设置主表

首先设计你想要运用数据有效性的表格布局. 本例中, 使用了工作

表'时间表' 中的A1:C7 单元格范围.在B列使用了数据有效性.

创建列表条目

创建一个你想在数据有效性下拉列表看到条目的列表. 在这里, 在

工作表“员工”的A1:A6, 单元格输入员工姓名

创建有效列表

A) 输入一个统计已经派出的员工名单的公式.

1. 在“员工”工作表的B1单元格输入下列公式:

=IF(COUNTIF(时间表!$B$2:$B$7,A1)>=1,"",ROW())

2. 将公式向下复制到B6单元格. 这个公式统计 "程香宙" 在工作表“时间表”中B2:B7区域出现次

数. 如果返回结果大于或等于1, 单元格将显示为空白.否则则返回

行数.

B) 创建一个没有使用的名称列表

下一步将使用一个数组公式将空白的单元格移动到序列的最后边.

1. 在C1单元格输入数组公式 (这个公式很长，但在同一行输入完

成).

=IF(ISNUMBER(SMALL(IF($B$1:$B$6="","",ROW($B$1:$B$6

)),ROW(1:1))),

INDIRECT("A"&SMALL(IF($B$1:$B$6="","",ROW($B$1:$B$6)),ROW(1:1))),"")

2. 按组合键Ctrl+Shift+Enter输入数组公式

3. 向下复制公式直到C6单元格.

定义有效列表名称

1. 选择插入>名称>自定义 2. 在名称框中输入一个名称，例如 NameCheck.