一、二级联动下拉菜单

让二级下拉菜单中的选项随一级下拉菜单中填充内容的改变而改变。

二、任务需求

在“学生信息表”中为每位学生添加“学院”和“专业”列,要求“学院”列为一级下拉菜单,“专业”列为二级下拉菜单,可以根据“学院”列的填充内容改变“专业”列中的下拉菜单选项。

excel 下拉菜单三级联动(Excel如何制作二级联动下拉菜单)(1)

学生信息表

excel 下拉菜单三级联动(Excel如何制作二级联动下拉菜单)(2)

学院及专业

excel 下拉菜单三级联动(Excel如何制作二级联动下拉菜单)(3)

二级联动菜单

三、任务难点

“专业”列下拉菜单选项随“学院”列填充内容的改变而改变。

四、解决思路

1、给各下拉菜单源数据定义名称。

2、在一级下拉菜单的单元格输入与定义名称相同的字符。

3、二级菜单根据一级菜单输入的字符用INDIRECT函数引用该名称下的源数据。

五、具体方法

(一)添加“学院”列下的一级菜单

按照常规添加一级菜单的方法设置。

(选择B2:B10单元格——数据——数据验证——允许——序列——来源——=学院及专业!$A$1:$D$1(学院及专业表中的学院名称行)——确定)

excel 下拉菜单三级联动(Excel如何制作二级联动下拉菜单)(4)

“学院”列下一级菜单——数据验证

excel 下拉菜单三级联动(Excel如何制作二级联动下拉菜单)(5)

“学院”列下一级菜单

(二)添加“专业”列下的二级联动菜单

1、定义名称

为“学院及专业”表中的各列专业定义名称,以学院名称命名。

(选中所有学院及专业——Ctrl G——常量——确定——公式——根据所选内容创建——首行——确定)

excel 下拉菜单三级联动(Excel如何制作二级联动下拉菜单)(6)

为专业定位

excel 下拉菜单三级联动(Excel如何制作二级联动下拉菜单)(7)

定位状态

excel 下拉菜单三级联动(Excel如何制作二级联动下拉菜单)(8)

定义名称

excel 下拉菜单三级联动(Excel如何制作二级联动下拉菜单)(9)

定义名称状态

注意:定义名称是为了把学院下的专业统计以定义的名称来表示,例如体育学院下的专业,统一用“体育学院”来表示,可以在公式中直接引用。

excel 下拉菜单三级联动(Excel如何制作二级联动下拉菜单)(10)

体育学院专业引用状态

2、数据验证

在“学生信息表”中选中“专业”列下要设置下拉菜单的单元格(C2:C10),为该部分单元格设置数据验证。

(选择C2:C10单元格——数据——数据验证——允许——序列——来源——=INDIRECT($B2)——确定)

excel 下拉菜单三级联动(Excel如何制作二级联动下拉菜单)(11)

“专业”列下的二级联动菜单——数据验证

3、结果验证

“专业”列下拉菜单选项随“学院”列填充内容的改变而改变。

excel 下拉菜单三级联动(Excel如何制作二级联动下拉菜单)(12)

二级联动下拉菜单

六、公式解析

=INDIRECT($B2)

1、INDEIRECT(ref_text)函数作用:返回由文本字符串指定的引用,并对引用进行计算,并显示其内容。

2、本例,根据B列单元格内容返回指定的专业引用。

3、每个学院的专业均已由学院名称命名定义,根据学院名称即可找到相应的定义引用。

以上就是如何制作二级联动下拉菜单方法,难点在定义名称和INDEIRECT(ref_text)函数的理解,你学“废”了吗?

案例中涉及的函数,后续有专门讲解,请持续关注动态!

我是今日Excel与编程,想了解更多的Excel与编程知识请点赞并关注我

,