59如何制作动态二级下拉菜单

昨天在视频号给大家分享了如何使用Excel制作二级下拉菜单↓

该制作方法优缺点都很明显，优点是操作简单见效快，缺点是当数据源发生了改变，下拉菜单的内容并不能动态更新。

今天再给大家补充一下如何制作动态二级下拉菜单，可以使下拉菜单的内容随数据源的改变而自动增减。

完成后效果如下：

准备数据源

首先准备一张数据源表，数据源结构如下图所示。标题行是一级菜单的内容，本例为省名，其余为二级菜单的内容，本例为市名。

制作一级动态下拉菜单

切换到需要制作下拉菜单的工作表，选中目标单元格区域，例如B2:B12。在【数据】选项卡下单击【数据验证】命令，打开数据验证对话框，【验证条件】设置为序列，来源输入以下公式，最后单击【确定】按钮关闭对话框。

大地主OFFSET ▼
=OFFSET(数据源!$A$1,0,0,1,
COUNTA(数据源!$1:$1))

解释下公式的意思。

COUNTA(数据源!$1:$1)统计数据源表第1行不为空的单元格个数，本例返回结果为4。

OFFSET函数以数据源表A1单元格为起点，向下扩展1行，向右扩展4列，返回A1:D1区域。

当数据源第1行的数据增加或删除时，COUNTA函数返回的结果会改变，导致OFFSET函数扩展的区域改变，于是下拉菜单的内容也就随之动态更新。

制作二级动态下拉菜单

选中目标单元格区域，例如C2:C12。在【数据】选项卡下单击【数据验证】命令，【验证条件】依然设置为序列，来源输入以下公式，最后单击【确定】按钮关闭对话框即可。

两个大地主OFFSET ▼
=OFFSET(数据源!$A$2,0,
MATCH(B2,数据源!$1:$1,0)-1,
COUNTA(
OFFSET(数据源!$A$2:$A$100,0,
MATCH(B2,数据源!$1:$1,0)-1)
)
)

上面这个公式使用了两个OFFSET函数，看起来颇为复杂。

外层OFFSET函数以数据源的A2单元格为基点，向右移动到B2省名所在的位置（由MATCH函数计算得出），然后向下扩展的行数有里层OFFSET函数计算得出。

里层的OFFSET函数以数据源的A2:A100为基点，向右移动到目标省份所在列，再使用COUNTA函数统计该列非空单元格的个数，作为外层OFFSET函数向下扩展的总行数。

看我真诚的小眼睛，如果你是函数新手，一时无法理解这条长长的函数公式，那是很正常的事情；毕竟不是每个人都像边同学那么聪明，此时更推荐你从新阅读往期OFFSET函数教程：

说来你不信，OFFSET其实是台游戏机👇

……

打个响指，以下是更多有关下拉菜单的往期教程，推荐给需要扩展阅读的男朋友和女朋友们

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