.net做网站的方式,网络优化的工作流程,搜索引擎优化的内部优化,南昌优化排名推广文章目录 【需求】在一级菜单选定后#xff0c;二级菜单联动显示一级菜单下的可选项【步骤】step1 制作辅助列1.列转行2.在辅助列中匹配班级成员 step2 名称管理器step3 制作二级下拉菜单step4 消除二级菜单中的空白 【总结】 之前做完了
【excel】设置可变下拉菜单#xff… 文章目录 【需求】在一级菜单选定后二级菜单联动显示一级菜单下的可选项【步骤】step1 制作辅助列1.列转行2.在辅助列中匹配班级成员 step2 名称管理器step3 制作二级下拉菜单step4 消除二级菜单中的空白 【总结】 之前做完了
【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-10OFFSET($D$1,COLUMN(**A1**)-1,0)的结果是D1单元格中的第一项也就是”一班“ 公式拖动到J1的时候偏移量是2-11OFFSET($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$100I$1,ROW($1:$100),4^8),ROW(A1)))注意绝对引用的位置 解释一下公式从内往外展开 IF($A$1:$A$100I$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$100I$1,ROW($A$1:$A$100),4^8) SMALL(IF($A$1:$A$100I$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函数返回的数组值为{655362365536……}。 因为INDEX($B:$B,SMALL(IF($A$1:$A$100I$1,ROW($1:$100),4^8),ROW(A1)))是要下拉的所以row()函数是变动的会变成row(A1)、row(A2)、row(A3)……row(A100)。 small()函数是返回数组中第k个最小值那么就是依次返回第一、第二、第三……个最小值那么就是返回与I1单元格的值相等的A列值的行号也就是数组中的23…… INDEX($B:$B,SMALL(IF($A$1:$A$100I$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$100I$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$100I$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、每一步的公式都略有点复杂不太好理解。会用就好了。 模板我上传了在文章的最上面。。不过不知道网站是不是要付费才能下载真的坑。。。 以上。
