用Excel做多级下拉菜单媲美网页筛选器

时光瘦了空瞳

用Excel做多级下拉菜单媲美网页筛选器
用Excel进行数据收集，做好一个数据录入文件给用户使用，结果收回来的数据让你令人抓狂：
·手机号不是11位
·面积单位有人写平方米，有人写m2
·张冠李戴，明明写姓名的地方，写了性别
·说好写保留一位小数，却提交了整数
·说好50字以内，却写了500字……
对于以上这些问题，如果你是用Excel收集数据的话，应该用Excel里的【数据有效性】（2013以后叫【数据验证】），提前针对指定单元格进行限制，让使用者按你的要求去录入正确的数据，这样收集效果会好一些。

但还有复杂一些的情况，可能需要逐级限制，例如你在一些网页，看到可以先选第一级菜单，然后第二级的内容是仅对应第一级出现的选项，这样才会能满足更复杂的录入需求。

所以只会做简单的数据有效性还不够，还要懂制作多级联动下拉菜单了：

可惜的是，Excel并没有自带这样一种功能，还是只能建立在现有的【数据验证】功能基础上去想办法。

一级菜单好办，直接设置对应的序列数据源就行了：

那二级、三级的数据验证的数据源怎么解决呢？

二级、三级的下拉列表是要根据一级的内容变化而变化的，也就是说二三级的数据验证数据源必须是一个动态变化的区域。

这里就有两种解决办法：

1、indirect引用名称法

2、offset动态区域法

以下介绍方法1的操作：

Step1：给二级的内容定义名称，名称为它对应的一级内容。如给广州市定义名称为广东省，给南京市定义名称为江苏省。

Step2：设置数据验证，选择用indirect函数动态引用一级单元格内容，即实现当一级为广东省时，二级的菜单数据源就是indirect(“广东省”)，当一级是江苏省时，二级的下拉数据源就是indirect(“江苏省”)。我们在上一步已定义好名称，名称“广东省”=“广州市”，“江苏省”=“南京市”，通过此法实现了二级的下拉菜单动态变化，是依赖一级的选择结果变化而变化。

Step3：做好二级菜单后，三级的问题也是同理可得。先给三级的内容设置名称，名称是对应的二级，然后也是通过indirect函数引用二级内容的单元格，实现当二级选择广州市时，三级的下拉数据源就是indirect(“广州市”)。


就这样，三级下拉菜单就做完了！其实也不难对吧，做出这种多级联动的下拉菜单，会让数据录入更方便快捷~

但是要注意的是，这也并不是绝对的限制，具体要注意的问题请见下方：

注意事项&总结

1、用indirect动态引用名称的办法，操作是简单，但若下级数据有更改时并不能自动扩展，后续维护麻烦。

解决扩展问题，可以在方法1基础上结合表去操作，或直接把二三级的序列数据源设置为offset动态引用区域即可。

2、因为二三级联动的下拉菜单，并不是Excel原生的功能，只是一个数据验证的功能；所以当你填写好了各级内容后，反过来再更改一级的选项时，二三级的内容并不会被清除或提示错误。

要解决这个问题，可以用条件格式根据判断下级内容是否属于上级进行提示，如改变填充色或字体色等；再高阶的办法是用VBA去判断上级单元格是否有变化，一旦变化，则清空下级单元格的内容。

3、Excel的【数据验证】功能比较脆弱，若别人在你设置了数据验证的区域进行粘贴的操作，就会被破坏掉了！这里又有几个解决方法：

技术法：用VBA限制不让用户粘贴。

管理法：搞清楚用户为什么要粘贴，因为是不懂使用么？还是要填写的重复内容太多？通过培训教用户去好好使用即可。