按省市县镇四级对列文本拆分

飞天篮球猪

集算器解法，仅供参考...

HAOQ

构建一个辅助列，其他列的公式很简单，然后筛选辅助列为错误值的结果粘贴出来就好

咔咔乱坠

楼主365版本的话一条公式直接出
第一种写法

1. =LET(萝,A3:A44982,莉,B3:B44982,a,FILTER(A3:B44982,RIGHT(萝,3)<>"000"),b,LAMBDA(x,LEFT(LEFT(TAKE(a,,1),x)&REPT(0,9),9)),HSTACK(b(2),XLOOKUP(b(2),萝,莉),b(4),XLOOKUP(b(4),萝,莉),b(6),XLOOKUP(b(6),萝,莉),a))

复制代码

第二种

1. =LET(萝,A3:A44982,莉,B3:B44982,a,FILTER(A3:B44982,RIGHT(萝,3)<>"000"),b,LEFT(LEFT(TAKE(a,,1),{2,4,6})&REPT(0,9),9),c,LAMBDA(x,IF({1,0},CHOOSECOLS(b,x),XLOOKUP(CHOOSECOLS(b,x),萝,莉))),HSTACK(c(1),c(2),c(3),a))

复制代码

Holiday2022

飞天篮球猪 发表于 2024-2-27 21:49
集算器解法，仅供参考...

您这个是怎么运行的？我的excel没有spl这样的函数，这是sql语法吗？

象山海鲜

1. =LET(J,A3:B44982,K,FILTER(J,-RIGHT(TAKE(J,,1),3)),REDUCE(K,{0,2,4},LAMBDA(x,y,LET(L,TRUNC(TAKE(K,,1),-y-3),HSTACK(L/1000,VLOOKUP(L&"",A:B,2,),x)))))

复制代码

Holiday2022

咔咔乱坠 发表于 2024-2-27 22:32
楼主365版本的话一条公式直接出
第一种写法

第一种解法看懂了，谢谢！

1. =LET(萝,A3:A44982,  //定义记号1
   
2.          莉,B3:B44982,  //定义记号2
   
3.          a,FILTER(A3:B44982,RIGHT(萝,3)<>"000"),  //定义记号3，从A列中筛选出结尾不为“000”的代码，也就是所有镇级代码
   
4.          b,LAMBDA(x,LEFT(LEFT(TAKE(a,,1),x)&REPT(0,9),9)),
   
5.          //定义新函数，用于调整镇级代码a。由于行政区划代码的前2位、前4位、前6位，在末尾补全“0”后，分别会变成省级、县级、区级代码，根据这一特性设置本函数。
   
6.          HSTACK(  //将结果进行堆叠
   
7.               b(2),XLOOKUP(b(2),萝,莉),  //省级行政区划的结果
   
8.               b(4),XLOOKUP(b(4),萝,莉),  //地级行政区划的结果
   
9.               b(6),XLOOKUP(b(6),萝,莉),  //县级行政区划的结果
   
10.               a                                       //镇级行政区划的结果
    
11.               ))

复制代码

咔咔乱坠

Holiday2022 发表于 2024-2-28 09:16
第一种解法看懂了，谢谢！

兄弟水平可以的，拆解的不错

Holiday2022

咔咔乱坠 发表于 2024-2-27 22:32
楼主365版本的话一条公式直接出
第一种写法

第2种方法也看懂了，它进一步精简了第1种方法。
全网能写出这么可爱的公式，也仅有您一个了！

1. =LET(
   
2.         萝,A3:A44982,  //定义记号1
   
3.         莉,B3:B44982,  //定义记号2
   
4.         a,FILTER(A3:B44982,RIGHT(萝,3)<>"000"),  //定义记号3，从A列中筛选出结尾不为“000”的代码，也就是所有镇级代码
   
5.         b,LEFT(LEFT(TAKE(a,,1),{2,4,6})&REPT(0,9),9),
   
6.         //调整镇级代码a。由于行政区划代码的前2位、前4位、前6位，在末尾补全“0”后，分别会变成省级、县级、区级代码，根据这一特性设置构造文本。
   
7.         //REPT的作用是，对从镇级代码中取出的结果进行末尾补“0”，LEFT的作用是仅保留结果的前9位（本案例行政区划代码总共9位）
   
8.         c,LAMBDA(x,  //定义新函数，从b和x中取出列，b为行政区划代码，x为根据b查找的地名
   
9.                 IF({1,0},
   
10.                         CHOOSECOLS(b,x),
    
11.                         XLOOKUP(CHOOSECOLS(b,x),萝,莉))),
    
12.         HSTACK(c(1),c(2),c(3),a))  //将结果进行堆叠

复制代码

咔咔乱坠

Holiday2022 发表于 2024-2-28 09:27
第2种方法也看懂了，它进一步精简了第1种方法。
全网能写出这么可爱的公式，也仅有您一个了！

c的解释有一点点问题，应为从b中取出第x列，将取出的结果（行政编码）和根据取出结果查找的地名横向堆积

Holiday2022

象山海鲜 发表于 2024-2-28 09:09

象山海鲜老师您好，为什么TRUNC函数第2参数是-y-3，该参数为负数也没啥作用吧？

1. =LET(J,A3:B44982,  //定义记号J
   
2.         K,FILTER(J,-RIGHT(TAKE(J,,1),3)), //从A列中筛选出结尾不为“000”的代码，也就是所有镇级代码，并将其定义为记号K。
   
3.         //这么写非常巧，FILTER的第2参数结果为TRUE时，会把同一行的结果筛选出来。
   
4.         //对于数字来说，0就是FALSE，非0就是TRUE，而本案例中镇级代码就是非0。
   
5.         REDUCE(K,{0,2,4},  //本来reduce函数就不好理解，象山海鲜老师在这里还用了一个数组，真是太牛了
   
6.                 //可以分开来看，将其拆分为：
   
7.                 //1. REDUCE(K,0,……),区级和镇级
   
8.                 //2. REDUCE(K,2,……),地级和镇级
   
9.                 //3. REDUCE(K,4,……),省级和镇级
   
10.                 LAMBDA(x,y,
    
11.                         LET(L,TRUNC(TAKE(K,,1),-y-3), //TRUNC函数的作用是：将数字截为整数或指定位数的小数，将指定位置后的数字全部扔掉
    
12.                                 //TRUNC函数第2参数是-y-3，该参数为负数也没啥作用吧？
    
13.                                 HSTACK(L/1000,              //调整行政区划代码，依次取出省、市、县三级
    
14.                                         VLOOKUP(L&"",A:B,2,),     //根据行政区划代码查找地名
    
15.                                         x)  //也就是前面定义的K，有两列，分别是行政区划代码和乡镇名
    
16.                                 //这里有一个特别巧妙的地方，L、K、x的行数都是相同的，所以在堆叠时直接横向摆放即可，不必考虑错行的问题
    
17.                                 ))))

复制代码