使用公式制作动态三级菜单
WPS函数专家
看到有小伙伴发贴需要做动态三级下拉列表,数据源格式结构很标准,很有代表性,结合最近新出的动态数组功能,通过设置几个公式即可设置好三级下拉列表,
原贴地址:https://bbs.wps.cn/topic/20438
新建4个工作表,依次将工作名设置为"源","一二级","三级","菜单",然后将下拉列表的数据复制粘贴到"源"工作表中,如下所示。
在"一二级"工作表A1单元格输入公式,如下图所示。
=TOROW(LET(x,UNIQUE(源!A2:A9999),FILTER(x,x<>0)))
使用UNIQUE函数对源工作表A2:A9999单元格区域去重复项,在引用源工作表单元格区域时,不建议引用整列,可以引用一个相对较大的单元格区域,根据实际数据量情况引用如999、9999或20000行数据,使用FILTER函数筛选,筛选不等于0的数据(筛选非空),也可以先筛选后去重复项,使用TOROW将数据转换为一行。
此公式的目的是对一级菜单去重复项。
在A2单元格输入公式后,根据一级菜单数量向右填充公式,可以多向右填写一些,给为以后添加的数据预留公式
=IF(A1="","",UNIQUE(FILTER(源!$B$1:$B$9999,源!$A$1:$A$9999=A1,"")))
使用FILTER函数筛选一级菜单对应的二级菜单内容,筛选后使用UNIQUE函数去重复项,使用if筛选判断如果一级为空返回空值,可提高公式计算效率。
在"三级"工作表A1单元格输入公式,将一级二级连接为一个字段后去重复项。
=TOROW(UNIQUE(源!A1:A9999&源!B1:B9999))
将一级菜单 和二级菜单使用&运算符连接后,使用UNIQUE函数去重复项,使用TOROW函数转换为一列。
5、在"三级"工作表A2单元格输入公式,查询一二级菜单对应的三级菜单内容,输入公式根据一二级菜单的数量向右填充公式,可以多填充一些公式为添加数据预留公式。
=IF(A1="","",FILTER(源!$C$1:$C$9999,源!$A$1:$A$9999&源!$B$1:$B$9999=A1,""))
第4、5步骤是为了兼容"源"工作表多级菜单内容顺序不同,兼容性更好一些,如果"源"工作表多级菜单顺序相同,可以省略这两步骤,做好一二级菜单后,根据一二级内容使用MATCH即可定位到一二级菜单的开始行数,然后使用COUNTIFS函数可以获取到一二级菜单的数量,然后使用OFFSET函数通过第二个参数偏移行定位到一二级菜单开始行后,通过第4个参数扩展行数使用COUNTIFS返回的值即可动态获取到三级菜单的内容。
设置一级菜单,选中"菜单"工作表A2单元格,或A列,依次单击"数据"-"有效性"-"序列"-"来源"输入公式
=一二级!$A$1#
6、设置二级菜单,选中"菜单"工作表B2单元格,或B列,依次单击"数据"-"有效性"-"序列"-"来源"输入公式
=OFFSET(一二级!$A$2,,MATCH(A2,一二级!$1:$1,0)-1)#
在确定时,会提示"源目前包含错误。是否继续?",点"是"即可。这是因为一级菜单还没有选择或一级菜单内容是通过复制粘贴,导致查找不到二级内容。
7、设置三级菜单,选中"菜单"工作表C2单元格,或C列,依次单击"数据"-"有效性"-"序列"-"来源"输入公式
=OFFSET(三级!$A$2,,MATCH(A2&B2,三级!$1:$1,0)-1)#
到这里三级菜单就制作完成了,因现在WPS现在还不支持下拉列表自动去重复功能,需要使用辅助列对一二级菜单去重复后引用,如果WPS以后支持下拉列表自动去重复,对下拉菜单源内容区域排序后,无需辅助列,直接设置公式即可制作动态三级菜单。
创作者俱乐部成员
创作者俱乐部成员
WPS函数专家
社区管理员