点亮☆星标，不错过精彩分享

在日常工作中，有时我们为了方便输入，会在EXCEL中，给一些固定不变的内容，设置下拉菜单选项，以提高输入效率和数据规范性。

但有时，数据选择的归属性级别比较多，大多数据时候，很多人只会设置一级，后面就不会设置了。

今天我们一个教程讲清楚一二三级菜单设置，教程可能会有点长，建议大家先转发收藏，慢慢学习，避免以后需要时找不到了。

我们以全国省-市里-区，三级归属分级为例说明。

一、数据准备与整理

在做三级菜单前，我们需要建一个表，对下拉菜单的选项数据进行整理和排序，要按一二三级归属关系进行整理排列。

一级包含二级，二级包含三级，以此类推。每个级别间，要排序好。

如果是省、自治区的，一级上省级名称，二级是城市、三级是城市下的区和县。如果是直辖市，一级是直辖市名称，二级是下属区和县，没有三级了。

我们把这个表称为【菜单目录】。

二、一级菜单设置

我们先明白一个逻辑，所有设置下拉菜单选项的方式，都是通过【数据有效性】做的。

旧版本的EXCEL叫【数据有效性】，新版本的EXCEL叫【数据验证】，WPS叫【有效性】。

在新一个要输入这些数据的表中，建类似这样的级别输入列。

一级菜单很简单，选中要输入省级名称的单元格，点菜单-数据验证-序列，把【菜单目录】表A列省级地址全部选中，就行了，EXCEL会自动去除重复值。

三、二级目录

二级菜单的逻辑是：根据一级菜单选择的省份，返回对应的下属城市列表，比如我一级选择上海，就给我返回上海对应的下属所有分区。

所以，逻辑是，先根据一级选择的省份名称，找到A列中该省份名称开始的位置，然后计算A列有几个该省份的单元格，返回B列对应个数的单元格，就是下属的地址了。

先找到第一个上海的位置（A18）,再计算A列有几个上海，B列就有几个下属名称，这个逻辑必须了解清楚。

这个动态取值的需求，我们用OFFSET来实现。可能会有同学问：OFFSET太复杂了，不好理解，为什么不用FILTER函数？

因为数据有效性，不支持FILTER这类动态数组公式，所以必须用OFFSET。

我们先在表格空白处，写公式测试一下效果，输入公式：

=OFFSET(城市列表!$A$1,MATCH(A2,城市列表!$A$1:$A$1510,0)-1,1,COUNTIF(城市列表!$A$1:$A$1510,$A2))

简单讲一下公式结构：

=OFFSET(目录表A1，MATCH(寻找上海的位置)-1，向右一列取B列，countif(计算有几个上海))

把这个公式复制粘贴到数据有效性-序列中

四、三级下拉菜单

明白了二级菜单的制作，三级也是一个原理。只不过，我们要寻找匹配的是二级菜单选择的内容。

=OFFSET(城市列表!$B$1,MATCH($B2,城市列表!$B$1:$B$1510,0)-1,1,COUNTIF(城市列表!$B$1:$B$1510,$B2))

=OFFSET(目录表B1，MATCH(寻找呼和浩特的位置)-1，向右一列取B列，countif(计算有几个呼和浩特))

同样把公式复制到有效性中

五、总结

所以，我们会发现，不管几级菜单，关键点在于数据源根目录表的数据整理和排序，必须要把下拉菜单选择的内容进行分类、归纳、排序。如果目录没整理好，这个工作无法完成。

如果还有四、五级等更多级别的菜单，也是一样的道理。

你学会了吗？需要这个全国省市城市行政区数据素材练习的小伙伴，关注我，公众号发私信：多级菜单