超人Excel 365函数专用群习题集（群号：717211831）

mao12306

1. =LET(arr,DROP(REDUCE(0,B2:B16,LAMBDA(x,y,LET(a,@TAKE(x,-1),IF(SUM(--TEXTSPLIT(a,,","))+y<=TEXTBEFORE(a,",",1,,1)*2,VSTACK(DROP(x,-1),a&","&y),VSTACK(x,y))))),1),rs,ROWS(arr),arr_1,DROP(REDUCE("",SEQUENCE(rs),LAMBDA(x,y,VSTACK(x,IFNA(HSTACK(--TEXTSPLIT(INDEX(arr,y,1),,","),y),y)))),1),REDUCE({"组长","成员","数量"},SEQUENCE(rs),LAMBDA(x,y,LET(o,FILTER(A2:B16,TAKE(arr_1,,-1)=y),VSTACK(x,IFNA(HSTACK(@o,DROP(o,1)),@o))))))

复制代码

shaowu459

mao12306 发表于 2025-1-26 15:09

感谢跟帖支持。
针对这类从上到下不定行分组，我有一个帖子，算个递归模板吧，可以解决很多类似问题，发过来供参考。
https://club.excelhome.net/threa ... tml?_dsign=b1015fb6

mao12306

谢谢老师分享

jmeasyone

刘明。。。。。。

mao12306

1. =LET(a,DROP(REDUCE("",A2:A7,LAMBDA(x,y,VSTACK(x,EXPAND(y,OFFSET(y,,1),,y)))),1),rs,ROUNDUP(ROWS(a)/162,0),mo,MOD(ROWS(a),162),arr,REDUCE(a,SEQUENCE(rs),LAMBDA(x,y,LET(ty,IF(ROWS(x)<162,TAKE(x,mo),TAKE(x,162)),un,UNIQUE(ty),VSTACK(IF(ROWS(x)<162,DROP(x,mo),DROP(x,162)),MAP(un,LAMBDA(z,z&","&ROWS(FILTER(ty,ty=z))&","&y&"号箱")))))),a_1,TEXTSPLIT(TEXTJOIN(";",,arr),",",";"),VSTACK({"规格型号","数量","箱号"},IFERROR(--a_1,a_1)))

复制代码

mao12306

shaowu459 发表于 2023-10-7 22:42
员工职级结构图转换：

交个作业啊，总感觉写的很臃肿，远不如老师简介高效。

1. =LET(arr,LET(a,A2:A46,b,B2:B46,c,C2:C46,a_1,FILTER(b,COUNTIF(b,c)=0),a_2,XLOOKUP(a_1,b,a),aa,a_1&"-"&a_2,REDUCE(aa,a,LAMBDA(x,y,DROP(REDUCE(0,SEQUENCE(ROWS(x)),LAMBDA(w,z,LET(e,INDEX(x,z,),t,TAKE(e,,-1),s,IFNA(TEXTBEFORE(t,"-"),","),p,SORT(FILTER(b,c=s,0)),k,XLOOKUP(p,b,a),VSTACK(w,IF(p<>0,IFNA(HSTACK(e,p&"-"&k),""),HSTACK(e,"")))))),1)))),by,BYCOL(arr,LAMBDA(x,OR(LEN(x)>4))),v_1,"第"&SEQUENCE(,SUM(N(by)))&"级",v_2,SUBSTITUTE(FILTER(arr,by),"-",CHAR(10)),VSTACK(v_1,v_2))

复制代码

shaowu459

mao12306 发表于 2025-2-27 12:57
交个作业啊，总感觉写的很臃肿，远不如老师简介高效。

这些题都是写着玩的，长短无所谓，思路清晰易懂即可。主要是因为可用函数和方法太多，太灵活了，很多思路能想通差不多就能写出来。

HDD666

shaowu459 发表于 2023-10-6 20:55
填空题答案回填。

好贴，必须顶一下

mao12306

交个作业哈

1. =LET(a,DROP(REDUCE(11,C2:C28,LAMBDA(x,y,LET(t,@TAKE(x,-1),IF(SUM(--TEXTSPLIT(t,,",",1))+y<=10,VSTACK(DROP(x,-1),t&","&y),VSTACK(x,y))))),1),DROP(REDUCE(0,SEQUENCE(ROWS(a)),LAMBDA(x,y,LET(l,--TEXTSPLIT(@CHOOSEROWS(a,y),,",",1),VSTACK(x,EXPAND(SUM(l),ROWS(l),,""))))),1))

复制代码