下拉菜单的“万能”公式，10级菜单，瞬间搞定【excel教程】

编按：哈喽，大家好！多级下拉菜单网上有很多教程，但今天的方法是最简单的。不需要定义名称，只使用一个公式就可以制作二级、三级、四级甚至更多级的菜单。公式用的函数也很常见，offset、match、countif。赶紧来看看吧！

---

制作二级三级菜单已经不是新问题了，关于这方面的教程咱们之前也分享过很多，比如《还不会做Excel三级下拉菜单？其实它跟复制粘贴一样简单》。

传统的方法，要做出二级三级菜单，少不了定义名称这个步骤，而且对于菜单内容（数据源）的排列方式要求比较高，并且当不同选项下的内容数量不一样多时，下拉选项中会出现空白项。

今天要分享的多级菜单制作方法，在操作上大大降低了难度，而且不管制作多少级的下拉菜单，都是一个公式套路搞定。还是用一个省、市、区的数据来做介绍，数据源如下。

在进行下拉菜单的设置之前，还是需要对这个原始数据源做点处理，不过非常简单。

第一步：将省这一列复制出来，删除重复项。

第二步：将省市这两列复制出来，删除重复项。

第三步：将市区这两列复制出来，因为数据只有三级，所以市区是不会有重复项的。

如果还有四级五级菜单，相信也知道该如何处理了吧，至此，数据源就处理完成了。

接下来进入下拉菜单的设置，同样非常简单。

一级菜单设置，直接使用数据验证（数据有效性）最基本的序列即可。

注意：一级菜单的内容相对比较固定，所以直接选择数据源区域即可。这里下拉选项的位置和数据源的位置是为了动画演示方便才放置到一个sheet里，实际使用中，数据源可以单独存放在一个sheet里。下拉选项的位置根据自己的需要灵活设置即可。

二级菜单设置，这一步开始，就要用到今天的主角了，由OFFSET、MATCH和COUNTIF共同构造的一个公式套路，公式为：

=OFFSET($R$1,MATCH(G2,Q:Q,0)-1,,COUNTIF(Q:Q,G2))

千万不要被这个公式吓住，其实这个公式是很好理解的，以下就为大家破解这个公式的秘密。

首先我们要明白OFFSET这个函数是干什么的。

简单来说，OFFSET是一个引用函数，可以为我们得到一个特定的单元格区域（可以理解为得到该区域中的一组数据），例如上面这个公式表面上得到的是一个错误值：

其实当我们在编辑栏选中公式，按F9键以后，看到的是这样的结果：

之所以显示错误值，是因为在一个单元格里无法显示出一个区域（四个单元格）的内容。

也就是说，公式得到了福建省所对应的市所在的区域，当省（G2单元格）的内容变化以后，公式结果也会随之变化，还是通过F9键来看看变化后的结果。

或许大家发现了，这里的数据是智能调整的，也就是说，对应几个市就显示几个市。

为什么会有这样的效果呢，这就要从OFFSET的五个参数来说起了。

OFFSET（起始位置，行偏移量，列偏移量，高度，宽度），一般的教程里会这样解释OFFSET的五个参数，本例中，只用到了其中的1、2、4三个参数。

如果我们要得到某个省所对应的市，必定要在R列确定具体区域，因此第一参数使用$R$1就不难理解了，但是不同的省，范围的起点是变化的，例如安徽省就要从第二行开始，福建省就要从第五行开始，这个问题就需要第二参数也就是行偏移量来起作用了。

行偏移量是个数字，当起始位置固定不变的时候，行偏移量的变化能使最终的区域发生变化。而要确定行偏移量，MATCH是最合适的。

MATCH(G2,Q:Q,0)的作用就是找到G2（某省）在Q列的第几行首次出现，例如安徽省首次出现在第二行，但是请注意，第二行相对于第一行来说，行偏移量是1。因此OFFSET的第二参数应该是MATCH(G2,Q:Q,0)-1，如果还不清楚MATCH的用法，可以参考以往的教程《MATCH：函数哲学家，找巨人做伴。新出道必学！》。

第三参数列偏移量也是同样的道理，本例中不涉及，所以直接逗号省略，进入第四参数。

可以说在MATCH的协助下，OFFSET准确定位到了目标区域的起点，那么目标区域到底是几个单元格呢？每个省所对应的市不一样多，目标区域也就不一样大。

对于一列数据来说，区域的大小就是高度（行数），在本例中要确定这个指标用COUNTIF就非常方便了，COUNTIF(Q:Q,G2)的作用很显然，就是确定要引用的省在Q列的个数。

同样本例的数据都是单列，不涉及宽度（列数）的问题，第五个参数也就用不到了。

想更深入了解OFFSET函数的小伙伴，可以查看往期文章《Excel进阶之路必学函数：动态统计之王——OFFSET（上篇）》。

至此，OFFSET已经准确得到了区域的起点和高度，接下来只需要将这个公式应用到数据验证（数据有效性）中即可。

方法非常简单，在序列中将公式复制进去就好了。

至此，一个智能的二级菜单设置完毕，再次说明，这里的智能指的是可以按照选项内容的多少自动进行调整，避免了空白选项的出现。

三级菜单的设置方法完全一样，只是需要修改一下公式，由于公式的原理完全一样，只是修改位置，所以有个直接用鼠标修改的方法，大家可以参考。

可以说，只要掌握了OFFSET-MATCH-COUNTIF这个公式套路，你就可以随心所欲的制作多级智能菜单了。

＊＊＊＊部落窝教育-excel多级下拉菜单＊＊＊＊

原创：老菜鸟/部落窝教育（未经同意，请勿转载）

更多教程：部落窝教育