最简单的多级下拉菜单制作方法，不需要定义名称

作者：老菜鸟来源：部落窝教育发布时间：2020-06-11 14:31:14点击：4024

分享到:

收藏收藏人气：1人

版权说明：原创作品，禁止转载。

编按：

哈喽，大家好！多级下拉菜单网上有很多教程，但今天的方法是最简单的。不需要定义名称，只使用一个公式就可以制作二级、三级、四级甚至更多级的菜单。公式用的函数也很常见，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：函数哲学家，找巨人做伴。新出道必学！》。