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

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

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里的公式到数据最后一行。结果如下图所示：

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

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

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

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

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里的公式到数据最后一行。结果如下图所示：

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

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

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里的公式到数据最后一行。结果如下图所示：

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

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

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

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里的公式到数据最后一行。结果如下图所示：

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

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

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

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