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)

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

创建一级菜单

一级菜单设置步骤如下:

  1. 选取一个空白单元格。

  1. 点击“数据”菜单。

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

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

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

创建二级菜单

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

=XLOOKUP(L4, G4#, H4#)

其中:

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

  1. G4# 代表一级菜单(省)的去重数据范围。

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

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

效果如下图所示:

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

  1. 点击“数据”菜单。

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

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

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

  1. 点完成,这样二级下拉菜单就创建完成

效果如下图所示:

创建三级、四级

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

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

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

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

318 整合WPS新函数:REDUCE、LAMBDA与VSTACK,构建高效BOM整理解决方案

317 【案例解析】信息化系统BOM数据不规范?看PMC如何化繁为简

316 PMC实战分享:如何应对大型零部件逐日管控难题与WPS表格模板设计

315 巧用表格函数优化销售业绩分配:提升奖金计算效率与准确性

314 巧用WPS 新函数:无辅助列情况下处理合并单元格查询统计难题

313 破译订单堆叠难题:WPS SCAN函数在PMC生产计划中的应用实例

312 PMC的日常挑战:如何用WPS表格精准抓取单元格内的数字信息

311 零件产销存报表难题:不规范数据格式与跨页数据整合的困局与破局

310 WPS最新函数技巧:精准定位与智能舍入,罗马数字秒变阿拉伯数字

309 PMC精益库存管理与订单分配自动化设计WPS一站式报表模板

308 工厂信息化升级实战:巧解无规律物料编码,动态提取字母组合

307 一招GET!借助通配符解决表格数据汇总难题:SUMIF函数实例详解

306 一键生成:RANDARRAY 函数在数据分组与数学作业个性化定制中的妙用

305 实战演练:10种创新策略解锁WPS表格函数在成绩查询中的高效应用

304 步步详解:运用WPS函数整合工厂报表,实现全年度产品数据深度挖掘

303 运用WPS新函数实现工厂产销存报表的智能化整合与数据分析

302 BOM转换策略:树型BOM与父件子件BOM的优缺点分析及其相互转换方法

301 深入理解订单齐套率计算方法及应用实践案例解析

300 物料需求运算表优化实战:从“卡顿”到“流畅”的转变

299 WPS表格自动化编号:升序与累计编号的实用方法

298 输入法配合WPS表格公式,轻松实现虚拟数据批量生产:助力讲师快速培训演示

297 探究二维与一维报表的奥秘:掌握三种实用转换策略,应对庞大数据量挑战

296 从理论到实践:WPS 内测SCAN函数在销售金额稳定性评估中的神奇运用

295 新旧交织,效率升级:WPS表格新函数REDUCE、BYROW等在复杂业务场景下的灵动

294 WPS内测版新功能揭秘:“SCAN”迭代函数的运用与实践

293 借助WPS动态数组公式实现智能扩展填充与数据转换自动化

292 MRP原理课后的表格制作练习题目:技能与实战双丰收

广东省
浏览 2479
11
82
分享
82 +1
48
11 +1
全部评论 48
 
长见识
· 山东省
回复
 
打卡
· 浙江省
回复
 
厉害。
· 湖北省
回复
 
签到
· 广东省
回复
 
有点难懂
· 河南省
回复
 
点赞+收藏
· 四川省
回复
 
有点难懂
· 四川省
回复
 
· 吉林省
回复
 
用偏移函数也可以实现吧?
· 中国
回复
 
打卡
· 陕西省
回复
 
打卡
· 上海
回复
 
跟着老师学习
· 江苏省
1
回复
 
打卡
· 云南省
回复
 
打卡
· 上海
回复
 
打卡
· 广东省
回复
 
nice
· 四川省
回复
 
太难了
· 广西
回复
 
需要提交
· 福建省
回复
 
打卡
· 江苏省
回复
 
打卡
· 天津
回复