【excel】设置二级可变联动菜单 |
您所在的位置:网站首页 › 一级菜单对应多个二级菜单怎么设置的 › 【excel】设置二级可变联动菜单 |
文章目录
【需求】在一级菜单选定后,二级菜单联动显示一级菜单下的可选项【步骤】step1 制作辅助列1.列转行2.在辅助列中匹配班级成员
step2 名称管理器step3 制作二级下拉菜单step4 消除二级菜单中的空白
【总结】
之前做完了
【excel】设置可变下拉菜单(一级联动下拉菜单),开始做二级联动菜单。
【需求】在一级菜单选定后,二级菜单联动显示一级菜单下的可选项
示例:A、B列为原始明细数据。 K2单元格为一级菜单,在K2单元格内容选定后,要求在L2单元格的下拉菜单中,显示K2对应的内容。 如,K2单元格为”一班“时,L2下拉菜单中,显示”一班“成员的列表: 在【excel】设置可变下拉菜单(一级联动下拉菜单)中,我们曾做过一个辅助列(辅助单元格),在此处具体步骤略。 我们从这篇文章的内容出发向下走。所以,当前我们的表格已经是这样的: 辅助列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项,没问题吧?)。 在I2单元格写入公式=INDEX($B:$B,SMALL(IF($A$1:$A$100=I$1,ROW($1:$100),4^8),ROW(A1)))&"",注意绝对引用的位置: 其实公式=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)。 选中有公式的I到S列,向下选中100行(行数根据实际需要)。为了方便演示,我选择了20行。 选中这个区域以后,【公式】-【根据所选内容创建定义的名称】: 选中二级下拉菜单的单元格G2,然后【数据】-【数据验证】-【数据验证(V)】进入数据验证对话框: 选中二级菜单项,也就是本例中的G2,再次进入【数据】-【数据验证】-【数据验证(V)】,将【设置】选项卡下【来源】中的公式由刚才的=indirect($F$2)替换为:=OFFSET(INDIRECT($F$2),0,0,MATCH(CHAR(1),INDIRECT($F$2),-1),1): 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、每一步的公式都略有点复杂,不太好理解。会用就好了。 模板我上传了,在文章的最上面。 以上。 |
今日新闻 |
推荐新闻 |
CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |