巧用数据验证功能、Choose嵌套函数制作二级下拉菜单

在实际的工作中，应用到下拉菜单的地方非常多，一把情况下都是为了规范数据的输入，放置数据冗余，且都是一级的。如果要制作二级下拉菜单，且必须是联动的，该如何去实现呢？

---

目的：根据下图，当在一级菜单中选择相应的课程时，在二级菜单中只能选择对应的讲师。

一、必备函数：Choose+Match。

（一）Choose。

功能：根据给定的索引值，从参数中选出相应的值或操作。

语法结构：=Choose(索引值,返回值1,返回值2,返回值3,返回值4……返回值N)。

目的：返回“计算机基础”中的第2位讲师和第3行的第3位讲师。

方法：

在目标单元格中输入公式：=CHOOSE(2,C3,C4,C5,C6,C7)、=CHOOSE(3,B5,C5,D5,E5)。

解读：

从公式中可以看出，Choose函数即可以在行中提取值，也可以在列中提取值，只需将对应的值放置在相应的参数中即可。

---

（二）Match。

功能：返回符合特定值特定顺序的项在数组中的相对位置。

语法结构：=Match(定位值,值所在的范围,匹配类型)。

注意事项：

参数“匹配类型”有三个值，分别为-1、0、1；分别代表“大于”、“精准”、“小于”。

目的：返回课程中“书法”的位置和“计算机基础”讲师“徐庶”的位置。

方法：

在目标单元格中输入公式：=MATCH('书法',C2:E2,0)、=MATCH('徐庶',C3:C7,0)。

解读：

从公式中可以看出，Match函数即可以在行中定位，也可以在列中定位。

---

二、二级联动下拉菜单技巧。

思路分析：当在一级菜单中选择相应的课程，如“计算机基础”时，二级菜单中只能出现：甘夫人、孙尚香、袁绍、徐庶、赵云；而不能出现其他的值。也就是首先定位课程的相对列数，然后根据相对列数的值返回对应的值。

一级菜单：

方法：

选中“课程”列中的下单元格区域，【数据】-【数据验证】，打开【数据验证】对话框，选择【允许】中的【序列】，单击【来源】右侧的折叠按钮，选择需要显示的内容，并单击展开按钮返回【数据验证】对话框，【确定】即可。

二级菜单：

方法：

选中“讲师”列中的单元格区域，【数据】-【数据验证】，打开【数据验证】对话框，选择【允许】中的【序列】，在【来源】中输入：=CHOOSE(MATCH(B3,Match!$C$2:$E$2,0),Match!$C$3:$C$7,Match!$D$3:$D$7,Match!$E$3:$E$9)并【确定】。

解读：

二级菜单的关键在于【来源】中的公式：=CHOOSE(MATCH(B3,Match!$C$2:$E$2,0),Match!$C$3:$C$7,Match!$D$3:$D$7,Match!$E$3:$E$9)。

其本质为Choose函数和Match函数的嵌套应用，首先用Match函数定位“课程”所在的相对列数，然后根据相对列数返回对应的讲师。

---