Excel中下拉菜单功能可以帮助我们节省输入时间，通过选取下拉菜单中的值来实现输入数据，非常快捷、方便。但是日常工作中，我们常需要一个下拉菜单，让后面的下拉菜单依据前面的下拉菜单的内容的改变而改变（也就是联动的下拉菜单）。
一、二级联动下拉菜单制作方法
首先看一下原始数据，原始信息在一张工作表，第一行是省市名称，下面的若干行为对应省市下面的地名和区名，如图1。需要在另外一张工作表中A列和B列建立联动的二级下拉菜单，如图2。
图1
图2
操作步骤如下：
Step1:选中原始表所有数据，按快捷键F5调出定位对话框，定位条件选择“常量”，点击“确定”按钮，这样所有非空单元格被选中。如图3。
图 3
Step2:点击功能区菜单“公式”→“根据所选内容创建”，如图4，因为标题在首行，所以选择“首行”作为名称，点击“确定”按钮，如图5。操作完毕后在菜单“公式”下的“名称管理器”就可以看到定义的名称了，如图6。
图 4
图 5
图 6
Step3:在另外一张工作表创建标题行，省/直辖市和市/区，选中A2单元格，点击菜单“数据”→“数据验证”（注：2013版本的“数据验证”在2003、2007、2010版本对应“数据有效性”），验证条件选择“序列”，来源选中原始数据表的首行数据，如图7。这样，在A2菜单就生成了省市下拉菜单，如图8。如果需要在更多的单元格区域设置下拉菜单，就选中更多的单元格区域，比如A2：A20,切忌选中整列区域，如果选中整列，会导致在很多没有用的区域设置了数据有效性，增加了文件的虚拟内存，使得文件变大，文件变大会导致打开和各种操作都会非常慢。
图 7
图 8
Step4:同样的方法，选中B2单元格，点击数据验证，在“来源”处输入公式=INDIRECT($A$2)，点击“确定”按钮。设置完毕后，A2单元格选择“深圳市”时B2的下拉菜单返回“深圳市”的信息，如图10；A2单元格选择“北京市”时B2的下拉菜单返回“北京市”的信息，如图11。
注意：
上述二级下拉菜单设置的公式采取了行列都绝对引用，如果要使二级下拉菜单对更多的单元格区域均可用，将公式更改为：=INDIRECT($A2)即可。
图 9
图10
图11
indirect函数功能是返回并显示指定引用的内容，可引用其他工作簿的名称、工作表名称和单元格引用。制作多级下拉菜单的原理就是利用定义名称，然后在单元格输入与定义名称相同的字符，再对含有这种字符的单元格用Indirect作引用。
操作动画如下：
二、多级（三、四级）联动下拉菜单制作方法
下面介绍多级别的下拉列表的制作方法。
数据源按下面的顺序排序：
图12
在制作多级下拉菜单之前我们先来了解这几个函数，其语法和功能分别是：
Match(找什么，在哪里找，0):返回符合特定值特定顺序的查询值在数组中的相对位置；
Countif(条件范围，条件):计算区域中满足给定条件的单元格的个数；
Vlookup(找什么，在哪里找，显示序列，匹配参数):搜索表区域首列满足条件的元素，确定待检索单元格在区域中的行序号，再进一步返回选定单元格的值；
Offset(参考单元格，偏移的行数，偏移的列数，返回引用区域的行数，返回引用区域的列数):以指定的引用为参照系，通过给定偏移量返回新的应用。
了解了函数的功能，接下来按以下步骤操作：
Step1:在C2单元格借助于Match函数，计算“广东省”在A列中的位置，因此该公式为：=MATCH(B2,A:A,0)。随后将该公式分别复制至C3、C6、C7、C8、C9单元格即可计算对应的项在A列中的起始位置，该数值用于指导offset函数往下偏移几行；
Step2:接下来要计算每个项目共有几个小项，在D2中利用countif函数计算个数，此处的公式为：=COUNTIF(A:A,B2)。该数值可以用在offset函数中的返回行数中；
Step3:最后在G列设置一级省份下拉列表，如图13：
图 13
Step4:对二级“地市”设置数据有效性。因为我们需要根据一级G2单元格选择的不同，设置不一样的下拉列表，而每个一级“省份”会有不一样个数的二级“地市”，所以借助offset函数来完成。在H2单元格设置数据有效性的“来源”位置，输入以下公式：=OFFSET($B$1,VLOOKUP(G2,$B:$D,2,0)-1,0,VLOOKUP(G2,$B:$D,3,0),1)。
图 14
该公式的意思为：
以B1单元格为基准，往下偏移几行，往右不偏移列，返回引用区域的行数，返回一列的数据。那么往下偏移几行，要根据前面的G2单元格的内容变化，所以利用vlookup函数来查找G2单元格的内容，位于B:D范围中第二列的结果，我们便可以从B1单元格往下偏移6行至B7单元格，再减去1，得到“广州市”的B6单元格；同样的，返回引用区域的行数，也借助vlookup函数来得到，如此一来，二级下来列表的“地市”也就完成了。
图15
Step5: 接下来，我们就用同样的offset函数来制作三级下拉列表，因此在I2单元格的数据有效性的公式为：=OFFSET($B$1,VLOOKUP($H$2,$B:$D,2,0)-1,0,VLOOKUP($H$2,$B:$D,3,0))
图 16
最后的效果为：
图 17
那么有了这种方法以后，我们想设置任意级别的下拉列表都可以实现了。
长按二维码免费订阅，让平凡的Excel给你带来不平凡的力量，为职场生涯添光加彩！
作者聂美骐，原名聂春霞，微信号sunnynie,爱好针灸、易经，2001年-2005年在江西移动工作，2005年开始就职互联网大厂，2011年开始兼职公司内部Excel课程讲师，2015年4月出版图书《Excel高手捷径：一招鲜，吃遍天》，同年12月在台湾出版繁体字版。2017年5月出版第二版《Excel职场手册：260招菜鸟变达人》。Excel交流QQ群1：465693036（群已满），Excel交流QQ群2：806295411，欢迎各位粉丝加入群交流。
