offset函数制作二级下拉菜单,二级下拉菜单自动联动 offset
Excel怎么制作二级,三级甚至无限级下拉菜单
以WPS 2019版本为例:
关于excel表格怎样设置二级,三级甚至无限级下拉项,在WPS「表格(Excel)」中操作方法如下:
1、首先,我们在表格将数据分别录入到Sheet2和Sheet3中,Sheet2中是一级和二级,Sheet3中是二级和三级的数据。和之前制作二级下拉菜单的方式差不多,我们先进入Sheet2中,选择所有数据,点击「公式-指定-只保留「首行」前面的勾,其他的全部取消掉。同样的方法,我们再次进入Sheet3中进行操作;
2、设置一级下拉菜单:进入Sheet1,选中A2单元格,进入「数据-有效性-有效性-选择序列」,在「来源」中选择Sheet2中的「A1:C1」单元格(就是一级下拉菜单的内容);
(注意:设置完成后续先选中一个选项,否则设置二级时会提示错误)
3、设置二级下拉菜单。光标定位到B2单元格,然后进入「数据-有效性-有效性-选择序列」「来源」中输入「=INDIRECT(A2)」确定;
4、设置三级下拉菜单。选中C2单元格,同样的操作,只是在「来源」中输入的是「=INDIRECT(B2)」。最后,我们选中A2:C2单元格,向下填充一下。此时,我们的多级下拉菜单就已经全部完成。
Excel二级联动下拉菜单问题,帮忙解读下面代码片段: 部门:=OFFSET(部门!$A$1,,,,COUNTA(部门!$1:$1)) 单
这不是代码,这是函数
offset是一个偏移引用的函数
部门:=OFFSET(部门!$A$1,,,,COUNTA(部门!$1:$1))
先通过counta算出部门这个表第一行里有多少个部门。
再以部门这个表A1单位格为基点,返回counta算出的部门个数的单位格,也就是所有的部门
单位:=OFFSET(部门!$A$1,1,MATCH(表!$A41,部门!$1:$1,)-1,COUNTA(OFFSET(部门!$A:$A,,MATCH(表!$A41,部门!$1:$1,)-1))-1,)
公式上",1,",中1表示部门a1单位格偏移一行。
MATCH(表!$A41,部门!$1:$1,)-1
找出表!中A41这个单位在部门表第一行的位置
COUNTA(OFFSET(部门!$A:$A,,MATCH(表!$A41,部门!$1:$1,)-1))-1
通过offset返回表!A41这个单位所在部门表的那一列,再以counta计算这一列单位的个数。
整个公式就是返回表!a41所在部门表的那一
列数据
。
Excel,二级下拉菜单的问题
首先看一下原始数据,原始信息在一张工作表,第一行是省市名称,下面的若干行为对应省市下面的地名和区名。需要在另外一张工作表中a列和b列建立联动的二级下拉菜单。
excel二级联动下拉菜单怎么做
excel二级联动下拉菜单怎么做
2
首先,选中原始表的所有数据(包括多余的空白单元格),按f5或者ctrl+g调出定位对话框。选择左下角的【定位条件】。
excel二级联动下拉菜单怎么做
3
如下图,选则【常量】,并点击【确定】按钮。这样,所有的非空单元格被选中。
excel二级联动下拉菜单怎么做
excel二级联动下拉菜单怎么做
选择功能区的【数据】-【有效性】-【根据所选内容创建】。
excel二级联动下拉菜单怎么做
由于标题在第一行,因此选择【首行】为名称,然后点击【确定】按钮。
excel二级联动下拉菜单怎么做
操作完毕后,在名称管理器中就可以看到定义的名称了。
excel二级联动下拉菜单怎么做
选中第一行的省市名称(也定位到非空白单元格),在名称框中输入“省市”两个字,然后按回车,这样就定义了一个“省市”的名称。
excel二级联动下拉菜单怎么做
选中操作界面的a2单元格,选择【数据】-【数据有效性】。
excel二级联动下拉菜单怎么做
如下图,选择【序列】,【来源处】输入:=省市,然后点击【确定】按钮。
excel二级联动下拉菜单怎么做
这样,就在a2单元格生成了省市信息的下拉菜单。
excel二级联动下拉菜单怎么做
同样的方法,选中b2单元格,设置数据有效性,输入公式:=indirect($a$2)。
excel二级联动下拉菜单怎么做
设置完毕后,a2单元格选择“河北”时b2的下拉菜单返回“河北”的信息;a2单元格选择“北京”时b2的下拉菜单返回“北京”的信息。
excel二级联动下拉菜单怎么做
excel二级联动下拉菜单怎么做
注意:
上述二级下拉菜单设置的公式采取了行列都绝对引用,如果要使二级下拉菜单对整列均可用,将公式更改为:=indirect($a2)即可。