懒人Excel

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

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

作者： · 发布于 · 更新于

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

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

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

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

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

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

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

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

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

精确选择步骤如下：

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

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

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

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

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

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

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

  • 一级教程

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

数据源的通用公式如下：

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

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

第六步，完成制作

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

转载注明：文章转载自「懒人Excel - www.lanrenexcel.com」
本文标题：Excel 制作二级联动下拉菜单教程
订阅评论
提醒
27 评论
最新
最旧 最多投票
内联反馈
查看所有评论
擦香香
7 月 前

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

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

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

0
回复
三十00
7 月 前

这个源数据怎么隐藏呢

0
回复
作者
懒人Excel
7 月 前
回复给  三十00

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

0
回复
北城以北
10 月 前

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

0
回复
作者
懒人Excel
10 月 前
回复给  北城以北

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

0
回复
天宇*
1 年 前

这个相当好哦

0
回复
Sammi
1 年 前

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

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

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

0
回复
the true
1 年 前

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

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

请参考上一个回复

0
回复
the true
1 年 前

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

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

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

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

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

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

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

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

奈斯，太感谢了，

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

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

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

请参考上一个回复

0
回复
LEVEN
1 年 前

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

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

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

0
回复
the true
1 年 前
回复给  懒人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
2 年 前

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

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

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

2
回复
tyyyys
2 年 前

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

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

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

2
回复
Sunshine
2 年 前

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

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

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

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