Excel表格不用填,3步搞定下拉列表!

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

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

 

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

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

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

 

特别是第2点尤为重要。

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

男性、女性

M、F

Boy、Girl

直男

人妖

……

 

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


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

 


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


 

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


来来来,我教你~

 

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


我们先将所有列表条目罗列好,其中一级列表的条目和二级列表的标题一一对应:

 

 

做好准备工作之后,分3步就能搞定多级下拉列表。

 

 


Step1 设置参数名称,操作演示如下:


 

 

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


 

 

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


 

 

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

如果动图还看不清楚,那就再看下面的操作要点图文说明,简单回顾一遍:

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

选中目标列所有内容

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


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

 

❷ 重复上一步操作

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

 

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


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


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

 

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

 


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

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


这样就能搞定啦~




 

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


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

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

Direct 直接的

Indirect 间接的

 

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

 

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

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

 

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

 

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

 

 

 


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

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

 

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

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

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

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

 

 

智能表格到底有何神通?

点这里,看这篇文章就一清二楚了


 


 

你用过下拉列表吗?

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

欢迎到留言区开拓思路~

 

 


作者丨King

编辑丨阿机

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

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



欢迎投稿 职场/创业方向. 邮箱wangfzcom(AT)163.com:王夫子社区 » Excel表格不用填,3步搞定下拉列表!

点评 0

评论前必须登录!

登陆 注册