思路决定出路，制作任意n级动态下拉菜单，一个函数搞定~

每天一篇Excel技术图文

微信公众号：Excel星球

NO.1060-动态n级下拉菜单

作者：看见星光

 微博：EXCELers / 知识星球：Excel

HI，大家好，我是星光。

今天给大家分享的表格技巧是使用函数制作动态多级下拉菜单。如果你还不晓得什么是下拉菜单，可以参考往期推文：

 ↑ 九个小动画，带你认识数据验证

而动态多级下拉菜单，顾名思义，就是动态的多级下拉菜单

Σ(⊙▽⊙'a……

还是看个不带书记颜色的小片子吧▼

如上图所示，输入省份，可以动态选择省内的市；输入市，可以动态选择市内的区县；输入区，可以动态选择相关街道。

如果有需要，你还可以设置相关村落或社区。

这看起来就挺好玩的——但那么如何制作这样的下拉菜单呢？

一般会认为这种嵌套复杂的下拉菜单，只能使用VBA来解决。但，实际上，使用函数制作会更简单，只需三步即可搞定。

首先，准备一份数据源。

 ↑ 数据源，注意数据结构 

这里需要注意的是数据源的数据布局结构。

第1行是一级菜单的选项，本例是几个省的名字。

除了第1行外，第1列是父层名称，同行为父层下的子层的数据。比如A4是福建，同行(第4行)则是福建省的部分市的名称。A9是厦门，同行（第9行）则为厦门市各区县的名字。

……

然后，制作一级下拉菜单。

选中目标区域，比如B2:B10。在【数据】选项卡下依次点击【数据验证】→【设置】→【序列】→【来源】

编写公式如下：

=OFFSET(数据源!$B$1,0,0,1,COUNTA(数据源!$1:$1)-1)

最后，制作多级下拉菜单。

选中目标区域，比如C2:E10。在【数据】选项卡下依次点击【数据验证】→【设置】→【序列】→【来源】

编写公式如下：

=OFFSET(

数据源!$B$1,

MATCH(B2,数据源!$A:$A,)-1,

0,

1,

COUNTA(

    OFFSET(数据源!$1:$1,MATCH(B2,数据源!$A:$A,)-1,0))-1

)

打个响指，就这么着，动态多级下拉菜单就制作完成了，是不是很简单？┓( ´∀` )┏

……

如需制作更多层次的下拉菜单，只需在数据源补充数据，同时调整上述操作第3步的区域覆盖范围即可。

比如新增5级菜单：社区。

 ↑ 补充数据源数据，注意数据结构 

不需要修改公式，直接将多级下拉菜单的覆盖范围调整为C2:F10。

调整后演示效果如下：