「技巧分享」 表格设置多级联动下拉菜单(可随意维护多级联动)

懒得批爆

创作者俱乐部成员

表格中设置下拉菜单,基本上大家都知道,简单的说明下,以防有同学不明白。

一、一级下拉列表

1、简单的介绍。在数据→有效性中设置

数据有效性面板中,有效性条件选择序列,接着在来源框中选择序列的编号

2、在数据→下拉列表中设置

插入下拉列表中手动添加下拉选项或从单元格选择下拉选项,此操作和第一种方法相同

以上的方法只能设置1个一级或多个一级下拉菜单,若想设置二级或多级下拉列表(见下图),怎么实现?


二、多级联动下拉列表

在制作多级联动下拉列表前,需要先准备一些资料,包括

一级下拉列表内容

二级下拉列表内容

三级下拉列表内容

此处以国内省、市、区县为例准备一级、二级、三级下拉列表

接着将此数据转换为两张工作表,转换方式详见本人提问及俊哥的回答

问题一 【文字组合和拆分-动态数组】将两列文字组合为符号连接的文字 | WPS官方社区--WPS爱好者家园
https://bbs.wps.cn/topic/17239
问题二 【文字组合和拆分-动态数组】 将两列数据转换为表格 | WPS官方社区--WPS爱好者家园
https://bbs.wps.cn/topic/17240

新建工作表设置一级下拉列表,这里一般来说有两种创建方式,一种是静态的一种是动态的。

静态的方式比较简单,也比较常用,不过不方便设置多级联动的下拉菜单。

  1. 静态下拉列表的制作,在省市列表中,按Ctrl+G(windows)或control+G(Mac)
  1. 在省市列表中,按Ctrl+G(windows)或control+G(Mac),选择定位→常量→确定

  1. 名称管理器创建,公式→指定(WPS)或公式→根据所选内容创建(Excel),指定内容选择首行

  1. 创建一级下拉列表,上文中已有简单介绍。

  1. 创建二级下拉列表,选中需要创建二级菜单的位置,设置数据→有效性,来源中填写=INDIRECT(一级下拉别表的单元格),这里填写的=INDIRECT(B2),如果一级下拉列表中无数据,会提示源目前包含错误,忽略提示直接确定。

此方法比较简单,也是常用的设置方法,因为日常工作中很少用到三级下拉列表,简单的方法也不方便后期维护资料,譬如需要更新资料,下拉列表中将无法更新,而需要再次设置

  1. 动态下拉列表
  1. 在省市列表中,按Ctrl+G(windows)或control+G(Mac),选择定位→常量→确定

  1. 名称管理器创建,公式→指定(WPS)或公式→根据所选内容创建(Excel),指定内容选择首行

这两步参见静态下拉列表,操作方法一致

  1. 创建一级下拉列表,依然在数据→数据有效性中设置,不过这次不是选定单元格,而是使用函数offsetCOUNTA,公式这里贴出来,参考使用:

=OFFSET(省市列表!$A$1,0,0,1,COUNTA(省市列表!$1:$1))
  1. 创建二级下拉列表,依然在数据→数据有效性中设置,使用函数offsetCOUNTAMATCH,公式这里贴出来,参考使用:

=OFFSET(省市列表!$A$1,1,MATCH(B2,省市列表!$1:$1,0)-1,COUNTA(OFFSET(省市列表!$A:$A,0,MATCH(B2,省市列表!$1:$1,0)-1))-1)
  1. 创建三级下拉列表,仍然在数据→数据有效性中设置,仍然函数offsetCOUNTAMATCH,公式这里贴出来,参考使用:

=OFFSET(区县列表!$A$1,1,MATCH(C2,区县列表!$1:$1,0)-1,COUNTA(OFFSET(区县列表!$A:$A,0,MATCH(C2,区县列表!$1:$1,0)-1))-1)

动态多级联动下拉列表可在后期维护下拉列表内容,下拉列表可动态自动更新。

以上案例练手:https://kdocs.cn/l/ckMpJQypkGEG

扩展思考,如何设置四级、五级等多级联动下拉列表

四川省
浏览 29637
22
25
分享
25 +1
16
22 +1
全部评论 16
 
厉害,正需要,学习了
· 湖北省
回复
 
请问数据源和下拉的表格只能放在同一张工作表里面操作吗?我分了两个工作表输入公式后会提示:“在数据有效性条件中不能使用对其他工作簿的引用”,我试了跨工作簿链接,下了access的插件,还是不行。我想做一个可以下拉的模板,但是这个模板需要移到汇总表上,不方便带着数据源跑
· 江苏省
回复
懒得批爆

创作者俱乐部成员

可以放在用一个工作簿不同的工作表中,这里为你普及一下 工作簿=xlsx文件 工作表=sheet 如果因为安全问题,可以将工作表隐藏后,通过审阅设置权限
· 四川省
回复
 
为什么我照着做提醒源目标错误啊。
· 上海
回复
懒得批爆

创作者俱乐部成员

是这里有错误吗,忽略就行了,做完后就好了
· 四川省
回复
 
二级菜单做好的只有下拉,但是部显示任何数据
· 新疆
回复
看下名称管理器中的名称,和一级菜单的名字是否一致, 常见的是因为“-”(短横线)这种连接符,名称管理器定义的名称只能使用“_”(下划线),在定义的时候软件会自动将“-”转为“_” ,检查下看。
· 浙江省
回复
 
· 四川省
回复
 
学习了,要多练习才行。
· 河北省
回复
 
优秀
· 河南省
回复
 
已收藏备学习
· 山东省
回复
 
志尧ZhiYao

创作者俱乐部成员

滴 学习卡
· 江西省
回复
 
很好,已收藏
· 广东省
回复