Excel中通过数据有效性设置下拉菜单功能可以帮助我们节省很多输入时间，通过选取下拉菜单中的值来实现输入数据，非常快捷、方便、并且能最大限度减少差错的发生。但是日常工作中，我们常需要一个下拉菜单，让后面的下拉菜单依据前面的下拉菜单的内容的改变而改变（也就是多级联动、动态更新的下拉菜单）。

目前网上能看到的教程大多是通过逐级进行定义名称，在通过indirect函数引用名称的方式实现联动菜单的效果。但此种方式有很大的局限性。具体来说有以下几点：

1. 定义名称繁琐。前一级菜单有多少个选项，就需要定义多少个名称，如果有上千个选项就需要定义上千个名称，工作量太大。

2. 选项可能不能定义为名称，名称的格式有要求：如必须以下划线或文字、字母开头，不能出现特殊的符号等，这样就限制了选项的范围。

3. 更新选项不便。若选项需要调整，需要逐级修改定义名称或范围，这对于需要经常更新的工作场景下显得非常不便。

那么有没有一种方法，可以避免上述的问题呢？答案是有的。今天我就这一方法首次公布于众，希望能给你的工作带来帮助……

我们先来看下最终的效果：（四级动态选择，若需要增加、减少选项只需在AreaCode工作表中进行操作，再选择全部刷新选项即可，更新维护非常简单。）

多级联动菜单演示效果

这一方法的实现思路是：

1. 利用数据透视表的去重功能得到不重复的选项数据。

2. 利用OFFSET函数实现对选项数据的动态范围引用，并将该动态区域定义为名称。

3. 利用数据有效性将名称作为数据源，从而形成联动效果。

下面我们就分步骤来看下如何实现这一效果：

第1步：将数据源定义为名称，方便后续建立透视表

第2步：在辅助表中建立数据透视表，为OFFSET函数提供数据

第3步：构建省份的动态引用

第4步：创建第2个透视表为城市提供数据

第5步：构建城市的动态引用

第6步：构建区县的动态引用

第7步：检查各项参数

第8步：新建名称

第9步：新建的名称及引用位置

第10步：为每个选项设置数据有效性

结语：其实excel多级联动菜单原理非常简单，只需要熟悉offset、match、countif、counta等函数的用法，就能轻松实现这一效果。在此基础之上，你还可以做出多级联动列表框的效果，是不是很酷呢？快来试试看吧！

多级联动列表框演示效果

千万别学excel