1 问题引入

有这样一个案例，数据如下图所示：

要求用公式法实现学校、二级学院、专业之间的三级下拉菜单联动。

2 问题分析

通过上篇教程我们可以知道，用公式法实现多级下拉菜单联动是从第二级菜单开始。

公式法的本质：将上一级菜单的选择项，在当前级菜单的数据对应项去查找，查找上一级选项的开始位置和个数，然后运用OFFSET函数去获取当前级菜单数据项的区域。

于是，在当前级的数据对应项中，上一级选项对应的数据区域不应该有重复，否则，就不能正确的查找到开始位置和个数。

如要完成的案例中，三级菜单对应的数据区域如下图所示：

我们要根据上一级菜单“二级学院”的选项来确定本级菜单“专业”的数据选项区域，按公式法通常做法，是根据上一级菜单的选项来确定它所在起始位置和个数，但是我们发现，如果我们选择软件学院的话，在对应的区域中会有2个数据区域与之对应，这样就无法确定起始位置和个数了。

怎么处理呢？将多个对应的数据区域由多个变成一个。

处理思路：更改三级菜单对应的数据区域，将三级菜单对应的上一级菜单变成一级菜单和二级菜单的联合。

这样处理后，专业对应的上一级菜单就是学校和二级学院的联合，如京东大学软件学院，在数据区域里面就只会有一个区域与之对应。由多个区域变成一个区域后，公式法就可以完美实现了。

在实际工作当中，我们得到的数据表最有可能是上图所示。我们要完成多级菜单联动，需要先形成各级菜单对应的数据关系表，然后再利用公式法完成。

（一）形成各级菜单对应的数据关系表

（1）巧用删除重复项，形成一级菜单数据表

第一步：复制表中“学校”这列数据到单独一列。

第二步：运用 数据菜单下的删除重复项 命令，得到一级菜单数据表。这种方法非常简单且高效。

具体操作过程及效果如下动图所示：

（2）巧用删除重复项，形成二级菜单数据对应表

第一步：复制表中“学校”和“二级学院”两列数据到单独区域。

第二步：运用 数据菜单下的删除重复项 命令，得到二级菜单数据对应表。

具体操作过程及效果如下动图所示：

（3）巧用公式，形成三级菜单数据对应表

第一步：运用公式将“学校”和“二级学院”两列数据联合在一起，放到单独一列中。

表中“学校”数据从B2开始往下，“二级学院”数据从C2开始往下，公式：=B2&C2，表示将两个单元格的内容联合在一起。其它区域采取公式复制的方式完成。

第二步：复制表中“专业”数据列到第一步位置右侧的区域。

具体操作过程及效果如下动图所示：

（二）运用数据有效性，完成一级菜单设定

根据一级菜单数据表，运有数据有效性完成一级菜单设定。具体操作过程及效果如下动图所示：

（三）运用数据有效性公式，完成二级菜单设定

选中H3单元格，设置数据有效性，设定内容如下图所示：

其中来源：=OFFSET($J$6,MATCH(G3,$I$6:$I$13,0)-1,0,COUNTIF($I$6:$I$13,G3),1)

对于公式不是很理解的，请查看进阶君的上一篇教程：

具体操作过程及效果如下动图所示：

（四）运用数据有效性公式，完成三级菜单设定

在前面，我们已经得到了处理后的三级菜单数据对应表，如下图所示：

此时我们要去找专业的话，应该根据一级和二级菜单的选择项的联合值去查找。在案例当中，一级和二级菜单的选择项的联合值可以用 G3&H3 得到。

选中I3单元格，设置数据有效性，设定内容如下图所示：

其中来源：=OFFSET($M$6,MATCH(G3&H3,$L$6:$L$21,0)-1,0,COUNTIF($L$6:$L$21,G3&H3),1)

最终完成效果如下动图所示：

在本篇教程中，讲解了对于不同的一级菜单选项，二级菜单选项出现重复值的情况，我们用了一种基本思想：将一级菜单选项和二级菜单选项进行联合，从而形成不出去现多个值相同区域。

这种方法学习后，在处理以后的多级下拉菜单联动时，都可以完美解决。

本站仅提供存储服务，所有内容均由用户发布，如发现有害或侵权内容，请点击举报。