EXCEL进阶课堂 · 函数说 持续更新！我们将为各位小伙伴提供更加专业、更加精炼、更加实用的EXCEL操作技能，帮助大家轻松解决工作任务，提高工作效率，不再做不停加班的表哥，表姐。欢迎各位小伙伴转发、点赞、讨论，更欢迎私信获取练习素材，刻意练习才能学有收获。
这是函数说的第24篇教程。
在上一篇教程中，我们用名称法解决了多级下拉菜单联动的问题，总体比较简单，大家的学习成本很低。
「函数说 23」多级下拉菜单联动？一种简单方法：名称 INDIRECT，分分钟搞定
但是，正如文章最后提到：如果每级涉及的选项很多时，需要去做的名称就会很多，反而变得操作繁琐了。
有没有更好的地解决办法呢？当然有。进阶君接下来给大家解讲一种高级的方法——公式法来解决这个问题。为了让小伙伴们有一个比对，我们仍然使用这个实例。
1 生成三级菜单对应数据关系表
根据填表说明，我们增加一个工作表，取名为“数据重组”，形成三级菜单对应数据关系，如图所示：
观察可以发现，在二级菜单对应区里面，专业是没有重复的情况，接下来要讲的方法对于二级菜单对应数据没有重复的情况是有通用性的。如果有重复怎么办，进阶君可以用特殊方法处理。
2 生成一级菜单下拉列表生成一级菜单下拉列表非常简单，直接用数据有效性来实现。
（1）选择单元格区域：用鼠标拖动的方法，选择“信息登记表”中的D3:D12单元格区域
（2）设置数据有效性：利用数据菜单中的数据有效性，设置数据有效性情况如下图所示
需要提醒的是： 来源 =数据重组!$B$2:$B$5 ，是取的 数据重组 工作表中的 一级菜单对应区。具体操作过程及效果如下动图所示：
3 生成二级菜单下拉列表
根据二级菜单对应关系可以知道，二级菜单的专业选项与一级菜单二级学院的密切相关。我们通过例子，来分析工作表当中，专业与学院的之间对应关系。
如果二级学院选择的是“软件学院”，首先要去确定“软件学院”在D列当中的起始位置，然后去统计D列当中“软件学院”的个数，接下来在E列当对应的区域就可以找到专业的位置。
我们的目的是要得到二级学院对应的专业，也就是选项区域。在我们选学的函数当中，OFFSET函数的结果就是一个区域。
OFFSET函数的格式：=OFFSET(参照单元格，偏移行数，偏移列数，选择行数，选择列数)
要得到二级学院对应专业所在选区：=OFFSET($E$1，偏移行数，0，选择行数，1)
于是问题就落脚在如何得到偏移行数和选择列数？
（1）确定偏移行数
偏移行数：是不是选择的二级学院在D列当中的起始位置-1。为什么要减1，因为我们要的偏移数，E1单元格，向下偏移1行，就得到了E2单元格。图中，软件学院在D列当中的起始位置是4，4-1=3，E列当中，以E1单元格向下偏移3行，于是得到了E4单元格，而E4单元格恰恰是“软件学院”的第一个专业——“软件工程”。
怎样得到偏移行数呢？用MATCH函数。
=MATCH（选择的二级学院，数据重组!$D$1:$D$10）-1
（2）确定选择行数
选择行数：是不是选择的二级学院在D列当中的个数。图中，软件学院在D列当中的个数为2，于是在E列当中，从E4单元格开始，选取2行，即 E4：E5，恰恰是软件学院对应的专业所在区域。
怎样得到选择行数？用COUNTIF函数。
=COUNTIF（数据重组!$D$1:$D$10，选择的二级学院）
（3）运用数据有效性，设置二级菜单下拉列表
选择 信息登记表 中 E3:E12单元格区域，设置数据有效性情况如下图所示：
其中 来源 =OFFSET(数据重组!$E$1,MATCH(D3,数据重组!$D$1:$D$9,0)-1,0,COUNTIF(数据重组!$D$1:$D$9,D3),1)
具体操作过程及效果如下动图所示：
4 生成三级菜单下拉列表
因为一级菜单对应的二级菜单没有重复值，接下来生成三级菜单下拉列表完全可以采用与二级菜单下拉列表同样方法完成。
如果专业选择的是“动漫设计”，首先要去确定“动漫设计”在H列当中的起始位置，然后去统计H列当中“动漫设计”的个数，接下来在I列当对应的区域就可以找到 班级的位置。
我们要得到所选专业对应的班级选区，采取 =OFFSET($I$1，偏移行数，0，选择行数，1) 完成。
偏移行数和选择行数，与二级菜单的方法类似。
选择 信息登记表 中 F3:F12单元格区域，设置数据有效性情况如下图所示：
其中，来源 =OFFSET(数据重组!$I$1,MATCH(E3,数据重组!$H$1:$H$19,0)-1,0,COUNTIF(数据重组!$H$1:$H$19,E3),1)
具体操作过程及效果如下动图所示：
5 总结与思考到此，给大家介绍了两种操作多级下拉菜单联动的方法：名称法和公式法。
名称法，理解简单，操作繁琐程度与数据选项的多少有关系；公式法，理解需要对公式进行分析，但是操作起来方便，尤其对于大批量数据。
同时请小伙伴们思考一个问题：在讲解案例当中，二级学院的专业都是唯一的，也就是没有重复值。那如果有重复值应该如何解决这样的问题呢？
如下案例：
为方便小伙伴们学习，进阶君将原始素材共享出来，获取素材的方法：
第一步：关注 Excel进阶课堂。
第二步：私信 Excel进阶课堂，因为设定的是自动回复，所以内容一定要准确
私信内容：练一练
第三步：根据得到信息打开网盘，找到 第24讲 公式法解决多级下拉菜单联动 工作簿 自行下载
,
