纯函数能否模拟多级下拉菜单功能？

咔咔乱坠

学习365函数有一阵了，在论坛也学到很多，总是想回馈一下大家
正好，前阵子回了一个关于行政区划代码的题，突然想到纯函数能否模拟多级下拉菜单功能
于是鼓捣了一晚上，可算憋出来了，算是匆忙之作，多有瑕疵，请大家多多指教
————————————————————————————————————————————
感谢Holiday2022提供的数据，现更新至5级菜单，省、市、县、镇、村
————————————————————————————————————————————
下载说明：附件一为4级菜单
                附件二为5级菜单，但是文件过大故分卷上传，需要将全部附件二下载后再统一解压缩
使用方法：把表格中'去掉，输入=
使用说明：见二楼附加内容
适用版本：excel365版本，WPS内测版
————————————————————————————————————————————
行政编码参考：
https://club.excelhome.net/forum.php?mod=viewthread&tid=1685575&_dsign=3a0afcda
https://github.com/adyliu/china_area

咔咔乱坠

补充说明：
1，结构1的4级菜单不推荐使用，数据结构不规范，需要将一列数据转成4列数据，故大量使用了查找函数，很卡
     结构2的4级菜单首选推荐使用，数据结构规范，样本量不多，运行速度快
     结构2的5级菜单建议看情况使用，数据结构规范，样本量较多，62万条数据，运行速度偏慢
2，查找内容全空时默认返回全部内容，但是不建议全空，全空逐行遍历十分卡顿
3，填写时可以忽略前面几个框，直接填镇级、村级，运行速度极快！
4，当关键字搜索为毫不相干的两个地点时，会将这两个地点的关联数据全部展示出来，使用者可以根据展示数据进一步完善关键词
5，返回内容为1项时，和上级平行
     返回内容为2项时，在上级下一层
     返回内容为>=3项时，上级居中显示
6，表格中省、市、县、镇、村数据按需可以换成父子结构、上下级结构等类似数据
7，附件中公式可微调，
     ROUND(r/2,) 改 --TEXT(r/2,0)，速度快零点几秒
     FILTER(a,b=MAX(b))可用let定义，速度快零点几秒
     SEARCH("*"&H2:K2&"*",a) 改 FIND(H2:L2,a)，速度快一倍，换用此处后5级菜单可以正常使用

咔咔乱坠

2024/10/15公式更新，附件未更新
更新内容：使用函数，把数据去重，居中，对数据弄上从属关系的链条，可适用于父子关系、BOM等
灵感来源：https://club.excelhome.net/thread-1670045-2-1.html
要求版本：excel365
注意事项：此自定义函数包含两个参数，必选参数ref，需为单元格引用，数据源的上侧右侧不能有东西
                                                               可选参数center，1为居中，0为不居中，默认为0

1. =LAMBDA(ref,[center],LET(D,DROP,V,VSTACK,H,HSTACK,R,REDUCE,S,SEQUENCE,I,INDEX,o,LAMBDA(r,c,OFFSET(ref,r,c)),链,IFS(o(,1)="","",(o(-1,)<>ref)*(o(1,)<>ref),"─",o(-1,)<>ref,"┌",ref<>o(1,),"└",1,"│"),中,IF(center,D(R(0,S(COLUMNS(ref)),LAMBDA(J,K,H(J,LET(序1,SCAN(0,I(ref,,K),LAMBDA(x,y,x+(y<>OFFSET(y,-1,)))),XLOOKUP(D(R(0,UNIQUE(序1),LAMBDA(x,y,V(x,LET(序2,SUM(N(序1=y)),IF(S(序2)=--TEXT(序2/2,0),y))))),1),序1,I(ref,,K),""))))),,1),IF(IFNA(V(1,DROP(ref,1)<>DROP(ref,-1)),1),ref,"")),D(R(0,S(COLUMNS(链)),LAMBDA(x,y,H(x,I(中,,y),I(链,,y)))),,1)))(结构2!A2:D27,1)

复制代码

简化掉一百个字符

1. =LAMBDA(ref,[center],LET(o,LAMBDA(r,c,OFFSET(ref,r,c)),链,IFS((o(,1)="")*(COLUMNS(ref)>1),"",(o(-1,)<>ref)*(o(1,)<>ref),"-",o(-1,)<>ref,"┌",ref<>o(1,),"└",1,"│"),DROP(REDUCE(0,SEQUENCE(COLUMNS(ref)),LAMBDA(J,K,LET(a,INDEX(ref,,K),序,SCAN(0,a,LAMBDA(x,y,x+(y<>OFFSET(y,-1,)))),HSTACK(J,IF(DROP(REDUCE(0,FREQUENCY(序,UNIQUE(序)),LAMBDA(x,y,IF(y,VSTACK(x,SEQUENCE(y)=IF(center,ROUND(y/2,),1)),x))),1),a,""),INDEX(链,,K))))),,1)))(结构2!A2:D27,1)

复制代码

晨光晞微

看到赣州很亲切，赞

咔咔乱坠

晨光晞微 发表于 2024-3-7 11:18
看到赣州很亲切，赞

哈哈哈，老表你好

Holiday2022

村级数据，请见附件。参考资料：https://github.com/adyliu/china_area（已更新到2024年）；https://github.com/modood/Administrative-divisions-of-China（更新到2023年）

Holiday2022

谢谢您对我问题的探索！

咔咔乱坠

Holiday2022 发表于 2024-3-7 17:34
村级数据，请见附件。参考资料：https://github.com/adyliu/china_area（已更新到2024年）；https://github ...

hhh，和楼主有缘，就是看了楼主的贴才有模拟多级下拉菜单的想法
村级的我晚上研究一下，应该写得出来，不过不推荐使用，数据量太大，函数吃不消

Holiday2022

咔咔乱坠 发表于 2024-3-7 19:19
hhh，和楼主有缘，就是看了楼主的贴才有模拟多级下拉菜单的想法
村级的我晚上研究一下，应该写得出来， ...

哈哈哈，意思不大啦，不搞也不要紧。之前我那个帖子，有个老师给我写的公式，内容没问题，但是运行时间超级久。我把原区域调整到10行，才能运行出结果来。

咔咔乱坠

Holiday2022 发表于 2024-3-7 19:41
哈哈哈，意思不大啦，不搞也不要紧。之前我那个帖子，有个老师给我写的公式，内容没问题，但是运行时间超 ...

感谢提供村级数据，5级菜单做好了，详见附件二，运行速度出乎意料还行，关键词多的话2秒左右出结果，看来reduce放最后面还是有成效的

Holiday2022

咔咔乱坠 发表于 2024-3-7 22:57
感谢提供村级数据，5级菜单做好了，详见附件二，运行速度出乎意料还行，关键词多的话2秒左右出结果，看来 ...

才发现原来我昨天的村级数据没上传成功（因为太大了）还要麻烦你去github下载