【excel】设置二级可变联动菜单

之前做完了 【excel】设置可变下拉菜单（一级联动下拉菜单），开始做二级联动菜单。

【需求】在一级菜单选定后，二级菜单联动显示一级菜单下的可选项

示例：A、B列为原始明细数据。
K2单元格为一级菜单，在K2单元格内容选定后，要求在L2单元格的下拉菜单中，显示K2对应的内容。
如，K2单元格为”一班“时，L2下拉菜单中，显示”一班“成员的列表：

【步骤】

在【excel】设置可变下拉菜单（一级联动下拉菜单）中，我们曾做过一个辅助列（辅助单元格），在此处具体步骤略。
我们从这篇文章的内容出发向下走。所以，当前我们的表格已经是这样的：

我们的目标是在G2单元格做二级下拉菜单。

step1 制作辅助列

1.列转行

辅助列D1单元格的公式写好以后，我们先将里面的内容横向展开。
选择I1单元格输入公式：=OFFSET($D$1,COLUMN(A1)-1,0)&""，然后横向拖动，多填几列（比如填充10列，就是预留出10个班级…具体根据实际需要）
OFFSET()公式，在这里是取D1单元格中的内容，这里COLUMN(A1)中的单元格是随着拖动公式而变化的。
在I1中，偏移量是1-1=0，OFFSET($D$1,COLUMN(**A1**)-1,0)的结果是D1单元格中的第一项，也就是”一班“；
公式拖动到J1的时候偏移量是2-1=1，OFFSET($D$1,COLUMN(**B1**)-1,0)的结果是D1单元格中的第二项，也就是”六班“；
公式向右拖动n个单元格，就是偏移n-1，相应的取第n项（注意这里的逻辑，从第一项，偏移n-1个，就是第n项，没问题吧？）。

公式后面的&“”，是将向右拖动的结果中的0替换为空。
因为拖动公式的单元格数量可能超过D1单元格中班级的数量，如果不写&""，后面的结果中将出现0，这是我们不想看到的：

&""可以将结果强制转换成文本，这样结果中的0就不再显示出来了。

2.在辅助列中匹配班级成员

在I2单元格写入公式=INDEX($B:$B,SMALL(IF($A$1:$A$100=I$1,ROW($1:$100),4^8),ROW(A1)))&""，注意绝对引用的位置：

解释一下公式，从内往外展开：
IF($A$1:$A$100=I$1,ROW($1:$100),4^8)，绝对引用A列的前100行，也就是”班级“列的内容，虽然这里也包含了不需要的表头，但是没关系，并不影响结果。
这部分是一个数组公式，它检查A1:A100中的每一个单元格是否等于I1单元格的值。
如果某个单元格的值与I1相等，则返回该单元格的行号（例如，如果A5等于I1，则返回5）。
如果不相等，则返回4^8（即65536，这是一个远大于100的数字，因此不会影响后续SMALL函数的结果）。

其实公式=row(1)是会报错的，但是上面的公式不会报错。所以为了更清楚的理解和公式的严谨，可以将上面的if()函数写成IF($A$1:$A$100=I$1,ROW($A$1:$A$100),4^8)
SMALL(IF($A$1:$A$100=I$1,ROW($1:$100),4^8),ROW(A1))，用来返回数组中的第k个最小值。
if()函数中返回的是A列的行号或者65536。如果匹配I1则返回的是行号。
本例中，if()函数先判断A1与I1，然后返回65536；再判断A2与I1，然后返回2；再判断A3与I1，返回行号3；判断A4与I1，返回65536；……
也就是if函数返回的（数组）值为{65536，2，3，65536，……}。
因为=INDEX($B:$B,SMALL(IF($A$1:$A$100=I$1,ROW($1:$100),4^8),ROW(A1)))&""是要下拉的，所以row()函数是变动的，会变成row(A1)、row(A2)、row(A3)……row(A100)。

small()函数是返回数组中第k个最小值，那么就是依次返回第一、第二、第三……个最小值，那么就是返回与I1单元格的值相等的A列值的行号，也就是数组中的2，3，……
INDEX($B:$B,SMALL(IF($A$1:$A$100=I$1,ROW($1:$100),4^8),ROW(A1)))
INDEX函数的基本语法是INDEX(array, row_num, [column_num])，其中Array是必需的参数，表示单元格区域或数组常量。Row_num和Column_num是可选参数，分别表示行号和列号。如下图，再X1到X5单元格区域中，返回第2行的值：

因此INDEX($B:$B,SMALL(IF($A$1:$A$100=I$1,ROW($1:$100),4^8),ROW(A1)))是返回B列中，与I1单元个的值相等的A列的行号对应的值，也就是B2、B3……
将I2单元格中的公式向右、向下填充，得到结果：

因为公式的结尾有&""，所以匹配不上的显示为空，而不会显示#N/A等报错信息。

step2 名称管理器

选中有公式的I到S列，向下选中100行（行数根据实际需要）。为了方便演示，我选择了20行。
选中这个区域以后，【公式】-【根据所选内容创建定义的名称】：

只选择【首行】，单击确定：

点击上方【名称管理器】，发现已经创建好了名称：

随便点开一个，就能看到它所对应的名称及单元格区域：

关闭名称管理器，返回excel编辑界面。

step3 制作二级下拉菜单

选中二级下拉菜单的单元格G2，然后【数据】-【数据验证】-【数据验证(V)】进入数据验证对话框：

选择【序列】，在来源中输入公式=indirect($F$2)，点击确定：

这时，二级下拉菜单也做好了：

但是这里有个问题，就是下拉菜单里面有空白。

step4 消除二级菜单中的空白

选中二级菜单项，也就是本例中的G2，再次进入【数据】-【数据验证】-【数据验证(V)】，将【设置】选项卡下【来源】中的公式由刚才的=indirect($F$2)替换为：=OFFSET(INDIRECT($F$2),0,0,MATCH(CHAR(1),INDIRECT($F$2),-1),1)：

尝试解释一下=OFFSET(INDIRECT($F$2),0,0,MATCH(CHAR(1),INDIRECT($F$2),-1),1)的含义：
公式里面，最重要的是MATCH(CHAR(1),INDIRECT($F$2),-1)。
MATCH函数用于在范围或数组中查找一个值，并返回其相对位置。具体参数：

这里的查找范围是INDIRECT($F$2)。在本例中，当F2中的选项是“三班”时，对应的数据范围是名称管理器中指定的N2:N20单元格区域（因为当时选了首行作为名称，所以就不包含在这里了）。
那么，match()函数在这里的意思就是，在N2:N20单元格区域查找返回ASCII值为1的字符，即“SOH”（Start of Header）字符，并返回它的相对位置。MATCH函数的第三个参数-1，是从范围中查找大于或等于char(1)的最小值。可以理解为char(1)是个极小值。大于或等于char(1)的就是N2:N20单元格区域中有值的4项，也就是A9、A10、A12、A16，而其它的单元格都是空白，所以一定是小于char(1)的。这时返回的值就是4，也就是match()函数返回结果4。
=OFFSET(INDIRECT($F$2),0,0,MATCH(CHAR(1),INDIRECT($F$2),-1),1)就是，在F2所代表的区域（N2:N20）中，不偏移的选择4列，也就是有值的值为A9、A10、A12、A16的这四个单元格。因为这是【数据验证】中【来源】里面的公式，所以，下拉菜单将只显示有值的结果，空白的区域就不可见了。
这里挺难理解的，我也是边看边试着去理解。

【总结】

1、首先做辅助数据。先用=OFFSET($D$1,COLUMN(A1)-1,0)&""进行列转行，得到表头。再用=INDEX($B:$B,SMALL(IF($A$1:$A$100=I$1,ROW($1:$100),4^8),ROW(A1)))&""得到表数据。
这个辅助区域的作用是定义名称管理器。
名称管理器的名字就是表头，而一级下拉菜单，也就是F2单元格的内容与名称管理器的名称一致时，二级下拉菜单的序列来源使用公式INDIRECT($F$2)，也就是对名称使用了函数indirect()，这个函数指向名称管理器的内容，也就是辅助区域内的数据值。
2、在辅助区域，用名称管理器定义名称。这个定义的名称，与以及下拉菜单的内容一致。
3、利用【数据验证】制作二级下拉菜单，并在【来源】中写入公式=OFFSET(INDIRECT($F$2),0,0,MATCH(CHAR(1),INDIRECT($F$2),-1),1)用以消除二级下拉菜单中的空白选项。
4、每一步的公式都略有点复杂，不太好理解。会用就好了。
模板我上传了，在文章的最上面。。不过不知道网站是不是要付费才能下载，真的坑。。。
以上。