本文用一篇文章让你全面掌握下拉菜单！

下拉菜单按制作方式可分为数据有效性方式、控制方式；按功能可分为一级下拉菜单、多级联动下拉菜单、查询下拉菜单。

01

如何创建下拉菜单

创建下拉菜单的方法有两种，最常用的就是我们熟悉的数据有效性，其实还有一个工具可以用来创建下拉菜单，那就是控件。

由于控制的灵活性较高，且篇幅有限，本文仅作简单介绍，并重点讨论数据有效性。

①数据有效性方法

数据有效性在2016版中被称为数据验证。

如图所示，需要为部门栏设置一级下拉菜单，设置下拉菜单之后，不仅可以提高输入效率，还可以有效防止不规范的输入。

：选中需要添加下拉菜单的单元格C2:C7，切换到“数据”选项卡，点击“数据验证”

：验证条件中，选择“允许”中的“顺序”

：在“来源”框中选择准备好的列表区域（也可以手动输入选项，选项之间用英文逗号分隔）

▼

GIF动画演示

②控制方式

控件是一个比较高级的功能百度提示框下拉生成★64xl.com下拉技术教程，百度搜索下拉词是自己写的吗，多用于VBA开发，集成在“”选项卡中，控件方式创建的下拉菜单多用于选择值，一般创建频率较低，且不能批量创建。

控制

如果您的浏览器中没有“开发工具”选项卡，则需要先在自定义功能区中添加“开发工具”选项卡。

只需检查下图所示的开发工具即可。

创建方法：

：切换到“开发人员”选项卡，在“控件”部分，单击“插入”，并选择“组合框”控件

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

▼

GIF动画演示

该控件使用起来非常灵活，可以和函数、函数、函数等组合，制作出非常高效的动态图表，这里就不多说了。

可以看出，无论是使用数据验证还是控件，创建一级下拉菜单都非常简单。其实质就是将下拉菜单中的数据作为数据源预先存储在菜单中。我们要做的就是设置数据源，菜单就会自行生成。

02

多级联动下拉菜单

一、创建二级联动菜单。

二级联动菜单就是指当我们选中一级菜单的时候，对应的二级菜单会根据一级菜单的不同而有不同的选项。二级菜单的创建方式有很多种，这里就说一下最常见的一种方式：通过函数来创建。

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

① 为省市创造“名字”

名称是一个有意义的缩写，可以用来代替单元格引用、常量、公式或表格。

例如，如果将 C20:C30 区域定义为名称：，则公式 =SUM() 可以替换为 =SUM(C20:C30)。可以看出关键下拉菜单，名称比单元格区域更具有实际意义。

：按住Ctrl键，用鼠标选中包含省份和城市名称的三列数据，重点是不要选中空单元格。（也可以用Ctrl+G调用定位条件，在常量中设置定位条件选中数据区域）

：切换到菜单栏中的【公式】选项卡→选择【定义的名称】栏→点击【从选择中创建】，在弹出的菜单中勾选【第一行】选项，如图。这样就创建了三个省的“名称”，并且“名称”的值与城市名称一一对应。

②创建联动菜单

：创建一级菜单

创建区域中省份列的一级菜单，创建方法采用“引用区域”方式，直接使用第一张图中的B1:D1区域作为数据源，这里就不赘述了。

：针对上图中的“城市”创建二级菜单

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

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

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

原理分析

实现二级联动菜单的核心是：定义名称和功能，搞清楚这两个核心是解决问题的关键。

原则①：根据“名称”的功能，当我们定义名称“江苏省”时，那么在功能引用中，“江苏省”可以替代“南京、苏州……”

原则②：函数是一种间接引用，可以将文本转化为引用。

图中显示了间接引语和直接引语的区别。

结合原则①和原则②，以江苏为例，在源中输入公式 = ($C3) 的意思是，首先，C3 单元格中的值为“江苏省”，文字可以用引用代替，且“江苏省”已定义为名称，代表“南京市、苏州市……”，因此在二级下拉菜单中出现了南京市、苏州市等。

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

▼

动画片

03

查询下拉菜单

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

我经常想，要是有个搜索下拉菜单该有多好，这里教给大家的方法，虽然没有搜索框，但是可以模拟搜索的效果。

我称之为查询下拉菜单。

如图所示，在A列分组列表的基础上，为了更加方便地选择分组，在E2单元格中创建了一个查询下拉菜单，该下拉菜单可以根据E2单元格中输入的第一个字符，动态显示所有以输入的汉字开头的分组，从而实现查询功能。

：对A列中的组按升序排序。

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

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

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

最终效果如下动图所示：

操作步骤也很简单，难点在于源码中的公式设置。

①分组数据列为什么要按升序排列？

经过排序之后，可以将首字符相同的组放在一起，以便以后输入首字符进行查询时，就可以显示这些组。

②功能

其语法为(,rows,cols,,)，其中参数1为参考系，参数2为要偏移的行数，参数3为要偏移的列数，参数4为要返回的行数，参数5为要返回的列数。

简单来说，这里的main函数的作用就是：当在E2单元格输入第一个字时，找出以输入的汉字开头的组名，引用所有符合条件的组作为下拉菜单的显示内容。

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

在群组列表中搜索以 E2 单元格中的字符开头的群组名称，并返回列表中找到的第一个群组的序号；

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

计算列表中以 E2 中的字符开头的组数

这里，函数的第二个参数是向下移动的行数，函数的第四个参数是从组列表返回的行数。

例如：在 E2 中输入“广”

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

此时 ($A$2:$A$15,$E$2&"*") 统计出以“广”开头的组有三组，所以返回值为 3。

main函数变成了 ($A$1,2,,3,1)，返回“以 A1 为基准，向下移动两行（A3），总行数为 3 行（A3:A5）的区域”，这个区域正是名称以“广”开头的三家集团：GF集团、广汇集团、GAC集团。

⑤为什么我查看不到错误警告？

数据验证要求输入的内容必须和设定的源中的内容一致关键下拉菜单，否则会提示警告，导致无法正常输入。由于我们是匹配第一个字，所以需要取消这个警告。

最后再强调一下，功能是重点，只有理解了本书中功能的含义，才能灵活设置查询下拉菜单。

今日互动

做一个三级下拉菜单发给我吧，我会给你一个惊喜。