二级联动菜单指的是，当我们选择一级菜单之后，对应的二级菜单会随着一级菜单的不同而选项也不同。二级菜单的创建方法有很多种，今天我给大家讲解最长用的创建方法：通过Indirect函数法和OFFSET函数法。

1、INDIRECT函数法

案例：如图2-29所示，我们要创建省份是一级下拉菜单，对应的市名是二级下拉菜单的联动菜单。

 

①为省市创建“名称”

名称是一个有意义的简略表示法，可以在Excel中方便的代替单元格引用、常量、公式或表。比如将C20:C30区域定义为名称：MySales，那么公式=SUM（MySales）可以替代=SUM（C20:C30），可见名称比单元格区域更具有实际意义。

Step1：按住Ctrl键，分别用鼠标选取包含省、市名的三列数据，主要不要选择空单元格。（也可以通过Ctrl+G调出定位条件，设置定位条件为在常量来选取数据区域）

 

提示：不能直接框选B1:D6的数据区域，因为含有空单元格，这样创建的下拉菜单会有空白选项；也可以使用【Ctrl】+【G】定位方法快捷选中非空单元格。

Step2：在菜单栏中切换到【公式】选项卡→选择【定义的名称】分区→点击【根据所选内容创建】，在弹出的菜单中，勾选【首行】选项，如图2-31所示，这样就创建了三个省份的“名称”，“名称”的值为对应的城市名。

 

②创建联动菜单

Step1：创建一级菜单，为区域中的省份一列创建一级菜单，如图2-32所示，创建方法通过“引用区域”的方式，直接将图2-30中的B1:D1区域作为数据来源，这里不再赘述。

 

Step2：为上图中的“市”创建二级菜单

选中【市】列需要设置的单元格区域→在验证条件中选择【序列】→【来源】中输入公式=INDIRECT($C3)→点击【确定】，此时会弹出错误提示，点击【是】继续下一步即可，如图2-34所示。

 

提示：这里出错的原因是此时C3单元格中为空，还未选择省份的数据，找不到数据源，不影响二级菜单的设置。

完成之后，就实现了二级联动菜单，如图2-34所示。

 

实现二级联动菜单的核心是：定义名称和INDIRECT函数，理解这两个核心是解题的关键。

原理①：根据“名称”的作用，当我们把图2-30中C2:C5区域定义为名称“江苏省”时，那么在函数引用中，“江苏省”能够代替C2:C5区域；

原理②：INDIRECT函数为间接引用，他可将文本转化为引用。如图2-35所示，A2单元格中放的是文本“C4”，直接引用的话返回值就是“C4”；而是使用INDIRECT函数间接引用，他可将“C4”转化为对单元格C4的引用，因此返回的值是单元格C4中放置的值“SeniorExcel”。

 

案例2中，将原理①和原理②结合起来，图2-33中=INDIRECT（$C3）的意思是，首先C3单元格中的值是“江苏省”，而INDIRECT可以将文本换成引用，而“江苏省”已经定义为名称，代表的是图2-30中C2:C5单元格区域，所以二级下拉菜单中出现的南京市、苏州市等。

2、OFFSET函数法

OFFSET函数是我最喜欢的Excel函数之一，她在构建动态区域方面无可匹敌。首先我们来回顾OFFSET函数的语法：

OFFSET(reference, rows, cols, [height], [width])

reference：作为参照的单元格引用

Rows：向上或向下偏移的行数

Cols：向左或向右偏移的列数

Height：高度，需要返回的引用的行高。 Height 必须为正数。

Width：需要返回的引用的列宽。 Width 必须为正数。

OFFSET函数语法

接下来我们来通过一个OFFSET函数案例加深对函数语法知识的掌握。

 

当然，仅仅是引用区域，是没有太大用处的，OFFSET函数的神奇之处在于，通过引用构造动态区域，从而完成复杂的数据汇总、高级动态图表、多级下拉菜单等！

▌案例

如图所示，根据AB两列的城市列表，在黄色区域设置二级下拉菜单，即在黄色区域选择不同的省份，城市下拉菜单中出现的是对应省份的城市。

 

Step1：设置一级下拉菜单

 

Step2：创建二级菜单的名称

在【新建名称】对话框中，【名称】处填写“二级菜单”，引用位置填写如下公式：

=OFFSET($A$1,MATCH($D$3,$A$2:$A$12,0),1,COUNTIF($A$2:$A$12, D$3),1)

 

Step3：设置二级下拉菜单

 

二级下拉菜单中的公式怎么解读呢？

①先说MATCH($D$3,$A$2:$A$12,0):

查找省份在列表中的出现的第一个位置，返回值作为OFFSET函数的第二个参数，表示向下偏移的行数

②再说COUNTIF($A$2:$A$12, D$3):

这个简单，是用来统计省份在列表中的个数，比如“江苏”，通过MACTH函数查找到第一个位置，然后通过COUNTIF函数计算出现的总个数5.

这个结果作为OFFSET函数的第四个参数表示返回区域的行数。

③最后合成公式（以江苏为例）:

=OFFSET($A$1,MATCH($D$3,$A$2:$A$12,0),1,COUNTIF($A$2:$A$12, D$3),1)

以A1为参照，向下偏移5行（江苏在A2：A12列表区域的第五行），向右偏移1列，行高为5（江苏总共有5行），列宽为1列。这个区域正是江苏对应的城市清单