|| 版本号：Excel2013. ||

====================

本篇来讲一个高级的用法，也是工作中常用到的。就是制作一个一级和二级的下拉菜单，而且二级菜单要随着

一级菜单的改变而改变。这个炫酷的功能可以用OFFSET结合MATCH函数做出来，如下：

需求是这样的：我们想把一级部和二级部做成一个下拉菜单的形式，而且随着我动态的增加一级部和二级部的名称，下拉列表

也要动态的随着改变。如下，我有增加了一个一级部和相应的二级部，那么下拉列表依然动态随着改变：

哈哈，是不是很炫酷，你是不是已经迫不及待的想要学习怎么做出这样的表格了呢？下面是教程。

==========================================================

其实主要的原理就是给单元格设置数据有效性，而数据有效性设置中就嵌套着我们用函数引用来的名称。

具体操作如下：

（1）我们首先来将一级部的所有名称引用到，这里我用的公式如下

OFFSET(Sheet7!I2,0,0,COUNTA(Sheet7!I:I)-1,1)

解释一下：OFFSET函数中使用的基点为“I2”单元格（因为我的表名字为Sheet7所以这里自动采用了绝对引用），

然后不发生偏移，即行列方向上偏移都为0。那么我想要的就是从 I2 开始一直到该列结束的所有非空

单元格，因此我们首先用COUNTA计算 I 列的所有非空单元格数目，再减去1（即减去了一级部那个

单元格）。那么从 I2 开始（包括 I2 ），行高度我们就要这么多行就可以了，然后列高度呢当然是一列

啊。因此OFFSET就构成了上面的公式，我们也就成功的把 I 列中所有一级部的名称给引用到了。

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

（2）下面要做的就是我们将引用到的一级部区域形成名称，操作如下：

首先选中我们的单元格：

接着点击“公式”选项卡中的“名称管理器”如下：

然后在名称管理器中，点击新建，如下：

最后我们输入下面的信息：

在名称中我们任意命名即可，在范围选择你的表格的有效范围（因为我的所有数据都在Sheet7中，因此

选择Sheet7），然后在引用位置上输入（1）中的公式。最后点击确定即可。

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

（3）已经形成了名称，下面只要对目标单元格设置数据有效性即可。依然是选中目标单元格（与

第（2）中的目标单元格一样），如下：

然后选择“数据”选项可中的“数据验证”，如下：

在“数据验证”中，如下输入：

主要就是来源选择我们刚刚自定义的名称即可。点击确定。

（4）这样一级部的下拉菜单已经做完，如下：

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

（5）下面做二级部的下拉菜单。原理步骤与一级部的下拉菜单完全相同。只是函数公式

比较复杂，如下：

OFFSET(I1,MATCH(E3,I:I,0)-1,1,1,COUNTA(OFFSET(J1:L1,MATCH(E3,I:I,0)-1,0)))

解释一下：a.我们首先要根据E3中一级部的内容搜素到它在 I 列中的位置，这个要借助MATCH函数。

b.然后在OFFSET函数中，基点我们选择 I1 单元格，只需要让 I1 往下移到a中的位置

列上往右偏移一列即可。比如一级部先向下偏移到产品开发部，又往右移到开发管理

这个单元格上。

c. 接着我们要在b产生的新基准点上计算相应二级部有多少非空单元格。也就是行高

为1，而列高就是二级部的个数，我们用COUNTA来计算。思路就是我们发现二级部

的列区域不会变（是从J1：L1)，只要我们找到相应二级部对应的行区域即可。这个就

再次借助OFFSET函数来产生引用。在公式中的蓝色标注部分，J1：L1这个基准向下偏移

的量其实跟 a中 I1 向下偏移的量一样，然后列不发生偏移，缺省行高和列高，那么就会得到

引用的行高和列高与J1：L1一样，而偏移到了相应二级部上。这样我们再用COUNTA计算

这个引用的非空单元格数目就是相应二级部的个数，也就是最外层的OFFSET应该有的

列高了。

好了这个公式解释完毕，如果还不懂，多看几次即可。

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

（6）这样根据一级部的引用我们获得了二级部的引用，下面形成名称，

首先选中单元格：

然后自定义名称，如下：

点击确定。

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

（7）然后给目标单元格设定数据有效性即可。如下：

选中单元格：

设定有效性：

点击确定即可。

看看效果吧，如下：

=============================================================

https://www.wukong.com/answer/6469352324841079053/?iid=16893732201&app=news_article&share_ansid=6469352324841079053&tt_from=android_share&utm_medium=toutiao_android&utm_campaign=client_share

转载请注明：徐自远的乱七八糟小站 » 【在excel中如何做二级下拉菜单和三级下拉菜单？】