自动建议下拉菜单_数据有效性的N个典型应用，建议收藏

Excel中有一位免费的小秘书，专门对咱们输入的数据进行验证审核的，你知道她藏在哪儿吗？

哈哈哈，有小伙伴可能已经知道了，这就是默默无闻的“数据验证”，曾用名“数据有效性”。 如果咱们提前设置好了允许录入的规则，当输入不符合条件的数据时，Excel就会弹出对话框，阻止我们录入非法数据。 接下来咱们就说说数据验证的几种典型用法：

01

限制年龄范围 

因为员工年龄不会小于18岁，也不会大于60岁，因此输入员年龄的区间应该是18~60之间的整数。通过设置数据验证，可以限制输入数据的区间范围。

02

限制输入重复数据 

在数据验证中，如果公式结果等于TRUE或是不等于0的任意数值，Excel允许录入，否则Excel将拒绝录入。

选中A2:A10，设置数据验证，自定义公式为：

=COUNTIF(A:A,A2)=1

其中的A2，是所选区域的活动单元格。

03

用下拉菜单限制输入固定内容

04

圈释无效数据

通常情况下，用户使用数据验证来限制输入的内容。

对于已经输入的内容，也可以先设置好数据验证规则，然后使用圈释无效数据功能，方便地查找出不符合要求的数据。

05

各项预算不能超过总预算 

如下图所示，是某人的育儿计划表，从幼儿园到结婚计划预算180万元，要求各分项预算之和不能超过总预算。

选中B2:B7单元格区域，数据→数据验证→自定义，输入以下公式：

=SUM($B$2:$B$7)<=$D$2

设置完成后，B列各分项之和超过D2单元格的预算，就会弹出错误提示。

06

根据其他列内容限制输入 

如下图所示，是某公司员工信息调查表，D列的配偶姓名填写时，要求C列的婚否一项中必须为“是”，否则禁止录入。

选中D2:D6单元格区域，数据→数据验证→自定义，输入以下公式。

=C2="是"

07

限制录入周末日期 

如下图所示，是某人的工作计划表，B列的拟定日期填写时，要求不能录入周末日期。

选中B2:B6单元格区域，数据→数据验证→自定义，输入以下公式：

=WEEKDAY(B2,2)<6

WEEKDAY(B2,2) ，根据B2单元格的日期，返回对应的星期。 第二参数使用2，用数字1~7来表示周一到周日。WEEKDAY(B2,2)<6，就是限定录入日期小于周六了。

08

制作下拉菜单 

09

动态扩展的下拉菜单 

如下图所示，要根据A列的对照表，在D列生成下拉菜单，要求能随着A列数据的增减，下拉菜单中的内容也会自动调整。

选中要输入内容的D2:D10单元格区域，数据→数据验证→序列，输入以下公式。

=OFFSET($A$2,0,0,COUNTA($A:$A)-1)

公式表示以A2作为基点，向下偏移0行，向右偏移0列，新引用的行数为COUNTA函数统计到的A列非空单元格个数，结果-1，是因为A1是表头，计数要去掉。 这样就是A列有多少个非空单元格，下拉菜单中就显示多少行。

10

动态二级下拉菜单 

如下图所示，A、B列是客户城市和县区的对照表，在D列已经生成一级下拉菜单，要求在E列生成二级下拉菜单，要求能随着D列所选不同的一级菜单，E列下拉菜单中的内容也会自动调整。 选中要输入内容的E2:E6单元格区域，数据→数据验证→序列，输入以下公式：

=OFFSET($B$1,MATCH($D2,$A$2:$A$16,0),0,COUNTIF($A:$A,$D2))

公式表示以B1为基点，以MATCH函数得到的城市首次出现的位置作为向下偏移的行数。 向右偏移的列数为0。 新引用的行数为COUNTIF($A:$A,$D2)的计算结果。 COUNTIF($A:$A,$D2)的作用是： 根据D列以及菜单中的城市名在A列统计有多少个与之相同的城市个数。有多少个城市名，OFFSET函数就引用多少行。

作者：祝洪忠

原载：Excel之家ExcelHome

处处留心皆学问，建议大家可以将这篇推文收藏，以备不时之需。 处处留心皆学问，建议大家将这篇推文收藏，以备不时之需。

你点的每个"在看"我都认真当成了喜欢▼