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

图/文 | 安伟星

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

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

01、下拉菜单制作方法

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

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

①数据有效性法

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

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

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

Step2：验证条件中，「允许」中选择「序列」

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

GIF动图演示

②控件法

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

Excel中的控件

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

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

创建方法：

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

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

GIF动图演示

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

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

02、多级联动下拉菜单

首先制作二级联动菜单。

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

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

①为省市创建“名称”

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

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

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

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

②创建联动菜单

Step1：创建一级菜单

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

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

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

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

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

原理解析

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

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

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

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

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

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

03、查询式下拉菜单

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

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

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

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

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

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

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

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

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

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

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

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

②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）的一个区域」，这个区域正是以广开头的三家集团：广发集团、广汇集团、广汽集团。

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

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

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

·The End·

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

,