第一天上班,老板让我用Excel创建二级联动菜单,我轻松过关 |
您所在的位置:网站首页 › html二级联动下拉列表动态 › 第一天上班,老板让我用Excel创建二级联动菜单,我轻松过关 |
双十一别再剁手了,来这里学习吧,点击下面卡片免费体验Excel课程! 二级联动菜单指的是,当我们选择一级菜单之后,对应的二级菜单会随着一级菜单的不同而选项也不同。二级菜单的创建方法有很多种,今天我给大家讲解最长用的创建方法:通过Indirect函数法和OFFSET函数法。 1、INDIRECT函数法案例:如图2-29所示,我们要创建省份是一级下拉菜单,对应的市名是二级下拉菜单的联动菜单。 ①为省市创建“名称” 名称是一个有意义的简略表示法,可以在Excel中方便的代替单元格引用、常量、公式或表。比如将C20:C30区域定义为名称:MySales,那么公式=SUM(MySales)可以替代=SUM(C20:C30),可见名称比单元格区域更具有实际意义。 Step1:按住Ctrl键,分别用鼠标选取包含省、市名的三列数据,主要不要选择空单元格。(也可以通过Ctrl+G调出定位条件,设置定位条件为在常量来选取数据区域) 提示:不能直接框选B1:D6的数据区域,因为含有空单元格,这样创建的下拉菜单会有空白选项;也可以使用【Ctrl】+【G】定位方法快捷选中非空单元格。 Step2:在菜单栏中切换到【公式】选项卡→选择【定义的名称】分区→点击【根据所选内容创建】,在弹出的菜单中,勾选【首行】选项,如图2-31所示,这样就创建了三个省份的“名称”,“名称”的值为对应的城市名。 ②创建联动菜单 Step1:创建一级菜单,为区域中的省份一列创建一级菜单,如图2-32所示,创建方法通过“引用区域”的方式,直接将图2-30中的B1:D1区域作为数据来源,这里不再赘述。 Step2:为上图中的“市”创建二级菜单 选中【市】列需要设置的单元格区域→在验证条件中选择【序列】→【来源】中输入公式=INDIRECT($C3)→点击【确定】,此时会弹出错误提示,点击【是】继续下一步即可,如图2-34所示。 提示:这里出错的原因是此时C3单元格中为空,还未选择省份的数据,找不到数据源,不影响二级菜单的设置。 完成之后,就实现了二级联动菜单,如图2-34所示。 实现二级联动菜单的核心是:定义名称和INDIRECT函数,理解这两个核心是解题的关键。 原理①:根据“名称”的作用,当我们把图2-30中C2:C5区域定义为名称“江苏省”时,那么在函数引用中,“江苏省”能够代替C2:C5区域; 原理②:INDIRECT函数为间接引用,他可将文本转化为引用。如图2-35所示,A2单元格中放的是文本“C4”,直接引用的话返回值就是“C4”;而是使用INDIRECT函数间接引用,他可将“C4”转化为对单元格C4的引用,因此返回的值是单元格C4中放置的值“SeniorExcel”。 案例2中,将原理①和原理②结合起来,图2-33中=INDIRECT($C3)的意思是,首先C3单元格中的值是“江苏省”,而INDIRECT可以将文本换成引用,而“江苏省”已经定义为名称,代表的是图2-30中C2:C5单元格区域,所以二级下拉菜单中出现的南京市、苏州市等。 2、OFFSET函数法OFFSET函数是我最喜欢的Excel函数之一,她在构建动态区域方面无可匹敌。首先我们来回顾OFFSET函数的语法: OFFSET(reference, rows, cols, [height], [width]) reference:作为参照的单元格引用 Rows:向上或向下偏移的行数 Cols:向左或向右偏移的列数 Height:高度,需要返回的引用的行高。 Height 必须为正数。 Width:需要返回的引用的列宽。 Width 必须为正数。 OFFSET函数语法 接下来我们来通过一个OFFSET函数案例加深对函数语法知识的掌握。 当然,仅仅是引用区域,是没有太大用处的,OFFSET函数的神奇之处在于,通过引用构造动态区域,从而完成复杂的数据汇总、高级动态图表、多级下拉菜单等! ▌案例 如图所示,根据AB两列的城市列表,在黄色区域设置二级下拉菜单,即在黄色区域选择不同的省份,城市下拉菜单中出现的是对应省份的城市。 Step1:设置一级下拉菜单 Step2:创建二级菜单的名称 在【新建名称】对话框中,【名称】处填写“二级菜单”,引用位置填写如下公式: =OFFSET($A$1,MATCH($D$3,$A$2:$A$12,0),1,COUNTIF($A$2:$A$12, D$3),1) Step3:设置二级下拉菜单 二级下拉菜单中的公式怎么解读呢? ①先说MATCH($D$3,$A$2:$A$12,0): 查找省份在列表中的出现的第一个位置,返回值作为OFFSET函数的第二个参数,表示向下偏移的行数 ②再说COUNTIF($A$2:$A$12, D$3): 这个简单,是用来统计省份在列表中的个数,比如“江苏”,通过MACTH函数查找到第一个位置,然后通过COUNTIF函数计算出现的总个数5. 这个结果作为OFFSET函数的第四个参数表示返回区域的行数。 ③最后合成公式(以江苏为例): =OFFSET($A$1,MATCH($D$3,$A$2:$A$12,0),1,COUNTIF($A$2:$A$12, D$3),1) 以A1为参照,向下偏移5行(江苏在A2:A12列表区域的第五行),向右偏移1列,行高为5(江苏总共有5行),列宽为1列。这个区域正是江苏对应的城市清单 精进Excel |
今日新闻 |
推荐新闻 |
CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |