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

用Excel进行数据收集,做好一个数据录入文件给用户使用,结果收回来的数据让你令人抓狂?

Excel进行数据收集,做好一个数据录入文件给用户使用,结果收回来的数据让你令人抓狂:

  • 手机号不是11位
  • 面积单位有人写平方米,有人写m2
  • 张冠李戴,明明写姓名的地方,写了性别
  • 说好写保留一位小数,却提交了整数
  • 说好50字以内,却写了500字……

对于以上这些问题,如果你是用Excel收集数据的话,应该用Excel里的【数据有效性】(2013以后叫【数据验证】),提前针对指定单元格进行限制,让使用者按你的要求去录入正确的数据,这样收集效果会好一些。

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

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

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

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

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

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

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

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

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

这里就有两种解决办法:

1、indirect引用名称法

2、offset动态区域法

以下介绍方法1的操作:

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

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

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

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

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

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

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

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

注意事项&总结

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

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

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

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

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

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

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

换工具:若培训不便操作,VBA又不懂的话,那你也就不适合用Excel来收集数据了。下方推荐几个在线收集数据的工具,在这方面会有提升,收集好数据可以直接导出到Excel,也很方便!甚至也有多级下拉菜单的,请自行探索~~

  • 麦客CRM

http://www.mikecrm.com/

  • 金数据

https://jinshuju.net/

  • 问卷星

https://www.wjx.cn/

  • 腾讯文档

https://docs.qq.com/

要做好一项工作,那就想办法去推进好,方法不行就换方法,工具不行就换工具,自己不行就多学习~~~

作者:刘振雄

来自:数据化管理(微信ID:sjhglwx)

本文为专栏文章,来自:数据化管理,内容观点不代表本站立场,如若转载请联系专栏作者,本文链接:https://www.afenxi.com/64336.html 。

(7)
上一篇 2018-07-01 11:23
下一篇 2019-06-05 11:27

相关文章

关注我们
关注我们
分享本页
返回顶部