函数题目！

=LET(a,PIVOTBY(1&A2:A20&";",C2:C20,"、"&B2:B20,CONCAT,,0,,0,-1),TRIM(TEXT({1,-1}%,IFERROR(BYROW(a,LAMBDA(x,TEXTJOIN(TEXT(ROW(1:99)," # "),,TOCOL(IF(x>"",MID(x,2,99)&TAKE(a,1),\),2)))),"部门;名次\/姓名\/业绩"))))

不感兴趣

开通SVIP免广告

=LET(a,UNIQUE(A2:A20),DROP(REDUCE(0,a,LAMBDA(x,y,LET(m,FILTER(B2:C20,A2:A20=y),n,GROUPBY(DROP(m,,1),TAKE(m,,1),ARRAYTOTEXT,,0,-1),VSTACK(x,HSTACK(y,TEXTJOIN(" ",,HSTACK(SEQUENCE(ROWS(n)),DROP(n,,1),TAKE(n,,1)))))))),1))
没加列标题都

这么长了，惭愧惭愧

=LET(a,PIVOTBY(1&A2:A20&";",C2:C20,"、"&B2:B20,CONCAT,,0,,0,-1),TRIM(TEXT({1,-1}%,IFERROR(BYROW(a,LAMBDA(x,TEXTJOIN(" "&ROW(1:99)&" ",,TOCOL(IF(x>"",MID(x,2,99)&TAKE(a,1),\),2)))),"部门;名次\/姓名\/业绩"))))

解2=GROUPBY(A1:A20,B1:B20&TEXT(C1:C20," 0;\\@"),LAMBDA(x,TEXTJOIN(" ",,SORTBY(x,-TEXTAFTER(x," ")))),,0)

加*去掉TRIM。=LET(a,PIVOTBY(1&A2:A20&";*",C2:C20,"、"&B2:B20,CONCAT,,0,,0,-1),TEXT({1,-1}%,IFERROR(BYROW(a,LAMBDA(x,TEXTJOIN(" "&ROW(1:9)&" ",,TOCOL(IF(x>"",MID(x,2,99)&TAKE(a,1),\),2)))),"部门;名次\\姓名\\业绩")))

=REDUCE({"部门","名次/姓名/业绩"},UNIQUE(A2:A22),LAMBDA(x,y,VSTACK(x,HSTACK(y,LET(a,FILTER(B2:C22,A2:A22=y),b,UNIQUE(TAKE(a,,-1)),CONCAT(BYROW(SEQUENCE(COUNTA(b)),LAMBDA(n,n&" "&TEXTJOIN(" ",,FILTER(TAKE(a,,1),TAKE(a,,-1)=LARGE(b,n)))&LARGE(b,n)&" "))))))))
不知道为啥不行
=LET(a,FILTER(B2:C22,A2:A22=E28),b,UNIQUE(TAKE(a,,-1)),CONCAT(BYROW(SEQUENCE(COUNTA(b)),LAMBDA(n,n&" "&TEXTJOIN(" ",,FILTER(TAKE(a,,1),TAKE(a,,-1)=LARGE(b,n)))&LARGE(b,n)&" "))))
主体这段可以，不管外面套reduce还是byrow都不行

不感兴趣

开通SVIP免广告

还真是套个tocol就可以了，uniqe返回的不是数组吗？搞不懂呀

扫二维码下载贴吧客户端

下载贴吧APP
看高清直播、视频！

11回复贴，共1页

<<返回excel吧

分享到:

日	一	二	三	四	五	六