利用EXCEL数据有效性制作多级动态下拉列表【百科全说】

数据有效性通常是使用下拉列表来进行数据的输入的。设置数据有效性不仅能够有效避免手误原因造成的输入错误，而且还可以在单元格中创建下拉列表方便用户选择性的输入，十分的方便和快捷。
但在日常工作中，我们经常会遇到象下表一样的多级分类：
1、每一个分类下面都包含几个小的分类。
2、每一个分类所包含小类的名称可能相同，也可能不相同，数量也可能相等，也可能不相等。
3、这种分类组合之后的数据量非常大。
因此为了减轻填表人的劳动强度，防止填写错误，需要严格限定单元格内容，使其在选择一个上级分类之后，只能显示限定于该分类的下一级分类。
下面就以上表为例来详细说明如何利用数据有效性制作多级联动的下拉列表：

操作方法

01

1、在表的前面插入8列，每个分类两列，上表共四个分类，AB列为第一层分类，CD列为第二层分类，以此类推……在第一行输入相应标题如下图所示：
02

2、建立第一层的下拉列表。 1) 在A3单元格输入以下公式：=IF(COUNTIFS($I$3:I3,I3)=1,MAX($A$2:A2)+1,"")。此公式的目的在于将第一层的分类筛选出来。 2) 在B3单元格输入以下公式：=IFERROR(VLOOKUP(ROW()-2,$A:$Q,9,0),"") 此公式的目的是把筛选后的第一层分类整合到一起。 3) 下拉A3和B3里的公式到数据最后一行。结果如下图所示：
03

4) 定义名称。定义第一层分类的名称为MS_00，输入以下公式： =OFFSET(多级分类列表!$B$3,0,0,SUMPRODUCT((LEN(多级分类列表!$B$3:$B$300)>1)*1))。见下图：
04

5) 制作下拉列表。在U1、U2、U3、U4单元格输入四个分类如下。
05

在第一个分类（MS）后用数据有效性建立下拉列表如下图所示：
06

下拉列表建立后的结果如下：
07

3、建立第二层的下拉列表。 1) 在C3单元格输入以下公式：=IF(I3=$V$1,IF(COUNTIFS($K$3:K3,K3)=1,MAX($C$2:C2)+1,""),"")。此公式的目的在于将第一层所包含的第二层分类筛选出来。 2) 在D3单元格输入以下公式：=IFERROR(VLOOKUP(ROW()-2,$C:$Q,9,0),"")。此公式的目的是把筛选后的第二层分类整合到一起。 3) 下拉C3和D3里的公式到数据最后一行。结果如下图所示：
08

4) 定义名称。定义第二层分类的名称为MS_01，输入以下公式： =OFFSET(多级分类列表!$D$3,0,0,SUMPRODUCT((LEN(多级分类列表!$D$3:$D$300)>1)*1))。见下图：
09

5) 制作下拉列表。在第二个分类（MS1）后用数据有效性建立下拉列表如下图所示：
10

下拉列表建立后的结果如下：
11

4、建立第三层的下拉列表。 1) 在E3单元格输入以下公式：=IF(I3&K3=$V$1&$V$2,IF(COUNTIFS($M$3:M3,M3)=1,MAX($E$2:E2)+1,""),"")。此公式的目的在于将第二层所包含的第三层分类筛选出来。 2) 在F3单元格输入以下公式：=IFERROR(VLOOKUP(ROW()-2,$E:$Q,9,0),"")。此公式的目的是把筛选后的第三层分类整合到一起。 3) 下拉E3和F3里的公式到数据最后一行。结果如下图所示：
12

4) 定义名称。定义第三层分类的名称为MS_02，输入以下公式： =OFFSET(多级分类列表!$F$3,0,0,SUMPRODUCT((LEN(多级分类列表!$F$3:$F$300)>1)*1))。见下图：
13

5) 制作下拉列表。在第三个分类（MS2）后用数据有效性建立下拉列表如下图所示：
14

下拉列表建立后的结果如下：
15

5、建立第四层的下拉列表。 1) 在G3单元格输入以下公式：=IF(I3&K3=$V$1&$V$2,IF(COUNTIFS($M$3:M3,M3)=1,MAX($E$2:E2)+1,""),"")。此公式的目的在于将第三层所包含的第四层分类筛选出来。 2) 在H3单元格输入以下公式：=IFERROR(VLOOKUP(ROW()-2,$E:$Q,9,0),"")。此公式的目的是把筛选后的第四层分类整合到一起。 3) 下拉G3和H3里的公式到数据最后一行。结果如下图所示：
16

4) 定义名称。定义第四层分类的名称为MS_03，输入以下公式： =OFFSET(多级分类列表!$H$3,0,0,SUMPRODUCT((LEN(多级分类列表!$H$3:$H$300)>1)*1))。见下图：
17

5) 制作下拉列表。在第三个分类（MS3）后用数据有效性建立下拉列表如下图所示：
18

下拉列表建立后的结果如下：
19

完成四级联动下拉列表的制作。

利用EXCEL数据有效性制作多级动态下拉列表

操作方法

相关推荐