懒人Excel

Excel 函数公式、操作技巧、数据分析、图表模板、VBA、数据透视表教程

Excel 制作二级联动下拉菜单教程

作者： · 发布于 · 更新于

二级联动下拉菜单可以根据一级菜单的内容，动态调整二级菜单的内容，显著提高输入有依赖关系的数据的效率。

本篇文章介绍 Excel 制作二级联动下拉菜单的方法，将用到 INDIRECT 函数和 Excel 名称。

本文目录 隐藏
第一步，准备一二级下拉菜单的源数据
第二步，制作一级下拉菜单
第三步，精确选择二级菜单数据
第四步，根据所选内容创建名称
第五步，动态引用二级下拉菜单内容
第六步，完成制作

第一步，准备一二级下拉菜单的源数据

为制作二级联动下拉菜单，准备如下格式的源数据表格。数据区域首行是一级菜单的来源，每一列数据是对应的二级菜单的来源。

第二步，制作一级下拉菜单

根据源数据的第一行，制作一级下拉菜单，具体步骤查看以下文章。

第三步，精确选择二级菜单数据

这一步一次性精确选择每一个二级菜单的数据来源，剔除因数据长度不同而会出现的空单元格。

精确选择步骤如下：

  1. 选中包括首行在内的一二级菜单的全部数据；
  2. 点击【开始】选项卡，【查找与选择】列表中的【常量】命令；

  1. Excel 会自动选择有内容的单元格，剔除空单元格。

第四步，根据所选内容创建名称

接上一步，精确选中数据源后，转到功能区【公式】选项卡，点击【根据所选内容创建】，在弹出的对话框中，只选择「首行」，再确定，Excel 会一次性批量创建名称。

其中，每一个名称的标题是数据区域首行单元格，即一级菜单的源数据。这为下一步动态选择二级菜单内容提供基础。

第五步，动态引用二级下拉菜单内容

这一步与制作一级菜单的步骤基本相同，唯一不同点是数据源的选择。

  • 一级教程

由于是根据一级菜单的内容动态调整二级菜单列表，需要使用 INDIRECT 函数指向对应的上一步创建的名称。

数据源的通用公式如下：

=INDIRECT(一级菜单单元格引用)

注意，一级菜单对应的单元格需要以相对引用方式书写，即不写美元符号（$）。

第六步，完成制作

经过前述步骤，一二级联动菜单制作完成。二级下拉菜单的内容根据一级菜单选择的内容动态调整。

转载注明：文章转载自「懒人Excel - www.lanrenexcel.com」
订阅评论
提醒
43 评论
最新
最旧 最多投票
内联反馈
查看所有评论
bellyoooo
2 月 前

试了一下用动态offset公式建立一个二级联动表格，有一个问题，动态一级列表改动的时候二级表格不会更新识别后面新加的内容，只能重新定位然后加入新建的名称项目，有方法可以解决吗？

0
回复
作者
懒人Excel
26 天 前
回复给  bellyoooo

初步思路是通过count函数替代offset函数的行方向移动的距离。

0
回复
bellyoooo
2 月 前

试了一下，WPS直接下拉列表相当于序列了，刚开始会报错后面又好了，要是能实现二级菜单后的数据也同步展示那是最好的

0
回复
zhr
6 月 前

如果那个引用不是常量，而是通过公式生成的数组时，定义名称时发现不能用怎么办？

0
回复
作者
懒人Excel
6 月 前
回复给  zhr

两个方法：
1、如果数据量一定，先转换为数值，名称创建完成后，再重新写回公式；
2、不需要常量功能选择，直接选择一个矩形区域。弊端是部分下拉列表会有空值

0
回复
lanlan
10 月 前

感谢分享，写的非常细，解决了我的难题。

数据源一级菜单的标题必须与需要下拉菜单表格的名称/标题一致。
二级菜单的标题分别是一级菜单中的每个选项。

1
回复
733
1 年 前

搜了好几篇，第一次见有教剔除空单元格，作者真的好细心呀

3
回复
111
1 年 前

二级联动菜单只能在一个sheet里面设置吗

0
回复
作者
懒人Excel
1 年 前
回复给  111

不需要的，输入INDIRECT函数的参数时，选取对应工作表的数据区域即可。

1
回复
oubaa
1 年 前

非常好，感谢！

0
回复
*白苏
1 年 前

假如 一个省带多个市，一个省还会有多个景区，请问如果我访问时要访问不同省的景区，那么省和景区是不是单独再建立一个新表做二级联动？

0
回复
作者
懒人Excel
1 年 前
回复给  *白苏

是的。但是按照本例的方法，使用【名称】实现下一级的联动，因此一个省的【名称】不能同时联动两组数据，所以这个方法可能不满足你的需求，需要另一个方法做联动。这个我再研究一下

0
回复
不想做表格
1 年 前
回复给  懒人Excel

研究好了吗？

0
回复
作者
懒人Excel
9 月 前
回复给  不想做表格

因为Excel的限制，确实不能一个内容同时绑定两组单元格区域。
一个迂回办法是在目标文本后拼接不同的单元格内容，然后在数据区域按需使用。
如下是一个示例，创建名称时，拼接【景区】后缀。使用的时候，通过标题名称拼接使用。

使用时，按需拼接后缀。

2
回复
擦香香
2 年 前

请问当一级和二级菜单是一对一关系时，怎么设置自动联动？

0
回复
作者
懒人Excel
2 年 前
回复给  擦香香

主要是看菜单源数据的分布方式，本例中就是一种。另一种是两列分布方式，这个后续再分享。

1
回复
三十00
2 年 前

这个源数据怎么隐藏呢

0
回复
作者
懒人Excel
2 年 前
回复给  三十00

把源数据单独放到一个工作表，设置好以后，隐藏这个表就可以

0
回复
北城以北
2 年 前

其实用xlookup函数可以很容易就实现诶

0
回复
作者
懒人Excel
2 年 前
回复给  北城以北

不同的情形确实有不同的解法，可以具体说一下吗

0
回复
天宇*
2 年 前

这个相当好哦

0
回复
Sammi
2 年 前

按照这个操作下来，选择某一一级菜单时，二级菜单下拉框中没有任何选项,请问可能是什么错误?

0
回复
作者
懒人Excel
2 年 前
回复给  Sammi

打开名称管理器看一下，第三步批量生成的名称是否正确生成？

0
回复
the true
2 年 前

你列子里面第一行数据选了广东，第二行我要选其他省份，第三行，第四行，后面有多少数据就会加多少行，那么这些增加的，怎么设置那个公式，每一行的都需要一一指定？

0
回复
作者
懒人Excel
2 年 前
回复给  the true

请参考上一个回复

0
回复
the true
2 年 前

多行的时候INDIRECT 里面的单元格怎么动态变化

0
回复
作者
懒人Excel
2 年 前
回复给  the true

描述的不是很清楚，可以再具体说一下吗

0
回复
the true
2 年 前
回复给  懒人Excel

就是第二行 那个函数里面的值，怎么设置，不会每一行都要手动去设置吧，我的意思就是，我在第一行设置好后，后面的自动可以用，比如你那里一个数据选选择广东，后面我要加一行，选择其他省份，后面我一直加下去，而且不用再去指定函数

0
回复
作者
懒人Excel
2 年 前
回复给  the true

不是的，一次性设置。设置时，选中想要设置的列的全部单元格，再输入INDIRECT函数和区域的第一个单元格，下面部分的区域将自动设置的。
二级菜单设置

0
回复
the true
2 年 前
回复给  懒人Excel

奈斯，太感谢了，

0
回复
the true
2 年 前
回复给  懒人Excel

就是第一行我指定了B3，那么第二行应该是B4，这样一直顺序下去，如何自动实现，B4,B5,B6这样，只要我加一行，就自动生成，不用再去指定

0
回复
作者
懒人Excel
2 年 前
回复给  the true

请参考上一个回复

0
回复
LEVEN
3 年 前

选择 某个“市”之后，再换“省”，原来选的“市”还在，有方法自动清空不关联的市吗？

0
回复
作者
懒人Excel
3 年 前
回复给  LEVEN

第一个想到的是用VBA编写清空代码。用Excel内置功能实现的话，还没想到一个好的方案

0
回复
the true
2 年 前
回复给  懒人Excel

怎么搞，有时间弄一弄，我看别人写了一个，但是不知道怎么用

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

With Target

If .Address(0, 0) = "A2" Then

Cells(2, 2) = ""

Cells(2, 3) = ""

End If

If .Address(0, 0) = "B2" Then

Cells(2, 3) = ""

End If

End With

End Sub
0
回复
alex
3 年 前

站点做的不错，10多年前接触过VB6.0 ，EXCEL一直觉得就可以很强大，原来可以这么玩。。有意思，PHPer 路过

1
回复
作者
懒人Excel
3 年 前
回复给  alex

感谢前辈肯定。共同学习，共同进步

2
回复
tyyyys
3 年 前

总是出现：“The source currently evaluates to an error. Do you want to continue?", 点Yes，数据验证失效，并不能正常出现下列列表。我查看了”名称列表“，没有问题呀。怎么回事？谢谢！

2
回复
作者
懒人Excel
3 年 前
回复给  tyyyys

可能是名称设置错误，可以在公众号后台联系交流，把具体的文件看一下

2
回复
Sunshine
4 年 前

“点击【开始】选项卡，【查找与选择】列表中的【常量】命令；剔除空白单元格”，创建名称及制作完下拉菜单后，在空白处接着输入城市，下拉菜单能自动添加进去吗？

最后由Sunshine编辑于4 年 前
0
回复
作者
懒人Excel
3 年 前
回复给  Sunshine

使用这篇文章中介绍的方法，是不能自动添加进去的。

1
回复
hhhh
8 月 前
回复给  懒人Excel

那请问想要可以添加信息即可更新需要怎么操作

0
回复
作者
懒人Excel
8 月 前
回复给  hhhh

这个我单独写一篇文章分享吧

0
回复
43
0
希望看到您的想法，请您发表评论x