XLOOKUP赋能动态联动菜单：轻松实现多级数据筛选与更新

优秀创作者

在工厂制定生产计划的过程中，常见的一种工作场景是运用下拉菜单系统对一级和二级数据，甚至三级数据进行层级分明的筛选操作。此类筛选功能具备数据联动性，即在限定所需数据范围的同时，省去了在单元格内重复键入对应数据的步骤，从而明显提高了工作效率。

在传统的电子表格操作技巧中，要构建包含一级、二级乃至三级联动的下拉菜单以实现数据筛选，往往需要运用一系列较为复杂的手段，诸如定义名称、运用高级函数如INDIRECT等。尽管这些方法确实能够达成目的，但其学习门槛相对较高。为此，古老师今天将介绍一个更为简便易学的技巧，以帮助大家实现同样的功能。该技巧的关键在于运用函数XLOOKUP。

基础数据

构建这类一级至四级的多级联动下拉菜单，其核心在于明确建立起各级菜单间一一对应的关联关系。即确保一级菜单选项与相应的二级菜单项、二级菜单项与三级菜单项、以及后续各级菜单间均形成逻辑严密的映射。一旦建立了这种完整的数据对应架构，即可利用函数XLOOKUP结合数据有效性（或数据验证）功能，顺利创建出所需的联动下拉菜单体系。

如附图所示，这是一份结构清晰、规范的一维数据表，其中详尽地梳理并明确了省、市、区及街道之间的隶属关系。

一级对应二级

针对上述提供的基础数据，为建立一级（省）与二级（市）间的对应关系，可借助WPS软件中的相关函数进行如下录入：

=UNIQUE(B4:B39)，对省去重

=DROP(REDUCE("",G4#,LAMBDA(X,Y,VSTACK(X,TOROW(UNIQUE(FILTER(C4:C39,B4:B39=Y)))))),1)

筛选省对应市，并通过REDUCE堆叠到一起。效果如下图所示：

二级对应三级

对于二级（市）与三级（区）间的对应关系，其函数公式构建原理与前述一级（省）对二级（市）的情形基本相同。请参照以下函数录入方式：

=UNIQUE(C4:C39)，对市去重；

=DROP(REDUCE("",G9#,LAMBDA(X,Y,VSTACK(X,TOROW(UNIQUE(FILTER(D4:D39,C4:C39=Y)))))),1)

筛选市对应区，并对区去重后转置成水平方向，效果如下图所示：

三级对应四级

如果还有四级对应五级，其原理也一样，这里我们继续录入三级对应四级的函数：

=UNIQUE(D4:D39)，对区去重

=DROP(REDUCE("",G15#,LAMBDA(X,Y,VSTACK(X,TOROW(UNIQUE(FILTER(E4:E39,D4:D39=Y)))))),1)

筛选区对应街道，并对街道去重后转置成水平方向，效果如下图所示：

创建一级菜单

一级菜单设置步骤如下：

选取一个空白单元格。

点击“数据”菜单。

在下拉列表中选择“有效性”或“数据验证”选项。

在弹出窗口中，于“允许”栏中选择“序列”。

接着，在“来源”栏中录入上一步对省去重后所得到的单元格函数引用位置，即“G4#”。至此，一级下拉菜单即创建完成。

创建二级菜单

在构建二级下拉菜单之前，我们首先在适当位置录入一个查找引用函数公式：

=XLOOKUP(L4, G4#, H4#)

其中：

L4 是指先前已录入的一级菜单所选内容，此处示例数据为“广东”。

G4# 代表一级菜单（省）的去重数据范围。

H4# 则对应一级菜单所选省份（如“广东”）下所有关联的市级信息。

此公式执行后，将根据一级菜单选定的“广东”，在指定范围内查找并返回与其对应的市级信息，即“深圳、广州”。

效果如下图所示：

录入完成后，剪切这个公式

点击“数据”菜单。

在下拉列表中选择“有效性”或“数据验证”选项。

在弹出窗口中，于“允许”栏中选择“序列”。

在“来源”栏中粘贴这个公式

点完成，这样二级下拉菜单就创建完成

效果如下图所示：

创建三级、四级

遵循上述逻辑，我们可以继续采用相似的方法创建三级和四级下拉菜单，分别录入如下函数：

三级菜单：=XLOOKUP(M4,G9#,H9#)

四级菜单：=XLOOKUP(N4,G15#,H15#)

至此，我们已成功设计出从一级至四级的完整联动下拉菜单体系。采用XLOOKUP函数进行制作，不仅使得逻辑更易于理解，而且由于其利用了WPS的动态数组特性，使得整个下拉菜单具备动态扩展能力。这意味着，当基础数据发生更新时，下拉菜单内容将自动随之同步刷新。特别强调，在构建各等级对应关系时，务必确保基础数据的引用范围涵盖足够广，以适应未来可能的数据增减。