干货 Excel中的下拉菜单制作与数据交互

 

各位早安，今天给大家介绍的主题是下拉菜单（Drop-down List）与数据交互。内容较多，先上个目录：

一、下拉菜单（Drop-down List）的制作

1、数据有效性制作下拉菜单



三、联级菜单的制作

01

下拉菜单的制作

在Excel中，有两种方式可以制作下拉菜单效果，分别是数据有效性和控件。尤其是控件，有着极其强大的交互功能。下面来看两种下拉菜单的制作方法。

1、利用数据有效性制作单元格下拉菜单



在单元格中制作下拉菜单，多数场景是为了预设选项（如“是,否”、“高一,高二,高三”），以防表格在发给他人填写的时候产生不规范输入。

但是，有的时候我们也用它来制作选择器，比如上面的GIF，通过H6单元格选择人名来查询考试成绩。

✔ 制作方法

1. 选择要添加下拉菜单的单元格，打开「数据有效性」（2016版译作数据验证）
2. 在「允许」中选择「序列」
3. 在「来源」框内选择已制作好的列表区域，或手动录入选项，选项之间用大写状态下的逗号隔开，确定完成。

2、利用控件制作下拉菜单



控件是EXCEL中一项非常特别的功能，它被集成在「开发工具」选项卡里，但由于EXCEL默认布局没有将「开发工具」展示出来，所以大多数人并不知道有这样的功能。原本强大的交互功能就这样被埋没，就好像悟空一样，明明有一身本领，却被镇压在五指山下。



EXCEL控件提供了很多类型的选择器，如单选按钮、复选框、滚动条、下拉菜单等，利用这些选择器，我们可以制作动态图表，极大地提升EXCEL的交互特性。试举几例：注：以上动态图表案例来自《Excel卓越之道》，在公众号回复01查看

✔ 制作方法

1. 调出「开发工具」选项卡
2. 在「开发工具」中「插入」组合框控件
3. 绘制生成控件，右键，在「设置控件格式」中选择数据源区域并链接到任意单元格

▲ 调用开发工具



▲ 制作下拉菜单（组合框）

一旦你理解了控件的功能，掌握一些必要的函数，那么制作动态图表就不在话下了。这里不再赘述，想系统学习动态图表制作的小伙伴可以查看我的书和权益。

02

自适应查询菜单制作的制作

使用下拉菜单有两个目的：一是限定选择范围，防止输入错误；二是提供交互选择，实现灵活查询。但是，如果你的选项过多（如几百上千）导致下拉列表太长时，要想快速从下拉菜单中找到目标选项就不那么容易了。

对于这种情况，我们可以制作一个自适应下拉菜单。所谓自适应菜单，即单元格内的下拉选项会根据输入字符智能匹配并显示以此开头的选项。

下面同样以《Excel卓越之道》中的例子为大家介绍“自适应下拉菜单”的做法，先上效果。

▼ 自适应菜单效果



如下图，表格所列为A股上市公司列表（A10:T1523），共计1513家公司。



现要求在A3单元格内制作下拉菜单以显示B列的股票名称，且该下拉菜单可以根据A3单元格内输入的第一个字来动态显示所有以输入汉字或字符开头的股票，这样便可以大大减少下拉菜单中显示的股票数量，便于我们快速定位目标股票。



以下是具体的操作步骤：

Step1  选中第11到1523行，按照“公司简称”进行升序排序。



Step2  选中A3单元格，打开“数据有效性”对话框。在“允许”中选择“序列”，并在“来源”中输入公式：

=OFFSET（$B$10,MATCH（A3&"*",$B$11:$B$1523,0）,,COUNTIF（$B$11:$B$1523,A3&"*"）,1）



点击确定，完成设置。

步骤很简单，关键在于公式的运用，要理解MATCH函数和COUNTIF函数所扮演的角色。

MATCH（A3&"*",$B$11:$B$1523,0）——在列表中查找以A3单元格字符打头的股票，返回找到的第一支股票在全部列表中的序号（星号为通配符）；

COUNTIF（$B$11:$B$1523,A3&"*"）——在列表中统计以A3中字符打头的股票的个数。

在这里，MATCH函数是OFFSET函数的第二个参数，即向下移动的行数；COUNTIF函数是OFFSET函数的第4个参数，意为从股票列表中选择的行数。

当A3单元格为空时，A3&”*”代表所有的股票名称列表，MATCH（A3&"*",$B$11:$B$1523,0）即为第一支股票“*ST创智”在全部股票列表中的序数，即为1，COUNTIF（$B$11:$B$1523,A3&"*"）代表股票总支数，即1523。因此，A3单元格未进行任何输入时，下拉列表显示的是全部的股票列表。



当A3单元格内输入文字时，OFFSET公式会迅速找到以输入文字开头的股票（已排序，因此在表格中相同汉字或字符开头的股票是连续的），并引用这一子列表作为下拉菜单的显示内容。



03

动态图表中的联级菜单制作



与上面的自适应菜单相似，本例中的二级菜单也是先确定一级选项，然后自动切换到相应的二级选项。不同的是，本例完全采用控件，即两个列表框来完成。

操作要点提示 >>>

❶ 添加控件

在开发工具中添加两个单选控件（控件1、控件2），本例中选择了两个列表框。其中，控件1用于显示表格列表，即「罗湖、福田、南山、宝安、盐田」，控件2用于动态显示指标列表。



❷定义名称



定义几个名称，其中：

List1至List5

分别引用五个表格的指标选项位置

ListSelect

=CHOOSE(Sheet2!$A$7,List1,List2,List3,List4,List5)

用CHOOSE函数实现二级菜单位置的引用，通过控件1链接单元格Sheet2!$A$7的输出值来动态引用目标表格对应的指标选项。

DataSource

=OFFSET(CHOOSE(Sheet2!$A$7,Sheet1!$A$1,Sheet1!$A$7,Sheet1!$A$13,Sheet1!$A$18,Sheet1!$A$24),Sheet2!$A$8,1,1,3)

用OFFSET函数连接到真正的展示数据源，内嵌CHOOSE函数用于确定OFFSET函数所指向的起始单元格。

❸ 设置控件2的链接信息，如下图所示：



❹ 插入空白图表（柱状图），设置数据源为DataSource。



❺ 简单调整控件位置、美化图表，完成。

好玩吗？

想系统学习Excel，收获更多有趣的案例

欢迎加入ExcelBro首期会员

双11，也别忘了投资自己哦~