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

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

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

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

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

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

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

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

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

精确选择步骤如下:

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

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

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

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

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

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

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

  • 一级教程

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

数据源的通用公式如下:

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

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

第六步,完成制作

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

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

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

lanlan
6 月 前

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

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

733
7 月 前

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

111
8 月 前

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

oubaa
8 月 前

非常好,感谢!

*白苏
10 月 前

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

不想做表格
7 月 前
回复给  懒人Excel

研究好了吗?

擦香香
1 年 前

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

三十00
1 年 前

这个源数据怎么隐藏呢

北城以北
2 年 前

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

天宇*
2 年 前

这个相当好哦

Sammi
2 年 前

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

the true
2 年 前

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

the true
2 年 前

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

the true
2 年 前
回复给  懒人Excel

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

the true
2 年 前
回复给  懒人Excel

奈斯,太感谢了,

the true
2 年 前
回复给  懒人Excel

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

LEVEN
2 年 前

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

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
alex
3 年 前

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

tyyyys
3 年 前

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

Sunshine
3 年 前

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

最后由Sunshine编辑于3 年 前
hhhh
3 月 前
回复给  懒人Excel

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

40
0
希望看到您的想法,请您发表评论x