巧用数据验证功能、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函数定位“课程”所在的相对列数,然后根据相对列数返回对应的讲师。


(0)

相关推荐