Excel二级联动动态下拉菜单怎么做

Excel二级联动动态下拉菜单怎么做

二级联动下拉菜单经典做法是采用数据验证（数据有效性）+定义名称+INDIRECT函数，但用这种方法制作的下拉菜单缺乏扩展性，不能自动添加新的菜单项；并且修改麻烦，如果菜单项名字有变动，必须修改定义名称或引用位置。

为弥补经典静态二级下拉菜单的不足，经过研究自创了动态二级联动下拉菜单，下面给大家分享下。如果对大家有所帮助，还请点赞支持，您的支持是我最大的动力，谢谢。

动态二级联动下拉菜单采用方法：数据验证（数据有效性）+OFFSET+COUNTA+MATCH函数。

首先来看下我们的数据源，是关于险种分类和每个分类下面的产品数据，需要做一个选了险种类型后，能自动出现该险种类型下面对应产品的二级菜单。

第一步：制作一级下拉菜单

选中需要设置一级下拉菜单的单元格，接着找到【数据】选项卡，点击【数据验证】按钮，进入“数据验证”设置页面。

“设置”页签下面的验证条件“允许（A）”选择“序列”，“来源（S）”填入公式“=OFFSET(数据源!$A$1,0,0,1,COUNTA(数据源!$1:$1))”，点击【确定】按钮，完成设置。

公式解析：

“=OFFSET(数据源!$A$1,0,0,1,COUNTA(数据源!$1:$1))”是组合嵌套函数，用于确定一级菜单下拉选项区域。

OFFSET函数：以指定的引用为参照系，通过给定偏移量得到新的引用范围。

该函数有五个参数，第一个参数是参考单元格；第二、三个参数分别是相对于参考单元格行和列方向的移动数（偏移量）；第四、五个参数是指行和列的扩展，即由前面参数确定了一个新的参考点，那么以这个新的参考点为准，需要取多少行和多少列的数据范围。以该例来看，我们以“数据源$A$1”为参考系，在行和列上都不需要位移，所以第二和第三参数都为0，由于只取一行，所以第四参数为1，但是第五个参数列数是不确定的，只能通过COUNTA函数。

COUNTA函数：返回参数列表中非空的单元格个数

所以设置公式COUNTA(数据源!$1:$1)，可以得出第一行有多少个非空单元格，也就确定了一级菜单该取的列数。

经过上一步操作，我们已经成功完成一级菜单设置，菜单效果如下。

第二步：制作二级下拉菜单

选中“B2”单元格，点击【数据】选项卡下面的【数据验证】按钮，进入“数据验证”设置页面。

“设置”页签下面的验证条件“允许（A）”选择“序列”，“来源（S）”填入公式“=OFFSET(数据源!$A$1,1,MATCH(A2,数据源!$1:$1,0)-1,COUNTA(OFFSET(数据源!$A$1,1,MATCH(A2,数据源!$1:$1,0)-1,1000,1)),1)”，点击【确定】按钮，此时由于A2单元格没有值，会出现错误提示，忽略错误提示，点击【是】按钮，即可完成设置。

关于二级菜单公式解析：

二级菜单仍延用一级菜单思路，采用OFFSET函数确定菜单取值区域，具体参数说明，请参考一级菜单公式解析，在此就不再赘述。

我们重点来看二级菜单PFFSET参数的第三和第四参数。

第三参数：MATCH(A2,数据源!$1:$1,0)-1

MATCH函数返回A2单元格对应的数据源列数，比如返回的值是2，那么以数据源!$A$1只需要向右移动一列，所以要将MATCH返回的值减去1，才是真正需要移动的列数。

第四参数：COUNTA(OFFSET(数据源!$A$1,1,MATCH(A2,数据源!$1:$1,0)-1,1000,1))

采用嵌套函数，里面的OFFSET函数主要是确定一个大概的二级菜单选项，由于二级菜单事先不清楚会有多少项，所以里面的OFFSET函数第四参数，给了足够大的数值1000，以确定一个1行1000列的数据范围，但1000列肯定会有空格，这是用COUNTA函数就可以确定有值的单元格数目。

最后实现的效果，我们看到可以动态添加一级和二级菜单选项。

动态联动下拉菜单有点：

1、具备扩展性，可以随时添加修改菜单字典；

2、修改简单，新增一级和二级菜单项放置到对应区域，下拉菜单会自动扩展。