【解密附下载】使用OFFICE365新函数实现多级联动下拉查询并返回多值结果

在前阵子笔者发布了一个小视频，演示在手机端实现多级联动下拉框的选择功能，此功能应用场景广阔，也是新时代OFFICE365新数组函数的一个非常典型的研究案例。 在此也公布所有秘密，让大家一起见识一下笔者的一个非常精彩脑洞大开的作品，并附上源文件供各爱好者拆解学习。

现实业务需求场景及候选方案

本文并非空穴来风，无中生有的普通堆砌的教学案例，乃是出自一个非常刚需的场景，如何能够让外勤人员轻松地查询到自己想要的信息。

一般来说，外勤人员如销售人员等，他们不太可能在户外轻松地使用电脑来操作提取一些信息，对传统的表哥表姐们来说，没有移动端的开发能力，想输出这样的成果给到外勤人员支持，非常困难。

就算一名传统的OFFICE开发人员，也是无从发力，移动端没有VBA的运行能力，所有的代码二次开发方案都要被否决。

剩下的，改变某个单元格内容，其他单元格可以同步改变，这样的效果，只能从函数上找方案了。

当然除了函数，还有切片器方案，但切片器能否轻松排版在手机端上使用也是一个问题，同时最大的问题是切片器没办法做到模糊查找效果，只能精确查找。 同样地还有是数据透视表方案，在筛选区域上改变单元格条件，最终透视表可以响应不同内容，但同样地，透视表在手机端不一定有很好的交互效果。

此处正式引出本篇核心知识，OFFICE365新的动态数组函数，其突破性地实现函数结果可返回多值，并且原生支持，无需自定义函数等二次开发。

Excel催化剂开发了大量的动态数组函数，可满足在非OFFICE365环境上使用，最低版本支持甚至是Excel2003，也是非常值得尝试使用的，但和本篇要求在移动端使用还是不符。 Excel催化剂自定义函数介绍链接：第4波-一大波自定义函数高级应用，重新定义Excel函数的学习和使用方法

成果展示

先给大家送上最终的效果，后面再一步步拆解带大家学习。

表格拆解

如此强大的交互效果，而且是零代码实现，这个相信不少Excel高级用户都十分好奇如何实现。

秉承互联网分享精神，此处全部细节完全公布于世，并给予讲解。在OFFICE365动态数组函数时代，许多旧时代的各种函数技巧无用武之地。

相信能够把本篇的方案理解透后，在函数的世界，已经可以晋升到黑带顶级水平。

一、数据源构成

好的技术方案，除了实现功能高级外，还需要有易用易维护的特点，撇开本篇对移动端的要求，电脑端可用的多级下拉方案，网络上不缺各种多级下拉联动的方案，但整个制作成本高昂，不具有推广价值。

Excel催化剂插件提供非常轻松易行的电脑端可用的多级联动下拉的解决方案，纯界面操作，零门槛配置，

本篇所使用的数据源，非常干净、规范，一个纯一维表结构的数据源，所有的维护，仅需基于此表格进行维护即可。

二、原理讲解

本篇的技术要点，非常有推广价值，也是让自己整个解决方案更清晰可维护的一些非常核心的技术，例如全程大量使用智能表格结构化函数语义层和定义名称功能处理中间过程清晰化多重运算。

1. 多级联动下拉技术实现

本篇中的多级联动下拉和模糊查找功能，皆用了OFFICE365的动态数组函数功能。

其中多级下拉中，使用【数据验证】的序列验证功能，将省、市、区县的查询值框定在指定范围内。

以下列出省、市、区县的【数据验证】的引用区域，并其公式实现。具体可下载文件来详细观摩。整个计算过程中间计算环节由定义名称语义化，非常清晰可读。

省：=UNIQUE(原始表[省份名称])
市：=UNIQUE(INDEX(FILTER(原始表,省级筛选),,MATCH(K1,原始表[#标题],0)))
区县：=UNIQUE(INDEX(FILTER(原始表,省级筛选*市级筛选),,MATCH(M1,原始表[#标题],0)))

上述函数公式中，就用到了FILTER和UNIQUE函数，筛选其父级及以上的当前筛选值，传入Filter条件，返回的列表结果，使用INDEX函数返回对应列的数据（MATCH函数就是个神助攻，返回INDEX函数返回的列序号，让动态进行到底，防止数据源表的列顺序有变更），再进行去重处理，最终结果以动态数组多值自动扩展的方式返回到多个单元格区域中。

除了OFFICE365新函数外，以前旧的函数也有许多满足返回多值结果的函数，如上面多级下拉还用到了INDEX函数返回某一列数组。

具体可支持的函数可参照此链接清单：

https://support.microsoft.com/zh-cn/office/%E8%BF%94%E5%9B%9E%E5%8C%BA%E5%9F%9F%E6%88%96%E6%95%B0%E7%BB%84%E7%9A%84-excel-%E5%87%BD%E6%95%B0-7d1970e2-cbaa-4279-b59c-b9dd3900fc69

经过自定义名称处理后，非常清晰可看到FILTER的筛选条件，例如区县的是【省级筛选*市级筛选】

而对应的【数据验证】的引用序列区域也一并定义为名称，可以更加语义化。

省级=OFFSET(数据源!$I$1,1,0,COUNTA(数据源!$I:$I)-1,1)
市级=OFFSET(数据源!$K$1,1,0,COUNTA(数据源!$K:$K)-1,1)
区县=OFFSET(数据源!$M$1,1,0,COUNTA(数据源!$M:$M)-1,1)

上述熟悉的OFFSET动态引用区域的味道，如今其实已经被动态数据能力给替代，无需这么复杂实现。但怀念过去学习这些套路的日子，此处仍然使用此套路怀旧一下。 新方式，简单到飞起。只需在动态数组函数返回的多值区域中，任一单元格后面加个#即可。如 省级=I2#

2. 自定义名称封装中间过程

上述【数据验证】函数中，用到的中间筛选条件，将其定义为名称存储，具体名称定义如下：

省级筛选：=IF(原始表[省份名称]=省级查询值,TRUE,IF(原始表[辅助列]*IF(省级查询值="",1,0),TRUE,FALSE))
市级筛选：=IF(原始表[城市名称]=市级查询值,TRUE,IF(原始表[辅助列]*IF(市级查询值="",1,0),TRUE,FALSE))
=IF(原始表[区县名称]=区县级查询值,TRUE,IF(原始表[辅助列]*IF(区县级查询值="",1,0),TRUE,FALSE))

而查询值，即查询面板里的定义了【数据验证】的单元格的值，根据其父值动态生成子值的【数据验证】引用序列内容。

省级查询值：=精确查询!$A$2
市级查询值：=精确查询!$A$5
区县级查询值：=精确查询!$A$8

辅助列的作用，用于处理当其查询内容为空时，可以返回所有内容，这个也是本篇一个精妙的处理，在模糊查找时也使用到此特性。

3. 查询结果返回值实现

一般多级联动方案中，仅用于做数据录入使用，本篇突破性地将其更深推进，可作为查询内容返回处理。将单元格交互后的值，作为返回内容的查询条件进行约束，动态返回不同内容。

返回区域公式：=IFERROR(INDEX(FILTER(原始表,省级筛选*市级筛选*区县筛选,""),,MATCH(A10,原始表[#标题],0)),"空记录")

此处和上述【数据验证】类似，只是筛选条件更多一级，以【省级筛选*市级筛选*区县筛选】作为筛选条件。

最终返回值内容无需去重处理，同样是返回某列的内容，如果返回多列，可构造多个函数返回不同列，当然返回全表的列字段更简单，不用套INDEX函数即可。

如果没有满足条件的记录返回错误，用IFERROR处理下，更语义化呈现结果。

4. 模糊查找实现

除了多级联动筛选，还可以使用模糊查找，这个可以将查询场景应用到最大化，可满足一般系统里的绝大部分的查询场景。

同样使用定义名称将其语义化。

模糊查找条件1=模糊查询!$A$2
模糊查找条件2=模糊查询!$A$5
模糊查找条件3=模糊查询!$A$8
模糊筛选1=IF(IF(ISERROR(FIND(模糊查找条件1,原始表[返回结果列])),FALSE,FIND(模糊查找条件1,原始表[返回结果列])>0),TRUE,IF(原始表[辅助列]*IF(模糊查找条件1="",1,0),TRUE,FALSE))
                
模糊筛选2=IF(IF(ISERROR(FIND(模糊查找条件2,原始表[返回结果列])),FALSE,FIND(模糊查找条件2,原始表[返回结果列])>0),TRUE,IF(原始表[辅助列]*IF(模糊查找条件2="",1,0),TRUE,FALSE))
                                
模糊筛选3=IF(IF(ISERROR(FIND(模糊查找条件3,原始表[返回结果列])),FALSE,FIND(模糊查找条件3,原始表[返回结果列])>0),TRUE,IF(原始表[辅助列]*IF(模糊查找条件3="",1,0),TRUE,FALSE))

上述模糊查找实现，使用了FIND函数，如果要不区分大小写和使用通配符，可以换成SEARCH函数。这个使用，也是考验大家对数组公式的理解，普通函数用于数组公式的场景，自己慢慢消化。

同样地模糊查找返回结果和精确查找类似，如下：

=IFERROR(INDEX(FILTER(原始表,模糊筛选1*模糊筛选2*模糊筛选3,""),,MATCH(A10,原始表[#标题],0)),"空记录")

结语

用一篇高级的实际案例场景，带给大家一场Excel函数盛宴，大胆推测，目前市面上能够做出这样的解决方案，聊聊无几。

能用代码二次开发的，就用插件的形式给予源源不断地助力，就如Excel催化剂插件的使命，成为Excel用户们的催化剂点燃Excel的爆点。

在极端环境下如手机移动端，同样可以提出出色的解决方案，例如本篇的函数实现法。

在国产化风波的今天，笔者也同样布局WPSJS新的插件开发技术，让广大国有企业、事业机关的表哥表妹群体一样可以享受催化剂的威力，欢迎这个群体也持续关注笔者的最新输出。

当然Mac苹果用户们也是此行列的人群，没有传统OFFICE开发的支持，但Mac用户们，在不久的未来可以在WPS上享受到插件助力的机会。