【原】多级（三、四级甚至更多级）下拉列表的制作

在Excel中使用下拉列表的功能，能帮助我们限制填写的内容，保证数据的有效无误，常规的数据有效性（下拉列表）很容易掌握，可是如果要制作更多级别的数据有效性，就有点困难了。小编之前分享过数据有效性和二级联动下拉菜单的制作方法，点击文字可以查看。

Excel中怎样实现二级联动下拉菜单？

关于数据有效性的几个鲜为人知的技巧

今天给大家分享多级别的下拉列表的制作方法。

数据源按下面的顺序排序：

图 1

在制作多级下拉菜单之前我们先来了解这几个函数，其语法和功能分别是：

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列设置一级省份下拉列表，如图2：

图 2

Step4:对二级“地市”设置数据有效性。因为我们需要根据一级G2单元格选择的不同，设置不一样的下拉列表，而每个一级“省份”会有不一样个数的二级“地市”，所以借助offset函数来完成。在H2单元格设置数据有效性的“来源”位置，输入以下公式：=OFFSET($B$1,VLOOKUP(G2,$B:$D,2,0)-1,0,VLOOKUP(G2,$B:$D,3,0),1)。

图 3

该公式的意思为：

以B1单元格为基准，往下偏移几行，往右不偏移列，返回引用区域的行数，返回一列的数据。那么往下偏移几行，要根据前面的G2单元格的内容变化，所以利用vlookup函数来查找G2单元格的内容，位于B:D范围中第二列的结果，我们便可以从B1单元格往下偏移6行至B7单元格，再减去1，得到“广州市”的B6单元格；同样的，返回引用区域的行数，也借助vlookup函数来得到，如此一来，二级下来列表的“地市”也就完成了。

图4

Step5: 接下来，我们就用同样的offset函数来制作三级下拉列表，因此在I2单元格的数据有效性的公式为：=OFFSET($B$1,VLOOKUP($H$2,$B:$D,2,0)-1,0,VLOOKUP($H$2,$B:$D,3,0))

图 5

最后的效果为：

图 6

那么有了这种方法以后，我们想设置任意级别的下拉列表都可以实现了。

今天的分享到此结束，如果想看更多历史文章，请从菜单所有文章查看。