excel真正的下拉菜单(这一篇让你完全掌握excel下拉菜单)

excel真正的下拉菜单(这一篇让你完全掌握excel下拉菜单)(1)

图/文 | 安伟星

早就承诺大家要写一篇Excel制作下拉菜单的教程,一直拖了这么久,这次用一篇文章让你完全掌握!

下拉菜单,从制作方法上,可以分为数据有效性法、控件法;从功能上,可以分为一级下拉菜单、多级联动下拉菜单、查询下拉菜单

01、下拉菜单制作方法

下拉菜单有两者制作方法,最常用的是我们熟知的数据有效性,其实Excel中还有一个工具可以制作下拉菜单,它就是控件。

由于控件灵活性非常强,篇幅有限,本文只做简要介绍,将主要精力放在数据有效性上面。

①数据有效性法

数据有效性在2016版Excel中叫做数据验证。

如图所示,需要为部门列设置一级下拉菜单,设置下拉菜单之后,不仅能够提高录入效率,而且可以有效防止不规范地输入。

excel真正的下拉菜单(这一篇让你完全掌握excel下拉菜单)(2)

Step1: 选择要添加下拉菜单的单元格C2:C7,切换到「数据」选项卡,点击「数据验证」

Step2:验证条件中,「允许」中选择「序列」

Step3:「来源」框内选择已制作好的列表区域(也可手动录入选项,选项之间用英文状态下的逗号隔开)

excel真正的下拉菜单(这一篇让你完全掌握excel下拉菜单)(3)

GIF动图演示

excel真正的下拉菜单(这一篇让你完全掌握excel下拉菜单)(4)

②控件法

控件是Excel中比较高级的一种功能,多用于VBA开发。它被集成在「开发工具」选项卡。控件法创建的下拉菜单,多数用于数值的选择,一般创建的较少,不能批量创建。

excel真正的下拉菜单(这一篇让你完全掌握excel下拉菜单)(5)

Excel中的控件

如果你的Excel中,没有开发工具这个选项卡,需要先在「自定义功能区」中将「开发工具」添加进来。

excel真正的下拉菜单(这一篇让你完全掌握excel下拉菜单)(6)

勾选如下图中的开发工具即可。

excel真正的下拉菜单(这一篇让你完全掌握excel下拉菜单)(7)

创建方法:

Step1:切换到在「开发工具」选项卡,在「控件」分区,点击「插入」,选择「组合框」控件

excel真正的下拉菜单(这一篇让你完全掌握excel下拉菜单)(8)

Step2:在工作表的任意位置绘制生成控件,选中控件点击「鼠标右键」→「设置控件格式」,在弹出的对话框中设置数据源区域,其他项保持默认即可。

excel真正的下拉菜单(这一篇让你完全掌握excel下拉菜单)(9)

GIF动图演示

excel真正的下拉菜单(这一篇让你完全掌握excel下拉菜单)(10)

控件的使用非常灵活,它和OFFSET函数、CHOOSE函数、MTATCH函数、INDEX函数等结合,能制作出非常高效的动态图表,这里不详细展开。

可以看出,不管是是用数据验证还是控件,制作一级下拉菜单都非常简单,其本质就是将下拉菜单中的数据作为数据源提前存储在菜单中,我们要做的就是设置好数据源即可,Excel自身会生成菜单。

02、多级联动下拉菜单

首先制作二级联动菜单。

二级联动菜单指的是,当我们选择一级菜单之后,对应的二级菜单会随着一级菜单的不同而选项也不同。二级菜单的创建方法有很多种,这里我们讲最常用的:通过indirect函数创建

如图所示,我们要创建省份是一级下拉菜单,对应的市名是二级下拉菜单的联动菜单。

excel真正的下拉菜单(这一篇让你完全掌握excel下拉菜单)(11)

①为省市创建“名称”

名称是一个有意义的简略表示法,可以在Excel中方便的代替单元格引用、常量、公式或表。

比如将C20:C30区域定义为名称:MySales,那么公式=SUM(MySales)可以替代=SUM(C20:C30),可见名称比单元格区域更具有实际意义。

Step1:按住Ctrl键,分别用鼠标选取包含省、市名的三列数据,要点是不要选择空单元格。(也可以通过Ctrl G调出定位条件,设置定位条件为在常量来选取数据区域)

excel真正的下拉菜单(这一篇让你完全掌握excel下拉菜单)(12)

Step2:在菜单栏中切换到【公式】选项卡→选择【定义的名称】分区→点击【根据所选内容创建】,在弹出的菜单中,勾选【首行】选项,如图所示,这样就创建了三个省份的“名称”,“名称”的值为对应着城市名。

excel真正的下拉菜单(这一篇让你完全掌握excel下拉菜单)(13)

②创建联动菜单

Step1:创建一级菜单

为区域中的省份一列创建一级菜单,创建方法通过“引用区域”的方式,直接将第一个图中的B1:D1区域作为数据来源,这里不在赘述。

Step2:为上图中的“市”创建二级菜单

选中【市】列需要设置的单元格区域→在验证条件中选择【序列】→【来源】中输入公式=INDIRECT($C3)→点击【确定】,此时会弹出错误提示,点击【是】继续下一步即可,如图。

excel真正的下拉菜单(这一篇让你完全掌握excel下拉菜单)(14)

提示:这里出错的原因是此时C3单元格中为空,还未选择省份的数据,找不到数据源,不影响二级菜单的设置。

完成之后,就实现了二级联动菜单,如图所示。

excel真正的下拉菜单(这一篇让你完全掌握excel下拉菜单)(15)

原理解析

实现二级联动菜单的核心是:定义名称和INDIRECT函数,理解这两个核心是解题的关键。

原理①:根据“名称”的作用,当我们定义名称“江苏省”时,那么在函数引用中,“江苏省”能够代替“南京、苏州……”

原理②:INDIRECT函数为间接引用,他可将文本转化为引用。

如图是间接引用于直接引用的不同。

excel真正的下拉菜单(这一篇让你完全掌握excel下拉菜单)(16)

将原理①和原理②结合起来,以江苏为例,在来源中输入的公式=INDIRECT($C3)的意思是,首先C3单元格中的值是“江苏省”,而INDIRECT可以将文本换成引用,而“江苏省”已经定义为名称,代表的是“南京、苏州……”,所以二级下拉菜单中出现的南京市、苏州市等。

多级下拉菜单的制作原理是完全一样的,学会了二级下拉菜单,三级菜单甚至四级菜单应该也不成问题,自己动手试一试吧!

03、查询式下拉菜单

下拉菜单的目的之一是提高输入的效率,但是,如果选项过多,那么下拉列表势必会很长,此时要想快速从下拉菜单中找到目标选项就非常困难。

我经常在想,如果能进行搜索下拉菜单该多好啊,这里教给你的方法,虽然没有搜索框,但是能模拟搜索的效果。

我把它称为查询式下拉菜单。

如图,要根据A列的集团列表,在E2单元格创建查询式下拉菜单,更方便地选择集团。该下拉菜单可以根据E2单元格内输入的第一个字来动态显示所有以输入汉字开头的集团,即实现查询作用。

excel真正的下拉菜单(这一篇让你完全掌握excel下拉菜单)(17)

Step1:对A列的集团进行升序排序。

Step2:选中E2单元格,打开「数据验证」对话框。在“允许”中选择“序列”,并在“来源”中输入公式:

=OFFSET($A$1,MATCH($E$2&"*",$A$2:$A$15,0),,COUNTIF($A$2:$A$15,$E$2&"*"),1)

excel真正的下拉菜单(这一篇让你完全掌握excel下拉菜单)(18)

Step3:在「数据验证」对话框,切换到「出错警告」窗口,取消勾选「输入无效数据时显示出错警告」,然后点击确定,完成设置。

excel真正的下拉菜单(这一篇让你完全掌握excel下拉菜单)(19)

最终的效果如下动图所示:

excel真正的下拉菜单(这一篇让你完全掌握excel下拉菜单)(20)

操作步骤同样很简单,难点是来源里面设置的公式。

①为什么要对集团数据列进行升序排序

排序之后,可以将第一个字相同的集团排在一起,这样在后面的输入首字进行查询式,这些集团都能够显示出来。

②OFFSET函数

它的语法形式是 OFFSET(reference,rows,cols,height,width),参数1为参照系,参数2为偏移行数,参数3为偏移列数,参数4为返回几行,参数5为返回几列。

总之,这里主函数OFFSET的作用就是:当E2单元格内输入首字时,找到以输入的汉字开头的集团名称,并引用所有符合条件的集团作为下拉菜单的显示内容。

③MATCH($E$2&"*",$A$2:$A$15,0)

在集团列表中查找以E2单元格字符开头的集团名称,返回找到的对应的第一个集团在列表中的序号;

④COUNTIF($A$2:$A$15,$E$2&"*")

在列表中统计以E2中字符开头的集团的个数

这里,MATCH函数作为OFFSET的第二个参数,即向下移动的行数;COUNTIF函数作为OFFSET的第4个参数,即从集团列表中返回的行数。

举例:当E2中输入“广”时

MATCH($E$2&"*",$A$2:$A$15,0)返回以广开头的集团在$A$2:$A$15中的序号,即2(广发集团排在第二位)。

此时COUNTIF($A$2:$A$15,$E$2&"*")统计出以广开头的集团共有三个,所以返回值为3。

主函数就变为OFFSET($A$1,2,,3,1),即返回「以A1为参照,向下移动移动两行(A3),行数总计为3行(A3:A5)的一个区域」,这个区域正是以广开头的三家集团:广发集团、广汇集团、广汽集团。

⑤为什么不能勾选出错警告

数据验证,要求输入的内容和设置的源中的内容必须一致,否则将提示警告,导致无法正常输入。我们因为是首字匹配,因此要取消警告。

excel真正的下拉菜单(这一篇让你完全掌握excel下拉菜单)(21)

最后,再次强调,函数是重点,理解了函数在本里中充当的含义,才能灵活的设置查询式下拉菜单。

·The End·

作者:安伟星,微软Office认证大师,领英中国专栏作者,《竞争力:玩转职场Excel,从此不加班》图书作者

,

免责声明:本文仅代表文章作者的个人观点,与本站无关。其原创性、真实性以及文中陈述文字和内容未经本站证实,对本文以及其中全部或者部分内容文字的真实性、完整性和原创性本站不作任何保证或承诺,请读者仅作参考,并自行核实相关内容。文章投诉邮箱:anhduc.ph@yahoo.com

    分享
    投诉
    首页