在工厂制定生产计划的过程中,常见的一种工作场景是运用下拉菜单系统对一级和二级数据,甚至三级数据进行层级分明的筛选操作。此类筛选功能具备数据联动性,即在限定所需数据范围的同时,省去了在单元格内重复键入对应数据的步骤,从而明显提高了工作效率。
在传统的电子表格操作技巧中,要构建包含一级、二级乃至三级联动的下拉菜单以实现数据筛选,往往需要运用一系列较为复杂的手段,诸如定义名称、运用高级函数如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的动态数组特性,使得整个下拉菜单具备动态扩展能力。这意味着,当基础数据发生更新时,下拉菜单内容将自动随之同步刷新。特别强调,在构建各等级对应关系时,务必确保基础数据的引用范围涵盖足够广,以适应未来可能的数据增减。
我是古哥计划,专注生产计划18年,头条号作者,职场问答专家,优质职场领域创作者。关注我,每天学习PMC相关知识。