wps表格制作二级下拉菜单(WPS表格下拉菜单的多种做法)

wps表格制作二级下拉菜单(WPS表格下拉菜单的多种做法)(1)

上一节我们学习了基础下拉列表的制作,我们想再加一下码,让我们的下拉列表更加高级,更加大气,更加实用,这就是多级联动的下拉列表。我们在日常生活中也经常能够用到,比如,中国的行政区划,分为多级,第一级是省自治区、直辖市,第二级是地区,第三级是县(当然还可以往下分,我们姑且分到三级)。当我们想在表格中选择一个**县时,我们不会在全国上千个县里一个个的找,那样得累死,也找不到,我们会先选择该县所在省,在相应的省里选择该县所在的地区,然后再选择**县。这就是多级下拉列表,而且还要求它有联动功能,当我选择河北省时,在下一级列表中应该显示河北省的地区,而不应该显示山东或其他省份的地区。


A. 用offset制作联动下拉列表

下面我们以一个实例来讲解,数据是集团公司下属两个分公司的不同部门人员花名册,要求制作下拉列表完成公司、部门和职工的原则。下图是我们的花名册数据,数据放在名为“数据源1”的工作表中。

wps表格制作二级下拉菜单(WPS表格下拉菜单的多种做法)(2)

数据源

(1)我们首先需要对数据进行以下改造,改造后数据如下。分出数据的等级,显然,从数据分析出,第一级为A公司、B公司,第二级为各个公司下属的管理部门,第三级是隶属于各部门的员工姓名。如下图,在“数据源1”工作表中进行数据改造。

wps表格制作二级下拉菜单(WPS表格下拉菜单的多种做法)(3)

数据改造

(2)构建一级下拉列表,选择公司

这一步最简单,选中需要设置下拉列表的区域(这里以C23单元格为例),点击 数据—有效性—选择序列—来源内输入“=数据源1!$F$2:$F$3”

(3)构建二级下拉列表,选择部门

选中需要设置下拉列表的区域(D23单元格),点击 数据—有效性—选择序列—来源内输入公式

=OFFSET(数据源1!$H$1,MATCH(C23,数据源1!$H$2:$H$7,0),1,countif(数据源1!$H:$H,C23),1)

说明:

C22为公司所在单元格;countif是为了计算出公司内部门的数量;由于A公司和B公司是连续的,通过offset计算出一段连续的偏移数据,得到部门所在的区域。

(4)构建三级下拉列表,选择员工

在 有效性—序列—来源中输入公式

=OFFSET(数据源1!K1,MATCH(C23&D23,数据源1!K2:K50,0),1,countif(数据源1!K2:K50,C23&D23),1)

通过公司和部门组合作为查找条件,来得到部门员工所在的数据区域。

wps表格制作二级下拉菜单(WPS表格下拉菜单的多种做法)(4)

数据有效性构建多级列表

综述:通过以上步骤,我们可以得到一个多级联动的下拉列表,关键是对数据区域进行改造,要保证同公司的部门连续,同部门的员工连续。

注意:设置下一级下拉列表时,上一级下拉列表必须有选择数据,不然的话可能会出错误,下一级的下拉列表依靠上一级具体选择的数据。

B. 利用工作表控件构建多级菜下拉单

下面我们用两个组合框制作二级下拉列表,数据源放在名为“数据源2”的工作表中

(1)进行数据改造

wps表格制作二级下拉菜单(WPS表格下拉菜单的多种做法)(5)

(2)制作组合框

在名为“2.b”工作表中绘制2个组合框

wps表格制作二级下拉菜单(WPS表格下拉菜单的多种做法)(6)

组合框

(3)定义2个名称

点击 公式-名称管理器-新建

wps表格制作二级下拉菜单(WPS表格下拉菜单的多种做法)(7)

分别新建2个名称

部门:=数据源2!$D$2:$D$4

员工:

=OFFSET(数据源2!$E$1,1,'2.b'!$C$4,COUNTA(OFFSET(数据源2!$E$1,1,'2.b'!$C$4,100,1)),1)

wps表格制作二级下拉菜单(WPS表格下拉菜单的多种做法)(8)

定义名称

(4)设置组合框格式

第一个组合框为部门选择组合框,数据源区域:部门(刚才定义的部门列表),单元格链接区域:$C$4(显示选择条目的索引号)

wps表格制作二级下拉菜单(WPS表格下拉菜单的多种做法)(9)

设置组合框格式

第二个组合框为员工选择组合框,数据源区域:员工(根据第一个组合框选择不同的部门,显示不同员工列表)

wps表格制作二级下拉菜单(WPS表格下拉菜单的多种做法)(10)

C. 制作下拉联想式列表

本例的数据源放在名为“数据源3”的工作表中,如图为各省地级市列表。

wps表格制作二级下拉菜单(WPS表格下拉菜单的多种做法)(11)

数据

选中想要设置联想式列表的单元格,点击 数据—有效性—选择 序列,在来源 框中输入如下公式:

=OFFSET(数据源3!$A$1,MATCH($C5&"*",数据源3!$A:$A,0),0,COUNTIF(数据源3!$A:$A,C5&"*"),1)

然后,点击 出错警告,将“输入无效数据时显示出错警告”前面√点掉。

wps表格制作二级下拉菜单(WPS表格下拉菜单的多种做法)(12)

wps表格制作二级下拉菜单(WPS表格下拉菜单的多种做法)(13)

这种联想式下拉列表要求相近的数据必须连续排列,比如说河北省的各地级市要连续排列,不能中间隔着山东省的地级市,那样的话数据下拉列表就会显示不全。还有一种联想式下拉列表之前的文章有过介绍,(“工作表中创建带有联想功能的下拉菜单,纯公式实现”)有感兴趣的请移步到去研究,

回顾:

用了两篇文章的篇幅把主要的下拉列表的做法给大家介绍一下,不能涵盖全部,但是,应付一般的日常应用应该是没有什么问题的。对于多级列表,原理大概都是以上讲的那些,掌握了这些,无论用数据有效性还是工作表控件,都能制作出二级、三级甚至更多级的下拉列表。讲了这么多,感觉用图片和文字做讲解,还真是很累,有些地方还是说不明白,希望大家多多联系,多多思考研究。

,

免责声明:本文仅代表文章作者的个人观点,与本站无关。其原创性、真实性以及文中陈述文字和内容未经本站证实,对本文以及其中全部或者部分内容文字的真实性、完整性和原创性本站不作任何保证或承诺,请读者仅作参考,并自行核实相关内容。文章投诉邮箱:anhduc.ph@yahoo.com

    分享
    投诉
    首页