Excel多级联动下拉菜单的制作
一、需求描述
在Excel数据录入过程中会遇到如下两种情况:
1.需要录入多个固定名称的数据，如果靠手动录入可能会出现录入错误，录入非标准定义的名称的情况。
这种情况如果提供一个序列供录入时选择，并同时对录入内容校验就可以避免这样的错误。
2.录入的数据之间有联动关系，比如部门层级、省市层级。下一级的数据需要输入上一级的数据。
这种情况就需要在录入下一级数据的时候验证该数据是否属于上一级。
下面会介绍一个简单的方法来保证录入的数据的可靠性并提高录入的便捷性。
二、解决思路
如标题所述，本次要介绍的方法就是通过Excel的多级下来菜单来进行有效数据录入。
该方法所需用到的功能及函数如下:
1.数据验证
数据验证功能属于Excel中数据工具板块的功能。该功能作用是用来校验输入的数据是否符合事先设定的规则。
数据验证条件可以验证录入数据的类型、录入值的大小、录入值的范围等。
本次用到的主要是其中的序列功能。
该功能用来保证输入的数据属于某个设定好的序列中。这样就能保证我们输入的信息不是我们随意录入的，而是在已知序列中的数值。
同时该功能还可以提供一个下拉菜单进行快速录入。
2.名称定义
名称定义功能是一个属于Excel公式模块下的功能。名称定义的作用在于用一个定义的名称去代表一个引用位置。如下图所示，定义的名称:名称1 所引用的位置是sheet1中的A1-B2范围的内容。后面如需引这个位置则可以直接输入:名称1 即可，无需记住繁琐的地址引用。
为什么需要介绍名称定义功能？因为在数据验证中的序列的验证数据来源可以直接引用这个定义好的名称。
3. INDIRECT函数
INDIRECT函数含义：返回文本字符串所指定的引用。
INDIRECT语法：=INDIRECT(引用区域，引用格式）
这里的引用格式有两种：
一种是A1格式直接忽略或者选择1（True）另一种是R1C1格式必须0（False），一般这种格式不常用。
当我们只需要一个下拉菜单时我们无需用到该公式。但是如果我们需要多级菜单时，就会发现，光用上面的办法不行。因为多级菜单之间需要数据联动，上一级菜单数据变化是，下一级菜单的数据序列就需要跟着变化了。因此这里使用 INDIRECT函数来根据一级菜单的值来分别引用不同的有效验证序列。
如下例：
将下列数据AAA、BBB定义为一级菜单，A1 A2 属于AAA菜单的字菜单而B1 B2则属于BBB菜单的菜单。我们将A1 A2区域数据定义名称为AAA，B1 B2区域数据定义为BBB。
在做序列联动是只需要将下图B2单元格的数据验证序列来源于=INDIRECT(A2)即可。这样当一级菜单数据变化是，二级菜单的引用序列就根据一级菜单的值及设定好的名称定义去寻找需要的序列，从而实现菜单的联动。
三、解决案例
根据上面所罗列的思路应该就可以实现多级联动下拉菜单了。下面通过一个简单的例子才演示一下。
我们通过制作一个省\市\县区的三级联动菜单来进行演示。
第1步：建立基础数据
根据省\市\县区三个级别分别定义出名称来。如下图三个红框区域数据分别定义名称为省份、江苏、南京。其余数据同该步骤这里就不多列举。
第2步: 一级菜单制作
直接通过数据验证功能将省份列的单元格设置验证序列来源=省份
这样第一级菜单就完成了。
第3步: 二级菜单制作
通过数据验证功能将省份列的单元格设置验证序列来源=INDIRECT(A2)，A2就是对应一级菜单位置的引用。
这样第二级菜单就完成了，并且与一级菜单实现了联动。当省份选择江苏时就只有江苏的城市，当省份选择浙江时就只有浙江的城市。
第4步: 三级菜单制作
三级菜单制作同二级菜单相同，只是序列的来源为=INDIRECT(B2)，也就是根据不同的城市来选取该城市的县、区。
这样就可以根据以上步骤，可以继续下一步做出四级菜单、五级菜单了。
如有疑问可联系我的个人微信:
我的微信/QQ：1026539820
备用微信/QQ：2261896995
留言Excel入群可加入Excel微信群
有相关问题咨询讨论可加入我的QQ群:
CAD交流学习群:1001732100
EXCEL技术交流群:499560580
更多Excel使用技巧关注我的微信公众号
关注公众号
标签: Excel
微信公众号
今日头条
最新文章
热门文章
存档
最新评论
链接
发表评论：