防止录入错误,3步搞定Excel下拉列表!

 

转载一篇文章给大家,作者:King,武汉幻方科技课程开发经理,微博:@文剑武书生King...



在阿猫阿狗的网站剁手时,最熟悉不过的就是商品分类菜单了。菜单提供了有限的选项,逐层打开,既方便又高大上。



我们在做Excel表时是不是也可以借鉴呢?

分发出去要别人填写的表单,如果也能提供菜单式的选项,至少会有2个好处:

- 省去手工输入,方便填写显贴心;

- 精确输入,预防出错和偏差;



特别是第2点尤为重要。

如果性别栏不限定填男、女,保不准就有人会填写成:

男性、女性

M、F

Boy、Girl

直男

人妖

……



以前搞活动回收表格做汇总统计时就曾饱受折腾…… 说多了都是泪。

自那一次之后,我就意识到,从自己手中出去的表格,做足安全措施非常必要!

后来无意中发现别人的表格竟然可以有下拉列表,就像这样:



瞬间惊为天人!而且只需要3步就可以搞定。

来来来,我教你~

巧妇难为无米之炊,要有下拉列表,首先得备米——参数表。

我们先将所有列表条目罗列好,其中一级列表的条目和二级列表的标题一一对应:
 
做好准备工作之后,分3步就能搞定多级下拉列表。
 
Step1 设置参数名称,操作演示如下:



Step2 设置一级菜单,操作演示如下:



Step3 设置二级菜单,操作演示如下:



照着上面的步骤,就能轻而易举的制作出二级联动列表的效果。

敲黑板了!!!结合图文说明,再回顾一遍操作要点:[/b]

❶ 给一级列表创建名称并命名:

选中目标列所有内容



点击【公式】选项卡,点击【根据选中内容创建名称】



创建的名称直接以首行-”品类”命名,剩下的行作为引用区域



❷ 重复上一步操作

其他参数列表的操作和❶相同,分别选中后按F4,不出3秒就全部搞定。



❸ 将一级列表区域关联到对应的名称

选中品类填写区域设置数据验证



验证的条件设置为【序列】,来源为前面所创建的列名称



参数表中的条目就会出现在品类下拉列表中



❹ 二级列表-商品动态引用参数

二级下拉列表同样是选中以后设置数据验证,但是商品一栏的下拉列表必须跟品类一列填写的项目相关联。这就需要用到一个查找引用类的函数Indirect



这样就能搞定啦~

刚学这一招时,我有两个疑问:

【Q1】Indirect函数是什么意思?为什么用它就能动态关联呢?

这得从两个英文单词说起:

Direct 直接的

Indirect 间接的

同样的,Excel函数也有直接引用和间接引用之分。直接引用很好理解,公式使用了哪个单元格,结果就来自于该单元格:



然而间接引用的功夫是——隔山打牛!

同样是使用A3,但是结果不是等于A3单元格的值A2,而是A2单元格里的值。



按照Indirect的脾性,商品一栏的验证公式=indirect(A2),其最终结果自然不会是”女性用品”,而是”女性用品”这个名称所指向的引用区域。而在第1步创建名称,已将该参数区域命名为”女性用品”。

于是验证公式的结果区域是女性用品下面的4个条目。



【Q2】列表的项目还要增加怎么办?能不能自动更新?

例如,要在男性用品一类中增加个什么玩具,按照上述做法,下拉列表并不会自动增加条目。要让列表根据条目多少自动扩展,只需要在前边的基础上多做一步。

普通青年的做法是:重新设置一遍

文艺青年的做法是:定义名称的区域改成Offset函数

超神青年的做法是:用智能表格



*选中和创建名称时同样的区域,再套用表格格式即可

你用过下拉列表吗?

除了商品类别,还可以用在哪些地方呢?

欢迎到留言区开拓思路~



作者丨King

编辑丨阿机

在公众号后台回复关键词 下拉列表,可以获取练习文件

点击【阅读原文】,立即开始 系统学习Excel


    关注 教学笔记


微信扫一扫关注公众号

0 个评论

要回复文章请先登录注册