Excel 2016︱数据验证的典型应用

限制输入指定区间的数据

示例35-1 　限制员工年龄范围

如图 35-3 所示，需要在员工信息表中输入员工年龄。因为员工年龄不会小于 18 岁，也不会大于 60 岁，因此输入员工年龄的区间应该是 18~60 之间的整数。通过设置数据验证，可以限制输入数据的区间范围。

操作步骤如下。

步 骤 1

选中 B2:B10 单元格区域，依次执行【数据】→【数据验证】命令，打开【数据验证】对话框。

步 骤 2

在【数据验证】对话框的【设置】选项卡下：

（1）单击【允许】下拉按钮，在下拉列表中选择【整数】选项。

（2）单击【数据】下拉按钮，在下拉列表中选择【介于】选项。

（3）在【最小值】编辑框内输入 18。

（4）在【最大值】编辑框内输入 60。

也可以单击【最小值】和【最大值】右侧的折叠按钮选择单元格地址，以单元格中的数据作为参照，最后单击【确定】按钮，如图 35-4 所示。

设置完成后，如果在 B2:B10 单元格区域输入 18~60 之外的内容，将弹出如图 35-5 所示的警告对话框，拒绝用户输入数据。

如果单击对话框中的【重试】按钮，当前单元格进入编辑状态，用户可继续尝试输入其他内容。如果单击【取消】按钮，则结束当前的输入操作。

限制输入重复数据

示例35-2 　限制输入重复身份证号码

如图 35-6 所示，需要在员工信息表中输入身份证号码。使用数据验证功能，可以避免信息重复输入。

操作步骤如下。

步 骤 1

选中需要输入身份证号的 C2:C10 单元格区域，依次执行【数据】→【数据验证】命令，打开【数据验证】对话框。

步 骤 2

在【设置】选项卡下单击【允许】下拉按钮，在下拉列表中选择【自定义】选项，在【公式】编辑框中输入以下公式，最后单击【确定】按钮，如图35-7 所示。

=COUNTIF(C:C,C2&'*')=1

因为身份证号码是 18 位数字，而 Excel 的数字处理精度是 15 位，因此对身份证号码中 15 位以后的数字都视为 0 处理。这种情况下，COUNTIF 函数对前 15 位相同的身份证号码，无论后 3 位是否一致，都会判断为相同。

COUNTIF 函数的第二参数使用 C2&'*'，表示查找以 C2 单元格内容开始的文本，最终返回 C列单元格区域中该身份证号码的个数，如果大于 1，则表示该身份证号码重复。

使用下拉菜单式输入

示例35-3 　使用下拉菜单输入员工性别

如图 35-8 所示，需要在员工信息表中输入性别。使用数据验证功能，可以实现下拉菜单式输入。

操作步骤如下。

步 骤 1

选中需要输入性别信息的 D2:D10 单元格区域。依次执行【数据】→【数据验证】命令，打开【数据验证】对话框。

步 骤 2

在【设置】选项卡下单击【允许】下拉按钮，在下拉列表中选择【序列】选项。

步 骤 3

保留右侧的【提供下拉箭头】复选框的选中，在【来源】编辑框中输入“男 , 女”，最后单击【确定】按钮，如图 35-9 所示。

设置完成后，单击单元格右侧的下拉按钮，即可在下拉菜单中选择输入内容。

制作二级下拉菜单

结合自定义名称和 INDIRECT 函数，可以方便地创建二级下拉列表，二级下拉列表的选项能够根据第一个下拉列表输入的内容调整范围。

示例35-4 　制作客户信息二级下拉菜单

如图 35-10 所示，在客户信息表的 B 列使用下拉菜单选择不同的省份，C 列的下拉菜单中就会出现对应省份的部分城市名称。

操作步骤如下。

步 骤 1

准备一个包含客户所在省份和对应城市名称的对照表，如图 35-11 所示。

步 骤 2

切换到“客户区域对照表”工作表，按 <F5> 功能键调出【定位】对话框，单击【定位条件】按钮，在弹出的【定位条件】对话框中选中【常量】单选按钮，然后单击【确定】按钮。此时表格中的常量全部被选中，如图 35-12 所示。

步 骤 3

依次单击【公式】→【根据所选内容创建】按钮，在弹出的【以选定区域创建名称】对话框中选中【首行】复选框，然后单击【确定】按钮，完成创建定义名称，如图 35-13 所示。

步 骤 4

创建省份下拉列表。

切换到 Sheet1 工作表，选中要输入省份的 B2:B8 单元格区域，依次执行【数据】→【数据验证】命令，打开【数据验证】对话框。在【允许】下拉列表中选择【序列】选项，单击【来源】编辑框右侧的折叠按钮，选中“客户区域对照表”工作表的 A1:F1 单元格区域，单击【确定】按钮关闭对话框，如图 35-14 所示。

步 骤 5

创建二级下拉列表。

选中要输入城市名称的 C2:C8 单元格区域，依次执行【数据】→【数据验证】命令，打开【数据验证】对话框。在【允许】下拉列表中选择【序列】，在【来源】编辑框输入以下公式，单击【确定】按钮关闭对话框，如图 35-15 所示。

=INDIRECT(B2)

步 骤 6

此时会弹出【源当前包含错误。是否继续？】的警告对话框，这是因为 B2 单元格还没有输入省份内容，INDIRECT 函数无法返回正确的引用结果，单击【确定】按钮即可，如图 35-16 所示。

设置完成后，在 B 列单元格选择不同的省份，C 列的城市下拉列表就会动态变化。

但是通过这样设置的数据验证，在 B 列没有输入省份的情况下，C 列可以手工输入任意内容，且不会有任何错误提示，如图 35-17 所示。

可以选中 C2 单元格，打开【数据验证】对话框。取消【忽略空值】复选框的选中，再选中【对有同样设置的所有其他单元格应用这些更改】复选框，单击【确定】按钮关闭对话框，如图 35-18所示。

设置完成后，再次尝试在 C 列手工输入不符合项的内容，Excel 就会弹出警告对话框，拒绝用户输入。

制作动态二级下拉菜单

使用 35.2.4 小节的方法创建二级下拉菜单，其优点是操作简单，缺点是不能随着数据的变化实现动态引用。如果在“客户区域对照表”中添加客户信息，Sheet1 中的下拉菜单将无法自动更新。

示例35-5 　制作可动态扩展的二级下拉菜单

仍以 35.2.4 小节中的数据为例，学习制作动态二级下拉菜单的方法，操作步骤如下。

步 骤 1

切换到“客户区域对照表”工作表。依次执行【公式】→【定义名称】命令，弹出【新建名称】对话框，在【名称】文本框中输入“客户区域”，在【引用位置】编辑框内输入以下公式，单击【确定】按钮，如图 35-19 所示。

=OFFSET($A$1,,,,COUNTA($1:$1))

步 骤 2

切换到 Sheet1 工作表，选中要输入省份的 B2:B10 单元格区域，依次执行【数据】→【数据验证】命令，打开【数据验证】对话框。将【允许】类型设置为【序列】，在【来源】编辑框内输入以下公式，如图 35-20 所示。

= 客户区域

步 骤 3

选中 C2 单元格，依次执行【公式】→【定义名称】命令，弹出【新建名称】对话框，在【名称】文本框中输入“客户城市”，在【引用位置】编辑框内输入以下公式，单击【确定】按钮，如图 35-21 所示。

=OFFSET( 客 户 区 域 对 照 表 !$A$2,,MATCH($B2, 客 户 区 域 对 照 表 !$1:$1,)-1,COUNTA(OFFSET(客户区域对照表!$A$2,,MATCH($B2,客户区域对照表!$1:$1,)-1,100)))

步 骤 4

选中要输入城市的 C2:C10 单元格区域，依次执行【数据】→【数据验证】命令，打开【数据验证】对话框。将【允许】类型设置为【序列】，在【来源】编辑框内输入以下公式，如图 35-22 所示。

= 客户城市

设置完成后，在 B 列选择客户省份，单击 C 列单元格

中的下拉按钮，就可以选择对应的城市选项。如果“客户区域对照表”中的数据发生变化，下拉列表中的选项会自动更新。

自动切换输入法模式

在【数据验证】的【输入法模式】选项卡下，有【随意】【打开】和【关闭（英文模式）】3 个模式选项，默认选项为【随意】，如图 35-23 所示。

在使用此选项时，需要计算机内安装有英文键盘输入法，并且在计算机的【设置】→【时间和语言】→【区域和语言】选项中，将中文设置为系统默认语言，如图 35-24 所示。

示例35-6 　自动切换输入法模式

如图 35-25 所示，需要在设备登记表中分别输入设备名称和规格型号。设备名称为中文，规格型号为英文字母和数字的组合。设置输入法模式后，在不同列输入内容时，系统能够自动切换输入法模式。

操作步骤如下。

步 骤 1

选中需要输入中文的 A2:A6 单元格区域，依次执行【数据】→【数据验证】命令，打开【数据验证】对话框。切换到【输入法模式】选项卡下，在【模式】下拉列表中选择【打开】选项，单击【确定】按钮，关闭对话框。

步 骤 2

选中需要输入英文 + 数字的 B2:B6 单元格区域，依次执行【数据】→【数据验证】命令，打开【数据验证】对话框。切换到【输入法模式】选项卡下，在【模式】下拉列表中选择【关闭（英文模式）】选项，最后单击【确定】按钮完成设置。

圈释无效数据

通常情况下，用户使用数据验证来限制输入的内容。对于已经输入的内容，也可以使用圈释无效数据功能，方便地查找出不符合要求的数据。

示例35-7 　圈释重复员工工号

如图 35-26 所示，是某单位员工信息表的部分内容，利用【圈释无效数据】功能，能够检查工号是否为重复输入。

操作步骤如下。

步 骤 1

选中 B2:B9 单元格区域，依次执行【数据】→【数据验证】命令，打开【数据验证】对话框。设置数据验证的【允许】规则为【自定义】，在公式编辑框中输入以下公式，限制 B 列输入的内容不能有重复项，如图 35-27 所示。

=COUNTIF(B:B,B2)=1

步 骤 2

依次选择【数据】→【数据验证】→【圈释无效数据】，如图 35-28 所示。

设置完成后，在不符合要求的有重复数据单元格上，都添加了红色的标识圈。将单元格修改为符合规则的数据后，标识圈将不再显示。

如需清除标识圈，可以在【数据验证】下拉菜单中选择【清除验证标识圈】命令或按 <Ctrl+S> 组合键。

---------------------------------------------------------------------