使用公式制作动态三级菜单

看到有小伙伴发贴需要做动态三级下拉列表，数据源格式结构很标准，很有代表性，结合最近新出的动态数组功能，通过设置几个公式即可设置好三级下拉列表，

原贴地址：https://bbs.wps.cn/topic/20438

1. 新建4个工作表，依次将工作名设置为"源","一二级","三级","菜单"，然后将下拉列表的数据复制粘贴到"源"工作表中，如下所示。

2. 在"一二级"工作表A1单元格输入公式，如下图所示。

=TOROW(LET(x,UNIQUE(源!A2:A9999),FILTER(x,x<>0)))

使用UNIQUE函数对源工作表A2:A9999单元格区域去重复项，在引用源工作表单元格区域时，不建议引用整列，可以引用一个相对较大的单元格区域，根据实际数据量情况引用如999、9999或20000行数据，使用FILTER函数筛选，筛选不等于0的数据（筛选非空），也可以先筛选后去重复项，使用TOROW将数据转换为一行。

此公式的目的是对一级菜单去重复项。

3. 在A2单元格输入公式后，根据一级菜单数量向右填充公式，可以多向右填写一些，给为以后添加的数据预留公式

=IF(A1="","",UNIQUE(FILTER(源!$B$1:$B$9999,源!$A$1:$A$9999=A1,"")))

使用FILTER函数筛选一级菜单对应的二级菜单内容，筛选后使用UNIQUE函数去重复项，使用if筛选判断如果一级为空返回空值，可提高公式计算效率。

4. 在"三级"工作表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返回的值即可动态获取到三级菜单的内容。

5. 设置一级菜单，选中"菜单"工作表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以后支持下拉列表自动去重复，对下拉菜单源内容区域排序后，无需辅助列，直接设置公式即可制作动态三级菜单。