利用数据验证功能设置下拉菜单（快速制作多级动态下拉菜单）

Excel的最大特点就是数据的分析与处理，但分析与处理的前提是数据，而且是正确有效的数据，如果数据源中的数据都是无效的，其分析和处理的结果肯定是不准确的，或者是无法分析的。所以，保证正确有效的数据，是数据处理和分析的前提，此时，可以用【数据验证】来完成。

在Excel2013及更高版本中，【数据验证】，在前期的版本中，称之为【数据有效性】，但功能是相同的。

---

一、数据验证：数值范围。

目的：将销售员的年龄控制在18-60岁之间。

方法：

1、选定目标单元格，【数据】-【数据验证】。

2、选择【允许】中的【整数】，【数据】中的【介于】，【最小值】中输入：18，【最大值】中输入：60并【确定】。

解读：

1、【数据】中除了【介于】之外，还有【未介于】、【等于】、【不等于】、【大于】、【小于】、【大于或等于】、【小于或等于】，可以根据实际需要选择。

2、如果数值类型为小数，其用法和【整数】的用法相同。

---

二、数据验证：下拉列表。

目的：选择政治面貌。

方法：

1、选定目标单元格，【数据】-【数据验证】。

2、选择【允许】中的【序列】，单击【来源】右侧的箭头，选择需要显示的内容，并单击箭头返回。

3、【确定】。

解读：

【序列】中的【来源】，除了选择外，还可以手动输入，如本示例中的输入内容为：中共党员,中共预备党员,共青团员,群众 ，每个内容之间用英文逗号（,）分割即可。

---

三、数据验证：文本长度。

目的：正确输入手机号码。

方法：

1、选定目标单元格，【数据】-【数据验证】。

2、选择【允许】中的【文本长度】，【数据】中的【等于】，在【长度】中输入：11并【确定】。

解读：

1、“11”为手机号码的长度，如果为身份证号码，则为：18，灵活应用。

2、【数据】中除了【等于】外，还有【介于】、【未介于】、【不等于】、【大于】、【小于】、【大于或等于】、【小于或等于】，可以根据实际需要选择。

---

四、数据验证：自定义。

目的：禁止输入重复值。

方法：

1、选定目标单元格，【数据】-【数据验证】。

2、选择【允许】中的【自定义】，并在【公式】中输入：=COUNTIF(B:B,B3)=1并【确定】。

解读：

Countif函数的作用为单条件计数，语法结构为：=Countif(条件范围,条件)，公式=COUNTIF(B:B,B3)=1中，计算指定单元格的值在B列中的个数，如果＞1就会提示错误。

---

五、数据验证：圈释无效数据。

目的：将无效的数据用红色椭圆圈圈起来。

方法：

1、选择已经有数据的目标单元格，【数据】-【数据验证】。

2、选择【允许】中的【序列】，单击【来源】右侧的箭头，选择需要显示的内容，并单击箭头返回。

3、【确定】。

4、【数据验证】-【圈释无效数据】。

解读：

如果数据不符合下拉列表中的值，则会被标识。

---

六、数据验证：不能超过指定值。

目的：各部门的预算金额不能超过10000元。

方法：

1、选定目标单元格，【数据】-【数据验证】。

2、选择【允许】中的【自定义】，在【公式】中输入：=SUM($C$3:$C$7)<=$D$4并【确定】。

解读：

如果当前选定的单元格区域的和值≤D4单元格的值（10000），则不会提示，如果大于则会报错，无法填充值。

---

七、数据验证：关联输入。

目的：如果“已婚”，可以输入“配偶姓名”，如果“未婚”，不允许输入“配偶姓名”。

方法：

1、选定目标单元格，【数据】-【数据验证】。

2、选择【允许】中的【自定义】，在【公式】中输入：=D3="已婚"并【确定】。

解读：

公式中的“D3”为条件所在列的单元格。

---

八、数据验证：禁用周末。

目的：销售日期中禁止录入周末日期。

方法：

1、选定目标单元格，【数据】-【数据验证】。

2、选择【允许】中的【自定义】，在【公式】中输入：=WEEKDAY(D3,2)<6并【确定】。

解读：

Weekday函数的作用为：返回代表一周中的第几天的数值，语法结构为：=Weekday(日期,统计方式)，代码“2”的意思为按照星期一为1，星期二为2……以此类推的方式进行，所以工作日＜6。

---

九、数据验证：动态下拉菜单。

目的：下拉菜单的内容跟随数据源的变化而变化，自动增加或减少。

方法：

1、选定目标单元格，【数据】-【数据验证】。

2、选择【允许】中的【自定义】，在【公式】中输入：=OFFSET($L$3,0,0,COUNTA($L:$L)-1)并【确定】。

解读：

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

---

十、数据验证：二级动态下拉菜单。

目的：根据一级菜单“省”的情况，自动匹配二级菜单“市”。

方法：

1、填充以及下拉菜单“省”。

2、选定“市”的目标单元格，【数据】-【数据验证】。

3、选择【允许】中的【自定义】，在【公式】中输入：=OFFSET($I$2,MATCH($L3,$H$3:$H$9,0),0,COUNTIF($H:$H,$L3))并【确定】。

解读：

公式中以I2为基准点，以Match函数得到的“省”首次出现的位置作为向下偏移的行数，向右偏移的列数为0，新引用的行数为COUNTIF($H:$H,$L3)的计算结果，根据L列以及一级菜单中的省的名称，统计在H列有多少个相同的名称，有多少多个相同的省名，Offset就引用多少行。

---

结束语：

通过本文的学习，相信大家对数据验证有了更深入的了解，尤其是动态下拉菜单和二级动态下拉菜单的制作，非常的智能和灵活，但前提条件是需要掌握Offset和Counta，以及Countif函数的功能及语法结构。如果有不明白的地方，欢迎在留言区留言讨论哦！

---

#职场达人炼成记# #职场干货# #Excel函数公式# @Excel函数公式

,